MySQL SQL优化之 执行计划 explain

1.环境

OS: CentOS 6.5 x64

MySQL: 5.6 for Linux (x86_64)

本例中用到的表，可以参考MySQL 库 和 样例表 创建脚本

2.优化第一步

拿到一个慢SQL时，第一步就是看执行计划并权衡是否可以加索引，就是这么简单，不要被高深莫测的人给蒙住说什么有更好的方法，告诉各位同学：没有更好的方法，看执行计划和权衡加索引就是最好的方法。然后才是考虑各种别的优化方案。

3.SQL优化注意几点

1).注意函数调用的次数，避免每行都调用一次

2).避免全表扫描，尤其是大表

3).定期执行Analyze Table

4).熟悉各个引擎的调优技术、索引技术和配置参数。主要引擎是MyISAM、InnoDB、MEMORY。

5).如果一个SQL太复杂，就拆分成一块一块地优化

6).调内存

7).注意锁

4.执行计划 EXPLAIN

要使用执行计划，首先要读懂执行计划，然后通过改写SQL和索引技术来改进执行计划。

MySQL5.6.3之前只有 SELECT 可以生成执行计划，5.6.3及之后的版本SELECT DELETE INSERT REPLACE UPDATE都可以生成执行计划。

explain语法：

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    explainable_stmt

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

看到了吧，查看执行计划不只explain命令，desc也可以，结果一样。

mysql> desc select * from p_range where id=12;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | p_range | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)


mysql> desc extended select * from p_range where id=12;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows |filtered | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | p_range | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

有一个warning，可以看看

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                  |
+-------+------+------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '12' AS `id`,'员工JONES' AS `name` from `test`.`p_range` where 1   |
+-------+------+------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
警告信息显示优化器优化后执行的SQL。再看一个复杂点的：

mysql> desc extended select * from emp where deptno in (select deptno from dept where deptno=20);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | dept  | const | PRIMARY       | PRIMARY | 1       | const |    1 |   100.00 | Using index |
|  1 | SIMPLE      | emp   | ALL   | NULL          | NULL    | NULL    | NULL  |   14 |   100.00 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)


mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                 |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`emp`.`empno` AS `empno`,`test`.`emp`.`ename` AS `ename`,`test`.`emp`.`job` AS `job`,`test`.`emp`.`mgr` AS `mgr`,`test`.`emp`.`hiredate` AS `hiredate`,`test`.`emp`.`sal` AS `sal`,`test`.`emp`.`comm` AS `comm`,`test`.`emp`.`deptno` AS `deptno` from `test`.`dept` join `test`.`emp` where (`test`.`emp`.`deptno` = 20) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

从警告里可以看出优化器最终将*替换成所有的列名，这不但增加了sql文本的长度占用更多内存，还会使返回的数据量增大，所以在select列表里一定要写明所选列的列名，尤其当表中列特别多时更应写出列名，只选要查看的列。

mysql> desc partitions select * from p_range where id=12;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | p_range | p0         | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

执行计划的解释可以参与这里：http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

以下摘录一部分:

EXPLAIN Output Columns

This section describes the output columns produced by EXPLAIN. Later sections provide additional information about the type and Extra columns.

Each output row from EXPLAIN provides information about one table. Each row contains the values summarized in Table 8.1, “EXPLAIN Output Columns”, and described in more detail following the table. Column names are shown in the table's first column; the second column provides the equivalent property name shown in the output when FORMAT=JSON is used.

Table 8.1 EXPLAIN Output Columns

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

Note

JSON properties which are NULL are not displayed in JSON-formatted EXPLAIN output.

•  id (JSON name: select_id)

  The SELECT identifier. This is the sequential number of the SELECT within the query. The value can be NULL if the row refers to the union result of other rows. In this case, the table column shows a value like <unionM,N> to indicate that the row refers to the union of the rows with id values of M and N.

•  select_type (JSON name: none)

  The type of SELECT, which can be any of those shown in the following table. A JSON-formatted EXPLAIN exposes the SELECT type as a property of aquery_block, unless it is SIMPLE or PRIMARY. The JSON names (where applicable) are also shown in the table.

  select_type Value	JSON Name	Meaning
  SIMPLE	None	Simple SELECT (not using UNION or subqueries)
  PRIMARY	None	Outermost SELECT
  UNION	None	Second or later SELECT statement in a UNION
  DEPENDENT UNION	dependent (true)	Second or later SELECT statement in a UNION, dependent on outer query
  UNION RESULT	union_result	Result of a UNION.
  SUBQUERY	None	First