浅析如何定位，排除和避免MySQL性能故障

首先是如何检查SQL的效率.

　　1.善用explain:

　　设计SQL后，应使用explain命令检查SQL，看是否使用到索引，是否存在filesort,重点检查检索的行数(rows)是否太大。

　　一般来说.

　　rows<1000，是在可接受的范围内的。

　　rows在1000~1w之间，在密集访问时可能导致性能问题，但如果不是太频繁的访问(频率低于1分钟一次)，又难再优化的话，可以接受，但需要注意观察

　　rows大于1万时，应慎重考虑SQL的设计，优化SQL，优化db，一般来说不允许频繁运行(频率低于1小时一次)。

　　rows达到10w级别时，坚决不能做为实时运行的SQL。但导数据场合除外，但导数据必须控制好时间，频度。

　　explain SQL语句应该是日常开发中的习惯动作，有时explain出来的结果，可能会出于偏离设计的意料之外,所以

　　**强烈建议在设计SQL，尤其是稍微复杂的SQL时，一定要在测试环境甚至是实际环境上预先进行explain**

　　2.MySQL慢查询日志

　　一般应打开MySQL的慢查询日志(在my.cnf中加入log_slow_queries和long_query_time两个参数),会记录所有查询持续时间超过long_query_time的SQL语句，把这些语句log下来之后，再一一分析(explain)优化。

　　3.监视当前进程

　　登陆MySQL,使用show processlist查看正在运行的SQL语句，如果正在运行的语句太多，运行时间太长，表示MySQL效率有问题。必要的时候可以将对应的进程kill掉。

　　4.系统命令

　　使用top/vmstat等系统命令来检查MySQL进程占用的cpu,内存,以及磁盘IO量。

　　对MySQL优化的文章很多，这里只提几点平时工作中比较常用到的方法。

　　◆建表时,显式指定使用innodb数据库引擎，而不是myisam,myisam引擎的锁是表锁,读锁和写锁是互斥的，读写操作是串行的，锁冲突会严重影响并发.而innodb提供行级锁，能提供较好的并发表现，在我们的业务场景里，也不会引起死锁。

　　◆善用索引，对SQL语句where条件里使用到的字段，合理建立索引。虽然对表建立索引一定程度上会影响写入效率，但在表数据规模不大，写入压力不是特别高的情况下，索引带来的好处是更多的。

　　◆当SQL语句是
由代码动态生成的，如在运行时根据用户操作加入不同的where参数，应在测试阶段对SQL生成的典型情况和边界情况进行测试，看是否有可能造成性能问题。并应适当生成一些日志，供提取最终生成的SQL进行效率分析。

◆对数据应合理分库分表，由应用层去动态的选择库和表。MySQL的innodb表虽然理论上可以装海量的数据，但在我们的业务场景下，数据控制在500w以下会比较合理，追求性能的话，最好控制在200w以下，合理索引。

　　◆需要联合查询时善用left join/right join而不是直接多表联合，怎么用，查manul ^_^

　　◆尽量不要使用select套select的复合查询，如果能拆开，尽量拆开,多条精悍的SQL,组合起来可能就是一条庞大的SQL，应该避免。

　　◆善用cache,将不常修改的，数据量有限的,又是被密集查询的信息，加载到cache里，可以有效的降低数据库压力。在一般的业务场景里，推荐使用开源memcache，简单高效。

　　◆如果一些逻辑可以放到应用层去完成，可以考虑放到应用层去完成。但如果将SQL逻辑分拆到应用层可能导致对数据更频繁的访问的话，那么需要考虑修改应用逻辑，数据结构，或回到合理的联合查询上来。

　　比如某些数据的排序可以load到php数组里，再sort.又比如需要查询A,B两个表，A表里的数据是B表里某个字段的对照说明(如A:t_service表，B.t_task表)，A表数据量有限，可以做联合查询，也可以先将A表先load到进程或内存里，用hash结构cache起来，再查B表，然后在cache里依次查询hash，获得对照说明。

　　◆关于导数据和统计性查询.导数据在计算和磁盘io上对数据库压力都会很大，应在时间和空间上合理分摊数据库压力如果需要导出批量的特定数据做分析，应建立专供数据分析的数据库服务器，或者建立临时库表，先导出数据，再在上面做分析运算。

　　导数据等可能引起批量数据读取的操作，应建立定时任务，在数据库不繁忙的时段(凌晨1~7时)运行一般的统计操作，对实时性要求都不会太高(5~10分钟以上，甚至一天，一周等)，这种数据不应在每次访问时运行库中直接count,group,而是应该由定时任务导出，建立结果表或中间结果表，供最终用户使用。

　　◆生产数据库上的操作权限应严格控制，而开发人员在生产数据库上直接运行SQL语句，要尽量慎重。

　　能做到以上这些，基本上可以算MySQL以及相关系统优化入门，可以保证不要让我们的数据库整天累趴下了。

　　最后，即使做足了功课，也还是要例行的对数据库运行情况进行观察，监控，尽早发现其性能瓶颈，在未造成危害前解决掉。

评论

1 楼 ujnlu 2011-08-04  

写的太笼统了
还有cache那块跟数据库没有什么关系吧