关于数据库:SQL优化in

原始SQL（执行工夫80s+）

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

优化后SQL（均匀执行工夫100ms）

select sum(id) as id  from (SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112310  AND  flag=9union allSELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112316  AND  flag=9union allSELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=106959  AND  flag=9union allSELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=110780  AND  flag=9union allSELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112324  AND  flag=9union allSELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112331  AND  flag=9union allSELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112317  AND  flag=9) t

阐明：
mysql版本5.6
表总的数据量60万+
cid和flag为int类型，参数去掉引号，防止隐式转换
(cid sid flag)是组合索引，应用uinon all拆分后，遍历的表多了，应用组全但更高效