十、MySQL表分区

  表分区是将⼀个表的数据按照⼀定的规则⽔平划分为不同的逻辑块，并分别进⾏物理存储，这个规则就叫做分区函数，可以有不同的分区规则。5.7可以通过show plugins语句查看当前MySQL是否⽀持表分区功能。

  但当表中含有主键或唯⼀键时，则每个被⽤作 分区函数的字段必须是表中唯⼀键和主键的全部或⼀部分 ，否则就⽆法创建分区表。⽐如下⾯的表由于唯⼀键和主键没有相同的字段，所以⽆法创建表分区

上述例⼦中删除唯⼀键，确保主键中的字段包含分区函数中的所有字段，创建成功

或者将主键扩展为包含ref字段

表分区的主要优势在于：

  可以允许在⼀个表⾥存储更多的数据，突破磁盘限制或者⽂件系统限制

  对于从表⾥将过期或历史的数据移除在表分区很容易实现，只要将对应的分区移除即可

  对某些查询和修改语句来说，可以 ⾃动 将数据范围缩⼩到⼀个或⼏个表分区上，优化语句执⾏效率。⽽且可以通过 显示指定表分区 来执⾏语句，⽐如 SELECT * FROM t PARTITION (p0,p1) WHERE c <5

表分区类型分为：

范围表分区，按照⼀定的范围值来确定每个分区包含的数据，分区函数使⽤的字段必须只能是 整数类型，分区的定义范围必须是连续的，且不能有重叠部分，通过使⽤VALUES LESS THAN来定义分区范围，表分区的范围定义是从⼩到⼤定义的

⽐如：

  Store_id<6的数据被放在p0分区⾥，6<=store_id<10之间的数据被放在p1分区⾥，以此类推，当新插⼊的数据为(72, ‘Mitchell’, ‘Wilson’, ‘1998-06-25’, NULL, 13) 时，则新数据被插⼊到p2分区⾥，但当插⼊的数据的store_id为21时，由于没有分区去容纳此数据，所以会报错，我们需要修改⼀下表的定义

报错：

修改表的定义：

   MAXVALUE关键词的作⽤是表示可能的最⼤值，所以任何store_id>=16的数据都会被写⼊到p3分区⾥。分区函数中也可以使⽤表达式 ，⽐如：

   对timestamp字段类型可以使⽤的表达式⽬前仅有unix_timestamp ，其他的表达式都不允许

列表表分区，按照⼀个⼀个确定的值来确定每个分区包含的数据，通过PARTITION BY LIST(expr)分区函数表达式必须返回整数，取值范围通过VALUES IN (value_list)定义

对List表分区来说，没有MAXVALUE特殊值，所有的可能取值都需要再VALUES IN中包含，如果有未定义的取值则会报错

同样，当有主键或者唯⼀键存在的情况下，分区函数字段需要包含在主键或唯⼀键中

对range和list表分区来说，分区函数可以包含多个字段，分区多字段函数(column partition) 所涉及的字段类型可以包括：

范围多字段分区函数与普通的范围分区函数的区别在于：

a) 字段类型多样化

b) 范围多字段分区函数 不⽀持表达式，只能⽤字段名

c) 范围多字段分区函数⽀持⼀个或多个字段

再⽐如创建如下的表分区：

对多列对⽐来说：

当然只要保证取值范围是增⻓的，表分区就能创建成功，⽐如：

但如果 取值范围不是增⻓的，就会返回错误 ：

对其他数据类型的⽀持：

list列表多字段表分区，例如：你有一个在12个城市客户的业务, 为了销售和市场的目的, 你的组织每3个城市划分为一个区域针对LIST COLUMNS分区, 你可以基于城市的名称创建一个客户数据表并声明4个分区当你的客户在对应的这个区域:

使用日期分区

