MySQL索引失效的场景

创建一个名为test_db的数据库，并在其中创建一个名为test_table的表。该表包含多个字段，并在某些字段上创建索引。

CREATE DATABASE IF NOT EXISTS test_db;

USE test_db;

CREATE TABLE IF NOT EXISTS test_table (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(255) NOT NULL,age INT,salary DECIMAL(10, 2),created_at DATETIME,INDEX idx_name(name),INDEX idx_age(age),INDEX idx_salary(salary),INDEX idx_created_at(created_at)
);

INSERT INTO test_table (name, age, salary, created_at)
SELECT CONCAT('Name', FLOOR(1 + RAND() * 10000)), FLOOR(18 + RAND() * 50), ROUND(RAND() * 100000, 2), NOW() - INTERVAL FLOOR(RAND() * 3650) DAY
FROM information_schema.COLUMNS 
LIMIT 1000;

SHOW INDEX FROM test_table;

这个问题要分版本回答！！！版本不同可能会导致索引失效的场景也不同，直接给答案的都是耍流氓！！！

一、索引失效的场景

1、使用 LIKE 并且是左边带 %

EXPLAIN SELECT * FROM test_table WHERE name LIKE '%abc';

EXPLAIN SELECT * FROM test_table WHERE name LIKE 'abc%';

EXPLAIN SELECT * FROM test_table WHERE name LIKE '%abc%';

在MySQL 8中，这种查询通常不会使用索引，因为左边的%使得索引无法按照字典序进行快速查找。

2、隐式类型转换导致索引失效

EXPLAIN SELECT * FROM test_table WHERE age = '25';

如果age字段是INT类型，但查询条件使用了字符串'25'，MySQL会进行隐式类型转换，导致索引失效。

在MySQL 8中，查询优化器的改进使得某些情况下，即使存在隐式类型转换，索引依然可能被使用。具体到提到的例子，即使age是INT类型，而查询条件是字符串'25'，MySQL优化器可能仍然决定使用索引，这取决于优化器的评估和表的具体结构、数据分布等因素。这说明了MySQL的查询优化器在处理隐式类型转换时更为智能。即使有类型不匹配，优化器依然可以选择使用索引，从而提升查询性能。

如何进一步验证索引的使用情况：

使用 ANALYZE TABLE 命令：确保表的统计信息是最新的。优化器依赖这些统计信息做出决策。

ANALYZE TABLE test_table;

运行 ANALYZE TABLE test_table; 后，MySQL 会重新计算该表的统计信息，以确保优化器能够基于最新的数据分布来做出最优的查询执行计划。返回的结果 status: OK 表示表的统计信息已经成功更新。

进一步的验证步骤：

EXPLAIN ANALYZE SELECT * FROM test_table WHERE age = '25';

根据 EXPLAIN ANALYZE 的输出，MySQL 优化器确实使用了 idx_age 索引来处理查询。以下是输出的具体解析：

Index lookup on test_table using idx_age: 说明 MySQL 使用了 idx_age 索引来查找满足 age = '25' 的记录。
(cost=6.25 rows=25): 这是优化器的估计值，表示查询的成本为6.25，预估返回25行数据。
(actual time=0.257…0.262 rows=25 loops=1): 这是实际执行时的测量数据，表示查询在0.257到0.262毫秒之间完成，实际返回了25行数据，仅执行了一次循环。

那么我们怎么来验证：隐式类型转换导致索引失效？？

使用 BINARY 强制类型匹配

使用 BINARY 强制MySQL将查询条件当作二进制字符串进行比较，避免隐式类型转换。

EXPLAIN SELECT * FROM test_table WHERE BINARY age = '25';

3、在 WHERE 条件中对索引列使用运算或函数

EXPLAIN SELECT * FROM test_table WHERE age + 1 = 30;

EXPLAIN SELECT * FROM test_table WHERE YEAR(created_at) = 2020;

在这些查询中，由于对索引列进行了运算或使用了函数，MySQL无法利用索引进行快速查找。

4 、使用 OR 且存在非索引列

EXPLAIN SELECT * FROM test_table WHERE age = 25 OR salary = 50000;

在这个查询中，age和salary上都有索引，但由于OR操作可能涉及非索引列，MySQL可能无法使用索引。

5 、在 WHERE条件中两列做比较

EXPLAIN SELECT * FROM test_table WHERE age = salary;

这种查询涉及两个字段的比较，通常会导致索引失效。

6、使用 IN 可能不会走索引

SET SESSION eq_range_index_dive_limit = 10;

EXPLAIN SELECT * FROM test_table WHERE age IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

