MySQL 8.4降级索引与查询优化器实战调优指南

MySQL 8.4查询优化器为什么需要手动干预

MySQL 8.4的查询优化器在大多数场景下能自动选择合理的执行计划，但在特定数据分布下会出现索引选择失误：该走二级索引的走了全表扫描，该走覆盖索引的回表了，该走范围扫描的走了索引全扫描。这些失误在高并发生产环境中直接转化为慢查询风暴。理解优化器的选择逻辑和手动干预手段，是DBA和后端开发必备的实战能力。

MySQL 8.4引入的降级索引（Descending Index）功能是优化器武器库的重要补充，但需要配合正确的查询写法才能生效。

降级索引的创建与使用场景

降级索引允许在索引定义中指定列的排序方向。之前版本虽然支持DESC语法，但实际创建时忽略方向，MySQL 8.0+开始真正支持降级索引存储。8.4进一步优化了降级索引的统计信息收集。

典型场景：按时间倒序分页查询。传统方案需要对created_at建立正向索引，查询时ORDER BY created_at DESC，MySQL需要反向扫描索引或在内存中排序。

-- 创建降级索引
ALTER TABLE orders
  ADD INDEX idx_created_at_desc (created_at DESC, id ASC);

-- 对比执行计划
-- 无降级索引时: Using filesort
EXPLAIN SELECT * FROM orders
  ORDER BY created_at DESC, id ASC
  LIMIT 20;

-- 有降级索引时: Using index, 无filesort
-- 优化器直接正向扫描降级索引, 结果已排序

降级索引的关键优势：ORDER BY created_at DESC, id ASC这种混合排序方向的查询，正向索引无法消除filesort（因为索引是created_at ASC, id ASC），而降级索引(created_at DESC, id ASC)正好匹配排序方向，扫描即有序。

降级索引与覆盖索引的组合优化

分页查询最常见的性能问题是”回表”。即使索引扫描很快，每条记录都需要回表取完整行数据。覆盖索引可以消除回表：

-- 覆盖索引 + 降级索引
ALTER TABLE orders
  ADD INDEX idx_covering_desc (
    user_id ASC,
    created_at DESC,
    id ASC,
    amount,                  -- 包含在索引中避免回表
    status
  );

-- 利用覆盖索引的分页查询
EXPLAIN SELECT user_id, created_at, id, amount, status
  FROM orders
  WHERE user_id = 1001
  ORDER BY created_at DESC
  LIMIT 20;

-- 执行计划: Using index (覆盖索引, 无回表)
-- 对比无覆盖索引: Using where; Using filesort + 大量回表

-- 延迟关联 (Deferred Join) 优化深分页
-- 问题: OFFSET 100000 需要扫描10万行
SELECT * FROM orders
  WHERE user_id = 1001
  ORDER BY created_at DESC
  LIMIT 100000, 20;

-- 优化: 先通过覆盖索引取主键, 再回表
SELECT o.* FROM orders o
  INNER JOIN (
    SELECT id FROM orders
    WHERE user_id = 1001
    ORDER BY created_at DESC
    LIMIT 100000, 20
  ) tmp ON o.id = tmp.id;

-- 子查询走覆盖索引 idx_covering_desc, 只取20个id
-- 外层查询精确回表20行, 而非扫描10万行回表

优化器索引选择失误的常见原因与修正

原因一：统计信息不准确

InnoDB通过采样估算索引的基数（Cardinality），采样比例默认约8个数据页。数据分布不均匀时，采样结果偏差大，优化器据此选择错误索引。

-- 检查索引基数
SHOW INDEX FROM orders;
-- Cardinality 列显示估算值

-- 手动更新统计信息
ANALYZE TABLE orders;

-- 修改采样页数 (默认8, 增大可提高精度但耗时长)
SET GLOBAL innodb_stats_persistent_sample_pages = 32;

-- 对核心表设置为更高采样密度
ALTER TABLE orders STATS_PERSISTENT=1,
  STATS_SAMPLE_PAGES=64;

原因二：索引选择率估算偏差

优化器根据索引列的等值条件估算匹配行数。当数据存在严重倾斜时，等值条件的实际行数与估算差异大：

-- 查看优化器估算的行数
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE status = 'shipped';
-- "rows_examined_per_scan": 50000  (估算)
-- 实际: SELECT COUNT(*) FROM orders WHERE status = 'shipped';  -> 500000

-- 修正方案1: 直方图统计 (MySQL 8.0+)
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 256 BUCKETS;

