mysql怎么建分区表 mysql建分区表语句

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://scyanting.com/article/dojedos.html