如果IN中的值超过eq_range_index_dive_limit的默认值（在MySQL 8中为200），可能不会使用索引。

MySQL环境变量eq_range_index_dive_limit的值对IN语法有很大影响，该参数表示使用索引情况下IN中参数的最大数量。MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit的默认值为10，之后的版本默认值为200。我们拿MySQL8.0.19举例，eq_range_index_dive_limit=200表示当IN (…)中的值 >200个时，该查询一定不会走索引。<=200则可能用到索引。

7 、使用非主键范围条件查询

EXPLAIN SELECT * FROM test_table WHERE age > 30;

在某些情况下，范围查询可能导致索引失效，具体行为取决于查询的条件和表的结构。

8 、使用 ORDER BY可能会导致索引失效

在MySQL中，ORDER BY 子句的使用可能会导致索引失效，尤其是在以下情况下：

排序列与索引列顺序不一致：如果ORDER BY子句中指定的列的顺序与索引中的顺序不一致，可能会导致索引失效。
使用的索引不覆盖排序的所有列：当索引没有包含所有在ORDER BY中指定的列时，MySQL可能无法利用索引进行排序。
混合升序和降序排序：如果索引的列和排序的顺序（升序或降序）不一致，索引可能无法用于优化排序。

场景 1：索引与排序顺序不一致

假设我们使用了 ORDER BY 对 name 和 age 列进行排序，但排序顺序与索引定义的顺序不一致。

EXPLAIN SELECT * FROM test_table ORDER BY age, name;

在这个查询中，ORDER BY 的顺序是 age, name，但我们定义的索引是 name 和 age 分别独立的索引。由于顺序不同，MySQL 可能无法利用这两个独立的索引来优化排序，因此可能导致索引失效。

场景 2：混合升序和降序排序

假设我们使用 ORDER BY 对 name 列升序排序，对 age 列降序排序。

EXPLAIN SELECT * FROM test_table ORDER BY name ASC, age DESC;

即使我们对 name 和 age 列分别创建了索引，由于我们使用的是混合的升序和降序排序，MySQL 可能无法利用索引进行优化。

场景 3：排序列不在同一个复合索引中

假设我们没有创建复合索引，而是创建了单独的索引，但我们想要按多个列排序：

EXPLAIN SELECT * FROM test_table ORDER BY name, age;

由于 name 和 age 在不同的索引中，MySQL 可能无法利用这两个独立的索引来同时优化排序。

如果看到 Using filesort，说明索引没有被利用，MySQL 使用了文件排序，这就是索引失效的情况。

9、使用 IS NULL 或 IS NOT NULL可能导致索引失效

EXPLAIN SELECT * FROM test_table WHERE salary IS NULL;

EXPLAIN SELECT * FROM test_table WHERE salary IS NOT NULL;

对于IS NULL或IS NOT NULL的查询，索引有时不会被使用。

后面我们详解：如果表中有字段为NULL 索引是否会失效？

10、计算、函数导致索引失效

EXPLAIN SELECT * FROM test_table WHERE salary + 0 = 50000;

当然我们也可以使用下属比较简单的方式进行验证：

EXPLAIN SELECT * FROM test_table WHERE DATE(created_at) = CURDATE();

-- 使用 UPPER() 函数转换 name 为大写，索引 idx_name 会失效
EXPLAIN SELECT * FROM test_table WHERE UPPER(name) = 'NAME1234';

11、范围条件右边的列索引失效

如果一个查询条件涉及到多个索引列，而其中一个条件是范围查询（如 >, <, BETWEEN），则范围条件右边的列的索引可能会失效。

EXPLAIN SELECT * FROM test_table  WHERE name = 'John' AND age > 30 AND salary > 50000;

现象是由于MySQL查询优化器的工作方式。在 EXPLAIN 结果中，MySQL选择了 idx_name 作为查询的主要索引，并且显示了“Using where”的额外信息，表示 MySQL 在扫描索引后仍然应用了 WHERE 条件来过滤数据。

为什么索引没有失效？

在这种情况下，MySQL选择了最有选择性的索引（通常是最能减少结果集的索引），然后再应用其他 WHERE 条件。具体到查询，MySQL首先使用 idx_name 索引查找 name = 'John' 的记录，因为它认为这个索引最有效。接着，MySQL会对找到的记录应用其他条件（age > 30 和 salary > 50000）。

虽然理论上范围查询后的索引可能失效，但这并不总是意味着索引在 EXPLAIN 中不会被使用。MySQL优化器可能会使用部分索引，然后在内存中应用剩余的条件。

我们来进一步演示：

强制使用索引：使用 FORCE INDEX 强制 MySQL 使用一个特定的索引，例如 idx_age。

