本文介绍了如何使用MySQL的PARTITION BY RANGE (TO_DAYS(date))对表进行分区，以`ClientActionTrack`表为例，展示了如何根据`startTime`的日期创建分区，并通过示例查询解释了不同分区的查询效率和范围。
to_days()
Given a date date, returns a day number (the number of days since year 0).
给定一个date 日期,返回天数
Create Table: CREATE TABLE `ClientActionTrack` (
`sn` bigint(20) NOT NULL AUTO_INCREMENT,
`clientSn` int(11) DEFAULT NULL,
`ip` varchar(32) DEFAULT NULL,
`url` varchar(1000) DEFAULT NULL COMMENT 'request url',
`httpMethod` varchar(100) DEFAULT NULL COMMENT 'http method',
`requestParams` text COMMENT '请求参数',
`requestHeader` varchar(2000) DEFAULT NULL COMMENT '请求头信息',
`pageUrl` varchar(500) DEFAULT NULL COMMENT '页面Url',
`sessionId` varchar(100) DEFAULT NULL COMMENT 'session的Id',
`startTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '访问开始时间',
`completeTime` datetime DEFAULT NULL COMMENT '访问完成时间',
PRIMARY KEY (`sn`,`startTime`)
) ENGINE=InnoDB AUTO_INCREMENT=4424745 DEFAULT CHARSET=utf8 COMMENT='用户访问记录表'
/*!50100 PARTITION BY RANGE (TO_DAYS(startTime))
(PARTITION p20151204 VALUES LESS THAN (736302) ENGINE = InnoDB,
PARTITION p20151205 VALUES LESS THAN (736303) ENGINE = InnoDB,
PARTITION p20151206 VALUES LESS THAN (736304) ENGINE = InnoDB,
PARTITION p20151207 VALUES LESS THAN (736305) ENGINE = InnoDB,
PARTITION p20151208 VALUES LESS THAN (736306) ENGINE = InnoDB,
PARTITION p20151209 VALUES LESS THAN (736307) ENGINE = InnoDB,
PARTITION p20160824 VALUES LESS THAN (736566) ENGINE = InnoDB, ##小于'2016-08-25 23:59:47'
PARTITION p20160825 VALUES LESS THAN (736567) ENGINE = InnoDB,
PARTITION p20160826 VALUES LESS THAN (736568) ENGINE = InnoDB,
PARTITION p20160827 VALUES LESS THAN (736569) ENGINE = InnoDB,
mysql> select TO_DAYS('2016-08-25 23:59:47') from Client limit 1;
+--------------------------------+
| TO_DAYS('2016-08-25 23:59:47') |
+--------------------------------+
| 736566 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select TO_DAYS('2016-08-26 23:59:47') from Client limit 1;
+--------------------------------+
| TO_DAYS('2016-08-26 23:59:47') |
+--------------------------------+
| 736567 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT min(startTime),max(startTime) from ClientActionTrack PARTITION (p20160825);
+---------------------+---------------------+
| min(startTime) | max(startTime) |
+---------------------+---------------------+
| 2016-08-25 00:04:57 | 2016-08-25 23:59:47 |
+---------------------+---------------------+
1 row in set (0.03 sec)
mysql> select min(startTime),max(startTime) from ClientActionTrack where startTime>'2016-08-25 00:00:00' and startTime
+---------------------+---------------------+
| min(startTime) | max(startTime) |
+---------------------+---------------------+
| 2016-08-25 00:04:57 | 2016-08-25 23:57:57 |
+---------------------+---------------------+
1 row in set (0.03 sec)
860
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
