首页
学习
活动
专区
工具
TVP
发布
30 篇文章
1
迁移到MySQL的业务架构演进实战
2
如何优化MySQL千万级大表,我写了6000字的解读
3
MySQL中的SQL优化建议那么多,该如何有的放矢
4
说几点关于数据库的见解
5
迁移至MySQL的数据流转流程优化
6
引入TiDB方案的一些思考
7
MySQL数据克隆的用户权限设计
8
MySQL逻辑数据恢复体系的设计
9
MySQL随机恢复的设计思路
10
从Oracle到MySQL,金融核心场景在线换库落地实战
11
基于Maxwell的MySQL数据传输服务整体设计
12
MySQL数据库升级的一些坑
13
数据架构选型必读:4月数据库产品技术解析
14
基于数据库中间件配置的几类问题
15
关于中间件服务的配置管理,分为5个阶段
16
MySQL中10多张表关联要做优化,怎么理解逻辑幂等
17
关于MySQL拓扑关系的梳理
18
对于新技术栈落地和架构思维的建议
19
MyCAT让人诟病的配置文件,说说破局的思路
20
MySQL多活数据消费服务设计方案
21
数据双向复制中的6个数据冲突场景和解决思路
22
MySQL双主模式下是如何避免数据回环冲突的
23
一个MySQL服务CPU 100%的优化案例反思
24
MySQL表添加了一个字段,竟然导致数据无法写入,反思
25
MySQL周期表管理太繁琐,通过Python自定义工具方法优雅解决
26
MySQL业务双活的初步设计方案
27
数据库修改密码风险高,如何保证业务持续,这几种密码双活方案可以参考
28
一道经典的MySQL面试题,答案出现三次反转
29
​业务双活的数据切换思路设计(下)
30
基于Consul的MySQL高可用服务,健康检查怎么做?这里有一个完整脚本

MySQL中10多张表关联要做优化,怎么理解逻辑幂等

最近优化了一条MySQL的慢查询SQL,还是蛮有感触,小结一下。

首先问题的背景是一个业务做压力测试,排除了很多的前期问题,使用的最有效手段就是索引,在最后一个环节,问题开始陷入焦灼状态,因为这一条SQL的相关表有16张,而且是在业务环节中频繁调用和引用的逻辑。

一般碰到问题都会有一个疑问,说这是谁写的SQL,应该快速重构,但是大部分优化场景都是:优化可以做,但业务不能停。 所以重构需要,但是不是现在。

在一种很复杂的心情下开始了优化,当然在查看了执行计划后让我除了绝望还有一种惊喜。那就是里面有一个明显全表扫描的逻辑,也就意味着尽管这么多表关联,但是数据量也可以接受,在优化器解析时大部分逻辑是走了索引,优化好最后一个全表扫描,整个问题就迎刃而解了。

当然我不用把整个SQL粘贴处理,全文超过5000字符,所以我做了简化,在做了一些对比测试之后,把问题的逻辑简化为下面的SQL形式,也就意味着这个SQL优化成功,则整个优化就意味着成功。

目标看起来很简单,但是让人开始纠结的是里面的都是left join,怎么破?

SQL语句如下:

SELECT 
    prod_id, prod_name, tag_url
FROM
    product sku
        LEFT JOIN
    (SELECT 
        jt.tag_url, jts.prod_id
    FROM
        tag jt
    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
            AND jt.end_time >= NOW()) AS tag_new ON sku.sku_id = tag_new.sku_id

整个SQL的逻辑是输出其中product表的数据(字段prod_id,prod_name)和tag表的数据(tag_url),其中表tag和表tag_product)他们通过字段(tag_id)进行关联,然后和外部的表使用prod_id进行关联,为了体现出是left join(左连接),我把表product的位置及往上放了放。

整个逻辑其实从上面的图看起来还是有点别扭,tag_product的数据还得反向和外部的表进行关联。

所以对于上面的逻辑,其实数据表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()
            AND jt.store_id = 0
            AND jt.end_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

这样一来就失去了过滤的意义。

当然沟通的过程中,也进一步理解了需求,其实我们所谓的逻辑幂等,不是真正意义上的业务逻辑幂等。 从业务逻辑幂等上,是按照表tag的输出为标准。所以整个tag和tag_product的关联可以降维为普通的表关联,而非left join.

整个改进的逻辑如下图所示:

在业务层明确之后,而且输出结果和预期一致的情况下,整个改动的部分就是删除了left join中的left,整个 SQL的执行效率又变得更加流畅。

下一篇
举报
领券