MySQL 索引失效的常见场景与排查方法

索引是 MySQL 性能优化的基石，但很多开发者都遇到过“明明建了索引，查询却依然很慢”的情况。这通常意味着索引没有被优化器选中，也就是我们常说的**索引失效**。理解索引失效的典型场景，并掌握一套可落地的排查方法，是从“会建索引”到“会用索引”的关键一步。

## 一、为什么索引会失效？

MySQL 优化器在选择执行计划时，会基于成本估算来决定是否使用索引。当它认为全表扫描的代价更低，或者索引无法有效缩小数据范围时，就会放弃索引。导致这一判断的常见原因包括：对索引列做了额外运算、隐式类型转换、不满足最左前缀原则、数据区分度太低等。

## 二、常见索引失效场景

### 1. 在索引列上使用函数或表达式

这是最容易被忽视的一类问题。只要在索引列上做了函数调用或算术运算，索引就很可能失效。

“`sql
— 失效：对索引列使用函数
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

— 优化：改为范围查询
SELECT * FROM orders
WHERE created_at >= ‘2024-01-01’ AND created_at < '2025-01-01'; ``` ### 2. 隐式类型转换 当索引列是字符串类型，而查询条件传入的是数字时，MySQL 会进行隐式类型转换，相当于对索引列使用了函数，导致索引失效。 ```sql -- phone 是 varchar 类型，传入数字会触发隐式转换 SELECT * FROM users WHERE phone = 13800138000; -- 正确写法 SELECT * FROM users WHERE phone = '13800138000'; ``` ### 3. 不满足最左前缀原则 对于联合索引 `(a, b, c)`，查询条件必须从最左列开始连续匹配，否则无法有效利用索引。 ```sql -- 索引 (a, b, c) SELECT * FROM t WHERE b = 1; -- 失效 SELECT * FROM t WHERE a = 1 AND c = 2; -- 只用到 a SELECT * FROM t WHERE a = 1 AND b = 2; -- 正常使用 ``` ### 4. 使用 `LIKE` 以通配符开头 ```sql SELECT * FROM products WHERE name LIKE '%手机%'; -- 失效 SELECT * FROM products WHERE name LIKE '手机%'; -- 可用 ``` ### 5. 使用 `OR` 连接非索引列 如果 `OR` 两侧的列并非全部都有索引，优化器可能直接选择全表扫描。 ```sql -- 假设 age 无索引 SELECT * FROM users WHERE name = 'Tom' OR age = 20; ``` ### 6. 使用 `!=`、`NOT IN`、`NOT EXISTS` 这类否定条件通常无法有效利用索引，尤其是当符合条件的记录占比较大时。 ### 7. 索引列区分度太低 如果某个列的值重复度极高（如性别、状态标志），优化器可能认为走索引不如全表扫描。 ### 8. 数据量过小 当表中数据只有几百行时，全表扫描的成本可能低于走索引，优化器会直接选择全表扫描。这属于正常行为，无需过度优化。 ## 三、排查方法 ### 1. 使用 `EXPLAIN` 分析执行计划 这是最核心的排查手段。重点关注以下字段： - **type**：`ALL` 表示全表扫描，`ref`、`range`、`eq_ref` 等表示使用了索引。 - **key**：实际使用的索引名称，为 `NULL` 则未使用索引。 - **rows**：预估扫描行数，数值越大越需要警惕。 - **Extra**：出现 `Using filesort`、`Using temporary` 通常意味着性能问题。 ```sql EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024; ``` ### 2. 使用 `EXPLAIN ANALYZE`（MySQL 8.0+） 相比 `EXPLAIN`，它会实际执行查询并给出真实的耗时与行数，能更准确地定位问题。 ### 3. 开启慢查询日志 通过 `slow_query_log` 捕获执行时间超过阈值的 SQL，再结合 `EXPLAIN` 逐一分析。 ### 4. 使用 `SHOW INDEX` 查看索引结构 确认索引的列顺序、基数（Cardinality）等信息，判断是否符合预期。 ```sql SHOW INDEX FROM orders; ``` ### 5. 强制使用索引进行对比验证 在排查阶段，可以用 `FORCE INDEX` 强制走索引，对比执行时间，验证索引本身是否有效。 ```sql SELECT * FROM orders FORCE INDEX (idx_created_at) WHERE created_at >= ‘2024-01-01’;
“`

## 四、总结

索引失效并不可怕，可怕的是不知道它为什么失效。日常开发中应养成两个习惯：一是写 SQL 时避免在索引列上做运算和隐式转换；二是对关键查询定期用 `EXPLAIN` 审查执行计划。把索引失效的常见场景当作一份检查清单，遇到慢查询时逐条比对，往往能快速定位问题根源。

未经允许不得转载：任鹏个人博客 » MySQL 索引失效的常见场景与排查方法