工作中我们经常用到多个left join去关联其他表查询结果,但是随着数据量的增加,一个表的数据达到百万级别后,这种普通的left join查询将非常的耗时。
举个例子:
现在porder表有 1000W数据,其他关联的表数据都很少,因为条件的限制 必须要关联3个表,正常的逻辑就是这样写,但是我们在数据库执行的时候会发现这样的SQL 非常耗时,
而且此时才 limit 800 这样的SQL怎么能让用户受得了呢?
select p.*,b.supplier,t.type, from porder p
left JOIN brand b on p.supplier = b.supplier_id and b.mark = 0
left JOIN purchase c on = 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优化方面的知识,发现一种比较好的优化方案:
selectp.*,b.supplier,t.type, from
(selectpo.id fromporder powhere po.mark = 0 order by po.id desc limit 800000,500) a
inner joinporder pon 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 = 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
且扫描到满足条件的20条后,就不会再多扫描,大大减少了扫描的数据量,自然也就提升了效率。
单表查询优化:
1、下面语句需要10秒
SELECT * FROM library_sample
WHERE create_date >= "-11-01 00:00:00"
AND
create_date < "-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 >= "-11-01 00:00:00"
AND
create_date < "-12-01 00:00:00"
ORDER BY id ASC
LIMIT 1500000, 2000) o
INNER JOIN library_sample a ON a.id = o.id
如果觉得《mysql多表关联查询慢_mysql数据库多表关联查询的慢SQL优化》对你有帮助,请点赞、收藏,并留下你的观点哦!