时间：2021-07-01 10:21:17 帮助过：15人阅读
分区：
分区的功能不是在存储引擎层实现的。因此不只是InnoDB才支持分区。MyISAM、NDB都支持分区操作。
分区的过程是将一个表或者索引分解为多个更小、更可管理的部分。从逻辑上将，只有一个表或者索引，但是在物理上这个表或索引可能由数十个物理分区组成。
每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。
MySQL只支持水平分区，不支持垂直分区。
水平分区：将同一表中不同行的记录分配到不同的物理文件中。
垂直分区：将同一表中不同列的记录分配到不同的物理文件中。
MySQL数据库的分区是局部分区索引。一个分区中既存放了数据又存放了索引。而全局分区索引指的是数据存放在各个分区中，但是所有数据的索引放在一个对象中。MySQL暂时还不支持全局分区索引。
查看当前数据库是否启用了分区功能：
> show plugins\G partition状态是ACTIVE表示可以支持分区。
查看目前MySQL上有哪些分区表：
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME,PARTITION_METHOD,CREATE_TIME from `PARTITIONS` where PARTITION_NAME is not null ;
MySQL数据库支持以下几种类型的分区：
RANGE分区
LIST分区
HASH分区
KEY分区
如下就是创建分区的表的方式：
> create table t4 (
col1 int null,
col2 date null,
col3 int null,
col4 int null,
key (col3)
) engine=InnoDB
partition BY HASH (col3)
partitions 4; -- 划分成4个分区
-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p0.ibd
-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p1.ibd
-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p2.ibd
-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 09:59 t4#P#p3.ibd
分区类型：
1 RANGE分区：
> create table t1 (id int)
partition by range(id)(
partition p0 values less than (10),
partition p1 values less than (20));
当数据小于10的时候，插入p0分区。大于等于10小于20时候，插入p1分区。
> INSERT INTO t1 SELECT 12;
> INSERT INTO t1 SELECT 2;
表物理文件变成了下面这种：
-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 10:14 t1#P#p0.ibd
-rw-rw---- 1 mariadb mariadb 98304 2016-08-07 10:17 t1#P#p1.ibd
从表面上，看不出来到底插入到什么分区中了，可以用下面的命令查看：
> SELECT * from information_schema.PARTITIONS where table_schema=database() and table_name=‘t1‘\G
***************************[ 1. row ]***************************
TABLE_CATALOG | def
TABLE_SCHEMA | hellodb
TABLE_NAME | t1
PARTITION_NAME | p0 # 这里能看到是插入到p0分区的
SUBPARTITION_NAME | None
PARTITION_ORDINAL_POSITION | 1
SUBPARTITION_ORDINAL_POSITION | None
PARTITION_METHOD | RANGE # 这里看得出是range分区类型
SUBPARTITION_METHOD | None
PARTITION_EXPRESSION | id
SUBPARTITION_EXPRESSION | None
PARTITION_DESCRIPTION | 10
TABLE_ROWS | 1 # 这个反映了该分区(这里是p0)记录的行数量。
AVG_ROW_LENGTH | 16384
DATA_LENGTH | 16384
MAX_DATA_LENGTH | None
INDEX_LENGTH | 0
DATA_FREE | 0
CREATE_TIME | None
UPDATE_TIME | None
CHECK_TIME | None
CHECKSUM | None
PARTITION_COMMENT |
NODEGROUP | default
TABLESPACE_NAME | None
***************************[ 2. row ]***************************
TABLE_CATALOG | def
TABLE_SCHEMA | hellodb
TABLE_NAME | t1
PARTITION_NAME | p1 # 这里能看到是插入到p1分区的
SUBPARTITION_NAME | None
PARTITION_ORDINAL_POSITION | 2
SUBPARTITION_ORDINAL_POSITION | None
PARTITION_METHOD | RANGE
SUBPARTITION_METHOD | None
PARTITION_EXPRESSION | id
SUBPARTITION_EXPRESSION | None
PARTITION_DESCRIPTION | 20
TABLE_ROWS | 1
AVG_ROW_LENGTH | 16384
DATA_LENGTH | 16384
MAX_DATA_LENGTH | None
INDEX_LENGTH | 0
DATA_FREE | 0
CREATE_TIME | None
UPDATE_TIME | None
CHECK_TIME | None
CHECKSUM | None
PARTITION_COMMENT |
NODEGROUP | default
TABLESPACE_NAME | None
> INSERT INTO t1 SELECT 32; # 这个插入会报错，因为我们上面定义的分区，并不包含这个区间。
对此，要允许插入大数的话，可以修改下表：
> ALTER TABLE t1 add partition( partition p2 values less than (30)); 或者 ALTER TABLE t1 add partition( partition p2 values less than maxvalue );
这下我们插入大数值的话，也不会报错了。
RANGE分区主要用于日期列的分区，例如对于销售类的表，可以根据年来分区存放销售记录。如下面的分区表sales:
> CREATE TABLE sales (
money INT UNSIGNED NOT NULL,
`date` DATETIME
) ENGINE=INNODB
PARTITION BY RANGE (YEAR(DATE)) (
PARTITION p2008 VALUES LESS THAN (2009),
PARTITION p2009 VALUES LESS THAN (2010),
PARTITION p2010 VALUES LESS THAN (2011)
);
> INSERT INTO sales SELECT 2399,‘2008-04-20‘;
> INSERT INTO sales SELECT 6569,‘2009-01-25‘;
> INSERT INTO sales SELECT 2399,‘2010-12-20‘;
这样的话，不同年份的数据就插入到不同的分区中，便于对sales这张表进行管理。
如果要删除2008年的数据，不需要执行delete from sales where date>=‘2008-01-01‘ and date<=‘2008-12-31‘; 只要删除2008年数据所在的分区即可：
> alter table sales drop partition p2008;
分区的另一个好处是：
加快某些查询，例如我们只要查询2009年整年的销售额，如下即可：
> explain partitions select * from sales where date >=‘2009-01-01‘ and date <=‘2009-12-31‘\G
***************************[ 1. row ]***************************
id | 1
select_type | SIMPLE
table | sales
partitions | p2009 # 只去p2009这个分区去搜索
type | ALL
possible_keys | None
key | None
key_len | None
ref | None
rows | 2
Extra | Using where
最常用的就是range分区。
但是注意：如果分区键是timestamp类型的，则必须用UNIX_TIMESTAMP转换下。如下例子：
ALTER TABLE `order_his_tmp` drop primary key, add primary key(id,order_time); 去掉原先的主键，加一个带分区ID的主键。
ALTER TABLE `order_his_tmp` PARTITION BY RANGE (UNIX_TIMESTAMP (order_time)) (
PARTITION p201508 VALUES LESS THAN (UNIX_TIMESTAMP(‘2015-09-01‘)) ,
PARTITION p201509 VALUES LESS THAN (UNIX_TIMESTAMP(‘2015-10-01‘)) ,
PARTITION P201510 VALUES LESS THAN (UNIX_TIMESTAMP(‘2015-11-01‘)) ,
PARTITION P201511 VALUES LESS THAN (UNIX_TIMESTAMP(‘2015-12-01‘)) ,
PARTITION P201512 VALUES LESS THAN (UNIX_TIMESTAMP(‘2016-01-01‘)) ,
PARTITION P201601 VALUES LESS THAN (UNIX_TIMESTAMP(‘2016-02-01‘)) ,
PARTITION P201602 VALUES LESS THAN (UNIX_TIMESTAMP(‘2016-03-01‘)) );
对于分区键是DATETIME的，要用TO_DAYS()函数操作,如下例子：
> CREATE TABLE sales(
money int unsigned not null,
date datetime)
partition by range (TO_DAYS(date)) (
partition p201001 values less than (TO_DAYS(‘2010-02-01‘)),
partition p201002 values less than (TO_DAYS(‘2010-03-01‘)),
partition p201003 values less than (TO_DAYS(‘2010-04-01‘))
);
2 LIST分区【不多见】：
和range分区类似，只是分区列的值是散列的，而非连续的。
> 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 (2,4,6,8)
);
注意：list分区中使用的是VALUES IN 这种格式。
> insert into t select 3,2;
> insert into t select 2,12; 执行这行插入语句会报错，因为按照LIST(b)划分的话，12不在上述的LIST里面。
> insert into t select 3,4; 执行这行插入语句会报错，因为按照LIST(b)划分的话，4不在上述的LIST里面。
> insert into t select 3,5;
> SELECT table_name,partition_name,table_rows from information_schema.Partitions where table_name=‘t‘ and table_schema=DATABASE()\G
结果如下：
***************************[ 1. row ]***************************
table_name | t
partition_name | p0
table_rows | 1
***************************[ 2. row ]***************************
table_name | t
partition_name | p1
table_rows | 2 表示p1分区有2行数据
注意：
InnoDB和MyISAM在遇到一次性插入多条数据中出现分区未定义错误的时候处理方式是不同的。
InnoDB会把整个SQL语句当做一个事务，只要有错误，就完全不执行。而MyISAM则会将错误之前的sql都执行成功。
3 HASH分区：
HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致都是一样的。
在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中。在HASH分区中，MySQL自动完成这些工作，用于所要做的只是基于将要进行哈希分区的列值指定一个列值或表达式，以及指定备份去的表将要被分割成的分区数量。
要使用HASH分区来分割一个表，要在CREATE TABLE语句上添加一个 PARTITION BY HASH(expr) 子句，其中expr是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的列名。
此外，用户可以自定义分区的数量，只要加上参数PARTITIONS xxx即可。如下：
> create table t_hash (
a int,
b datetime
) engine innodb
PARTITION BY HASH (YEAR(b))
PARTITIONS 4;
> INSERT INTO t_hash VALUES (6,‘2013-03-11 12:21:22‘);
上面这条插入的话，实际上要执行MOD(2013,4)取余数，得出这行数据存放在哪个分区中。
> SELECT table_name,partition_name,table_rows FROM information_schema.`PARTITIONS` WHERE table_schema=DATABASE() AND table_name=‘t_hash‘ ;
结果如下：
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| t_hash | p0 | 0 |
| t_hash | p1 | 1 | # 可以看到p1插入了4条数据，因为2013年除以4，余数是1。因此2013年都数据都落在p1分区
| t_hash | p2 | 0 |
| t_hash | p3 | 0 |
+------------+----------------+------------+
4 LINEAR HASH分区：(线性hash)
mysql还支持这种复杂的分区算法。语法和HASH分区类似，只是将关键字HASH改成了LINEAR HASH。
如下：
> CREATE TABLE t_l_hash (
a INT,
b DATETIME
) ENGINE INNODB
PARTITION BY LINEAR HASH(YEAR(b))
PARTITIONS 4;
取大于分区数量4的下一个2的幂值V，V=POWER(2，CEILING(LOG(2，num)))=4
所在分区N=YEAR(‘2010-04-01‘)&(V-1)=2.
LINEAR HASH分区的
优点：
增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有大量数据的表。
缺点：
与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡。
> INSERT INTO t_l_hash VALUE (2,‘2014-04-23 12:23:33‘);
> SELECT table_name,partition_name,table_rows
FROM information_schema.`PARTITIONS`
WHERE table_schema=DATABASE() AND table_name=‘t_l_hash‘\G
结果如下：
***************************[ 1. row ]***************************
table_name | t_l_hash
partition_name | p0
table_rows | 1
***************************[ 2. row ]***************************
table_name | t_l_hash
partition_name | p1
table_rows | 0
***************************[ 3. row ]***************************
table_name | t_l_hash
partition_name | p2
table_rows | 1
***************************[ 4. row ]***************************
table_name | t_l_hash
partition_name | p3
table_rows | 0
5 KEY分区：
和HASH分区类似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用MySQL数据库提供的函数进行分区。
对于NDB Cluster引擎，MySQL数据库使用MD5函数来分区；对于其他引擎，使用MySQL内部的哈希函数来分区。
> CREATE TABLE t_key (
a INT,
b DATETIME
) ENGINE INNODB
PARTITION BY KEY(b)
PARTITIONS 4 ;
6 COLUMNS分区【很常用】：
前面的几种分区都是有条件限制的。条件是：必须是整型，如果不是整型，那么也必须是可以通过函数转换为整型的，如YEAR()、TO_DAYS()、MONTH()等函数。
MySQL5.5版本开始支持的COLUMNS分区，可视为RANGE分区和LIST分区的一种进化。
COLUMNS分区看直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转换为整型。
此外，COLUMNS分区可以对多个列的值进行分区。
COLUMNS分区支持以下的数据类型：