Mysql多列索引分析

版本：mysql5.7.17

create table test_index (

c1 tinyint(1) not null default 0,

c2 tinyint(1) not null default 0,

c3 tinyint(1) not null default 0,

c4 tinyint(1) not null default 0,

c5 tinyint(1) not null default 0,

index c1234(c1,c2,c3,c4)

);

索引数据分布图

 

 

MySQL 执行计划简单介绍

using index ：使用覆盖索引的时候就会出现

using where：在查找使用索引的情况下，需要回表去查询所需的数据

using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

using index condition：在5.6版本后加入的新特性（Index Condition Pushdown）. Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；需要回表查询数据

以上四点就能看出它们之前的区别，或许有部分人都存在疑惑 using index & using where 和using index condition那个比较好，从上面的的解释中就能看出是前者比较好，毕竟不需要回表查询数据，效率上应该比较快的

using filesort :这个filesort 并不是说通过磁盘文件进行排序，而只是告诉我们MySQL要多做一次额外的排序, 确切的说是快速排序(Quicksort).

using filesort不一定引起mysql的性能问题。但是如果查询次数非常多，那么每次在mysql中进行排序，还是会有影响的。

此时，可以进行的优化：

1、修改逻辑，不在mysql中使用order by而是在应用中自己进行排序。

2、使用mysql索引，将待排序的内容放到索引中，直接利用索引的排序。

Using temporary:MySQL需要创建一个临时表来保存结果。 如果查询包含以不同方式列出列的GROUP BY和ORDER BY子句，则通常会发生这种情况。

如果有ORDER BY子句和一个不同的GROUP BY子句，或者如果ORDER BY或GROUP BY中的字段都来自其他的表而非连接顺序中的第一个表的话，就会创建一个临时表了。

 

explain select c1,c2,c3,c4 from test_index where c1=1 and c2=2 and c4>3 and c3=3;

使用的索引长度：4

使用的到的索引：c1,c2,c3,c4

使用索引排序：无需排序

使用覆盖索引，没有进行回表

 

 

explain select * from test_index where c1=1 and c2=2 and c4>3 and c3=3;

使用的索引长度：4

使用的到的索引：c1,c2,c3,c4

使用索引排序：无需排序

有进行回表

explain select * from test_index where c1=1 and c2=2 and c4=3 order by c3;

使用的索引长度：2

使用的到的索引：c1,c2

使用索引排序：c3

有进行回表

 

explain select * from test_index where c1=1 and c2=2 order by c3;

使用的索引长度：2

使用的到的索引：c1,c2

使用索引排序：c3

有进行回表

explain select * from test_index where c1=1 and c2>2 order by c3;

使用的索引长度：2

使用的到的索引：c1,c2

使用索引排序：无,进行了额外的排序操作

有进行回表

explain select * from test_index where c1=1 and c4= 2 group by c3,c2;

使用的索引长度：1

使用的到的索引：c1

使用索引排序：无,进行了额外的排序操作

有进行回表,创建了临时表

explain select * from test_index where c1=1 and c5=2 order by c2,c3;

使用的索引长度：1

使用的到的索引：c1

使用索引排序：c2,c3

有进行回表

 

explain select * from test_index where c1=1 and c2=3 and c5=2 order by c2,c3;

使用的索引长度：2

使用的到的索引：c1,c2

使用索引排序：c2,c3

有进行回表

Mysql索引使用最左精确匹配原则（=或in）。只有前一列索引进行了精确匹配，才可以使用下一列索引。