MySQL分区表简介

我们的业务只存近一段时间的数据，因此有大量表需要清理 历史 数据，目前使用的delete清理数据，存在以下问题。为避免同时支持大量delete，我们的清理任务只在低峰期串行执行，导致任务过多时需要排队，甚至失败的情况；数据清理使用delete语句，表数据量较大时，对数据库造成很大压力；即使我们删除了旧数据，已删除的数据仍占据存储空间，底层数据文件并没有立刻变小，以至于形成数据空洞。

查看MySQL官方文档时，发现了分区表，因此基于官方文档总结一下。

MySQL逻辑上为一个表，物理上存储在多个文件中，这是 MySQL 支持的功能（5.1 开始）, 8.0 版本只 InnoDB 和 NDB 支持分区表。

优点：

缺点：

根据分区表键值的范围把数据存储到表的不同分区中，适用于以时间或日期作为分区类型，方便数据清理。

小提示：

1.当插入数据分区不存在时会报错：Table has no partition for value xxx

2.Range类型分区字段必须是数值，时间类型可用函数转换为数值；

3.分区字段列值可以为null，所有为null的数据将存在最小的分区中；

按分区键取值的列表进行分区，每一行数据须找到对应的分区列表，否则数据插入失败

小提示：

根据指定分区表达式的整数值以及分区数进行数据划分(mod函数)

小提示：

按键分区类似于按哈希分区，只是哈希分区使用用户定义的表达式，用于键分区的哈希函数由 MySQL 服务器提供。NDB 集群为此使用 MD5() 对于使用其他存储引擎的表，服务器使用自己的内部哈希函数。

小提示：

子分区(subpartitioning)也称为复合分区(composite partitioning) ，是已分区表中每个分区的进一步划分

小提示：

小提示：

一、背景

话说风和日丽的一天，为提高随着业务增长的大表（3510449行吧）的访问效率，于是决定对表分区，记录如下。

二、实 ***

结合业务，若干条记录会集中在一个日期，查询时也往往只查询一个日期内的数据，于是选取分区字段为时间。

创建分区 比如

CREATE TABLE message_all (

id int(10) NOT NULL AUTO_INCREMENT,

......

createtime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'

PRIMARY KEY ( id , createtime )

) ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE (YEAR(createtime))

(PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB,

PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB,

PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB,

PARTITION p2018 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

不过我们表已经有了当然不能这么建，除非你想导一次数据。

如下 *** 作 ：

1、

ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime))

(

PARTITION p2015 VALUES LESS THAN (to_days('2016-01-01')),

PARTITION p2016 VALUES LESS THAN (to_days('2017-01-01')),

PARTITION p2017 VALUES LESS THAN (to_days('2018-01-01')),

PARTITION p2018 VALUES LESS THAN MAXVALUE

)

或者

2、ALTER TABLE message_all PARTITION BY RANGE (YEAR(createtime))

(

PARTITION p2015 VALUES LESS THAN (YEAR('2016-01-01'))

)

然后追加。

ALTER TABLE message_all ADD PARTITION

(

PARTITION p2016 VALUES LESS THAN (YEAR('2017-01-01')),

PARTITION p2017 VALUES LESS THAN (YEAR('2018-01-01')),

PARTITION p2018 VALUES LESS THAN MAXVALUE

)

这里会有几种错误情况：

1、ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime))

[Err] 1492 - For RANGE partitions each partition must be defined

解释：必须指定至少一个分区。

2、[Err] 1492 - A PRIMARY KEY must include all columns in the table's partitioning function

解释：分区字段必须是主键之一。

3、[Err] 1492 - Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

解释：分区字段为timestamp，换成datetime。

4、[Err] 1526 - Table has no partition for value xxxx

解释：用追加方式第一次必须覆盖目前所有数据。

总结：

1、创建时必须指定至少一个分区。

2、key必须为主键之一。

3、RANGE处必须为INT型，时间字段用函数转——YEAR()、YEARWEEK()、TO_DAYS()。

4、THAN处必须为INT型，时间字段用函数转——TO_DAYS、TO_SECONDS()、UNIX_TIMESTAMP()。

5、它就是以两个INT比大小划分的文件。

6、所有ENGINE必须一样。

7、范围分区添加只能在最大值后面追加。

8、分区是有上限的貌似1024个。

用到的其他 *** 作

1、删除分区（直接扔掉分区文件，数据也没了）

ALTER TABLE message_all DROP PARTITION p2016

2、清空分区数据

ALTER TABLE message_all TRUNCATE PARTITION p2017

3、重定义（可实现：分区拆分、合并、重命名）

ALTER TABLE message_all REORGANIZE PARTITION p201601,p201602,p201603,p201604 INTO

(

PARTITION p2016012 VALUES less than(TO_DAYS('2016-03-01')),

PARTITION p2016034 VALUES less than(TO_DAYS('2016-05-01'))

)

检查/查看你的分区

1、SHOW TABLE STATUS LIKE 'message_all'

2、SELECT * FROM information_schema.partitions WHERE table_name='message_all'

3、SHOW CREATE TABLE message_all

4、EXPLAIN SELECT COUNT(1) FROM message_all WHERE createtime>= '2016-01-01' AND createtime <'2016-12-30'如果用到了分区partitions里会有显示。

5、指定分区查

SELECT COUNT(1) FROM message_all PARTITION (p2016) 表别名 WHERE ......

到这里就结束啦，土豆白。

一些概念

水平分区Partition有以下几种模式

---