数据库性能优化,在很多人眼里是门"玄学"——慢了就加个索引,还慢就加内存,再慢就分库分表。这套凭感觉的打法,偶尔管用,经常把系统搞得更糟。

真正的性能优化是有方法论的:先定位瓶颈在哪,再看执行计划为什么慢,然后针对性地改索引 / 改 SQL / 改结构,最后验证。 跳过前两步直接动手,是优化最大的原罪。

本文以 Oracle 为主线（它的 AWR / ASH / DBMS_XPLAN 工具链最完整,是 DB 性能方法论的"参考实现"）,在每个分叉点给出 MySQL / PostgreSQL / SQL Server 的命令对照。原理通用,命令分库——方法论可以平移到任何一个关系数据库。

这是数据库系列的实战补充:系列（三）讲了索引是什么、（九）讲了优化器怎么工作,这篇回答的是手上拿到一个慢查询,怎么把它调快。

一、方法论:先定位,再优化

把优化分成三个层次,自顶向下,先确定"病在哪一层"再开方:

1
2
3
4
5
6
7
8

系统层   系统整体慢吗?瓶颈是 CPU / IO / 锁 / 等待?
   │      → 看等待事件、资源使用率
   ▼
会话层   是某几个会话 / 某几条 SQL 拖垮的吗?
   │      → 找 Top SQL、活跃会话
   ▼
SQL 层   这条 SQL 为什么慢?
          → 看执行计划,改索引 / 改 SQL

最大的反模式:跳过定位,直接"加索引"。 索引不是越多越好——它加速读,却拖慢写、占空间、还可能因为统计信息不准而根本不被使用。正确的姿势永远是:先用工具看清"慢在哪、为什么慢",再做最小且对症的改动。

二、第一步 · 定位慢查询

优化的第一步不是改 SQL,而是找出哪条 SQL 值得改。两个维度:累计耗时最长的和执行次数最多 × 单次代价的。

Oracle 主线:AWR + ASH + V$SQL

• AWR(Automatic Workload Repository):周期性快照,记录系统统计。两个快照之间生成报告,直接看 “Top SQL by Elapsed Time / by Buffer Gets”。

  1	sqlplus / as sysdba @?/rdbms/admin/awrrpt.sql # 选快照范围,生成 HTML 报告

• ASH(Active Session History):每秒采样一次活跃会话,用于定位"某个时间点系统在等什么"。V$ACTIVE_SESSION_HISTORY。
• V$SQL 找 Top SQL:

  1 2 3 4 5

  -- 平均单次耗时最长的 SQL SELECT sql_id, executions, elapsed_time/1e6 AS elapsed_s, ROUND(elapsed_time/GREATEST(executions,1)/1e6, 3) AS avg_s FROM v$sql ORDER BY avg_s DESC FETCH FIRST 20 ROWS ONLY;

• 等待事件(wait events)判断瓶颈类型,这是 Oracle 方法论的精华:
  • db file sequential read → 在做索引单块读(正常,但过多说明扫描次数多)
  • db file scattered read → 在做全表/全索引扫描(可疑)
  • log file sync → 提交(commit)代价,事务粒度太细
  • enq: TX - row lock contention → 行锁等待,并发写冲突
  • buffer busy waits / latch: ... → 热块 / 内存结构竞争

四库对照:慢查询定位工具

通用法则:先看等待事件回答"在等什么"(CPU / IO / 锁 / 日志),再用 Top SQL 回答"谁在等"。两者一交叉,病根就出来了。

三、第二步 · 读懂执行计划

这是性能优化最核心的技能。看不懂执行计划,等于在黑暗里改 SQL。

Oracle 主线:DBMS_XPLAN

• 估算计划(不实际执行):

  1 2	EXPLAIN PLAN FOR SELECT ... ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

• 真实计划(执行后看真实行数 / 时间,这才是生产排障用的):

  1 2 3 4

  -- 先让本次会话收集执行统计 ALTER SESSION SET STATISTICS_LEVEL = ALL; -- 执行目标 SQL,然后取其游标的真实计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

  ALLSTATS LAST 会显示 A-Rows(真实行数)、Starts(执行次数)、A-Time(真实耗时)、Buffers(逻辑读)。

看计划时盯住这几样(通用)

1. 真实行数 vs 估算行数:差一个数量级以上 → 统计信息过期,优化器基于错误估算选了烂计划。这是性能问题头号元凶。
2. 访问方式:TABLE ACCESS FULL(全表扫)、INDEX RANGE SCAN(索引范围扫)、INDEX UNIQUE SCAN(索引唯一扫)。全表扫不一定坏(小表、或返回大比例数据时全扫反而更快),但大表全扫 + 高频 = 红灯。
3. JOIN 方式:NESTED LOOPS(适合驱动表小、内表走索引)、HASH JOIN(适合大表等值连接)、SORT MERGE JOIN。选错驱动表或 JOIN 方式是常见瓶颈。
4. 有没有落盘排序/临时:SORT 但内存不够 → 写 temp,代价陡增。

四库对照:执行计划

通用判断:统计信息要准(这是地基);警惕"估算行数远小于真实行数"导致的错误 JOIN 顺序;任何"全表扫大表 + 高频"或"落盘排序"都值得深究。

四、第三步 · 索引调优

这是性价比最高、最通用的优化手段。系列（三）讲了索引的数据结构,这里讲怎么用索引提速。

通用法则

