jieye の 数字花园

Search

Search IconIcon to open search

Mysql join优化

Last updated Unknown

# Mysql Join 优化

# Mysql Join 优化

# Mysql Join 优化

image.png

# 先说结论

首先,使用 小表 作为驱动表

  1. 如果可以使用 Index Nested-Loop Join 算法,也就是说可以用上被驱动表上的索引,其实是没问题的;
  2. 如果使用 Block Nested-Loop Join 算法,扫描行数就会过多。尤其是在大表上的 join 操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源。所以这种 join 尽量不要用。

判断要不要使用 join 语句时,就是看 explain 结果里面,Extra 字段里面有没有出现“Block Nested Loop”字样

  1. 如果是 Index Nested-Loop Join 算法,应该选择 小表 做驱动表;
  2. 如果是 Block Nested-Loop Join 算法:
    • 在 join_buffer_size 足够大的时候,是一样的;
    • 在 join_buffer_size 不够大的时候(这种情况更常见),应该选择小表做驱动表。
      所以,这个问题的结论就是,总是应该使用 小表 做驱动表

其次,被驱动表尽量走索引或者用 where 过滤,order by 也尽量用外表字段排序,straight_join 强行指定驱动表

# MRR Multi-Range Read 优化

因为大多数的数据都是按照主键递增顺序插入得到的,所以我们可以认为,如果按照主键的递增顺序查询的话,对磁盘的读比较接近顺序读,能够提升读性能。

这,就是 MRR 优化的设计思路。此时,语句的执行流程变成了这样:

  1. 根据索引 a,定位到满足条件的记录,将 id 值放入 read_rnd_buffer 中 ;
  2. 将 read_rnd_buffer 中的 id 进行递增排序;
  3. 排序后的 id 数组,依次到主键 id 索引中查记录,并作为结果返回。

set optimizer_switch="mrr_cost_based=off"

# 使用小表作驱动表

join 语句执行过程中,驱动表是走全表扫描,而被驱动表是走树搜索。

假设被驱动表的行数是 M。每次在被驱动表查一行数据,要先搜索索引 a,再搜索主键索引。每次搜索一棵树近似复杂度是以 2 为底的 M 的对数,记为 log2M,所以在被驱动表上查一行的时间复杂度是 2*log2M。

假设驱动表的行数是 N,执行过程就要扫描驱动表 N 行,然后对于每一行,到被驱动表上匹配一次。

因此整个执行过程,近似复杂度是 N + N_2_log2M。

显然,N 对扫描行数的影响更大,因此应该让 小表 来做驱动表。

如果你没觉得这个影响有那么“显然”, 可以这么理解:N 扩大 1000 倍的话,扫描行数就会扩大 1000 倍;而 M 扩大 1000 倍,扫描行数扩大不到 10 倍。

到这里小结一下,通过上面的分析我们得到了两个结论:

  1. 使用 join 语句,性能比强行拆成多个单表执行 SQL 语句的性能要好;
  2. 如果使用 join 语句的话,需要让 小表 做驱动表。

但是,你需要注意,这个结论的前提是“可以使用被驱动表的索引”。此时的方法被称为 Simple Nested-Loop Join