7MySQL的优化
熟悉Oracle优化的人都知道Oracle的重要优化对象就是SGA区,其实MySQL也是一样的.MySQL的参数配置在my.cnf文件中。[注]。
mysql的配置大部分在my.cnf中，参数和SHOW VARIABLES显示的变量一样名称，但是有些却不是，有些是在启动参数中设置，有些是必须用SET语句更改。而且多个版本中有些参数名称还不一样。有时候搞得人也挺头大的。
7.1配置参数
MySQL有两种途径途径了解其的配置参数，一个是MySQL交互模式下的命令SHOW VARIABLES，一个使用mysqladmin variables 查询。
MySQL的配置参数分为2种，全局的和局部的。局部的配置变量可以在每次会话中自己更改。
从MySQL 4.0以后开始，在SHOW VARIABLES中显示的参数，大部分可以动态使用SET命令进行更改。
基本参数配置：
|
参数
|
说明
|
|
bind-address
|
绑定的IP地址
|
|
user
|
用户
|
|
port
|
端口号
|
|
datadir
|
数据文件目录
|
|
basedir
|
msyql应用程序的目录
|
|
socket
|
socket文件，默认在/tmp目录下，但是建议不要这样设置，/tmp目录是一个大家都愿意破坏的目录
|
|
default-table-type
|
默认表类型
|
查询的Cache的是从MySQL4.0版本开始提供的功能。相关的参数为：
|
参数
|
说明
|
|
query_cache_size
|
查询Cache的尺寸
|
|
query_cache_type
|
查询的Cache类型。
0 OFF，不进行缓冲
1 ON，进行缓冲
2 DEMAND，对SELECT SQL_CACHE开头的查询进行缓冲
|
|
query_cache_limit
|
查询的结果的限制长度，小于这个长度的数据才能Cache
|
MyISAM的索引参数：key_buffer_size为MyISAM引擎的最关键的优化参数之一。
|
参数
|
说明
|
|
key_buffer_size
|
(关键参数),索引块用的缓冲区大小，所有的连接程序线程共用
|
|
key_cache_block_size
|
每一个索引block的大小，默认1024字节,从4.1.1后才出现这个参数，原来都是直接采用1024字节作为Block的长度
|
InnoDB使用的参数：InnoDB的参数较少，笼统而不细致，内存的管理多由InnoDB引擎自己负责，主要的缓冲就是innodb_buffer_pool_size参数分配的缓冲。这样配置倒是简单了，但没有了细致优化乐趣。
|
参数
|
说明
|
|
innodb_buffer_pool_size
|
innodb的缓冲区大小，存放数据和索引,一般设置为机器内存的50%-80%
(关键参数)
|
|
innodb_log_buffer_size
|
InnoDB日志缓冲区大小
|
|
innodb_flush_method
|
刷新日志的方法
|
|
innodb_additional_mem_pool_size
|
innodb内存池的大小，存放着各种内部使用的数据结构
|
|
innodb_data_home_dir
|
InnoDB数据文件的目录
|
|
innodb_data_file_path
|
数据文件配置
|
|
innodb_log_files_in_group
|
Innodb日志的
|
|
innodb_log_file_size
|
Innodb日志文件的尺寸
|
|
innodb_lock_wait_timeout
|
等待数据锁的超时时间，避免死锁的一种措施
|
|
innodb_flush_log_at_trx_commit
|
日志提交方式 (关键参数)
0每秒写1次日志，将数据刷入磁盘，相当于每秒提交一次事务。
1每次提交事务写日志，同时将刷新相应磁盘，默认参数。
2每提交事务写一次日志，但每隔一秒刷新一次相应的磁盘文件[注]
|
|
innodb_force_recovery
|
在Innodb的自动恢复失败后，从崩溃中强制启动，有1-6个级别，数值越低恢复的方式也保守，默认为4。尽量使用较保守方式恢复。
恢复后要注释删除这一行。
|
Log的参数：MySQL的日志有6种，查询日志，慢查询日志，变更日志，二进制变更日志，告警日志，错误日志。my.cnf中可以配置日志的前缀和日志参数。日志是监控数据库系统的重要途径。
|
参数
|
说明
|
|
log
|
查询日志，记录所有的MySQL的命令操作，在跟踪数据库运行时非常有帮助，但在实际环境中就不要使用了
|
|
log-update
|
变更日志，用文本方式记录所有改变数据的变更操作，
|
|
log-bin
|
二进制变更日志，更加紧凑，使用mysqlbinlog读取，操作，转换
|
|
binlog_cache_size
|
临时存放某次事务的SQL语句缓冲长度
|
|
max_binlog_cache_szie
|
最大的二进制Cache日志缓冲区尺寸
|
|
max_binlog_size
|
最大的二进制日志尺寸
|
|
log-error
|
导致无法启动的错误日志
|
|
log-warnings
|
告警日志
|
|
long_query_time
|
慢查询时间限度，超过这个限度，mysqld认为是一个慢查询
|
|
log-queries-not-using-indexes
|
没有使用索引查询的日志,方便记录长时间访问的查询进行优化
|
|
log-slow-queries
|
慢速的查询日志，
|
打开文件参数：
|
参数
|
说明
|
|
table_cache
|
能够被同时打开的表最大个数，打开一个表使用2个文件描述符
(关键参数)
|
|
open_files_limit
|
mysqld保留的文件描述符号个数，和table_cache和max_connections设置相关，默认为0
设置为0, 系统设置max_connections*5或者max_connections + table_cache*2中的最大值
|
关于连接通信的参数：
|
参数
|
说明
|
|
max_connections
|
最大的连接数
|
|
max_connect_errors
|
同一个地址最大错误连接数，防止攻击用
|
|
net_buffer_length
|
服务器和客户之间通讯的使用的缓冲区长度
|
|
max_allowed_packet
|
服务器和客户之间最大的通信的缓冲区长度
|
|
net_read_timeout
|
网络读取超时
|
|
net_write_timeout
|
网络写入超时
|
|
interactive_timeout
|
交互模式下的没有操作后的超时时间
|
|
wait_ timeout
|
非交互模式的没有操作后的超时时间
|
每个会话使用的buffer设置，默认使用my.cnf的配置，也可以使用每个会话设置。不要设置的过大。
|
参数
|
说明
|
|
read_buffer_size
(record_buffer)
|
对数据表作顺序读取的缓冲大小
|
|
read_rnd_buffer_size
|
在排序后，读取结果数据的缓冲区大小，
|
|
sort_buffer_size
(sort_buffer)
|
用来完成排序操作的线程使用的缓冲区大小
|
|
join_buffer_size
|
全关联操作缓冲区（没有索引而进行关联操作）
|
|
write_buffer_size
|
myisamchk的特有选项
写入缓冲区大小
|
|
myisam_sort_buffer_szie
|
为索引的重新排序操作（比如CREATE INDEX）的分配的缓冲区的长度
|
对于磁盘缓式写入的一些选项，delay_key_write，flush，flush_time参数可能可以进一步提高MyISAM引擎的性能，但是在服务器Crash的时候，可能会丢失数据，造成表损坏。
MySQL对于插入语句支持一个选项INSERT DELAYED，如果有这个选项，MySQL将这些插入语句放入一个队列，并不马上读入磁盘。delay_insert_XXX的选项都是配置这个功能，
MySQL创建表的时候也有一个选项，DELAY_KEY_WRITE，有这个选项描述的表的键发生改动后，改动可以缓冲在key_buffer中，不立即回写磁盘。
|
参数
|
说明
|
|
delay_insert_limit
|
INSERT DELAYED语句选项。（插入语句的描述）
处理INSERT DELAYED语句，MYSQL插入delay_insert_limit条语句后检查是否有查询语句，如有有去查询，如果没有，则继续插入
|
|
delay_insert_timeout
|
在处理完INSERT DELAYED对列的插入数据后，MYSQL等待delay_insert_timeout秒后看看是否有INSERT DELAYED数据，如果有继续，如果没有结束这次操作。
|
|
delay_query_size
|
INSERT DELAYED插入数据对列的长度
|
|
max_delayed_threads
|
处理INSERT DELAYED语句的最大线程个数
|
|
delay_key_write
|
对于使用DELAY_KEY_WRITE选项的创建的表，可以延缓键读写
0N不延缓所有的键写如操作
OFF延缓有DELAY_KEY_WRITE选项的标的键写入操作
ALL延缓所有的表
|
|
flush
|
是否要在每个操作后立即刷新数据表
|
|
flush_time
|
每隔多少秒，对数据表进行一次刷新。关闭后打开。
|
关闭某些选项：关闭某些选项可以加快MySQL的运行速度，这些选项在MySQL SHOW VARIABLES 中显示为have_XXX 的变量。
|
参数
|
说明
|
|
skip-openssl
|
关闭mysql服务器对SSL加密的支持
|
|
skip-isam
|
关闭mysql服务器对isam的引擎的支持
|
|
skip-bdb
|
关闭mysql服务器对bdb的引擎的支持
|
|
skip-external-locking
|
不使用外部锁,MySQL的外部锁用于防止其他程序修改正在数据文件，但其在部分系统上不可靠，一般都不使用。(4.03版本前叫skip-locking)
|
|
skip-innodb
|
关闭mysql服务器对innodb的引擎的支持
|
|
skip_networking
|
只能从本地访问数据库
|
其他参数：
|
参数
|
说明
|
|
slow_launch_time
|
用多于这个时间创建的线程视为一个慢创建线程
|
|
binlog_cache_size
|
临时存放构成每次事务的SQL的缓冲区长度，(全局变量，但是应该影响每一个会话)
|
|
max_binlog_cache_size
|
二进制日志缓冲区的最大长度，其实就是事物的最大长度，默认4G
|
|
max_heap_table_size
|
HEAP表的最大允许长度
|
|
max_tmp_tables
|
临时tables的最大个数
|
|
myisam_recover_options
|
myisam引擎的自动恢复模式
|
|
thread_cache_size
|
线程缓冲区的所能容纳的最大线程个数
|
|
tmp_table_size
|
临时tables的最大尺寸
|
7.2运行状态监控
MySQL有两种途径途径了解其的运行状态，一个是MySQL交互模式下的命令SHOW STATUS，一个使用mysqladmin extended-status。两种方法异曲同工，通过观察其运行状态可以了解我们的参数设置是否合理，是否有要优化的表和数据。
SHOW STATUS显示了MySQL从运行开始到现在为止状态，大部分为一些计数器，使用FLUSH STATUS可以重新对各种状态变量进行计数。
表19 MySQL的状态计数器
|
参数
|
说明
|
|
Aborted_clients
|
因客户没有正确关闭而丢弃的连接数量，没有正确关闭指没有调用mysql_close就退出，连接超时，数据传送中客户端退出
|
|
Aborted_connects
|
试图连接MySQL服务器但没有成功的次数
|
|
Connections
|
试图连接MySQL服务器的尝试次数，（包括成功的和没有成功）
|
|
Com_XXX
|
执行语句的计数器，比如Com_select变量记录了select语句的个数
|
|
Created_tmp_disk_tables
|
使用磁盘创建临时表的次数，如果要创建的临时表的尺寸大于tmp_table_size，那么临时表将创建在磁盘上，
|
|
Created_tmp_tables
|
创建临时表的次数
|
|
Delayed_XXX
|
INSERT DELAYED语句的执行性能参数
|
|
Opened_tables
|
曾经打开过的数据表总数
|
|
Open_tables
|
当前处于打开的表个数
|
|
Open_files
|
当前处于打开的文件个数
|
|
Bytes_received
|
从客户收到的字节总数
|
|
Bytes_send
|
发送给客户的字节总数
|
|
Handler_commit
Handler_rollback
|
事务提交或者回滚的次数
|
|
Handler_delete
|
对数据表删除一条记录的次数
|
|
Handler_update
|
对数据表修改一条记录的次数
|
|
Handler_write
|
对数据表插入一条记录的次数
|
|
Handler_read_first
|
读取索引中第一个索引项的个数
|
|
Handler_read_key
|
根据索引直接读取一行数据的次数，这个数值高表示数据库有较好的检索能力。
|
|
Handler_read_next
|
根据索引读取下个数据行的请求次数. 在一个索引的区间内进行查询( > < ,orderby 这类查询条件)会影响这个计数器。
|
|
Handler_read_prev
|
根据索引读取前个数据行的请求次数.用于一些反序查询。
|
|
Handler_read_rnd
|
通过一个固定位置(应该就是不通过索引)读取一个数据行的次数。这个数值很高表示你的很多查询操作的结果需要排序，可能这些查询操作不能适当使用索引而要检索整个表。
|
|
Handler_read_rnd_next
|
请求从数据文件中读取下一个记录的次数.如果有很多全表的检索这个值将很高. 通常这表示数据表没有合适的索引。
|
|
key_blocks_used
|
索引缓冲区块中已经被使用的区块大小。Block的尺寸默认是1024字节，4.1.1后可以通过key_cache_block_size参数设置。可以根据key_buffer_size/(1024 or key_cache_block_size) 得到Block总数，然后知道key_buffer的利用率
|
|
Key_read_requests
|
从缓冲读取1个Block的次数
|
|
Key_read
|
从磁盘读取的次数
|
|
Key_write_requests
|
写入索引缓冲区写入一个Block的次数
|
|
Key_write
|
写回磁盘的次数
|
|
Qcache_free_blocks
|
Qcache没有使用的内存块个数
|
|
Qcache_free_memory
|
Qcache没有使用的内存尺寸
|
|
Qcache_hits
|
查询在Qcache中的命中次数，和Com_select比较，就可以知道Qache的大约命中率是多少。
|
|
Qcache_inserts
|
加入Cache中的查询个数
|
|
Qcache_lowmem_prunes
|
由于Qcache不够用，造成替换出Qcache的查询个数
|
|
Qcache_not_cached
|
没有能Cache的查询个数
|
|
Slow_queries
|
慢查询的次数，如果一个查询的所用的时间大于long_query_time设置的时间，则计数加1
|
|
Select_XXXX
|
关联查询的一些状态计数
|
|
Innodb_XXXX
|
InnoDB的状态技术器，不过只有MySQL 5.02的版本才支持这些计数器。这儿略过
|
|
Table_locks_waited
|
必须等待后才能完成表锁定的请求个数，如果这个数值和下面数值的比率过大，表示数据库的性能较低
|
|
Table_locks_immediate
|
无需等待，立即完成表锁定的请求个数。
|
|
Thread_connected
|
现在处在连接打开状态的线程个数
|
|
Thread_cached
|
现在在现场缓冲区的线程个数
|
|
Thread_created
|
到目前为止,创建的线程个数
|
|
Thead_running
|
现在运行的线程个数，不是所有打开的线程都在运行，有些会处于SLEEP状态
|
InnoDB的状态监控的要在交互模式下使用show innodb status命令。相对的可以利用InnoDB状态参数也过少。
7.3参数调整
了解了参数的含义，剩下的事情就是如何设置一个合理的MySQL参数了。下面我们结合几个关键参数讲解以下如何通过根据状态了解参数是否设置合理。
比如MySQL的重要参数table_cache,如果设置过小，SHOW STATUS显示的变量Opened_tables 会迅速增加，而正常状态下应该是保持稳定或者缓慢增加。
又比如想要了解查询Cache的状态，可以查询SHOW STATUS显示的Qcache_XXX 变量。变量Com_select表示查询语句的数量（实际放入Cache的语句数量是Qcache_inserts），Qcache_hits表示查询在Cache中命中的数量，除一下就可以得到你的查询Cache的命中率。是否值得使用查询Cache，就很明确了。
Com_XXX相关的状态计数是反映数据库处理的SQL语句的。通过这写计数器你可以了解你的数据库什么操作更多，从而更好的优化。
key_buffer_size对于MyISAM引擎是最重要的参数之一，其的使用情况可以通过key_XXX参数了解，key_blocks_used 状态计数器可以让你了解Key_buffer的使用情况，Block的尺寸默认是1024字节，MySQL4.1.1版本后可以通过key_cache_block_size参数设置。可以根据key_buffer_size/(1024 or key_cache_block_size) 得到Block总数，然后知道key_buffer的利用率。
最大连接数max_connections 是否设置合理可以查询SHOW STATUS的变量Connections，Connections变量为尝试连接的数量，监控其的增长速率就也可以知道客户对连接数的需求程度。
检查Table_locks_waited的数量以及和Table_locks_immediate的比率，可以了解查询操作是否有过多的锁等待，如果等待数量很大，建议你考虑更换引擎或者继续分割你的表。
Thread_XXX相关的状态参数可以告诉你MySQL的线程处理情况，Thead_running和Thread_connected的比率可以让你知道大部分连接的状态如何，是在SLEEP还是RUNNIG。
如果状态中的Created_tmp_disk_tables和Created_tmp_tables的比率过大，表示大量的临时表是在磁盘上创建的。这样效率当然是很低的，所以建议调整tmp_table_size的大小。
Slow_queries也是一个关键的计数器，MySQL将查询时间超过long_query_time的查询时为一个慢查询，出现一个慢查询就会将Slow_queries+1 。而long_query_time的默认设置是10秒，这个时间是比较长的，你可以调整的更加短一些，同时MySQL提供了日志可以记录慢查询的查询语句。my.cnf中可以配置log-queries-not-using-indexes参数记录没有使用索引查询的日志,log-slow-queries参数记录慢速的查询日志，在数据库设计初期阶段，通过这两个日志可以了解数据表的设计是否合理。
7.4飞得更高
当然，调整MySQL的配置参数只是优化的一种方式。飞得更高的方法还很多。
kenix问过一个奇怪的问题，如果想最小的成本进行优化,如何进行？对于这个问题我和sunbirdcui的答案一致，把硬盘改为RAID10（0+1）的。对于提高性能，升级硬件可能是最简单最省力的方式，而且对于我们大部分的DB，在磁盘空间不够前，机器的性能可能已经远远跟不上服务增长而要分布了。所以建议DB服务器不要采用RAID 5而采用RAID 10，这样可以轻松的提高20-30%的性能。网上能找到的相关比较的文章是《常见RAID模式性能比拼》，不过可惜是其比较的不是SCSI硬盘。
而对于内存的使用方面，MySQL好像没有过高要求，主要的除了每个连接使用的read_buffer_size 和sort_buffer_size尺寸的内存外，全局有一个Qcache使用query_cache_size尺寸的cache。MyISAM引擎主要使用的是key_buffer_size尺寸的内存,InnoDB主要使用内存就是 innodb_buffer_pool_size 尺寸内存。而且InnoDB可能还有一个2G内存使用限制（是否真没有这个问题有待验证）。所以依靠大规模增加内存提高MySQL性能未必有效。MySQL自己在内存管理可以提高的地方还很多。
其他的方面也还有很多，比如Linux的内核优化，2.6的内核的更加倾向于I/O的应用。大家也许记得我前面有台测试机器使用的是380G3 2.4.21的内核，一台是380G3 2.6.8.1的内核我们把它们相同测试的数据列出来。但请大家注意的是，由于不是同一台机器（型号倒是一致），参考意义有待进一步证实。
表20 不同内核下的性能比较
|
参数配置
|
插入100000条记录耗时
|
处理速度(条记录/s)
|
查询100000条记录耗时
|
处理速度(条记录/s)
|
修改100000条记录耗时
|
处理速度(条记录/s)
|
删除100000条记录耗时
|
处理速度(条记录/s)
|
|
2.4.21内核下MyISAM引擎,
|
197
|
5076.14
|
333
|
3003.00
|
173
|
5780.35
|
175
|
5714.29
|
|
2.6.8.1内核下MyISAM引擎
|
169
|
5917.16
|
282
|
3546.10
|
165
|
6060.61
|
172
|
5813.95
|
|
2.4.21内核下InnoDB引擎,
|
221
|
4524.89
|
425
|
2352.94
|
255
|
3921.57
|
234
|
4273.50
|
|
2.6.8.1内核下InnoDB引擎
|
176
|
5681.82
|
277
|
3610.11
|
182
|
5494.51
|
164
|
6097.56
|
另外的一个非常重要的决定性能的因素就是你对数据表的设计了。这部内容分包括数据表结构的设计，范式和索引，以及MySQL本身的查询优化.但是它们不在本文的讨论范畴之内了。
8后记
终于，终于在2005年把手上的测试数据整理为文档了。其实手上最早的测试记录已经是今年6月的了。毕业后工作之外几乎就没有写过5页以上的文档，这个文档几乎又是半途而废。激励自己完成这个稿子的事情很偶然，一次培训，liddlechen介绍说MySQL Insert的语句快于Select，我当即反驳，但是回来翻看自己的测试记录，却的确如此。一方面惭愧，一方面感觉到测试的数据如果不文档化几乎没有任何意义。
8.1如何看待测试数据
如何看待这些测试数据？正如我一直强调的任何Benchmark测试所产生的数据都只是参考，对于真实环境都是一种模拟，比如对于查询，如果是多个表的真实环境，数据表的查询效率可能要低很多，因为相关的索引必须在内存中换进换出，效率肯定成倍降低。
本文的作用是给出比较，对比参考，让你了解什么什么状态下可能具有更好的性能，比如MySQL的本地环境下的性能是网络环境下的性能的3倍多。STMT比原有的API可以提高处理30%的性能，这才是Benchmark参考的意义。
8.2鸣谢
感谢 zengyu提供InnoDB 测试的指导，感谢wang,chris对文档提供意见。同时也向对提供机器给我测试的echoqin,penghao表示谢意。
记得和wang聊天的一句话，“搞技术的就是在自己领域YY的人”。把自己的YY作品献给5年（9年）的技术生活。
9附录：
9.1测试程序
下面是我的测试程序，IP和密码部分已经注释了，请参考时注意，另外，所有的测试程序基于我自己的一套程序库zenlib，如果有需要，可以请向我索要，您也可以直接使用MySQL API替换之。而且对于Benchmark，我建议你自己写自己的测试用例，这样才能有最好的效果。
9.2参考书目
参考书目：
|
参考书目
|
作者
|
说明
|
|
《MySQL® Administrator’s Guide》
|
MySQL AB
|
|
|
《MySQL® Database Design and Tuning》
|
Robert D. Schneider
|
|
|
《MySQL:The definitive guide to using,programming,and administering MySQL 4 (second Edition)》
中文译本《MySQL权威指南》
|
Paul DuBois
|
中文译本是国内最好的中文MySQL书籍
唯一的瑕疵是没有介绍MySQL4.0后版本的新特性
|
|
《MySQL Reference Manual》
|
MySQL自己随颁布发布的参考手册，还是最好，最全的MySQL的参考
而且早期版本参考手册有中文翻译文档。
|