一、索引失效的常见场景
相信很多开发同学都遇到过这样的情况：明明表上已经建立了索引，但查询速度依然慢得像蜗牛爬。这种时候，十有八九是索引失效了。我们先来看看几种典型的索引失效场景。
最经典的就是在索引列上使用了函数或者运算。比如我们有个用户表，按照手机号的前三位建立了索引：
-- 用户表结构示例（MySQL技术栈）
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_phone_prefix` (`phone`(3))
) ENGINE=InnoDB;
如果查询时使用了函数：
-- 错误示例：在索引列上使用函数导致索引失效
SELECT * FROM users WHERE LEFT(phone, 3) = '138';
这个查询就无法使用我们精心设计的idx_phone_prefix索引了。正确的做法应该是：
-- 正确示例：避免在索引列上使用函数
SELECT * FROM users WHERE phone LIKE '138%';
二、隐式类型转换的坑
另一个常见的坑是隐式类型转换。MySQL在执行比较时，如果发现两边数据类型不一致，会自动进行类型转换，这往往会导致索引失效。
假设我们有个订单表：
-- 订单表结构示例（MySQL技术栈）
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_no` varchar(32) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`amount` decimal(10,2) DEFAULT NULL,
`status` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_order_no` (`order_no`)
) ENGINE=InnoDB;
如果查询时不小心把字符串类型的order_no和数字比较：
-- 错误示例：隐式类型转换导致索引失效
SELECT * FROM orders WHERE order_no = 12345678;
虽然语法上没问题，但MySQL会把order_no转换成数字再比较，导致索引失效。正确的做法是：
-- 正确示例：确保比较时类型一致
SELECT * FROM orders WHERE order_no = '12345678';
三、联合索引的最左前缀原则
联合索引的使用有个重要的原则叫"最左前缀匹配"，如果违反这个原则，索引也会失效。
来看个商品表的例子：
-- 商品表结构示例（MySQL技术栈）
CREATE TABLE `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) DEFAULT NULL,
`brand_id` int(11) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
`status` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_cate_brand_status` (`category_id`, `brand_id`, `status`)
) ENGINE=InnoDB;
以下几种查询方式，索引使用情况各不相同：
-- 情况1：完全匹配最左前缀，索引有效
SELECT * FROM products WHERE category_id = 1 AND brand_id = 5 AND status = 1;
-- 情况2：匹配部分最左前缀，索引部分有效
SELECT * FROM products WHERE category_id = 1 AND brand_id = 5;
-- 情况3：跳过最左列，索引失效
SELECT * FROM products WHERE brand_id = 5 AND status = 1;
-- 情况4：使用范围查询后，后续列索引失效
SELECT * FROM products WHERE category_id = 1 AND brand_id > 5 AND status = 1;
四、OR条件的陷阱
使用OR条件时也容易导致索引失效，特别是当OR连接的条件不是同一个索引时。
比如在用户表上查询：
-- 错误示例：OR条件导致索引失效
SELECT * FROM users WHERE phone LIKE '138%' OR username = '张三';
即使phone和username都有单独的索引，这个查询也无法有效使用索引。解决方案有几种：
- 使用UNION ALL改写：
-- 优化方案1：使用UNION ALL
SELECT * FROM users WHERE phone LIKE '138%'
UNION ALL
SELECT * FROM users WHERE username = '张三';
- 如果业务允许，可以考虑建立全文索引：
-- 优化方案2：考虑使用全文索引
ALTER TABLE users ADD FULLTEXT INDEX `idx_fulltext_search` (`username`, `phone`);
SELECT * FROM users WHERE MATCH(username, phone) AGAINST('张三 138*' IN BOOLEAN MODE);
五、索引选择性不足的问题
有时候索引失效是因为索引的选择性太差。所谓选择性，是指不重复的索引值与表记录数的比值。
举个例子，我们有个文章表：
-- 文章表结构示例（MySQL技术栈）
CREATE TABLE `articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) DEFAULT NULL,
`content` text,
`status` tinyint(4) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_status` (`status`)
) ENGINE=InnoDB;
如果status只有0和1两个值，那么在这个字段上建索引就没什么意义：
-- 选择性差的索引示例
SELECT * FROM articles WHERE status = 1;
这种情况下，MySQL优化器可能会选择全表扫描而不是使用索引，因为索引的选择性太差了。
六、优化实践建议
根据我的经验，总结几个实用的优化建议：
- 使用EXPLAIN分析SQL执行计划，这是排查索引问题的第一步。比如：
-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE phone LIKE '138%';
- 考虑使用覆盖索引，避免回表操作：
-- 创建覆盖索引示例
ALTER TABLE users ADD INDEX `idx_phone_cover` (`phone`, `username`);
-- 使用覆盖索引查询
SELECT phone, username FROM users WHERE phone LIKE '138%';
- 定期分析表，更新索引统计信息：
-- 更新统计信息
ANALYZE TABLE users;
对于复杂查询，考虑拆分成多个简单查询，在应用层组合结果。
监控慢查询日志，及时发现性能问题：
-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
七、真实案例分享
去年我们遇到一个生产环境的问题：用户反馈订单列表加载特别慢。经过排查，发现是如下查询导致的：
-- 原始问题SQL
SELECT * FROM orders
WHERE create_time > '2023-01-01'
AND (status = 2 OR pay_status = 1)
ORDER BY id DESC
LIMIT 20;
这个查询有多个问题：
- 使用了OR条件
- 排序字段和过滤字段没有关联
- 没有有效利用复合索引
我们的优化方案是：
- 建立新的复合索引：
ALTER TABLE orders ADD INDEX `idx_status_paystatus_time` (`status`, `pay_status`, `create_time`);
- 重写SQL，避免OR条件：
-- 优化后的SQL
SELECT * FROM orders WHERE create_time > '2023-01-01' AND status = 2
UNION ALL
SELECT * FROM orders WHERE create_time > '2023-01-01' AND pay_status = 1 AND status != 2
ORDER BY create_time DESC
LIMIT 20;
优化后，查询时间从原来的3秒多降到了100毫秒以内。
八、总结与思考
索引优化是个需要不断实践和总结的过程。通过这次分享，我们可以得出几个重要结论：
- 理解索引的工作原理是优化的基础
- 掌握常见索引失效场景可以避免很多低级错误
- 合理设计索引结构能显著提升查询性能
- 监控和分析是持续优化的关键
最后提醒一点：索引不是越多越好。每个额外的索引都会带来写入性能的开销和维护成本。我们需要在查询性能和写入性能之间找到平衡点。
评论