EXPLAIN SELECT * FROM test_table FORCE INDEX (idx_age) WHERE name = 'John' AND age > 30 AND salary > 50000;

这是因为MySQL优化器仍然认为这个索引可以提高查询性能，即使有范围条件。这种行为反映了MySQL优化器的智能性，尤其是在处理复合条件时。

12、不等于(!= 或者 <>)索引失效

使用 != 或 <> 进行查询时，可能导致索引失效。

-- 使用不等于操作符，idx_age 索引会失效
EXPLAIN SELECT * FROM test_table WHERE age != 30;

13、不匹配最左前缀法则

对于联合索引（复合索引），如果查询条件没有匹配到索引的最左前缀，那么该索引就会失效。

-- 创建联合索引 idx_name_age_salary
CREATE INDEX idx_name_age_salary ON test_table(name, age, salary);

-- 这里查询只使用了 age 和 salary，没有匹配最左前缀 name，导致索引失效
EXPLAIN SELECT * FROM test_table WHERE age = 25 AND salary = 50000;

在这种情况下，如果我们运行查询 不使用 name 列，而只使用 age 和 salary 列，理论上联合索引应该失效，因为我们没有使用最左前缀 name。

根据 EXPLAIN 输出，MySQL 选择了单列索引 idx_salary 而不是联合索引 idx_name_age_salary。这表明 MySQL 优化器在检测到你没有使用最左前缀 name 时，自动选择了其他可用的单列索引来优化查询，而不是强制执行联合索引。

可以强制 MySQL 使用联合索引 idx_name_age_salary:

EXPLAIN SELECT * FROM test_table FORCE INDEX (idx_name_age_salary) WHERE age = 25 AND salary = 50000;

为了后续的测试，我们删除掉索引：

-- 删除联合索引 idx_name_age_salary
DROP INDEX idx_name_age_salary ON test_table;

二、详解（了解即可）

1、如果表中有字段为NULL 索引是否会失效？

https://dev.mysql.com/doc/refman/8.0/en/is-null-optimization.html

中文：

https://mysql.net.cn/doc/refman/8.0/en/is-null-optimization.html

即使我们使用is null 或者is not null 它其实都是会走索引的。这里首先就得来讲讲NULL值是怎么在记录中存储的，又是怎么在B+树中存储的呢。

那么在InnoDB中分为聚簇索引和非聚簇索引两种，聚簇索引本身是不允许记录为空的，所以可以不不用考虑，那么就剩下非聚簇索引也就是我们的辅助索引。

那既然IS NULL、IS NOT NULL、!=这些条件都可能使用到索引，那到底什么时候索引，什么时候采用全表扫描呢？

首先我们得知道两个东西，第一个在InnoDB引擎是如何存储NULL值的，第二个问题是索引是如何存储NULL值的，这样我们才能从根上理解NULL在什么场景走索引，在什么场景不走索引。

1.2、在InnoDB引擎是如何存储NULL值的？

InnoDB引擎通过使用一个特殊的值来表示null，这个值通常被称为"null bitmap"。null bitmap是一个二进制位序列，用来标记表中每一个列是否为null。当null bitmap中对应的位为1时，表示对应的列为null；当null bitmap中对应的位为0时，表示对应的列不为null。在实际存储时，InnoDB引擎会将null bitmap作为行记录的一部分，存储在行记录的开头，这样可以在读取行记录时快速判断每个列是否为null。

当我们创建表的时候默认会创建一个*.ibd 文件，这个文件又称为独占表空间文件，它是由段、区、页、行组成。InnoDB存储引擎独占表空间大致如下图；

image_mqx_FlMZPq

Segment(表空间) 是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

数据段存放 B + 树的叶子节点的区的集合
索引段存放 B + 树的非叶子节点的区的集合
回滚段存放的是回滚数据的区的集合， MVCC就是利用了回滚段实现了多版本查询数据

Extent(区) 在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

（我们知道 InnoDB 存储引擎是用 B+ 树来组织数据的。B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。）

Page(页) 记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

因此，InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。

默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。

页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

页的类型有很多，常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的，数据页的结构这里我就不讲细说了，总之知道表中的记录存储在「数据页」里面就行。

Row(行) 数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。

Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，现在基本没人用了，那就不展开详讲了。
MySQL 5.0 之后引入了 Compact 行记录存储方式，由于 Redundant 不是一种紧凑的行格式，而采用更为紧凑的Compact ，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。
Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。

那么我们来看看Compact里面长什么样，先混个脸熟。

image_4g6C5DHCoH

这里简单介绍一下:

NULL值列表（本问题介绍重点）

