SQLite 3.40的MATERIALIZED CTE是如何工作的?

来源:网络推广作者:木下头衔:网络博主
导读:本期聚焦于木下创作的《SQLite 3.40的MATERIALIZED CTE是如何工作的?》,敬请观看详情。同一个CTE在SQLite查询里被引用多次时,优化器通常会尝试把它内联展开,这有时会重复计算并拖慢复杂报表;如果CTE本身包含聚合、递归或大范围扫描,重复执行的开销会更加明显。SQLite 3.40为WITH子句增加了MATERIALIZED提示,强制将CTE结果写入临时表,后续引用直接读取缓存,而不是再次执行子查询。本文从执行计划角度解释物化与内联的差异,结合递归CTE、多引用场景和EXPLAIN QUERY PLAN输出,说明何时使用MATERIALIZED能降低重复扫描,以及为什么过度物化反而会丢失索引过滤能力。还会演示NOT MATERIALIZED的对照效果,帮助读者在报表SQL和复杂关联中合理控制CTE执行策略。需要注意,物化会占用临时空间,结果集过大时可能写入磁盘,因此该提示更适合中等规模结果和高复用查询。

SQLite 3.40在WITH子句中加入了一个容易被忽略但很实用的关键字MATERIALIZED。它的作用不是改变SQL语义,而是向查询优化器给出明确指令:把这个公共表表达式的结果先计算出来并保存到临时表中,后续引用都从临时表读取。以前能否缓存由优化器自行判断,开发者在复杂报表里很难稳定控制重复计算。理解这一提示需要先看默认CTE的执行方式:非递归CTE通常会被展开成子查询,当同一个CTE在查询中被引用多次时,优化器可能每次引用都重新执行底层查询。如果底层查询包含聚合、排序或大范围扫描,重复执行就会造成无谓消耗。MATERIALIZED正是为这种复用场景提供确定性的缓存策略。

SQLite 3.40的MATERIALIZED CTE是如何工作的?

从执行计划看 MATERIALIZED 与内联的差异

先建立一个简单的订单表,并准备一个按客户聚合的CTE。假设订单表orders有百万行,聚合逻辑需要扫描整表。若查询中需要同时使用每个客户的订单数、总金额以及这两个指标进一步计算,CTE被引用多次时,普通写法可能会让聚合执行多次。

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount REAL NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

