SQLite大表更新：将JOIN查询改写为更快的子查询方案

原查询的核心问题 #

你的LEFT JOIN写法实际等价于INNER JOIN——因为WHERE子句里加了shots表的过滤条件，LEFT JOIN的关联逻辑被抵消，反而会让SQLite做不必要的全表关联计算。另外，900万行的shots表如果没有合适索引，每次关联都会触发全表扫描，这才是查询跑一整天的根本原因，比JOIN/子查询的语法差异影响大得多。

子查询改写方案 #

按照你提到的子查询思路，这里用标量子查询改写，逻辑更清晰，配合索引能大幅提升速度：

简洁版子查询写法 #

UPDATE shots
SET (x, y, z) = (
  SELECT hole_x, hole_y, hole_z 
  FROM holes 
  WHERE tournament = shots.tournament 
    AND course = shots.course 
    AND year = shots.year 
    AND round = shots.round 
    AND hole = shots.hole 
    AND hole_x != '0.0' 
    AND hole_y != '0.0' 
    AND hole_z != '0.0'
)
WHERE end = 'hole' 
  AND x = '0.0' 
  AND y = '0.0' 
  AND z = '0.0';

拆分字段的子查询写法（适合需要单独判断的场景） #

UPDATE shots
SET 
  x = (SELECT hole_x FROM holes 
       WHERE tournament = shots.tournament 
         AND course = shots.course 
         AND year = shots.year 
         AND round = shots.round 
         AND hole = shots.hole 
         AND hole_x != '0.0'),
  y = (SELECT hole_y FROM holes 
       WHERE tournament = shots.tournament 
         AND course = shots.course 
         AND year = shots.year 
         AND round = shots.round 
         AND hole = shots.hole 
         AND hole_y != '0.0'),
  z = (SELECT hole_z FROM holes 
       WHERE tournament = shots.tournament 
         AND course = shots.course 
         AND year = shots.year 
         AND round = shots.round 
         AND hole = shots.hole 
         AND hole_z != '0.0')
WHERE end = 'hole' 
  AND x = '0.0' 
  AND y = '0.0' 
  AND z = '0.0';

必做优化：添加索引 #

不管用JOIN还是子查询，没有索引都是白搭。给两张表创建以下联合索引，直接把关联和过滤的速度拉满：

给holes表创建关联核心索引 #

CREATE INDEX idx_holes_key ON holes(tournament, course, year, round, hole);

给shots表创建更新定位索引 #

CREATE INDEX idx_shots_update ON shots(end, x, y, z, tournament, course, year, round, hole);

额外提速技巧 #

• 如果x/y/z是数值类型（不是字符串），别用'0.0'做比较，直接写= 0，避免类型转换开销。
• 临时关闭SQLite的写同步（有数据丢失风险，操作前务必备份）：PRAGMA synchronous = OFF;
• 分批更新：如果还是慢，按tournament或year拆分更新批次，避免长时间锁表。

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