mysql-logo.gif (3082 bytes)MySQL中文参考手册

译者:晏子 (clyan@sohu.com)      主页:http://linuxdb.yeah.net


第一章, 前一章, 下一章, 最后一章目录.


10 从MySQL得到最大的性能

优化是一项复杂的任务,因为它最终需要对整个系统的理解。当用你的系统/应用的小知识做一些局部优化是可能的时候,你越想让你的系统更优化,你必须知道它也越多。

因此,本章将试图解释并给出优化MySQL的不同方法的一些例子。但是记住总是有某些(逐渐变难)是系统更快的方法留着去做。

10.1 优化概述

为了使一个系统更快的最重要部分当然是基本设计。你也需要知道你的系统将做这样的事情,那就是你的瓶颈。

最常见的瓶颈是:

10.2 系统/编译时和启动参数的调节

我们以系统级的东西开始,因为这些决策的某一些很早就做好了。在其他情况下,快速浏览这部分可能就够了,因为它对大收获并不重要,但是有一个关于在这个层次上收获有多大的感觉总是好的。

使用的缺省OS确实重要!为了最大程度地使用多CPU,应该使用Solaris(因为线程工作得确实不错)或Linux(因为2.2本的核心又确实不错的SMP支持)。而且在32位的机器上,Linux缺省有2G的文件大小限制。当新的文件系统被释出时( XFS ),希望这不久被修正。

因为我们没在很多平台上运行生产MySQL,我们忠告你在可能选择它前,测试你打算运行的平台。

其他建议:

10.2.1 编译和链接怎样影响MySQL的速度

大多数下列测试在Linux上并用MySQL基准进行的,但是它们应该对其他操作系统和工作负载给出一些指示。

当你用-static链接时,你得到最快的可执行文件。使用Unix套接字而非TCP/IP连接一个数据库也可给出好一些的性能。

在Linux上,当用pgcc-O6编译时,你将得到最快的代码。为了用这些选项编译“sql_yacc.cc”,你需要大约200M内存,因为gcc/pgcc需要很多内存使所有函数嵌入(inline)。在配置MySQL时,你也应该设定CXX=gcc以避免包括libstdc++库(它不需要)。

只通过使用一个较好的编译器或较好的编译器选项,在应用中你能得到一个10-30%的加速。如果你自己编译SQL服务器,这特别重要!

在Intel上,你应该例如使用pgcc或Cygnus CodeFusion编译器得到最大速度。我们已经测试了新的 Fujitsu编译器,但是它是还没足够不出错来优化编译MySQL。

这里是我们做过的一些测量表:

由TcX提供的MySQL-Linux的分发用pgcc编译并静态链接。

10.2.2 磁盘问题

10.2.2.1 为数据库和表使用符号链接

你可以从数据库目录移动表和数据库到别处,并且用链接到新地点的符号代替它们。你可能想要这样做,例如,转移一个数据库到有更多空闲空间的一个文件系统。

如果MySQL注意到一个表是一个符号链接,它将解析符号链接并且使用其实际指向的表,它可工作在支持realpath()调用的所有系统上(至少Linux和Solaris支持realpath())!在不支持realpath()的系统上,你应该不同时通过真实路径和符号链接访问表!如果你这样做,表在任何更新后将不一致。

MySQL缺省不支持数据库链接。只要你不在数据库之间做一个符号链接,一切将工作正常。假定你在MySQL数据目录下有一个数据库db1,并且做了一个符号链接db2指向db1

shell> cd /path/to/datadir
shell> ln -s db1 db2

现在,对在db1中的任一表tbl_a,在db2种也好象有一个表tbl_a。如果一个线程更新db1.tbl_a并且另一个线程更新db2.tbl_a,将有问题。

如果你确实需要这样,你必须改变下列在“mysys/mf_format.c”中的代码:

if (!lstat(to,&stat_buff))  /* Check if it's a symbolic link */
    if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))

把代码改变为这样:

if (realpath(to,buff))
 

10.2.3 调节服务器参数

你能用这个命令得到mysqld服务器缺省缓冲区大小:

shell> mysqld --help

这个命令生成一张所有mysqld选项和可配置变量的表。输出包括缺省值并且看上去象这样一些东西:

