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查询，必须是明确的数值！）

 

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

　　注：这个优化后的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条后，就不会再多扫描，大大减少了扫描的数据量，自然也就提升了效率。

 

参考：https://www.cnblogs.com/tianzong/p/10552182.html