6.1.3 MySQL上线后根据status状态进行适当优化
MySQL数据库上线后，可以等其稳定运行一段时间后再根据服务器的“status”状态进行适当优化，我们可以用如下命令列出MySQL服务器运行的各种状态值：
mysql> show global status;
笔者个人较喜欢的用法是show status like ’查询值%'。
1. 慢查询
有时为了定位系统中效率比较低下的Query语名，需要打开慢查询日志，也就是Slow
Query Log，查询慢查询日志的相关命令如下：
mysql> show variables like '%slow%';
+---------------------+---------+
| Variable_name | Value |
+---------------------+---------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
+---------------------+---------+
mysql> show global status like '%slow%';
+---------------------+---------+
| Variable_name | Value |
+---------------------+---------+
| Slow_launch_threads | 0 |
| Slow_queries | 4148 |
+---------------------+---------+
打开慢查询日志可能会对系统性能有一点影响，如果你的MySQL是主-从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响也会很小。另外，可用MySQL自带的命令mysqldumpslow进行查询，比如，下面的命令可以查出访问次数最多的20个sql语句：
mysqldumpslow -s c -t 20 host-slow.log
2. 连接数
如果经常遇见“MySQL: ERROR 1040: Too manyconnections”的问题，一种情况是访问量确实很高，MySQL服务器抗不住，这个时候需要考虑增加从服务器分散读压力；另外一种情况是MySQL配置文件中max_connections的值过小。举例说明：
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+
这台MySQL服务器的最大连接数是256，然后查询一下该服务器响应的最大连接数：
mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 245 |
+----------------------+-------+
MySQL服务器过去的最大连接数是245，没有达到服务器连接数的上限256，应该不会出现1040错误，比较理想的设置是：
Max_used_connections / max_connections ＊ 100% ≈ 85%
最大连接数占上限连接数的85%左右，如果发现比例在10%以下，则说明MySQL服务器连接数的上限设置过高。
3. Key_buffer_size
key_buffer_size是设置MyISAM表索引擎缓存空间的大小，此参数对MyISAM表性能影响最大，下面是一台以MyISAM为主要存储引擎服务器的配置：
mysql> show variables like 'key_buffer_size';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| key_buffer_size | 536870912 |
+-----------------+-----------+
从上面的配置可以看出，系统分配了512MB内存给key_buffer_size，我们再查看一下key_buffer_size的使用情况：
mysql> show global status like 'key_read%';
+---------------------+-------------+
| Variable_name | Value |
+---------------------+-------------+
| Key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+---------------------+-------------+
一共有27813678764个索引读取请求，有6798830个请求在内存中没有找到，直接从硬盘读取索引，计算索引未命中缓存的概率：key_cache_miss_rate=Key_reads/Key_read_requests * 100%。
比如上面的数据，key_cache_miss_rate为0.0244%,4000个索引读取请求才有一个直接读硬盘，已经很BT了，key_cache_miss_rate在0.1%以下都很好（每1000个请求有一个直接读硬盘），如果key_cache_miss_rate在0.01%以下，说明key_buffer_size分配过多，可以适当减少。
MySQL服务器还提供了key_blocks_*参数，如下所示：
mysql> show global status like 'key_blocks_u%';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| Key_blocks_unused | 0 |
| Key_blocks_used | 413543 |
+--------------------+----------+
Key_blocks_unused表示未使用的缓存簇（blocks）数，Key_blocks_used表示曾经用到的最大的blocks数，比如这台服务器，所有的缓存都用到了，要么增加key_buffer_size，要么就是过渡索引，把缓存占满了。比较理想的设置是：Key_blocks_used/（Key_blocks_unused + Key_blocks_used)*100%≈80%。
4. 临时表
当执行语句时，关于已经被创造的隐含临时表的数量，我们可以用如下命令查知其具体情况：
mysql> show global status like 'created_tmp%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1771587 |
+-------------------------+---------+
每次创建临时表时，Created_tmp_tables都会增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也会增加。Created_tmp_files表示MySQL服务创建的临时文件数，比较理想的配置是：Created_tmp_disk_tables/Created_tmp_tables*100%<=25%。
比如上面的服务器Created_tmp_disk_tables/Created_tmp_tables*100%=1.20%，应该说已经相当不错了。我们再看一下MySQL服务器对临时表的配置：
mysql> show variables where Variable_name in（'tmp_table_size', 'max_heap_table_
size');
+---------------------+-----------+
| Variable_name | Value |
+---------------------+-----------+
| max_heap_table_size | 268435456 |
| tmp_table_size | 536870912 |
+---------------------+-----------+
只有256MB以下的临时表才能全部放在内存中，超过的就会放到硬盘临时表。
5. Open Table的情况
Open_tables表示打开表的数量，Opened_tables表示打开过的表数量，可以用如下命令查看其具体情况：
mysql> show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 919 |
| Opened_tables | 1951 |
+---------------+-------+
如果Opened_tables数量过大，说明配置中table_cache（MySQL5.1.3之后这个值叫做table_open_cache）的值可能太小，我们查询一下服务器table_cache值：
mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache | 2048 |
+---------------+-------+
比较合适的值为：
Open_tables / Opened_tables ＊ 100% >= 85%
Open_tables / table_cache ＊ 100% <= 95%
6. 进程使用情况
如果我们在MySQL服务器的配置文件中设置了thread_cache_size，当客户端断开之时，服务器处理此客户请求的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，可以用如下命令查看：
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+-------------------+-------+
如果发现Threads_created的值过大，表明MySQL服务器一直在创建线程，这也是比较耗费资源的，可以适当增大配置文件中thread_cache_size的值，查询服务器thread_cache_size配置，如下所示：
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+
示例中的MySQL服务器还是比较健康的。
7. 查询缓存（query cache）
它涉及的主要有两个参数，qrery_cache_size和query_cache_type。其中query_cache_size设置MySQL的Query Cache大小，query_cache_type设置使用查询缓存的类型，可以用如下命令查看其具体情况：
mysql> show global status like 'qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 22756 |
| Qcache_free_memory | 76764704 |
| Qcache_hits | 213028692 |
| Qcache_inserts | 208894227 |
| Qcache_lowmem_prunes | 4010916 |
| Qcache_not_cached | 13385031 |
| Qcache_queries_in_cache | 43560 |
| Qcache_total_blocks | 111212 |
+-------------------------+-----------+
MySQL查询缓存变量的相关解释如下所示：
❑ Qcache_free_blocks：缓存中相邻内存块的个数，数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
❑ Qcache_free_memory：缓存中的空闲内存。
❑ Qcache_hits：表示有多少次命中。通过这个参数可以查看到Query Cache的基本效果。
❑ Qcache_inserts：每插入一个查询时就会增大。命中次数除以插入次数就是不中比率。
❑ Qcache_lowmem_prunes：表示有多少条Query因为内存不足而被清除出Query Cache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚地了解系统中Query Cache的内存大小是否真的足够，是否频繁出现因为内存不足而有Query被换出的情况。
❑ Qcache_not_cached：不适合进行缓存的查询数量，通常是由于这些查询不是SELECT语句或用了now()之类的函数。
❑ Qcache_queries_in_cache：当前缓存的查询（和响应）数量。
❑ Qcache_total_blocks：缓存中块的数量。
我们再查询一下服务器上关于query_cache的配置：
mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 203423744 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
各字段的解释如下所示：
❑ query_cache_limit：超过此大小的查询将不缓存。
❑ query_cache_min_res_unit：缓存块的最小值。
❑ query_cache_size：查询缓存大小。
❑ query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存select sql_no_cache查询。
❑ query_cache_wlock_invalidate：表示当有其他客户端正在对MyISAM表进行写操作时，读请求是要等WRITE LOCK释放资源后再查询还是允许直接从Query Cache中读取结果，默认为FALSE（可以直接从Query Cache中取得结果）。
query_cache_min_res_unit的配置是一柄“双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果都是小数据查询，就容易造成内存碎片和浪费。
查询缓存碎片率=Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者如果你的查询都是小数据量，尝试减小query_cache_min_res_unit。
查询缓存利用率=（query_cache_size-Qcache_free_memory）/query_cache_size*100%
查询缓存利用率在25%以下说明query_cache_size设置过大，可适当减小；查询缓存利用率在80%以上且Qcache_lowmem_prunes＞50则说明query_cache_size可能过小，或是碎片太多。
查询缓存命中率=（Qcache_hits-Qcache_inserts）/Qcache_hits*100%
示例服务器中的查询缓存碎片率等于20.46%，查询缓存利用率等于62.26%，查询缓存命中率等于1.94%，说明命中率很差，可能写操作比较频繁，而且可能存在碎片。
8. 排序使用情况
表示系统中对数据进行排序时使用的Buffer，我们可以用如下命令查看：
mysql> show global status like 'sort%';
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| Sort_merge_passes | 29 |
| Sort_range | 37432840 |
| Sort_rows | 9178691532 |
| Sort_scan | 1860569 |
+-------------------+------------+
Sort_merge_passes包括如下步骤：MySQL首先会尝试在内存中排序，使用的内存大小由系统变量sort_buffer_size决定，如果它不够大，则把所有的记录都读到内存中，而MySQL则会把每次在内存中排序的结果存到临时文件中，等MySQL找到所有记录之后，再把临时文件中的记录做一次排序，这次再排序就会增加sort_merge_passes。实际上，MySQL会用另一个临时文件来存储再次排序的结果，所以我们通常会看到sort_merge_passes增加的数值是创建临时文件数的两倍。因为用到了临时文件，所以速度可能会较慢，增大sort_buffer_size会减少sort_merge_passes和创建临时文件的次数，但盲目地增大sort_buffer_size并不一定能提高速度。
9. 文件打开数（open_files）
我们在处理MySQL故障时发现，当open_files大于open_files_limit值时，MySQL数据库就会发生卡住的现象，导致Apache服务器也打不开相应页面，大家在工作中要注意这个问题，我们可以用如下命令查看其具体情况：
mysql> show global status like 'open_files';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Open_files | 1410 |
+------------------+-------+
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 4590 |
+------------------+-------+
比较合适的设置是：Open_files/open_files_limit*100%<=75%。
10. Innodb_buffer_pool_size的合理设置
InnoDB存储引擎的缓存机制和MyISAM的最大区别在于，InnoDB不仅仅缓存索引，同时还会缓存实际的数据。此参数用于设置InnoDB最主要的buffer（InnoDB buffer pool）大小，也就是用户表及索引数据的最主要缓存空间，对InnoDB整体性能的影响也最大。
无论是MySQL官方手册还是网络上许多人分享的InnoDB优化建议，都是建议简单地将此值设置为整个系统物理内存的50%～80%之间。这种做法其实是不妥的，我们应根据实际的运行场景来正确设置此项参数。以笔者的生产数据库（因为历史遗留问题，表引擎有InnoDB和MyISAM两种）为例，物理服务器总内存为8 GB，配置Innodb_buffer_pool_size为2048 MB，网站稳定上线后，通过以下命令观察：
mysql> show status like 'Innodb_buffer_pool_%';
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Innodb_buffer_pool_pages_data | 118505 |
| Innodb_buffer_pool_pages_dirty | 30 |
| Innodb_buffer_pool_pages_flushed | 4061659|
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_misc | 12567 |
| Innodb_buffer_pool_pages_total | 131072 |
| Innodb_buffer_pool_read_ahead_rnd | 18293 |
| Innodb_buffer_pool_read_ahead_seq | 19019 |
| Innodb_buffer_pool_read_requests | 3533588224 |
| Innodb_buffer_pool_reads | 1138442 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 58802802 |
+-----------------------------------+------------+
12 rows in set (0.00 sec)
通过此命令得出的结果可以计算出InnoDB buffer pool的read命中率大约为：（3533588224-1138442）/3533588224=99.96%。
write命令中率大约为：118505/131072=90.41%。
我们发现这个值设置过小，后期考虑将其增加到4GB左右（这个值的设定具体要求也要根据服务器的物理内存而定）。
注意
32位CentOS因为系统方面的制约，此值最大也只能配置为2.7GB左右，所以建议大家的数据库系统选择为64位的系统。
6.1.4 利用tuning-primer脚本进行数据库调优
在工作中，等MySQL在线上稳定运行一段时间后，可以调用MySQL调优脚本tuning-primer.sh来检查我们的参数是否合理，它的下载地址为：http://launchpad.net/mysql-tuning-primer/trunk/1.5-r5/+download/tuning-primer.sh。
该脚本使用“SHOW STATUS LIKE…”和“SHOW VARIABLES LIKE…”命令获得MySQL的相关变量和运行状态。然后根据推荐的调优参数对当前的MySQL数据库进行测试。最后根据不同颜色的标识来提醒用户需要注意的各个参数设置。
当前版本会处理如下这些推荐的参数：
❑ Slow Query Log（慢查询日志）
❑ Max Connections（最大连接数）
❑ Worker Threads（工作线程）
❑ Key Buffer（Key缓冲）
❑ Query Cache（查询缓存）
❑ Sort Buffer（排序缓存）
❑ Joins（连接）
❑ Temp Tables（临时表）
❑ Table（Open & Definition）Cache（表缓存）
❑ Table Locking（表锁定）
❑ Table Scans（read_buffer）（表扫描，读缓冲）
❑ InnoDB Status（InnoDB状态）
笔者用tuning-primer.sh脚本扫描新接手的一台MySQL数据库服务器后发现还是有很多问题的，比如：
1）MySQL有时连接非常慢，严重时会被拖死。
通过Show processlist发现大量的unauthenticated user连接，数据库肯定每次都要响应，所以速度越来越慢。解决方法其实很简单，在mysql.cnf里添加skip-name-resolve即可，也就是不启用DNS反应解析。
发生这种情况的原因也很简单，MySQL的认证实际上是user+host的形式（也就是说user可以相同），所以MySQL在处理新连接时会尝试解析客户端连接的IP，启用参数skip-name-resolve后MySQL授权的时候就只能用纯IP的形式了。
2）数据库在繁忙期间负载很大，长期达到了13，远远超过了系统平均负载4。
通过脚本扫描，发现没有建立thread_cache_size，所以加上thread_cache_size=256后，重启数据库，数据库的平均负载一下子降到了5～6。
3）数据库中有张new_cheat_id表被读取频繁，而且长期处于Sending data状态。
初步怀疑为磁盘I/O压力过大所致，所以操作如下：
explain SELECT count(new_cheat_id) FROM new_cheat WHERE account_id = '14348612'
AND offer_id = '689'\G;
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ 1. row ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
id: 1
select_type: SIMPLE
table: new_cheat
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2529529
Extra: Using where
1 row in set (0.00 sec)
这个问题很严重，后来跟研发团队确认，此表忘记建立索引了，导致每次都是全表扫描2529529行记录，严重消耗服务器的I/O资源，所以立即建好索引，并用show index命令查看表索引：
mysql> show index from new_cheat;
+-------------+------------+------------+-------------+--------------+----------
+-------------+------------+-----------+--------------+-------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation|
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+------------+-------------+--------------+----------
+-------------+------------+-----------+--------------+-------------+---------+
| new_cheat | 0 | PRIMARY | 1 | new_cheat_id | A |
2577704 | NULL | NULL | | BTREE | |
| new_cheat | 1 | ip | 1 | ip | A |
1288852 | NULL | NULL | | BTREE | |
| new_cheat | 1 | account_id | 1 | account_id | A |
1288852 | NULL | NULL | | BTREE | |
+-------------+------------+------------+-------------+--------------+----------
+-------------+------------+-----------+--------------+-------------+---------+
3 rows in set (0.01 sec)
我们再看explain结果：
mysql> explain SELECT count(new_cheat_id) FROM new_cheat WHERE account_id =
'14348612' AND offer_id = '689'\G;
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ 1. row ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
id: 1
select_type: SIMPLE
table: new_cheat
type: ref
possible_keys: account_id
key: account_id
key_len: 4
ref: const
rows: 6
Extra: Using where
1 row in set (0.00 sec)
大家可以发现，建立索引后，此SQL通过account_id索引直接读取了6条记录就获得了查询结果，系统负载由5～6直接降到了3.07～3.66。
附上我们的电子订单系统MySQL数据库（服务器为DELL R710,16G内存，RAID 10，表引擎为MyISAM）调整后myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
long_query_time = 2
slow_query_log
slow_query_log_file = /data/mysql/slow.log
skip-name-resolve
skip-locking
skip-networking
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 512M
innodb_data_file_path = ibdata1:256M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no-auto-rehash
Remove the next comment character if you are not familiar with SQL
safe-updates
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
