如何通过PhpMyadmin优化SQL语句？

最近Hroot的CRM系统出奇的慢，数据量不是太大，一万多个客户、几千个联系记录、几千个订单，当点击未联系客户、待联系客户等视图时，服务器处理时间竟然达到几十妙，但是安驾的CRM系统几十万的客户也不会这么慢，速度慢的真是有点奇怪。

初步分析2个系统的差别，发现两个系统的视图不太一样，hroot的未联系客户视图需要和产品表、联系记录表、订单表关联，用了2个子查询，accountid not in (有联系记录的客户) and accountid not in (有订单的客户)，订单表、联系记录都和产品关联，而安驾的未联系客户不需要和产品表、订单表关联，只通过状态来进行区分是否联系过，没有使用子查询，由此得出结论，速度慢是由子查询引起的。google和百度mysql的子查询，很多人也反映mysql的子查询效率特别差，多一个子查询，查询效率就会发生数量级的变化。于是就赶紧修改sql语句，改用left join实现以上的功能，但结果还是一样，看来子查询还不是影响查询效率的瓶颈。

Jason建议增加mysql的缓存来提高查询效率，缓存已经有384M，应该不小了，加到500M，查询还是很慢。

和oneal讨论一下这个问题，决定找一些mysql工具来分析执行比较慢的sql语句，看了一下mysql的bin目录，看到了mysqladmin，查看一下帮助，发现一个参数比较有用，可以查看当前mysql的processlist，首先在phpmyadmin里执行sql语句，然后使用mysqladmin查看当前的processlist，发现一个现象，sql语句执行过程中，mysql一直在执行一个进程，复制数据到临时表，然后google一下临时表的信息，有朋友介绍说，如果查询数据返回的数据量比较大时，复制到临时表的操作时间比较长，这种情况可以增加临时表的空间。赶紧set了一下临时表的空间，增加到100M，但还是比较慢。

在执行sql语句的时候，发现phpmyadmin里有一个profiling选项，以前没看到过，这次是在linux xampp下测试的，然后我们就选择了一下profiling这个选项，点击这个选项发生了意想不到的现象，它竟然把sql查询的过程列举了出来：
(initialization) 0.000003
Opening tables 0.000184
System lock 0.000005
Table lock 0.000007
init 0.000035
optimizing 0.000021
statistics 0.000033
preparing 0.000021
executing 0.000004
Sending data 0.038583
end 0.000009
query end 0.000006
freeing items 0.000015
closing tables 0.000025
logging slow query 0.000004

通过这个列表，就可以知道sql语句在什么地方执行慢了，hroot的问题就是在复制数据到临时表时占用了大部分时间。

然后又点了一下profiling旁边的Explain SQL，真是太cool了，它把查询过程中使用到的select type都列举出来
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ec_account ALL PRIMARY NULL NULL NULL 16277
1 SIMPLE ec_notes ref notes_accountid_idx notes_accountid_idx 5 ec_account.accountid 2 Using where; Not exists
1 SIMPLE ec_crmentity eq_ref PRIMARY PRIMARY 4 ec_account.accountid 1 Using index
通过explain得到的列表了解到ec_notes表里没有accountid的索引，而只有primarykey的索引，于是就在ec_notes表里增加了一个有关accountid的索引，然后重新执行了一下sql语句。
Oh,My God，执行时间只有几十微秒，问题解决了，^_^，原来是索引问题！！！

通过解决这个问题我们认识到：
1）索引是非常重要的，特别是关键字段的索引；
2）phpmyadmin是强大的，通过profiling和explain可以分析sql语句的执行效率；
3）mysql是可以信赖的。

 

转载自：http://www.c3crm.com/blog/?p=413