MySQL索引深度解析：类型、扫描方式与优化策略-优快云博客

之前写过一篇索引的总结，但是发现有些东西还是没有涉及到又或是不够详细，这次再继续写

索引type

使用explan 解释sql语句时，会有一列type，这个type标志了该索引的级别，EXPLAIN执行计划中type字段分为以下几种：ALL ，INDEX ，RANGE ，REF ，EQ_REF CONST, SYSTEM NULL，性能从最差到最好

上面各类扫描方式由快到慢：

system > const > eq_ref > ref > range > index > ALL

下面一一举例说明。

一、system

explain select * from mysql.time_zone;

上例中，从系统库mysql的系统表time_zone里查询数据，扫码类型为system，这些数据已经加载到内存里，不需要进行磁盘IO。

这类扫描是速度最快的。

explain select * from (select * from user where id=1) tmp;

再举一个例子，内层嵌套(const)返回了一个临时表，外层嵌套从临时表查询，其扫描类型也是system，也不需要走磁盘IO，速度超快。

二、const

数据准备：

create table user (

id int primary key,

name varchar(20)

)engine=innodb;

insert into user values(1,‘shenjian’);

insert into user values(2,‘zhangsan’);

insert into user values(3,‘lisi’);

const扫描的条件为：

（1）命中主键(primary key)或者唯一(unique)索引；

（2）被连接的部分是一个常量(const)值；

explain select * from user where id=1;

如上例，id是PK，连接部分是常量1。

画外音：别搞什么类型转换的幺蛾子。

这类扫描效率极高，返回数据量少，速度非常快。

三、eq_ref

数据准备：

create table user (

id int primary key,

name varchar(20)

)engine=innodb;

insert into user values(1,‘shenjian’);

insert into user values(2,‘zhangsan’);

insert into user values(3,‘lisi’);

create table user_ex (

id int primary key,

age int

)engine=innodb;

insert into user_ex values(1,18);

insert into user_ex values(2,20);

insert into user_ex values(3,30);

insert into user_ex values(4,40);

insert into user_ex values(5,50);

eq_ref扫描的条件为，对于前表的每一行(row)，后表只有一行被扫描。

再细化一点：

（1）join查询；

（2）命中主键(primary key)或者非空唯一(unique not null)索引；

（3）等值连接；

explain select * from user,user_ex where user.id=user_ex.id;

如上例，id是主键，该join查询为eq_ref扫描。

这类扫描的速度也异常之快。

四、ref

数据准备：

create table user (

id int,

name varchar(20) ,

index(id)

)engine=innodb;

insert into user values(1,‘shenjian’);

insert into user values(2,‘zhangsan’);

insert into user values(3,‘lisi’);

create table user_ex (

id int,

age int,

index(id)

)engine=innodb;

insert into user_ex values(1,18);

insert into user_ex values(2,20);

insert into user_ex values(3,30);

insert into user_ex values(4,40);

insert into user_ex values(5,50);

如果把上例eq_ref案例中的主键索引，改为普通非唯一(non unique)索引。

explain select * from user,user_ex where user.id=user_ex.id;

就由eq_ref降级为了ref，此时对于前表的每一行(row)，后表可能有多于一行的数据被扫描。

explain select * from user where id=1;

当id改为普通非唯一索引后，常量的连接查询，也由const降级为了ref，因为也可能有多于一行的数据被扫描。

ref扫描，可能出现在join里，也可能出现在单表普通索引里，每一次匹配可能有多行数据返回，虽然它比eq_ref要慢，但它仍然是一个很快的join类型。

五、range

数据准备：

create table user (

id int primary key,

name varchar(20)

)engine=innodb;

insert into user values(1,‘shenjian’);

insert into user values(2,‘zhangsan’);

insert into user values(3,‘lisi’);

insert into user values(4,‘wangwu’);

insert into user values(5,‘zhaoliu’);

range扫描就比较好理解了，它是索引上的范围查询，它会在索引上扫码特定范围内的值。

explain select * from user where id between 1 and 4;

explain select * from user where idin(1,2,3);

explain select * from user where id>3;

像上例中的between，in，>都是典型的范围(range)查询。

画外音：必须是索引，否则不能批量"跳过"。

六、index

图片

index类型，需要扫描索引上的全部数据。

explain count (*) from user;