Possible variables for option --set-variable (-O) are:
back_log              current value: 5
connect_timeout       current value: 5
delayed_insert_timeout  current value: 300
delayed_insert_limit  current value: 100
delayed_queue_size    current value: 1000
flush_time            current value: 0
interactive_timeout   current value: 28800
join_buffer_size      current value: 131072
key_buffer_size       current value: 1048540
lower_case_table_names  current value: 0
long_query_time       current value: 10
max_allowed_packet    current value: 1048576
max_connections       current value: 100
max_connect_errors    current value: 10
max_delayed_threads   current value: 20
max_heap_table_size   current value: 16777216
max_join_size         current value: 4294967295
max_sort_length       current value: 1024
max_tmp_tables        current value: 32
max_write_lock_count  current value: 4294967295
net_buffer_length     current value: 16384
query_buffer_size     current value: 0
record_buffer         current value: 131072
sort_buffer           current value: 2097116
table_cache           current value: 64
thread_concurrency    current value: 10
tmp_table_size        current value: 1048576
thread_stack          current value: 131072
wait_timeout          current value: 28800

如果有一个mysqld服务器正在运行,通过执行这个命令,你可以看到它实际上使用的变量的值:

shell> mysqladmin variables

每个选项在下面描述。对于缓冲区大小、长度和栈大小的值以字节给出,你能用于个后缀“K”“M” 指出以K字节或兆字节显示值。例如,16M指出16兆字节。后缀字母的大小写没有关系;16M16m是相同的。

你也可以用命令SHOW STATUS自一个运行的服务器看见一些统计。见7.21 SHOW语法(得到表、列的信息)

back_log
要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 Unix listen(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效的。
connect_timeout
mysqld服务器在用Bad handshake(糟糕的握手)应答前正在等待一个连接报文的秒数。
delayed_insert_timeout
一个INSERT DELAYED线程应该在终止之前等待INSERT语句的时间。
delayed_insert_limit
在插入delayed_insert_limit行后,INSERT DELAYED处理器将检查是否有任何SELECT语句未执行。如果这样,在继续前执行允许这些语句。
delayed_queue_size
应该为处理INSERT DELAYED分配多大一个队列(以行数)。如果排队满了,任何进行INSERT DELAYED的客户将等待直到队列又有空间了。
flush_time
如果这被设置为非零值,那么每flush_time秒所有表将被关闭(以释放资源和sync到磁盘)。
interactive_timeout
服务器在关上它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE选项的客户。也可见wait_timeout
join_buffer_size
用于全部联结(join)的缓冲区大小(不是用索引的联结)。缓冲区对2个表间的每个全部联结分配一次缓冲区,当增加索引不可能时,增加该值可得到一个更快的全部联结。(通常得到快速联结的最佳方法是增加索引。)
key_buffer_size
索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),到你能负担得起那样多。如果你使它太大,系统将开始换页并且真的变慢了。记住既然MySQL不缓存读取的数据,你将必须为OS文件系统缓存留下一些空间。为了在写入多个行时得到更多的速度,使用LOCK TABLES。见7.24LOCK TABLES/UNLOCK TABLES语法
long_query_time
如果一个查询所用时间超过它(以秒计),Slow_queries记数器将被增加。
max_allowed_packet
一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节,但是可在需要时增加到max_allowed_packet个字节。缺省地,该值太小必能捕捉大的(可能错误)包。如果你正在使用大的BLOB列,你必须增加该值。它应该象你想要使用的最大BLOB的那么大。
max_connections
允许的同时客户的数量。增加该值增加mysqld要求的文件描述符的数量。见下面对文件描述符限制的注释。见18.2.4 Too many connections错误
max_connect_errors
如果有多于该数量的从一台主机中断的连接,这台主机阻止进一步的连接。你可用FLUSH HOSTS命令疏通一台主机。
max_delayed_threads
不要启动多于的这个数字的线程来处理INSERT DELAYED语句。如果你试图在所有INSERT DELAYED线程在用后向一张新表插入数据,行将被插入,就像DELAYED属性没被指定那样。
max_join_size
可能将要读入多于max_join_size个记录的联结将返回一个错误。如果你的用户想要执行没有一个WHERE子句、花很长时间并且返回百万行的联结,设置它。
max_sort_length
在排序BLOBTEXT值时使用的字节数(每个值仅头max_sort_length个字节被使用;其余的被忽略)。
max_tmp_tables
(该选择目前还不做任何事情)。一个客户能同时保持打开的临时表的最大数量。
net_buffer_length
通信缓冲区在查询之间被重置到该大小。通常这不应该被改变,但是如果你有很少的内存,你能将它设置为查询期望的大小。(即,客户发出的SQL语句期望的长度。如果语句超过这个长度,缓冲区自动地被扩大,直到max_allowed_packet个字节。)
record_buffer
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,你可能想要增加该值。
sort_buffer
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BYGROUP BY操作。见18.5 MySQL在哪儿存储临时文件
table_cache
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符,见下面对文件描述符限制的注释。对于表缓存如何工作的信息,见10.2.4 MySQL怎样打开和关闭表
tmp_table_size
如果一张临时表超出该大小,MySQL产生一个The table tbl_name is full形式的错误,如果你做很多高级GROUP BY查询,增加tmp_table_size值。
thread_stack
每个线程的栈大小。由crash-me测试检测到的许多限制依赖于该值。缺省队一般的操作是足够大了。见10.8 使用你自己的基准
wait_timeout
服务器在关闭它之前在一个连接上等待行动的秒数。也可见interactive_timeout

