MySQL基于时间戳的分区查询

Question

我在 Solaris 10 上运行 MySQL 5.1.30。 因为我有一个表，我根据每个月将其分为 12 个。

表结构如下

mysql> desc my_events;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| event_id  | bigint(20)   | NO   | PRI | NULL    | auto_increment | 
| timestamp | timestamp    | NO   | PRI | NULL    |                | 
| object    | varchar(10)  | YES  |     | NULL    |                | 
| info      | text         | YES  |     | NULL    |                | 
+-----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_EXPRESSION, PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test11' AND PARTITION_METHOD = 'RANGE';
+----------------+------------+----------------------+-----------------------+
| PARTITION_NAME | TABLE_ROWS | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+----------------+------------+----------------------+-----------------------+
| p001           |          0 | to_days(timestamp)   | 733773                | 
| p002           |          0 | to_days(timestamp)   | 733804                | 
| p003           |      20863 | to_days(timestamp)   | 733832                | 
| p004           |     269336 | to_days(timestamp)   | 733863                | 
| p005           |    3094672 | to_days(timestamp)   | 733893                | 
| p006           |    2639348 | to_days(timestamp)   | 733924                | 
| p007           |     314010 | to_days(timestamp)   | 733954                | 
| p008           |          0 | to_days(timestamp)   | 733985                | 
| p009           |          0 | to_days(timestamp)   | 734016                | 
| p010           |          0 | to_days(timestamp)   | 734046                | 
| p011           |          0 | to_days(timestamp)   | 734077                | 
| p012           |          0 | to_days(timestamp)   | 734107                | 
+----------------+------------+----------------------+-----------------------+
12 rows in set (0.05 sec)

当我想查询特定日期范围时，

mysql> DESCRIBE PARTITIONS SELECT * FROM my_events where timestamp > '2009-03-01' and timestamp < '2009-03-30';
+----+-------------+-------------------+-------------------------------------------------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table             | partitions                                                  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------------------+-------------------------------------------------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | my_events | p001,p002,p003,p004,p005,p006,p007,p008,p009,p010,p011,p012 | ALL  | NULL          | NULL | NULL    | NULL | 6338229 | Using where | 
+----+-------------+-------------------+-------------------------------------------------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)

。当我使用 timestamp 类型作为时间戳时，它会查询所有分区。 但是，当我将时间戳类型更改为 datetime 时，它仅查询 1 个分区 (p004)。

mysql> DESCRIBE PARTITIONS SELECT * FROM my_events where timestamp > '2009-03-01' and timestamp < '2009-03-30';
+----+-------------+-------------------+------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | my_events | p004       | ALL  | NULL          | NULL | NULL    | NULL | 6338229 | Using where | 
+----+-------------+-------------------+------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

如何仅查询 1 个分区，但使用 timestamp 类型作为时间戳？

Answer 1

我刚刚研究过这个问题，简短的回答是你不能根据 http ://bugs.mysql.com/bug.php?id=24245

“修剪预计不适用于分区表
TIMESTAMP 列，您应该使用 DATETIME 或 DATE 列来代替。”

Answer 2

这是一篇好文章 http://dev.mysql .com/tech-resources/articles/testing-partitions-large-db.html

就我个人而言，我会在分区上采用 innodb 集群复合 PK 索引，直到我的数据存储要求变得愚蠢 - 真的很愚蠢