Skip to main content

我以为是在优化 SQL，结果越跑越慢了

2025/10/19大约 11 分钟WorkSQLDatabasePerformance

这次问题发生在一个 Oracle 查询上。业务页面要展示一批物料在仓储流程里的状态，数据来自本地表，也来自一张通过 DB Link 访问的远程明细表。页面慢，用户看到的是转圈，开发看到的是一段很长的 SQL。

我第一次看这段 SQL 的反应很直接：太乱了，先整理一下。

它里面有 CTE，有远程表，有 CONCAT(CREATION_DATE, CREATION_TIME)，有 SUBSTR 截取库位号后 10 位，有 TO_CHAR(rm_tag, 'hh24miss') 判断班次，还有一堆状态 CASE WHEN。从代码可读性上看，确实有很多可以改的地方。问题是，数据库不按人的阅读体验执行。

下面的 SQL 是脱敏后的问题片段，表名、schema、DB Link 都做了替换。我没有把所有维表和展示字段放进来，只保留这次性能判断有关的结构。

WITH tmp_order AS (
  SELECT
    MIN(CONCAT(creation_date, creation_time)) AS time_data,
    dest_storage_unit
  FROM remote_wm_transfer_order@remote_link
  WHERE LENGTH(material) = 13
    AND source_storage_type IN ('981', '9IB')
  GROUP BY dest_storage_unit
),
tmp_oits AS (
  SELECT
    su,
    pn,
    MIN(create_time) AS create_time
  FROM ods_transfer_state
  GROUP BY su, pn
)
SELECT
  ddwt.material,
  ddwt.dest_storage_unit,
  opph.rm_tag,
  oits.create_time,
  CASE
    WHEN TO_CHAR(opph.rm_tag, 'hh24miss') BETWEEN '070000' AND '145900' THEN '0'
    WHEN TO_CHAR(opph.rm_tag, 'hh24miss') BETWEEN '150000' AND '225900' THEN '1'
    WHEN TO_CHAR(opph.rm_tag, 'hh24miss') >= '230000'
      OR TO_CHAR(opph.rm_tag, 'hh24miss') <= '065900' THEN '2'
  END AS shift
FROM remote_wm_transfer_order@remote_link ddwt
JOIN tmp_order tmpo
  ON tmpo.dest_storage_unit = ddwt.dest_storage_unit
 AND tmpo.time_data = ddwt.creation_date || ddwt.creation_time
LEFT JOIN tmp_oits oits
  ON oits.su = SUBSTR(ddwt.dest_storage_unit, -10, 10)
 AND oits.pn = ddwt.material
LEFT JOIN ods_pallet_hu opph
  ON SUBSTR(ddwt.dest_storage_unit, -10, 10) = SUBSTR(opph.hu_number, -10, 10)
WHERE opph.rm_tag IS NOT NULL
  AND TO_CHAR(opph.rm_tag, 'yyyymmddhh24miss') > '20241014000000'
  AND TO_CHAR(opph.rm_tag, 'yyyymmddhh24miss') <= '20241114000000';

我当时犯的第一个错误，是把“SQL 写得更清楚”和“SQL 执行得更快”混在一起了。

第一次改写为什么不靠谱

我最开始的思路是把中间结果拆出来。比如先把远程表里每个 dest_storage_unit 最早的时间算出来，再和主查询 Join。这个想法看上去合理，因为主查询里重复逻辑确实很多。

但这里有几个坑。

第一个坑是远程表。remote_wm_transfer_order@remote_link 不是本地表，查询计划里会出现 REMOTE 节点。只要过滤条件没有尽早推到远端，或者远端返回的数据量太大，本地这边再怎么整理 SQL，也可能只是把网络传输和本地 Hash Join 的成本放大。

第二个坑是函数包裹字段。比如：

SUBSTR(ddwt.dest_storage_unit, -10, 10) = SUBSTR(opph.hu_number, -10, 10)

这段对业务来说很自然：两个字段格式不完全一样，只能拿后 10 位匹配。但从数据库角度看，它不再是两个列的直接比较。普通索引很难直接发挥作用，除非提前沉淀后 10 位字段，或者建立函数索引。

第三个坑是时间字段。远程表里日期和时间是两个字段，查询里把它们拼成一个字符串再取最小值。

MIN(CONCAT(creation_date, creation_time))

这不是不能用，但它有前提：日期和时间必须保持固定格式，比如日期永远是 YYYYMMDD，时间永远补齐到 6 位。如果有脏数据，或者 000000 这类占位值混进来，结果就会变得很脆弱。更麻烦的是，这种写法也会影响优化器对索引和选择性的判断。

我后面还踩过一个很典型的坑：为了“优化”，尝试把日期和时间转成一个统一时间字段，结果引用了实际表里不存在的列，直接报错。

ORA-00904: invalid identifier

这个错误本身不复杂，就是引用了实际表里不存在的字段。但它提醒我一件事：不要在没确认表结构的情况下，想当然地把模型改成自己希望的样子。现实里的生产表经常不是理想模型。

执行计划里真正刺眼的地方

后来我停下来先看执行计划。下面是脱敏后的计划片段。