但是这种情况在日期增长到非常大的时候是很复杂的, 所以这种还是使用RANGE 分区方式比较好

  按照⼀个⾃定义的函数返回值来确定每个分区包含的数据，这个 ⾃定义函数也可以仅仅是⼀个字段名字

  通过PARTITION BY HASH (expr)⼦句来表达哈希表分区，其中的 expr表达式必须返回⼀个整数，基于分区个数的取模（%）运算。根据余数插⼊到指定的分区

  对哈希表分区来说只需要定义分区的个数，其他的事情由内部完成

如果没有写明PARTITIONS字段，则默认为1，表达式可以是整数类型字段，也可以是⼀个函数，⽐如

⽐如： CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4

如果插⼊⼀条数据对应的col3为‘2005-09-15’时，则插⼊数据的分区计算⽅法为：

  与哈希表分区类似，只不过哈希表分区依赖于⾃定义的函数，⽽key表分区的哈希算法是依赖MySQL本身， CREATE TABLE ... PARTITION BY KEY () 创建key表分区， 括号⾥⾯可以包含0个或者多个字段，所引⽤的字段必须是主键或者主键的⼀部分 ，如果括号⾥⾯没有字段，则代表使⽤主键

如果表中没有主键但有唯⼀键，则使⽤唯⼀键，但 唯⼀键字段必须定义为not null ，否则报错

所引⽤的字段未必必须是整数类型，其他的类型也可以使⽤，⽐如：

⼦表分区，是在表分区的基础上再创建表分区的概念， 每个表分区下的⼦表分区个数必须⼀致 ，⽐如：

ts表拥有三个范围分区，同时每个分区都各⾃有两个⼦分区，所以总共有6个分区

⼦表分区必须是范围/列表分区+哈希/key⼦表分区的组合

⼦表分区也可以显示的指定⼦表分区的名字，⽐如：

不同的表分区对NULL值的处理⽅式不同

对范围表分区来说，如果插⼊的是NULL值，则将数据放到最⼩的分区表⾥

对list表分区来说，⽀持NULL值的唯⼀情况就是某个分区的允许值中包含NULL

对哈希表分区和Key表分区来说，NULL值会被当成0值对待

通过alter table命令可以执⾏增加，删除，重新定义，合并或者拆分表分区的管理动作

对范围表分区和列表表分区来说，删除⼀个表分区命令如下：

删除表分区的动作不光会把分区删掉，也会把表分区⾥原来的数据给删除掉

在原分区上增加⼀个表分区可以通过alter table … add partition语句来完成

但对范围表分区来说，增加的表分区必须在尾部增加，在头部或者在中间增加都会失败：

为解决这个问题，可以使⽤ REORGANIZE 命令：

对列表表分区来说，只要新增加的分区对应的值在之前的表分区中没有出现过，就可以通过alter table… add partition来增加

当然， 也可以通过REORGANIZE命令将之前的多个分区合并成⼀个或⼏个分区，但要保持分区值⼀致：

更复杂的⽐如将多个分区重组成多个分区：

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。但内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉。我们前边又说过，采用SNLJ 算法的两表联接过程中， 被驱动表可是要被访问好多次的 。被驱动表具体的访问次数就是由驱动表返回结果集记录数决定！如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。

也就是说在有索引的情况下，MySQL会尝试去使用Index Nested-Loop Join算法，在有些情况下，可能Join的列就是没有索引，那么这时MySQL的选择绝对不会是最先介绍的Simple Nested-Loop Join算法，SNLJ算法是最慢的join，毕竟是笛卡尔积！

而Block Nested-Loop Join算法较Simple Nested-Loop Join的改进就在于可以减少内表的扫描次数，甚至可以和Hash Join算法一样，仅需扫描内表一次。其使用Join Buffer（联接缓冲）来减少内部循环读取表的次数。

关于 join buffer https://www.jianshu.com/p/3c0816862cc9

可以看到相比Simple Nested-Loop Join算法，Block Nested-LoopJoin算法仅多了一个所谓的Join Buffer，为什么这样就能减少内表的扫描次数呢？下图相比更好地解释了Block Nested-Loop Join算法的运行过程：

