Mysql查询语句优化一则

   最近一直忙于开发业务系统，数据库从原来的Oracle被替换成了Mysql，但在实际线上运行中发现有条sql执行起来非常慢，更奇怪的是这句sql还会导致整个数据库性能下降。这个问题非常严重！该sql和表结构如下：

1. SELECT name 
2.        ,COUNT(*) AS counts 
3.        ,type 
4. FROM  entityNameTemp 
5. WHERE postTime > '2011-06-01 00:00:00' 
6. GROUP BY name 
7. ORDER BY counts DESC 
8. LIMIT  10 

1. | entityNameTemp | CREATE TABLE `entityNameTemp` ( 
2.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', 
3.   `name` varchar(600) DEFAULT NULL COMMENT '人名或者机构名', 
4.   `type` enum('personName','organizationName') DEFAULT NULL COMMENT 'personName 人名；organizationName:机构名', 
5.   `postTime` timestamp NULL DEFAULT NULL COMMENT '发帖时间', 
6.   `createTime` timestamp NULL DEFAULT NULL COMMENT '创建时间', 
7.   PRIMARY KEY (`id`) 
8. ) ENGINE=Innodb AUTO_INCREMENT=1931915 DEFAULT CHARSET=utf8                        | 

    这是对一个百万级别的临时表，目的是做一下统计取排名前十的数据。先来查看一下执行计划：

1. +----+-------------+----------------+------+---------------+------+---------+------+---------+----------------------------------------------+ 
2. | id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows    | Extra                                        | 
3. +----+-------------+----------------+------+---------------+------+---------+------+---------+----------------------------------------------+ 
4. |  1 | SIMPLE      | entityNameTemp | ALL  | NULL          | NULL | NULL    | NULL | 1735829 | Using where; Using temporary; Using filesort | 
5. +----+-------------+----------------+------+---------------+------+---------+------+---------+----------------------------------------------+ 

    无疑是用到了临时表以及排序，且没有用上索引。但mysql的执行计划实在很难定位具体问题。之前有查过mysql对临时文件的使用规则，主体思路是查看tmp_table_size参数，mysql会评估一下本次查询大概会需要用到的内存大小，如果小于该参数则会使用磁盘临时文件。但这个参数我已经改到了200m，但问题依旧。查看了一下，发现设置了参数但仍然使用了磁盘。查询发现Created_tmp_disk_tables参数在sql语句执行前后增加了1：

1. mysql> show status like  '%tmp%'; 
2. +-------------------------+-------+ 
3. | Variable_name           | Value | 
4. +-------------------------+-------+ 
5. | Created_tmp_disk_tables | 2     | 
6. | Created_tmp_files       | 15    | 
7. | Created_tmp_tables      | 7     | 
8. +-------------------------+-------+ 

    这就非常奇怪了，因为我手动计算发现这些数据量绝对不会超过200m，理论上是应该要用内存临时表的。这只好拿出杀手锏，查看详细的执行计划。在命令行下依次执行：
    1) set profiling = 1; #启动分析功能
    2) #sql语句执行； 
    3) show profile; #就可以看到详细的时间消耗，另外可以用show profiles查看执行过的sql语句； 
    4) show profiles; #就可以看到刚才所有查询的执行时间。

    当前sql语句执行情况分析如下：

1. mysql> show profile; 
2. +--------------------------------+------------+ 
3. | Status                         | Duration   | 
4. +--------------------------------+------------+ 
5. | starting                       |   0.000023 | 
6. | checking query cache for query |   0.000069 | 
7. | Opening tables                 |   0.000016 | 
8. | System lock                    |   0.000008 | 
9. | Table lock                     |   0.000036 | 
10. | init                           |   0.000030 | 
11. | optimizing                     |   0.000011 | 
12. | statistics                     |   0.000018 | 
13. | preparing                      |   0.000014 | 
14. | Creating tmp table             |   0.000265 | 
15. | executing                      |   0.000008 | 
16. | Copying to tmp table           | 165.312749 | 
17. | Sorting result                 |   0.258847 | 
18. | Sending data                   |   0.000094 | 
19. | end                            |   0.000007 | 
20. | removing tmp table             |   0.302258 | 
21. | end                            |   0.000026 | 
22. | query end                      |   0.000007 | 
23. | freeing items                  |   0.000171 | 
24. | storing result in query cache  |   0.000017 | 
25. | logging slow query             |   0.000007 | 
26. | logging slow query             |   0.000006 | 
27. | cleaning up                    |   0.000008 | 
28. +--------------------------------+------------+ 

    从上可以清楚的看到时间消耗基本都花费在临时文件拷贝上了，对于排序其实还没花费多久。那问题的关键就是在于解决临时文件如何在内存中建立。

    简单商讨了一下，觉得还是先建立索引看看吧。针对这个查询条件应该建立postTime和name的联合索引。但执行时发现：

1. mysql> alter table entityNameTemp add key idx_postTime_name ( postTime, name ); 
2. ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes 

    这怎么会超过长度了呢？name字段应该很短才对，postTime还是一个时间字段更长不了。但是一检查发现居然建表的人写的name是varchar(600)。突然想到mysql读取时内存开辟是根据声明的长度来的，再一联想，mysql估计需要读取文件的大小就是根据字段声明来算出来的。果断修改name到varchar(20)，一执行就几秒了，再看一下详细时间消耗：

1. mysql> show profile; 
2. +--------------------------------+----------+ 
3. | Status                         | Duration | 
4. +--------------------------------+----------+ 
5. | starting                       | 0.000036 | 
6. | checking query cache for query | 0.000094 | 
7. | Opening tables                 | 0.000216 | 
8. | System lock                    | 0.000010 | 
9. | Table lock                     | 0.000038 | 
10. | init                           | 0.000038 | 
11. | optimizing                     | 0.000014 | 
12. | statistics                     | 0.000019 | 
13. | preparing                      | 0.000018 | 
14. | Creating tmp table             | 0.000040 | 
15. | executing                      | 0.000008 | 
16. | Copying to tmp table           | 3.863467 | 
17. | Sorting result                 | 0.092263 | 
18. | Sending data                   | 0.000061 | 
19. | end                            | 0.000006 | 
20. | removing tmp table             | 0.004514 | 
21. | end                            | 0.000009 | 
22. | query end                      | 0.000005 | 
23. | freeing items                  | 0.000035 | 
24. | storing result in query cache  | 0.000013 | 
25. | logging slow query             | 0.000005 | 
26. | cleaning up                    | 0.000005 | 
27. +--------------------------------+----------+ 

    问题基本算解决了，查看临时文件使用情况也确实使用了内存临时文件。加上索引试试，查看执行计划也用上索引了，但是实际执行效果来看提升效果不大。因为还是要拷贝到临时文件表，innodb对于count操作优化确实比较难。

    另外一个问题就是对整个系统的影响，这估计是因为用到了磁盘会导致io占用过高。现在查询时间比较短，现象比较难重现了。

本文转自passover 51CTO博客，原文链接：http://blog.51cto.com/passover/632493，如需转载请自行联系原作者