SQL 调优学习

一、调优

SQL 调优不是想到哪改到哪，而是一条有先后顺序的链路。先取计划，再定位代价最高的节点，判断是索引、统计信息还是写法问题，改完之后必须复跑对比，确认计划确实发生了预期变化。
有一个容易被忽略的环节，就是最后的复跑验证。建了索引不等于优化生效，如果统计信息没有更新，或者写法本身让索引列失效，计划可能仍然是全表扫描。没有经过计划对比就宣称优化完成，。

二、构造可复现的测试用例

先建一张订单明细表并灌入 100 万行数据。DM8 支持用 CONNECT BY 层级查询批量造数，配合 ROWNUM 一次插入即可，比逐行循环快得多。

代码 1　建表并构造 100 万行订单数据

CREATE TABLE T\_ORDER (

&#x20;   ID          INT PRIMARY KEY,

&#x20;   USER\_ID     INT,

&#x20;   ORDER\_TIME  DATETIME,

&#x20;   AMOUNT      DECIMAL(10,2),

&#x20;   STATUS      VARCHAR(8)

);

INSERT INTO T\_ORDER

SELECT ROWNUM,

&#x20;      MOD(ROWNUM, 10000) + 1,

&#x20;      DATE'2024-01-01' + MOD(ROWNUM, 365),

&#x20;      ROUND(DBMS\_RANDOM.VALUE(10, 1000), 2),

&#x20;      CASE WHEN MOD(ROWNUM, 3) = 0 THEN 'PAID' ELSE 'NEW' END

FROM SYSDUAL

CONNECT BY ROWNUM <= 1000000;

COMMIT;

造数完成后先收集一次统计信息，保证后续对比的基线是干净的。DM8 通过 DBMS_STATS 包收集表和索引的统计信息，优化器据此估算行数和代价。

代码 2　收集表统计信息

CALL DBMS\_STATS.GATHER\_TABLE\_STATS('SYSDBA', 'T\_ORDER');

三、看懂执行计划

在 SELECT、INSERT、UPDATE、DELETE 语句前加 EXPLAIN 关键字，DM8 会输出这条语句的执行计划但不实际执行它。计划中每一行是一个操作符节点，节点末尾的方括号是代价三元组，依次为代价、估计行数和字节数。控制流从上向下，数据流从下向上，真正扫表的节点在最底层。

代码 3　用 EXPLAIN 查看执行计划

EXPLAIN SELECT ID, USER\_ID FROM T\_ORDER

WHERE ORDER\_TIME BETWEEN '2024-06-01' AND '2024-06-30';

读计划的重点放在最底层的表访问节点，以及连接、分组这类高开销节点上。官方文档给出的方法是，操作符含义可查动态视图 VSQL_NODE_NAME，开启统计信息收集后，还可以对照 VSQL_NODE_HISTORY 查看每个操作符在实际执行中的耗时。下表列出了现场最常遇到的几个操作符。

操作符	含义	性能判断
CSCN2	聚集索引扫描，无合适二级索引时退化为全表扫描	行数大时是典型瓶颈
SSEK2	二级索引范围扫描	范围、等值查询的理想节点
CSEK2	聚集索引范围扫描	效率较高
SSCN2	二级索引全扫描	扫描整个二级索引，仍需警惕
BLKUP2	回表，按二级索引的 ROWID 回聚集索引取数据	产生二次 IO
HAGR2	HASH 分组，缓存全部数据后分组	数据量大时开销高
SAGR2	排序分组，依赖分组列有序	有索引支持时优先出现
HASH2 INNER JOIN	哈希连接，等值连接的常见方式	适合大表等值连接
NEST LOOP	嵌套循环连接	小表驱动大表时更优
SLCT2 / PRJT2 / NSET2	过滤、投影、结果集输出	对应 WHERE、SELECT、返回

四、案例一：范围查询的全表扫描

先看代码 3 那条按时间范围查询的语句。在 ORDER_TIME 没有任何索引时，计划最底层是 CSCN2，估计行数接近全表的 100 万。这意味着数据库要把整张表遍历一遍，再做 SLCT2 过滤。数据量继续增长，耗时随之线性上升。

