Mysql 日志表按照时间分区

1 典型的时间分区

drop table pss_log;
CREATE TABLE `pss_log` (
  `id` bigint(18) NOT NULL AUTO_INCREMENT,
  `method_name` varchar(100) NOT NULL COMMENT '方法名',
  `input` text COMMENT '方法输入',
  `output` text COMMENT '方法输出',
  `client_ip` varchar(16) DEFAULT NULL COMMENT '调用ip',
  `server_ip` varchar(16) DEFAULT NULL COMMENT '服务器ip',
  `invoked_by` varchar(80) DEFAULT NULL COMMENT '调用方',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `ticket` varchar(36) NOT NULL COMMENT '调用id',
  `result_code` varchar(10) DEFAULT NULL COMMENT '方法结果码,用于表示是否成功',
  `version` varchar(20) DEFAULT NULL COMMENT '客户端调用版本',
  `time_used` int(10) DEFAULT NULL COMMENT '服务端耗时',
  PRIMARY KEY (`id`,`create_time`),
  KEY `idx_pss_log_create_time` (`method_name`,`create_time`,`invoked_by`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE(UNIX_TIMESTAMP(`create_time`))
(PARTITION  P20140701 VALUES LESS THAN (UNIX_TIMESTAMP('2014-07-01 00:00:00'))  ENGINE = InnoDB,
PARTITION  P20140801 VALUES LESS THAN (UNIX_TIMESTAMP('2014-08-01 00:00:00'))  ENGINE = InnoDB,
PARTITION  P20140901 VALUES LESS THAN (UNIX_TIMESTAMP('2014-09-01 00:00:00'))  ENGINE = InnoDB,
PARTITION  P20141001 VALUES LESS THAN (UNIX_TIMESTAMP('2014-10-01 00:00:00'))  ENGINE = InnoDB,
PARTITION  P20141101 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-01 00:00:00'))  ENGINE = InnoDB,
PARTITION  P20141201 VALUES LESS THAN (UNIX_TIMESTAMP('2014-12-01 00:00:00'))  ENGINE = InnoDB,
PARTITION  P20150101 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00'))  ENGINE = InnoDB,
PARTITION  P20150201 VALUES LESS THAN (UNIX_TIMESTAMP('2015-02-01 00:00:00'))  ENGINE = InnoDB,
PARTITION  P20150301 VALUES LESS THAN (UNIX_TIMESTAMP('2015-03-01 00:00:00'))  ENGINE = InnoDB,
PARTITION  P20150401 VALUES LESS THAN (UNIX_TIMESTAMP('2015-04-01 00:00:00'))  ENGINE = InnoDB,
PARTITION  P20150501 VALUES LESS THAN (UNIX_TIMESTAMP('2015-05-01 00:00:00'))  ENGINE = InnoDB,
PARTITION  P20150601 VALUES LESS THAN (UNIX_TIMESTAMP('2015-06-01 00:00:00'))  ENGINE = InnoDB,
PARTITION  P20150701 VALUES LESS THAN (UNIX_TIMESTAMP('2015-07-01 00:00:00'))  ENGINE = InnoDB
);

2 检验分区

insert into pss_log (method_name,create_time,ticket) values('clyde1',  '2014-06-01 00:00:00' ,'ticket1');
insert into pss_log (method_name,create_time,ticket) values( 'clyde2', '2014-06-02 00:00:00' ,'ticket1');

explain PARTITIONS  select * from pss_log where CREATE_TIME<'2014-07-05 00:00:00' 

从执行计划可以看出, 由于create_time<'2014-07-05 00:00:00' ,索引只会在2个分区P20140701,P20140801中查找,但用的2个partition的全表扫描

explain  select * from pss_log where CREATE_TIME<'2014-07-05 00:00:00' 

id	select_type	partitions	type	possible_keys	key	rows	Extra
1	SIMPLE	P20140701,P20140801	ALL	NULL	NULL	1	Using where

where 语句中增加method_name条件,会用到了索引

explain   partitions  select * from pss_log where  method_name='clyde1' and  CREATE_TIME<'2014-07-05 00:00:00' 

id	select_type	partitions	type	possible_keys	key	rows	Extra
1	SIMPLE	P20140701,P20140801	range	idx_pss_log_create_time	idx_pss_log_create_time	1	Using where

如果时间范围更小的话,就会只在一个分区中找

explain   partitions  select * from pss_log where  method_name='clyde1' and  CREATE_TIME<'2014-07-05 00:00:00'  and create_time>'2014-07-02 00:00:00';

id	select_type	partitions	type	possible_keys	key	rows	Extra
1	SIMPLE	P20140801	range	idx_pss_log_create_time	idx_pss_log_create_time	1	Using where

3 另外条件允许的话,partitions分得越小越好,