mysql数据库多表关联查询的慢SQL优化

  工作中我们经常用到多个left join去关联其他表查询结果,但是随着数据量的增加,一个表的数据达到百万级别后,这种普通的left join查询将非常的耗时。

举个例子:

    现在porder表有 1000W数据,其他关联的表数据都很少,因为条件的限制 必须要关联3个表,正常的逻辑就是这样写,但是我们在数据库执行的时候会发现这样的SQL 非常耗时,

  而且此时才 limit 800  这样的SQL怎么能让用户受得了呢?

        select p.*,b.supplier,t.type,c.org   from porder p
        left JOIN brand b on p.supplier = b.supplier_id and b.mark = 0
        left JOIN purchase c on p.org = c.id and c.mark = 0
        left JOIN type t on c.category = t.type_id and t.mark = 0
        WHERE p.nark = 0 ORDER BY p.id desc limit 800,500;

  通过查询SQL优化方面的知识,发现一种比较好的优化方案:

        select p.*,b.supplier,t.type,c.org from
        (select po.id from porder po where po.mark = 0 order by po.id desc limit 800000,500) a
        inner join porder p on a.id = p.id and p.mark = 0
        left JOIN brand b on p.supplier = b.supplier_id and b.mark = 0
        left JOIN purchase c on p.org = c.id and c.mark = 0
        left JOIN type t on c.category = t.type_id and t.mark = 0;

  我们可以先将数据量最大表的满足条件的ID查询出来,创建临时表,再用这个临时表去关联这个表本身以及其他表。limit80W 也就1S时间。

SQL分析:

  我们可以使用 explain 查看上面2种SQL的执行计划。第一种SQL的执行计划中 通过 row 和extra 都可以看出 非常差,row几乎为全部扫描。

  优化后的SQL通过 row 和extra 都可以看出都是很好的状态,row的数据是第一种的 1%。相当于提升了 100倍。

  执行计划中的id列的数值越大,执行权就越高。id列的值相等的,就从上之下依次执行。明白了这一点,我们就可以再分析SQL了。

  数据库先执行了 select po.id from porder po where po.mark = 0 order by po.id desc limit 800000,500 这段SQL,将查询出的有效id(满足条件的id)放在了临时表a中,

  然后表a 再与其他的表匹配查询。

  (注:优先执行的SQL 不参与 后面的表匹配。这里要理解,不然单独看执行计划,你会纳闷为何row列上 a表 中数值小,而 id列为2的表(po) row列的数值也很大。

  你也可以拆分SQL。优先执行的SQL 单独拿出来执行,将查询到的结果当作查询条件,传给普通的 left join 中的where条件里面 即 in(), in的里面不要写SQL查询,必须是明确的数值!)

我只是提供方法,具体的原理,大家可以上网查一查。数据库有一种叫 驱动表的概念,大家可以了解下。或许对于理解这种方法更方便!

  注:这个优化后的SQL在执行 limit1000000,**  的时候效率也就下降了,大概4S钟以上。所以这个SQL也是有极限的,对于分页查询等等,如果数据量超过100W 要注意!

希望有大神,能在SQL上能有更高的突破,有方法的,希望大家一起分享,一起学习。谢谢~

  补:为了应对超过百万级别的查询,或者导出,SQL优化暂时没有好的办法,但是我们可以在传参上做文章。

比如分页查询时,每页展示20条数据,首页查询时,我们可以得到首页最后一条数据的ID (起名:lastId)(按ID排序,降序),当点击第2页时,我们可以将 lastId 作为参数传入分页查询的SQL中。

这样分页时就加上了一个条件 就是 ID<lastId (按ID排序,降序),limit也可以优化成   limit 20, 这样优化后,因为limit 不再是 limit xxx,20 ,这样数据库在扫描满足条件的数据时,就会从此ID往后扫描,

且扫描到满足条件的20条后,就不会再多扫描,大大减少了扫描的数据量,自然也就提升了效率。

 

  

 

posted @ 2019-03-18 14:55  天纵%  阅读(36030)  评论(4编辑  收藏  举报