MySQL⼤表关联如何优化_MySQL中10多张表关联要做优
googlemap离线地图下载化,怎么理解逻辑幂等
最近优化了⼀条MySQL的慢查询SQL,还是蛮有感触,⼩结⼀下。
⾸先问题的背景是⼀个业务做压⼒测试,排除了很多的前期问题,使⽤的最有效⼿段就是索引,在最后⼀个环节,问题开始陷⼊焦灼状态,因为这⼀条SQL的相关表有16张,⽽且是在业务环节中频繁调⽤和引⽤的逻辑。
⼀般碰到问题都会有⼀个疑问,说这是谁写的SQL,应该快速重构,但是⼤部分优化场景都是:优化可以做,但业务不能停。 所以重构需要,但是不是现在。
在⼀种很复杂的⼼情下开始了优化,当然在查看了执⾏计划后让我除了绝望还有⼀种惊喜。那就是⾥⾯有⼀个明显全表扫描的逻辑,也就意味着尽管这么多表关联,但是数据量也可以接受,在优化器解析时⼤部分逻辑是⾛了索引,优化好最后⼀个全表扫描,整个问题就迎刃⽽解了。
当然我不⽤把整个SQL粘贴处理,全⽂超过5000字符,所以我做了简化,在做了⼀些对⽐测试之后,把问题的逻辑简化为下⾯的SQL形式,也就意味着这个SQL优化成功,则整个优化就意味着成功。
⽬标看起来很简单,但是让⼈开始纠结的是⾥⾯的都是left join,怎么破?
SQL语句如下:
SELECT
prod_id, prod_name, tag_url
FROM
product sku
mysql语句多表查询
LEFT JOIN
(SELECT
jt.tag_url, jts.prod_id
FROM
tag jt
java爱心代码编程简单
LEFT JOIN prod_tag jts ON jt.tag_id = jts.tag_id
WHERE
jts.sku_id IN (1 , 2, 3, 4)
AND NOW() >= jt.start_time
AND jt.store_id = 0以下哪一个二维数组定义
d_time >= NOW()) AS tag_new ON sku.sku_id = tag_new.sku_id
第1次逻辑梳理
这条语句的逻辑怎么理解呢,通过执⾏计划看到的tag这张表是⾛了全表扫描。我们⽤下⾯的图来表⽰整个解析过程。
整个SQL的逻辑是输出其中product表的数据(字段prod_id,prod_name)和tag表的数据(tag_url),其中表tag和表tag_product)他们通过字段(tag_id)进⾏关联,然后和外部的表使⽤prod_id进⾏关联,为了体现出是left join(左连接),我把表product的位置及往上放了放。
整个逻辑其实从上⾯的图看起来还是有点别扭,tag_product的数据还得反向和外部的表进⾏关联。
第2次逻辑梳理
所以对于上⾯的逻辑,其实数据表product和表tag要联合输出数据,需要借助⼀个中间表tag_product,那么tag_product应该是连接数据的纽带,⼀个相对⽐较合理的⽅式就是其实基于表product,tag_product和tag这样的顺序来进⾏过滤。
所以我补充了如下的图来说明这个逻辑。
从通常的设计来说,这样是最合理的⽅式,可以使得逻辑关系更加清晰。
看起来这应该是⽐较合理的⽅式了。
SELECT prod_id,prod_name,tag_url
FROM product sku
LEFT JOIN tag_product jts on jts.sku_id =sku.sku_id
left join (select tag_url,tag_id
from tag jt where jt.start_time <= NOW()strncpy安全吗
AND jt.store_id = 0
d_time >= NOW()) tag_new
on tag_new.tag_id=jts.tag_id
在经过测试之后,感觉已经很接近问题的真相了。
但是在进⼀步和业务沟通,了解了业务的实现细节,发现整个逻辑似乎和我们理解的不⼤⼀样。
⽐如tag表的数据
tag_id:1,tag_status:ACTIVE,
tag_id:2,tag_status:INACTIVE
tag_product的数据
tag_id:1,prod_id:100
tag_id:1,prod_id:200
tag_id:2,prod_id:100
按照业务逻辑,如果tag表中的做过滤后的数据为
tag_id:1,tag_status:ACTIVE,
则根据SQL的逻辑,left join会和表tag_product再做⼀次连接,数据以tag表中的tag_id为准,输出就是:
tag_id:1,prod_id:100
tag_id:1,prod_id:200
⽽如果采⽤上述的连接⽅式,其实就会出现意料之外的数据。
⽐如,按照tag_product进⾏过滤
tag_id:1,prod_id:100
tag_id:1,prod_id:200
tag_id:2,prod_id:100
然后和tag做关联,tag输出数据为:
tag_id:1,tag_status:ACTIVE,
tag_id:2,tag_status:INACTIVE
使用spring可以实现声明式事务吗这样⼀来就失去了过滤的意义。
第3次逻辑梳理
当然沟通的过程中,也进⼀步理解了需求,其实我们所谓的逻辑幂等,不是真正意义上的业务逻辑幂
等。从业务逻辑幂等上,是按照表tag 的输出为标准。所以整个tag和tag_product的关联可以降维为普通的表关联,⽽⾮left join.
整个改进的逻辑如下图所⽰:
在业务层明确之后,⽽且输出结果和预期⼀致的情况下,整个改动的部分就是删除了left join中的left,整个 SQL的执⾏效率⼜变得更加流畅。
⼩结
对于多表关联,在业务层是强烈建议做重构的,⽽在优化中,如何尽可能减少改动幅度,同时能够提
⾼效率是我们和业务同学需要共同关注的,优化脱离了业务也就脱离了优化的重⼼。

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。