MySQL索引失效场景及解决方案

在对SQL语句进行索引查询时会遇到索引失效的时候，对于该语句的可行性以及性能效率方面有至关重要的影响，本篇剖析索引为何失效，有哪些情况会导致索引失效以及对于索引失效时的优化解决方案，其中着重介绍最左前缀匹配原则、MySQL逻辑架构和优化器、索引失效场景以及为何会失效。

二、最左前缀匹配原则

之前有写了一篇关于MySQL添加索引特点及优化问题方面的文章，下面将介绍索引失效的相关内容。

首先引入在之后的索引失效原因中会使用到的一个原则：最左前缀匹配原则。

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

什么是最左前缀匹配原则呢？要想理解联合索引的最左匹配原则，先来理解下索引的底层原理：索引的底层是一颗B+树，那么联合索引的底层也就是一颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。

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

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

由于整个过程是基于explain结果分析的，那接下来在了解下explain中的type字段和key_lef字段。

1.type：联接类型。

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const。eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。注意：ALL全表扫描的表记录最少的表如t1表ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引。index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）ALL：Full Table Scan，遍历全表以找到匹配的行

2.key_len：显示MySQL实际决定使用的索引的长度。如果索引是NULL，则长度为NULL。如果不是NULL，则为使用的索引的长度。所以通过此字段就可推断出使用了那个索引。

计算规则：

1.定长字段，int占用4个字节，date占用3个字节，char(n)占用n个字符。2.变长字段varchar(n)，则占用n个字符+两个字节。3.不同的字符集，一个字符占用的字节数是不同的。Latin1编码的，一个字符占用一个字节，gdk编码的，一个字符占用两个字节，utf-8编码的，一个字符占用三个字节。

（由于我数据库使用的是Latin1编码的格式，所以在后面的计算中，一个字符按一个字节算）

4.对于所有的索引字段，如果设置为NULL，则还需要1个字节。

了解了最左前缀匹配原则后我们来看看索引失效的场景以及剖析为何会失效。

三、MySQL逻辑架构和优化器

MySQL逻辑架构：

mysql架构可分为大概的4层，分别是：

1.客户端：各种语言都提供了连接mysql数据库的方法，比如jdbc、php、go等，可根据选择的后端开发语言选择相应的方法或框架连接mysql2.server层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖mysql的大多数核心服务功能，以及所有的内置函数（例如日期、世家、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。3.存储引擎层：负责数据的存储和提取，是真正与底层物理文件打交道的组件。数据本质是存储在磁盘上的，通过特定的存储引擎对数据进行有组织的存放并根据业务需要对数据进行提取。存储引擎的架构模式是插件式的，支持Innodb，MyIASM、Memory等多个存储引擎。现在最常用的存储引擎是Innodb，它从mysql5.5.5版本开始成为了默认存储引擎。4.物理文件层：存储数据库真正的表数据、日志等。物理文件包括：redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等。

server层重要组件介绍：

1.连接器

连接器负责来自客户端的连接、获取用户权限、维持和管理连接。

一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建连接才会使用新的权限设置。

2.查询缓存

mysql拿到一个查询请求后，会先到查询缓存查看之前是否执行过这条语句。前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果当前sql查询能够直接在查询缓存中找到key，那么这个value就会被直接返回给客户端。

其实大多数情况下建议不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。查询缓存非常容易失效，只要对一个表进行更新，与这个表相关的所有查询缓存都会被清空。因此很可能费劲把结果存起来后，还没使用就被一个更新操作全清空了。对于更新操作多的数据库来说，查询缓存的命中率会非常低。除非业务需要的是一张静态表，很长时间才会更新一次。比如，一个系统配置表，那么这张表的查询才适合使用查询缓存。

3.分析器

