3.8 SQL 优化与索引调优：执行计划、索引设计与连接池

预计阅读时间：23 分钟

3.6：MySQL 数据库 MySQL 和 3.7：PostgreSQL 数据库 PostgreSQL 的安装配置只是数据库入门。生产环境中，查询性能才是核心问题。本文系统讲解 SQL 优化方法：EXPLAIN 执行计划解读、索引设计、慢查询分析和连接池配置。

学习目标

• 读懂 EXPLAIN 执行计划，快速定位全表扫描、文件排序、临时表等性能杀手
• 掌握索引设计的核心原则：复合索引列顺序、覆盖索引、索引失效场景
• 建立慢查询治理流程：开启日志、定期分析、跟踪优化效果
• 理解 OFFSET 分页与游标分页的取舍，掌握连接池（PgBouncer/ProxySQL）配置
• 识别常见 SQL 反模式，养成"先 EXPLAIN 再上线"的工程习惯

前置知识

• 3.6：MySQL 数据库 MySQL/MariaDB 安装与权限管理——知道如何连接数据库并执行查询
• 3.7：PostgreSQL 数据库 PostgreSQL 安装与 pg_hba.conf 配置——了解 PostgreSQL 与 MySQL 的差异
• SQL 基础语法：SELECT、JOIN、WHERE、ORDER BY、LIMIT
• 4.7：压力测试实战 压力测试实战——用 sysbench 验证优化前后的性能差异

1. EXPLAIN 执行计划

EXPLAIN 是 SQL 优化的起点。它告诉数据库如何执行一条查询——使用哪些索引、表访问顺序、预估行数。

MySQL EXPLAIN

EXPLAIN SELECT u.name, o.amount
FROM users u JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
ORDER BY o.amount DESC
LIMIT 20;

PostgreSQL EXPLAIN

EXPLAIN (ANALYZE, BUFFERS, TIMING) SELECT u.name, o.amount
FROM users u JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
ORDER BY o.amount DESC
LIMIT 20;

# PostgreSQL 习惯：每季度跑一次 ANALYZE 更新统计信息
ANALYZE;

type 访问方式全解

一次完整的 EXPLAIN 解读

mysql> EXPLAIN SELECT o.id, u.name
    -> FROM orders o JOIN users u ON o.user_id = u.id
    -> WHERE o.status = 'paid' AND o.created_at > '2025-06-01'
    -> ORDER BY o.id LIMIT 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: o
   partitions: NULL
         type: ref                      # 命中复合索引的等值部分
possible_keys: idx_status_created, idx_user_id
          key: idx_status_created       # 实际选用的索引
      key_len: 42                       # status 是 varchar(10) utf8mb4，42=40+2
          ref: const
         rows: 12500                    # 预估扫描 1.25 万行
     filtered: 22.5                     # 过滤后预计保留 22.5%
        Extra: Using index condition; Using temporary
# 解读：type=ref 正常；但 Extra 出现 Using temporary——
# ORDER BY o.id 与索引 (status, created_at) 的顺序不一致导致排序临时表。
# 修复：把 id 加入复合索引尾部（status, created_at, id），排序走索引。

key_len 的妙用：复合索引 (status, created_at) 中 status 是 varchar(10) 且 utf8mb4（每字符 4 字节），key_len = 10×4+2 = 42；如果 key_len 只有 42 说明只用了第一列，created_at 的范围过滤没用上索引——检查列顺序是否符合最左前缀。

rows 与 filtered 的判断技巧

• rows 接近表总行数：索引没命中或统计过时，先 ANALYZE TABLE 再看，仍大则查索引是否生效
• rows 小但 filtered 低（低于 10%）：索引筛选后还需大量回表过滤，考虑覆盖索引把过滤列并进去
• rows 与实际返回偏差大于 10 倍：统计信息过期，或列间相关性强（如 status='paid' 与 created_at 联动），可考虑 MySQL 8.0 的直方图

JOIN 算法：NLJ / BNL / Hash Join

# 判断当前走哪种算法：看 EXPLAIN 输出
# MySQL 老版本：Extra 出现 Using join buffer (Block Nested Loop) → BNL
# MySQL 8.0.18+ / PostgreSQL：节点名直接写 Hash Join / Nested Loop
# 优化方向：
# 1. 内表无索引 → 给 JOIN 列加索引（NLJ 生效，最常见最有效手段）
# 2. 大表等值 JOIN → 确保走 Hash Join，调大 work_mem（PG）/ join_buffer_size（MySQL）
# 3. 能拆则拆：JOIN 双方若可按主维度分片，拆成两个查询在应用层合并

