Mysql explain执行计划详解

explain可以查看sql的执行计划，根据执行计划，可以看出sql慢在哪，方便进行调优。

explain中比较重要的几个字段：type、key、key_len、rows、filtered、Extra。以下列举比较常见的情况

type

type取值，一般而言从上到下性能依次降低

• system
  • 表中只有一条数据的情况
• const
  • 查询条件只有一个值，通过主键或者唯一索引精确匹配一条记录
• eq_ref
  • 发生在链表查询中
  • 使用非唯一索引进行关联
• ref
  • 连表、不连表都可能发生
  • 不连表：使用一个值通过非唯一索引进行查询
  • 连表：使用非唯一索引进行关联
• range
  • 使用索引进行范围查询（大于、大于等于、小于、小于等于、in）
• index
  • 对辅助索引树进行全量扫描
  • 建议优化掉
• ALL
  • 对主键索引树进行全量扫描
  • 一定要优化掉

key

• 具体使用的索引

key_len

• 使用了索引的长度
• 可以根据这个参数来减小索引长度
  • 索引长度减小后，一个页就能装更多的索引数据，能减少IO次数，从而提高性能

rows/filtered

• rows: 扫描了多少行
• filtered: 返回的行数，占扫描行数的百分比，是最直观的指标，越大越好

Extra

• Using index
  • 发生了索引覆盖，不回表
• Using where; Using index
  • 发生了索引覆盖，通过1个索引值查出来的数据有多余的，需要通过where上的其他条件对索引树进行过滤。
  • 发生索引覆盖，不回表
• Using index condition
  • 发生回表
  • 在索引树上使用where条件对索引树进行筛选
• Using index condition; Using where
  • 发生回表
  • 在索引树上使用where条件对索引树进行过滤
  • 同时还要在主键索引的叶子节点上使用where条件对数据进行筛选
• Backward index scan
  • 通过索引反向扫描，来实现排序
• Using filesort
  • 不能通过索引达到排序效果，使用了外部排序
  • 一般有这个标识的查询，对CPU消耗比较高
• Using temporary
  • 使用了临时表
  • 一般出现在排序、分组、连表的场景
• Using where
  • 使用索引查询后，还需要使用其他条件进行筛选数据

type和Extra的一些组合

数据准备

