MySQL的优化
熟悉Oracle优化的人都知道Oracle的重要优化对象就是SGA区,其实MySQL也是一样的.MySQL的参数配置在my.cnf文件中。[注]。
mysql的配置大部分在my.cnf中，参数和SHOW VARIABLES显示的变量一样名称，但是有些却不是，有些是在启动参数中设置，有些是必须用SET语句更改。而且多个版本中有些参数名称还不一样。有时候搞得人也挺头大的。
7.1 配置参数
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
系统设置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选项的创建的表，可以延缓键读写
不延缓所有的键写如操作
OFF延缓有DELAY_KEY_WRITE选项的标的键写入操作
ALL延缓所有的表
flush
是否要在每个操作后立即刷新数据表
flush_time
每隔多少秒，对数据表进行一次刷新。关闭后打开。
中显示为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 运行状态监控
。两种方法异曲同工，通过观察其运行状态可以了解我们的参数设置是否合理，是否有要优化的表和数据。
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总数，然后知道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参数了。下面我们结合几个关键参数讲解以下如何通过根据状态了解参数是否设置合理。
会迅速增加，而正常状态下应该是保持稳定或者缓慢增加。
变量。变量Com_select表示查询语句的数量（实际放入Cache的语句数量是Qcache_inserts），Qcache_hits表示查询在Cache中命中的数量，除一下就可以得到你的查询Cache的命中率。是否值得使用查询Cache，就很明确了。
Com_XXX相关的状态计数是反映数据库处理的SQL语句的。通过这写计数器你可以了解你的数据库什么操作更多，从而更好的优化。
状态计数器可以让你了解Key_buffer的使用情况，Block的尺寸默认是1024字节，MySQL4.1.1版本后可以通过key_cache_block_size参数设置。可以根据key_buffer_size/(1024 or key_cache_block_size) 得到Block总数，然后知道key_buffer的利用率。
是否设置合理可以查询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的大小。
。而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硬盘。
和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(R) Administrator’s Guide》
MySQL AB
《MySQL(R) 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的参考
而且早期版本参考手册有中文翻译文档。
9.3 作者说明
工作于 腾讯科技（深圳）有限公司 互动娱乐事业部
#mysql #linux