执行计划深读：优化器为什么"不听"你的索引

加了对的索引但执行计划没变，先别怀疑索引——优化器选计划靠的是统计信息（行数、基数、分布）。MySQL 8.0 用 optimizer trace 查看优化器的完整决策过程：

-- MySQL：打开优化器追踪（默认最多记录 20 秒）
SET optimizer_trace='enabled=on';
SELECT * FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid' AND o.created_at > '2025-06-01'
ORDER BY o.id LIMIT 100;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
-- 关键看三段：
-- 1. "considered_execution_plans"：比较了哪些候选索引与连接顺序
-- 2. "chosen"：最终选了哪个、为什么（基于 cost 对比）
-- 3. "refine_plan"：是否追加了排序/临时表
SET optimizer_trace='enabled=off';

-- PostgreSQL：VERBOSE + JSON 格式看代价明细
EXPLAIN (VERBOSE, COSTS, FORMAT JSON) SELECT * FROM orders WHERE status = 'paid';
-- Total Cost 由"预估行数 × 单行代价"组成，行数来自 pg_statistic。
-- 行数不准先 ANALYZE；仍不准可提高采样精度：
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

2. 索引设计与类型

B-tree 索引（默认）

支持等值查询（=）、范围查询（>、<、BETWEEN）、前缀匹配（LIKE 'abc%'）。适用于大多数场景。

-- MySQL
CREATE INDEX idx_users_created ON users(created_at);

-- PostgreSQL（B-tree 是默认类型）
CREATE INDEX idx_orders_amount ON orders(amount DESC);

复合索引（多列）

最左前缀原则：索引 (a, b, c) 可以加速 WHERE a=?、WHERE a=? AND b=?、WHERE a=? AND b=? AND c=?，但无法加速 WHERE b=?。

-- 适合：WHERE status='active' AND created_at > '2025-01-01'
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 不适合：WHERE created_at > '2025-01-01'（跳过了 status）

其他索引类型

生产环境加索引的姿势

大表直接 ALTER TABLE ADD INDEX 会在 DDL 期间阻塞写入（MySQL 5.6 之前；5.6+ 的 ALGORITHM=INPLACE 已支持并发 DML，仅重建表类 DDL 如改列类型仍会阻塞），生产环境用在线变更工具。加索引本身只花几秒，但重建索引数据要数分钟到数小时，必须避开业务高峰：

# 1. 先在测试库 EXPLAIN 确认索引收益
# 2. 用 pt-online-schema-change 在线加索引（Percona Toolkit）
pt-online-schema-change --alter "ADD INDEX idx_user_created (user_id, created_at)" \
  --max-load Threads_running=100 --chunk-size=500 \
  D=mydb,t=orders --execute
# 原理：创建影子表 → 分块拷贝数据 + 应用 binlog 增量 → 原子切换表名
# 3. 加完索引立即更新统计信息
ANALYZE TABLE orders;
# 4. 观察一天慢查询日志，确认目标 SQL 消失

索引基数（Cardinality）与选择率

索引的价值取决于列的数据分布。基数 = 列中不同值的个数，选择率 = 过滤后行数 / 总行数。选择率越低索引越有价值，超过 20% 时优化器通常直接放弃索引：

-- 查看列的基数分布
SELECT COUNT(DISTINCT status) / COUNT(*) AS selectivity
FROM orders;

-- 查看优化器记录的索引基数（Cardinality 列）
SHOW INDEX FROM orders;
-- Cardinality 接近行数 → 高基数列；远小于行数 → 低基数列

3. 索引失效案例实战

案例一：隐式类型转换

-- 表结构：phone 是 VARCHAR(20)
SELECT * FROM users WHERE phone = 13800001111;   -- 数字字面量

-- EXPLAIN 结果：type=ALL，rows=100000（全表扫描）
-- 原因：MySQL 把 phone 隐式 CAST(phone AS DOUBLE) 再比较，
--       索引列被函数包裹 → 索引失效
-- 修复：用字符串字面量匹配列类型
SELECT * FROM users WHERE phone = '13800001111';
-- 修复后：type=ref，rows=1

案例二：函数包裹索引列

-- 慢查询日志抓到的语句
SELECT COUNT(*) FROM orders
WHERE DATE(created_at) = '2025-06-01';           -- 对索引列套 DATE()