MySQL使用是很具伸缩性的算法,因此你通常能用很少的内存运行或给MySQL更多的被存以得到更好的性能。

如果你有很多内存和很多表并且有一个中等数量的客户,想要最大的性能,你应该一些象这样的东西:

shell> safe_mysqld -O key_buffer=16M -O table_cache=128 \
           -O sort_buffer=4M -O record_buffer=1M &

如果你有较少的内存和大量的连接,使用这样一些东西: 
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
           -O record_buffer=100k &
或甚至: 
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
           -O table_cache=32 -O record_buffer=8k -O net_buffer=1K &

如果有很多连接,“交换问题”可能发生,除非mysqld已经被配置每个连接使用很少的内存。当然如果你对所有连接有足够的内存,mysqld执行得更好。

注意,如果你改变mysqld的一个选项,它实际上只对服务器的那个例子保持。

为了明白一个参数变化的效果,这样做:

shell> mysqld -O key_buffer=32m --help

保证--help选项是最后一个;否则,命令行上在它之后列出的任何选项的效果将不在反映在输出中。

10.2.4 MySQL怎样打开和关闭数据库表

table_cache, max_connectionsmax_tmp_tables影响服务器保持打开的文件的最大数量。如果你增加这些值的一个或两个,你可以遇到你的操作系统每个进程打开文件描述符的数量上强加的限制。然而,你可以能在许多系统上增加该限制。请教你的OS文档找出如何做这些,因为改变限制的方法各系统有很大的不同。

table_cachemax_connections有关。例如,对于200个打开的连接,你应该让一张表的缓冲至少有200 * n,这里n是一个联结(join)中表的最大数量。

