MySQL SQL优化之执行计划 explain

最新推荐文章于 2024-10-28 13:11:45 发布

原创

最新推荐文章于 2024-10-28 13:11:45 发布 · 2.1k 阅读

1 ·

CC 4.0 BY-SA版权

本文介绍了MySQL SQL优化中查看执行计划的重要性和方法。通过`EXPLAIN`和`EXTENDED`关键字，我们可以分析SQL的执行过程，了解是否需要添加索引以及优化查询。文中展示了不同类型的查询输出，解释了执行计划的各列信息，强调了选择明确的列名以减少内存消耗和数据量。此外，还提到了MySQL 5.6.3以后支持在不同类型的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也可以，结果一样。

有一个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列表里一定要写明所选列的列名，尤其当表中列特别多时更应写出列名，只选要查看的列。

执行计划的解释可以参与这里：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`.