MySQL执行计划

explain查看执行计划

在 MySQL 中如何知道一条 sql 到底有没有用到索引呢？MySQL 提供了 explain 关键字来查询一条 sql 的执行效率。
比如我们有一张 user 表：

CREATE TABLE `user` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `userId` int(11) NOT NULL,  
  `age` int(11) NOT NULL,  
  `name` varchar(255) NOT NULL,  
  PRIMARY KEY (`id`),  
  KEY `idx_userId` (`userId`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询下面 sql 的查询效率：

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

执行计划各个字段的含义如下：

这么多字段我们挑几个重点来解释一下：

id

执行序号，id 列的编号是 select 的序列号，有几个 select 就有几个 id，并且 id 的顺序是按select 出现的顺序增长的。id 越大执行优先级越高，id 相同则从上往下执行，id 为 NULL 最后执行。

key_len

key_len 长度表示在索引里使用的字节数，通过这个值可以估算出具体使用了索引中的哪些列。

ken_len 计算规则如下：

• 字符串 ：char(n)：n 字节长度； varchar(n)：n 字节存储字符串长度，如果是 utf-8， 则长度是 3n+2，这里的长度与字符集有直接关系；
• 数值类型：tinyint：1 字节；smallint：2 字节 ；int：4 字节； bigint：8字节；
• 时间类型 ：date：3字节；timestamp：4字节；datetime：8字节。

如果字段允许为 NULL，需要 1 字节记录是否为 NULL； 索引最大长度是 768 字节，当字符串过长时，MySQL 会做一个类似做前缀索引的处理，将前半部分的字符串提取出来做索引。

type

type 显示的是访问类型，结果值从好到坏依次是：

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

一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

下面对这几个类型简要说明：

• system
  该表只有一行（如：系统表）。这是 const 连接类型的特例。
• const
  该表最多只有一个匹配行，在整个查询过程中这个表最多只会有一条匹配的行，用到了 primary key 或者unique 索引。
  比如主键查询肯定只有一条记录被匹配到。
• eq_ref
  对于前面表格中的每个行组合，从该表中读取一行。除了 system 和 const 类型之外，这是最好的连接类型。当连接使用索引的所有部分且索引是 索引 PRIMARY KEY 或 UNIQUE NOT NULL 索引时使用它。
  SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
• ref
  表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
• fulltext
  使用 FULLTEXT 索引执行连接。
• ref_or_null
  该连接类型如同 ref，但是添加了 MySQL 可以专门搜索包含 NULL 值的行。
  SELECT * FROM ref_table WHERE key_column IS NULL;
• index_merge
  该连接类型表示使用了索引合并优化方法。
  SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20; SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;
• unique_subquery
  此类型替换 以下形式的 eq_ref 某些 IN子查询：
  value IN (SELECT primary_key FROM single_table WHERE some_expr)
• index_subquery
  此连接类型类似于 unique_subquery。它替换 IN 子查询，但它适用于以下形式的子查询中的非唯一索引：
  value IN (SELECT key_column FROM single_table WHERE some_expr)
• range
  给定范围内的检索，使用一个索引来检查行。通常发生在在索引列上使用范围查询，如 >，<，in 等时，非索引列是 ALL。
• index
  按索引次序扫描，先读索引，再读实际的行，结果也是全表扫描，主要优点是避免了排序。（索引是排好序的，并且 all 是从硬盘中读的，index 可能不在硬盘上。s
• ALL
  对前面表格中的每个行组合进行全表扫描。如果表是第一个未标记的表 const，通常不好，并且在所有其他情况下通常 非常糟糕。通常，您可以ALL通过添加基于常量值或早期表中的列值从表中启用行检索的索引来避免

row

这一列是 MYSQL 估计要读取并检测的行数，注意这个不是结果集的行数。

Extra

Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示 MySQL 在查询过程中的一些详细信息。

• Distinct：MySQL 发现第 1 个匹配行后，停止为当前的行组合搜索更多的行。
• Not exists：MySQL 能够对查询进行 LEFT JOIN 优化，发现1个匹配 LEFT JOIN 标准的行后，不再为前面的的行组合在该表内检查更多的行。
• range checked for each record：MySQL 没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知。可能部分索引可以使用。
• Using filesort：看到这个的时候，查询就需要优化了。MySQL 需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
• Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
• Using temporary：为了解决查询，MySQL 需要创建一个临时表来容纳结果。看到这个就需要进行优化了，这通常发生在对不同的列集进行 order by 上，而不是 group by 上。
• Using where：WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
• Using sort_union| Using union|Using intersect：这些函数说明如何为 index_merge 联接类型合并索引扫描。
• Using index for group-by：类似于访问表的 Using index 方式，Using index for group-by 表示 MySQL 发现了一个索引，可以用来查询 GROUP BY 或 DISTINCT 查询的所有列，而不要额外搜索硬盘访问实际的表。

了解了执行计划，当你不确定一条 sql 查询效率的时候 就可以使用 Explain 来查看。

最后给大家总结一下SQL优化的套路：

• 查看执行计划 explain sql
• 如果有告警信息，查看告警信息 show warnings;
• 查看SQL涉及的表结构和索引信息
• 根据执行计划，思考可能的优化点
• 按照可能的优化点执行表结构变更、增加索引、SQL改写等操作
• 查看优化后的执行时间和执行计划
• 如果优化效果不明显，重复第四步操作