锐客网

  1. 首页 > 睿知 > it技术 > >

mysql分区怎么弄 mysql分区方式

IT技术数据

Mysql分区表Partition一、背景
话说风和日丽的一天，为提高随着业务增长的大表（3510449行吧）的访问效率 ， 于是决定对表分区 ， 记录如下 。
二、实操
结合业务 ， 若干条记录会集中在一个日期，查询时也往往只查询一个日期内的数据 ， 于是选取分区字段为时间 。
创建分区 比如
CREATE TABLEmessage_all(
idint(10) NOT NULL AUTO_INCREMENT,
......
createtimedatetime 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、指定分区查

推荐阅读


上一篇：关于美的mp90js860使用方法的信息

下一篇：sqlserver建表举例，sql server中创建表