Mysql 按天分区

一、表结构

CREATE TABLE `visitor_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uicode` varchar(32) NOT NULL,
  `actcode` varchar(10) DEFAULT '',
  `F` varchar(30) DEFAULT '',
  `uid` bigint(20) NOT NULL,
  `element` varchar(32) DEFAULT '',
  `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `i_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `day` int(11) DEFAULT NULL,
  `h` int(11) DEFAULT NULL,
  `i` int(11) DEFAULT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,i_time),
  KEY `idx_itimef` (`i_time`,`F`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8
partition by range(to_days(i_time))
(PARTITION p20180128 VALUES LESS THAN (to_days('2018-01-28')),
PARTITION p20180129 VALUES LESS THAN (to_days('2018-01-29')),
PARTITION p20180130 VALUES LESS THAN (to_days('2018-01-30')),
PARTITION p20180131 VALUES LESS THAN (to_days('2018-01-31')),
PARTITION p20180201 VALUES LESS THAN (to_days('2018-02-01')),
PARTITION p20180202 VALUES LESS THAN (to_days('2018-02-02')),
PARTITION p20180203 VALUES LESS THAN (to_days('2018-02-03')),
PARTITION p20180204 VALUES LESS THAN (to_days('2018-02-04')),
PARTITION p20180205 VALUES LESS THAN (to_days('2018-02-05')),
PARTITION p20180206 VALUES LESS THAN (to_days('2018-02-06')),
PARTITION pMax VALUES LESS THAN MAXVALUE);

• 分区字段不能使用timestamp，因为这个字段跟时区有关，插入的时候会校验
• 分区字段要包含在主键索引中

二、explain 分析sql，extra字段说明

• Using where: 使用了where条件

  explain select F from visitor_log where uid=1234

• Using index: 数据在索引文件中能找到不需要回表查询

  explain select F from visitor_log

• Using where;Using index: 用到了索引，并且数据在索引中不需要回表查询

  explain select F from visitor_log where i_time >= ‘2018-01-23 00:00:00’ AND i_time <= ‘2018-01-23 12:29:02’;

• Using index condition：用到了索引，部分列不在索引文件中，需要回表查询

  explain select F,uid from visitor_log where i_time >= ‘2018-01-23 00:00:00’ AND i_time <= ‘2018-01-23 12:29:02’;

• Using MRR: 将随机io变成顺序io,(意味了需要回表查询)

三、生成分区语句

function create_partiton_sql_str($days,$startDay='',$partPrefix='p'){
    if($days<=1){
        return false;
    }
    if(empty($startDay)){ //为空，从当前day开始分区
        $startDay = date('Y-m-d');
    }
    $str='partition by range(to_days(c3))'."\n".'(';
    for($i=0;$i<$days;$i++){
        $date = date('Y-m-d',strtotime("+${i} days",strtotime($startDay)));
        $pDate = $date;
        $partName = ${partPrefix}.str_replace('-','',$pDate);
        $str.="PARTITION ${partName} VALUES LESS THAN (to_days('${date}')),\n";
    }

    $str .='PARTITION pMax VALUES LESS THAN MAXVALUE)'."\n";
    echo $str;
}

create_partiton_sql_str(10);