MySQL多表关联查询效率高点还是多次单表查询效率高,为什么?

本文探讨了MySQL中多表关联查询与单表查询的效率对比,解释了《阿里巴巴JAVA开发手册》中关于禁止三表以上join的原因,涉及数据库性能、查询优化、业务场景和分布式架构。提到了冗余设计和不同查询策略的选择。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

MySQL多表关联查询对比多次单表查询,哪个效率高?

疑问:

比如有a,b两张表,两张表都差不多有十几w行记录,需要查询的字段在两张表中,那么是关联查询效率高点还是分别查a,b两张表,再在service层合并数据效率高点?想问问大家在实际开发中都是怎么考量的?谢谢!

高手解答:

先说效率哪个更高:A,B两个表数据规模十几万,数据规模都不大,单机MySQL够用了。
在单机的基础上要关联两表的数据,

  • 先说一个极端情况,A,B两个表都没有索引,并且关联是笛卡尔积,那关联结果会爆炸式增长,可能到亿级别,这个时候网络IO成了瓶颈,这个时候两次十万行结果集的拉取可能远小于1次亿级别的结果集的拉取,那么将关联合并拉到service层做更快。
  • 但实际业务中一般不会有这么蠢的行为,一般关联会有连接条件,并且连接条件上会有索引,一般是有一个结果集比较小,拿到这个结果集去另一张表去关联出其它信息。
    如果放到service层去做,最快的方式是,先查A表,得到一个小的结果集,一次rpc(RPC一般指远程过程调用。RPC是远程过程调用(Remote Procedure Call)的缩写形式),再根据结果集,拼凑出B表的查询条件,去B表查到一个结果集,再一次rpc,再把结果集拉回service层,再一次rpc,然后service层做合并,3次rpc获得结果
    如果用数据库的join,关联结果拉回来,一次rpc,帮你省了两次rpc,当然数据库上做关联更快,对应到数据库就是一次blk nested loop join,这是业务常用情况。
    但是确实大多数业务都会考虑把这种合并操作放到service层,我觉得有几方面考虑:
    第一:单机数据库计算资源很贵,数据库同时要服务写和读,都需要消耗CPU,为了能让数据库的吞吐变得更高,而业务又不在乎那几百微妙到毫秒级的延时差距,业务会把更多计算放到service层做,毕竟计算资源很好水平扩展,数据库很难啊,所以大多数业务会把纯计算操作放到service层做,而将数据库当成一种带事务能力的kv系统(KV数据库:Key-Value,让人想到Mongo数据库和JSON数据交换格式。 数据存储,存储的数据主要分两种:结构数据(关系表),非结构数据(大文件,杂数据(模式和内容揉合(HTML)))来使用,这是一种重业务,轻DB的架构思路
    第二:很多复杂的业务可能会由于发展的历史原因,一般不会只用一种数据库,一般会在多个数据库上加一层中间件,多个数据库之间还能做FetchMode.join,自然业务会抽象出一个service层,降低对数据库的耦合。
    第三:对于一些大型公司由于数据规模庞大,不得不对数据库进行分库分表,这个问题我在《阿里为什么要禁用三表以上的join》上也回答过,对于分库分表的应用,使用join也受到了很多限制,除非业务能够很好的根据sharding key(分区key)明确要join的两个表在同一个物理库中。而中间件一般对跨库join都支持不好。举一个很常见的业务例子,在分库分表中,要同步更新两个表,这两个表位于不同的物理库中,为了保证数据一致性,一种做法是通过分布式事务中间件将两个更新操作放到一个事务中,但这样的操作一般要加全局锁,性能很捉急,而有些业务能够容忍短暂的数据不一致,怎么做?让它们分别更新呗,但是会存在数据写失败的问题,那就起个定时任务,扫描下A表有没有失败的行,然后看看B表是不是也没写成功,然后对这两条关联记录做订正,这个时候同样没法用join去实现,只能将数据拉到service层应用自己来合并了。

《阿里巴巴JAVA开发手册》里面写超过三张表禁止join 这是为什么?这样的话那sql要怎么写?

一:为什么做这种限制?

打个比方,如果我有无限的钱,我想买个豪华别墅,想买个跑车,想买个直升飞机,但现实是我没钱,只能租房住,只能走路上下班。。
如果数据库的性能无限强大,多个表的join肯定是需要的,尤其是复杂的分析型(OLAP)查询,甚至可能涉及10几个表的join,但现实是大部分数据库的性能都太弱了,尤其是涉及到多表join的查询。
规范一看就是在使用MySQL时的限制(这种规范实际上迫不得已的限制),做这个限制有两个原因:一是优化器很弱,涉及多个表的查询,往往得不到很好的查询计划;二是执行器很弱,只有nested loop join,block nested loop join和index nested loop join。

  1. nested loop join就是分别从两个表读一行数据进行两两对比,复杂度是n^22.
  2. block nested loop join是分别从两个表读很多行数据,然后进行两两对比,复杂度也是n^2,只是少了些函数调用等overhead
  3. index nested loop join是从第一个表读一行,然后在第二个表的索引中查找这个数据,索引是B+树索引,复杂度可以近似认为是n log n,比上面两个好很多,这就是要保证关联字段索引的原因
  4. 如果有hash join,就不用做这种限制了,用第一个表(小表)建hash table,第二个表在hash table中查找匹配的项,复杂度是n。缺点是hash table占的内存可能会比较大,不过也有基于磁盘的hash join,实现起来比较复杂

二:在这种限制下SQL怎么写?

可是我确实需要两个表里的数据链接在一起啊,我们可以做个冗余,建表的时候,就把这些列放在一个表里。
比如: 一开始有
student(id, name),
class(id, description),
student_class(student_id, class_id)三张表,
这样是符合数据库范式的(第一范式,第二范式,第三范式,BC范式等),没有任何冗余,但是马上就不符合“编程规范“了,那我们可以用一张大表代替它,student_class_full(student_id, class_id, name, description),这样name和description可能要被存储多份,但是由于不需要join了,查询的性能就可以提高很多了。任何的规范都是在特定情况下的某种妥协,脱离了这个环境,就不一定成立了。

<think>嗯,用户想了解MySQL关联查询的性能对比,哪个更快。我需要先回忆一下相关的知识。记得之前看过一些资料,说子查询JOIN的性能比较并不是绝对的,取决于具体情况。比如子查询有时候会被优化成JOIN操作,但有时候如果子查询相关的话,可能会有不同的执行计划。 首先,应该考虑数据库的优化器如何处理这种写法。MySQL的优化器在某些版本中对子查询的处理可能不够高效,尤其是在旧版本中,相关子查询可能会导致性能问题,因为每行数据都要执行一次子查询。而JOIN操作通常可以利用索引,尤其是当关联字段有索引时,性能会更好。 不过也有例外情况,比如当子查询的结果集很小,并且外层查询能够有效利用索引时,子查询可能更快。例如,引用中的例子提到子查询作为外层WHERE条件,可能让外层查询走索引,从而避免全扫描。这时候子查询可能比LEFT JOIN快。 另外,还要看具体的查询结构。如果是层嵌套的子查询,尤其是相关子查询,可能会影响性能,因为需要为外层查询的每一行执行子查询。而JOIN操作可以通过一次扫描完成,尤其是当使用合适的连接算法时,比如Nested Loop Join、Hash Join等。 还要考虑索引的情况。如果关联字段或者子查询中的过滤条件字段有合适的索引,那么无论是JOIN还是子查询都可能高效。反之,没有索引的话,者都可能慢。 可能还需要注意数据量的大小。当数据量很大时,JOIN操作可能会更高效,尤其是在关联字段有索引的情况下,可以快速定位到匹配的行。而子查询如果结果集很大,可能会导致临时的创建,增加额外的开销。 另外,子查询的类型也很重要。比如,EXISTS子查询通常可以转换为半连接(Semi Join),这种情况下优化器可能会优化得比较好。而IN子查询在某些情况下可能会被优化成JOIN,但有时候也可能导致全扫描。 总结一下,需要告诉用户,性能对比没有绝对的答案,要根据具体情况分析。通常建议使用JOIN,尤其是当关联字段有索引时,但也要考虑子查询的具体写法、数据量、索引情况以及MySQL的版本。最好是通过实际执行EXPLAIN命令来看执行计划,比较者的查询开销,或者在实际数据环境中进行性能测试。</think>### MySQL关联与子查询性能对比分析 #### 一、核心结论 1. **常规场景下,关联(JOIN)通常性能更优** - 当关联字段有索引时,JOIN可通过索引快速匹配数据,减少扫描量[^3][^4] - JOIN操作能利用数据库的优化器生成更高效的执行计划[^1] 2. **特定场景下子查询可能更快** - 子查询结果集极小且外层查询能利用索引时(如`WHERE id IN (子查询)`)[^3] - 层嵌套相关子查询可精准控制筛选逻辑时[^2] #### 二、性能差异原理 | 操作类型 | 执行特 | 典型场景 | |---------|---------|---------| | **关联** | 单次扫描关联,通过索引快速匹配 | 数据量较大且有关联索引 | | **子查询** | 可能逐行执行子查询(相关子查询) | 筛选条件复杂且结果集小 | #### 三、优化建议 1. **优先尝试JOIN写法** ```sql -- JOIN示例 SELECT e1.* FROM employees e1 JOIN departments d ON e1.dept_id = d.id WHERE d.name = 'Engineering'; ``` 优势:可利用`dept_id``id`的联合索引 2. **子查询优化策略** ```sql -- 优化后的子查询 SELECT * FROM orders WHERE customer_id IN ( SELECT id FROM customers WHERE country = 'CN' -- 确保子查询字段有索引 ); ``` 关键:为`customers.country`字段添加索引 #### 四、验证方法 1. 使用`EXPLAIN`分析执行计划 ```sql EXPLAIN SELECT... -- 对比种写法的type/rows/Extra字段 ``` 2. 性能测试工具 ```sql -- 使用BENCHMARK函数(需注意缓存影响) SELECT BENCHMARK(100000, (SELECT ...)); ``` #### 五、典型场景对比 | 场景 | 推荐方案 | 原因 | |------|---------|------| | 层过滤条件 | 相关子查询 | 精准控制逻辑流 | | 大数据量关联 | JOIN操作[^1] | 减少临时生成 | | 存在覆盖索引 | 子查询[^3] | 避免全扫描 |
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值