第四章 SQL优化全解

4.1 执行计划深度解读

EXPLAIN FORMAT=JSON关键路径分析

核心价值：揭示优化器的决策细节，定位性能瓶颈

关键路径解析模板

{
  "query_block": {
    "cost_info": {
      "query_cost": "3.23"          // 总预估成本
    },
    "table": {
      "access_type": "range",       // 访问类型 ★★★
      "possible_keys": ["idx_age"],
      "key": "idx_age",
      "used_key_parts": ["age"],    // 实际使用的索引列
      "rows_examined_per_scan": 189,// 扫描行数
      "rows_produced_per_join": 12,
      "filtered": "6.34",          // 筛选效率
      "cost_info": {
        "read_cost": "2.97",        // 磁盘IO+内存计算成本
        "eval_cost": "0.26",        // 结果集处理成本
        "prefix_cost": "3.23"       // 累计总成本
      },
      "attached_condition": "(`employees`.`dept_id` = 5)" // 未能下推的条件
    }
  }
}

优化关键点：

1. read_cost占比超过70% → 检查索引覆盖性
2. attached_condition存在 → 尝试索引扩展
3. rows_examined_per_scan与rows_produced比值大 → 添加条件过滤

Cost值计算逻辑

成本模型公式：
总成本 = IO成本（读取页数 × 1.0） + CPU成本（扫描行数 × 0.1）

手动成本估算演练

-- 表统计信息
SHOW TABLE STATUS LIKE 'orders';
/*
Data_length: 1024000 （总数据字节数）
Rows: 100000
*/

-- 计算扫描页数（假设每页16KB）
IO_COST = (1024000 / 16384) * 1.0 = 62.5

-- 计算CPU成本（估算扫描行数）
CPU_COST = 100000 * 0.1 = 10000 

-- 总成本 = 62.5 + 10000 = 10062.5

优化器偏差处理：

-- 强制刷新统计信息
ANALYZE TABLE orders;

-- 指定索引（当优化器误判时）
SELECT * FROM orders FORCE INDEX(idx_status) WHERE ...;

4.2 反模式重构案例

大分页优化：游标替代LIMIT

性能断崖点：OFFSET超过10000时响应时间指数级增长

优化方案对比

游标分页实现

-- 第一页（传统方式）
SELECT * FROM logs 
ORDER BY id 
LIMIT 10;

-- 后续分页（携带最后一条ID）
SELECT * FROM logs 
WHERE id > 1000  -- 上一页末尾ID
ORDER BY id 
LIMIT 10;

深度优化技巧：

-- 添加延迟关联（应对复杂查询）
SELECT * FROM logs 
INNER JOIN (
  SELECT id 
  FROM logs 
  WHERE create_time > '2023-01-01'
  ORDER BY id 
  LIMIT 100000,10
) AS tmp USING(id);

热点更新：排队论应用

典型场景：库存扣减、计数器累加

悲观锁方案（高并发瓶颈）

BEGIN;
SELECT quantity FROM products WHERE id=1 FOR UPDATE;
UPDATE products SET quantity = quantity - 1 WHERE id=1; 
COMMIT; 
-- 吞吐量：约200 TPS

乐观锁+批量更新（性能提升10倍）

-- 无锁读取
SELECT version, quantity FROM products WHERE id=1;

-- 异步批量提交（应用层合并请求）
UPDATE products 
SET quantity = quantity - 5, 
    version = version + 1 
WHERE id=1 AND version = 23;
-- 吞吐量：约2000 TPS

排队论参数配置：

# InnoDB参数优化（针对高并发更新）
innodb_thread_concurrency = 32
innodb_adaptive_max_sleep_delay = 150000

4.3 窗口函数性能陷阱

性能损耗根源

执行阶段分析：

1. 创建临时表存储分区数据
2. 逐行计算窗口范围内数据
3. 内存消耗与窗口大小成指数关系

性能对比测试（100万数据）

优化方案四象限

窗口函数优化示例

-- 原始低效写法
SELECT id, 
       SUM(amount) OVER(PARTITION BY user_id ORDER BY log_date 
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
FROM transactions;

-- 优化方案：限制窗口范围 + 利用索引
CREATE INDEX idx_user_time ON transactions(user_id, log_date);

SELECT t1.id, 
       (SELECT SUM(amount) 
        FROM transactions t2 
        WHERE t2.user_id = t1.user_id 
          AND t2.log_date <= t1.log_date) AS running_total 
FROM transactions t1;

附录：SQL优化工具箱

1. 执行计划分析器

#!/bin/bash
# 生成可视化执行计划
mysql -uroot -p -e "EXPLAIN FORMAT=JSON $1" | jq '.' > plan.json

2. 慢查询重写助手

-- 自动生成优化建议（需安装Percona Toolkit）
pt-query-advisor --analyze slow.log

3. 压力测试模板

sysbench oltp_read_write --threads=32 --time=300 \
--mysql-host=127.0.0.1 --mysql-user=root --mysql-password=xxx \
--mysql-db=test --tables=10 --table-size=1000000 run

下一章预告：第五章将深入探讨MySQL集群化架构设计，详解读写分离、分布式事务与云原生优化方案，并给出双十一级流量下的PolarDB调优模板。