大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说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条后，就不会再多扫描，大大减少了扫描的数据量，自然也就提升了效率。
单表查询优化：
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
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/9365.html
