Mysql的InnoDB引擎-4.表（3）

表的分区类型

RANGE分区

CREATE table t (
id int
)ENGINE=INNODB
PARTITION BY RANGE(id) (
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN maxvalue);

创建分区之后，SQL的优化器会根据你的SQL进行分区的查询优化，比如你查询id为8的数据，SQL优化器就会帮你查询p0分区，不会去查询其他分区。所以这样开启分区大大提升了查询速度。但是如果你查询的数据是id>8，那么就会查询多个分区的数据，这样分区的开启意义就不是很大了，我们要根据具体的业务场景合适的选择分区。

LIST分区

list分区和range分区非常相似，只是list分区列的值是离散的，而非连续的。

CREATE TABLE T (
a int, b int
)ENGINE=INNODB
PARTITION BY LIST(b)(
PARTITION P0 VALUES IN (1,3,5,7,9),
PARTITION P1 VALUES IN (0,2,4,6,8)
);

注意：range分区和list分区一样，如果插入的时候，数据没有匹配到对应的分区，数据库会抛出异常。

Hash分区

Hash分区的目的是将数据均匀的分布在预先设定的区中，保证每个分区的数据量大致是一样的。在RANGE和LIST分区中，必须明确指定一个列值或者列值集合用来保证数据存入哪个分区，但是在HASH中，mysql自己完成了这些工作，用户所要做的只是基于将要进行哈希分区的列值指定一个列值或者表达式，以及分区的数量即可。

CREATE TABLE t_hash(
a int，b datetime
)ENGINE=INNODB
PARTITION BY HASH (YEAR(b))
PARTITIONS 4;

MySQL数据库还支持更复杂的LINEAR HASH的分区，语法只是HASH改为了 LINEAR HASH。

LINEAR HASH分区的优点在于，增加、删除、合并和拆分分区变得更加快捷，有利于处理大量数据的表，缺点在于，与使用HASH分区得到的数据分布相比，每个分区数据分布可能不均匀。

KEY分区

KEY分区和HASH分区类似，不同之处就是HASH分区是采用用户定义的函数进行分区，KEY分区使用MySQL数据库提供的函数进行分区。

CREATE TABLE t_key (
a int,
b datetime
)ENGINE=INNODB
PARTITION BY KEY(b)
PARTITIONS 4;

COLUMNS分区

可以视为是list分区和range分区的进化。columns分区可以直接使用非整形的数据进行分区，也可以使用多个列进行分区。

支持的数据类型：

• 所有整形类型：int、smallint、tinyint、bigint。decimal和float不支持
• 日期类型：date、datetime，其余日期类型不支持
• 字符串类型：char、varchar、binary、varbinary。blob和text不支持

创建多列分区:

CREATE TABLE t_column (
a int, b int, c char(3), d int
)engine=innoDB
partition by range columns (a,d,c) (
partition p0 values less than(5,10,'ggg'),
partition p1 values less than(10,20,'mmm'),
partition p2 values less than(15,30,'sss'),
partition p3 values less than(maxvalue,maxvalue,maxvalue));

子分区

子分区是在分区的基础上再进行分区，MySQL允许在range分区和list分区上载进行HASH或KEY的子分区。

子分区创建要注意以下问题：

• 每个子分区的数量必须相同
• 要在一个子分区的任务分区上使用subpartition来明确定义任何子分区，就必须定义所有子分区。
• 每一个subpartition子句必须包括子分区的一个名字。
• 每个子分区的名字必须是唯一的

NULL值插入

• range分区插入null值，会插入到最左边的分区
• list分区必须在创建分区的时候指定null值插入到哪个分区，否则会报错
• hash分区和key分区对应null的处理方式和range、list不同，任务分区函数都将null值记录返回为0

结语

分区使用需要慎重，合理的分区可能带来性能上的提升，但是分区不合理可能会导致性能的下降，比如B+树2次IO的操作由于分区过多，分区不理想，就会出现 分区数*2次的IO操作。所以合理的书写SQL，合理的使用分区是非常重要的。