为什么mysql不建议对超过3个表进行多表相关查询？

作者：爪哇语搞笑

在和其他公司的DBA交流的时候，谈到了MySQL和PG在多表相关查询方面的一些区别。相比之下，MySQL 对表只有一种类型的联接：嵌套循环联接（nested loop），不支持排序联接（merge join）和散列联接（hash join），而 PG 则支持这两种联接，并且 mysql 的设计就是为了简单。如果有多个表相关查询（超过3个表）的效率不如PG。

简单讨论一下MySQL多表关联功能~

是多张MySQL表的关联查询还是一张表的多次查询效率更高呢？

A、B两个表的数据规模为数十万。数据范围并不大。一台 MySQL 服务器就足够了。在单独的基础上，两个表的数据必须相关。我们先说极端的情况。 A 和 B 表上没有索引，关联是笛卡尔积，所以关联结果爆炸，可以达到上亿。此时，网络IO成为瓶颈。此时，100,000 行结果集中的两次扫描可能比一次扫描小得多。要下载亿级结果集，合并关联并下载到服务层会更快。

但这种愚蠢的行为在现实业务中一般不存在。一般来说，关联会有连接条件，连接条件会有索引。一般来说，存在一个相对较小的结果集。获取这个结果集并转到另一个表。想要关联更多的信息，如果在服务层做的话，最快的办法就是先查A表得到一个小的结果集，一次RPC，然后根据结果集拼出B表的查询条件，去B中。表是找到的结果集，再次使用rpc，将结果集下载回服务层，再次使用rpc，然后服务层合并，rpc完成3次。如果您使用数据库连接，则会下载相关结果，rpc 将为您提供帮助。这节省了两次 RPC。数据库中的关联当然更快。对应的数据库是嵌套循环连接，这是常见的业务情况。

但确实大多数企业都会考虑将这种合并操作放在服务层。一般来说，需要考虑以下因素：

• 首先：单独数据库的计算资源非常昂贵，并且数据库必须用于写入和读取都需要CPU。为了提高数据库吞吐量并且不用担心数百微秒到毫秒的延迟，企业将更多的计算放入服务层。毕竟计算资源非常有限。横向扩展很难，但是数据库就很难，所以大多数企业把纯粹的计算操作放在服务层，把数据库作为一个具有事务能力的kv系统。这就是重业务、轻业务的DB架构思想
• 第二：很多复杂的业务由于历史发展的原因可能不会只使用一种数据库。一般情况下，一个中间件层会加入多个数据库。多个数据库 无法连接到它们。自然，业务对服务层进行了抽象，以减少与数据库的连接。
• 第三：对于一些大公司来说，由于数据规模太大，要做分库分表。对于分库分表的应用，join的使用也受到很多限制，如果业务不能很好的根据sharding key明确表明要join的两个表在同一个物理数据库中。中间件一般不能很好地支持跨数据库连接。

举一个很常见的业务例子，一个分库一个分表需要同步更新两张表。两个表位于不同的物理库中。为了保证数据的一致性，一种方法是使用分布式事务。中间件在单个事务中嵌入两个更新操作，但此类操作通常需要全局锁，性能非常慢。然而，一些企业可以容忍短期数据不一致。怎么做？让他们分别更新，但是存在数据写入失败的问题，那么运行一个定时作业，扫描A表是否有失败的行，然后看看B表是否也写入成功，然后匹配两个关联。此时无法使用连接更正记录。数据只能下载到服务层并由应用程序本身进行合并。 。 。

这里的答案很明确了~

分解相关查询

许多高性能应用程序都会分解相关查询。

您只需对每个表执行一个表查询，然后在应用程序中关联结果。例如，以下查询：

select * from tag
join tag_post on tag_post.tag_id=tag.id
join post on tag_post.post_id=post.id
where tag.tag=’mysql’;

可以拆分为以下查询：

Select * from tag where tag=’mysql’;
Select * from tag_post where tag_id=1234;
Select * from post where id in(123,456,567,9989,8909);

为什么会发生这种情况？原来是一个查询变成了多个查询，而且返回的结果是一模一样的。

通过分解相关查询来重构查询实际上有以下好处：

1. 缓存效率。
   很多应用程序都可以方便地缓存单表查询对应的结果对象。此外，如果MySQL查询缓存更改了关联中的表，则查询缓存将无法使用。分区后，如果表很少发生变化，可以重复基于表的查询。使用查询缓存结果。
2. 拆分后执行单个查询可以减少锁争用。
3. 在应用层创建关联可以轻松对数据库进行分区并实现高性能和可扩展性。
4. 查询本身的有效性也可以提高
5. 可以减少对冗余记录的查询。
6. 更进一步，这相当于在应用程序中实现了哈希关联，而不是使用 MySQL 的嵌套循环关联。在某些场景下，哈希关联的效率要高得多。