Written by honors

JOIN语句优化

JOIN的种类

file

还有一种不在图中的JOIN,cross join (笛卡尔连接)

select 
* 
from 
    users a  
cross join order b

不需要ON,会将两个表中的数据任意连接，得出的是两个表中数据的乘积。如果带有ON字句，就相当于INNER JOIN.

JOIN算法-Nested-Loop JOIN (NLJ) (嵌套循环JOIN)

file

如图的三张表，使用NLJ算法时，会先查询出t1中符合条件的数据，使用for循环遍历这些数据，然后在t2表中查询出符合条件的数据，并使用reference key 去匹配，也就是匹配时的ON字段，再查询出第三张表中的数据去做匹配，由于第三张表时全表扫描，所以直接用一个for循环去循环t3表的所有数据，然后将符合条件的数据返回给客户端。
可以看出这个算法是比较粗暴的，外表的数据越多，内表循环的次数也就越多

JOIN算法-Block Nested-Loop JOIN (BNLJ) (块循环嵌套JOIN)

file

从伪代码中可以看出，在前两个循环中是一样的，区别在第三步，在执行第三步时，先将t1，t2表中需要用到的字段存到一个叫join buffer(连接缓存)的地方。如果循环满了，就去匹配t3，循环t3，将t3中的数据和join buffer中的数据做匹配，符合条件的数据返回给客户端。
相对于NLJ,如果在循环t3开始之前，有100条元素，那么就会循环100次t3，而对于BNLJ,会将这100条数据缓存到join buffer中，如果缓存足够大，可以将100条全部存放进join buffer，大幅度减少了内存循环的表扫描次数，如果不能全部放入缓存，会扫描的次数可以使用扫描次数计算公式计算
扫描计算公式

(S*C)/join_buffer_size +1

S : 缓存的t1/t2表的一行数据
C : 缓存的行数
join_buffer_size : join buffer的大小

使用join buffer 的条件

  • 连接类型时ALL、index或range
  • 第一个nonconst table（非常量表）不会分配join buffer，即使类型是ALL或者index
  • join buffer 只会缓存需要的字段，而非整行数据
  • 可以通过设置join_buffer_size改变join buffer 的大小
  • 每个能被缓存的join都会分配一个join buffer，一个查询可能拥有多个join buffer
  • join buffer会在执行连接之前分配，在查询完成之后释放

设置join_buffer_size

# 查看join_buffer_size大小
show variables like 'join_buffer_size';
# 设置当前查询join_buffer_size大小为50M
set 'join_buffer_size' = 1024 * 1024 * 50;
# 设置全局join_buffer_size大小为50M
set global 'join_buffer_size' = 1024 * 1024 * 50;

查看join buffer是否使用
file
可以看到Extra中，Using where; Using join buffer (flat, BNL join)

Batch Key Access Join（BKA）(批量键值访问)

  • MySQL5.6引入
  • BKA基础：Multi Range Read(MRR)
  • MRR核心：将随机IO转换成顺序IO，从而提升性能

BKA流程

file
先从join buffer中读取数据，最后从T3表的索引和Join buffer匹配，筛选出匹配的索引之后，再按照T3表的索引进行排序，最后再去T3表的表数据中读取数据，也就是说使用BKA的话，会批量读取一堆数据的行，使用MRR进行排序，最后再去表中获取数据

BKA参数

  • optimizer_switch的子参数
    • batch_key_access: on开启，off关闭

了解MRR

创建一条语句

EXPLAIN SELECT * FROM salaries WHERE from_date <= '1980-01-01'

执行之前先查看表结构
file
salaries表的主键是emp_no和salaries，而我们的查询只有salaries，不符合最左前缀原则，所以主键是用不上的
然后再看索引
file
创建了一个组合索引，作用在from_date和to_date上，因此这个查询会使用这个组合索引
执行
file
确实是组合索引，但是我们的查询条件是范围查询，即便是使用了索引，依然会伴随大量的随机IO,
因为我们的数据是根据主键排列的，而不是from_date排列的，一旦有随机IO就需要定位等等，所以性能会比较差，MRR会优化掉这个随机IO

MRR优化方式
MRR并不是查询到一条符合条件的索引就去表中寻找数据，而是把符合条件的索引先丢到一个缓存中，比如依次找到了一下数据

[1978-06-06,1978-07-07,(30000,1978-06-06)]
[1979-06-06,1979-07-07,(20000,1979-06-06)]
[1977-06-06,1977-07-07,(80000,1978-06-06)]

MRR会先将这些数据按照主键排序，变成

[1979-06-06,1979-07-07,(20000,1979-06-06)]
[1978-06-06,1978-07-07,(30000,1978-06-06)]
[1977-06-06,1977-07-07,(80000,1978-06-06)]

排序完成之后，再去表中寻找数据，因为B+tree中的数据都是按照主键顺序排列的，使用MRR排序后，就更像是顺序IO,性能要比随机IO好很多，查询就会快一些
MRR并不一定会快一些，因为他带来了排序的开销
MRR参数

  • optimizer_switch的子参数
    • mrr: 是否开启mrr,on开启，off关闭
    • mrr_cost_based:表示是否要开启基于成本计算的MRR，on开启，off关闭
  • read_rnd_buffer_size: 指定mrr缓存大小

HASH JOIN

MySQL 8.0.18引入，用来替代BNLJ
join buffer 缓存外部循环的hash表，内层循环遍历时到hash表匹配

注意点

  • MySQL8.0.18才引入，且有很多限制，比如不能作用于外连接没比如left join / right join 等等。从8.0.20开始，限制少了很多，建议使用8.0.20或更高版本
  • 从MySQL8.0.18开始，hash join 的join buffer是递增分配的，这意味着，你可以将join_buffer_size设置的比较大。而在MySQL8.0.18中，如果你使用了外连接，外连接没法使用hash join,此时join_buffer_size会按照你设置的值直接分配内存。因此join_buffer_size还是得谨慎设置。
  • 从MySQL8.0.20开始，BNLJ已经被删除了，用hash join 替代了BNLJ

驱动表和被驱动表

外层循环得表是驱动表，内层循环得表是被驱动表
file

如图，t1是t2的驱动表，t2是t1的被驱动表，t2是t3的驱动表，t3是t2的被驱动表.

JOIN 调优原理

  • 用小表驱动大表，即用数据量较小的表做驱动表，数据量较大的表做被驱动表。（一般不需要人工考虑，关联查询优化器会自动选择最优的执行顺序，如果优化器抽风，可以使用STRAIGHT_JOIN）
  • 如果有where条件，应当要能够使用索引，并尽可能的减少外层循环的数据量
  • 参与join的表不要太多
  • 如果被驱动表的join字段用不了索引，且内存较为充足，可以考虑把join buffer设置的大一些

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注