MySQL按月自动创建分区表(千万级大表优化)

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。

MySQL实现分区的方式也意味着索引也是按照分区的子表定义， 没有全局索引 。

分区的意思是指将同一表中不同行的记录分配到不同的物理文件中 ，几个分区就有几个.idb文件。MySQL数据库的分区是局部分区索引，一个分区中既存了数据，又放了索引。也就是说，每个区的聚集索引和非聚集索引都放在各自区的（不同的物理文件）。

1、可以让单表 存储更多的数据 。

2、 分区表的数据更容易维护 ，可以通过删除与那些数据有关的分区，更容易删除数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等 *** 作。

3、部分查询能够从查询条件确定只落在少数分区上， 查询速度会很快 。

4、通过跨多个磁盘来分散数据查询，来 获得更大的查询吞吐量 。

要使定时事件起作用，MySQL的常量GLOBAL event_scheduler必须为on或者是1。

1、查看scheduler的当前状态：

2、修改scheduler状态为打开(0:off , 1:on)：

3、临时打开定时器(四种方法)：

4、永久生效的方法，修改配置文件my.cnf

5、临时开启某个事件

6、临时关闭某个事件

当数据表中的数据量很大时，分区带来的效率提升才会显现出来。

只有检索字段为分区字段时，分区带来的效率提升才会比较明显。因此， 分区字段的选择很重要 ，并且 业务逻辑要尽可能地根据分区字段做相应调整 （尽量使用分区字段作为查询条件）。

1、分区表对业务透明，只需要维护一个表的数据结构。

2、DML *** 作加锁仅影响 *** 作的分区，不会影响未访问分区。

3、通过分区交换快速将数据换入和换出分区表。

4、通过TRUNCATE *** 作快速清理特定分区数据。

5、通过强制分区仅访问特定分区数据，减少 *** 作影响。

6、通过大数据量分区能有效降低索引层数，提高查询性能。

1、创建表时指定分区

物理文件：

2、插入数据

3、查询 information_schema.partitions 表得到该表的分区信息

4、查询计划分析

5、添加分区

若报错：

6、修改/覆盖/合并分区

报错

所以需要添加pmax 分区，一同修改

7、删除分区

8、查询具体分区的下数据

MySQL分区的限制

一、背景

话说风和日丽的一天，为提高随着业务增长的大表（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有以下几种模式

---