《MySQL技术内幕：SQL编程》读书笔记 -- 分区

概述

MySQL在5.1版本时添加了对分区的支持，分区的过程是指将一个表或索引分解为多个更小的、可管理的部分。逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引由好几个物理分区构成，每个分区都是独立的对象，都可自行处理。

注意：MySQL支持的仅支持水平分区和全局分区；

水平分区：将同一表中不同行的数据分到不同的分区；

垂直分区：将同一表中不同列的数据分到不同的分区；

全局分区：一个分区即存放数据，又存放索引；

局部分区：数据存放在各个分区，索引只有一个对象。

注意：无论创建何种索引，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分，否则会报错。

一、分区类型

1、RANGE分区

RANGE分区是最常见的一种分区，适合用来根据时间段来分区（年、月、日），下面创建一个id列的RANGE分区，当id小于10时，将数据插入p0分区，当id大于等于10小于20时，将数据插入p1分区，大于等于20放在p2分区，maxvalue代表正无穷：

mysql> create table range_test(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
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into range_test values (1), (2), (11), (21);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from range_test;
+------+
| id   |
+------+
|    1 |
|    2 |
|   11 |
|   21 |
+------+
4 rows in set (0.00 sec)

接下来我们看数据的分布情况，通过下图可知，TABLE_ROWS表示分区的数据行数，和我们预想的结果一样：

mysql> select TABLE_NAME,PARTITION_NAME,TABLE_ROWS from information_schema.partitions where table_schema = database() and table_name = 'range_test' \G;
*************************** 1. row ***************************
    TABLE_NAME: range_test
PARTITION_NAME: p0
    TABLE_ROWS: 2
*************************** 2. row ***************************
    TABLE_NAME: range_test
PARTITION_NAME: p1
    TABLE_ROWS: 1
*************************** 3. row ***************************
    TABLE_NAME: range_test
PARTITION_NAME: p2
    TABLE_ROWS: 1
3 rows in set (0.00 sec)

注意：

1、对于RANGE分区，优化器YEAR()、TO_DAYS()、TO_SECONDS()和UNIX_TIMESTAMP()这类函数进行优化选择；

2、RANGE分区适用于日期列的分区，例如销售记录按月分区。

2、LIST分区

LIST分区和RANGE分区非常相似，只是分区列的值是散列的，例如：

mysql> create table list_test(id int)
    -> engine=InnoDB
    -> partition by list(id) (
    -> partition p1 values in (1,3,5,7,9),
    -> partition p2 values in (2,4,6,8,10)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into list_test values(1), (2), (3);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select TABLE_NAME,PARTITION_NAME,TABLE_ROWS from information_schema.partitions where table_schema = database() and table_name = 'list_test' \G;
*************************** 1. row ***************************
    TABLE_NAME: list_test
PARTITION_NAME: p1
    TABLE_ROWS: 2
*************************** 2. row ***************************
    TABLE_NAME: list_test
PARTITION_NAME: p2
    TABLE_ROWS: 1
2 rows in set (0.00 sec)

注意：如果插入的值不是分区的定义中，同样会报错。

3、HASH分区

HASH分区的目的是将数据均匀的分布到预先定义的各个分区中，保证分区的数据量大致一致，例如：

mysql> create table hash_test( a int, b datetime )engine=InnoDB partition by hash(YEAR(b)) partitions 4;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into hash_test values (1, '2018-10-01');
Query OK, 1 row affected (0.10 sec)

-- 通过下面查询我们可以知道 '2018-10-01' 会被hash到p2分区

mysql> select MOD(YEAR('2018-10-01'), 4);
+----------------------------+
| MOD(YEAR('2018-10-01'), 4) |
+----------------------------+
|                          2 |
+----------------------------+
1 row in set (0.00 sec)

-- 验证是否在p2分区
mysql> select TABLE_NAME,PARTITION_NAME,TABLE_ROWS from information_schema.partitions where table_schema = database() and table_name = 'hash_test' \G;
*************************** 1. row ***************************
    TABLE_NAME: hash_test
PARTITION_NAME: p0
    TABLE_ROWS: 0
*************************** 2. row ***************************
    TABLE_NAME: hash_test
PARTITION_NAME: p1
    TABLE_ROWS: 0
*************************** 3. row ***************************
    TABLE_NAME: hash_test
PARTITION_NAME: p2
    TABLE_ROWS: 1
*************************** 4. row ***************************
    TABLE_NAME: hash_test
PARTITION_NAME: p3
    TABLE_ROWS: 0
4 rows in set (0.00 sec)

MySQL还支持一种LINEAR HASH分区，其优点在于增加、删除、合并、拆分分区更加快加。

4、KEY分区

KEY分区和HASH分区相似，区别在于HASH分区使用用户自定义的函数进行分区，KEY分区使用MySQL数据库提供的函数进行分区：

mysql> create table key_test( a int, b datetime )engine=InnoDB partition by key(b) partitions 4;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into key_test values (1, '2018-10-01');
Query OK, 1 row affected (0.03 sec)

mysql> select TABLE_NAME,PARTITION_NAME,TABLE_ROWS from information_schema.partitions where table_schema = database() and table_name = 'key_test' \G;
*************************** 1. row ***************************
    TABLE_NAME: key_test
PARTITION_NAME: p0
    TABLE_ROWS: 0
*************************** 2. row ***************************
    TABLE_NAME: key_test
PARTITION_NAME: p1
    TABLE_ROWS: 1
*************************** 3. row ***************************
    TABLE_NAME: key_test
PARTITION_NAME: p2
    TABLE_ROWS: 0
*************************** 4. row ***************************
    TABLE_NAME: key_test
PARTITION_NAME: p3
    TABLE_ROWS: 0
4 rows in set (0.00 sec)

5、COLUMNS分区

前面介绍的RANGE、LIST、HASH、KEY分区的分区列只能是整型，COLUMNS分区可以直接使用非整型的数据列进行分区，另外RANGE COLUMNS分区可以对多个列的值进行分区。

COLUMNS分区支持以下分区类型：

1、所有整型类型，INT、SMALLINT、TINYINT、BIGINT，对FLOAT和DECIMAL则不支持；

2、日期类型，DATE和DATETIME，其他的日期类型则不予支持；

3、字符串类型，如CHAR、VARCHAR、BINARY、VARBINARY，对BLOB和TEXT则不支持。

例如：

mysql> create table range_columns_test( a int, b datetime )engine=InnoDB 
    -> partition by range columns(b) (
    -> partition p0 values less than ('2018-10-01'),
    -> partition p1 values less than ('2019-10-01')
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> insert into range_columns_test values (1, '2018-09-30');
Query OK, 1 row affected (0.06 sec)

mysql> select TABLE_NAME,PARTITION_NAME,TABLE_ROWS from information_schema.partitions where table_schema = database() and table_name = 'range_columns_test' \G;
*************************** 1. row ***************************
    TABLE_NAME: range_columns_test
PARTITION_NAME: p0
    TABLE_ROWS: 1
*************************** 2. row ***************************
    TABLE_NAME: range_columns_test
PARTITION_NAME: p1
    TABLE_ROWS: 0
2 rows in set (0.01 sec)

二、子分区

子分区是在已有的分区基础上再分区，需要注意以下几点：

1、每个子分区数量必须相同；

2、如果在一个分区表的任何分区上使用SUBPARTITION来明确定义任何子分区，那么就必须定义所有子分区；

3、每个SUBPARTITION子句后必须跟子分区的名字；

4、子分区的名字必须是唯一的。

例如：

mysql> create table ts ( 
    -> a int,
    -> b date
    -> ) engine=InnoDB
    -> partition by range(YEAR(b))
    -> subpartition by hash(TO_DAYS(b)) (
    -> partition p0 values less than (2017) (
    -> subpartition s0,
    -> subpartition s1
    -> ),
    -> partition p1 values less than (2018) (
    -> subpartition s2,
    -> subpartition s3
    -> )
    -> );
Query OK, 0 rows affected (0.08 sec)

三、分区中的NULL值处理

MySQL的分区中总是把NULL值视为小于任何一个值的非NULL值，这点和ORDER BY、GROUP BY一致。

1、对于RANGE分区，NULL被放在最左面的分区中；

2、对于LIST分区，NULL值必须指明哪个分区中存放NULL值，否则会报错；

3、HASH和KEY分区对NULL处理是：任何分区函数都将NULL值返回为0，所以也是保存在最左边的分区中。