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、临时关闭某个事件

Mysql分区表Partition

一、背景

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

MySQL-分区表

对底层表的封装，意味着索引也是按照分区的子表定义的，而没有全局索引。（所以即使有唯一性索引，在不同子表中可能会有重复数据）

单表数据量超大时索引失效

将单表分区成数个区域，通过分区函数，可以快速地定位到数据的区域。而且相比于索引，分区不需要额外的数据结构记录每个分区的数据，代价更低。只需要一个简单的表达式就可以指向正确的分区

可以只是用简单的分区方式存放表，不要任何索引，只要将查询定位到需要的大致数据位置，通过where条件，将需要的数据限制在少数分区中，则效率是很高的。WARNNING：查询需要扫描的分区个数限制在一个很小的数量。

如果数据有明显的“热点”，可以将热点数据单独放在一个分区，让这个分区的数据能够有机会都缓存在内存中。

如果分区表达式的值可以是NULL：第一个分区会使一个特殊分区。以partition by range year(order_date)为例，所有在order_date列为NULL或者非法值的数据都会被放到第一个分区。那么所有的查询在定位分区后都会增加扫描第一个分区。而且如果第一个分区很大的时候，查询的成本会被这个“拖油瓶”分区无情的增加。

创建一个无用的第一分区可以解决这个问题，partition p_nulls values less than (0);

对于分区列和索引列不匹配的查询，虽然查询能够使用索引，但是无法通过分区定位到目标数据的分区（也就是数据分布相对更加分散）,需要遍历每个分区内的索引，除非查询中的条件同时也包含分区条件。所以期望分区条件范围被热门查询索引所包含。

对于 范围分区 技术，需要适当限制分区的数量，否则对于大量数据批量导入的场景，选择分区的成本过高。对于大多数系统，100个左右的分区是没有问题的。

文章题目：mysql怎么建分区表 mysql建分区表语句
本文路径：http://myzitong.com/article/dojedos.html