mysql分区表

假如有一个每分钟定时执行的定时任务，一分钟的数据量是100，一天24小时，每小时60分钟

一天的数据量是24*60*100=144000，30天的数据量高达430万+，源源不断的数据使得mysql的效率骤然下降，mysql单表数据量达到千万、亿级，这时如何存储大量数据是个重要的问题，如果水平分表，此时业务涉及到时间跨度较长的情况，则多表联查是个问题，这个时候可以做mysql分区表

分区表有以下特点：
分区表个数限制最多不能超过1024
不支持外键
不支持全文索引

设计分区表有两个维度，一、以时间维度，二、以实例维度
https://www.cnblogs.com/gengwudaxia/p/5961367.html

上面链接是分区表使用

数据表create语句：

CREATE TABLE `accounting` (
`date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`nas` varchar(16) NOT NULL DEFAULT '',
`uid` varchar(20) NOT NULL DEFAULT '',
`terminal` varchar(20) DEFAULT NULL,
`client_ip` varchar(16) NOT NULL DEFAULT '',
`type` varchar(20) DEFAULT NULL,
`service` varchar(20) DEFAULT NULL,
`priv_lvl` int(2) DEFAULT NULL,
`cmd` varchar(255) DEFAULT NULL,
`elapsed_time` int(6) DEFAULT NULL,
`bytes_in` int(10) DEFAULT NULL,
`bytes_out` int(10) DEFAULT NULL,
`id` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`, `date`),                                      按月分表，date需要为primary key
KEY `date_index` (`date`),
KEY `acct_index` (`uid`),
KEY `nas_index` (`nas`),
KEY `client_index` (`client_ip`),
KEY `nas_date` (`date`,`nas`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

PARTITION BY RANGE (to_days(date)) (
PARTITION p201409 VALUES LESS THAN (to_days('2014-10-01')),
PARTITION p201410 VALUES LESS THAN (to_days('2014-11-01')),
PARTITION p201411 VALUES LESS THAN (to_days('2014-12-01')),
PARTITION p201412 VALUES LESS THAN (to_days('2015-01-01')),
PARTITION p201501 VALUES LESS THAN (to_days('2015-02-01')),
PARTITION p201502 VALUES LESS THAN (to_days('2015-03-01')),
PARTITION p201503 VALUES LESS THAN (to_days('2015-04-01')),
PARTITION p201504 VALUES LESS THAN (to_days('2015-05-01')),
PARTITION p201505 VALUES LESS THAN (to_days('2015-06-01')),
PARTITION p201506 VALUES LESS THAN (to_days('2015-07-01')),
PARTITION p201507 VALUES LESS THAN (to_days('2015-08-01')),
PARTITION p201508 VALUES LESS THAN (to_days('2015-09-01')),
PARTITION p201509 VALUES LESS THAN (to_days('2015-10-01')),
PARTITION p201510 VALUES LESS THAN (to_days('2015-11-01')),
PARTITION p201511 VALUES LESS THAN (to_days('2015-12-01')),
PARTITION p201512 VALUES LESS THAN (to_days('2016-01-01')),
PARTITION p201601 VALUES LESS THAN (to_days('2016-02-01')),
PARTITION p201602 VALUES LESS THAN (to_days('2016-03-01')),
PARTITION p201603 VALUES LESS THAN (to_days('2016-04-01')),
PARTITION p201604 VALUES LESS THAN (to_days('2016-05-01')),
PARTITION p201605 VALUES LESS THAN (to_days('2016-06-01')),
PARTITION p201606 VALUES LESS THAN (to_days('2016-07-01')),
PARTITION p201607 VALUES LESS THAN (to_days('2016-08-01')),
PARTITION p201608 VALUES LESS THAN (to_days('2016-09-01')),
PARTITION p201609 VALUES LESS THAN (to_days('2016-10-01')),
PARTITION p201610 VALUES LESS THAN (to_days('2016-11-01')),
PARTITION p201611 VALUES LESS THAN (to_days('2016-12-01')),
PARTITION p201612 VALUES LESS THAN (to_days('2017-01-01')),
PARTITION p201701 VALUES LESS THAN (to_days('2017-02-01')),
PARTITION p201702 VALUES LESS THAN (to_days('2017-03-01')),
PARTITION p201703 VALUES LESS THAN (to_days('2017-04-01')),
PARTITION p201704 VALUES LESS THAN (to_days('2017-05-01')),
PARTITION p201705 VALUES LESS THAN (to_days('2017-06-01')),
PARTITION p201706 VALUES LESS THAN (to_days('2017-07-01')),
PARTITION p201707 VALUES LESS THAN (to_days('2017-08-01')),
PARTITION p201708 VALUES LESS THAN (to_days('2017-09-01')),
PARTITION p201709 VALUES LESS THAN (to_days('2017-10-01')),
PARTITION p201710 VALUES LESS THAN (to_days('2017-11-01')),
PARTITION p201711 VALUES LESS THAN (to_days('2017-12-01')),
PARTITION p201712 VALUES LESS THAN (to_days('2018-01-01')),
PARTITION p201801 VALUES LESS THAN (to_days('2018-02-01')),
PARTITION p201802 VALUES LESS THAN (to_days('2018-03-01')),
PARTITION p201803 VALUES LESS THAN (to_days('2018-04-01')),
PARTITION p201804 VALUES LESS THAN (to_days('2018-05-01')),
PARTITION p201805 VALUES LESS THAN (to_days('2018-06-01')),
PARTITION p201806 VALUES LESS THAN (to_days('2018-07-01')),
PARTITION p201807 VALUES LESS THAN (to_days('2018-08-01')),
PARTITION p201808 VALUES LESS THAN (to_days('2018-09-01')),
PARTITION p201809 VALUES LESS THAN (to_days('2018-10-01')),
PARTITION p201810 VALUES LESS THAN (to_days('2018-11-01')),
PARTITION p201811 VALUES LESS THAN (to_days('2018-12-01')),
PARTITION p201812 VALUES LESS THAN (to_days('2019-01-01')),
PARTITION p2019 VALUES LESS THAN (MAXVALUE) )
;

 

 

 

注意：字段类型datetime，分区格式按月分配，todays

分区表不能与外键结合使用