论MySQL的监控和调优

论MySQL的监控和调优

懂PHP的人一般都懂MySQL这一点不假，大多数书籍里也是这样，书中前面讲PHP后面到数据库这块就会讲到MySQL的一些知识，前几年MySQL一直是PHP书籍的一部分，后来开始从国外翻译了一些专门讲述MySQL的书籍。但大多数还是不如MySQL手册里讲的精细。

目前国内也有了一些讲MySQL比较好的数据，我推荐几本大家可以看，一本是《MySQL性能调优与架构设计》，一本是《深入浅出MySQL--数据库开发、优化与管理维护》这两本是笔者确实读过的，也是国内两只比较有实力的DBA团队的经验之谈。

书大家可以买回去慢慢读，作为一篇文章篇幅有限，我只用片言片语引导性的介绍一些MySQL监控和优化方面的知识，这就算是一种普及式的讲解，深入的了解我会在文中插入一些参考阅读，深入了解大家可以在参考阅读中获取。

MySQL的监控和调试办法也是MySQL逐步发展一步步完善起来的，大体上可以被分为几个方面，从日志分析性能瓶颈，从运行时信息观察性能，从运行参数调优，对执行语句进行分析调优等。

第一种方法，慢查询日志

慢查询是MySQL自带的一种长期观测执行效率的日志系统，对于长期运营的大型网站是必须要开启的一项服务，对于那些流量极少或是一般性的企业小站而言，是没有必要的，所以我发现很多正在做外包或正在做企业网站的朋友，对这方面就不太在意，甚至可能并不清楚这东西，如果想开启慢查询，需要在my.ini或my.cnf中加入以下语句：

log-slow-queries=/data/log/www_langwan_com/mysql_slow.log
long_query_time=1


记录执行在1秒以上的慢查询，初期如果网站没有进行过任何优化，光日志一天就要几十兆并不少见。日志中记录了从被监控的时候起到目前的所有可被称为慢查询的SQL语句，不一定慢的就是SELECT，UPDATE这样的操作也有可能，有时候更新索引比查询要更慢。

如果你发现日志太大，可以先调整一下long_query_time，例如放宽到5秒，先解决一些最棘手的慢查询，逐步缩小时间，直到有一天你发现慢查询日志基本消失为止，最好是完全消失，例如以下日志：

# Time: 090909 14:04:24
# User@Host: sparty[sparty] @  [localhost]
# Query_time: 20  Lock_time: 0  Rows_sent: 10  Rows_examined: 2500284
select * from test order by views limit 10;

Query_time : 执行时间20秒已经很慢了。
Lock_time :  锁定时间0秒，如果锁的时间较长，应该找到引起锁等待的语句。
Rows_sent : 返回10行记录
Rows_examined: 一共影响了250万行记录

这四个参数每个都需要仔细看，例如最后一个影响的记录数往往可以进一步缩小，例如用时间范围进一步缩小范围。从250万行数据中返回10行这明显是大动干戈了。

select * from test where create_time > 'xxxxx' order by views limit 10;

返回两周以来的数据中最新的10条数据，也许这时候你会看到Rows_examined会被减少到2万行，甚至更少。

如果你的日志真的有几十兆，最好用工具来分析慢查询日志，可以更好的捕捉到一些通病。关于MySQL慢查询分析工具，官方自带的是mysqldumpslow，但我推荐大家使用mysqlsla这个工具来进行分析，网上有一篇文章叫《五款常用mysql slow log分析工具的比较》值得一看，地址是：http://www.iteye.com/topic/242516，本文只是一个短小精悍的引导文，告诉大家方向，推荐一些书籍和文章地址，详细细节大家可以亲自翻阅，细节上的东西就不翻出来再讲了。这个工具与官方工具mysqldumpslow结合到一起使用即可。

第二种方法，show [full] processlist

