作者:Scarlett_girl | 来源:互联网 | 2023-05-18 04:37
以下都是innodb搜索引擎的前提下1.limit优化:当一个info表很大的时候,执行select*frominfolimit10000,10,这时候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的字段上,一般只需要在后面的表的相应字段创建索引。
待续。。。