MySQL分区总结

前提

检查是否打开分区功能

1

show variables like "%partition%"\G

为1或YES表示打开

分区表的限制

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

`MySQL`支持的分区模式

`RANGE`模式

1
2
3
4
5


create table t(id int) engine=innodb 
partition by range(id)  (
partition p0  values less than(20),
partition p1 values less than(40))
partition p2 values less than maxvalue;

其他说明:

分区表p0只能保存id值小于20。p1是大于20小于40
MAXVALUE，表示正无穷大。所有大于40的值都会放入该分区
一般RANGE分区用于DATETIME列的数据。而且只对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行了优化选择（避免全分区扫描）

`LIST`模式。基于散列模式

1
2
3
4


create table test(id int,name varchar(25)) engine=innodb 
partition by list(id) (
partition p0 values in (1,3,5,7,9),
partition p1 values in (0,2,4,6,8));  

说明:

注意，使用上面语句分区后，只能存储0-9的数字。因为只定义了这些

`HASH`模式

尽量将数据均匀地分布到预先定义的各个分区中。保证各分区的数据量大致都一样。基于将要进行哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要分割成的分区数量。

有两种创建方式

1
2
3


create table_hash(id int,name varchar(25))
partition by hash(id)
partitions 4;

或者

1
2
3


create table t_hash(a int,b datetime) engine=innodb 
partition by hash(YEAR(b))
partitions 4;

上面的语句表示根据年份HASH，分为四个表

`LINEAR HASH`

语法同HASH一样，将关键字修改为liner hash即可。优点是增加、删除、合并分区将变得更加快捷。缺点在于同HASH对比，数据没前者分布均匀。

1
2
3


create table t_linear_hash(a int,b datetime) engine=innodb
partition by linear hash(YEAR(b))
partition 4;

`Key`

Key分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区。key分区使用数据库提供的函数进行分区。

1
2
3


create table t_key(a int,b datetime) engine=innodb
partition by key(b)
partitions 4;

`COLUMNS`

前面指定分区的列值数据类型须为整型。如果不是整型需要使用函数转化，如将datetime通过YEAR()转化。COLUMNS可以直接使用非整型的数据进行分区。RANGE COLUMNS对多个列的值进行分区。

支持的数据类型多:

整型除FLOAT DECIMAL都支持
日期类型除DATE和DATETIME
其余不支持字符串除BLOB和TEXT不支持
其他支持

1
2
3
4


create table t_columns_range(a int,b datetime,d datetime) engine=innodb
partition by range columns(b,d) (
partition p0 values less than('2009-01-01','2009-01-01'),
partition p1 values less than('2010-01-01','2010-01-01');

使用分区后注意的地方:

查询语句只要使用分区的列进行过滤时，MySQL才只扫描数据所在的分区。如果过滤条件不是分区的列。还是会扫描所有分区
分区后，要在使用WHERE子句中尽量使用分区的键，避免全表扫描。只在一个分区上扫描。不然，对提升性能无帮助。
不论创建何种类型的分区，如果表中存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。

分区的管理

为分区表增加分区

增加p2分区，大于40至无穷大存入该分区表中

1

alter table t add partition(partition p2 values less than maxvalue);

删除分区

1

alter table t drop partition p0;

在查询时注意。MySQL优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这几类函数进行优化选择。在查询时，根据分区键信息，只查询指定分区。

查看分区表的相关信息

MySQL中自带的information_schema库中的PARTITIONS表。可以根据表名与TABLE_SCHEMA来过滤。TABLE_SCHEMA表示表所在的数据库。通过该表，可以查看表分区名称，以及各分区表的记录行数等信息。

1
2
3
4


select * from 
    information_schema.PARTITIONS 
where
    table_schema=database()  AND table_name="your_table_name";

table_rows列反映了每个分区中记录的数值partition_method表示分区的类型

未分区与分区之间的数据转移

语法: ALTER TABLE ....... EXCHANGE PARTITION

如果非分区表的数据为空，相当于将分区中的数据导入非分区中。若分区中的数据为空，相当于将非分区中的数据导入分区中。当两表需要相同的列定义，另AUTO_INCREMENT列将被重置。

如，将分区表test表中的数据转换至未分区的test2中。

先依test表创建一个新表，并将分区信息删除

1
2


create table test2 like test;
alter table test2 remove partitioning;

将test中的分区数据，依分区导入test2中。这里假设test有两二个分区p0,p1
1

alter table test exchange partition p0 with table test2;
两个表中只能有一个表是空的，如果两个表中都有数据，会报错。所有每个分区的数据需要放入不同的空表中。不能集中放在同一个表中。

子分区

子分区是在分区的基础上再进行分区。有时也称这种分区为复合分区。MySQL数据库允许在RANGE和LIST的分区上再进行HASH或者KEY的子分区

1
2
3
4
5
6
7


CREATE TABLE ts(a int,b data) engine=innodb
partition by range(YEAR(B)),
subpartiton by hash(TO_DAYS(b)),
subpartitions 2(
   partition p0 values less than(1990),
   partition p1 values less than(2000),
   partition p2 values less than maxvalue);

子分区建立要注意的地方：

每个子分区的数量必须相同
要在一个分区表的任何分区上使用subpartiton来明确定义任何子分区，就必须定义所有的子分区。

各种分区对`NULL`值的差异与及分区分配

`RANGE`

将NULL值视为最小值，将含用NULL的数据存放在最左边。less than 最小的分区中

`LIST`

需要在分区中明确定义可以放入NULL，否则，在存放数据时会报错

`HASH`

将含有NULL值的记录返回为0

`KEY`

同HASH方法

文章目录

前提

分区表的限制

MySQL支持的分区模式

RANGE模式

LIST模式。基于散列模式

HASH模式

LINEAR HASH

Key

COLUMNS