【MySQL】不建议使用分区表

分区表简介

分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言，逻辑上只有一个表或一个索引，但是实际上这个表可能由数10个物理分区对象组成，每个分区都是一个独立的对象，可以独自处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。

从上面的说明我们可以看到，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的的封装。对分区表的请求，都会转化成对存储引擎的接口调用。所以分区对于SQL层来说是一个完全封装底层实现的黑盒子，对应用是透明的，但是如果我们从底层的文件系统来看就会发现，每一个分区表都有一个使用#分隔命名的表文件。

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

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

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

分区表本身也有一些限制，下面是其中比较重要的几点：

• 一个表最多只能有1024个分区。
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
• 分区表中无法使用外键约束。

分区表的原理

如前所述，分区表由多个相关的底层表实现，这些底层表也是由句柄对象（Handlerobject）表示，所以我们也可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

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

分区表的类型

MySQL支持多种分区表：

• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
• LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
• HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
• KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
• 复合分区/子分区：目前只支持RANGE和LIST的子分区，且子分区的类型只能为HASH和KEY。

分区的基本语法如下:

CREATE TABLE p_range (
order_date DATETIME NOT NULL
) ENGINE=InnoDB
PARTITION BY RANGE(YEAR(order_date))(
PARTITION p_0 VALUES LESS THAN (2010),
PARTITION p_1 VALUES LESS THAN (2011),
PARTITION p_2 VALUES LESS THAN (2012),
PARTITION p_other VALUES LESS THAN MAXVALUE);

按照这种分区方案，那么order_date在2010年以前（不包括2010年）的记录进入p_0分区，年份在2010年的进入p_1分区，年份在2011年的进入p_2分区，其他年份的进入p_other分区。

创建完表后，在物理存储上会看到四个分区所对应ibd文件，也就是把数据根据时间列order_date存储到对应的4个文件中：

-rw-r----- 1 mysql mysql 98304 Dec 27 06:28 p_range#P#p_0.ibd
-rw-r----- 1 mysql mysql 98304 Dec 27 06:28 p_range#P#p_1.ibd
-rw-r----- 1 mysql mysql 98304 Dec 27 06:28 p_range#P#p_2.ibd
-rw-r----- 1 mysql mysql 98304 Dec 27 06:28 p_range#P#p_other.ibd
-rw-r----- 1 mysql mysql  8572 Dec 27 06:28 p_range.frm

所以，MySQL中的分区表是把一张大表拆成了多张表，每张表有自己的索引，从逻辑上看是一张表，但物理上存储在不同文件中。

上面的创建表的SQL语句中并没有指定主键，下面我们指定主键看看会发生什么？

mysql> CREATE TABLE p_range_pk (
id int not null,
order_date DATETIME NOT NULL,
primary key(id) USING BTREE
) ENGINE=InnoDB
PARTITION BY RANGE(YEAR(order_date))(
PARTITION p_0 VALUES LESS THAN (2010),
PARTITION p_1 VALUES LESS THAN (2011),
PARTITION p_2 VALUES LESS THAN (2012),
PARTITION p_other VALUES LESS THAN MAXVALUE);

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

无论选择哪种分区函数，都要指定相关列成为分区算法的输入条件，这些列就叫“分区列”。另外，在MySQL分区表中，主键和唯一索引列也必须是分区列的一部分，不然创建分区表时会失败。

LIST分区有点类似枚举，比如：

CREATE TABLE p_list (
c1 INT,
c2 INT
)
PARTITION BY LIST(c1) (
PARTITION p0 VALUES IN (1, 4, 7),
PARTITION p1 VALUES IN (2, 5, 8));

意味着insert时，c1的值只能在[1, 4, 7, 2, 5, 8]中，不在这个范围内插入就会报错。

在上面的例子中，我们看见range和list都是整数类型分区，其实range和list也支持非整数分区，但是要结合COLUMN分区，支持整形、日期、字符串，比如：

CREATE TABLE p_range2 (
order_date DATETIME NOT NULL
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(order_date)(
PARTITION p_0 VALUES LESS THAN ('2010-01-01'),
PARTITION p_1 VALUES LESS THAN ('2011-01-01'),
PARTITION p_2 VALUES LESS THAN ('2012-01-01'),
PARTITION p_other VALUES LESS THAN MAXVALUE);

和

CREATE TABLE p_list2 (
category VARCHAR(30)
)
PARTITION BY LIST COLUMNS (category)(
PARTITION p0 VALUES IN ('a','b') ,
PARTITION p1 VALUES IN('c', 'd'),
PARTITION p2 VALUES IN('e','f'),
PARTITION p3 VALUES IN('g'),
PARTITION p4 VALUES IN('h'));

在结合COLUMN分区时还支持多列比如：

CREATE TABLE p_range3(
a INT,
b INT
)
PARTITION BY RANGE COLUMNS(a,b)(
PARTITION p01 VALUES LESS THAN (0,10),
PARTITION p02 VALUES LESS THAN (10,10),
PARTITION p03 VALUES LESS THAN (10,20),
PARTITION p04 VALUES LESS THAN (10,35),
PARTITION p05 VALUES LESS THAN (10,MAXVALUE),
PARTITION p06 VALUES LESS THAN (MAXVALUE,MAXVALUE));

Hash分区如下：

CREATE TABLE p_hash (
id INT NOT NULL
)
PARTITION BY HASH (id) PARTITIONS 4;

这里我们创建了一个基于id列HASH分区的表，表被分成了4个分区，如果我们插入的记录id=234，则234%4=2，这条记录就会保存到第二个分区。虽然我们在HASH中直接使用的id列，但是MySQL是允许基于某列值返回一个整数值的表达式或者MySQL中有效的任何函数或者其他表达式都是可以的。

Key分区语法如下：

CREATE TABLE p_key (
id INT NOT NULL
)
PARTITION BY KEY (id) PARTITIONS 4;

上面创建了一个基于id字段进行Key分区的表，表被分成了4个分区，KEY()里只允许出现表中的字段。

分区表能提升性能？

很多同学会认为，分区表是把一张大表拆分成了多张小表，所以这样MySQL数据库的性能会有大幅提升。这是错误的认识！如果你寄希望于通过分区表提升性能，那么我不建议你使用分区，因为做不到。

分区表技术不是用于提升MySQL数据库的性能，而是方便数据的管理。

但是当你使用分区之后，效果就不一样了，比如上面的表p_range，我们根据时间拆成每年一张表，这时，虽然B+树的高度从4降为了3，但是这个提升微乎其微。

除此之外，分区表还会引入新的性能问题，比如非分区列的查询。非分区列的查询，即使分区列上已经创建了索引，但因为索引是每个分区文件对应的本地索引，所以要查询每个分区。

所以，分区表设计时，务必明白你的查询条件都带有分区字段，否则会扫描所有分区的数据或索引。所以，分区表设计不解决性能问题，更多的是解决数据迁移和备份的问题。

不建议MySQL分区表

但在实际的应用中，MySQL分区表用的极少，更多的是自己分库分表。

分库分表除了支持MySQL分区表的水平切分以外，还支持垂直切分，把一个很大的库（表）的数据分到几个库（表）中，每个库（表）的结构都相同，但他们可能分布在不同的MySQL实例，甚至不同的物理机器上，以达到降低单库（表）数据量，提高访问性能的目的。

两者对比：

• 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
• 一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难
• 自己分库分表，自己掌控业务场景与访问模式，可控
• 分区表无论怎么分，都是在一台机器上，天然就有性能的上限。