可以看到Join Buffer用以缓存联接需要的列（所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录呢），然后以Join Buffer批量的形式和内表中的数据进行联接比较。就上图来看，记录r1，r2 … rT的联接仅需扫内表一次，如果join buffer可以缓存所有的外表列，那么联接仅需扫描内外表各一次，从而大幅提升Join的性能。

Block Nested-Loop Join开销

Block Nested-Loop Join极大的避免了内表的扫描次数，如果Join Buffer可以缓存外表的数据，那么内表的扫描仅需一次，这和Hash Join非常类似。但是Block Nested-Loop Join依然没有解决的是Join比较的次数，其仍然通过Join判断式进行比较。综上所述，到目前为止各Join算法的成本比较如下所示：

Block Nested-Loop Join影响

在使用 Block Nested-Loop Join(BNL) 算法时，还是可能会对被驱动表做多次扫描（尽管可能已经将驱动表中大部分关联字段数据存入join buffer）。如果这个被驱动表是一个大的冷数据表，除了会导致 IO 压力大以外，还会对 buffer pool产生严重的影响！

如果了解 InnoDB 的 LRU 算法就会知道，由于 InnoDB 对 Bufffer Pool 的 LRU 算法做了优化，即：第一次从磁盘读入内存的数据页，会先放在 old 区域。如果 1 秒之后这个数据页不再被访问了，就不会被移动到 LRU 链表头部，这样对 Buffer Pool 的命中率影响就不大。

但是，如果一个使用 BNL 算法的 join 语句，多次扫描一个冷表，而且这个语句执行时间超过 1 秒，就会在再次扫描冷表的时候，把冷表的数据页移到 LRU 链表头部。这种情况对应的，是冷表的数据量小于整个 Buffer Pool 的 3/8，能够完全放入 old 区域的情况。如果这个冷表很大，就会出现另外一种情况：业务正常访问的数据页，没有机会进入 young 区域。(导致正常业务sql查询因为没有剩余buffer pool空间进一步让磁盘IO变多而变得缓慢)

由于优化机制的存在，一个正常访问的数据页，要进入 young 区域，需要隔 1 秒后再次被访问到。但是，由于我们的 join 语句在循环读磁盘和淘汰内存页，进入 old 区域的数据页，很可能在 1 秒之内就被淘汰了。这样，就会导致这个 MySQL 实例的 Buffer Pool 在这段时间内，young 区域的数据页没有被合理地淘汰。

也就是说，这两种情况都会影响 Buffer Pool 的正常运作。 大表 join *** 作虽然对 IO 有影响，但是在语句执行结束后，对 IO 的影响也就结束了。但是，对 Buffer Pool 的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。

为了减少这种影响，你可以考虑增大 join_buffer_size 的值，减少对被驱动表的扫描次数!

也就是说，BNL 算法对系统的影响主要包括三个方面： 可能会多次扫描被驱动表，占用磁盘 IO 资源； 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源； 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。

那么假设被驱动表全在内存中，这个时候 SNLJ 和 BNL 算法还有性能差别吗？当然是有的，由于 SNLJ 这个算法天然会对被驱动表的数据做多次访问，所以更容易将这些数据页放到 Buffer Pool 的头部，从而污染 Buffer Pool。另外，即使被驱动表数据都在内存中，但每次查找“下一个记录的 *** 作”，都是类似指针 *** 作。而 BNL 算法中的 join_buffer 是数组，遍历的成本更低，从被驱动表读取一条数据去 join_buffer 中遍历。

BNL的相关设置

mysql默认开启BNL

开关BNL

1、 缓存块嵌套循环连接通过一次性缓存多条数据，把参与查询的列缓存到Join Buffer 里，然后拿join buffer里的数据批量与内层表的数据进行匹配，从而减少了内层循环的次数、减少了内部表访问次数（遍历一次内层表就可以批量匹配一次Join Buffer里面的外层表数据）。

