MariaDB是否会缓存复用WITH子句中间结果？查询变慢如何优化？

问题根因 #

你遇到的性能问题是MariaDB 10.3版本对CTE（WITH子句）的默认处理逻辑导致的：10.3及更早版本中，非递归CTE默认不会被物化暂存，而是会在每次被引用时都重新执行一次内部的查询逻辑。你修改后的语句共8次引用了tbl_sums，所以整个22000行的基础查询链被重复执行了8次，耗时刚好是原查询的8倍左右，这不是Bug，是该版本的默认优化策略。

解决方案 #

方案1：开启CTE物化优化（优先推荐）

MariaDB 10.3已经支持CTE物化能力，只是默认没有开启：

• 如果你的应用允许在查询前执行单条设置语句，可以先运行：

  SET optimizer_switch = 'cte_materialization=on';
  

  这个参数会强制MariaDB把CTE的结果临时物化存储，后续多次引用时直接读取暂存的8行结果，性能会回到500ms左右的水平。
• 如果不允许执行多条语句，可以直接在查询中加入优化器提示，不需要修改原有逻辑：

  WITH /*+ materialize(tbl_sums) */
  tbl_base AS (
      -- 原有基础查询逻辑不变
      SELECT ……… FROM <many things>
  ),
  tbl_middle AS (
      -- 原有中间查询逻辑不变
      SELECT ……… FROM tbl_middle …
  ),
  tbl_states AS (
      -- 原有中间查询逻辑不变
      SELECT
          ……… AS state,
          ……… AS `elec`
      FROM    tbl_states …
  ),
  tbl_sums AS (
      -- 原有分组查询逻辑不变
      SELECT
          `state`,
          SUM(NOT `elec`) AS `vls`,
          SUM(`elec`) AS `vae`,
          count(`state`) AS `all`
      FROM tbl_states
      GROUP BY `state`
      ORDER BY `state`
  )
  
  -- 你原有UNION查询逻辑完全不变
  SELECT `state`, `vae`, `vls`, `all` FROM tbl_sums
  UNION
  SELECT 'all_sta',SUM(`vls`),SUM(`vae`),SUM(`all`) FROM tbl_sums WHERE `state` IN ('ok','warn','bad','maint')
  UNION
  SELECT 'all_run',SUM(`vls`),SUM(`vae`),SUM(`all`) FROM tbl_sums WHERE `state` IN ('run','longrun')
  UNION
  SELECT 'operative',SUM(`vls`),SUM(`vae`),SUM(`all`) FROM tbl_sums WHERE `state` IN ('ok','warn','run','longrun')
  UNION
  SELECT 'unusable',SUM(`vls`),SUM(`vae`),SUM(`all`) FROM tbl_sums WHERE `state` IN ('bad','maint')
  UNION
  SELECT 'present',SUM(`vls`),SUM(`vae`),SUM(`all`) FROM tbl_sums WHERE `state` IN ('ok','warn','bad','maint','run','longrun')
  UNION
  SELECT 'missing',SUM(`vls`),SUM(`vae`),SUM(`all`) FROM tbl_sums WHERE `state` IN ('removed','lost')
  UNION
  SELECT 'total',SUM(`vls`),SUM(`vae`),SUM(`all`) FROM tbl_sums
  

方案2：改写SQL避免多次引用CTE

如果优化器提示因为环境限制不生效，可以把后续的多次UNION聚合改成单次条件聚合，仅扫描1次tbl_sums即可生成所有汇总行：

-- 前面的tbl_base、tbl_middle、tbl_states、tbl_sums定义完全不变
SELECT `state`, `vae`, `vls`, `all` FROM tbl_sums
UNION ALL
SELECT t.label, SUM(t.vls), SUM(t.vae), SUM(t.all)
FROM (
    -- 把所有汇总维度的映射关系写在CASE语句中
    SELECT m.label, s.vls, s.vae, s.all
    FROM tbl_sums s
    JOIN (
        SELECT 'all_sta' AS label, 'ok,warn,bad,maint' AS states
        UNION ALL SELECT 'all_run', 'run,longrun'
        UNION ALL SELECT 'operative', 'ok,warn,run,longrun'
        UNION ALL SELECT 'unusable', 'bad,maint'
        UNION ALL SELECT 'present', 'ok,warn,bad,maint,run,longrun'
        UNION ALL SELECT 'missing', 'removed,lost'
        UNION ALL SELECT 'total', 'bad,longrun,lost,maint,ok,removed,run,warn'
    ) m ON FIND_IN_SET(s.state, m.states)
) t
GROUP BY t.label

这种写法全程仅引用1次tbl_sums，哪怕不开物化开关，耗时也和原500ms的查询基本一致。

补充说明 #

如果你可以升级MariaDB版本到10.4及以上，CTE物化是默认开启的，不需要任何修改即可达到预期性能。

内容的提问来源于stack exchange，提问作者GingkoFr