mysql常用语句优化

以下都是innodb搜索引擎的前提下

1.limit优化：当一个info表很大的时候，执行select * from info limit 10000,10，这时候mysql的搜索引擎会扫描表的前100010条数据，然后返回10条数据。然而当10000变成十万，一百万页的时候这条sql就会变得特别的慢。那么我们该如何优化这条语句呢？

首先我们要懂得什么叫覆盖索引：当搜索条件，搜索返回字段都存在于同个索引中，我们称它为覆盖索引。当使用explain的时候在Extra中有Using index出现的时候证明其属于覆盖索引。它的主要作用是搜索引擎只需要搜索索引就能返回需要的数据，而不需要再进行回表搜索的操作，这样会使效率高很多。

然后我们把上面的sql分解成一个关联查询：select * from info inner join (select id from info limit 10000,10) as info2 USING(id)

修改前的sql执行时间为1s左右，修改后的sql执行时间为0.04s左右（表大小为500w）。当然如果分页数不大的话不建议这么用，直接limit会比较快。具体需要根据业务分析

2.order by ：首先，order by跟着的列必须要建立索引（因为索引列本身是已经排好序的）。如果order by后面跟着多个列，则需要建立联合索引，且必须遵守最左索引规则。其次，当几张表关联后结果的order by，则order by不可以跟着不同表的列，因为那样会导致索引无法使用。

3.在union all 后使用limit ： 如select * from info union all select * from info limit 10 。这样如果info表中存在100w条数据，那么会把200w条数据存放到临时表后再进行limit取出前10条。那么我们该如何优化此sql呢？我们可以在每个子查询中加入limit，例如：（select * from info limit 10） union all （select * from info limit 10 )  limit 10 。 那有人会问查询第10页的前10条怎么办。此时我们只需要该sql为：（select * from info limit 110 ） union all （select * from info limit 110 )  limit 100,10 这样就能确保搜索出来的数据排序没错，当然当分页数越多也会越慢，可以结合第一条来优化。

4.对于多表的join查询，首先我们应该考虑一下mysql的优化器在优化计划的选择时，它会先把各个表的执行前后进行排列组合，也就是我们关联了5张表，那么优化器需要计算3125次，当关联10张表则会计算一亿次。当然mysql肯定不会去这样做，当计算次数超过设置的最大值时它将抛弃后面的计算，在前面的计算中获取最优的选择。此时优化器生成的最优计划往往不是最优的计划，因此绝大程度的影响到性能。所以当关联表很多的时候就应该考虑拆分sql。把sql拆成一个个小sql再聚起来返回相同的结果。有人说这样会有很多应用跟mysql的会话操作，会有很多网路传输的浪费，但其实拆分成小sql往往性能跟效率都要远高于大sql。因为它少了表锁的争夺，而且mysql在小sql的执行速度上做过优化，速度要远远高于大的sql，同时也少了很多的io操作。所以我们需要根据业务需求考虑是拆分还是不拆分。

5. 索引的创建：严格遵守最左索引规则。在两表关联on的字段上，一般只需要在后面的表的相应字段创建索引。

待续。。。