索引失效场景分析

引言

索引失效是 SQL 性能问题的常见原因之一。当索引失效时，MySQL 会被迫进行全表扫描，导致查询性能急剧下降。理解索引失效的场景并掌握避免方法，是编写高效 SQL 的关键技能。

本章将详细讲解：

• 索引失效的常见场景
• 使用 EXPLAIN 诊断索引失效
• 避免索引失效的最佳实践
• 实际案例分析与优化

核心概念

什么是索引失效

索引失效是指 MySQL 查询优化器在执行查询时，没有使用预期的索引，而是选择了全表扫描（ALL）或其他低效的访问方式。

诊断方法：

EXPLAIN SELECT * FROM users WHERE name = '张三';
-- 查看 key 列：如果为 NULL，表示未使用索引
-- 查看 type 列：如果为 ALL，表示全表扫描

索引失效的影响

结论： 数据量越大，索引失效的影响越严重！

索引失效常见场景

场景 1：在索引列上使用函数

错误示例：

-- 假设 create_time 有索引
SELECT * FROM orders WHERE YEAR(create_time) = 2024;
-- ❌ 索引失效，全表扫描

SELECT * FROM users WHERE DATE(create_time) = '2024-01-01';
-- ❌ 索引失效

SELECT * FROM users WHERE SUBSTRING(phone, 1, 3) = '138';
-- ❌ 索引失效

优化方案：

-- ✅ 使用范围查询
SELECT * FROM orders 
WHERE create_time >= '2024-01-01 00:00:00' 
  AND create_time < '2025-01-01 00:00:00';

-- ✅ 使用日期范围
SELECT * FROM users 
WHERE create_time >= '2024-01-01 00:00:00' 
  AND create_time < '2024-01-02 00:00:00';

-- ✅ 使用 LIKE（最左匹配）
SELECT * FROM users WHERE phone LIKE '138%';

验证：

EXPLAIN SELECT * FROM orders 
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
-- key: idx_create_time
-- type: range

场景 2：隐式类型转换

错误示例：

-- 假设 phone 是 VARCHAR 类型且有索引
SELECT * FROM users WHERE phone = 13800138000;
-- ❌ 索引失效！数字会触发隐式转换

-- 假设 user_id 是 BIGINT 类型且有索引
SELECT * FROM orders WHERE user_id = '100';
-- ⚠️ 可能索引失效（取决于 MySQL 版本）

优化方案：

-- ✅ 使用正确的数据类型
SELECT * FROM users WHERE phone = '13800138000';
-- ✅ 字符串列使用字符串值

SELECT * FROM orders WHERE user_id = 100;
-- ✅ 数字列使用数字值

原理分析：

-- 隐式转换相当于
SELECT * FROM users WHERE CAST(phone AS SIGNED) = 13800138000;
-- 对索引列使用函数，导致索引失效

最佳实践：

• 应用层确保参数类型与数据库列类型一致
• 使用预处理语句（Prepared Statement）避免类型问题
• 代码审查时注意字符串拼接的 SQL

场景 3：LIKE 以通配符开头

错误示例：

-- 假设 name 有索引
SELECT * FROM users WHERE name LIKE '%张%';
-- ❌ 索引失效，全表扫描

SELECT * FROM users WHERE name LIKE '%三';
-- ❌ 索引失效

优化方案：

-- ✅ 最左匹配（可以使用索引）
SELECT * FROM users WHERE name LIKE '张%';

