使用JOIN 实现表关联,面试挂了!
发布网友
发布时间：2024-10-01 10:32
共1个回答
热心网友
时间：2024-12-03 14:41
记得有一次去面试，面试官问了一道关于数据库的问题，问题大致内容如下：
面试官：你平时经常写sql吗？
我：肯定啊，这不是必备小技能吗。
面试官：那你知道多表数据之间有关联，你是怎么查数据的呢？
我：JOIN啊。
面试官：好用吗？
我：贼好用，我特别是LEFT JOIN，我用的贼熟练，天天写。
面试官：emm......，那你回去等通知吧。
不知道大家有没有遇到过这种问题，或者说大家平时是如何处理多表关联的数据的，是不是也用JOIN语句来处理呢。
我们暂且不直接下定义说JOIN语句有啥问题，先来看一个简单的例子。
测试数据准备这里我使用的是mysql数据库，由于测试需要我们先创建两张表t/t2，语句比较简单
CREATETABLE`t`(`id`int(11)NOTNULL,`a`int(11)NOTNULL,`b`int(11)NOTNULL,PRIMARYKEY(`id`),KEY`a`(`a`),KEY`b`(`b`))ENGINE=InnoDBDEFAULTCHARSET=latin1;CREATETABLE`t2`(`id`bigint(19)NOTNULL,`a`int(11)DEFAULTNULL,`c`varchar(255)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=latin1;再使用函数创建一些测试数据，我这里在 t 表中创建一千万条，在 t2 表中创建一万条。
CREATEDEFINER=`root`@`localhost`PROCEDURE`testdata`()begindeclareitemint;setitem=1;while(item<=10000000)doinsertintotvalues(item,item,item);setitem=item+1;endwhile;endJOIN测试先写一句小学生都会的语句测一下：
selectt.*,t2.*fromtleftjoint2ont.a=t2.aemm......不知道等了多久之后，还是没有结束，于是乎我就加了一个limit*条件，只查询10条数据
selectt.*,t2.*fromtleftjoint2ont.a=t2.alimit10000,10这次等待的时间要少了不少，共计消耗 19.99s
这速度，感觉可以拎包袱回家种地了...
问题分析想要搞明白为什么消耗这么长时间，就要先理解这个查询过程是怎么实现的，然后才能得出结论，JOIN究竟能不能被使用。
首先看一下执行计划
其中比较重要的三个参数：
type：判断是全表扫描还是索引扫描，这里我们是 ALL ，也就是全表扫描；
rows：找到最终结果的过程中，需要扫描的行数，越少越好；
Extra：不适合其他列显示的额外信息，本例中关注 Block Nested Loop。
从上述执行计划中我们看到，为了拿到最终的 10条数据，我们需要扫描将近10000000万行数据，这个过程是非常耗时且浪费的。
整个过程可以理解为下面几步：
从表t 中读取一条数据R；
对比R中的a字段与表t2 比较；
将表t 全部数据加载到内存（join_buffer）中，逐行取出表t2数据，在内存中对比并找到满足条件 t.a = t2.a 的行，形成结果集；
重复上述步骤，直至 表t 中的数据全部遍历完成。
时间复杂度：
单次查询消耗的时间复杂度为：M+N，M驱动表数据，N被驱动表数据。
总时间复杂度：M*N。
初步解决对数据库有过一点了解的小伙伴肯定问了，表t2 中为啥不给 字段a 加索引呢，加了不就快了吗。
没毛病，我们就先尝试加一下索引，然后看看查询同样的sql耗时多少。
oh my god，这快的可不是一点两点，现在查询一次仅仅耗时0.04s，简直快到飞起
再来看一下执行计划：
此时表t依旧为全表扫描，不同的是 表t2 中的type是ref，也就是说此时我们是用索引，再来看一下整个执行过程：
从表t 中读取一条数据R；
对比R中的a字段与表t2 比较；
根据索引a， 匹配满足条件 t.a = t2.a的行，形成结果集；
重复上述步骤，直至 表t 中的数据全部遍历完成。
可以发现，与上面的步骤相比，只是在第三步 ，查找被驱动表t2 时有所不同，每执行一次步骤3，就只需要执行两次搜索，即搜索索引a和主键索引，然后找到所需要的被驱动表中的数据。
时间复杂度：
单次查询消耗的时间复杂度为：1 + 2 * log2N，N为被驱动表t2 的行数。
总时间复杂度：M(1 + 2log2N)* ，M驱动表数据，N被驱动表数据。
JOIN算法对比分析上面分析了两种JOIN过程，即 使用索引和无索引状态下的JOIN过程：
Simple Nested-Loop Join
该算法就是嵌套循环，依次读取驱动表中的数据，对于每次取出的数据，再和被驱动表进行比较，比如本例中我们驱动表数据（10000000）* 被驱动表数据（10000） = 100000000000 亿次的时间复杂度，当然这种算法没有数据库会采用的。
Block Nested-Loop Join
该算法就是我们没有新增索引时，MySQL的InnoDB引擎 就会使用这种算法，具体过程如上，时间复杂度和上述算法相同，都是M * N。
Index Nested-Loop Join
该算法是增加索引后，MySQL会使用该算法，具体过程上面也已经介绍过。
JOIN能不能用如果我们使用 Index Nested-Loop Join 算法，其实是没有多大影响的，完全满足绝大多数场景的使用。
如果使用 Block Nested-Loop Join 算法，就会导致大量的扫描行数，尤其像本例中这种上千万，乃至上亿的数据量，这样会导致被驱动表扫描过多次，占用大量的系统资源和缓存资源，非常不建议使用。
至于开头提到的面试官的问题，其实是真的发生过的，面试官难道想得到的答案是在内存中处理表关联的数据，在sql执行时只查询单表吗，其实这种时间复杂度并没有减少，反而会增加sql执行次数，不知道大家是怎么使用的呢？
下一章我们就要探讨一下，当使用JOIN时如何对其进行优化？以及本例中的缓存（join_buffer）是什么，如何优化？以及其他提升查询效率的有效手段。
