使用WITH子句(CTE)与IN运算符的性能考量及优化疑问

背景与不同写法 #

在使用WITH子句（CTE）和IN运算符时，MySQL和SQLite的语法存在差异，我整理了专属写法和跨库通用写法：

MySQL专属写法 #

WITH cte AS (SELECT column1, column2 FROM Table1 WHERE ...)
SELECT * FROM Table2 WHERE ... AND ((column1, column2) IN (TABLE cte))
ORDER BY column3

SQLite专属写法 #

WITH cte AS (SELECT column1, column2 FROM Table1 WHERE ...)
SELECT * FROM Table2 WHERE ... AND ((column1, column2) IN cte)
ORDER BY column3

跨数据库通用写法 #

WITH cte AS (SELECT column1, column2 FROM Table1 WHERE ...)
SELECT * FROM Table2 WHERE ... AND ((column1, column2) IN (SELECT * FROM cte))
ORDER BY column3

疑问解答 #

Q1：当Table2数据量很大时，使用IN (SELECT * FROM cte)是否会带来较大执行负担？ #

执行负担的大小取决于几个核心因素：

• CTE结果集规模：如果CTE返回的行数很多，IN子查询需要比对大量数据，执行时间会明显拉长；若CTE结果集很小，基本不会有额外负担。
• Table2的索引配置：如果Table2的(column1, column2)组合有联合索引，数据库能快速定位匹配行，大幅降低扫描成本；没有索引的话，数据库会全表扫描Table2，每一行都要和CTE结果集比对，数据量大时性能会急剧下降。
• 多列匹配的复杂度：多列IN的比对逻辑本身比单列复杂，数据量越大，这种复杂度带来的开销越突出。

总的来说，在数据量极大且缺少合适索引的情况下，确实会产生较大执行负担，但通过添加联合索引、控制CTE结果集大小等优化手段，能把负担降到可接受范围。

Q2：MySQL和SQLite对此种写法是否有相关优化措施？ #

MySQL的优化措施

1. CTE物化与重用：MySQL 8.0+会根据CTE的使用场景选择是否物化（将CTE结果暂存到临时表），若CTE被多次引用，物化能避免重复计算；对于单次使用的CTE，优化器可能会直接将其逻辑展开到主查询中，合并执行以减少临时表开销。
2. 子查询转JOIN：MySQL优化器会尝试把IN (SELECT * FROM cte)转换为JOIN操作，尤其是当CTE结果集有合适索引时，转换后的JOIN能利用索引快速匹配，效率更高。
3. 联合索引支持：只要Table2存在(column1, column2)联合索引，MySQL会直接用索引查找匹配行，跳过全表扫描。

SQLite的优化措施

1. CTE逻辑展开：SQLite默认会将CTE的逻辑直接展开到主查询中，避免创建临时表的开销；只有当CTE被多次引用时，才会考虑将其物化。
2. 子查询转换：SQLite优化器会尝试把IN子查询转换为EXISTS或JOIN形式，在多列匹配场景下，转换后的执行计划通常更高效。
3. 索引利用：如果Table2的(column1, column2)有联合索引，SQLite会优先使用索引进行查找，减少全表扫描的次数。

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