明明白白告诉你MySQL索引效率分析和使用原则

MySQL索引分析语句和使用

explain分析语句

EXPLAIN语句的基本语法如下:

EXPLAIN [EXTENDED] SELECT select_options

使用EXTENED关键字，EXPLAIN语句将产生附加信息。select_options 是SELECT语句的查询选项，包括FROM WHERE子句等。

执行该语句，可以分析EXPLAIN后面的SELECT语句的执行情况，并且能够分析出所查 询的表的一些特征。

使用EXPLAIN语句来分析1个查询语句，执行如下语句:

下面我们对查询结果进行解释：

字段	描述
id	select识别符。这是select的查询序列号，id的值越大优先级别越高，越先被执行，如果id相同，执行顺序右上至下
select_type	select_type表示select语句的类型。
table	type表示这一行的数据是关于哪张表的。
partitions
type	type表示的是表的连接类型。
possible_keys	possible_keys列支出mysql能使用哪个索引在该表中找到行。如果这列是null，则没有相关的索引。在这种情况下，可以通过检查where子句查看它是否引用某些列或者适合索引的列来提高查询性能。如果是这样，可以创建适合的索引来提高查询的性能
key	key表示查询实际应用到的索引，如果没有选择索引，该列的值是null。想要强制使mysql使用或者忽视possible_type列中的索引，在查询中使用force index，use index 或者 ignore index。
key_len	key_len表示mysql选择的索引字段按字节计算的长度，如果键是null，则长度为null，注意通过key_len值可以确定mysql将实际使用一个多列索引中的几个字段
ref	ref表示使用哪个列或常数与索引一起来查询记录
rows	rows显示mysql表中进行查询时必须检查的行数
filtered	通过查询条件获取的最终记录行数 占 通过type字段指明的搜索方式搜索出来的记录行数的百分比
Extra	extra显示mysql在处理查询时的详细信息。

性能判定依据主要字段信息不同值的解释：

select_type

(1) SIMPLE

SIMPLE表示简单查询，其中不包括连接查询和子查询。

(2) PRIMARY与SUBQUERY

PRIMARY表示主查询或者最外层的查询语句。SUBQUERY : 子查询

 (3) DERIVED： 衍生查询-在select出一批自定义列的数据，概念上相当于一张表

(4) UNION 与 UNION RESULT

UNION ：联合查询，union 后面的那张表就会表示成它

UNION RESULT:  联合结果

type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

Extar

Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，就不一个一个介绍了，在这只介绍常见的一些额外信息说明 .

1, Using filesort: 如果根据索引列进行排序（order by 索引列）是可以用到索引的，SQL查询引擎会先根据索引列进行排序，然后获取对应记录的主键id执行回表操作，如果排序字段用不到索引则只能在内存中或磁盘中进行排序操作，MySQL把这种在内存或者磁盘上进行排序的方式统称为文件排序（英文名：filesort），如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort

2, Using temporary: 许多查询的执行过程中，MySQL会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含distinct、group by、union等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示.

3, USING index: 表示相应的select操作中使用了覆盖索引（Covering Index）,避免回表操作，效率不错！

如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查找动作。

4, Using where: 使用了where过滤

5, using join buffer: 在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度

6, impossible where: where子句的值总是false，不能用来获取任何元组

7, select tables optimized away: 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

8, distinct: 优化distinct，在找到第一匹配的元组后即停止找同样值的工作

9, Using index condition：查找使用了索引，但是需要回表查询数据

MySQL索引效率分析

查询效率大概的顺序为：

USING index 查询效率最高

Using where;USING index; 次于覆盖索引,使用了where过滤和索引查询

Using where 使用了where过滤

Using index condition; 使用了索引，但是要回表查询数据

Using filesort | Using temporary (两个差不多同级，效率都不高)

关于 Using where 和 Using where;USING index 的效率比较：

我网上看到有人说Using where 比 Using where;USING index 效率要高，我不能理解，但作为求真精神，所以我自己进行压测。

sql语句

SELECT a.id,a.`host` FROM (select id,`host` FROM eju_static_url WHERE id >= 6053) a WHERE a.`host`='sh.zhilaowang.com';

