mysql分区实践_MySQL分区表实践

与分区函数使用列无关的查询条件

MySQL>EXPLAIN   PARTITIONS SELECT COUNT(*) FROMRPT_MALEVENTS WHERE ALERT_TYPE = 1\G;

***************************1. row ***************************

id: 1

select_type: SIMPLE

table: RPT_MALEVENTS

partitions: p2011,p20110809,p20110810,p20110811,p20110812,p20110813,p20110814,p20110815,p20110816,p20110817,p20110818,pMax[z6]

type: index

possible_keys: NULL

key: RECORD_DATETIME

key_len: 8

ref: NULL

rows: 17084274[z7]

Extra: Using where; Using index

1 row in set (0.00sec)

mysql> EXPLAINSELECT COUNT(*) FROM RPT_MALEVENTS WHERE ALERT_TYPE = 1\G;

***************************1. row ***************************

id: 1

select_type: SIMPLE

table: RPT_MALEVENTS

type: index

possible_keys: NULL

key: RECORD_DATETIME

key_len: 8

ref: NULL

rows: 17082459

Extra: Using where; Using index

1 row in set (0.00sec)

采用分区函数使用的列

mysql> EXPLAINPARTITIONS SELECT COUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE >'2011-08-09' AND RECORD_DATE < '2011-08-15'\G;

***************************1. row ***************************

id: 1

select_type: SIMPLE

table: RPT_MALEVENTS

partitions: p20110810,p20110811,p20110812,p20110813,p20110814,p20110815[z8]

type: range

possible_keys:PRIMARY

key: PRIMARY

key_len: 3

ref: NULL

rows: 3767081[z9]

Extra: Using where; Using index

1 row in set (0.08sec)

mysql> EXPLAINPARTITIONS SELECT COUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE >'2011-08-09' AND RECORD_DATE < '2011-08-15'\G;

***************************1. row ***************************

id: 1

select_type: SIMPLE

table: RPT_MALEVENTS

partitions: NULL

type: range

possible_keys:PRIMARY

key: PRIMARY

key_len: 3

ref: NULL

rows: 8541229[z10]

Extra: Using where; Using index

1 row in set (0.00sec)