跳到主要内容

SQL 优化

EXPLAIN 怎么读​

最该记住的三列是 type、key、rows，再加 Extra。

type（访问类型）从好到坏：

system > const > eq_ref > ref > range > index > ALL
  • const —— 主键或唯一索引等值查询，最多返回一行
  • eq_ref —— 关联查询中用主键 / 唯一索引关联
  • ref —— 普通索引等值查询，可能返回多行
  • range —— 索引范围扫描（BETWEEN、IN、>）
  • index —— 扫描整棵索引树。注意这不是好事，只是比全表扫快一点
  • ALL —— 全表扫描

生产 SQL 的底线是 range，核心接口应该做到 ref 或更好。

Extra 的关键取值：

值含义
Using index✅ 覆盖索引，不回表
Using index condition✅ 索引下推生效
Using where在 Server 层过滤，说明索引没完全命中条件
Using filesort⚠️ 排序没用上索引，要额外排序
Using temporary⚠️ 用了临时表，常见于 GROUP BY 和 DISTINCT 列无索引
Using join buffer⚠️ 关联字段无索引

Using filesort 不一定意味着写磁盘 —— 数据量小于 sort_buffer_size 时在内存里排（快排），超了才用归并排序落盘生成临时文件。

rows 是估算值，来自索引统计信息，不是精确行数。统计信息过期会导致优化器选错索引，ANALYZE TABLE 可以重新采样。

深分页​

LIMIT 1000000, 20 的问题：MySQL 必须先取出前 100 万 + 20 行再丢弃前 100 万行，走二级索引时这 100 万次回表全是浪费。

三种改写方案：

1. 游标 / 书签式（最优，但要求有序且连续）

-- 记住上一页最后一条的 id
SELECT * FROM t WHERE id > 1000000 ORDER BY id LIMIT 20;

直接定位，与页码无关，耗时恒定。缺点是不能跳页。

2. 延迟关联（能跳页）

SELECT t.* FROM t
INNER JOIN (
  SELECT id FROM t ORDER BY id LIMIT 1000000, 20
) AS tmp USING (id);

子查询只扫索引（覆盖索引，不回表）拿到 20 个 id，再用这 20 个 id 回表。把 100 万次回表降到 20 次。

3. 业务层限制 —— 搜索场景限制最大页数（搜索引擎也只给 100 页左右），或改成「加载更多」的游标形态。

大表 DDL​

直接 ALTER TABLE 的风险见锁机制里的 MDL 事故模式。生产做法：

  • MySQL 5.6+ 的 Online DDL —— 加列、加索引等操作支持 ALGORITHM=INPLACE, LOCK=NONE，但仍需短暂的 MDL 写锁来开始和结束
  • gh-ost / pt-online-schema-change —— 建影子表 + 增量同步 + 原子改名。gh-ost 读 binlog 做同步，不用触发器，对主库压力更小
  • 操作窗口 —— 低峰期执行，执行前确认无长事务

慢查询定位流程​

-- 1. 确认慢查询日志开着
SHOW VARIABLES LIKE 'slow_query_log';
SET GLOBAL long_query_time = 1;  -- 单位秒

-- 2. 看当前在跑什么
SHOW FULL PROCESSLIST;
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started;

-- 3. 分析具体语句
EXPLAIN ANALYZE SELECT ...;   -- 8.0.18+，给出实际执行耗时

用 mysqldumpslow 或 pt-query-digest 聚合慢日志，按总耗时排序而不是单次耗时 —— 执行 10 万次的 50ms 查询比执行一次的 10 秒查询更值得优化。

其他高频优化点​

COUNT(*) vs COUNT(1) vs COUNT(列) —— COUNT(*) 和 COUNT(1) 性能基本一致（优化器会选最小的索引树扫描）；COUNT(列) 要判断该列是否为 NULL，且不统计 NULL 行，语义不同。想要快速估算用 SHOW TABLE STATUS 的 rows 字段（估算值）。

JOIN 的驱动表选择 —— 小表驱动大表。LEFT JOIN 时左表是驱动表，INNER JOIN 由优化器决定。被驱动表的关联字段必须有索引，否则退化成 Block Nested-Loop Join，复杂度从 O(n+m) 变 O(n×m)。

IN vs EXISTS —— 子查询结果集小用 IN，外层表小用 EXISTS。MySQL 5.6+ 对 IN 子查询做了半连接优化，差异已经不大，别过度纠结。

NULL 的代价 —— 可为 NULL 的列需要额外一个字节存 NULL 标记，且 NULL 参与比较要用 IS NULL，容易写错导致漏数据。能给默认值就加 NOT NULL DEFAULT。

字段类型尽量小 —— 能用 int 不用 bigint，能用 varchar(50) 不用 varchar(255)。原因不只是省磁盘：单页能放的行数变多 → 同样 IO 读到更多数据 → 树更矮。

最后于 更新