SQLite查询优化：相等条件连接慢于不等条件的原因及优化方案

两张各含约150万条记录的表master和slave，file和size字段均已创建独立索引。以下查询执行速度较快：

SELECT count(*) FROM master m INNER JOIN slave s ON m.file = s.file WHERE m.size <> s.size;

但下面的查询却无法完成（等待30分钟后被迫终止）：

SELECT count(*) FROM master m INNER JOIN slave s ON m.file = s.file WHERE m.size = s.size;

使用explain query plan未得到有效帮助，请问该现象的原因是什么？如何优化？

• 结果集量级差异：size <> s.size的匹配结果可能远小于size = s.size的结果。当通过file关联后，大部分匹配记录的size都是相等的，导致后者需要处理的中间结果集规模极大，内存、磁盘IO资源被耗尽，拖慢执行速度。
• 索引组合效率不足：单独的file和size索引无法让数据库同时完成关联与条件筛选。对于size = s.size的查询，数据库可能先通过file关联生成大量匹配对，再逐一比对size，而非直接利用联合索引筛选，产生不必要的计算开销。
• 统计信息过时：数据库的表统计信息未及时更新，导致查询优化器做出错误的执行计划选择——比如选择了适合小数据集的嵌套循环连接，而非更适合大数据量的哈希连接，或错误预估结果集大小，分配的资源不足以支撑查询。

• 创建联合索引：为两张表分别创建(file, size)联合索引，让数据库可以直接通过联合索引同时完成关联与条件筛选，避免生成大量无效中间结果。
• 重构查询逻辑：利用已有快速查询的结果推导目标值，比如先计算file关联的总匹配数，再减去size <> s.size的计数：

  SELECT (SELECT count(*) FROM master m INNER JOIN slave s ON m.file = s.file) - 
         (SELECT count(*) FROM master m INNER JOIN slave s ON m.file = s.file WHERE m.size <> s.size)
  

• 更新统计信息：执行数据库的统计信息更新命令（如MySQL的ANALYZE TABLE master, slave;，PostgreSQL的ANALYZE master, slave;），让优化器基于准确数据生成更优执行计划。
• 强制调整连接类型：临时切换到更适合大数据量的哈希连接（如MySQL用STRAIGHT_JOIN配合优化器提示，PostgreSQL执行SET enable_nestloop = off;），避免嵌套循环连接的低效遍历。
• 分批次统计：如果必须执行原查询，可按file的范围分段查询，累加各段结果，避免一次性处理全量数据导致资源过载。

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