如上例，id是主键，该count查询需要通过扫描索引上的全部数据来计数。

画外音：此表为InnoDB引擎。

它仅比全表扫描快一点。

七、ALL

数据准备：

create table user (

id int,

name varchar(20)

)engine=innodb;

insert into user values(1,‘shenjian’);

insert into user values(2,‘zhangsan’);

insert into user values(3,‘lisi’);

create table user_ex (

id int,

age int

)engine=innodb;

insert into user_ex values(1,18);

insert into user_ex values(2,20);

insert into user_ex values(3,30);

insert into user_ex values(4,40);

insert into user_ex values(5,50);

explain select * from user,user_ex where user.id=user_ex.id;

如果id上不建索引，对于前表的每一行(row)，后表都要被全表扫描。

今天这篇文章中，这个相同的join语句出现了三次：

（1）扫描类型为eq_ref，此时id为主键；

（2）扫描类型为ref，此时id为非唯一普通索引；

（3）扫描类型为ALL，全表扫描，此时id上无索引；

总结

（1）explain结果中的type字段，表示（广义）连接类型，它描述了找到所需数据使用的扫描方式；

（2）常见的扫描类型有：

system>const>eq_ref>ref>range>index>ALL

其扫描速度由快到慢；

（3）各类扫描类型的要点是：

system最快：不进行磁盘IO

const：PK或者unique上的等值查询

eq_ref：PK或者unique上的join查询，等值匹配，对于前表的每一行(row)，后表只有一行命中

ref：非唯一索引，等值匹配，可能有多行命中

range：索引上的范围扫描，例如：between/in/>

index：索引上的全集扫描，例如：InnoDB的count

ALL最慢：全表扫描(full table scan)

（4）建立正确的索引(index)，非常重要；

（5）使用explain了解并优化执行计划，非常重要；

explain结果中Extra字段

explain结果里还有一个Extra字段，这个字段的意义是什么呢？
数据准备：

create table user (

id int primary key,

name varchar(20),

sex varchar(5),

index(name)

)engine=innodb;

insert into user values(1, ‘shenjian’,‘no’);

insert into user values(2, ‘zhangsan’,‘no’);

insert into user values(3, ‘lisi’, ‘yes’);

insert into user values(4, ‘lisi’, ‘no’);

数据说明：

用户表：id主键索引，name普通索引（非唯一），sex无索引；

四行记录：其中name普通索引存在重复记录lisi；

一、【Using where】

在这里插入图片描述实验语句：

explain select * from user where sex=‘no’;
结果说明：

Extra为Using where说明，SQL使用了where条件过滤数据。
需要注意的是：

（1）返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化；

（2）使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断；
本例虽然Extra字段说明使用了where条件过滤，但type属性是ALL，表示需要扫描全部数据，仍有优化空间。
常见的优化方法为，在where过滤属性上添加索引。
画外音：本例中，sex字段区分度不高，添加索引对性能提升有限。

二、【Using index】

在这里插入图片描述
实验语句：

explain select id，name from user where name=‘shenjian’;

结果说明：

Extra为Using index说明，SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录，不会回表查询,非聚簇索引里包含了name和id。

画外音：The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row.

这类SQL语句往往性能较好。

三、【Using index condition】

在这里插入图片描述实验语句：

explain select id,name,sex from user

where name=‘shenjian’;

画外音：该SQL语句与上一个SQL语句不同的地方在于，被查询的列，多了一个sex字段。

结果说明：
能够命中name索引，索引叶子节点存储了主键id，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取sex字段，效率会降低。

画外音：聚集索引，普通索引的底层实现差异，详见1分钟了解MyISAM与InnoDB的索引差异。

如果把(name)单列索引升级为联合索引(name, sex)就不同了。

create table user (

id int primary key,

name varchar(20),

sex varchar(5),

index(name, sex)

)engine=innodb;

在这里插入图片描述可以看到：

select id,name … where name=‘shenjian’;

select id,name,sex … where name=‘shenjian’;

都能够命中索引覆盖，无需回表。

哪些场景可以利用索引覆盖来优化SQL？

场景1：全表count查询优化
在这里插入图片描述利用索引数据不去回表查询

场景2：列查询回表优化

select id,name,sex … where name=‘shenjian’;

这个例子不再赘述，将单列索引(name)升级为联合索引(name, sex)，即可避免回表。