-- 查看直方图
SELECT * FROM information_schema.COLUMN_STATISTICS
  WHERE table_name = 'orders' AND column_name = 'status';

-- 修正方案2: 索引提示强制使用正确索引
SELECT * FROM orders FORCE INDEX (idx_status_created)
  WHERE status = 'shipped'
  ORDER BY created_at DESC
  LIMIT 20;

Optimizer Switch精细控制

MySQL优化器的行为通过optimizer_switch系统变量控制。当特定优化策略导致执行计划偏差时，可以逐项关闭：

-- 查看当前优化器开关
SELECT @@optimizer_switch\G

-- 常见调整项:
-- 关闭索引合并 (Index Merge), 避免优化器选择多个低效索引合并
SET SESSION optimizer_switch = 'index_merge=off';

-- 关闭索引条件下推 (ICP), 某些场景ICP反而降低性能
SET SESSION optimizer_switch = 'index_condition_pushdown=off';

-- 关闭MRR (Multi-Range Read), 当范围扫描回表随机IO开销大时MRR有效
-- 但排序后的范围查询MRR排序步骤浪费CPU
SET SESSION optimizer_switch = 'mrr=off';

-- 针对单条SQL的Hint写法 (MySQL 8.0+)
SELECT /*+ NO_INDEX_MERGE(orders) */
  * FROM orders
  WHERE status = 'shipped' AND user_id = 1001;

MySQL 8.4优化器Trace实战分析

当EXPLAIN无法解释优化器的选择时，开启Optimizer Trace查看完整的代价计算过程：

-- 开启优化器Trace
SET SESSION optimizer_trace = 'enabled=on';
SET SESSION optimizer_trace_max_mem_size = 1048576;  -- 1MB

-- 执行查询
SELECT * FROM orders
  WHERE user_id = 1001 AND status = 'shipped'
  ORDER BY created_at DESC
  LIMIT 20;

-- 查看Trace结果
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

-- 关键字段解读:
-- range_analysis: 各索引的范围扫描代价估算
--   - index_idx_user_id: cost=120.5, rows=500
--   - index_idx_status: cost=8900.2, rows=50000  (错误选择)
--   - index_idx_covering_desc: cost=35.8, rows=500  (最优)
-- best_plan: 优化器最终选择的计划及其代价
-- considered_execution_plans: 所有候选计划列表

通过Trace可以精确定位优化器为什么放弃最优索引——往往是因为统计信息偏差导致某个索引的rows_estimated远低于实际值，代价计算失真。

慢查询自动化诊断脚本

#!/bin/bash
# 慢查询自动诊断脚本
# 从slow log提取Top 10慢查询, 逐条生成优化建议

SLOW_LOG="/var/log/mysql/slow.log"
OUTPUT="/tmp/mysql_diag_$(date +%Y%m%d).md"

echo "# MySQL 慢查询诊断报告 - $(date +%Y-%m-%d)" > $OUTPUT
echo "" >> $OUTPUT

# 提取Top 10慢查询
mysqldumpslow -s t -t 10 $SLOW_LOG | while read line; do
  echo "## 慢查询: $line" >> $OUTPUT
  echo "\`\`\`sql" >> $OUTPUT
  echo "$line" >> $OUTPUT
  echo "\`\`\`" >> $OUTPUT
  echo "" >> $OUTPUT
done

# 检查缺失索引
echo "## 潜在缺失索引" >> $OUTPUT
mysql -e "
  SELECT
    s.TABLE_SCHEMA, s.TABLE_NAME,
    s.INDEX_NAME, s.NON_UNIQUE,
    s.SEQ_IN_INDEX, s.COLUMN_NAME
  FROM information_schema.STATISTICS s
  LEFT JOIN information_schema.TABLES t
    ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND s.TABLE_NAME = t.TABLE_NAME
  WHERE t.TABLE_ROWS > 100000
    AND s.TABLE_SCHEMA NOT IN ('mysql','sys','performance_schema')
  ORDER BY s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME, s.SEQ_IN_INDEX
" >> $OUTPUT

echo "诊断报告已生成: $OUTPUT"

MySQL 8.4的优化器足够智能，但不是万能的。降级索引、直方图、Optimizer Trace三层工具组合，覆盖了从索引设计、统计信息修正到执行计划调试的完整链路。核心原则是：用Trace定位问题，用统计信息修正偏差，用索引Hint做兜底。