词法分析（识别关键字，操作，表名，列名）语法分析 (判断是否符合语法）

4.优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

5.执行器

开始执行的时候，要先判断一下用户对这个表 T 有没有执行查询的权限。如果没有，就会返回没有权限的错误。如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去调用这个引擎提供的接口。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同的。

MySQL优化器：

MySQL 优化器使用基于成本的优化方式（Cost-based Optimization），以 SQL 语句作为输入，利用内置的成本模型和数据字典信息以及存储引擎的统计信息决定使用哪些步骤实现查询语句，也就是查询计划。

从高层次来说，MySQL Server 可以分为两部分：服务器层以及存储引擎层。其中，优化器工作在服务器层，位于存储引擎 API 之上。

优化器的工作过程从语义上可以分为四个阶段：

1.逻辑转换，包括否定消除、等值传递和常量传递、常量表达式求值、外连接转换为内连接、子查询转换、视图合并等；2.优化准备，例如索引 ref 和 range 访问方法分析、查询条件扇出值（fan out，过滤后的记录数）分析、常量表检测；3.基于成本优化，包括访问方法和连接顺序的选择等；4.执行计划改进，例如表条件下推、访问方法调整、排序避免以及索引条件下推。

四、索引失效场景以及为何会失效

1.like以通配符%开头索引失效。上面介绍了最左前缀匹配底层原理，我们知道了通常用的索引数据结构是B+树，而索引是有序排列的。如果索引关键字的类型是Int 类型，索引的排列顺序如下：

数据只存放在叶子节点，而且是有序的排放。

如果索引关键字的类型是String类型，排列顺序如下：

可以看出，索引的排列顺序是根据比较字符串的首字母排序的。我们在进行模糊查询的时候，如果把 % 放在了前面，最左的 n 个字母便是模糊不定的，无法根据索引的有序性准确的定位到某一个索引，只能进行全表扫描，找出符合条件的数据。(最左前缀底层原理)

在使用联合索引时也是如此，如果违背了索引有序排列的规则，同样会造成索引失效，进行全表扫描。例子:表example中有个组合索引为：（A,B,C）SELECT * FROM example WHERE A=1 and B =1 and C=1; 可以走索引；SELECT A FROM example WHERE C =1 and B=1 ORDER BY A; 可以走索引（使用了覆盖索引）SELECT * FROM example WHERE C =1 and B=1 ORDER BY A; 不可以走索引

覆盖索引:索引包含所有满足查询需要的数据的索引，称为覆盖索引(Covering Index)

可以有两种方式优化：一种是使用覆盖索引，第二种是把%放后面。

2.字段类型是字符串，where时没有用引号括起来。表中的字段为字符串类型，是B+树的普通索引，如果查询条件传了一个数字过去，它是不走索引的。例子:表example中有个字段为pid是varchar类型。

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE pid = 1

//此时执行语句type为ref索引查询
explain SELECT * FROM example WHERE pid = '1'

为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。

3.OR 前后只要存在非索引的列，都会导致索引失效。查询条件包含or，就有可能导致索引失效。例子:表example中有字段为pid是int类型,score是int类型。

//此时执行语句type为ref索引查询
explain SELECT * FROM example WHERE pid = 1

//把or条件加没有索引的score，并不会走索引，为ALL全表查询
explain SELECT * FROM example WHERE pid = 1 OR score = 10

这里对于OR后面加上没有索引的score这种情况，假设它走了p_id的索引，但是走到score查询条件时，它还得全表扫描，也就是需要三步过程： 全表扫描+索引扫描+合并。mysql是有优化器的，处于效率与成本，遇到OR条件，索引可能会失效也是合理的。

注意: 如果or条件的列都加了索引，索引可能会走的。

4.联合索引(组合索引)，查询时的条件列不是联合索引中的第一个列，索引失效。在联合索引中，查询条件满足最左匹配原则时，索引是正常生效的。当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。例子:有一个联合索引idx_pid_score，pid在前，score在后。

//此时执行语句type为ref索引查询,idx_pid_score索引
explain SELECT * FROM example WHERE pid = 1 OR score = 10

//此时执行语句type为ref索引查询,idx_pid_score索引
explain SELECT * FROM example WHERE pid = 1

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score = 10

联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关。

5.计算、函数、类型转换(自动或手动)导致索引失效，索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。birthtime加了索引，但是因为使用了mysql的内置函数Date_ADD()，也没有走索引。例子：在表example中有idx_birth_time索引为datetime类型的birthtime字段

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE Date_ADD(birthtime，INTERVAL 1 DAY) = 6

还有对索引列运算（如，+、-、*、/），索引失效。例子：在表example中有int类型的score字段索引idx_score

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score-1=5

还有不等于(!= 或者<>)导致索引失效。例子：在表example中有int类型的score字段索引idx_score

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score != 2

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE score <> 3

虽然score 加了索引，但是使用了！= 或者 < >，not in这些时，索引如同虚设。6. is null可以使用索引，is not null无法使用索引。例子:在表example中有varchar类型的name字段索引idx_name，varchar类型的card字段索引idx_card。

//此时执行语句type为range索引查询
explain SELECT * FROM example WHERE name is not null

//此时执行语句type为ALL全表查询
explain SELECT * FROM example WHERE name is not null OR  card is not null

7.左连接查询或者右连接查询查询关联的字段编码格式不一样。两张表相同字段外连接查询时字段编码格式不同则会不走索引查询。例子:在表example中有varchar类型的name字段编码是utf8mb4,索引为idx_name在表example_two中有varchar类型的name字段编码为utf8,索引为idx_name。

//此时执行语句example表会走type为index类型索引，example_two则为ALL全表搜索不走索引
explain SELECT e.name,et.name FROM example e LEFT JOIN example_two et on e.name = et.name

当把两表的字段类型改为一致时:

//此时执行语句example表会走type为index类型索引，example_two会走type为ref类型索引
explain SELECT e.name,et.name FROM example e LEFT JOIN example_two et on e.name = et.name

所以字段类型也会导致索引失效8.mysql估计使用全表扫描要比使用索引快,则不使用索引。当表的索引被查询，会使用最好的索引，除非优化器使用全表扫描更有效。优化器优化成全表扫描取决与使用最好索引查出来的数据是否超过表的30%的数据。建议:不要给’性别’等增加索引。如果某个数据列里包含了均是"0/1"或“Y/N”等值，即包含着许多重复的值，就算为它建立了索引，索引效果不会太好，还可能导致全表扫描。Mysql出于效率与成本考虑，估算全表扫描与使用索引，哪个执行快，这跟它的优化器有关。