mysql查找执行效率慢的SQL语句

一些小技巧
1. 如何查出效率低的语句？
在MySQL下，在启动参数中设置 --log-slow-queries=[文件名]，就可以在指定的日志文件中记录执行时间超过long_query_time（缺省为10秒）的SQL语句。你也可以在启动配置文件中修改long query的时间，如：

    # Set long query time to 8 seconds
    long_query_time=8

2. 如何查询某表的索引？
可使用SHOW INDEX语句，如：

    SHOW INDEX FROM [表名]

3. 如何查询某条语句的索引使用情况？
可用EXPLAIN语句来看一下某条SELECT语句的索引使用情况。如果是UPDATE或DELETE语句，需要先转换为SELECT语句。
4. 如何把导出INNODB引擎的内容到错误日志文件中?
我们可以使用SHOW INNODB STATUS命令来查看INNODB引擎的很多有用的信息，如当前进程、事务、外键错误、死锁问题和其它一些统计数据。如何让该信息能记录在日志文件中呢？只要使用如下语句创建innodb_monitor表，MySQL就会每15秒钟把该系统写入到错误日志文件中：

    CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

如果你不再需要导出到错误日志文件，只要删除该表即可：

    DROP TABLE innodb_monitor;

5. 如何定期删除庞大的日志文件？
只要在启动配置文件中设置日志过期时间即可：

    expire_logs_days=10

经验教训
1. 重点关注索引
下面以表TSK_TASK表为例说明SQL语句优化过程。TSK_TASK表用于保存系统监测任务，相关字段及索引如下：

    ID：主键；
    MON_TIME：监测时间；建了索引；
    STATUS_ID：任务状态；与SYS_HIER_INFO.ID建立了外键关系。


注MySQL自动会为外键建立索引，在本次优化过程中，发现这些自动建立的外键索引会对SQL语句的效率产生不必要的干扰，需要特别注意！

首先，我们在日志文件中查到下面语句的执行比较慢，超过10秒了：

    # Query_time: 18 Lock_time: 0 Rows_sent: 295 Rows_examined: 88143
    select * from TSK_TASK WHERE STATUS_ID = 1064 and MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23';


哦，原来在88143条记录中要查出符合条件的295条记录，那当然慢了。赶紧用EXPLAIN语句看一下索引使用情况吧：

    +----+-------------+----------+------+----------------------------------------------------------+------------------------------------+---------+-------+--------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+----------+------+----------------------------------------------------------+------------------------------------+---------+-------+--------+-------------+
    | 1 | SIMPLE | TSK_TASK | ref | FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME | FK_task_status_id_TO_SYS_HIER_INFO | 9 | const | 276168 | Using where |
    +----+-------------+----------+------+----------------------------------------------------------+------------------------------------+---------+-------+--------+-------------+


可以看出，有两个索引可用FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME，而最终执行语句时采用了STATUS_ID上的外键索引。

再看一下TSK_TASK表的索引情况吧：

    +----------+------------------------------------+-------------+-------------+
    | Table | Key_name | Column_name | Cardinality |
    +----------+------------+------------------------------------+--------------+
    | TSK_TASK | PRIMARY | ID | 999149 |
    | TSK_TASK | FK_task_status_id_TO_SYS_HIER_INFO | STATUS_ID | 16 |
    | TSK_TASK | TSK_TASK_KEY_MON_TIME | MON_TIME | 13502 |
    +----------+------------------------------------+-------------+-----------+--


在Oracle或其他关系数据库下，WHERE条件中的字段顺序对索引的选择起着很重要的作用。我们调整一下字段顺序，把STATUS_ID放在后面，再EXPLAIN一下：

    EXPLAIN select * from TSK_TASK WHERE MON_TIME >= '2007-11-22' and MON_TIME < '2007-11-23' and STATUS_ID = 1064;


但是没什么效果，MySQL还是选用系统建立的STATUS_ID外键索引。

仔细分析一下，看来Cardinality属性（即索引中的唯一值的个数）对索引的选择起了极其重要的作用，MySQL选择了索引值唯一值个数小的那个索引作为整条语句的索引。

针对这条语句，如果使用FK_task_status_id_TO_SYS_HIER_INFO做索引，而TSK_TASK表中存放很多天数据的话，那扫描的记录数会很多，速度较慢。可以有以下几个优化方案：

    如果一天的任务数不多的话，我们删除索引FK_task_status_id_TO_SYS_HIER_INFO，那MySQL会使用索引TSK_TASK_KEY_MON_TIME，然后在该天的数据中在扫描STATUS_ID为1064的记录，那速度也不慢；
    如果一天的任务数多的话，我们需删除索引FK_task_status_id_TO_SYS_HIER_INFO和TSK_TASK_KEY_MON_TIME，然后再建立STATUS_ID,MON_TIME的联合索引，这样效率肯定会很高。


因此建议，对那些记录数多的表，建议不要使用外键，以避免造成性能效率的严重降低。
2. 尽量控制每张表的记录数
当一张表的记录数很大时，管理和维护就会很麻烦，如索引维护就会占用很长时间，从而会给系统的正常运行造成很大的干扰。

对随时间推移数据量不断增长的表，我们可以根据时间来区分实时数据和历史数据，可以使用后台服务程序定期移动实时表中的数据到历史表中，从而控制实时表的记录数，提高查询和操作效率。但注意每次移动的时间要足够短，不要影响正常程序的数据写入。如果占用时间太长，可能会造成死锁问题。
3. 数据散列(partition)策略
当客户数达到一定规模后，单个数据库将无法支撑更高的并发访问，此时可以考虑把客户数据散列(partition)到多个数据库中，以分担负载，提高系统的整体性能与效率。

数据散列可以考虑采用federeated或sharded方式，网上有不少这方面的资料。

 

Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效 果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。

Explain语法：explain select … from … [where ...]

例如：explain select * from news;

输出：

+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+


下面对各个属性进行了解：

1、id：这是SELECT的查询序列号

2、select_type：select_type就是select的类型，可以有以下几种：

SIMPLE：简单SELECT(不使用UNION或子查询等)

PRIMARY：最外面的SELECT

UNION：UNION中的第二个或后面的SELECT语句

DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询

UNION RESULT：UNION的结果。

SUBQUERY：子查询中的第一个SELECT

DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询

DERIVED：导出表的SELECT(FROM子句的子查询)

3、table：显示这一行的数据是关于哪张表的

4、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。

结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行

6、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好

8、ref：显示使用哪个列或常数与key一起从表中选择行。

9、rows：显示MySQL认为它执行查询时必须检查的行数。

10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。

Distinct
一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists
MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，

就不再搜索了

Range checked for each

Record（index map:#）
没有找到理想的索引，因此对于从前面表中来的每一 个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort
看 到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来 排序全部行

Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表 的全部的请求列都是同一个索引的部分的时候

Using temporary
看到这个的时候，查询需要优化了。这 里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index， 这就会发生，或者是查询有问题

其他一些Tip：

1. 当type 显示为 “index” 时，并且Extra显示为“Using Index”， 表明使用了覆盖索引。