KEY `RECORD_DATETIME` (`RECORD_DATETIME`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci /*!50100 PARTITIONBY RANGE (TO_DAYS(RECORD_DATE)[z1] ) (PARTITION p2011 VALUES LESS THAN (734503)ENGINE = InnoDB, PARTITION p20110809 VALUES LESS THAN (734724) ENGINE = InnoDB,PARTITION p20110810 VALUES LESS THAN (734725) ENGINE = InnoDB, PARTITIONp20110811 VALUES LESS THAN (734726) ENGINE = InnoDB, PARTITION p20110812 VALUESLESS THAN (734727) ENGINE = InnoDB, PARTITION p20110813 VALUES LESS THAN(734728) ENGINE = InnoDB, PARTITION p20110814 VALUES LESS THAN (734729) ENGINE= InnoDB, PARTITION p20110815 VALUES LESS THAN (734730) ENGINE = InnoDB,PARTITION p20110816 VALUES LESS THAN (734731) ENGINE = InnoDB, PARTITIONp20110817 VALUES LESS THAN (734732) ENGINE = InnoDB, PARTITION p20110818 VALUESLESS THAN (734733) ENGINE = InnoDB, PARTITION pMax VALUES LESS THAN MAXVALUE[z2] ENGINE = InnoDB)
分区表的物理存储如下，当前用的是innodB的存储引擎，采用分表结构
分析如下
(条件查询查询全部数据)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-01' AND RECORD_DATE< '2011-08-19';
+----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (21.62sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-01' AND RECORD_DATE< '2011-08-19';
+----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (29.20sec)
(查询部分数据，不使用分区函数使用的列)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-02' ANDRECORD_DATETIME < '2011-08-11';
+----------+
| COUNT(*) |
+----------+
| 5083194 |
+----------+
1 row in set (2.83sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-02' AND RECORD_DATETIME< '2011-08-11';
+----------+
| COUNT(*) |
+----------+
| 5083194 |
+----------+
1 row in set (5.60sec)
(使用其他条件查询部分数据)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE ALERT_TYPE = 1;
+----------+
| COUNT(*) |
+----------+
| 88739 |
+----------+
1 row in set (8.49sec)
SELECT COUNT(*) FROMRPT_MALEVENTS WHERE ALERT_TYPE = 1;
+----------+
| COUNT(*) |
+----------+
+----------+
1 row in set (12.88sec)
(小范围查询,在一个分区内查询)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-13' AND RECORD_DATE< '2011-08-15';
+----------+
| COUNT(*) |
+----------+
| 2116249 |
+----------+
1 row in set (1.85sec)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-13' AND RECORD_DATE< '2011-08-15';
+----------+
| COUNT(*) |
+----------+
| 2116249 |
+----------+
1 row in set (3.10sec)
分析SQL语句的执行过程
rows表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。
mysql>EXPLAIN PARTITIONS SELECT * FROMRPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-12' AND RECORD_DATETIME <'2011-08-13' LIMIT 1\G;
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
partitions: p2011,p20110809,p20110810,p20110811,p20110812,p20110813,p20110814,p20110815,p2011[z3] 0816,p20110817,p20110818,pMax
type: range
possible_keys:RECORD_DATETIME
key: RECORD_DATETIME
key_len: 8
ref: NULL
rows: 355911[z4]
Extra: Using where
1 row in set (0.00sec)
mysql> EXPLAIN SELECT * FROM RPT_MALEVENTS WHERERECORD_DATETIME > '2011-08-12' AND RECORD_DATETIME < '2011-08-13' LIMIT1\G;
***************************1. row ***************************
id: 1
select_type: SIMPLE
table: RPT_MALEVENTS
type: range
possible_keys:RECORD_DATETIME
key: RECORD_DATETIME
key_len: 8
ref: NULL