MySQL 分区表实践

1、分区表

官方手册：
https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象（Handler Object）的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。所以分区表对于 SQL 层来说是一个完全封装底层实现的黑盒子，对应用是透明的，但是从底层的文件系统来看就很容易发现，每个分区表都有一个使用 # 分隔命名的表文件。

MySQL 实现分区表的方式——对底层表的封装——意味着索引页是按照分区的子表定义的，而没有全局索引。

MySQL 在创建表时使用 PARTITION BY 子句定义每个分区存放的数据。在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无须扫描所有分区。

分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起，另外，如果想一次批量删除整个分区的数据也会变得很方便。

应用场景：

表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。
分区表的数据更容易维护。分区独立优化，检查，修复等。
分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
可以使用分区表来避免某些特殊的瓶颈。例如 InnoDB 的单个索引的互斥访问、ext3 文件系统的 inode 锁竞争等。
如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。
可以阅读 MySQL 官方手册中的 “ 分区 ” 一节：

https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

分区表本身的一些限制：

一个表最多只能有 1024 个分区。
在 MySQL 5.1 中，分区表达式必须是整数，或者是返回整数的表达式。在 MySQL 5.5 中，某些场景中可以直接使用列来进行分区。
如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
分区表中无法使用外键约束。
1.1、分区表的原理
如前所述，分区表由多个相关的底层表实现，这些底层表也是由句柄对象（Handler Object）表示，所以我们也可以直接访问各个分区。

存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个完全相同的索引。

从存储引擎的角度来看，底层表和普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

分区表上的操作按照下面的操作逻辑进行：

SELECT 查询

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。

INSERT 操作

当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表。

DELETE 操作

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。

UPDATE 操作

当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL 先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

有些操作是可以支持过滤的。

例如，当删除一条记录时，MySQL 需要先找到这条记录，如果 WHERE 条件恰好和分区表达式匹配，就可以将所有不包含着条记录的分区都过滤掉。这个对 UPDATE 语句同样有效。如果是 INSERT 操作，则本身就是只命中一个分区，其他分区都会过滤掉。MySQL 先确定这条记录属于哪个分区，再将记录写入对应的底层分区表，无须对任何其他分区进行操作。

虽然每个操作都会 “ 先打开并锁住所有的底层表 ”，但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁，例如 InnoDB，则会在分区层释放对应表锁。这个加锁和解锁过程与普通 InnoDB 上的查询相似。

1.2、分区表的类型
官方手册文档 :

https://dev.mysql.com/doc/refman/5.7/en/partitioning-types.html

MySQL 支持多种分区表。

我们看到最多的是根据范围进行分区，每个分区表存储落在某个范围的记录，分区表达式可以是列，也可以是包含列的表达式。

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

PARTITION 分区子句中可以使用各种函数。但是返回必须是一个明确的整数，不能是常数。

MySQL 还支持键值、哈希和列表分区，这其中有些还支持子分区，不过我们在生产环境中很少见到。

我们还看到的一些其他的分区技术包括：

根据键值进行分区，来减少 InnoDB 的互斥量竞争。
使用数学模函数来进行分区，然后将数据轮询放入不同的分区。
假设表有一个自增的主键列 id，系统根据时间将最近的热点数据集中存放。那么必须将时间戳包含在主键当中才行，而这和主键本身的意义相矛盾。这种情况下也可以使用这样的分区表达式来实现相同的目的：HASH(id DIV 1000000) ，这将为100万数据建立一个分区。这样一方面实现了当初的分区目的，另一方面比起使用时间范围分区还避免了一个问题，就是超过一定阈值时，如果使用时间范围分区就必须新增分区。
1.3、如何使用分区表
数据量巨大，肯定不能在每次查询的说话都扫描全表。

在数据量超大的时候，B-Tree 索引就无法起作用了。除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录，如果数据量巨大，这将产生大量随机 I/O ，随之，数据库的响应时间将大到不可接收的成都。另外，索引维护（磁盘空间、I/O 操作）的代价也非常高。

理解分区时还可以将其当作索引的最初形态，以代价非常小的方式定位到需要的数据在哪一片 “ 区域 ”。在这片 “ 区域 ” 中，你可以做顺序扫描，可以建索引，还可以将数据缓存大内存，等等。因为分区无须额外的数据结构记录每个分区有哪些数据——分区不需要精确定位每条数据的位置，也就无须额外的数据结构——所以其代价非常低。只需要一个简单的表达式就可以表达每个分区存放的是什么数据。

为了保证大数据量的可扩展性，一般有下面两个策略：

全量扫描数据，不要任何索引。

可以使用简单的分区方式存放表，不要任何索引，根据分区的规则大致定位需要的数据位置。只要能够使用 WHERE 条件，将需要的数据限制在少数分区中，则效率是很高的。当然，也需要做一些简单的保证查询的响应时间能够满足需要。使用该策略假设不用讲数据完全放入到内存中，同时还假设选哟的数据全都在磁盘上，因为内存相对很小，数据很快会被挤出内存，所以缓存起不了任何作用。这个策略使用于以正常的方式访问大量数据的时候。警告：必须将查询需要扫描的分区个数限制在一个很小的数据。
索引数据，并分离热点。

如果数据有明显的 “ 热点 ”，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中。这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效地使用缓存。
仅仅知道这些还不够，MySQL 的分区表实现还有很多陷阱。

1.4、什么情况下会出问题
上面我们介绍的两个分区策略都基于两个非常重要的假设：查询都能够过滤（prunning）掉很多额外的分区、分区本身并不会带来很多额外的代价。而事实证明，这两个假设在某些情况下会有问题。

下面介绍一些可能会遇到的问题：

NULL 值会使分区过滤无效
分区列和索引列不匹配
选择分区的成本可能很高
打开并锁住所有底层表的成本可能很高
维护分区的成本可能很高
如上所述，分区表不是什么 “ 银弹 ”。下面是目前（5.5）分区实现中的一些其他限制：

所有分区都必须使用相同的存储引擎。
分区函数中可以使用的函数和表达式也有一些限制。
某些存储引擎不支持分区。
对于 MyISAM 的分区表，不能使用 LOAD INDEX INTO CACHE 操作。
对于 MyISAM 表，使用分区表时需要打开更多的文件描述符。
1.5、查询优化
引入分区给查询优化带来了一个些新的思路（同时也带来新的bug）。分区最大的有点就是优化器可以根据分区函数来过滤一些分区。根据粗粒度索引的优势，通过分区过滤通常可以让查询扫描更少的数据（在某些场景下）。

所以，对于访问分区表来说，很重要的一点是要在 WHERE 条件中带入分区列，有时候即使看似多余的也要带上，这样就可以让优化器能够过滤掉无须访问的分区。如果没有这些条件，MySQL 就需要让对应存储引擎访问这个表的所有分区，如果表非常大的话，就可能会非常慢。

使用 EXPLAIN PARTITIONS 可以观察优化器是否执行了分区过滤。

MySQL 只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值来过滤分区，即使这个表达式就是分区函数也不行。

1.6、合并表
合并表（Merge table）是一种早期的、简单的分区实现，和分区表相比有一些不同的限制，并且缺乏优化。分区表严格来说是一个逻辑上的概念，用户无法访问底层的各个分区，对用户来说分区是透明的。但是合并表允许用户单独访问各个子表。合并表是一种被淘汰的技术。

1.7 分表分库的区别

引用：
https://www.cnblogs.com/GrimMjx/p/11772033.html