学习笔记：MySQL索引（三）

explain语法

• 用于查看sql语句是否使用索引

列名	列解释
id	SELECT标识符。这是SELECT的查询序列号，id值越大优先级越高，id值相同则自上而下顺序执行
select_type	SELECT语句的类型 (1) SIMPLE：简单的SELECT语句（不包括UNION操作或子查询操作） (2) PRIMARY：当SELECT语句中包含子查询，则最外层的SELECT被标记为PRIMARY (3) UNION：UNION操作中，最外层的SELECT标记为PRIMARY，内层的SELECT标记为UNION (4) DEPENDENT UNION：同UNION，区别在于DEPENDENT UNION依赖外部查询 (5) UNION RESULT：UNION操作的结果，id值通常为NULL（UNION语句中所有的内层SELECT） (6) SUBQUERY：子查询中的首个SELECT，结果不依赖于外部查询 (7) DEPENDENT SUBQUERY：子查询中的首个SELECT，结果依赖于外部查询 (8) DERIVED：派生表的SELECT，FROM子句的子查询 (9) UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，每次都要计算（子查询不可被物化，耗时操作）
table	显示这一步所访问的数据表名，有可能是简称
partitions	查询将从中匹配记录的分区（该值适用于未分区的表）
type	对表访问方式，表示MySQL在表中找到所需行的方式，常用的类型有： ALL、index、range、 ref、eq_ref、const、system（从左到右，性能从差到好） ALL：全表扫描 index：遍历索引树进行全表扫描 range：有范围的索引扫描 ref：查询条件列使用了索引且列不为主键和UNIQUE（即使用了索引，但该索引列的值并不唯一） eq_ref：使用了主键或者唯一性索引进行查找的情况（即使用了索引，且索引列的值唯一） const：通常情况下，如果将一个主键作为条件查询，MySQL优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器 system：const的特例，平时不会出现，可以忽略不计
possible_keys	MySQL可以选择从中查找此表中的行的索引
key	MySQL实际决定使用的索引，必然包含在possible_keys中
key_len	MySQL决定使用的索引的长度（通过该值能够大概确定 MySQL 实际使用联合索引中的多少个列）
ref	表的连接匹配条件，即哪些索引列或常量（const）被用于查找
rows	估算的找到结果所需要读取的行数
filtered	表示返回结果的行数占需要读取的行数的百分比（filtered值越大越好）
Extra	MySQL解析查询的详细信息，这里列举几种常见情况： Using where：SQL使用了where条件过滤数据且有部分查询列没有走索引 Using index：SQL走的索引列包含了要查询的列（覆盖索引），即通过索引即可拿到需要的数据，无需到数据表中读取行（这类sql语句往往性能较好） Using index condition：走了索引，但查询列不全在索引树上，还需要访问实际的行记录（这类sql语句性能也较好，但不如 Using index） Using filesort：得到所需结果集，需要对所有记录进行文件排序。（这类sql语句性能较差，需要优化。在一个没有建立索引的列上进行了 order by，就会触发filesort，常见优化方案是给 order by 列添加索引，避免每次查询都全量排序） Using temporary：需要建立临时表来暂存中间结果。（这类sql语句性能较差，需要优化。当 group by 和 order by 同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集） Using join buffer (Block Nested Loop)：在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫 join buffer 的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法 Impossible WHERE：where 子句的值总是 false 时触发 Select tables optimized away：基于索引优化 MIN、MAX 操作或者对于MyISAM存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

最左匹配原则

当B+树的数据项是复合的数据结构时，B+树是按照从左到右的顺序来建立搜索树的，即索引的最左匹配特性。

特点：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

假设对表user_info建立复合索引(name,age,sex)，则：

注：在页中的记录是以name asc, age asc, sex asc排序方式存储的

1、全值匹配查询

select * from user_info where name = '小白' and age = 23 and sex = 1;

• 这种情况就会走索引
• where子句中查询条件顺序可随意调换（MySQL有查询优化器，会自动优化查询顺序）

2、匹配左边的列

select * from user_info where name = '小白';
select * from user_info where name = '小白' and age = 23;
select * from user_info where name = '小白' and age = 23 and sex = 1;

• 上述语句都是从最左边开始连续匹配，都会走索引

select * from user_info where age = 23;
select * from user_info where sex = 1;
select * from user_info where age = 23 and sex = 1;

• 上述语句都没有从最左边开始，不会走索引，用的是全表扫描

select * from user_info where name = '小白' and sex = 1;

• 上述语句从最左边开始，但是不连续，这种情况只会用到name列的索引，不会用到sex列的索引

3、模糊查询

