性能优化从删除子查询做起

以前的sql是用mybatis生成的，为了适应mybatis的模板，生成的sql比较奇怪，如下所示：

select a,b,c…

from (select a,b,c from X where …)

       join Y on ….

      join Z on

where ….

order by …

limit 0,1000

这个sql的执行时间很长，需要用到8秒。执行计划如下：

简要说明下select-type表示选择的类型，primary表示它的内部包含了子查询，或者union之类的。如果没有这些，就是simple。

table表示该步骤从哪里获取数据。

type表示mysql如何获取数据，all表示全表扫描，index表示以索引的顺序扫描，如果extra里面也是using index则表示使用了covering index，所需数据全部来自于索引。range比index更好，只需要扫描一个index的区间；ref表示索引值和常数进行比较；eq-ref返回一个值；const，system会对查询的部分进行优化，将其变成一个常数。

possible keys表示在早期mysql可选的index，可能在后期不会用到。key表示mysql最终决定使用的key。key-len当然表示key的长度。ref表示key所比较的对象。

rows表示获得需要的结果需要扫描的行数。

最后是extra，包含了很多其他的信息，常见的比如“using index”表示数据从index中获得；using where表示在引擎获得结果后进行过滤；tempoarary临时表，filesort文件排序而不是索引排序。

那么上述的第一个步骤表示它需要从一个子查询或者union的结果中来，做了一个全表扫描，没有用到key，共扫描了34722行，结果放到临时表中，没有用到index进行排序。最后一个步骤，实际上是先知行的，全表扫描18万行。

优化后的sql结构如下：

select * from X JOIN Y JOIN Z where …

此时的执行计划如下：

主要的区别是上面那个扫描了18万行的全表扫描没有了。而且步骤1的34k也变成了17k。最重要的是第四行，type为ref，key是idx-rule-id，和taskrule。id进行比较。这说明了一点，X join Y用的是索引。这就是两个执行计划的关键区别。

第一个sql由于用了子查询，join的时候没有索引，耗时8秒。第二个sql没有用子查询，join的时候用了索引，耗时0.4秒。可以子查询一定要慎用。join的时候一定要索引。