打开表的缓存可以增加到一个table_cache的最大值(缺省为64;这可以用mysqld-O table_cache=#选项来改变)。一个表绝对不被关闭,除非当缓存满了并且另外一个线程试图打开一个表时或如果你使用mysqladmin refreshmysqladmin flush-tables

当表缓存满时,服务器使用下列过程找到一个缓存入口来使用:

对每个并发存取打开一个表。这意味着,如果你让2个线程存取同一个表或在同一个查询中存取表两次(用AS),表需要被打开两次。任何表的第一次打开占2个文件描述符;表的每一次额外使用仅占一个文件描述符。对于第一次打开的额外描述符用于索引文件;这个描述符在所有线程之间共享。

10.2.5 在同一个数据库中创建大量数据库表的缺点

如果你在一个目录中有许多文件,打开、关闭和创建操作将会很慢。如果你执行在许多不同表上的SELECT语句,当表缓存满时,将有一点开销,因为对每个必须打开的表,另外一个必须被关闭。你可以通过使表缓冲更大些来减少这个开销。

10.2.6 为什么有这么多打开的表?

当你运行mysqladmin status时,你将看见象这样的一些东西:

Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

如果你仅有6个表,这可能有点令人困惑。

MySQL是多线程的,因此它可以同时在同一个表上有许多询问。为了是2个线程在同一个文件上有不同状态的问题减到最小,表由每个并发进程独立地打开。这为数据文件消耗一些内存和一个额外的文件描述符。索引文件描述符在所有线程之间共享。

10.2.7 MySQL怎样使用内存

下表指出mysqld服务器使用存储器的一些方式。在应用的地方,给出与存储器使用相关的服务器变量的名字。

ps和其他系统状态程序可以报导mysqld使用很多内存。这可以是在不同的内存地址上的线程栈造成的。例如,Solaris版本的ps将栈间未用的内存算作已用的内存。你可以通过用swap -s检查可用交换区来验证它。我们用商业内存漏洞探查器测试了mysqld,因此应该有没有内存漏洞。

10.2.8 MySQL怎样锁定数据库表

MySQL中所有锁定不会是死锁的。这通过总是在一个查询前立即请求所有必要的锁定并且总是以同样的顺序锁定表来管理。

WRITEMySQL使用的锁定方法原理如下:

READMySQL使用的锁定方法原理如下:

当一个锁定被释放时,锁定可被写锁定队列中的线程得到,然后是读锁定队列中的线程。

这意味着,如果你在一个表上有许多更改,SELECT语句将等待直到有没有更多的更改。

为了解决在一个表中进行很多INSERTSELECT操作的情况,你可在一张临时表中插入行并且偶尔用来自临时表的记录更新真正的表。

这可用下列代码做到:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> insert into real_table select * from insert_table;
mysql> delete from insert_table;
mysql> UNLOCK TABLES;

如果你在一些特定的情况字下区分检索的优先次序,你可以使用LOW_PRIORITY选项的INSERT。见7.14 INSERT句法

你也能改变在“mysys/thr_lock.c”中的锁代码以使用一个单个队列。在这种情况下,写锁定和读锁定将有同样优先级,它可能帮助一些应用程序。

10.2.9 数据库表级锁定的问题

MySQL的表锁定代码是不会死锁的。

MySQL使用表级锁定(而不是行级锁定或列级锁定)以达到很高的锁定速度。对于大表,表级锁定对大多数应用程序来说比行级锁定好一些,但是当然有一些缺陷。

MySQL3.23.7和更高版本中,一个人能把行插入到MyISAM表同时其他线程正在读该表。注意,目前只有在表中内有删除的行时才工作。

表级锁定使很多线程能够同时读一个表,但是如果一个线程想要写一个表,它必须首先得到独占存取权。在更改期间,所有其他想要存取该特定表的线程将等到更改就绪。

因为数据库的更改通常被视为比SELECT更重要,更新一个表的所有语句比从一个表中检索信息的语句有更高的优先级。这应该保证更改不被“饿死”,因为一个人针对一个特定表会发出很多繁重的查询。

MySQL 3.23.7开始,一个人可以能使用max_write_lock_count变量强制MySQL在一个表上一个特定数量的插入后发出一个SELECT

对此一个主要的问题如下:

对这个问题的一些可能的解决方案是:

10.3 使你的数据尽可能小

最基本的优化之一是使你的数据(和索引)在磁盘上(并且在内存中)占据的空间尽可能小。这能给出巨大的改进,因为磁盘读入较快并且通常也用较少的主存储器。如果在更小的列上做索引,索引也占据较少的资源。

你能用下面的技术使表的性能更好并且使存储空间最小:

10.4 MySQL索引的使用

索引被用来快速找出在一个列上用一特定值的行。没有索引,MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大,花费时间越多。如果表对于查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要考虑所有数据。如果一个表有1000行,这比顺序读取至少快100倍。注意你需要存取几乎所有1000行,它较快的顺序读取,因为此时我们避免磁盘寻道。

所有的MySQL索引(PRIMARYUNIQUEINDEX)在B树中存储。字符串是自动地压缩前缀和结尾空间。见7.27 CREATE INDEX句法

索引用于:

假定你发出下列SELECT语句:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果一个多列索引存在于col1col2上,适当的行可以直接被取出。如果分开的单行列索引存在于col1col2上,优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。

如果表有一个多列索引,任何最左面的索引前缀能被优化器使用以找出行。例如,如果你有一个3行列索引(col1,col2,col3),你已经索引了在(col1)(col1,col2)(col1,col2,col3)上的搜索能力。

如果列不构成索引的最左面前缀,MySQL不能使用一个部分的索引。假定你下面显示的SELECT语句:

mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果一个索引存在于(col1、col2、col3)上,只有上面显示的第一个查询使用索引。第二个和第三个查询确实包含索引的列,但是(col2)(col2、col3)不是(col1、col2、col3)的最左面前缀。

如果LIKE参数是一个不以一个通配符字符起始的一个常数字符串,MySQL也为LIKE比较使用索引。例如,下列SELECT语句使用索引:

mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

在第一条语句中,只考虑有"Patrick" <= key_col < "Patricl"的行。在第二条语句中,只考虑有"Pat" <= key_col < "Pau"的行。

下列SELECT语句将不使用索引:

mysql> select * from tbl_name where key_col LIKE "%Patrick%";
mysql> select * from tbl_name where key_col LIKE other_col;

在第一条语句中,LIKE值以一个通配符字符开始。在第二条语句中,LIKE值不是一个常数。

如果 column_name 是一个索引,使用column_name IS NULL的搜索将使用索引。

MySQL通常使用找出最少数量的行的索引。一个索引被用于你与下列操作符作比较的列:=>>=<<=BETWEEN和一个有一个非通配符前缀象'something%'LIKE的列。

任何不跨越的在WHERE子句的所有AND层次的索引不用来优化询问。

下列WHERE子句使用索引:

... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
          /* optimized like "index_part1='hello'" */

这些WHERE子句使用索引:

... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
... WHERE index=1 OR A=10                  /* No index */
... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows */

10.5 存取或更新数据的查询速度

首先,一件事情影响所有的询问。你有的许可系统设置越复杂,你得到更多的开销。

如果你不让任何GRANT语句执行,MySQL将稍微优化许可检查。因此如果你有很大量,值得花时间来避免授权,否则更多的许可检查有更大的开销。

如果你的问题是与一些明显的MySQL函数有关,你总能在MySQL客户中计算其时间:

mysql> select benchmark(1000000,1+1);
+------------------------+
| benchmark(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.32 sec)

上面显示MySQL能在PentiumII 400MHz上以0.32秒执行1,000,000个+表达式。

所有MySQL函数应该被高度优化,但是以可能有一些例外并且benchmark(loop_count,expression)是找出是否你的查询有问题的一个极好工具。

10.5.1 估计查询性能

在大多数情况下,你能通过计算磁盘寻道估计性能。对小的表,你通常能在1次磁盘寻道中找到行(因为这个索引可能被缓冲)。对更大的表,你能估计它(使用 B++ 树索引),你将需要:log(row_count)/log(index_block_length/3*2/(index_length + data_pointer_length))+1次寻道找到行。

MySQL中,索引块通常是1024个字节且数据指针通常是4个字节,这对一个有一个索引长度为3(中等整数)的 500,000 行的表给你:log(500,000)/log(1024/3*2/(3+4)) + 1= 4 次寻道。

象上面的索引将要求大约 500,000 * 7 * 3/2 = 5.2M,(假设索引缓冲区被充满到2/3(它是典型的)),你将可能在内存中有索引的大部分并且你将可能仅需要1-2调用从OS读数据来找出行。

然而对于写,你将需要 4 次寻道请求(如上)来找到在哪儿存放新索引并且通常需2次寻道更新这个索引并且写入行。

注意,上述不意味着你的应用程序将缓慢地以 N log N 退化!当表格变得更大时,只要一切被OS或SQL服务器缓冲,事情将仅仅或多或少地更慢。在数据变得太大不能被缓冲后,事情将开始变得更慢直到你的应用程序仅仅受磁盘寻道限制(它以N log N增加)。为了避免这个增加,索引缓冲随数据增加而增加。见10.2.3 调节服务器参数

10.5.2 SELECT查询的速度

总的来说,当你想要使一个较慢的SELECT ... WHERE更快,检查的第一件事情是你是否能增加一个索引。见10.4 MySQL 索引的使用。在不同表之间的所有引用通常应该用索引完成。你可以使用EXPLAIN来确定哪个索引用于一条SELECT语句。见7.22 EXPLAIN句法(得到关于一条SELECT的信息)

一些一般的建议:

10.5.3 MySQL怎样优化WHERE子句

where优化被放在SELECT中,因为他们最主要在那里使用里,但是同样的优化被用于DELETEUPDATE语句。

也要注意,本节是不完全的。MySQL确实作了许多优化而我们没有时间全部记录他们。

MySQL实施的一些优化列在下面:

下面是一些很快的查询例子:

mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name
           WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name
           ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> SELECT ... FROM tbl_name
           ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;

下列查询仅使用索引树就可解决(假设索引列是数字的):

mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql> SELECT COUNT(*) FROM tbl_name
           WHERE key_part1=val1 AND key_part2=val2;
mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

下列查询使用索引以排序顺序检索,不用一次另外的排序:

mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,...
mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...

10.5.4 MySQL怎样优化LEFT JOIN

MySQL中,A LEFT JOIN B实现如下:

10.5.5 MySQL怎样优化LIMIT

在一些情况中,当你使用LIMIT #而不使用HAVING时,MySQL将以不同方式处理查询。

10.5.6 INSERT查询的速度

插入一个记录的时间由下列组成:

这里的数字有点与总体时间成正比。这不考虑打开表的初始开销(它为每个并发运行的查询做一次)。

表的大小以N log N (B 树)的速度减慢索引的插入。

加快插入的一些方法:

为了对LOAD DATA INFILEINSERT得到一些更快的速度,扩大关键字缓冲区。见10.2.3 调节服务器参数

10.5.7 UPDATE查询的速度

更改查询被优化为有一个写开销的一个SELECT查询。写速度依赖于被更新数据大小和被更新索引的数量。

使更改更快的另一个方法是推迟更改并且然后一行一行地做很多更改。如果你锁定表,做一行一行地很多更改比一次做一个快。

注意,动态记录格式的更改一个较长总长的记录,可能切开记录。因此如果你经常这样做,时不时地OPTIMIZE TABLE是非常重要的。见7.9 OPTIMIZE TABLE句法

10.5.8 DELETE查询的速度

删除一个记录的时间精确地与索引数量成正比。为了更快速地删除记录,你可以增加索引缓存的大小。见10.2.3 调节服务器参数

从一个表删除所有行比删除行的一大部分也要得多。

10.6 选择一种表类型

用MySQL,当前(版本 3.23.5)你能从一个速度观点在4可用表的格式之间选择。

静态MyISAM
这种格式是最简单且最安全的格式,它也是在磁盘格式最快的。速度来自于数据能在磁盘上被找到的难易方式。当所定有一个索引和静态格式的东西时,它很简单,只是行长度乘以行数量。而且在扫描一张表时,用每次磁盘读取来读入常数个记录是很容易的。安全性来自于如果当写入一个静态MyISAM文件时,你的计算机崩溃,myisamchk能很容易指出每行在哪儿开始和结束,因此它通常能回收所有记录,除了部分被写入的那个。注意,在MySQL中,所有索引总能被重建。
动态MyISAM
这种格式有点复杂,因为每一行必须有一个头说明它有多长。当一个记录在更改时变长时,它也可以在多于一个位置上结束。你能使用OPTIMIZE tablemyisamchk整理一张表。如果你在同一个表中有象某些VARCHARBLOB列那样存取/改变的静态数据,将动态列移入另外一个表以避免碎片可能是一个好主意。
压缩MyISAM
这是一个只读类型,用可选的myisampack工具生成。
内存(HEAP 堆)
这种表格式对小型/中型查找表十分有用。对拷贝/创建一个常用的查找表(用联结)到一个(也许临时)HEAP表有可能加快多个表联结。假定我们想要做下列联结,用同样数据可能要几倍时间。
SELECT tab1.a, tab3.a FROM tab1, tab2, tab3
        WHERE tab1.a = tab2.a and tab2.a = tab3.a and tab2.c != 0;

为了加速它,我们可用tab2和tab3的联结创建一张临时表,因为用相同列( tab1.a )查找。这里是创建该表和结果选择的命令。

CREATE TEMPORARY TABLE test TYPE=HEAP
        SELECT
                tab2.a as a2, tab3.a as a3
        FROM
                tab2, tab3
        WHERE
                tab2.a = tab3.a and c = 0;
SELECT tab1.a, test.a3 from tab1, test where tab1.a = test.a1;
SELECT tab1.b, test.a3 from tab1, test where tab1.a = test.a1 and something;

10.6.1 静态(定长)表的特点

10.6.2 动态表的特点

10.6.3 压缩表的特点

MySQL能支持不同的索引类型,但是一般的类型是ISAM。这是一个B树索引并且你能粗略地为索引文件计算大小为(key_length+4)*0.67,在所有的键上的总和。(这是对最坏情况,当所有键以排序顺序被插入时。)

字符串索引是空白压缩的。如果第一个索引部分是一个字符串,它也将压缩前缀。如果字符串列有很多尾部空白或是一个总不能用到全长的VARCHAR列,空白压缩使索引文件更小。如果很多字符串有相同的前缀,前缀压缩是有帮助的。

10.6.4 内存表的特点

堆桌子仅存在于内存中,因此如果mysqld被关掉或崩溃,它们将丢失,但是因为它们是快,不管怎样它们是有用的。

MySQL内部的HEAP表使用没有溢出区的100%动态哈希并且没有与删除有关的问题。

你只能通过使用在堆表中的一个索引的用等式存取东西(通常用=操作符)。

堆表的缺点是:

  1. 你要为你想要同时使用的所有堆表需要足够的额外内存。
  2. 你不能在索引的一个部分上搜索。
  3. 你不能顺序搜索下一个条目(即使用这个索引做一个ORDER BY)。
  4. MySQL也不能算出在2个值之间大概有多少行。这被优化器使用来决定使用哪个索引,但是在另一方面甚至不需要磁盘寻道。

10.7 其他优化技巧

对加快系统的未分类的建议是:

10.8 使用你自己的基准测试

你决定应该测试你的应用程序和数据库,以发现瓶颈在哪儿。通过修正它(或通过用一个“哑模块”代替瓶颈),你能容易确定下一个瓶颈(等等)。即使对你的应用程序来说,整体性能“足够好”,你至少应该对每个瓶颈做一个“计划”,如果某人“确实需要修正它”,如何解决它。

对于一些可移植的基准程序的例子,参见MySQL基准套件。见11 MySQL 基准套件。你能利用这个套件的任何程序并且为你的需要修改它。通过这样做,你能尝试不同的你的问题的解决方案并测试哪一个对你是最快的解决方案。

在系统负载繁重时发生一些问题是很普遍的,并且我们有很多与我们联系的客户,他们在生产系统中有一个(测试)系统并且有负载问题。到目前为止,被一种这些的情况是与基本设计有关的问题(表扫描在高负载时表现不好)或OS/库问题。如果系统已经不在生产系统中,它们大多数将容易修正。

为了避免这样的问题,你应该把一些力气放在在可能最坏的负载下测试你的整个应用!

10.9 设计选择

MySQL在分开的文件中存储行数据和索引数据。许多(几乎全部)其他数据库在同一个文件中混合行和索引数据。我们相信,MySQL的选择对非常广泛的现代系统的来说是较好的。

存储行数据的另一个方法是在一个分开的区域保存每列信息(例子是SDBM和Focus)。这将对每个存取多于一列的查询获得一个性能突破。因为在多于一列被存取时,这快速退化,我们相信这个模型对通用功能的数据库不是最好。

更常见的情形是索引和数据一起被存储(就象Oracle/Sybase)。在这种情况中,你将在索引的叶子页面上找到行信息。有这布局的好处是它在许多情况下(取决于这个索引被缓冲得怎样)节省一次磁盘读。有这布局的坏处是:

10.10 MySQL设计局限/折衷

因为MySQL使用极快的表锁定(多次读/一次写),留下的最大问题是在同一个表中的一个插入的稳定数据流与慢速选择的一个混合。

我们相信,在其他情况下,对大多数系统,异常快速的性能使它成为一个赢家。这种情形通常也可能通过表的多个副本来解决,但是它要花更多的力气和硬件。

对一些常见的应用环境,我们也在开发一些扩展功能以解决此问题。

10.11 可移植性

因为所有SQL服务器实现了SQL的不同部分,要花功夫编写可移植的SQL应用程序。对很简单的选择/插入,它是很容易的,但是你需要越多,它越困难,而且如果你想要应用程序对很多数据库都快,它变得更难!

为了使一个复杂应用程序可移植,你需要选择它应该与之工作的很多SQL服务器。

当你能使用MySQL的crash-me 程序(http://www.mysql.com/crash-me-choose.htmy)来找出你能与之使用的数据库服务器的选择的函数、类型和限制。crash-me现在对任何可能的事情测试了很长时间,但是它仍然理解测试过的大约450件事情。

例如,如果你想要能使用Informix 或 DB2,你不应该有比18个字符更长的列名。

MySQL基准程序和crash-me是独立于数据库的。通过观察我们怎么处理它,你能得到一个感觉,你必须为编写你的独立于数据库的应用程序做什么。基准本身可在MySQL源代码分发的“sql-bench”目录下找到。他们用DBI数据库接口以Perl写成(它解决问题的存取部分)。

http://www.mysql.com/benchmark.html看这个基准的结果。

正如你可在这些结果看见的那样,所有数据库都有一些弱点。这就是他们不同的设计折衷导致的不同行为。

如果你为数据库的独立性而努力,你需要获得每个SQL服务器瓶颈的良好感受。MySQL在检索和更新方面很快,但是在同一个表上混合读者/写者方面将有一个问题。在另一方面,当你试图存取你最近更新了的行时,Oracle有一个很大问题(直到他们被清空到磁盘上)。事务数据库总的来说在从记录文件表中生成总结表不是很好,因为在这种情况下,行级锁定几乎没用处。

为了使你的应用程序“确实独立于数据库”,你需要定义一个容易的可扩展的接口,用它你可操纵你的数据。因为C++在大多数系统上可以得到的,使用数据库的一个C++ 类接口是有意义的。

如果你使用一些某个数据库特定的功能(在MySQL中,象REPLACE命令),你应该为SQL服务器编码一个方法以实现同样的功能 (但是慢些)。用MySQL,你能使用/*! */语法把MySQL特定的关键词加到查询中。在/**/中的代码将被大多数其他SQL服务器视为一篇注释(被忽略)。

如果高性能真的比准确性更重要,就像在一些web应用程序那样。一种可能性是创建一个应用层,缓冲所有的结果以给你更高的性能。通过只是让老的结果在短时间后‘过期’,你能保持缓存合理地刷新。这在极高负载的情况下是相当不错的,在此情况下,你能动态地增加缓存到更大并且设定较高的过期时限直到一切回到正常。

在这种情况下,创建信息的表应该包含缓存初始大小和表一般应该被刷新几次的信息。

10.12 我们已将MySQL用在何处?

在MySQL起初开发期间,MySQL的功能适合我们的最大客户。他们为在瑞典的一些最大的零售商处理数据仓库。

我们从所有商店得到所有红利卡交易的每周总结并且我们被期望为所有店主提供有用的信息以帮助他们得出他们的广告战如何影响他们的顾客。

数据是相当巨量的(大约每月7百万宗交易总结)并且我们保存4-10年来的数据需要呈现给用户。我们每周顾客那里得到请求,他们想要“立刻”访问来自该数据的新报告。

我们通过每月将所有信息存储在压缩的“交易”表中来解决它。我们有一套简单的宏/脚本用来生成来自交易表的不同条件( 产品组,顾客id,商店...)的总结表。报告是由一个进行语法分析网页的小perl脚本动态生成的网页,在脚本中执行SQL语句并且插入结果。现在我们很想使用PHP或mod_perl,但是那时他们没有。

对图形数据,我们用C语言编写了一个简单的工具,它能基于SQL查询的结果(对结果的一些处理)产生赠品,这也从分析HTML文件的perl脚本中动态地执行。

在大多数情况下,一个新的报告通过简单地复制一个现有脚本并且修改其中的SQL查询来完成。在一些情况下,我们将需要把更多的字段加到一个现有的总结表中或产生一个新的,但是这也相当简单,因为我们在磁盘上保存所有交易表。(目前我们有至少50G的交易表和200G的其他顾客数据)。

我们也让我们的顾客直接用ODBC存取总结表以便高级用户能自己试验数据。

我们用非常中档的Sun Ultra sparcstation ( 2x200 Mz )来处理,没有任何问题。最近我们升级了服务器之一到一台2个CPU 400 Mz的Ultra sparc,并且我们现在计划处理产品级的交易,这将意味着数据增加10番。我们认为我们能通过只是为我们的系统增加更多的磁盘就能赶上它。

我们也在试验Intel-Linux以便能更便宜地得到更多的cpu动力。既然我们有二进制可移植的数据库格式(在3.32中引入),我们将开始在应用程序的某些部分使用它。

我们最初的感觉是Linux在低到中等负载时执行的较好,但是你开始得到导致的高负载时,Solaris将表现更好,因为磁盘IO的极限,但是我们还没有关于这方面的任何结论。在与一些Linux核心开发者的讨论以后,这可能是 Linux 的副作用,它给批处理以太多的资源使得交互的性能变得很低。当大的批处理正在进行时,这使机器感觉很慢且无反应。希望这将在未来的Linux内核中解决。


第一章, 前一章, 下一章, 最后一章目录.