场景3：分页查询

select id,name,sex … order by name limit 500,100;

将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。

四、【Using filesort】

在这里插入图片描述实验语句：

explain select * from user order by sex;

结果说明：

Extra为Using filesort说明，得到所需结果集，需要对所有记录进行文件排序。
这类SQL语句性能极差，需要进行优化。

典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序，注意，只有当不会回表的时候索引才有作用，如下
在这里插入图片描述

五、【Using temporary】

在这里插入图片描述结果说明：

Extra为Using temporary说明，需要建立临时表(temporary table)来暂存中间结果。

这类SQL语句性能较低，往往也需要进行优化。

典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

六、【Using join buffer (Block Nested Loop)】

在这里插入图片描述实验语句：

explain select * from user where id in(select id from user where sex=‘no’);

结果说明：

Extra为Using join buffer (Block Nested Loop)说明，需要进行嵌套循环计算。

画外音：内层和外层的type均为ALL，rows均为4，需要循环进行4*4次计算。

这类SQL语句性能往往也较低，需要进行优化。

典型的，两个关联表join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。

关于索引的覆盖、回表等概念可以回头看下我的这篇文章mysql索引详解

好的，接下来我们讲下其他没有涉及到的索引方式和细节

前缀索引

有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

案例演示：
–创建数据表 create table citydemo(city varchar(50) not null); insert into citydemo(city) select city from city;

–重复执行5次下面的sql语句 insert into citydemo(city) select city from citydemo;

–更新城市表的名称 update citydemo set city=(select city from city order by rand() limit 1);

–查找最常见的城市列表，发现每个值都出现45-65次， select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;

–查找最频繁出现的城市前缀，先从3个前缀字母开始，发现比原来出现的次数更多，可以分别截取多个字符查看城市出现的次数 select count() as cnt,left(city,3) as pref from citydemo group by pref order
by cnt desc limit 10; select count() as cnt,left(city,7) as pref from
citydemo group by pref order by cnt desc limit 10;
–此时前缀的选择性接近于完整列的选择性

–还可以通过另外一种方式来计算完整列的选择性，可以看到当前缀长度到达7之后，再增加前缀长度，选择性提升的幅度已经很小了 select count(distinct left(city,3))/count() as sel3, count(distinct
left(city,4))/count() as sel4, count(distinct left(city,5))/count() as sel5, count(distinct left(city,6))/count() as sel6, count(distinct left(city,7))/count() as sel7, count(distinct left(city,8))/count() as sel8 from citydemo;

–计算完成之后可以创建前缀索引 alter table citydemo add key(city(7));

–注意：前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by 和 group by。

使用索引扫描来排序

mysql有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描，如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序；
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录就得回表查询一次对应的行，这基本都是随机IO，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢；
mysql可以使用同一个索引即满足排序，又用于查找行，如果可能的话，设计索引时应该尽可能地同时满足这两种任务；
只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，mysql才能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当orderby子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则，mysql都需要执行顺序操作，而无法利用索引排序

–sakila数据库中rental表在rental_date,inventory_id,customer_id上有rental_date的索引
–使用rental_date索引为下面的查询做排序 explain select rental_id,staff_id from rental where rental_date=‘2005-05-25’ order by inventory_id,customer_id\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE
table: rental partitions: NULL
type: ref possible_keys: rental_date
key: rental_date
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition 1 row in set, 1 warning (0.00 sec)
–order by子句不满足索引的最左前缀的要求，也可以用于查询排序，这是因为所以你的第一列被指定为一个常数

–该查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两个列组合在一起，就形成了索引的最左前缀 explain select rental_id,staff_id from rental where rental_date=‘2005-05-25’ order by
inventory_id desc\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE
table: rental partitions: NULL
type: ref possible_keys: rental_date
key: rental_date
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using where 1 row in set, 1 warning (0.00 sec)

–下面的查询不会利用索引 explain select rental_id,staff_id from rental where rental_date>‘2005-05-25’ order by rental_date,inventory_id\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE
table: rental partitions: NULL
type: ALL possible_keys: rental_date
key: NULL
key_len: NULL
ref: NULL
rows: 16005
filtered: 50.00
Extra: Using where; Using filesort

