1, Table structure

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

• Timestamps cannot be used for the partition field, because this field is related to the time zone and will be verified when inserting
• Partition fields to be included in the primary key index

2, explain analysis sql, extra field description

• Using where: where condition used

  explain select F from visitor_log where uid=1234

• Using index: the data can be found in the index file. No need to query back to the table

  explain select F from visitor_log

• Using where;Using index: the index is used, and the data does not need to be returned to the table for query in the 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: index is used, some columns are not in the index file, and need to be returned to the table for query

  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: change random io to sequential io, (which means that the query back to the table is needed)

3, Generate partition statement

function create_partiton_sql_str($days,$startDay='',$partPrefix='p'){
    if($days<=1){
        return false;
    }
    if(empty($startDay)){ //Empty, partition from current 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);