关于WITH子句、联表查询场景下索引扫描触发情况的技术咨询

WITH子句选取的索引列，在主查询中能否触发索引扫描 #

针对这个场景，没有绝对的“是”或“否”，核心判断依据是数据库查询优化器的处理能力：

• 现在主流的现代数据库（MySQL 8.0+、PostgreSQL 12+、Oracle 11g及以上版本）默认会开启谓词下推优化：你写在主查询里的EMP_ID >= '2000'过滤条件，会被优化器直接挪到CTE对应的基表查询环节，根本不会等CTE把全量EMP_ID列捞出来拼成临时结果集，再去外层做过滤。这种逻辑下，优化器会直接走EMP_ID上的主键索引扫描；因为整个查询只用到了EMP_ID列，甚至会直接走覆盖索引扫描，连回表读数据块的步骤都省了。
• 如果你用的是对CTE优化能力较弱的老版本数据库（比如PostgreSQL 11及更早版本，默认对CTE做强物化——也就是必须先把CTE的查询结果落地成临时表，再执行外层的主查询），那原表的索引就用不上了：因为落地的临时表不会自动给EMP_ID建索引，外层的过滤条件只能对临时表的全量数据做顺序扫描。
• 就你给出的示例来说，哪怕不考虑谓词下推的优化逻辑，因为CTE查询的列本身就是索引覆盖列，只要数据量达到阈值，优化器依然会优先选索引扫描，不会盲目走全表顺序扫。

双表带索引列做关联+独立过滤时，联表前是否会各自触发索引扫描 #

针对这个双表关联的场景，正常数据分布下两张表的索引都会被用到，但触发扫描的顺序不是固定的“先各自扫索引再联表”：

• 你写的这种用逗号分隔表名、把关联条件写在WHERE子句里的写法，和标准的INNER JOIN A ON A.COL1 = B.COL1语法的执行逻辑完全等价，优化器对两种写法的处理没有任何区别。
• 正常优化流程中，优化器会先基于两张表的独立过滤条件（A.COL1 > 200、B.COL1 > 100），分别走各自COL1上的索引做单表初筛——因为初筛只需要用到COL1列，甚至可以不回表读整行数据，直接从索引结构上拿到满足条件的行，再把两个初筛结果集按等值条件A.COL1 = B.COL1做匹配关联。整个流程的成本，比先把两张表全量扫描出来再做关联要低得多。
• 有两类特殊情况不会触发索引扫描：
  • 单表过滤条件能命中的数据占全表比例过高（一般超过20%-30%，不同数据库的成本阈值有差异），优化器会判断顺序扫全表的成本比走索引+回表更低，直接放弃索引。
  • 表的统计信息长期没更新，优化器对数据分布、总行数的估算出现严重偏差，错误选择了更高成本的执行路径。

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