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