代码 4　建索引前后的计划节点对比

\# 优化前（节选）：无索引，聚集索引全扫描

3   #SLCT2: ...

4   #CSCN2: \[代价, 1000000, 字节数]; 聚集索引(T\_ORDER)

CREATE INDEX IDX\_ORDER\_TIME ON T\_ORDER(ORDER\_TIME);

CALL DBMS\_STATS.GATHER\_TABLE\_STATS('SYSDBA', 'T\_ORDER');

\# 优化后（节选）：二级索引范围扫描

4   #BLKUP2: ...

5   #SSEK2: scan\_type(ASC), IDX\_ORDER\_TIME(T\_ORDER),

&#x20;           scan\_range('2024-06-01','2024-06-30')

建好索引并重新收集统计信息后，底层节点从 CSCN2 变为 SSEK2，扫描范围被限定在一个月的区间内，估计行数从百万级降到几万级。需要说明的是，代价三元组的具体数值随数据分布和版本变化，现场判断时重点看操作符类型和估计行数，不必纠结绝对值。

这里还藏着一个细节。由于查询写的是 SELECT ID, USER_ID，而索引只建在 ORDER_TIME 上，计划里出现了 BLKUP2。数据库先在二级索引上定位到 ROWID，再回到聚集索引取 USER_ID，这就是回表。

五、案例二：回表与覆盖索引

回表本身不是错误，但当查询只需要少数几列时，每一次回表都是一次额外的 IO。官方文档明确建议，可以把查询涉及的列放进二级索引，用空间换掉回表的开销。把索引改成包含查询列的覆盖索引后，BLKUP2 会消失。

代码 5　用覆盖索引消除回表

DROP INDEX IDX\_ORDER\_TIME;

CREATE INDEX IDX\_ORDER\_COVER ON T\_ORDER(ORDER\_TIME, ID, USER\_ID);

EXPLAIN SELECT ID, USER\_ID FROM T\_ORDER

WHERE ORDER\_TIME BETWEEN '2024-06-01' AND '2024-06-30';

\# 计划中仅剩 SSEK2，不再出现 BLKUP2

覆盖索引不是越多越好。索引列越多，占用空间越大，增删改时维护索引的代价也越高。是否值得建覆盖索引，要看这条查询的执行频率和回表行数，偶发查询没有必要为它专门建一个宽索引。

六、案例三：建了索引却没走

这是最容易让人困惑的场景：索引明明建了，计划却还是全表扫描。常见原因有两类，一类是统计信息陈旧，优化器不知道表已经变大，错误地估算了行数；另一类是写法让索引列失效。

统计信息问题在大批量数据导入、删除之后尤其突出。优化器依赖统计信息判断数据分布，信息过期就可能选错计划。处理方式是重新收集统计信息，必要时连同索引一起收集。

代码 6　重新收集表与索引统计信息

CALL DBMS\_STATS.GATHER\_TABLE\_STATS('SYSDBA', 'T\_ORDER');

CALL DBMS\_STATS.GATHER\_INDEX\_STATS('SYSDBA', 'IDX\_ORDER\_COVER');

写法问题主要出现在对索引列使用函数或做运算。比如在 ORDER_TIME 上套 TO_CHAR 再按字符串比较，优化器无法直接使用索引上的有序值，只能逐行计算。应把条件改写为对原始列的范围比较。

代码 7　避免在索引列上使用函数

\# 索引列被函数包裹，难以走范围扫描

SELECT \* FROM T\_ORDER

&#x20;WHERE TO\_CHAR(ORDER\_TIME,'YYYY-MM-DD') = '2024-06-15';

\# 改写为对原始列的范围比较，可命中 SSEK2

SELECT \* FROM T\_ORDER

&#x20;WHERE ORDER\_TIME >= '2024-06-15 00:00:00'

&#x20;  AND ORDER\_TIME <= '2024-06-15 23:59:59';