select * from user_info where name like '小白%';// 走索引
select * from user_info where name like '%小白';// 全表扫描
select * from user_info where name like '%小白%';// 全表扫描

• 前缀是排序的，所以会走索引。另外两个只能全表扫描

4、匹配范围值

• 我们假设对表user_info建立复合索引(age,salary)

select * from user_info where age > 18 and age < 25;

• 可以对最左边的列进行范围查询，所以上述语句会走索引

select * from user_info where age > 18 and age < 25 and salary > 10000;

• 多个列同时进行范围查询时，只有最左边的列能走索引
• 上述情况只有age能走索引，找到 18 < age < 25 的数据后，再根据 salary > 10000 逐条过滤（因为在 18 < age < 25 的数据中 salary 是无序的）

5、精确匹配最左列，范围查询另一列

• 我们假设对表user_info建立复合索引(age,salary)

select * from user_info where age = 23 and salary > 10000;

• 这种情况会走索引（因为当 age = 23 时 salary 是有序的）

索引区分度

• 索引区分度 = count(distinct 记录) / count(记录)

索引区分度越高，检索速度越快；

索引区分度越低，说明重复数据比较多，检索时候就需要访问更多记录才能找到所有的目标数据；

当索引区分度非常小的时候，就接近于全索引数据的扫描了，此时查询速度是较慢的。

例：有序数组一[1,2,3,4,5]，有序数组二[1,1,1,2,2]

数组一索引区分度为1，数组二索引区分度为0.4

所以我们在创建索引时，应尽量选择区分度高的列作为索引（即选择重复数据少的列作为索引）

其他情况

1、排序 (order by)

• 当语句 explain 结果 Extra 里出现了 Using filesort 代表 MySQL 进行了文件排序操作，也说明索引并没有生效

• 索引排序是否生效取决于 where 子句是否走索引以及 order by 使用的列是否在索引列中

• 我们假设对表user_info建立复合索引(age,salary)

SELECT * from user_info order by email,age;
SELECT * from user_info where name = '小白' order by email,age;

• 没有 where 句或 where 子句不走索引，会进行文件排序

select * from user_info where age = 23 and salary > 10000 order by salary,age;
select * from user_info where age = 23 and salary > 10000 order by salary;
select * from user_info where age = 23 and salary > 10000 order by age;
select * from user_info where age = 23 order by salary,age;

• where 子句走索引；order by 子句中均为 where 子句走的索引列，走索引排序

select * from user_info where age = 23 and salary > 10000 order by salary,age,sex;

• where 子句走索引；order by 子句中 sex 非索引列，会进行文件排序

2、OR

• 我们假设对表user_info建立复合索引(age,salary)

SELECT * from user_info where age = 23 or salary > 10000;

• or会使索引失效（除非查询字段相同），因此上述语句不会走索引

SELECT * from user_info where age > 23 or age < 35;

• 虽然使用了 or，但是查询字段相同，因此会走索引

3、数字使索引字符串类索引失效

SELECT * from user_info WHERE name = "1";// 走索引
SELECT * from user_info WHERE name = 1;// 不走索引

• name 是字符串类型，与数字比较时会将字符串强转为数字再比较，所以第二个查询是全表查询

4、索引字段使用函数查询会使索引失效

SELECT * from user_info WHERE concat(name, "123") = "小白123";

• 使用函数之后，name 所在的索引树是无法快速定位需要查找的数据所在页的，只能进行全表扫描，然后对每条数据使用函数计算之后再进行条件判断

总结

1、不使用索引的情况

(1) 表记录太少；

(2) 数据重复且分布平均的字段（索引区分度太低）；

(3) 经常做增删改的表要减少索引；

(4) 例如text这些数据量大的列不应该建立索引（除非只为其前几个字符建立索引）；

(3) MySQL 估计出全表扫描比使用索引更快时，不使用索引

2、索引失效的情况

(1) 联合索引查询时不符合最左匹配原则；

(2) 查询使用一个索引，但对在另一个索引上做 order by，此时只有查询的索引会生效（因为每一次查询只使用一个索引）；

(3) where 子句使用 or 且查询字段不相同；

(4) where 子句使用 like 且未使用左前缀；

(5) 列类型为字符串时，where 子句未使用引号，发生类型转换，索引失效；

(6) 对索引字段使用函数查询

3、使用索引的建议

(1) 在区分度高的字段上面建立索引可以有效的使用索引；

(2) 使用联合索引时要注意最左匹配原则，MySQL 会按索引顺序向右匹配直到遇到范围查询（>、<、between、like）就停止匹配；

(3) 查询时尽量利用覆盖索引，可以减少回表操作，提升查询效率；

(4) 排序中尽量使用索引字段，这样可以减少文件排序，提升查询效率