SELECT STATEMENT
  HASH JOIN RIGHT OUTER
    NESTED LOOPS OUTER
      HASH JOIN
        HASH JOIN OUTER
          HASH JOIN
            VIEW
              REMOTE
            HASH JOIN
              TABLE ACCESS FULL ODS_WH_STORAGE_TYPE
              REMOTE REMOTE_WM_TRANSFER_ORDER
        VIEW
          HASH GROUP BY
            TABLE ACCESS FULL ODS_TRANSFER_STATE
      TABLE ACCESS FULL ODS_PALLET_HU
        filter: TO_CHAR(rm_tag, 'yyyymmddhh24miss') > '20241014000000'

当时最值得注意的不是“出现了全表扫描”这一个点。全表扫描不一定错。如果返回比例很高，或者表本身很小，全表扫描可能比走索引更合适。真正要看的是几个信号叠在一起：

REMOTE 节点参与了大数据量传输。
HASH JOIN 之前，远程表数据没有明显被压小。
SUBSTR 后缀匹配导致连接条件不可直接利用普通索引。
TO_CHAR(rm_tag) 出现在过滤条件里，时间过滤没有保持字段可搜索状态。
tmp_oits 先 GROUP BY，再参与后续 Join，形成了一个较大的中间结果。

执行计划里有一段远程表节点的估算非常夸张：

REMOTE REMOTE_WM_TRANSFER_ORDER
  Cost: 230561
  Rows: 30951280
  Bytes: 10956753120

这说明问题不是“SQL 排版不好看”。主要压力在远程表访问、函数 Join、聚合后的中间结果，以及后续 Hash Join。这个时候继续把 SQL 拆得更漂亮，只会让问题变得更隐蔽。

CTE 在这次问题里不是优化器

我以前很容易把 CTE 理解成“先算一小块，再给主查询使用”。这只是人的阅读顺序，不一定是数据库的执行顺序。

在这次查询里，tmp_order 和 tmp_oits 都有聚合。

WITH tmp_oits AS (
  SELECT
    su,
    pn,
    MIN(create_time) AS create_time
  FROM ods_transfer_state
  GROUP BY su, pn
)

执行计划里能看到：

VIEW
  HASH GROUP BY
    TABLE ACCESS FULL ODS_TRANSFER_STATE

也就是说，这个 CTE 不是天然帮我“减少了计算”。它先扫了一遍表，再聚合出一个中间结果，后面继续 Join。如果这个中间结果本身不小，或者 Join 条件选择性不好，CTE 反而会把成本提前固化下来。

这也是我后来对 CTE 更谨慎的原因。它首先是可读性工具，不是性能保证。Oracle 里 CTE 可能被 inline，也可能被 materialize，还可能受 hint、统计信息、版本和查询形态影响。要判断它有没有帮助，只能看实际执行计划和执行统计。

我后来会先确认这几件事

这类问题不能上来就加索引，也不能上来就重写 SQL。我的处理顺序后来改成了下面这样。

第一步，保留原始 SQL、参数和执行计划。尤其要保留执行计划里真正影响判断的节点。

EXPLAIN PLAN FOR
SELECT ...

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

如果能在测试环境拿到实际执行统计，会比只看预估计划更有价值。这里不能只执行 EXPLAIN PLAN，因为它只给预估计划。要看 A-Rows、Buffers 这类信息，需要执行过 SQL，并且打开对应统计。

SELECT /*+ GATHER_PLAN_STATISTICS */
  ...
FROM ...

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

我会重点看这些东西：

E-Rows 和 A-Rows 差距有多大。
Buffers 和 Reads 主要耗在哪个节点。
Hash Join、Sort、Group By 是否用了大量 Temp。
远程表返回了多少行。
过滤条件是在远程侧生效，还是拉回本地后才生效。

第二步，单独确认远程表能不能先缩小范围。

原查询里远程表既参与了 tmp_order，又在主查询里作为 ddwt 再访问一次。如果时间范围、物料长度、仓储类型这些条件不能尽早作用在远程侧，DB Link 会非常吃亏。

我更倾向先把远程侧需要的字段和过滤条件收敛成一个小集合，再参与本地 Join。这里有一个前提：creation_date 如果是字符型日期，必须保证它一直是 YYYYMMDD 这种可按字典序比较的格式。否则还是应该先转成日期类型，或者在数据进入平台时就把时间字段处理好。

WITH ddwt_base AS (
  SELECT
    dest_storage_unit,
    material,
    creation_date,
    creation_time,
    plnt,
    whn,
    storage_type
  FROM remote_wm_transfer_order@remote_link
  WHERE LENGTH(material) = 13
    AND source_storage_type IN ('981', '9IB')
    AND creation_date >= '20241014'
    AND creation_date <= '20241114'
)
SELECT ...
FROM ddwt_base ddwt
...

这里的重点不是这段 SQL 一定最快，而是先验证过滤是否能推到远程侧。如果执行计划仍然显示远程返回大量数据，那说明真正的问题可能不在主查询格式，而在远程访问路径和数据同步策略。

第三步，拆开函数条件看影响。