压测语句

mysqlslap --no-defaults -h 127.0.0.1 -u root -p123456 -c 10 -i 1 --create-schema test -q "SELECT a.id,a.`host` FROM (select id,`host` FROM eju_static_url WHERE id >= 6053) a WHERE a.`host`='sh.zhilaowang.com'" -e innodb --number-of-queries=100

Using where

在没有给host字段添加索引的时候，explain的extra就是 Using where

压测结果

Using where;USING index

给host添加索引后，explain的extra就是 Using where; Using index

 压测结果

关于 Using where 和 Using index condition 的效率比较：

 Using index condition是需要进行回表查询的，那么与不需要回表的Using where对比，是不是性能更差呢？网上说是，自己写代码验证一下

sql语句

SELECT action FROM eju_static_url WHERE `host`='sh.zhilaowang.com' AND id >= 2000;

压测语句

mysqlslap --no-defaults -h 127.0.0.1 -u root -p123456 -c 10 -i 1 --create-schema test -q "SELECT action FROM eju_static_url WHERE `host`='sh.zhilaowang.com' AND id >= 2000" -e innodb --number-of-queries=100

Using where

在没有给host字段添加索引的时候，explain的extra就是 Using where

压测结果

 Using index condition

给host添加索引后，explain的extra就是Using index condition

压测结果

 也就是说，索引不是随随便便都可以加的，如果没有加正确或者使用正确，可能数据库查询的效率会更慢。同时也得出结论：索引回表查询(相当于查询了两次)比直接表查询(只查询一次)更慢。

正确的操作是：把等式搜索的条件字段和搜索结果尽可能都制作成联合索引

索引使用原则

索引最左原则

查询语句执行时的最左原则

where > group by > order by

where条件最左原则

查询语句的where左右先后顺序会影响查询效率的高低，MySQL会优先查询where最左边的内容后筛选右边的内容，排序从两方面综合判定：

索引顺序：主键>唯一主键>普通主键>无索引

范围顺序：等号>in>范围(大于小于，between，like)

 实践

测试sql语句

SELECT action FROM eju_static_url WHERE version >= 2000 AND `host`='sh.zhilaowang.com';

SELECT action FROM eju_static_url WHERE `host`='sh.zhilaowang.com' and version >= 2000;

压测结果

我们可以看到，在都无索引的情况下，是等式的条件排在最左边的时候，比排在右边时效率更高

like模糊查询最左原则

模糊查询时，能不能确定左边的内容，也会影响查询的效率，如果能确定左边的内容，其实是可以使用到索引的。

 可以看到同一个搜索语句在搜索结果差不多的情况下，最左确定的情况下效率更高一些

联合索引生成时的最左原则

创建联合索引为【a,b,c,d】,那么查询where可以被联合索引匹配的字段的组合方式有【a】【a,b】【a,b,c】【a,b,c,d】，其他的如【a,c】【a,c,d】等非从左顺序组合的查询，都不会使用到索引，具体逻辑看检索索引btree顺序的最左原则

检索索引btree顺序的最左原则

在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

如：创建一个（a,b）的联合索引，那么它的索引树就是下图的样子。

 可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。但是我们又可发现a在等值的情况下，b值又是按顺序排列的，但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。所以b=2这种查询条件没有办法利用索引。

规范使用原则

1. 唯一字段可以建立单索引,如手机号码、身份证号，非唯一字段可以考虑建立联合索引【索引字段的选择：一般会选择推荐重复比较少的字段 ，如果是项目需求，（可以考虑联合索引）】；

2. 索引的个数与包含的字段，最佳是不超出6个，勉强可以是10个,如果超过的话，就建议水平分表(减少索引字段)或垂直分表(减少数据量)；

3. 索引的使用遵循最左匹配原则其次就是覆盖索引

4. 索引的选择字段尽量要小一些 int varchar(10) char(5) es 存储文本路径

5. 避免 like between 等范围查询

6. 尽量多使用explain进行分析

7. 优先考虑建立联合索引，索引的字段不要包含null 或者是 '';