一、慢查询的隐蔽性
在微服务架构里，数据库慢查询是最难定位的性能问题之一。不像 CPU 飙升或内存溢出那样有明确的监控报警，慢查询往往表现为“偶发性延迟抖动”——P99 延迟从 50ms 跳到 2000ms，但 P50 依然正常。这种间歇性问题在常规监控中容易被平均值掩盖，往往等到用户投诉才被发现。
慢查询的原因很少是单一的，通常是索引设计、统计信息或查询写法的问题。一个缺少合适索引的查询，数据量从 10 万增长到 1000 万时，性能可能下降上百倍。统计信息过期的优化器，甚至会在索引存在的情况下选择全表扫描。更麻烦的是，某些查询在低并发下正常，高并发时却因锁竞争和 Buffer Pool 争用导致性能急剧下降。理解 MySQL 的执行机制和索引结构，是解决这些问题的基础。
二、查询执行与索引机制
2.1 查询执行流程
一条 SQL 从发送到返回，主要经历以下阶段：
优化器是链路中的决策点。它基于表的统计信息（行数、索引基数、数据页数）计算执行计划的代价。统计信息不准时，优化器可能做出错误选择——比如在千万级表上选全表扫描。
2.2 B+ 树索引与查找
InnoDB 使用 B+ 树存储索引。非叶子节点存键值用于路由，数据全在叶子节点，叶子节点用双向链表连接。这种结构让范围查询很高效：找到起始键值后，顺着链表扫就行。
聚簇索引（主键索引）：叶子节点存完整行数据。InnoDB 表本身就是聚簇索引，数据按主键物理存储。
二级索引（非主键索引）：叶子节点存主键值。查数据时，如果查询列不在索引里，得回表：先在二级索引找主键，再去聚簇索引找完整行。回表是慢查询的常见原因。
覆盖索引：如果查询的所有列都在二级索引里（含主键），就不用回表。这是索引优化里最有效的手段。
2.3 索引选择性与最左前缀
索引选择性定义为 SELECT COUNT(DISTINCT col) / COUNT(*) FROM table。选择性越高，过滤效果越好。选择性 0.01 意味着每个索引值对应 100 行，扫描后还得回表读 100 行，效率很低。
联合索引的最左前缀原则：索引 (a, b, c) 支持 a、a,b、a,b,c 的查询，但不支持 b 或 b,c。因为 B+ 树先按 a 排，a 相同再按 b 排。跳过 a 直接查 b，用不上索引的有序性。
三、慢查询分析与索引优化
3.1 定位与执行计划
-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1;
SET GLOBAL log_queries_not_using_indexes = ON;
-- 分析慢查询日志（命令行）
-- mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
-- 执行计划解读示例
EXPLAIN SELECT o.order_id, o.amount, u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID'
AND o.created_at BETWEEN '2025-01-01' AND '2025-06-30'
ORDER BY o.amount DESC
LIMIT 100;
-- 关键字段：
-- type: 访问类型 (system > const > eq_ref > ref > range > index > ALL)
-- key: 实际使用的索引
-- rows: 预估扫描行数
-- Extra: 额外信息 (Using index, Using filesort, Using temporary 等)
3.2 索引设计实战
-- 场景一：单表条件查询
-- 错误：单独建 status 和 created_at 索引，MySQL 只能选一个
-- 正确：联合索引 (status, created_at)
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
-- 场景二：覆盖索引消除回表
-- 查询只需 order_id, amount, status
-- 覆盖索引：(status, created_at, amount)
ALTER TABLE orders ADD INDEX idx_cover_paid (status, created_at, amount);
-- 场景三：JOIN 查询优化
-- 1. 被驱动表 users 的 JOIN 列 (id) 必须有索引
-- 2. 驱动表 orders 的 WHERE 条件需要高效过滤
-- 联合索引 (status, amount) 可同时满足过滤和排序，避免 filesort
ALTER TABLE orders ADD INDEX idx_status_amount (status, amount);
-- 场景四：索引下推 (ICP)
-- MySQL 5.6+ 支持。联合索引最左前缀无法完全匹配 WHERE 时，
-- 将部分条件下推到索引扫描阶段过滤，减少回表。
-- 示例：索引 (user_id, status)，查询 WHERE user_id = 100 AND status LIKE 'PENDING%'
-- 有 ICP 时，在索引扫描阶段就过滤 status，无需回表后再过滤。
-- 场景五：统计信息维护
-- 手动更新统计信息
ANALYZE TABLE orders;
-- 查看索引基数
SHOW INDEX FROM orders;
-- 定期清理碎片
ALTER TABLE orders ENGINE=InnoDB;
3.3 优化效果
在 1000 万行 orders 表上的测试：
四、索引优化的代价与陷阱
4.1 写入代价
每个二级索引都会增加 INSERT/UPDATE/DELETE 的开销。InnoDB 修改数据时得同步更新所有受影响索引。一张表有 5 个二级索引，写入性能可能比无索引时下降 50%-70%。高写入表（如日志、流水表）要严格控制索引数量。建议：写入频繁的表索引不超过 3 个，读多写少的表可适当增加。
4.2 联合索引列顺序
联合索引列顺序要遵循“等值条件列在前，范围条件列在后”。范围查询列放前面，等值列的过滤能力就没了。更麻烦的是，一组联合索引没法同时满足所有查询。比如索引 (a, b) 支持 WHERE a=? AND b=? 和 WHERE a=?，但不支持 WHERE b=?。如果业务同时有这两种查询，得建两个索引，增加写入和存储开销。
4.3 统计信息时效性
InnoDB 统计信息通过随机采样 8 个数据页估算，不是精确值。数据分布剧烈变化（如批量导入、大范围删除）后，统计信息可能失真，导致优化器选错计划。大批量数据变更后，建议手动执行 ANALYZE TABLE，或设置 innodb_stats_auto_recalc=ON（默认开启，但仅对变更超 10% 的表触发）。
4.4 索引失效陷阱
以下情况会导致索引失效：
索引列上用函数：WHERE YEAR(created_at) = 2025 应改为范围查询
隐式类型转换：WHERE varchar_col = 123 应改为 WHERE varchar_col = '123'
OR 连接不同索引列：MySQL 大多数情况只能用一索引
NOT IN、!=、LIKE '%xxx' 等否定或前缀模糊条件
五、总结与建议
慢查询优化不是“加个索引”那么简单，得从定位、分析到优化系统性地做。执行计划（EXPLAIN ）是核心工具，得逐字段解读，重点关注 type、key、rows 和 Extra。索引设计要同时考虑查询模式、数据分布和写入代价，联合索引列顺序直接影响有效性。覆盖索引是消除回表的最有效手段，ICP 能在无法覆盖时减少回表。统计信息准确是优化器正确决策的基础，大批量变更后得及时更新。
建议的操作步骤：
开启慢查询日志，设合理阈值，用 mysqldumpslow 聚合分析高频慢查询
对 Top 10 慢查询执行 EXPLAIN，识别全表扫描、filesort、临时表等问题
根据查询模式设计联合索引，优先处理等值列在前、范围列在后的顺序
高频查询考虑覆盖索引，减少回表
每次索引变更后，用 EXPLAIN 确认计划，用基准测试验证性能提升
索引优化没有终点，数据增长和查询模式变化后，得持续监控和迭代。