一、索引失效的常见场景
咱们先聊聊那些让MySQL索引"罢工"的典型场景。就像你拿着地图却找不到路一样，索引失效时数据库也得全表扫描，性能直接跌入谷底。
最常见的就是在查询条件中使用函数操作。比如你想按用户名的首字母查询：
-- 错误示范：对索引列使用函数会导致索引失效
SELECT * FROM users WHERE LEFT(username, 1) = '张';
-- 正确做法：使用前缀查询
SELECT * FROM users WHERE username LIKE '张%';
另一个经典场景是隐式类型转换。当比较的字段类型不匹配时，MySQL会自动做类型转换，这会让索引失效：
-- 错误示范：user_id是整型但用字符串比较
SELECT * FROM orders WHERE user_id = '12345';
-- 正确做法：保持类型一致
SELECT * FROM orders WHERE user_id = 12345;
二、复合索引的使用陷阱
复合索引就像多列组合的快捷通道，但用不好反而会适得其反。最典型的问题是违反最左前缀原则：
-- 假设有复合索引 (department, position)
-- 错误示范：跳过了department字段
SELECT * FROM employees WHERE position = '工程师';
-- 正确做法：从最左列开始使用
SELECT * FROM employees WHERE department = '研发部' AND position = '工程师';
还有个常见错误是在复合索引列上使用范围查询后，后续列无法使用索引：
-- 假设有复合索引 (create_time, status)
-- 错误示范：范围查询后status无法使用索引
SELECT * FROM articles
WHERE create_time > '2023-01-01' AND status = 1;
-- 优化方案：调整索引顺序或拆分为两个查询
三、OR条件的优化方案
OR条件是个索引杀手，特别是当OR两边的条件涉及不同列时：
-- 错误示范：这种OR条件会导致索引失效
SELECT * FROM products
WHERE category_id = 5 OR price > 1000;
-- 优化方案1：使用UNION替代OR
SELECT * FROM products WHERE category_id = 5
UNION
SELECT * FROM products WHERE price > 1000;
-- 优化方案2：建立合适的复合索引
对于必须使用OR的场景，可以考虑使用索引合并优化：
-- 需要确保category_id和price都有独立索引
SELECT * FROM products
WHERE category_id = 5 OR price > 1000;
-- 查看执行计划确认是否使用了index_merge
EXPLAIN SELECT * FROM products WHERE category_id = 5 OR price > 1000;
四、LIKE查询的优化技巧
模糊查询是索引失效的重灾区，特别是前导通配符的情况：
-- 错误示范：前导通配符导致索引失效
SELECT * FROM customers WHERE name LIKE '%张%';
-- 优化方案1：使用后缀通配符
SELECT * FROM customers WHERE name LIKE '张%';
-- 优化方案2：考虑使用全文索引
ALTER TABLE customers ADD FULLTEXT(name);
SELECT * FROM customers WHERE MATCH(name) AGAINST('张');
对于必须使用前导通配符的场景，可以考虑使用反向索引：
-- 添加反向存储的列
ALTER TABLE customers ADD COLUMN name_reverse VARCHAR(100);
UPDATE customers SET name_reverse = REVERSE(name);
-- 创建索引并查询
CREATE INDEX idx_name_reverse ON customers(name_reverse);
SELECT * FROM customers WHERE name_reverse LIKE REVERSE('%张');
五、NULL值处理的注意事项
NULL值在索引中是个特殊存在，处理不当会导致索引失效：
-- 错误示范：IS NULL可能导致索引失效
SELECT * FROM users WHERE phone IS NULL;
-- 优化方案1：设置默认值替代NULL
ALTER TABLE users MODIFY phone VARCHAR(20) DEFAULT '';
-- 优化方案2：使用覆盖索引
CREATE INDEX idx_phone ON users(phone);
SELECT phone FROM users WHERE phone IS NULL;
对于允许NULL的列，查询时要注意等值比较：
-- 错误示范：NULL不能用=比较
SELECT * FROM users WHERE phone = NULL; -- 不会返回任何结果
-- 正确做法：使用IS NULL
SELECT * FROM users WHERE phone IS NULL;
六、索引选择性对性能的影响
索引选择性是指索引列中不同值的比例，选择性差的索引可能不如不用：
-- 假设status只有0和1两个值
-- 低选择性索引效果差
CREATE INDEX idx_status ON orders(status);
-- 高选择性索引效果更好
CREATE INDEX idx_user_status ON orders(user_id, status);
如何评估索引选择性：
-- 计算某列的选择性基数
SELECT COUNT(DISTINCT status)/COUNT(*) FROM orders;
-- 选择性大于0.1的列才适合建索引
七、索引维护的最佳实践
索引不是建完就完事了，还需要定期维护：
-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;
-- 定期优化表重建索引
OPTIMIZE TABLE orders;
-- 删除重复索引
SHOW INDEX FROM orders;
对于大表的索引变更要特别注意：
-- 错误示范：直接在大表上添加索引
ALTER TABLE huge_table ADD INDEX idx_new(new_column);
-- 正确做法：使用pt-online-schema-change等工具
-- 或在低峰期执行，并预估耗时
八、执行计划的分析技巧
学会看执行计划是诊断索引问题的关键：
-- 基本执行计划分析
EXPLAIN SELECT * FROM products WHERE category_id = 5;
-- 重点关注type列：const > ref > range > index > ALL
-- 查看是否使用了预期索引
EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category_id = 5;
高级分析技巧：
-- 查看索引统计信息
SHOW INDEX FROM products;
-- 强制使用特定索引测试性能
SELECT * FROM products FORCE INDEX(idx_category) WHERE category_id = 5;
九、常见优化方案总结
综合各种场景，我们总结出这些优化方案：
- 避免在索引列上使用函数或计算
- 注意隐式类型转换问题
- 复合索引要遵循最左前缀原则
- 范围查询后索引列失效
- OR条件尽量用UNION替代
- LIKE查询避免前导通配符
- NULL值处理要特别注意
- 选择高选择性的列建索引
- 定期维护和清理无用索引
- 学会分析执行计划
记住，索引不是越多越好。每个额外的索引都会增加写入开销。要根据实际查询模式设计最合适的索引策略。
评论