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 （按ID排序，降序），limit也可以优化成   limit 20， 这样优化后，因为limit 不再是 limit xxx,20 ，这样数据库在扫描满足条件的数据时，就会从此ID往后扫描，

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

单表查询优化：

1、下面语句需要10秒

SELECT * FROM library_sample 
WHERE create_date >= "2019-11-01 00:00:00"
    AND 
create_date < "2019-12-01 00:00:00"
    ORDER BY id ASC
LIMIT 1500000, 2000

2、优化后需要1秒，提升10倍

SELECT * FROM 
(SELECT id FROM library_sample
WHERE create_date >= "2019-11-01 00:00:00"
    AND 
create_date < "2019-12-01 00:00:00"
    ORDER BY id ASC
LIMIT 1500000, 2000) o
INNER JOIN library_sample a ON a.id = o.id