使用Mysql EXPLAIN分析、优化SQL语句

以前对explain工具不是很了解，抽空学习了下。真的很强大。

首先分析下两条sql带来的性能问题。

开启profiling

set profiling=1;

34 | 0.01580100 | select sql_no_cache * from user left join user_ext on user.id = user_ext.id where user_ext.id <100 ORDER BY user_ext.id limit 10

35 | 0.04485000 | select sql_no_cache * from user left join user_ext on user.id = user_ext.id where user_ext.id <100 ORDER BY user.id limit 10 

看到因为排序导致同样的结果性能截然不同。

那么我们逐个看下他们的执行步骤：

mysql> show profile for query 34;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000146 |
| Opening tables     | 0.000011 |
| System lock        | 0.000004 |
| Table lock         | 0.000006 |
| init               | 0.000021 |
| optimizing         | 0.000013 |
| statistics         | 0.000040 |
| preparing          | 0.000014 |
| executing          | 0.000002 |
| Sorting result     | 0.015265 |
| Sending data       | 0.000063 |
| end                | 0.000005 |
| query end          | 0.000003 |
| freeing items      | 0.000201 |
| logging slow query | 0.000003 |
| cleaning up        | 0.000004 |
+--------------------+----------+
16 rows in set (0.00 sec)

mysql> show profile for query 35;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000118 |
| Opening tables       | 0.000012 |
| System lock          | 0.000004 |
| Table lock           | 0.000007 |
| init                 | 0.000022 |
| optimizing           | 0.000013 |
| statistics           | 0.000040 |
| preparing            | 0.000013 |
| Creating tmp table   | 0.000021 |
| executing            | 0.000002 |
| Copying to tmp table | 0.043937 |
| Sorting result       | 0.000080 |
| Sending data         | 0.000044 |
| end                  | 0.000004 |
| removing tmp table   | 0.000017 |
| end                  | 0.000007 |
| query end            | 0.000005 |
| freeing items        | 0.000475 |
| logging slow query   | 0.000022 |
| cleaning up          | 0.000007 |
+----------------------+----------+
20 rows in set (0.00 sec)

可以看到query 35明显多了几步操作。他们分别是Creating tmp table;Copying to tmp table;removing tmp table;end;

再看耗时 Copying to tmp table 明显是问题所在。

下面我们用explain分析下这两条语句。

mysql> explain select sql_no_cache * from user left join user_ext on user.id = user_ext.id where user_ext.id <100 ORDER BY user_ext.id limit 10 ;
+----+-------------+----------+--------+---------------+---------+---------+------------------+--------+------------------------------------------+
| id | select_type | table    | type   | possible_keys | key     | key_len | ref              | rows   | Extra                                    |
+----+-------------+----------+--------+---------------+---------+---------+------------------+--------+------------------------------------------+
|  1 | SIMPLE      | user_ext | index  | NULL          | name    | 38      | NULL             | 100000 | Using where; Using index; Using filesort |
|  1 | SIMPLE      | user     | eq_ref | PRIMARY       | PRIMARY | 4       | test.user_ext.id |      1 | Using index                              |
+----+-------------+----------+--------+---------------+---------+---------+------------------+--------+------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain select sql_no_cache * from user left join user_ext on user.id = user_ext.id where user.id <100 ORDER BY user.id limit 10 ;
+----+-------------+----------+-------+---------------+---------+---------+------+--------+-----------------------------------------------------------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows   | Extra                                                     |
+----+-------------+----------+-------+---------------+---------+---------+------+--------+-----------------------------------------------------------+
|  1 | SIMPLE      | user     | range | PRIMARY       | PRIMARY | 4       | NULL |    101 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | user_ext | index | NULL          | name    | 38      | NULL | 100000 | Using index                                               |
+----+-------------+----------+-------+---------------+---------+---------+------+--------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

我们看到Extra里，第二条语句有一个Using temporary。意味着将产生临时表。

－－－－－－－－－－－－－－－－－－－－－－－－ref－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

Extra列包含MySQL解决查询的详细信息,下面详细.

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

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

就不再搜索了

(3).Range checked for each

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

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

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

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

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

－－－－－－－－－－－－－－－－－－－－－－－－end ref－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

参考：

url:http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html