WITH order_stats AS (
  SELECT customer_id,
         COUNT(*) AS cnt,
         SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT c.name,
       os.cnt,
       os.total,
       os2.customer_id AS filter_id
FROM customers c
JOIN order_stats os ON os.customer_id = c.id
JOIN order_stats os2 ON os2.customer_id = c.id
WHERE os2.total > 1000;

上面查询把order_stats连接了两次。默认情况下SQLite可能将CTE展开,导致两次引用分别执行一遍聚合。虽然优化器有时会识别相同子查询并复用临时结果,但这种行为并不总是稳定。使用MATERIALIZED后,聚合结果会先写入临时表,两次连接都从临时表读取,底层订单大表只需要扫描一次。

WITH order_stats AS MATERIALIZED (
  SELECT customer_id,
         COUNT(*) AS cnt,
         SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT c.name,
       os.cnt,
       os.total,
       os2.customer_id AS filter_id
FROM customers c
JOIN order_stats os ON os.customer_id = c.id
JOIN order_stats os2 ON os2.customer_id = c.id
WHERE os2.total > 1000;

可以通过EXPLAIN QUERY PLAN观察差异。默认写法通常出现两次SCAN orders或两次聚合子查询;物化写法会在计划顶部出现类似CO-ROUTINE order_stats和MATERIALIZE order_stats的步骤,说明结果集被保存成临时表,之后只是扫描临时表。对于百万级订单表,这种差异可能从数秒降到几百毫秒。但要注意,只有CTE被多次引用或计算非常昂贵时,物化才有明显收益。如果只引用一次,临时表读写反而增加一个中间步骤。

递归 CTE 与日期序列的物化场景

递归CTE本身就是一种流式计算,它在运行时会维护一个工作表和结果队列。SQLite对递归CTE的默认处理通常已经包含一定的缓冲,但当递归结果被外层查询多次引用时,重复驱动递归过程仍然可能发生。使用MATERIALIZED可以先把递归结果完整地保存为临时表,让外层查询只面对一个静态表。

下面生成一个从2024-01-01到2024-01-31的日期序列,然后在外层同时统计每一天的订单量,并和前一天对比。日期序列CTE被引用了两次:一次在目标日,一次作为前一天。如果递归每个日期都重复计算,浪费会随着序列长度线性放大。

WITH RECURSIVE dates(d) AS MATERIALIZED (
  SELECT DATE('2024-01-01')
  UNION ALL
  SELECT DATE(d, '+1 day')
  FROM dates
  WHERE d < DATE('2024-01-31')
)
SELECT cur.d,
       (SELECT COUNT(*)
        FROM orders
        WHERE created_at >= cur.d
          AND created_at < DATE(cur.d, '+1 day')) AS daily_orders,
       (SELECT COUNT(*)
        FROM orders
        WHERE created_at >= prev.d
          AND created_at < DATE(prev.d, '+1 day')) AS prev_orders
FROM dates cur
LEFT JOIN dates prev ON prev.d = DATE(cur.d, '-1 day')
ORDER BY cur.d;

这里递归CTE生成31行数据,结果非常小,写入临时表的成本可以忽略。但外层查询多次查找日期时,如果递归被反复计算,就需要反复执行递归循环,每个DATE计算和UNION ALL步骤都会叠加。强制物化后,递归只需要运行一次,31行结果被固定下来,外层两个子查询可以快速扫描临时表。这个场景也说明一个判断标准:当CTE结果行数不大、计算步骤不简单且被多次消费时,物化的收益通常最明显。

需要留意的是,递归CTE默认已经使用内存中的协程来推进,很多人会误以为递归结果天然只会计算一次。实际上,SQLite的WITH RECURSIVE在复杂外层查询中仍可能被重新求值,尤其是外层存在多个引用或子查询时。显式MATERIALIZED消除了这种不确定性,也让查询计划更容易预测。

NOT MATERIALIZED 与条件下推:什么时候不要物化

物化的代价是先把结果集完整保存,再让外层查询过滤。对于结果集很小、计算很重的CTE这很划算,但如果CTE底层是大表,外层又只取其中极小一部分,强制物化会逼迫SQLite先扫描整张底层表,再在临时表上过滤,完全没有利用索引。

以一个带主键索引的订单表为例。下面的查询只取一个订单号,使用MATERIALIZED会让SQLite先把全表数据写进临时表,然后从临时表里查order_id = 20241101。对于千万行订单表,这显然不可接受。

WITH big_orders AS MATERIALIZED (
  SELECT order_id, customer_id, amount
  FROM orders
)
SELECT *
FROM big_orders
WHERE order_id = 20241101;

改用NOT MATERIALIZED,优化器会把CTE当作普通子查询展开,并将外层WHERE条件下推到orders表上。执行计划会从全表扫描变成主键索引查找,只访问一行数据。

WITH big_orders AS NOT MATERIALIZED (
  SELECT order_id, customer_id, amount
  FROM orders
)
SELECT *
FROM big_orders
WHERE order_id = 20241101;

这两种写法的结果完全相同,但性能可能相差数百倍。因此,不要把MATERIALIZED当成本能加在每一个CTE上。它解决的是重复计算,不是条件下推。外部过滤条件的选择性越强,越应该让优化器自由展开;当CTE结果需要被多次使用且过滤后仍然较大时,才考虑显式物化。

可以从一个简单的问题开始判断:这个CTE会被外层查询重复读取吗?如果只出现一次,优先使用默认行为或NOT MATERIALIZED;如果出现多次,再看CTE底层是否包含聚合、排序、递归或大范围扫描。若答案都是“是”,MATERIALIZED通常值得尝试。下面这张表总结了常见场景。

场景建议原因
CTE被多个JOIN或子查询引用,且结果集不大MATERIALIZED一次计算,多次读取,避免重复执行
递归CTE生成少量维度数据后频繁关联MATERIALIZED递归步骤只执行一次,静态化结果
CTE只出现一次且外层过滤条件能走索引NOT MATERIALIZED或默认条件下推优先,避免全量缓存
CTE结果集非常大,内存可能不足谨慎物化临时表可能落盘,产生额外的写入开销

临时表空间与参数调优

强制物化会把结果写入临时表。SQLite的临时表可以保存在内存或磁盘上,具体由PRAGMA temp_store和编译期参数决定。如果结果集只有几万行,内存临时表几乎不构成压力;如果结果集达到千万行,临时表很可能溢出到磁盘,造成大量随机写。此时不仅没有优化,反而会让查询变慢。

PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -65536;

WITH recent_orders AS MATERIALIZED (
  SELECT customer_id, amount
  FROM orders
  WHERE created_at >= '2024-01-01'
)
SELECT customer_id, SUM(amount)
FROM recent_orders
GROUP BY customer_id;

上面的PRAGMA temp_store = MEMORY要求临时表优先使用内存。对于中小型结果集,这能提高读写速度;但如果给一个超大结果集强制指定MEMORY,可能耗尽进程内存。更稳妥的做法是保持默认,让SQLite根据结果大小自动切换。可以在查询前后通过sqlite3_status或文件系统中的临时文件变化来观察物化是否落盘。通常,如果临时文件增长明显,说明这个CTE不适合物化,或者需要先增加过滤条件缩小结果。

另外,物化后的临时表不会继承底层表的索引。这意味着外层查询如果在临时表上做复杂过滤或连接,必须依赖自动创建的临时索引,或者接受全表扫描。如果外层还需要对物化结果排序,SQLite会额外排序。因此,在决定物化之前,最好先评估外层查询对临时结果的使用方式。临时表越小,这种副作用越不敏感;临时表越大,越需要谨慎。

SQLite 3.40加入的MATERIALIZED和NOT MATERIALIZED提示,本质上把CTE执行策略从优化器内部决策变成开发者可控。它不改变SQL语义,却可能给复杂报表带来数量级的性能变化。最典型的用法是对被多次引用的聚合CTE或递归维度CTE做物化,把昂贵计算收敛为一次执行。反过来,对单次引用且条件下推友好的CTE使用NOT MATERIALIZED,可以避免全量临时表带来的索引丢失。实际项目中不必每条CTE都加提示,先在EXPLAIN QUERY PLAN中确认重复扫描,再用物化对比执行时间,才能找到适合当前数据规模的策略。

SQLite MATERIALIZED CTE公共表表达式SQL查询优化修改时间:2026-10-23 22:03:37

免责声明:已尽一切努力确保本网站所含信息的准确性。网站作品多为原创整理与精心创作,观点力求客观中立。本站旨在免费分享,内容仅供个人学习、研究或参考使用。若引用了第三方作品,版权归原作者所有。如内容涉及您的权益,请联系我们进行处理Email:chomcom@qq.com。
引用或转载本作品时,请注明当前出处:https://www.ipipp.com/html/1023/70936.html,基于非商业用途的前提下,欢迎转载或二创本作品。
内容垂直聚焦
专注技术核心技术栏目,确保每篇文章深度聚焦于实用技能。从代码技巧到架构设计,为用户提供无干扰的纯技术知识沉淀,精准满足专业提升需求。
知识结构清晰
覆盖从开发到部署的全链路。AI、前端、编程、数据库、服务器、建站、系统层层递进,构建清晰学习路径,帮助用户系统化掌握开发与运维所需的核心技术。
深度技术解析
拒绝泛泛而谈,深入技术细节与实践难点。无论是数据库优化还是服务器配置,均结合真实场景与代码示例进行剖析,致力于提供可直接应用于工作的解决方案。
专业领域覆盖
精准对应开发生命周期。从前端界面到后端编程,从数据库操作到服务器运维,形成完整闭环,一站式满足全栈工程师和运维人员的技术需求。
即学即用高效
内容强调实操性,步骤清晰、代码完整。用户可根据教程直接复现和应用于自身项目,显著缩短从学习到实践的距离,快速解决开发中的具体问题。
持续更新保障
专注既定技术方向进行长期、稳定的内容输出。确保各栏目技术文章持续更新迭代,紧跟主流技术发展趋势,为用户提供经久不衰的学习价值。