为什么不建议使用多表join？

最主要的原因就是join的效率比较低 MySQL是使用了嵌套循环（Nested-Loop Join）的方式来实现关联查询的，就是要通过两层循环，用第一张表做外循环，第二张表做内循环，外循环的每一条记录跟内循环中的记录作比较，符合条件的就输出。 <!--more--> #### 性能问题： - 多表 JOIN 会增加查询的复杂性，可能导致性能下降，特别是在数据量大时。 - 数据库需要在执行查询时处理更多的行和列，这可能导致更高的 I/O 操作和内存使用。 #### 可读性和维护性： - 复杂的 JOIN 查询会使 SQL 语句变得难以理解，导致维护成本增加。 - 当查询需要频繁修改时，复杂的 JOIN 会让代码更容易出错。 #### 索引利用率： - 多表 JOIN 可能会导致数据库无法有效利用索引，影响查询的优化。 - 如果 JOIN 的字段没有适当的索引，查询性能会显著下降。 #### 锁竞争： - 多表 JOIN 可能导致更长时间的行锁或表锁，从而增加锁竞争的可能性，影响并发性能。 #### 数据完整性： - 复杂的 JOIN 查询可能掩盖数据问题或不一致性，使得调试较为困难。 - 难以确保在 JOIN 查询中返回的数据符合业务逻辑和数据完整性要求。 ### 如何优化： - 分解查询：在内存中自己做关联，即先从数据库中把数据查出来之后，再次查询，然后再进行数据封装。 - 考虑数据冗余：在某些情况下，可以考虑数据冗余来减少 JOIN 的需要。 - 宽表：就是基于一定的join关系，把数据库中多张表的数据打平做一张大宽表，可以同步到ES或者干脆直接在数据库中直接查都可以 #### 什么是hash join(扩展阅读) mysql8.0 以前join查询使用Nested-Loop Join算法实现 Nested-Loop Join：嵌套循环连接，如果有2张表join的话，复杂度最高是O(n^2)，3张表则是O(n^3)，表中的数据量越多，JOIN的效率会呈指数级下降。 在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行。两个概念：驱动表（外部表）和内部表，这里用表这个词其实不是很准确，外部表和内部表可以是某张表的结果集。 在执行计划中如何区分外部表和内部表。我的通常习惯是安装前后顺序，nestloop join 里面位于上面的是外部表，位于下面的是内部表。一般情况下外部表的loop=1，内部表的loop=外部表的行数。 如下： 根据定义我们可以得到 nest loop 适合于一下两个场景 1、外部表结果集较小，循环次数就少。内部表如果是大表一定要保证大表对应的join字段有索引。 2、外部表结果集较大，内部表结果集较少的场景，同样可以保证nest loop 性能较优，但是如果外部表结果集非常大，循环次数就会很多，就会导致查询缓慢，这时候用hash join 可能效果会更好。 所以总结下来 如果要优化 nest loop join 有两个方向 1、减少驱动表的结果集，降低循环次数 2、加快内部表的遍历时间，通过加索引等方式提高内部表的查询效率。 但是具体外部表的记录集多大之后就不建议使用 nest loop join 了，有说1万的有说10万的，本人觉得还是要根据实际sql，这里应该没有具体限定值。 nest loop 相关内核参数 enable_nestloop 是否使用 nestloop join 默认是on MySQL 8.0中优化了join查询，新增了 hash join算法。 Hash Join 是一种高效的联表查询算法，通常用于处理较大数据集的连接操作。下面将详细介绍 Hash Join 的原理，并通过示例图解说明其查询步骤。 Hash Join 原理 Hash Join 的基本原理是将一个表的数据构建成一个哈希表，然后利用该哈希表来查找另一个表中匹配的行。其主要分为两个阶段： 构建阶段（Build Phase）： - 选择一个较小的表（称为构建表）来创建哈希表。 - 根据连接条件的键值计算哈希值，并将这些键值和对应的行存储在哈希表中。 探测阶段（Probe Phase）： - 对另一个表（称为探测表）逐行读取数据。 - 对于探测表中的每一行，计算连接字段的哈希值，并在哈希表中查找匹配的行。 - 如果找到匹配，则将匹配的行组合在一起，形成结果集。 hash join 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。 这种方式适用于较小的表完全可以放于内存中的情况， 这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。 从这里就可以看到他的特点和hash索引有点相似，只能用于等值连接。另外他还受限于内存设置，而KingBase 对应的内存就是work_mem。需要保证hash数据全部存放在内存中才能保证hash join 性能最优，否则如果用到过多的 disk 就会严重影响性能。 这里的hash表是存在内存中的，内存是有限制的，超过阈值之后就会走 磁盘Hash join 的算法 磁盘hash join 如果驱动表中的数据量超过阈值，就会走磁盘hash join的算法。将驱动表拆分成多个哈希区（或桶），每个桶存储在磁盘上。读取磁盘上的hash桶分别加载到内存，进行探测匹配，探测完成释放当前内存桶，继续从磁盘上读取下一个hash桶进行探测匹配，直到磁盘上所有的hash桶都处理完毕。 总结 在实际开发中，尽量减少多表join查询，保持SQL的逻辑清晰，这样不仅能提高性能，还有利于维护。 1. nest loop join 顺序很重要，驱动表要尽可能晓。 因为loop循环次数直接影响执行时间 2. 两个大表关联场景hash join 最优，但是hash join 对work_mem 要求高，尤其是IO比较慢的情况下。 3. 一般小表的join，hash join 快，但都是ms级别响应，差别不大。 转载: >https://juejin.cn/post/7438597251487268875 https://www.cnblogs.com/kingbase/p/17798306.html