一、索引失效的常见场景
索引就像是数据库的目录，但有时候这个目录会突然"失明"。最常见的情况就是在查询条件中使用函数操作。比如我们要查询用户表中姓"张"的用户：
-- 错误示例：索引失效
SELECT * FROM users WHERE SUBSTRING(name,1,1) = '张';
-- 正确示例：使用索引
SELECT * FROM users WHERE name LIKE '张%';
另一个典型场景是隐式类型转换。假设我们有个手机号字段是varchar类型，但查询时用了数字：
-- 错误示例：索引失效
SELECT * FROM customers WHERE phone = 13800138000;
-- 正确示例：保持类型一致
SELECT * FROM customers WHERE phone = '13800138000';
二、最左前缀原则的坑
联合索引就像多层目录，必须按照建立时的顺序使用。比如我们有个联合索引idx_age_name(age,name)：
-- 有效使用索引
SELECT * FROM employees WHERE age = 30 AND name = '张三';
-- 部分有效（只用到age索引）
SELECT * FROM employees WHERE age = 30;
-- 完全失效（跳过了age）
SELECT * FROM employees WHERE name = '张三';
有个特例是范围查询后的列索引会失效：
-- 只有age能用索引，name失效
SELECT * FROM employees WHERE age > 30 AND name = '张三';
三、OR条件的陷阱
OR条件就像个"索引杀手"，特别是当两边条件字段不同时：
-- 索引完全失效
SELECT * FROM products WHERE category_id = 5 OR price > 100;
-- 优化方案1：改用UNION
SELECT * FROM products WHERE category_id = 5
UNION
SELECT * FROM products WHERE price > 100;
-- 优化方案2：使用IN（适用于离散值）
SELECT * FROM products WHERE category_id IN (5,6,7);
四、不等于和不走索引
不等于操作符（!=或<>）会让优化器很为难：
-- 索引失效
SELECT * FROM orders WHERE status != 'completed';
-- 优化方案：改为范围查询
SELECT * FROM orders WHERE status < 'completed' OR status > 'completed';
对于NULL值判断也要特别注意：
-- 索引失效
SELECT * FROM users WHERE email IS NOT NULL;
-- 优化方案：考虑默认值替代NULL
SELECT * FROM users WHERE email != '';
五、索引选择性太低
当某个字段的值重复率太高时，索引效果会大打折扣。比如性别字段：
-- 低效的索引使用
SELECT * FROM members WHERE gender = 'F';
-- 优化方案：组合更有区分度的字段
ALTER TABLE members ADD INDEX idx_gender_age(gender,age);
可以通过这个SQL检查索引选择性：
SELECT
COUNT(DISTINCT gender)/COUNT(*) AS selectivity
FROM members;
六、索引优化的实战技巧
- 使用EXPLAIN分析执行计划是基本功：
EXPLAIN SELECT * FROM articles WHERE title LIKE 'MySQL%';
- 强制使用索引有时候很管用：
SELECT * FROM orders FORCE INDEX(idx_user_id) WHERE user_id = 100;
- 覆盖索引能大幅提升性能：
-- 普通查询
SELECT * FROM products WHERE category = '电子产品';
-- 优化为覆盖索引查询
SELECT id,name FROM products WHERE category = '电子产品';
-- 需要建立索引：ALTER TABLE products ADD INDEX idx_category_name(category,name);
七、索引维护的注意事项
索引不是建完就一劳永逸的，需要定期维护：
-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;
-- 重建碎片化索引
ALTER TABLE logs REBUILD INDEX idx_created_at;
还要注意索引的存储开销，一个表索引数量最好不要超过5-6个。
八、不同场景下的优化策略
对于高频查询但很少变更的数据，可以考虑使用内存表：
CREATE TABLE hot_users (
id INT PRIMARY KEY,
name VARCHAR(50)
) ENGINE=MEMORY;
对于超大数据表，分区表可能是更好的选择：
CREATE TABLE logs (
id INT AUTO_INCREMENT,
log_time DATETIME,
content TEXT,
PRIMARY KEY (id, log_time)
) PARTITION BY RANGE (YEAR(log_time)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
九、总结与最佳实践
索引优化是门艺术，需要平衡查询性能与维护成本。记住几个黄金法则：
- 频繁查询的字段优先建索引
- 区分度高的字段更适合建索引
- 避免过度索引，每个索引都有维护成本
- 定期检查索引使用情况，删除无用索引
- 结合业务特点选择适合的索引类型
最后提醒，索引优化不能只看单条SQL，要放在整个业务场景中考量。有时候调整业务逻辑比死磕索引更有效。
评论