mysql笔记 - 分区表 - 快车库

分区表概念

将一个表或者索引分解为多个更小、更可管理的部分

目前只支持水平分区

只支持局部分区索引

每个分区保存自己的数据与索引

分区列必须是唯一索引的一个组成部分

CREATE TABLE T1(
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY(col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

分区类型支持

RANGE
LIST
HASH
KEY
COLUMNS

RANGE分区

use test; -- 5.7以上版本应该没有这个库，可以自行创建

CREATE TABLE t_range (
     id INT PRIMARY KEY
) ENGINE=INNODB
PARTITION BY RANGE(id) ( -- 根据id划分
PARTITION p0 VALUES LESS THAN (10), -- 小于10的在p0分区
PARTITION P1 VALUES LESS THAN(20)); --大于10的在p1分区

RANGE后面必须是int类型的，如果是日期类型的需要转成int类型的，比如RANGE(year(from_date)),这样就是按年进行分区

LESS THAN (MAXVALUE)表示剩下的所有数据都插入到这个区间内

查看表结构

show create table t\G;

看到下图这样的有PARTITION选项的就表示是一张分区表

同时在数据库目录下面看到了有2个ibd文件，因为这里设置了2个分区

然后运行一些插入语句

insert into t values(1);
insert into t values(2);
insert into t values(16);
select * from t;

虽然这是一张有2个分区的分区表，但是查询的时候看到的内容还是一样的

有个小细节，如果这个时候插入一个10，是会被分配到p1分区的，可以这样查看

insert into t values(10);
explain select * from t where id = 10;

此时如果插入大于20的数据是会报错的，无法插入

insert into t values(30);

特殊数据类型NULL值

数据库不是一个二元的比较结果集(只会返回1或者0)，数据库还可以返回NULL这个不确定性的值

select NULL = NULL; -- 依旧返回NULL值
select NULL is NULL; -- 查询NULL值需要is NULL来查询

空值会导致分区有一些小小的考虑的问题，如果from_date是NULl值，会被分配到p0或者最小的分区内，因为NULL值会被作为负无穷的值(最小值)。但是推荐创建表的时候分区键为NOT NULL，创建表的时候所有字段尽量要求也是非空的。NULL值不是一件好事情。

在mysql中NULL值和空字符串不是一个意思

LIST分区

CREATE TABLE t_list(
    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));

LIST分区很简单，13579的数据会被插入到p0分区，02468的数据会被插入到p1分区，而且有a,b两个字段的时候，插入操作的时候必须对b列的数据进行插入操作。LIST简单点说也就是指定的值插入到对应的分区，而RANGE是一个范围的值插入到对应的分区。

HASH分区

CREATE TABLE t_hash(
    a INT,
    b DATETIME
)ENGINE=INNODB
PARTITION BY HASH(YEAR(b))
PARTITIONS 4; -- 4个分区

hash分区和range分区差不多，先做一次hash，然后平均的放到这4个分区里

面。hash分区相对range分区好处是数据的分布相对来说会比较平均一些。

KEY分区

CREATE TABLE t_key(
    a INT,
    b DATETIME
)ENGINE=INNODB
PARTITION BY KEY(b)
PARTITIONS 4; -- 4个分区

相对range和hash分区，key分区对分区键不需要进行整形转换，使用KEY(b)即可。

COLUMNS分区 5.5版本加持

支持所有的

整形类型(INT,SMALLINT,TINYINT,BIGINT)，FLOAT和DECIMAL不支持
日期类型(DATE,DATETIME)，其余不支持
字符串类型(CHAR,VARCHAR,BINARY,VARBINARY)，BLOB和TEXT不支持

CREATE TABLE t_columns_range(
    a INT,
    b DATETIME
) ENGINE=INNODB
    PARTITION BY RANGE|LIST COLUMNS(b)(
    PARTITION p0 VALUES LESS THAN('2009-01-01'),
    PARTITION p1 VALUES LESS THAN('2010-01-01')
);

COLUMNS函数的作用是不用在写其它的转换函数了

子分区

在分区的基础上再进行分区
也称为符合分区

允许在RANGE和LIST的分区上再进行HASH或者是KEY的子分区

CREATE TABLE ts (a INT, b DATE) ENGINE=INNODB
    PARTITION BY RANGE(YEAR(b))
    SUBPARTITION 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
    );

上面一共创建了3个分区表以及每个分区表对应的2个子分区，所以在数据文件目录下你一共会看到3*2=6个相关的分区文件

实际中子分区用得并不多

更可管理

用分区表用得最多的时候不是hash，是range然后使用日期进行分区，因为很多数据都是有日期属性的，这样就实现了“更小、更可管理”

清理数据的时候可能会用到这样的方法

delete from ts where b >= '2016-06-01' and b <= '2016-06-30';

如果表的数据过大的时候，这样的逻辑删除操作是比较耗时的而且会产生大量的日志，做出从同步的时候会有比较大的延时，所以这样清理数据可能并不是一个很好的方法

但是如果做了分区表，可以直接直接drop partition

alert table ts drop partition p0;

实际上这个操作就是直接删除了该分区表对应的数据文件，还可以通过exchange进行数据表的交换，这样数据就显得非常灵活。

但是mysql中分区表某些场景下性能可能会不好

局部分区

mysql的分区是局部分区，所有的索引只在当前分区里面的

CREATE TABLE T1(
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY(col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

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

当一张表有了唯一索引但是分区字段不是唯一索引，这里col1和col2有了唯一索引，但是想通过col3来进行分区就会报错，因为分区列必须是唯一索引的一个组成部分。

这样的例子就没问题

CREATE TABLE t1(
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    UNIQUE KEY (col1, col2)
) PARTITION BY HASH(col2) PARTITIONS 4;

原理图，mysql只能保证1在每个分区里保持唯一，不能保证在t1p1中也不存在1

性能

通常用分区表最常见的场景是根据日期来进行分区，然后对日期的这个子段进行查询、数据清理、归档。

mysql一张表多大进行分区没有这一说，一千万的表和一亿的表其实只有微小的性能差距，拆成小表的好处是管理起来比较方便，可以分别alter table。但是支持了在线的online DDL的，就不会阻塞应用，一张表多大数据就没有什么区别。当然分区还是管理比较方便的

查询每个分区的记录数量

use information_schema;
select * from partitions where TABLE_NAME='titles'\G;

使用explain来查看数据所在的分区

explain select * from t where id = 10;

一个分区的时候性能相对来说是不错的

explain select * from t where id >= 0;

用到多个分区的时候性能可能会非常糟糕

所以分区对性能有没有帮助，其实是要看你的查询方式，如果是在多个分区中进行查询，那每个分区表都要扫一下和只扫一个分区，性能会差非常多

explain查看分区直接显示只有在5.7里支持