在MySQL优化中，查询分析语句是不可或缺的工具。在数据库开发中，遇到查询速度缓慢的问题是司空见惯的挑战。MySQL作为一款广泛使用的关系型数据库，其查询性能优化显得尤为重要。

在MySQL中索引可以优化我们的查询速度，但是如果不正确使用也会导致白白浪费空间而无法优化我们的查询速度，接下来我们来研究下MySQL中索引的机制。

本文章中使用的表结构如下所示。

一、索引的使用

1.创建索引

使用MySQL创建索引

REATE INDEX IndexName ON `TableName`(`字段名`(length)); 
# 或者 
ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length));

2.简单查询

我们来看一下使用索引进行查询与不使用索引进行查询的性能区别。

当不使用索引指定条件进行查询时。

我们现在执行命令在name字段上创建一个索引

create index user_username_index
    on user (username);

我们可以看到查询的时间被大幅缩短，以上就是索引的最简单的使用。

那么我们在执行查询时如何得知是否使用了索引？ 只需要在查询语句前加上explain即可获取此次查询相关信息。

图片仅截取使用索引相关信息,他们表示以下信息 possible_keys 展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。 key 表示MySQL实际选择的索引 我们这次的查询使用了user_username_index这个索引。 type

• NULL : MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引
• SYSTEM :  表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略。
• const : 表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量。
• eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
• ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行。
• range :只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
• index 全索引扫描，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。
• all 全表扫描，将遍历全表以找到匹配的行。

这些类型从上到下性能依次降低。

3.多字段查询

如果执行多字段查询，例如根据username查询username与age，表中只有username有索引。那么在MySQL的二级索引的b+树中，叶子节点只存储了索引的值与对应主键的值而没有其他字段的值，因此需要回到主键索引中再去根据主键值查询需要的数据，这个操作叫做回表，会产生额外的IO。

此时我们可以通过创建一个索引，其中字段为username与age，这样可以避免回表的产生。

创建索引也不是越多越好需要结合实际需要在频繁被作为条件使用的字段上创建合适的索引。

二、索引失效的情况

1.查询条件顺序与索引顺序不一致

创建一个索引包含字段(age,role,username)并使用不同顺序在where中进行查询

按照索引中字段顺序查询，key中显示使用了索引。

使用age与role作为条件也依然可以使用索引

使用age与role进行查询时，虽然显示使用了索引，但是type变为range即范围查询，extra中多出了 Using index for skip scan这条信息。 MySQL8.0.13开始支持 index skip scan 也即索引跳跃扫描。该优化方式支持那些SQL在不符合组合索引最左前缀的原则的情况，优化器依然能组使用组合索引。 但是这也会在一定程度上影响性能，如果MySQL版本较低，回导致进行全表扫描大幅下降性能。

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

• where a=1；
• where a=1 and b=2 and c=3；
• where a=1 and b=2；

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

• where b=2；
• where c=3；
• where b=2 and c=3；

2.对索引使用函数

我们的username列上建立了索引然后进行查询

查询类型变为index,需要扫描索引上的全部数据，它仅比全表扫描快一点。

MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

例如针对上面的查询创建索引

alter table user add key idx_name_length ((length(username)));

再次执行相同的查询

type变为ref并且使用了索引。

3.对索引进行计算

在查询条件中对索引进行表达式计算，会导致性能下降。

type又变为了index,对所有索引进行了扫描。 在查询语句中，不应对索引的值进行计算，如果稍微变化上面的查询，如下

这样我们就可以正常使用索引。

4.对索引使用左模糊匹配。 当我们使用左模糊匹配时也会导致索引失效，例如like %xxx 或者like %xxx%。

索引本身就相当于目录，从左到右逐个排序。而条件的左侧使用了占位符，导致无法按照正常的目录进行匹配，导致索引失效就很正常了。

5.使用OR操作

查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效

由于图片较长仅截取部分图片，在age和username都有索引的情况下使用or不会导致索引失效，MySQL对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(type=index_merge)。

删除age上的索引后再次执行该查询

type变为ALL，进行了全表扫描，这是最差的情况。

不同MySQL版本，对于同一查询处理方式可能不同，在日常使用时如果不确定自己的查询的索引使用情况，可以自行使用explain进行验证。