MySQL索引失效的几种情况

一、如何判定索引实效

• 索引实效本质上是 MYSQL 查询优化器判断使用索引的查询成本高于全表扫描，或者无法通过索引快速定位到要查询的数据，故而放弃使用索引，执行了全表扫描。
• 使用explain 分析查询语句，可以用于判断查询语句是否使用到索引，重点关注 type 和 key 两个字段，type 字段表示索引类型，key 字段表示索引列。

二、Explain 分析结果字段说明

使用 explain 分析查询语句会返回一些字段，下面是对字段的解释

可以使用 select version() 查询数据库版本

假定执行的sql语句为：

explain SELECT * FROM authentication.tokens where id = 1;

你会得到如下的结果

• select-type : 查询类型，有以下几种类型
  • SIMPLE : 简单查询，未使用 union 或子查询
  • PRIMARY : 最外层的查询
  • SUBQUERY : 子查询
  • DEPENDENT SUBQUERY : 依赖子查询，依赖于外层查询
  • DERIVED : 用来表示包含在 from 子句的子查询中的 select ，mysql 会递归执行并将结果放到临时表中，mysql 内部称其为派生表(derived table)，因为该表是从子查询中派生出来的
  • DEPENDENT DERIVED : 派生表，依赖了其他表
  • MATERIALIZED : 物化视图
  • UNION : 在 union 中的第二个和随后的select，如果 union 被 from 字句中的资查询包含，则它的第一个 select 为 DERIVED
  • DEPENDENT UNION : union 中的第二个或后面的查询，依赖于外层查询
  • UNION RESULT : union 的结果集
  • UNCACHEABLE SUBQUERY : 无法缓存的子查询，子查询的结果不能被缓存
  • UNCACHEABLE UNION : 无法缓存的联合查询，联合查询的结果不能被缓存
• tabel : 表名，表示当前这一行正在访问哪张表，如果SQL定义了别名，则展示表的别名
• partitions : 当前查询匹配记录的分区。对于未分区的表，返回null
• type : 连接类型，以下性能从好到坏
  • system : 该表只有一行，相当于系统表
  • const : 该表中所有索引列都包含了常量，查询时最多匹配一行
  • eq_ref : 在连接查询中，对于前一个表的每一行，在后一个表中只有一行数据与之匹配。通常出现在使用主键或唯一非空索引进行表连接的查询中
  • ref : 区别于 eq_ref，ref 类型的连接查询会返回多行匹配的数据。这通常发生在非唯一性索引的等值查询中
  • range : 表示在一个给定的范围内进行索引扫描，而不是扫描整个索引
  • index : 全索引扫描，与 ALL 的区别在于 index 类型只遍历索引树。虽然也扫描了整个索引，但通常比全表扫描快，因为索引文件通常比数据文件小
  • ALL : 全表扫描，MySQL 需要从头到尾扫描整个表来找到匹配的行
• possible_keys : 展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的
• key : 表示MySQL实际选择的索引
• key_len : 索引使用的字节数。由于存储格式，当字段允许为NULL时，key_len比不允许为空时大1字节
• ref : 数据库是用什么（常量、列、函数）来和 key 列中选用的索引进行比较，从而选出记录的，常见的有以下几种情况
  • const ： 示查询条件中，索引列是与一个常量值进行比较的
  • 数据库名.表名.列名 : 表示查询条件中，索引列是与另一个表的列进行比较的。这通常出现在 JOIN 操作中
  • func : 表示索引列是与一个函数的结果或类型转换后的结果进行比较
  • NULL : 表示没有使用索引进行具体的等值查找（Range 扫描或全表扫描
• rows : MySQL估算会扫描的行数，数值越小越好
• filtered : 这是一个百分比（0.00% ~ 100.00%）。它表示存储引擎返回的数据，经过 Server 层过滤后，剩余记录占读取记录的比例
  • 计算公式：有效数据 / 扫描到的数据 * 100%
  • 用于评估索引的精准度，百分比越大，索引的效率越高