表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。
二进制位的值为1时，代表该列的值为NULL。二进制位的值为0时，代表该列的值不为NULL。另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。
当然NULL 值列表也不是必须的。当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以节省 1 字节的空间（NULL 值列表占用 1 字节空间）。
「NULL 值列表」的空间不是固定 1 字节的。当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。

1.2、索引是如何存储NULL值的？

我们知道InnoDB引擎中按照物理存储的不同分为聚簇索引和非聚簇索引，聚簇索引也就是主键索引，那么是不允许为空的。那就不再我们本问题的讨论范围，我们重点来看看非聚簇索引，非聚簇索引是允许值为空的。

在InnoDB中非聚簇索引是通过B+树的方式进行存储的

从图中可以看出，对于s1表的二级索引idx_key1来说，值为NULL的二级索引记录都被放在了B+树的最左边，这是因为设计InnoDB有这样的规定：

We define the SQL null to be the smallest possible value of a field.

也就是说他们把SQL中的NULL值认为是列中最小的值。在通过二级索引idx_key1对应的B+树快速定位到叶子节点中符合条件的最左边的那条记录后，也就是本例中id值为521的那条记录之后，就可以顺着每条记录都有的next_record属性沿着由记录组成的单向链表去获取记录了，直到某条记录的key1列不为NULL。

我们了解了上面的两个问题之后，我们就可以来看看，使不使用索引的依据是什么了

实际上来说我们用is null is not null ≠ 这些条件都是能走索引的，那什么时候走索引什么时候走全表扫描呢？

总结起来就是两个字：成本！！！

如何去度量成本计算使用某个索引执行查询的成本就非常复杂了，这里总结性讲讲：第一个，读取二级索引记录的成本，第二，将二级索引记录执行回表操作，也就是到聚簇索引中找到完整的用户记录操作所付出的成本。

要扫描的二级索引记录条数越多，那么需要执行的回表操作的次数也就越多，达到了某个比例时，使用二级索引执行查询的成本也就超过了全表扫描的成本（举一个极端的例子，比方说要扫描的全部的二级索引记录，那就要对每条记录执行一遍回表操作，自然不如直接扫描聚簇索引来的快）

所以MySQL优化器在真正执行查询之前，对于每个可能使用到的索引来说，都会预先计算一下需要扫描的二级索引记录的数量，比方说对于下边这个查询：

SELECT * FROM s1 WHERE key1 IS NULL;

优化器会分析出此查询只需要查找key1值为NULL的记录，然后访问一下二级索引idx_key1，看一下值为NULL的记录有多少（如果符合条件的二级索引记录数量较少，那么统计结果是精确的，如果太多的话，会采用一定的手段计算一个模糊的值，当然算法也比较麻烦，我们就不展开说了），这种在查询真正执行前优化器就率先访问索引来计算需要扫描的索引记录数量的方式称之为index dive。当然，对于某些查询，比方说WHERE子句中有IN条件，并且IN条件中包含许多参数的话，比方说这样：

SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');

这样的话需要统计的key1值所在的区间就太多了，这样就不能采用index dive的方式去真正的访问二级索引idx_key1，而是需要采用之前在背地里产生的一些统计数据去估算匹配的二级索引记录有多少条（很显然根据统计数据去估算记录条数比index dive的方式精确性差了很多）。

反正不论采用index dive还是依据统计数据估算，最终要得到一个需要扫描的二级索引记录条数，如果这个条数占整个记录条数的比例特别大，那么就趋向于使用全表扫描执行查询，否则趋向于使用这个索引执行查询。

理解了这个也就好理解为什么在WHERE子句中出现IS NULL、IS NOT NULL、!=这些条件仍然可以使用索引，本质上都是优化器去计算一下对应的二级索引数量占所有记录数量的比值而已。

大家可以看到，MySQL中决定使不使用某个索引执行查询的依据很简单：就是成本够不够小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件。大家以后也多多辟谣吧，没那么复杂，只是一个成本而已。

2、为什么LIKE以%开头索引会失效？

首先看看B+树是如何查找数据的：

查找数据时，MySQL会从根节点开始，按照从左到右的顺序比较查询条件和节点中的键值。如果查询条件小于节点中的键值，则跳到该节点的左子节点继续查找；如果查询条件大于节点中的键值，则跳到该节点的右子节点继续查找；如果查询条件等于节点中的键值，则继续查找该节点的下一个节点。

比如说我有下面这条SQL：

select * from `user` where nickname like '%笑';

如果数据库中存在大笑偷笑傻笑狂笑，那么在B+树中搜索的效率和全表扫描还有什么区别呢？

我走聚簇索引全表扫描还不用回表。

在这里插入图片描述