mysql 進行表分割槽

seanHai發表於2019-01-04
MySql

先簡單記錄一下，有時間再優化分析。

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`;

相關文章