• Extra : 包含了 MySQL 执行查询时的详细动作，结果可以分为三大类，下面是详细说明
  • ✅ 好：
    • Using index : 查询需要的所有列都在索引树上找到了，不需要回表（即不需要去查数据行）
  • ⭕ 中：
    • Using where : 存储引擎将数据读出来后，Server 层根据 WHERE 条件又进行了一次过滤
    • Using index condition : MySQL 的优化特性。它把部分 WHERE 条件“下推”给存储引擎，在引擎层就过滤掉不符合索引条件的数据，减少回表次数
  • ❌ 坏：
    • Using filesort : 文件排序，查询包含 ORDER BY，但无法利用索引完成排序，MySQL 必须在内存（或磁盘）中进行额外的排序操作
    • Using temporary : 临时表，MySQL 需要创建一张内部临时表来处理查询。常见于 GROUP BY 或 DISTINCT 操作，且列没有索引时
  • 其他情况：
    • Select tables optimized away : 使用聚合函数（如 MAX(), MIN()）直接访问索引，甚至不需要查表，速度极快
    • NULL : 如果使用了主键或唯一索引查找，Extra 可能是空的

三、索引实效的情况

假设有一张 staff 表，如下定义：

CREATE TABLE staff (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(24) NOT NULL,
    age INT NOT NULL,
    pos VARCHAR(20) NOT NULL,
    phone VARCHAR(20),
    INDEX idx_name_age_pos (name, age, pos),
    INDEX idx_phone (phone)
);

插入一条数据

INSERT into staff(`name`,age,pos,phone) value("张三",30,"总经理","13111112222");

查看数据

select * from staff;

1. 违反 最左匹配原则 。

如果索引了多列，要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列

• 失效sql：

  EXPLAIN SELECT * FROM staff WHERE age = 25;

• 部分失效sql：

  EXPLAIN SELECT * FROM staff WHERE name = '张三' AND pos = '总经理';

• 有效sql：

  EXPLAIN SELECT * FROM staff WHERE name = '张三' and age = 25;

2. 在 索引列上操作。

如索引列上进行计算、函数、自动或手动类型转换

• 失效sql：

  EXPLAIN SELECT * FROM staff WHERE LEFT(name, 1) = '张';
  EXPLAIN SELECT * FROM staff WHERE phone = 13800001234

• 有效sql：

  EXPLAIN SELECT * FROM staff WHERE nam like '张%';
  EXPLAIN SELECT * FROM staff WHERE phone = '13800001234';

3. 使用 不等于 （!= 或 <>等）。

在某些版本或特定数据量下，使用了不等号会导致索引失效。等于某个值可以快速定位，但不等于某个值，通常意味着要取出一大部分数据，优化器可能认为全表扫描更快

• 可能失效sql：

  EXPLAIN SELECT * FROM staff WHERE name != '张三';

4. 使用 or 。

如果 OR 连接的条件中，有一个字段没有索引，那么涉及的所有索引都不会被用到

• 失效sql：

   EXPLAIN SELECT * FROM staff WHERE name = '张三' OR age = 25;

• 优化方案：
  • 给OR条件中的字段加索引
  • 使用 union 代替 or 连接条件

5. like查询 是以 '%'开头 。’

索引失效，原因是like查询是以'%'开头，B+树是按照顺序排列的，后缀匹配无法利用有序性。

• 失效sql：

    EXPLAIN SELECT * FROM staff WHERE name LIKE '%张';

• 有效sql：

  EXPLAIN SELECT * FROM staff WHERE name LIKE '张%';

6. 使用 <,>、between、and。

在联合索引中，如果遇到范围查询（>, <, BETWEEN, LIKE 'a%'），则范围条件右边的列无法用到索引，存储引擎不能使用索引中范围条件右边的列，无法重建索引

• 部分失效 SQL：

  EXPLAIN SELECT * FROM staff WHERE name = '张三' AND age > 25 AND pos = '董事长';

7. IS NULL 和 IS NOT NULL

不同版本的 MySQL 中表现不同，但通常建议尽量避免允许 NULL 值。

• IS NOT NULL 通常会触发全表扫描
• 如果表中绝大多数数据都是 NULL，那么查 IS NOT NULL 可能会走索引。反之亦然。这取决于数据分布。

8. 使用 select 。

索引失效，原因是 select 语句可能会导致 MySQL 优化器放弃使用索引，执行全表扫描。

• 尽可能只查询需要的字段，避免 select *

四、 小口诀

全值匹配我最爱，最左前缀要遵守；
      带头大哥不能死，中间兄弟不能断；
      索引列上少计算，范围之后全失效；
      LIKE百分写最右，覆盖索引不写星；
      不等空值还有OR，索引失效要唯心（看优化器心情）。