-- 修复：改成等价的范围查询（能走索引）
SELECT COUNT(*) FROM orders
WHERE created_at >= '2025-06-01'
  AND created_at < '2025-06-02';
-- 修复后 rows 从 200 万降到当日行数

案例三：前导模糊匹配

SELECT * FROM articles WHERE title LIKE '%优化%';   -- 前导通配符
-- '%优化%' 无法利用 B-tree 索引的有序性 → 全表扫描
-- 修复方案（按数据量递进）：
-- 1. 可确定前缀的改后缀通配：LIKE '优化%' 能走索引
-- 2. 量级小（万级）：接受扫描，加应用层缓存
-- 3. 量级大：换全文索引
--    MySQL:  ALTER TABLE articles ADD FULLTEXT(title);
--             SELECT * FROM articles WHERE MATCH(title) AGAINST ('优化' IN BOOLEAN MODE);
--    PostgreSQL: CREATE INDEX ON articles USING gin(to_tsvector('zh', title));
--    前提：需安装 zhparser/pg_jieba 扩展并创建 'zh' 文本搜索配置（内置无中文分词配置，否则报错）

案例四：OR 条件

SELECT * FROM orders
WHERE status = 'paid' OR user_id = 12345;
-- 两边各有独立索引，但优化器难以把两个索引"并集"高效组合
-- → 选择全表扫描
-- 修复方案：
-- 1. 改写为 UNION，两段各自走索引
SELECT * FROM orders WHERE status = 'paid'
UNION
SELECT * FROM orders WHERE user_id = 12345;
-- 2. 高频组合条件建 (status, user_id) 复合索引，用 EXPLAIN 验证
-- 3. 应用层拆两次查询，内存合并结果（数据量小时最可控）

四个案例的共性：索引失效的本质是"破坏了索引列的有序性"——函数、类型转换、前导通配符都让 B-tree 无法利用有序性；OR 则让优化器无法确定单一扫描路径。修复思路永远是：让条件保持"索引列的原始形态 + 值"。

4. 慢查询日志

MySQL 慢查询

-- 开启慢查询日志（my.cnf）
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1           # 超过 1 秒的查询
log_queries_not_using_indexes = 1

# 常用分析工具
pt-query-digest /var/log/mysql/slow.log   # Percona Toolkit
mysqldumpslow /var/log/mysql/slow.log     # 内置工具

PostgreSQL 慢查询

-- postgresql.conf 配置
log_min_duration_statement = 1000   # 记录超过 1000ms 的查询
log_line_prefix = '%t [%p] '        # 时间 + PID
log_checkpoints = on
log_connections = on

-- 查询当前正在运行且耗时较长的查询
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC
LIMIT 10;

治理闭环：从日志到优化验证

# 1. 开启慢查询日志（见上文 my.cnf 配置），观察一天
systemctl restart mysql

# 2. 用 pt-query-digest 聚合分析（按总耗时排序，自动分组归类）
pt-query-digest --limit 20 /var/log/mysql/slow.log > digest.txt
# 输出按 profile 分组：总耗时、次数、平均/最大执行时间、样例 SQL

# 3. 聚焦 TOP 语句提取特征
pt-query-digest --limit 1 --group-by query /var/log/mysql/slow.log
# 输出示例：
# # Query 1: 0.00014 QPS, 0.009x concurrency, ID 0x1F2A...
# # Time range: 2026-07-30 00:00:00 to 23:59:59
# # Attribute    pct   total     min     max     avg     95%
# # ============ === ======= ======= ======= ======= =======
# # Count         20      12
# # Exec time    800s      2s     70s     66s     68s
# # Rows examine  25M    1.8M   2.1M   1.9M   1.9M
# # 解读：全天仅 12 次执行（约 0.00014 QPS）却耗 800 秒、每次扫描近 200 万行——典型的无索引大表扫描

# 4. 修复：加索引
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);

# 5. 验证闭环：重跑 EXPLAIN 对比 rows 与执行时间，
#    隔天再看 digest 中该 SQL 是否从 TOP 消失
pt-query-digest --limit 5 /var/log/mysql/slow.log

治理节奏建议：每周跑一次 digest，新出现的 TOP 语句按"出现频率 × 单次耗时"排序，先处理乘积最大的——修一条 70 秒的查询比修十条 1 秒的查询收益高一个量级。同时给慢日志配好 logrotate 轮转与按月归档，为容量规划保留历史数据。

5. 分页优化