–该查询使用了两中不同的排序方向，但是索引列都是正序排序的 explain select rental_id,staff_id from rental where rental_date>‘2005-05-25’ order by inventory_id
desc,customer_id asc\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE
table: rental partitions: NULL
type: ALL possible_keys: rental_date
key: NULL
key_len: NULL
ref: NULL
rows: 16005
filtered: 50.00
Extra: Using where; Using filesort 1 row in set, 1 warning (0.00 sec)

–该查询中引用了一个不再索引中的列 explain select rental_id,staff_id from rental where rental_date>‘2005-05-25’ order by inventory_id,staff_id\G
*************************** 1. row ***************************
id: 1 select_type: SIMPLE
table: rental partitions: NULL
type: ALL possible_keys: rental_date
key: NULL
key_len: NULL
ref: NULL
rows: 16005
filtered: 50.00
Extra: Using where; Using filesort 1 row in set, 1 warning (0.00 sec)

union all,in,or都能够使用索引，但是推荐使用in

explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
explain select * from actor where actor_id in (1,2);
explain select * from actor where actor_id = 1 or actor_id =2;

强制类型转换会全表扫描

案例： create table user(id int,name varchar(10),phone varchar(11));
alter table user add index idx_1(phone);

explain select * from user where phone=13800001234; 不会触发索引 explain
select * from user where phone=‘13800001234’;触发索引

连接查询时，* 无法进入缓冲池

mysql中连接查询的原理是先对驱动表进行查询操作，然后再用从驱动表得到的数据作为条件，逐条的到被驱动表进行查询。
每次驱动表加载一条数据到内存中，然后被驱动表所有的数据都需要往内存中加载一遍进行比较。效率很低，所以mysql中可以指定一个缓冲池的大小，缓冲池大的话可以同时加载多条驱动表的数据进行比较，放的数据条数越多性能io操作就越少，性能也就越好。所以，如果此时使用select * 放一些无用的列，只会白白的占用缓冲空间。浪费本可以提高性能的机会。

参考：mysql数据索引，netty

mysql join底层实现

5.5 版本之前，MySQL本身只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会非常长。在5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。

mysql底层join实现只支持一种算法：嵌套循环连接（Nested-Loop Join）,nested-Loop-Join有三种变种：

Simple Nested-Loop Join 简单嵌套循环连接
Index Nested-Loop Join 索引嵌套循环连接
Block Nested-Loop Join 块索引嵌套连接

1.Simple Nested-Loop Join：

如图，A为驱动表，B为匹配表。从A中取出数据1放入内存，遍历B，将匹配到的数据放到result… 以此类推，每条A表数据都会轮询B表

在这里插入图片描述

2.Index Nested-Loop Join（索引嵌套）：

在这里插入图片描述

这个需要查询时，关联非驱动表（也就是匹配表）的索引，通过索引来减少比较，加速查询。

在查询时驱动表会根据关联字段的索引到非驱动表查找数据，找到对应的值，此时分为两种情况：

如果索引不是主键索引的话，需要进行回表查询（根据索引携带的主键信息查询数据）不是主键时，要进行多次回表查询，先关联索引，再根据主键ID查询，性能上要慢很多。

如果关联字段是非驱动表的主键时，性能会非常高，直接就能定位到数据。

3.Block Nested-Loop Join（块嵌套）：

在这里插入图片描述
如果join的列不是索引，就会采用Block Nested-Loop Join。首先将驱动表的结果集中所有与join相关的列都先缓存到join buffer中（这样当查找完成时，就可以将匹配到的记录从内存与非驱动表放到result返回），然后批量与匹配表进行匹配，将第一种中的多次比较合并为一次，降低了非驱动表的访问频率。默认情况下join_buffer_size=256K（可以通过show variables like ‘join_%’ 查看大小）。

BNL 算法:将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数.

举例来说，外层循环（驱动表）的结果集是100行，使用NLJ 算法需要扫描内部表100次（被驱动表），如果使用BNL算法，先把对Outer Loop表(外部表)每次读取的10行记录放到join buffer,然后在InnerLoop表(内部表)中直接匹配这10行数据，内存循环就可以一次与这10行进行比较, 这样只需要比较10次，对内部表的扫描减少了9/10。所以BNL算法就能够显著减少内层循环表扫描的次数.

更多细节可以看这篇文章MySQL联接查询算法（NLJ、BNL、BKA、HashJoin）