1.5.2　利用tuning-primer脚本来调优MySQL数据库

MySQL在线上稳定运行一段时间后，就可以调用MySQL调优脚本tuning-primer.sh来检查参数的设置是否合理，该脚本的下载地址为：

http://www.day32.com/MySQL/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状态）

笔者之前所在公司的主营业务是CPA电子广告平台，公司规模比较小，所以没有配备专业的MySQL DBA，线上的MySQL数据库（四核CPU）服务器问题比较多，用tuning-primer.sh脚本扫描后发现有如下问题：

·MySQL数据库有时连接非常慢，严重时会被拖死。

通过show full processlist命令可以发现大量的“unauthenticated user”连接，数据库肯定每次都要响应，所以速度越来越慢，解决方法其实很简单：在mysql.cnf里添加skip-name-resolve，即不启用DNS反向解析。

发生这种情况的原因其实也很简单，MySQL的认证实际上是user＋host的形式（也就是说user可以相同），所以MySQL在处理新连接时会试着去解析客户端连接的IP，启用参数skip-name-resolve后MySQL授权的时候就只能使用纯IP的形式了。

·数据库在繁忙期间负载很大，长期达到了13，远远超过了系统平均负载4，这个肯定是不正常的。

通过脚本扫描，发现没有新建thread_cache_size，所以加上了thread_cache_size＝256，然后重启数据库，数据库的平均负载一下子降到了5~6。

·发现数据库里有张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)

上面出现的这种问题很严重，new_cheat没有建好索引，导致每次都要全表扫描2529529行记录，严重消耗了服务器的I/O资源，所以立即建好索引，并用show index命令查看了表索引：

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结果：

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条记录（请对比关注rows这行）就获得了查询结果，系统负载由5~6直接降到了3.07~3.66了，这个负载还是能在可接受范围之内的。

注意

MySQL的explain命令可用于SQL语句的查询执行计划（QEP）。这条命令的输出结果能够让我们了解MySQL优化器是如何执行SQL语句的。这条命令并没有提供任何调整建议，但 它提供的重要信息能够帮助你做出调优决策。

最后要说明一点的是，对于网站来说，MySQL单机优化对整体性能提升的作用毕竟有限，尤其是在MySQL单机写入方面，如果在工作中遇到了那种对MySQL即时写入和读取速度要求很高的场景，建议大家可以多关注分布式的SQL解决方案，例如Hadoop的HBase和AWS的RedShift等分布式SQL系统。