比如这个 Join：

SUBSTR(ddwt.dest_storage_unit, -10, 10) = SUBSTR(opph.hu_number, -10, 10)

我会先确认它到底放大了多少行，而不是直接创建索引。

SELECT
  COUNT(*) AS join_rows,
  COUNT(DISTINCT SUBSTR(ddwt.dest_storage_unit, -10, 10)) AS ddwt_keys,
  COUNT(DISTINCT SUBSTR(opph.hu_number, -10, 10)) AS opph_keys
FROM ddwt_base ddwt
JOIN ods_pallet_hu opph
  ON SUBSTR(ddwt.dest_storage_unit, -10, 10) = SUBSTR(opph.hu_number, -10, 10);

如果这里已经明显放大，后面再优化 CASE WHEN 意义就不大。更合理的方向可能是把“后 10 位匹配”变成模型的一部分。可以是实体列，也可以是虚拟列，取决于表的写入压力和发布窗口。下面两个方案是二选一，不是都执行。

ALTER TABLE ods_pallet_hu ADD hu_number_suffix VARCHAR2(10);

UPDATE ods_pallet_hu
SET hu_number_suffix = SUBSTR(hu_number, -10, 10)
WHERE hu_number IS NOT NULL;

CREATE INDEX idx_pallet_hu_suffix
ON ods_pallet_hu(hu_number_suffix);

如果不想回填实体列，也可以考虑 Oracle 虚拟列，再给虚拟列建索引。

ALTER TABLE ods_pallet_hu ADD (
  hu_number_suffix GENERATED ALWAYS AS (SUBSTR(hu_number, -10, 10)) VIRTUAL
);

CREATE INDEX idx_pallet_hu_suffix
ON ods_pallet_hu(hu_number_suffix);

生产环境里当然不能随便 ALTER TABLE，这需要评估写入影响、历史数据回填、发布窗口和回滚方案。这里放出来只是说明方向：如果业务长期依赖“后 10 位匹配”，那它最好变成数据模型的一部分，而不是每次查询时临时算。

第四步，处理时间过滤。

原来的写法是：

TO_CHAR(opph.rm_tag, 'yyyymmddhh24miss') > '20241014000000'
AND TO_CHAR(opph.rm_tag, 'yyyymmddhh24miss') <= '20241114000000'

如果 rm_tag 本身是日期类型，更稳的写法应该保留日期字段本身，不要对列做 TO_CHAR。

opph.rm_tag >  TO_DATE('2024-10-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND opph.rm_tag <= TO_DATE('2024-11-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

这种改动看起来不花哨，但比“重写一整段 SQL”更可能有效。因为它让数据库有机会使用 rm_tag 上的普通索引，也让优化器更容易估算过滤选择性。

索引不是第一反应，证据才是

当时也有人会自然想到加索引。比如：

CREATE INDEX idx_pallet_hu_rm_tag
ON ods_pallet_hu(rm_tag);

CREATE INDEX idx_transfer_state_su_pn
ON ods_transfer_state(su, pn);

这些方向都可能有价值，但不能跳过验证。因为索引会影响写入和批处理，远程表上的索引也不是我想建就能建。更麻烦的是，如果查询里一直写：

TO_CHAR(rm_tag, 'yyyymmddhh24miss')

那普通的 rm_tag 索引可能还是用不上。你建了索引，却没有改变查询使用字段的方式，最后只是多维护了一个对象。

函数索引也不是不能考虑。

CREATE INDEX idx_pallet_hu_rm_tag_char
ON ods_pallet_hu(TO_CHAR(rm_tag, 'yyyymmddhh24miss'));

但这类索引我会更谨慎。它绑定了当前查询写法，也会增加维护成本。如果业务上本来就应该按时间范围查，优先把条件改回日期范围，一般比给错误写法补索引更干净。

这次问题真正改变的是我的排查顺序

这次之后，我处理慢 SQL 不再先问“怎么改写”，而是先问“证据在哪里”。

我会按这个顺序来：

先复现慢的页面和参数。
保留原 SQL，不急着格式化。
拿执行计划，必要时拿实际执行统计。
看远程表、本地大表、聚合和 Join 哪个节点最重。
确认过滤条件有没有在大数据量 Join 前生效。
检查函数和隐式转换有没有破坏索引使用。
一次只改一个变量。
每次改完都对比执行计划，而不是只看 SQL 是否更顺眼。

这套方法不复杂，但它能避免一种很常见的无效劳动：为了证明自己在优化，不断叠加新的改动。加一个 CTE，再加一个索引，再改一个 Join，再加一个 hint。最后就算快了，也很难说清楚到底是哪一步起了作用。如果更慢，就更难回滚。

这篇复盘里我没有写“优化前多少秒、优化后多少秒”。这组数字很重要，但在我的旧记录里我没有完整的记录下来。现在回头看，这是当时复盘材料的缺口：我保留了 SQL、报错和执行计划，却没有把每一次改动后的耗时对比整理成表。这个缺口本身也算一个教训。性能问题不能靠印象复盘，数字没留下来，后面就只能讲判断过程，不能硬补结果。