索引失效场景分析

索引失效会导致查询性能急剧下降，需识别并避免以下场景。

索引列运算

在索引列上运算

-- ❌ 索引失效：索引列参与运算
SELECT * FROM orders WHERE id + 1 = 100;
SELECT * FROM orders WHERE id * 2 = 200;
SELECT * FROM orders WHERE id - 10 = 90;

-- ✅ 索引有效：运算移到右侧
SELECT * FROM orders WHERE id = 100 - 1;
SELECT * FROM orders WHERE id = 200 / 2;
SELECT * FROM orders WHERE id = 90 + 10;

执行计划验证

EXPLAIN SELECT * FROM orders WHERE id + 1 = 100;
-- type: ALL（全表扫描）
-- key: NULL（未使用索引）

EXPLAIN SELECT * FROM orders WHERE id = 99;
-- type: const
-- key: PRIMARY

函数使用

在索引列上使用函数

-- ❌ 索引失效：索引列使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-05-15';
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
SELECT * FROM orders WHERE UPPER(name) = 'ZHANG';
SELECT * FROM orders WHERE SUBSTRING(phone, 1, 3) = '138';

-- ✅ 索引有效：避免函数或改写条件
SELECT * FROM orders WHERE created_at >= '2026-05-15' AND created_at < '2026-05-16';
SELECT * FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31';
SELECT * FROM orders WHERE name = 'zhang' COLLATE utf8_general_ci;
SELECT * FROM orders WHERE phone LIKE '138%';

类型隐式转换

字符列用数字查询

-- phone字段为VARCHAR类型
CREATE TABLE users (
    id INT PRIMARY KEY,
    phone VARCHAR(20),
    INDEX idx_phone(phone)
);

-- ❌ 索引失效：字符列用数字查询
SELECT * FROM users WHERE phone = 13800000000;
-- 隐式转换：phone = CAST(13800000000 AS VARCHAR)
-- 索引失效

-- ✅ 索引有效：类型一致
SELECT * FROM users WHERE phone = '13800000000';

执行计划对比

EXPLAIN SELECT * FROM users WHERE phone = 13800000000;
-- type: ALL
-- key: NULL
-- Warning: Cannot use index 'idx_phone' due to type conversion

EXPLAIN SELECT * FROM users WHERE phone = '13800000000';
-- type: ref
-- key: idx_phone

LIKE左模糊

模糊匹配场景

CREATE INDEX idx_name ON users(name);

-- ✅ 索引有效：右模糊（前缀匹配）
SELECT * FROM users WHERE name LIKE '张%';

-- ✅ 索引有效：全匹配
SELECT * FROM users WHERE name LIKE '张三';

-- ❌ 索引失效：左模糊
SELECT * FROM users WHERE name LIKE '%张';

-- ❌ 索引失效：双模糊
SELECT * FROM users WHERE name LIKE '%张%';

解决方案

-- 1. 使用全文索引
CREATE FULLTEXT INDEX ft_name ON users(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张');

-- 2. 使用覆盖索引减少回表开销
CREATE INDEX idx_id_name ON users(id, name);
SELECT id, name FROM users WHERE name LIKE '%张%';

-- 3. 数据量小时可接受全表扫描

OR连接非索引列

OR导致索引失效

CREATE INDEX idx_user ON orders(user_id);

-- ❌ 索引失效：OR连接非索引列
SELECT * FROM orders WHERE user_id = 1 OR amount > 100;
-- amount无索引，整体索引失效

-- ✅ 索引有效：两列都有索引
CREATE INDEX idx_amount ON orders(amount);
SELECT * FROM orders WHERE user_id = 1 OR amount > 100;
-- 可使用索引合并（Index Merge）

-- ✅ 改写为UNION ALL
SELECT * FROM orders WHERE user_id = 1
UNION ALL
SELECT * FROM orders WHERE amount > 100;

联合索引不满足最左前缀

最左前缀原则

CREATE INDEX idx_a_b_c ON t1(a, b, c);

-- ✅ 索引有效
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3  -- 使用索引(a)，c在索引中但跳跃

-- ❌ 索引失效
WHERE b = 2           -- 无最左列a
WHERE c = 3           -- 无最左列a
WHERE b = 2 AND c = 3 -- 无最左列a

范围查询中断

-- ✅ 索引有效：范围查询前的列
WHERE a = 1 AND b > 2 AND c = 3
-- 使用索引(a, b)，c索引失效

-- ✅ 改写避免范围查询在中间列
CREATE INDEX idx_a_c_b ON t1(a, c, b);
WHERE a = 1 AND c = 3 AND b > 2
-- 索引全部有效

NULL值查询

NULL值索引使用

-- InnoDB索引可包含NULL值
CREATE INDEX idx_status ON orders(status);

-- ✅ 索引有效
SELECT * FROM orders WHERE status IS NULL;
SELECT * FROM orders WHERE status = 1;

-- ⚠️ 可能索引失效（全表扫描效率更高时）
SELECT * FROM orders WHERE status IS NOT NULL;
-- 优化器可能选择全表扫描

NOT IN和NOT EXISTS

NOT操作符影响

CREATE INDEX idx_status ON orders(status);

-- ❌ 可能索引失效
SELECT * FROM orders WHERE status NOT IN (1, 2);
SELECT * FROM orders WHERE status != 1;

-- ✅ 改写为IN
SELECT * FROM orders WHERE status IN (0, 3, 4);

-- ⚠️ NOT EXISTS可能更好
SELECT o.* FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM status_filter s WHERE o.status = s.id);

低选择性索引

优化器放弃索引

-- status只有3种值，选择性极低
CREATE INDEX idx_status ON orders(status);

SELECT * FROM orders WHERE status = 1;
-- 若status=1占50%记录，优化器可能放弃索引
-- 全表扫描效率更高

-- ✅ 改写查询提高选择性
SELECT * FROM orders WHERE status = 1 AND user_id = 100;
CREATE INDEX idx_status_user ON orders(status, user_id);

索引失效诊断方法

-- 1. 查看执行计划
EXPLAIN SELECT ...;
-- 关注 type 和 key 列

-- 2. 查看索引使用统计
SELECT
    TABLE_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage;

-- 3. 使用EXPLAIN ANALYZE（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT ...;

要点总结

• 索引列运算、函数使用导致索引失效
• 类型不一致触发隐式转换，索引失效
• LIKE左模糊匹配无法使用索引
• OR连接非索引列导致整体失效
• 联合索引必须满足最左前缀原则
• 范围查询后的列索引失效
• 低选择性时优化器可能放弃索引
• 使用EXPLAIN诊断索引使用情况