从单索引到复合索引SQL优化

     从工作开始，就一直在做SQL优化的知识储备。然而一直没有多少机会让我运用到这些知识。很幸运，这次重构老项目的终于能练把手。

    这次优化对象是一个180W的表，数据说多也不多，但由于做了很多冗余，里面有30多个字段，查询下来速度也够慢的。接下来就从简单到复杂sql一条一条分析。

   1、select * from table where orgId=? and isdelete=0 order by operTime desc limit 10

        这是一条极简单的sql，orgId基数为200多，isdelete基数为2，只有0，1 两个数字，由于isdelete基数很小，按道理直接在orgid字段和operTime 建立索引即可，但效果不太理想。随后将isdelete也尝试建立了索引，发现性能大大提升。这现象有点出乎我意料。回头一想，表中isdelete有90%以上都为1，只有不到10%的数据是0，如果经常查找为0的数据，对此字段建立索引效果就很明显了。

       另外，由于表中字段数较多，则尽量只返回只需要的字段，改造成select orgId,isdelete,operTime  from table ......实测，对返回字段越少（即数据越少），则查询速度确实能快一点点（不多）。

  2、select * from table where orgId='1' and isdelete=1 order by operTime desc limit 10

         一次意外发现，跟上一条“差不多”的sql，程序执行这条却特别慢，导入到navicate执行，效果也一样。我寻思着嗯 ？怎么这么神奇。也很快发现，orgid是int型，但传的却是一个字符型，因为没用上索引，将传参是的''去掉即可。

3、select orgId,orgName,userId,userName,batchId ,operTime  from table where orgId=? and isdelete=1 group by batchId order by operTime desc limit 10

        这条就有点意思了。我一开始给orgid isdelete  batchId operTime 都单独加了索引，batchId 的基数是18W，然而这样一条sql跑下来要整整4s。在生产环境中这样的速度当然是没办法忍受的。我反复在网上查找资料，一开始得出的结论是，基数太大，group by 、排序需要的时间本来就要很长，目前索引都加上了，效果也不会理想。所以之后一直考虑着分表的事情。但回头想想，才不到200W的数据就开始分表了，以后岂不是更麻烦，又放弃了分表的念头。部门里的一个大神听我讲了一下需求，他给我的建议是如果group by 的数据量大了，你可以考虑单独为这个查询建立一个表，然后直接读他就行了，不用group by了，这样效率就很快，但是你变更数据的同时，也要去更新这个表，编码方面会比较麻烦。而这也不失为一个候选方案。

       随后，我想着减少筛选的数量再进行group by ，于是将数据缩减至1000，放入虚拟表中再进行group by：

        select * from (select orgId,orgName,userId,userName,batchId,operTime  from table where orgId=1 and isdelete=1 and type=1  order  by operTime desc limit 1000 ) A group by batchId order by operTime desc

      这下好了点，从4s变成了2s，可这样仍不能满足性能要求。当前索引为：复合索引orgId、type、isdelete，单索引batchId 和operTime。

      接下来，我对索引重新进行改造。建立复合索引orgId,type,isdelete,operTime，由于batchId也会经常作为单独搜索字段，所以batchId 单索引保留，OK，完美解决性能问题。

      可见，对某些速度较慢，查询条件较多也相对固定的sql建立复合索引，对性能优化能起到非常大的作用。