LIKE 匹配同理。前导通配的 LIKE ‘% ABC’ 无法利用索引的有序性，只能全表扫描；以固定前缀开头的 LIKE ‘ABC%’ 可以优化为范围扫描。如果业务确实需要按后缀匹配，官方文档给出的办法是在 REVERSE (列) 上建函数索引，把后缀匹配反转成前缀匹配。

七、案例四：分组查询从 HAGR 到 SAGR

统计每个用户的订单数时，GROUP BY 在没有索引支持的情况下走 HAGR2，也就是 HASH 分组。它要把中间结果缓存起来再聚合，数据量大时内存和 IO 开销都不低。在分组列上建索引后，数据天然有序，优化器可以切换为 SAGR2 排序分组。

代码 8　GROUP BY 列建索引切换分组方式

\# 优化前：HASH 分组

EXPLAIN SELECT USER\_ID, COUNT(\*) FROM T\_ORDER GROUP BY USER\_ID;

\#   #HAGR2: ...

CREATE INDEX IDX\_ORDER\_UID ON T\_ORDER(USER\_ID);

CALL DBMS\_STATS.GATHER\_TABLE\_STATS('SYSDBA', 'T\_ORDER');

\# 优化后：排序分组

\#   #SAGR2: ...

官方文档对 GROUP BY 还有一条建议：HAVING 中与集函数无关的过滤条件，应当下放到 WHERE。虽然优化器会尝试自动转换一部分等价写法，但显式写出更清晰的 SQL，能减轻优化器的判断负担。

八、从写法上减少不必要的开销

索引能解决访问路径问题，SQL 写法决定了数据库要做多少无用功。官方《SQL 调优》章节给出了一组开发有效 SQL 的原则，结合测试表整理如下。

同一列上的多个 OR 条件，改写为 IN 列表。OR 在执行时可能被转换为类似 UNION 的处理，其中一个分支用不上索引就会拖垮整条语句。

只取需要的列，避免 SELECT *。列越少，回表和数据传递的开销越小，列存表的 IO 优势也不会被抵消。

统计行数优先用 COUNT (*)。单表且无过滤条件时，优化器直接读取索引中存储的行数，不必回表取数据；COUNT (列名) 不统计 NULL，口径不同，需按业务选择。

不要求去重时用 UNION ALL 代替 UNION。UNION 要建 HASH 表去重，数据量大时还会刷盘。

HAVING 中与集函数无关的条件移到 WHERE，尽早过滤，减少参与分组的数据量。

索引设计上有两条经验。一是联合索引遵循最左匹配，列顺序一般按等值过滤、范围过滤、排序的优先级安排，让区分度高、过滤性强的列靠前。二是索引并非越多越好，写频繁的表上每多一个索引，插入、更新、删除都要多维护一份，函数索引的计算开销尤其不能忽略。

九、HINT：当优化器的选择与经验不符

绝大多数情况下应当让优化器自己选计划，但在统计信息受限、或对数据分布有明确判断时，可以用 HINT 提示指定索引。DM8 的索引 HINT 有两种写法，一种是表名后直接跟 INDEX 索引名，另一种是注释形式。一条语句最多指定 8 个索引，如果 SQL 里给表起了别名，HINT 中必须使用别名。

代码 9　INDEX / NO_INDEX / FULL 提示

SELECT \* FROM T\_ORDER INDEX IDX\_ORDER\_UID

&#x20;WHERE USER\_ID = 100;

SELECT /\*+ INDEX(T\_ORDER, IDX\_ORDER\_UID) \*/ \*

&#x20; FROM T\_ORDER

&#x20;WHERE USER\_ID = 100;

SELECT /\*+ NO\_INDEX(T\_ORDER, IDX\_ORDER\_UID) \*/ \*

&#x20; FROM T\_ORDER

&#x20;WHERE USER\_ID = 100;

SELECT /\*+ FULL(T\_ORDER) \*/ \* FROM T\_ORDER WHERE USER\_ID = 100;

HINT 是兜底手段而不是常规手段。它把访问路径写死在语句里，数据分布变化后不会自动调整，用错了反而会锁住一个糟糕的计划。现场优先解决统计信息和索引问题，确认优化器确实判断失误，再考虑 HINT，并且要在注释里写明使用原因。