最近在排查一个订单查询的慢SQL时，遇到了一个典型的索引失效案例。表结构如下：

CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
status TINYINT,
create_time DATETIME,
INDEX idx_composite (user_id, status, create_time)
);

当执行下方代码时

SELECT * FROM orders WHERE status = 2 AND create_time > '2024-01-01'

发现查询耗时高达1.2秒，但WHERE user_id = 1001的同类查询仅需5ms。

检查
可以通过EXPLAIN发现：
1.复合索引idx_composite未被使用（possible_keys显示该索引）。
2.实际使用了全表扫描（type=ALL）。
3.关键线索：key_len值为NULL。

原理剖析
MySQL的最左前缀匹配原则要求：
1.查询条件必须包含复合索引的第一个字段（user_id）。
2.条件字段需按索引顺序连续使用。

当前查询跳过了索引首字段user_id，导致出现问题:
1.索引树无法按层级定位数据。
2.优化器判定全表扫描代价更低。

对比实验

-- 有效使用索引（type=ref） 
EXPLAIN SELECT * FROM orders   
WHERE user_id = 1001 AND status = 2;   

-- 部分使用索引（type=range，使用到user_id+create_time）
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND create_time > '2024-01-01';

--索引完全失效（type=ALL）
EXPLAIN SELECT * FROM orders 
WHERE status = 2 AND create_time > '2024-01-01';

优化方案
1.查询重写：增加user_id条件。 2.索引调整：新建针对性索引。

ALTER TABLE orders ADD INDEX idx_status_time(status, create_time);

3.强制索引（应急）：

SELECT * FROM orders FORCE INDEX(idx_composite) WHERE ...  

设计启示

1.复合索引字段顺序遵循高频在前、区分度高在前原则。
2.避免创建超过3个字段的复合索引。
3.定期使用SHOW INDEX FROM table分析索引使用情况。

经验总结

这个案例揭示了索引设计的核心逻辑：索引是排好序的数据结构。当跳过索引首字段时，相当于在无序的数据中搜索，自然失去索引优势。建议：
1.在编写SQL时注意条件顺序。
2.使用EXPLAIN验证索引使用情况。
3.建立索引时考虑业务查询模式。

通过调整索引策略，最终将该查询耗时从1.2秒降至15ms。这再次证明：好的索引设计，往往比硬件升级更能带来质的性能提升。
灵感来源于CSDN,需要可查看以下博主： 麒麟来编程 YornChu