1. 从一条慢SQL说起：订单分页查询的困境
去年双11大促期间，我们的订单系统突然出现了一批奇怪的慢查询。这些查询看起来非常简单——就是根据买家ID查询最近的订单列表，但平均执行时间却达到了惊人的2秒。典型的SQL长这样：
SELECT order_id FROM tcorder WHERE is_main=1 AND buyer_id=12345678 ORDER BY create_time DESC, order_id ASC LIMIT 0,10通过EXPLAIN分析执行计划，发现虽然命中了buyer_id的二级索引，但Extra列赫然显示着"Using filesort"。这意味着MySQL不得不把所有符合条件的记录都加载到内存中进行排序，然后再取出前10条。对于一个日均订单量千万级的电商平台，这种操作简直就是性能杀手。
更诡异的是，当我们去掉order_id的排序条件后，查询速度立即恢复正常。这引出了两个关键问题：为什么多一个排序条件会导致性能断崖式下跌？为什么这个看似多余的order_id排序会被加到查询中？
2. B+Tree索引原理深度解析
2.1 为什么索引能加速查询？
想象一下图书馆找书的场景。没有索引就像在书库里一本本翻找，而索引就像图书目录——先找到分类号，再定位到具体书架。MySQL的B+Tree索引就是这样一个多级目录结构：
- 非叶子节点存储索引键值和子节点指针（类似"历史类→中国史→明清史"）
- 叶子节点存储索引键值和主键（相当于具体书架位置）
- 所有叶子节点通过指针相连形成链表（方便范围查询）
-- 查看索引统计信息 SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME='tcorder';2.2 复合索引的排列玄机
复合索引的字段顺序至关重要，就像电话号码的区号必须在前。我们的索引idx_buyer_create(buyer_id,create_time)：
- 先按buyer_id排序
- 相同buyer_id下按create_time排序
- 但order_id完全无序
这就是为什么ORDER BY create_time能用索引，而ORDER BY create_time,order_id必须filesort——索引中order_id的排列就像乱放的书籍，无法利用索引顺序。
2.3 索引选择背后的数学
MySQL优化器是个精明的会计，它会计算每种执行计划的成本：
-- 查看优化器追踪信息 SET optimizer_trace="enabled=on"; SELECT * FROM tcorder WHERE ...; SELECT * FROM information_schema.optimizer_trace;当它发现：
- 使用现有索引需要排序10万条记录
- 全表扫描只需要过滤5万条 就会"聪明"地选择全表扫描。这就是为什么有时EXPLAIN结果反直觉。
3. 实战：订单表索引优化方案
3.1 临时解决方案：查询重写
我们首先尝试最小化改动：
-- 移除order_id排序（业务允许时） SELECT order_id FROM tcorder WHERE is_main=1 AND buyer_id=12345678 ORDER BY create_time DESC LIMIT 0,10 -- 或使用索引提示 SELECT order_id FROM tcorder FORCE INDEX(idx_buyer_create) WHERE is_main=1 AND buyer_id=12345678 ORDER BY create_time DESC, order_id ASC LIMIT 0,103.2 终极方案：索引重构
经过压测验证，我们设计了新索引：
ALTER TABLE tcorder ADD INDEX idx_opt(buyer_id, create_time, order_id);这个索引的妙处在于：
- buyer_id用于快速定位用户订单
- create_time和order_id已经按需排序
- 覆盖查询所需全部字段（无需回表）
3.3 灰度上线SOP
大表索引变更必须慎之又慎：
- 先在备库验证执行计划
- 使用pt-online-schema-change在线变更
- 按分库分批次灰度
- 监控QPS/CPU/慢查询
- 新旧索引并行运行至少一周
# 使用pt工具添加索引 pt-online-schema-change --alter "ADD INDEX idx_opt(buyer_id,create_time,order_id)" \ D=test,t=tcorder --execute4. 避坑指南：索引优化的常见误区
4.1 新手容易踩的坑
过度索引：每个查询一个索引，导致写入性能下降
- 解决方案：使用复合索引覆盖多个查询
无效索引：区分度低的字段建索引（如性别字段）
-- 查看字段区分度 SELECT COUNT(DISTINCT status)/COUNT(*) FROM tcorder;隐式转换：字段类型不匹配导致索引失效
-- buyer_id是varchar却用数字查询 SELECT * FROM tcorder WHERE buyer_id=12345678
4.2 高级技巧
索引跳跃扫描（MySQL 8.0+）：
-- 即使索引(a,b)只查b也能用索引 SELECT * FROM table WHERE b=1;降序索引优化倒序查询：
CREATE INDEX idx_desc ON tcorder(create_time DESC);索引合并的陷阱：
- 有时分开索引比复合索引更高效
- 需要analyze table更新统计信息
5. 性能对比：优化前后的数字说话
优化效果立竿见影：
| 指标 | 优化前 | 优化后 | 下降幅度 |
|---|---|---|---|
| 平均查询时间 | 2017ms | 23ms | 98.8% |
| CPU使用率 | 75% | 32% | 57% |
| 慢查询数量 | 3106 | 12 | 99.6% |
通过这个案例，我深刻体会到：索引优化不是玄学，而是建立在深入理解存储引擎工作原理基础上的精确手术。每个索引都应该有明确的使命，就像图书馆的每本目录都要解决特定的查找需求。