传统 OFFSET 分页在大偏移量下性能极差——数据库仍需扫描并丢弃前 N 行。深翻页（超过 100 页）时延迟从毫秒级涨到秒级，且 OFFSET 越大劣化越严重。

游标分页（Keyset Pagination）

-- 传统方式（OFFSET 大时慢）
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;

-- 游标分页（利用索引快速定位）
SELECT * FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;

游标分页要求排序字段唯一且有索引。适合无限滚动场景。缺点是无法直接跳转到任意页码。

延迟关联（只对主键排序）

-- 先查主键，再关联回原表（减少回表次数）
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders
    ORDER BY created_at DESC
    LIMIT 20 OFFSET 100000
) AS sub ON o.id = sub.id
ORDER BY o.created_at DESC;

深分页方案对比

选型经验：后台管理列表（翻到 200 页看历史单）用延迟关联 + 索引兜底；用户端信息流（下拉加载）用游标分页；秒杀/热榜（数据量固定且小）直接缓存全量。唯一原则：排序字段必须有索引且结果稳定，否则任何方案都会翻页错乱。若产品强需求"显示总页数/跳页"，把 COUNT(*) 结果缓存到 Redis 或统计表，翻页本身仍走游标。

6. 连接池

每个数据库连接消耗约 1-2MB 内存。应用侧直连数据库在高并发时容易耗尽连接数。连接池复用连接，是生产标配。

PgBouncer（PostgreSQL）

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction        # 事务级复用
default_pool_size = 25          # 每个数据库的连接池大小
max_client_conn = 200           # 最大客户端连接
pkt_buf = 4096

# userlist.txt
"myuser" "md5xxxxxx"

ProxySQL（MySQL）

# ProxySQL 管理接口（mysql -h127.0.0.1 -P6032 -uadmin -padmin）

# 添加后端 MySQL 节点
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (0, '192.168.1.10', 3306);

# 配置连接池规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT', 0, 1);

# 加载运行时配置
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

# 保存到磁盘
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

ProxySQL 还支持读写分离、查询缓存、流量镜像。生产建议与 3.6：MySQL 数据库 MySQL 主从配合使用。

7. 常见 SQL 反模式

8. 优化流程总结

1. 定位：慢查询日志 → 找到耗时最长的 SQL
2. 分析：EXPLAIN ANALYZE → 确认全表扫描、排序、临时表
3. 设计：加复合索引 / 重写 SQL / 改分页方式
4. 验证：再跑 EXPLAIN ANALYZE，对比扫描行数和执行时间
5. 压测：用 sysbench 确认优化后 QPS/TPS 提升幅度
6. 监控：长期跟踪慢查询次数和数据库响应时间

整套流程的产出物建议写成"优化工单"：问题 SQL、EXPLAIN 前后对比截图、收益量化（耗时/扫描行数）、上线后慢查询趋势——积累三个工单后，你就能总结出自己业务的索引设计模式。

9. 分库分表：单库扛不住之后

索引与连接池能解决到单库千万级数据量；再往上，就要考虑把数据拆开。分库分表不是银弹，它把"SQL 问题"变成"分布式问题"，先看拆分的三种姿势：

水平分片是最后手段，触发前先确认三件事：单表已超千万行？索引与数据归档已做仍扛不住？写入吞吐超过单机上限？（MySQL 单实例 TPS 通常在 1-5 万量级）

分片键与路由策略

-- 分片键选型：必须来自高频 WHERE 条件，且取值分布均匀
-- 取模分片（user_id % 16 → 16 张表）
CREATE TABLE orders_0 LIKE orders;
CREATE TABLE orders_1 LIKE orders;
-- ... orders_15（应用层或中间件按 user_id % 16 路由）

-- 时间分片（按月归档、冷热分离）：orders_202506 / orders_202507

-- 全局唯一 ID：分片后单表自增主键不再唯一
-- 方案一：Snowflake（时间戳 + 机器 ID + 序列）
-- 方案二：数据库号段模式——每片预取一段连续 ID（如 1-10000）

分片后的问题清单

• 聚合与排序：COUNT/SUM/ORDER BY 需各分片并行执行后应用层归并（中间件自动处理）
• 分页：深分页必须逐片取满再归并，成本随分片数线性上涨——业务上限制跳页或用游标
• 跨分片事务：本地事务失效，改用最终一致（本地消息表/事务消息）或分布式事务（Seata AT/TCC）
• 扩容迁移：16 片扩到 32 片时取模路由全量失效——迁移期用"双写 + 追平"，或建表时预留槽位

