如何按datetime列对表进行分区？

Question

我想对mysql的表按datetime列进行分区，一天一个分区，创建表的脚本如下：CREATE TABLE raw_log_2011_4 (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  logid char(16) NOT NULL,
  tid char(16) NOT NULL,
  reporterip char(46) DEFAULT NULL,
  ftime datetime DEFAULT NULL,
  KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=286802795 DEFAULT CHARSET=utf8
PARTITION BY hash (day(ftime)) partitions 31;
但是当我查询一天的数据的时候，查询计划并不能直接定位分区。我的查询语句如下：explain partitions select * from raw_log_2011_4 where day(ftime) = 30;换了好多查询语句都不行。当我使用如下的语句，可以直接定位分区，但是不能查询一天的数据。explain partitions select * from raw_log_2011_4 where ftime = '2011-03-30';是否有方法能让我既能查询数据又可以利用分区，多谢！

Answer 1

explain partitions select * from raw_log_2011_4 where day(ftime) =30;
结果如何

Answer 2

全表扫描。|  1 | SIMPLE      | raw_log_2011_4 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30 | ALL  | NULL          | NULL | NULL    | NULL | 1000498 | Using where |

Answer 3

把你的day(ftime)换成to_days(ftime)
mysql只认识to_days(), year()两个分区函数

Answer 4

mysql> CREATE TABLE raw_log_2011_5 (
    ->   id bigint(20) NOT NULL AUTO_INCREMENT,
    ->   logid char(16) NOT NULL,
    ->   tid char(16) NOT NULL,
    ->   reporterip char(46) DEFAULT NULL,
    ->   ftime datetime DEFAULT NULL,
    ->   KEY id (id)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=286802795 DEFAULT CHARSET=utf8
    -> PARTITION BY hash (to_days(ftime)) partitions 31;
Query OK, 0 rows affected (0.89 sec)mysql> explain partitions select * from raw_log_2011_5 where ftime = to_days('20
11-03-30')\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: raw_log_2011_5
   partitions: p8
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)ERROR:
No query specified

Answer 5

使用to_days也不行。CREATE TABLE raw_log_2011_4 (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  logid char(16) NOT NULL,
  tid char(16) NOT NULL,
  reporterip char(46) DEFAULT NULL,
  ftime datetime DEFAULT NULL,
  KEY id (id)
) ENGINE=InnoDB AUTO_INCREMENT=286802795 DEFAULT CHARSET=utf8
PARTITION BY hash (to_days(ftime)) partitions 31;explain partitions select * from raw_log_2011_4 where to_days(ftime) = to_days('2011-03-30');依旧是全表扫描。

Answer 6

记录有10多亿吧。注意ftime是datetime类型。

Answer 7

CREATE TABLE `raw_log_2011_4` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `logid` char(16) NOT NULL,
  `tid` char(16) NOT NULL,
  `reporterip` char(46) DEFAULT NULL,
  `ftime` datetime DEFAULT NULL,
  KEY `id` (`id`),
  KEY `ftime` (`ftime`)
) ENGINE=InnoDB AUTO_INCREMENT=286802799 DEFAULT CHARSET=utf8
PARTITION BY HASH (to_days(ftime))
PARTITIONS 31 
这样试试

Answer 8

http://blog.chinaunix.net/space.php?uid=24086995&do=blog&id=127389看看这个，自动维护分区的。

Answer 9

explain partitions select * from raw_log_2011_4 where ftime = to_days('2011-03-30');