MySQL的优化

7               MySQL的优化

熟悉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的参考 而且早期版本参考手册有中文翻译文档。

 

9.3         作者说明

曾星（Sail）  工作于 腾讯科技（深圳）有限公司  互动娱乐事业部

Tencent Technology (Shenzhen) Company Limited  IED

EMail：sailzeng@tencent.com

本着自由的精神，阅读者可以无须授权就可以自由的转载这个文档，我只保留作者的署名权利，也就是说，你转载只需保留这段说明和文档的完整性。