这是我学过的最早的一条分析语句，当线上系统不正常运行的时候，可以在任何客户端下输入show processlist指令来观察当前MySQL正在运行的SQL语句。有三种情况是立即可以发现问题的，某update或select语句反复执行这可能是你的缓存没有生效，导致与数据库频繁交互，也许你会看到State这一列有大量的Locked标志，说明你的数据库由于读写冲突产生了锁，同时观察到Time一列的时间很大，这需要找到最初被锁的语句，后面的语句往往是受到最早那条语句的影响。最后一种情况出现大量的unauthenticated user，这是一个非常经典的问题，解决办法是在MySQL启动参数中增加skip-name-resolve即不启用DNS反向解析。

我只是提到了最普遍的三种现象关于show processlist的详细使用，可以通过网络中其他文章进行更细的了解。

第三种方法 explain

无论我们从慢查询日志还是从show指令当中拿到了一条具体的有效率问题的SQL语句，经验丰富并且语句并不复杂的基础上可以人为判断出问题的所在，但大多数情况下还是要依赖explain命令通过MySQL本身的优化策略来找到问题的关键所在，这条指令帮助我们分析SQL语句的执行效率，例如SQL语句是否利用到了索引，是否进行了额外的排序，是否进行了全表扫描等，实际上SQL语句并没有执行，仅仅是通过Explain对可能的执行效率进行了详细的分析，帮助程序员对SQL语句的索引等进行优化。网上对Explain的分析文章较多，但最详细的还是建议大家多看MySQL参考手册，应该描述的相对比较全面了，另外最近也发现Explain可以和其它一些命令选项配合使用，具体可以参考老王的一篇文章http://hi.baidu.com/thinkinginlamp/blog/item/eef0cd119239db17b8127b6f.html详细写了关于Explain的一些使用上的问题。不过对于Explain最直接有效的方法还是看MySQL手册。

第四种方法 profile

利用Profile可以观察MySQL占用的CPU及内存等信息，早期版本并不支持profile，所以你要确定自己的MySQL至少是5.0.37版关于Profile的详细用法笔者也推荐一篇资料大家可以查阅，地址是：http://blog.csdn.net/radkitty/archive/2009/10/04/4632289.aspx

第五种方法 调整MySQL参数和状态

1. 修改启动参数

MySQL参数一共有两种，第一种是启动参数，需要在MySQL启动之前改写my.cnf文件或添加到启动命令行中才可以生效，例如前面提到的慢查询日志，需要在启动前配置my.cnf文件。

2. 修改运行时状态及变量

MySQL在运行时通过两组数据来描述MySQL的状态，一种是VARIABLES变量集合，一种是STATUS状态集合，例如在命令行下输入如下两个命令：

mysql> show global VARIABLES like '%cache%';
mysql> show global STATUS like '%cache%';

第一行用于获取与cache有关的变量信息，例如是否开启了查询缓存，缓存有多大。第二行用户获取与cache有关的状态信息，例如缓存已经被使用了多少，还剩余多少等。

关于MySQL参数的调优的确有点复杂，一般应该是DBA来进行处理，但对于PHP程序员多了解些也无妨，因为大多数公司是没有专门的MySQL DBA的，所以这些工作还是需要我们自己来完成。

状态是无法设置的，只有变量可以，例如：

set global query_cache_size = 32 * 1024 * 1024;

我加大了 query_cache_size ，查询缓存大小。

由于工作关系我经常的需要帮公司调试各类MySQL状态，每次也是要重新查阅不少资料，因为MySQL的变量和状态值相当多，并且经常暗含一些公式，所以为了减轻每次的工作负担我写了一个软件叫MySQLMonitor，目前公司内的所有MySQL都使用这个软件进行了监控，通过这个软件可以获取对MySQL参数和状态优化的一些建议信息。就和我在现场调试差不多。

MySQLMonitor中的优化算法来源于已经向大家推荐的两本MySQL专业书籍，以及对MySQL参数手册中一些算法公式的研究，也包括一些自己的经验所得，可以从http://www.echohello.cn/获取到这个软件。

本文章已经在《草根》杂志第一期上首发过。