中间件 vs 应用层路由

起步建议：先用读写分离 + 垂直拆分（ProxySQL / PgBouncer 都是现成的）撑到单表千万级；确实需要水平分片时优先 ShardingSphere-JDBC 的取模分片，并把"分片键 + 全局 ID"两个决策写进上线前评审清单。

10. 数据库监控：让性能问题提前暴露

慢查询治理是被动的——查询慢了才处理。监控体系让问题在"用户感知之前"暴露：连接数逼近上限、锁等待变长、复制延迟扩大，都是事故的前兆。

Prometheus + mysqld_exporter 接入

# 1. 为监控建最小权限账号
CREATE USER 'exporter'@'127.0.0.1' IDENTIFIED BY 'exporter_pass';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'127.0.0.1';

# 2. prometheus.yml 添加抓取任务
#   - job_name: mysql
#     static_configs:
#       - targets: ['127.0.0.1:9104']
#   （mysqld_exporter 默认端口 9104；PG 用 postgres_exporter，端口 9187）

# 3. 常用告警规则（PromQL，评估间隔 1 分钟）
# 连接数占比 > 80%：
#   mysql_global_status_threads_connected
#   / mysql_global_variables_max_connections > 0.8
# 缓冲池命中率 < 90%：
#   1 - (innodb_buffer_pool_reads
#   / (innodb_buffer_pool_reads + innodb_buffer_pool_read_requests)) < 0.9

锁等待排查脚本

-- PostgreSQL：谁在等锁、谁持有锁
SELECT blocked.pid AS blocked_pid,
       blocking.pid AS blocking_pid,
       blocked.query AS blocked_query,
       blocking.query AS blocking_query
FROM pg_stat_activity blocking
JOIN pg_stat_activity blocked
  ON blocked.pid IN (SELECT pid FROM pg_locks WHERE NOT granted)
 AND blocking.pid IN (SELECT pid FROM pg_locks WHERE granted)
WHERE blocked.state != 'idle' AND blocking.state != 'idle';

-- MySQL：当前事务与锁等待（InnoDB）
SELECT trx_id, trx_state, trx_query, trx_rows_locked
FROM information_schema.INNODB_TRX
ORDER BY trx_started LIMIT 10;

建立"指标 → 告警 → 工单"的闭环：把数据库指标接入 3.12：系统监控与告警 的 Prometheus/Grafana 大盘，锁等待与复制延迟单独拉高告警级别——这两类问题几乎从不自己恢复。

常见错误

最佳实践

1. 只选需要的列：避免 SELECT *，减少网络传输和回表开销。
2. 复合索引列顺序：等值条件在前，范围条件在后，排序字段尽量靠后。
3. 小事务原则：单次事务控制在 100-1000 行，避免长事务锁竞争。
4. 连接池必配：生产环境 PgBouncer / ProxySQL 不可少，pool_mode 建议 transaction。
5. 定期 ANALYZE：至少每季度跑一次 ANALYZE 更新统计信息，防止执行计划退化。
6. 加索引用在线工具：大表 DDL 用 pt-online-schema-change / gh-ost，不要在业务高峰期直接 ALTER。

练习题

1. 写一条包含 JOIN、WHERE、ORDER BY 的查询，用 EXPLAIN 分析其执行计划，找出可优化的点并重写。
2. 设计一个用户订单表的复合索引，覆盖「按状态筛选 + 按创建时间倒序 + 分页」的场景，并解释为什么这样设计。
3. 对比 OFFSET 分页和游标分页在 10 万行偏移量下的性能差异，用实际数据验证。
4. 在测试库复现四种索引失效场景（隐式转换/函数包裹/前导模糊/OR 条件），逐个用 EXPLAIN 验证修复前后的 type 与 rows 变化。
5. 用 pt-query-digest 分析慢查询日志（或手动造几条慢查询），按"频率 × 耗时"排序给出 TOP 3 优化建议。

学习检查点

学完本章后，请检验自己是否掌握以下内容：

本章总结

SQL 优化不是玄学，而是一条可复制的工程流程：先 EXPLAIN 看执行计划，再针对全表扫描和文件排序设计索引，用慢查询日志持续发现漏网之鱼，最后用连接池和游标分页解决规模问题。记住三个关键词：索引先行、EXPLAIN 验证、持续治理——任何优化都要用实际压测数据说话，而不是凭感觉。

延伸阅读