SQL 调优
SQL 调优按三步走：先用 slow query log、pg_stat_statements、performance_schema 找出总耗时最高的语句，再用 EXPLAIN ANALYZE 看它慢在哪，最后改写、加索引或调内存并复测。讲清深分页 OFFSET 改 keyset 的算例、MySQL 行构造器比较用不全索引的坑、work_mem 与 buffer pool 的文档建议值、已删除的 query cache，以及面试答法。
SQL 调优不是背一串技巧，而是回答三个问题：哪条语句占了数据库最多的时间、它的执行计划慢在哪一步、改完之后怎么证明变快了。 索引结构本身（B+ tree、最左前缀、覆盖索引、EXPLAIN 各字段含义）见 Indexes，这一章讲排查流程和索引以外的修法。
利用基准测试和 performance 分析来模拟和发现系统瓶颈很重要：
- 基准测试：用 ab 压 HTTP 接口，或用 pgbench 直接压 PostgreSQL，模拟高负载情况。
- performance 分析：启用 slow query log、
pg_stat_statements等工具追踪 performance 问题。
有约束的设计问题
一个订单服务（MySQL 8.4 或 PostgreSQL，下文两边都给写法），orders 表 5,000 万行。上线「订单导出」和「客户订单列表翻页」两个功能后，数据库 CPU 从 30% 涨到 90%，订单 API 的 p99 从 80 ms 涨到 2 s。数字是本章的假设。先别急着加索引：要先知道是哪条语句。
第一步：按总耗时排出最贵的语句
慢查询日志只抓单次超过阈值的语句，一条 5 ms、每秒跑 3,000 次的语句不会进日志，却可能是真正的大户。所以两类工具都要看：
MySQL
# my.cnf
slow_query_log = ON
long_query_time = 0.2 # 文档写明默认值是 10 秒，线上排查通常调低
-- performance_schema 按语句模板（digest）聚合，时间单位是皮秒
SELECT DIGEST_TEXT, COUNT_STAR,
SUM_TIMER_WAIT / 1e12 AS total_s,
SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
PostgreSQL
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'
log_min_duration_statement = 250ms # 默认 -1，即不记录
auto_explain.log_min_duration = 500ms # 超过阈值的语句把执行计划写进日志
SELECT calls,
round(mean_exec_time::numeric, 1) AS mean_ms,
round(total_exec_time::numeric) AS total_ms,
shared_blks_read, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
看三个数：总耗时（决定先修谁）、调用次数（高频小查询往往是 N+1，修法在应用层，见 SQL databases）、扫描行数和返回行数的比例（SUM_ROWS_EXAMINED 远大于 SUM_ROWS_SENT 说明在大量读了再丢）。
第二步：用 EXPLAIN ANALYZE 找慢在哪一步
- MySQL：
EXPLAIN ANALYZE SELECT ...执行语句并输出每一步的实际耗时和行数。 - PostgreSQL：
EXPLAIN (ANALYZE, BUFFERS) SELECT ...，对比每个节点估计的rows和实际的actual rows，看Buffers里有多少页是真正从磁盘读的。
两边的 ANALYZE 都会真的执行语句，排查 UPDATE / DELETE 时放在事务里执行后回滚。各字段的读法见 Indexes 的「读 EXPLAIN 看什么」一节。
第三步：按症状选修法
| 症状（计划里看到的） | 原因 | 修法 |
|---|---|---|
| 扫描行数随页码线性增长 | 深分页 LIMIT 20 OFFSET 99980 | 改 keyset 分页，见下面的算例 |
| 有合适的索引却走了全表扫描 | 列上套了函数或类型不匹配 | 改写成范围条件；参数类型与列类型一致；或建表达式索引 |
| 估计行数和实际行数差几个数量级 | 统计信息过期 | PostgreSQL ANALYZE orders，MySQL ANALYZE TABLE orders；大批量导入后立即执行 |
PostgreSQL 排序节点显示 Sort Method: external merge | 排序超过 work_mem，写了临时文件 | 让索引提供顺序免排序；或只给这个会话 SET work_mem，不要全局调大 |
| 语句本身很快，但日志里耗时很长 | 在等锁，不是计划问题 | PostgreSQL 看 pg_stat_activity 的等待事件，MySQL 看 performance_schema.data_lock_waits；缩短持锁事务 |
| 同一模板被调用几万次 | 应用层 N+1 或逐行写入 | 批量读；多行 INSERT，PostgreSQL 大批量导入用 COPY |
| 返回了用不到的大字段 | SELECT * 带出 TEXT / JSON 列 | 只选需要的列，必要时用覆盖索引 |
算一遍：深分页改 keyset
客户订单列表按时间倒序，每页 20 条，索引是 (customer_id, created_at, id)。
-- OFFSET：第 5,000 页要先按顺序走过 99,980 行再丢掉
SELECT id, created_at, total_cents FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 99980;
PostgreSQL 文档写明，被 OFFSET 跳过的行仍然要在服务器内计算，大 OFFSET 可能很低效。第 1 页读 20 行，第 5,000 页读 100,000 行，代价随页码线性增长；导出功能从头翻到尾，总共读的行数约是 20 ×（1 + 2 + … + 5,000）≈ 2.5 亿行次。
Keyset 分页带上一页最后一行的位置，每一页都只读 20 行：
-- PostgreSQL：行比较可以直接用上 (customer_id, created_at, id) 索引
SELECT id, created_at, total_cents FROM orders
WHERE customer_id = 42
AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- MySQL：展开写
WHERE customer_id = 42
AND (created_at < :last_created_at
OR (created_at = :last_created_at AND id < :last_id))
MySQL 要展开写的原因在官方文档里：c1 = 1 AND (c2, c3) > (1, 1) 这种行构造器没有覆盖索引前缀，优化器只用上了 c1，文档建议改写成等价的非构造器表达式。
Keyset 的代价：只能「下一页 / 上一页」，不能直接跳到第 3,721 页；接口要返回游标而不是页码。对导出这种从头读到尾的需求，这正好够用。
Schema 层面的建议
这部分沿用原章节，修正了几处不准确的说法：
CHAR存固定长度的值（国家代码、定长编码）。MySQL 文档写明VARCHAR用 1 或 2 字节的长度前缀加数据存储，前缀记录的是字节数：最大字节数不超过 255 用 1 字节，否则用 2 字节。所以 utf8mb4 下的VARCHAR(255)最多 1,020 字节，用的是 2 字节前缀，「255 刚好省一个字节」只在单字节字符集下成立。- 大块文本（博客正文）用
TEXT。需要全文搜索时建FULLTEXT索引（MySQL）或用tsvector+ GIN（PostgreSQL），而不是LIKE '%keyword%'。 - 整数按范围选：MySQL
INT是 4 字节，有符号上限 2,147,483,647，无符号上限 4,294,967,295；自增主键可能超过 21 亿的，一开始就用BIGINT，事后改类型是大表的全量重写。 - 货币用
DECIMAL（或以分为单位的整数），避免浮点误差。 - 避免在数据库里存大文件（
BLOB），存对象存储的 key，文件放 对象存储。 - 能
NOT NULL的列设成NOT NULL：它首先是数据约束，让「缺值」只有一种表示。 - 加载大量数据时，先删二级索引、导入、再重建，可能比边导边维护更快；导入后执行
ANALYZE。 - 有性能需要时可以 denormalize 避免高成本 join，把热点数据拆到单独的表里，但每份冗余都要说清谁负责更新。
服务器内存：先看文档给的起点
| 参数 | 文档说法 | 注意 |
|---|---|---|
MySQL innodb_buffer_pool_size | 专用数据库服务器上，常把最多 80% 的物理内存分给 buffer pool | 同机还跑别的进程时要减 |
PostgreSQL shared_buffers | 1 GB 以上内存的专用服务器，合理的起点是内存的 25% | PostgreSQL 同时依赖操作系统页缓存，不是越大越好 |
PostgreSQL work_mem | 默认 4 MB；一个复杂查询可能同时有多个排序和哈希操作，每个都能用到这么多，多个会话还会同时执行 | 全局调大很容易在并发高峰时耗尽内存；按会话或按角色调 |
MySQL query cache 已经没有了。 老资料会建议「调优查询缓存」，MySQL 8.0 的新特性说明里写明 query cache 已被移除。要缓存查询结果，放到应用层的 缓存 里，并自己负责失效。
常见翻车
| 翻车 | 用户看到什么 | 修法 |
|---|---|---|
| 只看 slow log，漏掉高频小查询 | 数据库 CPU 一直很高，slow log 里却没几条 | 用 pg_stat_statements / digest 表按总耗时排序 |
在生产主库上对写语句跑 EXPLAIN ANALYZE | 数据被真的改了 | 放进事务后回滚，或在副本、预发环境上跑 |
| 为一条慢查询加索引，没算写入代价 | 查询快了，写入 p99 变差 | 每个索引对应至少一个查询；定期删掉未使用的索引 |
全局调大 work_mem | 平时正常，并发高峰时数据库被 OOM 杀掉 | 按会话设置；用索引免排序 |
| 大批量导入后没更新统计信息 | 导入后查询计划突然变差 | 导入后立即 ANALYZE |
| 深分页导出 | 导出越往后越慢，最后超时 | keyset 分页或按主键范围分批 |
| 把锁等待当成慢查询优化 | 加了索引依然慢 | 先看等待事件，找到长事务 |
面试时这样回答
- 先定位。 「我会先按总耗时排出前十条语句模板，而不是凭感觉加索引」，说出
pg_stat_statements或events_statements_summary_by_digest。 - 再看计划。
EXPLAIN ANALYZE看估计行数与实际行数、扫描方式、有没有排序落盘。 - 按症状给修法。 深分页改 keyset、函数包列改范围、统计信息过期就
ANALYZE、N+1 改批量，索引只是其中一种。 - 算一个数。 例如 OFFSET 第 5,000 页读 10 万行，keyset 每页只读 20 行。
- 说验证和代价。 改完复测同一个指标；索引增加写入成本，keyset 失去跳页能力，内存参数要按并发算。
相关章节：Indexes、SQL databases、Normalization vs Denormalization、Transactions、Cache 模式详解、Database Replication。
一手证据
- MySQL：The Slow Query Log
- MySQL：Performance Schema Statement Summary Tables
- MySQL：Performance Schema Event Timing（皮秒）
- MySQL：EXPLAIN / EXPLAIN ANALYZE
- MySQL：Row Constructor Expression Optimization
- MySQL：The CHAR and VARCHAR Types
- MySQL：Integer Types
- MySQL：InnoDB Buffer Pool
- MySQL 8.0：What Is New（query cache 已移除）
- PostgreSQL：pg_stat_statements
- PostgreSQL：auto_explain
- PostgreSQL：Error Reporting and Logging（log_min_duration_statement）
- PostgreSQL：Using EXPLAIN
- PostgreSQL：LIMIT and OFFSET
- PostgreSQL：Resource Consumption（shared_buffers、work_mem）
- Apache HTTP Server：ab
- PostgreSQL：pgbench
场景中的 CPU、p99 和表规模是假设值；OFFSET 的行数是按执行方式推出来的，不是基准测试结果。