共计 2278 个字符，预计需要花费 6 分钟才能阅读完成。
先简单记录一下，有时间再优化分析。
CREATE TABLE `client_shop_pay_flow` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uname` varchar(45) NOT NULL DEFAULT ” COMMENT ‘uname’,
`money` double DEFAULT ‘0’ COMMENT ‘ 金额 ’,
`paycode` varchar(100) NOT NULL DEFAULT ” COMMENT ‘ 订单号 ’,
`pay_type` tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘1 云豆,2 积分 ’,
`status` tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘0 默认 1 成功 2 异常 ’,
`ctime` int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘ 创建时间 ’,
`utime` int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘ 响应时间 ’,
PRIMARY KEY (`id`,`ctime`),
KEY `pay_code` (`pay_code`),
KEY `index_uname` (`uname`),
KEY `index_utime` (`utime`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (ctime)(
PARTITION p201901 VALUES LESS THAN (unix_timestamp(‘2019-02-01’)),
PARTITION p201902 VALUES LESS THAN (unix_timestamp(‘2019-03-01’)),
PARTITION p201903 VALUES LESS THAN (unix_timestamp(‘2019-04-01’)),
PARTITION p201904 VALUES LESS THAN (unix_timestamp(‘2019-05-01’)),
PARTITION p201905 VALUES LESS THAN (unix_timestamp(‘2019-06-01’)),
PARTITION p201906 VALUES LESS THAN (unix_timestamp(‘2019-07-01’)),
PARTITION p201907 VALUES LESS THAN (unix_timestamp(‘2019-08-01’)),
PARTITION p201908 VALUES LESS THAN (unix_timestamp(‘2019-09-01’)),
PARTITION p201909 VALUES LESS THAN (unix_timestamp(‘2019-10-01’)),
PARTITION p201910 VALUES LESS THAN (unix_timestamp(‘2019-11-01’)),
PARTITION p201911 VALUES LESS THAN (unix_timestamp(‘2019-12-01’)),
PARTITION p201912 VALUES LESS THAN (unix_timestamp(‘2020-01-01’)),
PARTITION p202001 VALUES LESS THAN (unix_timestamp(‘2020-02-01’)),
PARTITION p202002 VALUES LESS THAN (unix_timestamp(‘2020-03-01’)),
PARTITION p202003 VALUES LESS THAN (unix_timestamp(‘2020-04-01’)),
PARTITION p202004 VALUES LESS THAN (unix_timestamp(‘2020-05-01’)),
PARTITION p202005 VALUES LESS THAN (unix_timestamp(‘2020-06-01’)),
PARTITION p202006 VALUES LESS THAN (unix_timestamp(‘2020-07-01’)),
PARTITION p202007 VALUES LESS THAN (unix_timestamp(‘2020-08-01’)),
PARTITION p202008 VALUES LESS THAN (unix_timestamp(‘2020-09-01’)),
PARTITION p202009 VALUES LESS THAN (unix_timestamp(‘2020-10-01’)),
PARTITION p202010 VALUES LESS THAN (unix_timestamp(‘2020-11-01’)),
PARTITION p202011 VALUES LESS THAN (unix_timestamp(‘2020-12-01’)),
PARTITION p202012 VALUES LESS THAN (unix_timestamp(‘2021-01-01′)),
PARTITION p2021 VALUES LESS THAN (MAXVALUE)
);
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name=’client_shop_pay_flow’;
