SQL优化—in 数据库

原始SQL（执行时间80s+）

SELECT count(id) as id FROM task WHERE cid=100015ANDsid IN(112310,112316,106959,110780,112324,112331,112317)ANDflag='9'

优化后SQL（平均执行时间100ms）

select sum(id) as idfrom ( SELECT count(*) as id FROM task WHERE ANDcid=123456ANDsid=112310ANDflag=9 union all SELECT count(*) as id FROM task WHERE ANDcid=123456ANDsid=112316ANDflag=9 union all SELECT count(*) as id FROM task WHERE ANDcid=123456ANDsid=106959ANDflag=9 union all SELECT count(*) as id FROM task WHERE ANDcid=123456ANDsid=110780ANDflag=9 union all SELECT count(*) as id FROM task WHERE ANDcid=123456ANDsid=112324ANDflag=9 union all SELECT count(*) as id FROM task WHERE ANDcid=123456ANDsid=112331ANDflag=9 union all SELECT count(*) as id FROM task WHERE ANDcid=123456ANDsid=112317ANDflag=9 ) t

【SQL优化—in】说明：
mysql版本5.6
表总的数据量60万+
cid和flag为int类型，参数去掉引号，避免隐式转换
(cid sid flag)是组合索引，使用uinon all拆分后，遍历的表多了，使用组全但更高效