CREATE TABLE `test` (
  `a` int NOT NULL AUTO_INCREMENT,
  `b` int DEFAULT NULL,
  `c` int DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;
insert into test (a,b,c) values (1,1,1), (2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7);

CREATE TABLE `test_explain` (
  `id` int NOT NULL AUTO_INCREMENT,
  `index_unique` int DEFAULT null,
  `index` int DEFAULT NULL,
  `index_joint_1` int default null,
  `index_joint_2` int default null,
  `index_joint_3` int default null,
  `no_index_1` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  unique key `index_unique` (`index_unique`),
  key `index` (`index`),
  key `idx_joint` (`index_joint_1`, `index_joint_2`, `index_joint_3`)
) ENGINE=InnoDB;
insert into test_explain (id, index_unique, `index`, index_joint_1, index_joint_2, index_joint_3, no_index_1)
VALUES (1, 1, 1, 1,1,1,1), (2,2,2,2,1,1,2),(3,3,3,3,1,2,3), (4,4,4,4,1,2,4), (5,5,5,5,1,3,5), (6,6,6,6,6,6,6),(7,7,7,7,7,7,7);

• 使用一个值，通过主键、唯一索引精确匹配一条记录

-- 使用一个值，通过主键、唯一索引精确匹配一条记录
-- type=const, Extra=
explain select * from test_explain where id = 1;
-- type=const, Extra=
explain select * from test_explain where id = 1 and index_unique = 1;

-- type=const, Extra=
explain select * from test_explain where id = 1 and `index` = 1;
-- type=const, Extra=
explain select * from test_explain where index_unique = 1;

• 使用一个值，通过主键、唯一索引精确匹配一条记录，发生索引覆盖，不回表

-- 使用一个值，通过主键、唯一索引精确匹配一条记录，发生索引覆盖，不回表
-- type=const, Extra=Using index
explain select index_unique from test_explain where index_unique = 1;

• 连表查询， 使用主键或者唯一键进行关联

-- 连表查询， 使用主键或者唯一键进行关联
-- type=ALL, type=eq_ref
explain select * from test  t left join test_explain te on te.index_unique = t.b;
-- type=ALL, type=eq_ref
explain select * from test  t, test_explain te where te.index_unique = t.a;
-- type=ALL, type=eq_ref
explain select * from test  t left join test_explain te on te.id = t.b;
-- type=ALL, type=eq_ref
explain select * from test  t, test_explain te where te.id = t.a;
-- type=ALL, type=eq_ref
explain select * from test  t left join test_explain te on te.index_unique = t.b;

•  连表查询， 使用非唯一键进行关联

-- 连表查询， 使用非唯一键进行关联
-- type=ALL, type=ref
explain select * from test  t left join test_explain te on te.`index` = t.b;
-- type=ALL, type=ref
explain select * from test  t left join test_explain te on te.`index_joint_1` = t.b;
-- type=ALL, type=ref
explain select * from test  t, test_explain te where te.index = t.a;
-- type=ALL, type=ref
explain select * from test  t, test_explain te where te.index_joint_1 = t.a;

•  使用一个值，通过非唯一索引匹配一条或多条记录

-- 使用一个值，通过非唯一索引匹配一条或多条记录
-- type=ref, Extra=
explain select * from test_explain where `index_joint_1` = 7;
-- type=ref, Extra=
explain select * from test_explain where `index` = 7;
-- type=ref, Extra=Using index
explain select index_joint_1 from test_explain where `index_joint_1` = 1;
-- type=ref, Extra=Using index
explain select `index` from test_explain where `index` = 1;

• 使用索引进行范围查询，在主键索引树上过滤数据

-- 使用索引进行范围查询，在主键索引树上过滤数据
-- type=range, Extra=Using where
explain select * from test_explain where id in (1,2);

• 通过索引进行范围查询，用where过滤索引，发生索引覆盖，不回表

-- 通过索引进行范围查询，用where过滤索引，发生索引覆盖，不回表
-- type=range, Extra=Using where; Using index
explain select index_unique from test_explain where index_unique<> 1;
-- type=range, Extra=Using where; Using index
explain select `index` from test_explain where `index`<> 1;
-- type=range, Extra=Using where; Using index
explain select index_unique from test_explain where index_unique in (1,2);
-- type=range, Extra=Using where; Using index
explain select index_unique from test_explain where index_unique>1 and index_unique < 4;
-- type=range, Extra=Using where; Using index
explain select index_joint_2 from test_explain where `index_joint_1` in (1,2);
-- type=range, Extra=Using where; Using index
explain select index_joint_1 from test_explain where `index_joint_1` < 4;

•  通过索引进行范围查询，用where过滤索引，发生回表

-- 通过索引进行范围查询，用where过滤索引，发生回表
-- type=range, Extra=Using index condition
explain select * from test_explain where index_unique in (1,2);
-- type=range, Extra=Using index condition
explain select * from test_explain where index_unique in (1,7);
-- type=range, Extra=Using index condition
explain select * from test_explain where index_unique>1 and index_unique < 4;
-- type=range, Extra=Using index condition
explain select * from test_explain where `index`<> 1;
-- type=range, Extra=Using index condition
explain select * from test_explain where `index` in (1,2);
-- type=range, Extra=Using index condition
explain select * from test_explain where `index_joint_1` in (1,2);
-- type=range, Extra=Using index condition
explain select * from test_explain where `index_joint_1` < 4;

• 通过索引进行范围查询，用where过滤索引，发生回表，用where过滤数据

-- 通过索引进行范围查询，用where过滤索引，发生回表，用where过滤数据
-- type=range, Extra=Using index condition; Using where
explain select * from test_explain where index_unique > 1 and `index` < 7;
-- type=range, Extra=Using index condition; Using where
explain select * from test_explain where `index_joint_1` < 4 and no_index_1 > 1;
-- type=range, Extra=Using index condition; Using where
explain select * from test_explain where `index_joint_1` < 4 and `index` > 1;

• 通过索引进行范围查询，用where过滤索引，发生回表，用where过滤数据，使用索引进行排序

-- 通过索引进行范围查询，用where过滤索引，发生回表，用where过滤数据，使用索引进行排序
-- type=range, Extra=Using index condition; Using where
explain select * from test_explain where `index_joint_1` < 4 and no_index_1 > 1 order by index_joint_1 asc;

• 通过索引进行范围查询，用where过滤索引，发生回表，用where过滤数据，使用索引进行逆序排序

-- 通过索引进行范围查询，用where过滤索引，发生回表，用where过滤数据，使用索引进行逆序排序
-- type=range, Extra=Using index condition; Using where; Backward index scan
explain select * from test_explain where `index_joint_1` < 4 and no_index_1 > 1 order by index_joint_1 desc;

• 通过索引进行范围查询，用where过滤索引，发生回表，用where过滤数据， 使用外部排序

-- 通过索引进行范围查询，用where过滤索引，发生回表，用where过滤数据， 使用外部排序
-- type=range, Extra=Using index condition; Using where; Using filesort
explain select * from test_explain where `index_joint_1` < 4 and no_index_1 > 1 order by index_joint_2 asc;
-- type=range, Extra=Using index condition; Using where; Using filesort
explain select * from test_explain where `index_joint_1` < 4 and no_index_1 > 1 order by `index` asc;

• 全量扫描索引树，发生索引覆盖，不回表

-- 全量扫描索引树，发生索引覆盖，不回表
-- type=index, Extra=Using index
explain select index_unique from test_explain;

• 全量扫描索引树，在索引树上过滤数据，发生索引覆盖，不回表

-- 全量扫描索引树，在索引树上过滤数据，发生索引覆盖，不回表
-- type=index, Extra=Using where; Using index
explain select index_joint_1 from test_explain where `index_joint_3` = 1;