背景介绍
我们经常在写多表关联的 SQL 时, 会想到 left jion(左关联),right jion(右关联),inner jion(内关联) 等.
但是, 当表中数据量过大时, 如果没有写好查询条件或者查询条件书写的先后顺序不同, 可能会有明显的性能差别.
近期, 有个同事遇到一个 SQL 查询比较慢的问题: tableA,tableB,tableC 三张表联合查询的 SQL, 查询用时将近 50s.
原因分析
1, 分别确认 3 张表的数据量
tableA:3 千万 + 条记录;
tableB:5 千 + 条记录;
tableC:7 千 + 条记录;
2, 确认 SQL 查询逻辑
SQL 如下 (查询 tableA 中 name 包含 miracle,tableB 中 age 大于 20 岁, tableC 性别为 male 的联合信息):
- select *
- from
- tableA a, tableB b, tableC c
- where a.id = c.id
- and b.uuid = c.uuid
- and a.name like '%miracle%'
- and b.age> 20
- and c.sex = 'male'
上述 SQL 的效果和内关联等价, 根据 SQL 的关联逻辑我们可以知道, 表之间关联查询, 其实就是集合之间先做 "笛卡尔积", 根据查询条件对这个笛卡尔积结果集再次做过滤.
可以看到, 此时笛卡尔积的集合容量为:(3 千万 +)*(5 千 +)*(7 千 +), 是一个百万亿级的庞大数据集合.
因此从这个庞大集合中, 再按照过滤条件查询想要的数据, 当然会慢很多.
调优方案
1, 单表预处理
tableA 预处理 (处理后, tableA 中 "有效" 数据量级降到 1 千 +):
select * from tableA where name like '%miracle%'
tableB 预处理 (处理后, tableB 中 "有效" 数据量级降到 2 千 +):
select * from tableB where age> 20
tableC 预处理 (处理后, tableC 中 "有效" 数据量级降到 3 千 +):
select * from tableC where sex = 'male'
此时三张表 "笛卡尔" 的数据量级为:(1 千 +)*(2 千 +)*(3 千 +), 约为 十亿级的数据集合. 相比之前, 量级已经降低了十万倍.
2, 调整查询 SQL 结构
- select *
- from
- (select * from tableA where name like '%miracle%') a,
- (select * from tableB where age> 20) b,
- (select * from tableC where sex = 'male') c
- where a.id = c.id
- and b.uuid = c.uuid
此时, SQL 的查询时间为 0.14s, 相比之前的 50s, 查询速度已经提高了几百倍.
3, 表关联方式转换 (二次优化)
做了上面的操作, 查询速度得到了明显提高.
如果我们想让查询效率更上一层楼, 可以对关联方式做下调整.
由于是三张表关联, 左关联和内关联在性能上还是有很大差距的.
此时三表的左关联比内关联查询性能上是提高的, SQL 调整如下:
- select *
- from
- (select * from tableA where name like '%miracle%') a
- left jion
- (select * from tableC where sex = 'male') c on a.id = c.id
- left jion (select * from tableB where age> 20) b on b.uuid = c.uuid
此时 tableA 和 tableC 左关联的笛卡尔积集合容量为 (1 千 +)*(2 千 +), 是一个 百万级的数据集合, 经过 a.id = c.id 过滤后得到是一个 1 千 + 的数据集合
将 tableA 和 tableC 左关联后的结果集和 tableB 进行左关联, 其笛卡尔积集合容量为 为 (1 千 +)*(3 千 +), 也是一个 百万级的数据集合.
相比于步骤 2 的 十亿量级, 又降低了 1000 倍. 最终, 上述 SQL 执行用时不到 0.1s
优化总结
对于数据表的数据量比较大的多表联合查询的场景.
SQL 优化原则如下:
1, 预处理单表数据, 获取每张表的 "有效" 数据, 达到首次 "降级" 的目的;
2, 调整关联关系, 实现二次 "降级".
- (说明: 本文说的 "降级", 指的是降低 SQL 执行的数量级)
- PS:
希望能帮到大家, 谢谢!
来源: http://www.bubuko.com/infodetail-3327491.html