[原] SQL 查询优化,子查询

场景

假设表 table_snapshot 表 为一个电商系统库存的快照表,里面有 goods_id,num,time 等字段。快照数据大约为50W。其中 goods_id 字段,其上未加索弓丨,不同的goods_id有大约3000个。 

要求

指定了 250个goods_id,要求判断其中多少个在 table_snapshot 表中出现过。 

第一版SQL

SELECT DISTINCT goods_id FROM table_snapshot
WHERE goods_id IN (250个指定goods_id) 

执行速度约40秒

第二版SQL

SELECT * FROM ( SELECT DISTINCT goods_id FROM table_snapshot ) AS a 
WHERE a.goods_id IN (250个指定goods_id)

执行速度约0.7秒 

分析原因

1. 因为 goods_id 上未加索弓丨,直接where条件in的话会对50万行原始数据依次比较是否在250个指定 goods_id 中,最大比较次数为1.25亿次

2. 如果先distinct全部,再子查询in,只会在distinct的3000个结果中依次比较250个指定goods_id,最大比较次数为75万次

结论

虽然在大多数情况下,我们都会尽量减少子查询,甚至很多公司要求禁止使用。但是实际上,正确使用子查询,也是一种非常有效的优化方式。

在未加索引的字段上面进行大量in操作,使用子查询先预筛选,可以达到近似索引的效果。 

当然前提是 distinct 预筛选的记录行数不能太多。这个 case 中是 50W 行,并不算多。