MySQL执行计划
explain查看执行计划
在 MySQL 中如何知道一条 sql 到底有没有用到索引呢？MySQL 提供了 explain 关键字来查询一条 sql 的执行效率。
比如我们创建测试表t_user_info、t_user_order：
drop TABLE if exists `t_user_info` ;
CREATE TABLE `t_user_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`age` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_userId` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into t_user_info values (1,1,10,'jack'),(2,2,12,'tom'),(3,3,12,'mock'),(4,4,12,'test');
drop TABLE if exists `t_user_order` ;
CREATE TABLE `t_user_order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`amount` int(11) NOT NULL,
`order_desc` varchar(255) NOT NULL,
`create_time` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `idx_orderId` (`order_id`) ,
KEY `idx_userId` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into t_user_order (id,order_id,user_id,amount,order_desc) values (1,1,1,10,'订单1'),(2,2,1,10,'订单2'),(3,3,3,10,'订单3'),(4,4,3,10,'订单4');
查询下面 sql 的查询效率：
explain select * from t_user_info where id = 3;
执行计划各个字段的含义如下：
| 列名 | 含义 |
|---|---|
| id | 执行序号，id 列的编号是 select 的序列号，有几个 select 就有几个 id，并且 id 的顺序是按select 出现的顺序增长的。id 越大执行优先级越高，id 相同则从上往下执行，id 为 NULL 最后执行。 |
| select_type | 查询类型：SIMPLE: 简单查询PRIMARY: 外层查询SUBQUERY: 子查询DERIVED: 派生查询（FROM 中包含的子查询）UNION: UNION 中第二个或后面的那个查询UNION RESULT: UNION 的结果 |
| table | 引用的表 |
| partitions | 所属分区 |
| type | 访问类型官方文档，常见访问类型：system : 只有一条记录的表（=系统表）const : 通过索引一次就查询到eq_ref : 唯一索引等值扫描ref : 非唯一索引等值扫描range : 范围索引扫描index : 索引扫描all : 全表扫描 |
| possible_keys | 可能使用的索引（优化前） |
| key | 实际使用的索引（优化后） |
| key_len | 使用索引的长度，通过这个值可以估算出具体使用了索引中的哪些列。 |
| ref | 上述表的连接匹配条件（哪些列或常量被用于查找索引列上的值） |
| rows | 必须扫描的行数 |
| Extra | 附加信息官方文档，常见附加信息：Using filesort : mysql 无法利用索引完成排序操作Using temporary : 使用了临时表保存中间结果Using index : select 操作使用了覆盖索引Using where : 使用 where 过滤using join buffer : 使用了连接缓存impossible where : where 子句的值总是 false，不能用来获取任何记录distinct : 优化 distinct，在找到第一个匹配的记录后停止扫描同样值的动作 |
针对上面几个字段，来补充些详细解释：
id
执行序号，id 列的编号是 select 的序列号，有几个 select 就有几个 id。
下面举例一个联表查询：
explain select uo.* from t_user_info ui left join t_user_order uo on ui.user_id=uo.user_id where ui.user_id = 3;
可见有多条记录，但id均为1:
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优化的套路：
- 当不确定一条 sql 查询效率的时候 就可以通过 explain sql 来查看
- 执行sql时（不仅限于explain语句），如果有返回告警提示，可接着执行 show warnings 以查看详细提示
- 根据执行计划，结合相关的表结构和索引信息，思考可能的优化点
- 按照可能的优化点执行表结构变更、增加索引、SQL改写等操作
- 查看优化后的执行时间和执行计划
- 如果优化效果不明显，重复第3步操作
