如何找出MySQL数据库中的低效SQL语句

要找出mysql中低效的sql语句我们可以使用 EXPLAIN分析低效sql，但是在使用 EXPLAIN之前我需要开启mysql慢查询日志，这样才可以使用 EXPLAIN，下面我们一起来看看。

面对业务的迅猛发展，DBA的一项重要工作就是及时发现数据库中的低效SQL语句，有的可以立刻着手解决（比如缺少合适的索引），有的需要尽快反馈给开发人员进行修改。

MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句：

1，slow_query_log

这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。

2，long_query_time

当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。

3，slow_query_log_file

记录日志的文件名。

4，log_queries_not_using_indexes

这个参数设置为ON,可以捕获到所有未使用索引的SQL语句,尽管这个SQL语句有可能执行得挺快.

Windows下开启MySQL慢查询

MySQL在Windows系统中的配置文件一般是是my.ini找到[mysqld]下面加上如下代码:

log-slow-queries = F:/MySQL/log/mysqlslowquery.log

long_query_time = 2

Linux下启用MySQL慢查询

MySQL在Windows系统中的配置文件一般是是my.cnf找到[mysqld]下面加上如下代码:

log-slow-queries=/data/mysqldata/slowquery.log

long_query_time=2

注意:log-slow-queries = F:/MySQL/log/mysqlslowquery.log为慢查询日志存放的位置,一般这个目录要有MySQL的运行帐号的可写权限,一般都将这个目录设置为MySQL的数据存放目录.

long_query_time=2中的2表示查询超过两秒才记录；

配置好上述几个参数后,我们就可以监视日志,然后着手进行解决了,如下通过 EXPLAIN 分析低效 SQL的执行计划:

通过以上步骤查询到效率低的SQL后，我们可以通过explain或者desc 获取MySQL如何执行SELECT语句的信息，包括select语句执行过程表如何连接和连接的次序。

explain可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT,代码如下:

1. mysql> explain select sum(moneys) from sales a,companys b where a.company_id = 
2. b.id and a.year = 2006; 
3. +----------------+----------+-----------+----------------+----------------+--------- 
4. -+-----------+----------------+ 
5. | select_type| table | type | possible_keys| key| key_len|rows 
6. | Extra| 
7. +----------------+----------+-----------+----------------+----------------+--------- 
8. -+-----------+----------------+ 
9. | SIMPLE| b| index | PRIMARY| PRIMARY| 4|1 | Using index 
10. | 
11. | SIMPLE| a| ALL| NULL| NULL| NULL |12 | Using where 
12. | 
13. +----------------+----------+-----------+----------------+----------------+--------- 
14. -+-----------+----------------+ 
15. 2 rows in set (0.02 sec) 

select_type:select 类型

table:输出结果集的表

type:表示表的连接类型

当表中仅有一行是type的值为system是最佳的连接类型；

当select操作中使用索引进行表连接时type的值为ref；

当select的表连接没有使用索引时,经常会看到type的值为ALL,表示对该表进行了全表扫描,这时需要考虑通过创建索引来提高表连接的效率.

possible_keys:表示查询时,可以使用的索引列.

key:表示使用的索引

key_len:索引长度

rows:扫描范围

Extra:执行情况的说明和描述

确定问题,并采取相应的优化措施,经过以上步骤,基本可以确认问题出现的原因,可以根据情况采取相应的措施,进行优化提高执行的效率.

例如上面的例子,我们确认是对a表的全表扫描导致效率的不理想,我们对a表的year字段创建了索引,查询需要扫描的行数明显较少,代码如下:

1. mysql> explain select sum(moneys) from sales a,companys b where a.company_id = 
2. b.id and a.year = 2006; 
3. +----------------+----------+-----------+----------------+----------------+--------- 
4. -+-----------+----------------+ 
5. | select_type| table | type | possible_keys| key| key_len|rows 
6. | Extra| 
7. +----------------+----------+-----------+----------------+----------------+--------- 
8. -+-----------+----------------+ 
9. | SIMPLE| b| index | PRIMARY| PRIMARY| 4|1 | Using index 
10. | 
11. | SIMPLE| a| ref| year| year| 4|3 |Using 
12. where|   //phpfensi.com 
13. +----------------+----------+-----------+----------------+----------------+--------- 
14. -+-----------+----------------+ 
15. 2 rows in set (0.02 sec)