mysql之explain详解（分析索引最佳使用）

用法

explain的用法相当简洁，直接在sql语句前加上EXPLAIN 即可

-- 实际SQL，查找租户为测试的用户，并排序
 select * from uw_tenant where tenant_name like "%test%" order by tenant_name
-- 查看SQL是否使用索引，前面加上explain即可
explain select * from uw_tenant where tenant_name like "%test%" order by tenant_name

使用explain来优化Mysql的速度

使用explain之后会有以下元素id、select_type、table、type、possible_key、key、key_len、ref、rows、Extra 下面将一一对上面的元素进行分析。

id

id是标识为一个序号，也可以说是sql的执行顺序，序号越大越先执行，如果序号一样则按顺序执行

select_type

1. SIMPLE: 指示非子查询和uNIOn的简单查询。

   PRIMARY: 指示在有子查询的语句中最外面的select，主查询。

2. UNioN： 指示在使用union语句的第二个或后面的select。

3. DEPENDENT UNION: 子查询union语句的第二个或后面的select

4. UNION RESULT：子查询union语句的第二个或后面的select

5. SUBQUERY：子查询语句

6. DEPENDENT SUBQUERY：子查询中第一个语句。

7. DERIVED：派生表的SELECT(FROM子句的子查询)。

table

就是表名，本行记录对应查询所应用的表。可以通过table表名帮助定位查询。

使用explain时，有时table字段显示的并不是表名，而是 derived2 或 derived3 等等。。。 即 derived x 代表的是id为x的查询所得的结果集。

type

这列很重要,显示了连接使用了哪种类别,有无使用索引. 从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL ALL < index < range ~ index_merge < ref < eq_ref < const < system

1. system 这是const联接的一个特例，表仅有一行满足条件

2. const 表最多一个匹配行，他将在查询开始时被读取，因为仅有一行，在这行的列值可被优化器剩余部分任务是常数

3. eq_ref 对于每个来自前面表的行组合，从该表读取一行，它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。 eq_ref可以用于使用= 操作符比较的带索引的列。

4. ref 对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。

5. ref_or_null 该联接类型如同ref，但是添加了mysql可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化

6. index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

7. unique_subquery 该类型替换了下面形式的IN子查询的ref：

8. index_subquery 该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引 .range

9. 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range

10. index 该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

11. All 对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。

possible_keys

possible_keys列指出MySQL可能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

key

key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。 使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref

ref列显示使用哪个列或常数与key一起从表中选择行。

rows

rows列显示MySQL认为它执行查询时必须检查的行数。

Extra

该列包含MySQL解决查询的详细信息,下面详细.

1. Distinct 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

2. Not exists MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了

3. Range checked for each Record（index map:#） 没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

4. Using filesort 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.

5. Using index 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

6. Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDERBY上，而不是GROUP BY上

7. Using where 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题

索引最佳实践

1. 联合索引的全值匹配
2. 最左前缀原则（mysql会自动调整查询条件顺序）
3. 不要在索引列上做计算
4. 索引范围的后面的列不使用索引
5. 尽量不使用select*
6. ！=，<> 不能使用索引
7. is null，is not null不走索引
8. 通配符开头不能走索引
9. 字符串不加‘’，不走索引，明确字符和数字，不能更改类型查询
10. 少用or

优化实战

1. 最左前缀不要使用范围查询，否则索引失效，
2. 使用覆盖索引
3. 第一个索引失效，后面的索引都失效
4. 联合索引第一个字段不要用范围
5. order by 字段如果没有索引，则extra会出现useFilesort
6. 小表先执行，小表驱动大表

总结

在实际工作中可以多尝试使用explain去优化sql，特别是一些复杂一点的。这篇文章主要还是介绍explain的基本用法，后面应该会专门写两篇关于sql优化相关的文章。当然这种优化类的主要适用于开发人员，各位如果发现对你有帮助记得关注一下公众号：科比可比克。你的关注是我最大的动力。