MySQL 记一个调优记录：最大化获取 uid 和 mobile

Xin学数据

已于 2023-07-03 00:43:35 修改

阅读量364

点赞数

于 2023-07-03 00:01:52 首次发布

本文链接：https://blog.csdn.net/qq_45476428/article/details/131507240

版权

数据库专栏收录该内容

19 篇文章 1 订阅

订阅专栏

前言

环境：MySQL 5.6、windows 11

前阵子，有一个 BI 看板跑不起来，每次执行跑了很久，还不一定有结果，急需维护迭代。

经过调试，发现看板的SQL 逻辑中有一个开销非常大的逻辑影响了整个 SQL 的逻辑。
抽象出来讲就是，有两个表暂且叫t1和t2吧，两个表都有用户的两个标识，一个是uid，另一个是mobile。t1表中的uid和mobile字段都有可能是空字符串，但至少有一个值；而t2表都有uid，但是mobile字段可能为空字符串。

现在的目的就是将两个表进行关联，最大化的获取到uid和mobile的值，以保证数据的准确性和完整性。

补充说明：t1表的数据量是万级别，mobile='' 大约一两百个；而t2表的数据量是千万级别，70% 的mobile为空。两个表的相关字段都有索引。

原先的逻辑大致如下：

-- sql1：
SELECT IF(t1.uid='',t2.uid,t1.mobile) uid,IF(t1.mobile='',t2.mobile,t1.mobile) mob
FROM t1
LEFT JOIN t2 ON (t1.uid !='' AND t1.uid=t2.uid) or (t1.mobile !='' AND t1.mobile=t2.mobile)
limit 200;

通过LEFT JOIN进行关联，关联条件使用了OR进行连接，每一个独立的条件里分别还有一个AND判断。

执行策略简化之后大致如下（局部调整）：
在这里插入图片描述

执行计划是树状结构，顺序是从下往上看，先是第 8 步，操作策略是TableScan，即全表扫描，数据量是1.0E7。然后第 7 步进行Exchange[REPLICATE]，即数据交换操作，将从上一个步骤的单个执行线程复制到多个执行线程，数据量也是1.0E7。可以看到后面是会有一些发散，由 1.0E7 增加到 1.1E7。而且LEFT JOIN的行数也是大表的数据，而非小表。

由于执行计划是树状结构，仅看到了执行的顺序、操作和数据量，没有像 MySQL 默认的表格形式般展示执行策略。所以更多的是通过执行的时间进一步辅助判断性能。

该逻辑跑了几次没有跑出结果，每次等待了30多秒，最终放弃了。直接改！

调优过程

众所周知，OR是一个相对较慢的查询优化方案，所以为了保证查询效率，首先要把OR优化掉，尝试使用LEFT JOIN来优化。
故分别单独关联一个条件，然后执行查询，观察最终的效果。结果每个任务的执行速度都提升到了一个层次，几百毫秒的功夫便跑完了！
那么，基于以上的观察，一个初步的、可行的方案就浮现出来了，那就是使用LEFT JOIN的方式，去关联两次t2表，一次使用uid来关联，一次使用mobile来关联。

SELECT t1.uid,t2.mobile FROM t1 LEFT JOIN t2 ON t1.uid=t2.uid AND t1.uid<>'' limit 200;
SELECT t2.uid,t1.mobile FROM t1 LEFT JOIN t2 ON t1.mobile=t2.mobile AND t1.mobile<>'' limit 200;

最终的结果如下：

-- sql2：
SELECT IF(t1.uid='',t2_2.mobile,t1.mobile) uid,IF(t1.mobile='',t2.uid,t1.uid) mob
FROM t1
LEFT JOIN t2 ON t1.uid=t2.uid
LEFT JOIN t2 t2_2 ON t1.mobile=t2_2.mobile AND t2.uid IS NULL

简单分析下：

先看关联逻辑，都是使用LEFT JOIN，优先采用关联t1.uid，再关联t1.mobile。
- 第一个LEFT JOIN由于t1.uid90% 不为空，所以直接关联，把分步测试的t1.uid<>''限制条件去掉，减少一次较低效判断的开销。
- 第二个LEFT JOIN同理，把t1.mobile<>''去掉了，但是加了另外一个条件AND t2.uid IS NULL，将匹配的数据量降低到原来的 10%（10% 有mobile无uid）。
关于SELECT有一个容易绕的逻辑，就是通过t1.uid匹配上的则取t2.mobile，通过t1.mobile匹配上的则取t2.uid，如果绕不过来，可以细品一下。