Mysql如何Join一条语句

597 阅读7分钟

为什么在执行Join语句时,常说需要小表驱动大表去查询,小表驱动大表就可以查询的更快呢?

在了解这个机制之前,先了解一下Mysql是如何执行一条Join语句,那么自然也就知道了为什么Mysql建议小表驱动大表的方式去进行查询,可以有效的提升查询效率

Simple Nested-Loop Join

简单join查询,Mysql并没用使用这种查询方式,因为这种查询方式会产生笛卡尔积,导致在表关联的查询过程中,扫描大量的行数,导致查询效率非常慢。

  • SLJ的执行过程

    select * from t1 straight_jon t2 on t1.a = t2.a;
    

    其中t1表和t2表的a字段都没有创建索引

    执行流程:

    1. 从表t1中读取一行数据,并获取到其中的a字段
    2. 拿到t1表的a字段,全表扫描t2表,进行a字段的匹配
    3. 如果可以匹配到,就将匹配到的数据加入结果集
    4. 重复执行1-3步骤,直到t1表做一次全量扫描
  • 时间复杂度

    执行的过程中,需要对表t1进行一次全表扫描,对t2表进行一次全表扫描,所以查询效率较慢,时间复杂度为:N*M

    当表的数据量较大的时候,扫描的行数是非常大的,例如t1和t2都是10w行,那么扫描总行数为100亿行

Index Nested-Loop Join

在这种查询的过程中,和嵌套插叙类似,并且在查询的过程中可以使用到被驱动表的索引,所以可以简称为NLJ

  • NLJ的执行过程

    select * from t1 straight_jon t2 on t1.a = t2.a;
    

    其中t2表的a字段创建了索引

    之所以使用straight_join是为了防止mysql优化器对表的驱动方式进行优化

    目前这条sql语句中,t1作为驱动表,t2作为被驱动表,这条语句的执行流程为:

    1. 先从表t1中读取一行数据
    2. 从t1中读取的一行数据中取出字段a,拿到a字段到t2表中查询
    3. 先使用t1中的a字段的值到t2的a字段的索引树上进行查询,然后拿到对应的t2表的主键id
    4. 使用主键id到t2表的主键索引树上进行查询,获取到整行数据
    5. 将t1中的一行结果和t2表中的一行结果加入到结果集中
    6. 重复执行步骤1-5,直到t1表的数据扫描完成
  • 时间复杂度

    在执行的过程中,NLJ的时间复杂度主要由2个表的扫描构成:

    • t1表的全量扫描N行
    • 查询一行被驱动表,t2表的a字段索引查询logM,回表查询logM,时间复杂度为2logM,当查询N行时,总的时间复杂度N*2*logM

    总的时间复杂度为:N + N*2*logM

  • 小表驱动大表

    显然可以看到,N对整体的时间复杂度影响更大,所以需要尽可能让N的值小,那么在两个表join时,让小表作为驱动表就更合适,时间复杂度更低,查询效率就更高

    当然,这里的小表驱动大表的前提是“可以使用被驱动表的索引”,当无法使用到被驱动表的索引时,时间复杂度就不一样了

Block Nested-Loop Join

为了解决SLJ算法的扫描行数过多的问题,Mysql使用了BLJ算法来解决当被驱动表上没有索引时,两个表之间的关联查询操作

  • BLJ的执行流程

    select * from t1 straight_jon t2 on t1.a = t2.a;
    

    其中t1表和t2表的a字段都没有创建索引

    1. 将表t1的数据读入线程内存join_buffer中,由于这里select *,所以将t1表所有字段都放入到内存
    2. 扫描t2表,将t2表中的每一行都取出来,然后去join_buffer中的t1表的数据做匹配,如果满足join条件,那么将这一行数据加入到结果集中
    3. 重复执行2步骤,知道t2表扫描完成
  • 时间复杂度

    在BLJ的执行过程中,对t1表和t2表都做了一次全表扫描,所以时间复杂度是N+M,对t2表的每一行都进行判断,所以判断的次数是N*M。虽然从时间复杂度上来讲,BLJ和SLJ是一样的,但是BLJ算法的全表扫描几乎是常数项的时间复杂度,可以忽略不计,唯一比较耗时的是判断次数,但是这些判断操作都是在内存中进行操作,速度会快很多,并且性能也好。

    这个时间复杂度和驱动表没有什么关系,无论是t1还是t2作为驱动表,时间复杂度几乎都是一样的,唯一的区别就是join_buffer加载的数据不一样

  • 分段查询

    可以看到大表或者小表作为驱动表的时间复杂度都是一样的,但是join_buffer使用是不一样的,那么大表如果在join_buffer中放不下怎么办?Mysql的策略就是如果放不下,就执行分段查询

    join_buffer的大小是通过参数join_buffer_size设置,默认的大小时256k

    • 分段查询过程

      如果表的大小大于join_buffer,那么就会执行分段查询

      1. 扫描表t1,顺序读取数行放入join_buffer中,直到join_buffer放满之后,无法继续放了,那么就会停止,先执行第二步
      2. 扫描表t2,把t2中的每一行取出来,和join_buffer中的数据做匹配,满足join条件的数据,会被放入到结果集中
      3. 清空join_buffer
      4. 继续扫描表t1,顺序读取剩下的行数据,继续放入到join_buffer中,然后继续执行步骤2

      可以看到,分段查询的过程中,会重复利用join_buffer去进行匹配结果集

    • 分段查询的时间复杂度

      分段查询的过程中,虽然利用了join_buffer的内存查询效率较高的特性,但是也会产生两次扫描表t2的过程

      假设t1表的行数为N,需要分为K段才能完成扫描,被驱动表的行数为M

      那么时间复杂度为:

      • 扫描行数时间复杂度为:N+K*M
      • 内存的判断次数为:N*M

      显然,扫描的行数依赖于K的大小,而K表示的分的段数,在join_buffer确定的情况下,表的数据量越大,那么分的段数就越多,扫描的行数就越多

  • 小表驱动大表

    所以,根据上面的时间复杂度可以判断,当驱动表的数据行数越小,那么总的扫描的行数就越少,总的判断次数是不变的,那么查询的效率就越高。

    当然,这个前提是表数据量过大,不能一次性全部加载到join_buffer中,当join_buffer足够大,可以一次性将表全部加载到内存时,驱动表的选择不会影响查询效率

    这也是为什么一些建议将join_buffer_size设置大一些

小表的定义

  • 什么是小表

    • 条件中筛选的数据量少

      select * from t1 stright_join t2 on t1.a = t2.a where t2.id < 50;
      

      t1表的数据量为1w条,而t2表的数据量为10w条

      这条sql只查询了t2表的前50条数据,所以相对于t1表而言,t2表在当前这条sql中是""小表"

    • 查询字段少

      select t1.b, t2.* from t1 stright_join t2 on t1.a = t2.a
      

      假设t1、t2中都只有100行数据进行join

      上面的sql中,t1理论上来讲应该是"小表",因为t1中只需要将字段b放入join_buffer,而t2需要将所有的字段放入到join_buffer

    所以,在定义小表的过程中,不仅需要看表的数据量大小,还需要结合sql的条件进行过滤,过滤完成之后,根据查询的字段再进行判断,数据量小的表就是"小表"

所以,在Mysql查询的过程中,总是应该选择小表驱动大表的方式进行查询,降级查询的时间复杂度,提高查询效率,特别是当表的数据量过大时,更应该采用这种方式查询。