2、什么时候会使用BNL? 当内表关联字段上没有索引时，不使用Index Nested-Loop Join的时候，默认使用Block Nested-Loop Join。

3、join buffer的相关概念：

待续。。。。。

4、使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on，默认为开启。

mysql8以前 的 join 算法只有 nested loop 这一种，在 MySQL8 中推出了一种新的算法 hash join，比 nested loop 更加高效。mysql8中的部分NLJ算法已经取消，hash join 是它的的替代方案。像属于NLJ的BNLJ、SNLJ都会被Hash join替代！不过基于索引的INLJ算法还是存在的，所以实际使用中可以对比下INLJ和Hash Join的查询性能然后做出选择。

个人觉得mysql8这个hash join也只能算是一个锦上添花的功能，顶多是代替了没有加索引时默认走的BNLJ算法，提高了join的性能下限。说白了就是给不懂加索引的mysql新用户提高下join性能。其实也不绝对，不过我有做 INLJ和Hash Join 对比实验，Hash Join 很有可能比需要在内部表建立索引的INLJ算法性能要好！毕竟当INLJ需要回表查的时候性能会大幅度下降，这时候Hash Join绝对值得一试的，当然具体两者之间的选择还请自己实际测试下。

创建user和book表

可以看看下列语句的执行计划，Extra 出现了 Using join buffer (hash join) 说明该语句使用到了hash join。这里还使用了 IGNORE index(index_user_id)禁用索引，不然使用的是INLJ。

那么，使用Hash Join会分为下面2个阶段：

1、build 构建阶段：从参与join的2个表中选一个，选择占空间小的那个表，不是行数少的，这里假设选择了 user 表。对 user表中每行的 join 字段值进行 hash(a.id ) 计算后放入内存中 hash table 的相应位置。所有行都存放到 hash table 之后，构建阶段完成。

溢出到磁盘在构建阶段过程中，如果内存满了，会把表中剩余数据写到磁盘上。不会只写入一个文件，会分成多个块文件。

2、probe 探测阶段：对 book 表中每行中的 join 字段的值进行 hash 计算：hash(b.user_id) 拿着计算结果到内存 hash table 中进行查找匹配，找到一行就发给 client。这样就完成了整个 join *** 作，每个表只扫描一次就可以了，扫描匹配时间也是恒定的，非常高效。

散列连接的内存使用可以使用join_buffer_size系统变量来控制；散列连接使用的内存不能超过这个数量。当散列连接所需的内存超过可用的数量时，MySQL通过使用磁盘上的文件来处理这个问题(溢出到磁盘)。

如果发生这种情况，您应该知道，如果散列连接无法容纳在内存中，并且它创建的文件超过了为open_files_limit设置的数量，则连接可能不会成功。

为避免此类问题，请执行以下任一更改:

1、增加join_buffer_size，以便哈希连接不会溢出到磁盘。

在MySQL 8.0.19及更高版本中， 设置 optimizer_switch 变量值 hash_join=on or hash_join=off 的方式已经失效了

2、增加open_files_limit。若数据量实在太大内存无法申请更大的join_buffer，就只能溢出到磁盘上了。我们可以增加open_files_limit，防止创建的文件超过了为open_files_limit设置的数量而join失败。

必须使用format=tree（8.0.16的新特性）才能查看hash join的执行计划：

创建几张测试表

从MySQL 8.0.18开始，MySQL对每个连接都有一个等连接条件的任何查询都使用散列连接，并且没有可应用于任何连接条件的索引，例如:

在MySQL 8.0.20之前，如果任何一对连接的表没有至少一个等连接条件，就不能使用Hash Join，并且使用了较慢的BNLJ。而 在MySQL 8.0.20和更高版本中，hash join可以用于未包含等值连接条件的查询

甚至是笛卡尔积的join

Semijoin也行

还有 antijoin

---