• 在 WHERE / JOIN / ORDER BY / GROUP BY 的列上建索引,优先选择性高(基数大)的列。
• 复合索引与最左前缀:(a, b, c) 能服务 a、(a,b)、(a,b,c),但不能服务 b 单独。把等值条件列放前、范围条件列放后。
• 覆盖索引避免回表:查询列都在索引里,就不用回表取数据。SELECT 别写 *,只取需要的列,才有机会用覆盖索引。
• 警惕索引失效的写法(让优化器放弃索引、退回全表扫):
  • 函数包裹列:WHERE YEAR(create_time)=2024 → 改 WHERE create_time >= ... AND create_time < ...
  • 隐式类型转换:WHERE phone = 13800000000(列是字符,值是数字)→ 统一类型
  • 前导通配:WHERE name LIKE '%张' → 走不了普通 B 树索引
  • OR 两边不全有索引 → 考虑拆 UNION ALL 或补索引
  • IS NULL 的处理各库不同(PG 会索引 NULL,部分库默认不索引)

Oracle 索引类型补充

• B-tree(默认)、位图索引(低基数列、数据仓库,OLTP 慎用)、反键索引(单调递增键消除热块)、函数索引(CREATE INDEX i ON t(UPPER(name)))、索引组织表(IOT)。

四库对照:索引能力

通用铁律:加了索引要验证它真被用了(看执行计划),并保持统计信息新鲜。索引建了却没被用,是最常见的"优化了个寂寞"。

五、第四步 · SQL 改写

写法不对,再好的索引也救不了。下面这些反模式几乎全库通用。

通用反模式

• SELECT *:多取列 → 无法用覆盖索引、增加网络与内存开销。永远只取需要的列。
• N+1 查询:循环里逐条查(通常是 ORM 的锅)。→ 改成一次 IN / 批量查。
• 深分页:OFFSET 1000000 LIMIT 20 要先扫过前 100 万行。→ keyset(游标)分页:WHERE id > :last_id ORDER BY id LIMIT 20。
• IN 子查询:部分库优化器对 IN (subquery) 优化不佳。→ 改 JOIN 或 EXISTS。
• COUNT(*) 全表:大表上很贵。→ 业务能接受就用近似值,或维护计数表。
• 笛卡尔积 / DISTINCT 滥用:JOIN 条件缺失或多余的去重,常常是中间结果集爆炸的根源。
• 相关子查询:每行都执行一次。→ 改成 JOIN 或窗口函数。

四库对照:分页与批量写语法

改写的核心思想:让优化器能走索引、减少中间结果集、减少排序与聚合代价。每改一处,用执行计划前后对比验证。

六、第五步 · 写优化与批量

读优化讲得多,但写往往是系统瓶颈所在。

• 批量 > 循环单条:Oracle 用 FORALL 批量绑定、多行 INSERT、SQL*Loader / 外部表;MySQL LOAD DATA INFILE;PG COPY;SQL Server BULK INSERT / SqlBulkCopy。逐条 INSERT + 逐条 commit 是性能杀手。
• 合理的事务粒度:log file sync(Oracle)/ binlog 同步代价来自 commit。太小(commit 太频繁)→ 日志成为瓶颈;太大 → 锁持有久、回滚贵。按业务折中。
• 大批量写入前先减负:临时禁用非关键索引(建完数据再建)、Oracle 用 NOLOGGING + APPEND 减少 redo。
• 顺序写优于随机写:自增主键 / 序列插入顺序,减少索引页分裂与热点。

七、第六步 · 结构与连接层

SQL 和索引调到头了,就上结构层:

• 连接池:短连接每次建连 + 认证开销巨大。务必用连接池(Oracle 用 UCP / 应用侧 HikariCP,PG 配 pgbouncer,MySQL/SQL Server 用应用侧池),池大小不是越大越好(数据库的进程/线程和内存有上限)。
• 读写分离:读多写少的场景,主写从读,把读压力分摊到副本。
• 分区(Partitioning):大表按范围 / 列表 / 哈希分区,配合分区裁剪(partition pruning),让查询只扫相关分区。
• 反范式 / 冗余:为了少 JOIN,适度冗余高 frequency 读取的字段(用空间换时间,要权衡一致性)。
• 缓存层:热点数据放 Redis,挡在数据库前面。

四库对照:分区

八、避坑清单 + 结语

常见坑:

• 凭感觉加索引,不验证是否真被用、不收集统计信息。
• 不看执行计划就改 SQL,改完不对比。
• 只盯着单条 SQL,忽略等待事件暴露的系统性问题。
• OFFSET 深分页、N+1、SELECT * 这类反模式。
• 忽略写入代价:逐条 commit、大批量写入时不减负。
• 过度优化:为了 1% 的场景把系统搞复杂,得不偿失。

结语:

数据库性能优化不是"加索引"三个字,而是一套先定位、再诊断、后动手的工程方法论。它的通用内核是:用等待事件回答"系统在等什么",用执行计划回答"这条 SQL 为什么慢",用索引 / SQL / 结构三层武器对症下药,每一步都用数据(执行计划、统计)验证,而不是靠手感。

这套方法论在 Oracle 上最成熟(AWR / ASH / DBMS_XPLAN 是标杆),但思想完全平移到 MySQL、PostgreSQL、SQL Server——工具名换了,逻辑不变。掌握了它,你面对任何一个"慢"字,都不再是玄学,而是可拆解、可验证的工程问题。