-- ✅ 使用全文索引（适用于长文本）
ALTER TABLE users ADD FULLTEXT INDEX ft_name (name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张三');

-- ✅ 使用覆盖索引 + 过滤
SELECT id FROM users WHERE name LIKE '张%';  -- 使用索引
-- 然后在应用层进一步过滤

特殊情况：

-- ✅ 覆盖索引场景（无需回表）
SELECT name FROM users WHERE name LIKE '%张%';
-- 如果 name 列有索引，可能使用 Using index

场景 4：联合索引不满足最左前缀原则

错误示例：

-- 联合索引：idx_name_age_city (name, age, city)
SELECT * FROM users WHERE age = 25;
-- ❌ 索引失效（跳过第 1 列 name）

SELECT * FROM users WHERE city = '北京';
-- ❌ 索引失效（跳过前两列）

SELECT * FROM users WHERE name = '张三' AND city = '北京';
-- ⚠️ 部分使用索引（只用 name 列，city 用不到）

优化方案：

-- ✅ 包含最左列
SELECT * FROM users WHERE name = '张三';

-- ✅ 按顺序使用
SELECT * FROM users WHERE name = '张三' AND age = 25;

-- ✅ 使用全部列
SELECT * FROM users WHERE name = '张三' AND age = 25 AND city = '北京';

设计建议：

-- 如果查询模式多样，考虑创建多个索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_name_age ON users(name, age);

-- 或者使用索引合并（Index Merge）
-- MySQL 可以同时使用多个单列索引

场景 5：OR 连接非索引列

错误示例：

-- 假设 user_id 有索引，但 status 无索引
SELECT * FROM orders WHERE user_id = 100 OR status = 1;
-- ❌ 索引失效，全表扫描（因为 status 无索引）

优化方案：

-- ✅ 为 OR 两边的列都创建索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
SELECT * FROM orders WHERE user_id = 100 OR status = 1;
-- ✅ 可能使用 Index Merge

-- ✅ 使用 UNION 改写
SELECT * FROM orders WHERE user_id = 100
UNION
SELECT * FROM orders WHERE status = 1;

-- ✅ 如果业务允许，改为 AND
SELECT * FROM orders WHERE user_id = 100 AND status = 1;
-- ✅ 索引有效

场景 6：不等于操作（!= 或 <>）

错误示例：

-- 假设 age 有索引
SELECT * FROM users WHERE age != 18;
-- ⚠️ 可能索引失效（取决于数据分布）

SELECT * FROM users WHERE status <> 1;
-- ⚠️ 可能索引失效

优化方案：

-- ✅ 改为范围查询
SELECT * FROM users WHERE age < 18 OR age > 18;

-- ✅ 使用 IN 列举所有可能值
SELECT * FROM users WHERE age IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

-- ✅ 如果数据分布特殊，考虑覆盖索引
SELECT age FROM users WHERE age != 18;
-- 可能使用 Using index

注意： 不等于操作是否使用索引取决于：

• 数据分布（选择性）
• MySQL 优化器的成本估算
• 表的大小

场景 7：IS NULL / IS NOT NULL

错误示例：

-- 假设 email 有索引，但允许 NULL
SELECT * FROM users WHERE email IS NULL;
-- ⚠️ 可能索引失效（取决于 NULL 值比例）

SELECT * FROM users WHERE email IS NOT NULL;
-- ⚠️ 可能索引失效（如果非 NULL 值占大多数）

优化方案：

-- ✅ 设计时避免 NULL，使用默认值
ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL DEFAULT '';
SELECT * FROM users WHERE email = '';

-- ✅ 如果必须有 NULL，创建专门的索引
CREATE INDEX idx_email_null ON users(email);
-- NULL 值会被存储在索引中

场景 8：范围查询后的列无法使用索引

错误示例：

-- 联合索引：idx_name_age_city (name, age, city)
SELECT * FROM users 
WHERE name = '张三' AND age > 20 AND city = '北京';
-- ⚠️ name 和 age 使用索引，city 无法使用（范围查询后停止）

优化方案：

-- ✅ 调整索引列顺序（将范围查询列放在最后）
CREATE INDEX idx_name_city_age ON users(name, city, age);
SELECT * FROM users 
WHERE name = '张三' AND city = '北京' AND age > 20;
-- ✅ 三列都可以使用索引

-- ✅ 使用覆盖索引
SELECT name, age, city FROM users 
WHERE name = '张三' AND age > 20 AND city = '北京';
-- ✅ 无需回表

场景 9：ORDER BY 与索引不一致

错误示例：

-- 索引：idx_create_time (create_time)
SELECT * FROM orders 
ORDER BY create_time DESC, id ASC;
-- ❌ 无法使用索引排序（排序方向不一致）

-- 索引：idx_name_age (name, age)
SELECT * FROM users 
WHERE name = '张三' 
ORDER BY age DESC;
-- ⚠️ 可能无法使用索引排序（如果有 WHERE 条件）

优化方案：

-- ✅ 排序方向与索引一致
SELECT * FROM orders 
ORDER BY create_time DESC, id DESC;

-- ✅ 创建匹配的索引
CREATE INDEX idx_name_age_desc ON users(name, age DESC);
SELECT * FROM users 
WHERE name = '张三' 
ORDER BY age DESC;

-- ✅ 使用 LIMIT 优化
SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 10;
-- 即使有 filesort，数据量小也很快

场景 10：数据分布导致优化器选择全表扫描

特殊情况：

-- 假设 status 列有索引，但 99% 的数据 status=1
SELECT * FROM orders WHERE status = 1;
-- ⚠️ 优化器可能选择全表扫描（因为大部分数据都符合条件）

SELECT * FROM orders WHERE status = 0;
-- ✅ 可能使用索引（因为只有 1% 的数据）

验证方法：

-- 查看数据分布
SELECT status, COUNT(*) 
FROM orders 
GROUP BY status;

-- 强制使用索引
SELECT * FROM orders FORCE INDEX(idx_status) WHERE status = 1;

-- 分析执行计划
EXPLAIN SELECT * FROM orders WHERE status = 1;
-- 查看 rows 列的估算值

优化方案：

-- ✅ 如果查询高频，考虑拆分表
-- 将 status=0 的 rare 数据单独存放

-- ✅ 使用覆盖索引
SELECT id FROM orders WHERE status = 1;

-- ✅ 接受优化器的选择
-- 如果全表扫描更快，不必强求使用索引

使用 EXPLAIN 诊断索引失效

EXPLAIN 关键字段

EXPLAIN SELECT * FROM users WHERE name = '张三';

诊断流程

graph TD
    A[执行 EXPLAIN] --> B{key 是否为 NULL?}
    B -->|是 | C[索引失效]
    B -->|否 | D[索引有效]
    
    C --> E{type 是否为 ALL?}
    E -->|是 | F[全表扫描，需优化]
    E -->|否 | G[部分扫描，可接受]
    
    D --> H{Extra 是否有 Using index?}
    H -->|是 | I[覆盖索引，最优]
    H -->|否 | J[需要回表]
    
    F --> K[检查索引失效场景]
    K --> L[优化 SQL 或索引设计]

实战示例

-- 示例 1：函数导致索引失效
EXPLAIN SELECT * FROM orders WHERE YEAR(create_time) = 2024;
-- type: ALL, key: NULL, Extra: Using where
-- 优化：改为范围查询

-- 示例 2：覆盖索引
EXPLAIN SELECT id, name FROM users WHERE name = '张三';
-- type: ref, key: idx_name, Extra: Using index
-- 最优情况

-- 示例 3：部分使用索引
EXPLAIN SELECT * FROM users WHERE name = '张三' AND city = '北京';
-- type: ref, key: idx_name, Extra: Using where
-- 只有 name 使用了索引

注意事项

1. 不要盲目优化

-- 小表不需要索引优化
SELECT * FROM small_table WHERE status = 1;
-- 如果 small_table 只有 100 行，全表扫描也很快

-- 先分析再优化
SELECT COUNT(*) FROM table_name;
SHOW TABLE STATUS LIKE 'table_name';

2. 相信优化器

MySQL 优化器通常能做出正确的选择：

-- 优化器可能选择全表扫描（如果更快）
EXPLAIN SELECT * FROM large_table WHERE status = 1;
-- 如果 90% 数据 status=1，全表扫描可能更快

-- 不要强制使用索引，除非有充分理由
SELECT * FROM large_table FORCE INDEX(idx_status) WHERE status = 1;

3. 定期分析表

-- 更新统计信息，帮助优化器做出正确选择
ANALYZE TABLE users;

-- 检查表健康度
CHECK TABLE users;

4. 监控慢查询

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询

-- 分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

总结

索引失效检查清单

• 是否在索引列上使用了函数
• 是否存在隐式类型转换
• LIKE 是否以通配符开头
• 联合索引是否满足最左前缀原则
• OR 连接的列是否都有索引
• 是否使用了 != 或 <> 操作
• 范围查询后是否还有其他列查询
• ORDER BY 是否与索引一致

优化原则

1. 预防优先：编写 SQL 时避免索引失效场景
2. 诊断先行：使用 EXPLAIN 分析执行计划
3. 数据驱动：根据实际数据分布选择优化策略
4. 平衡取舍：在查询性能和写入性能之间找到平衡

下一步

掌握索引失效分析后，下一章我们将学习：

• EXPLAIN 执行计划的详细解读
• 各字段的含义和优化方向
• 实际案例的性能调优

参考资料

• MySQL 官方文档 - EXPLAIN
• 《高性能 MySQL》第 4 章：查询性能优化
• MySQL Index Optimization
• MySQL 优化器工作原理