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

从执行计划看 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通常值得尝试。下面这张表总结了常见场景。

临时表空间与参数调优

强制物化会把结果写入临时表。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中确认重复扫描，再用物化对比执行时间，才能找到适合当前数据规模的策略。