| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 6 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> INSERT INTO t2 SELECT * FROM t1;
Query OK, 10 rows affected (0.36 sec)
Records: 10 Duplicates: 0 Warnings: 0
再次查看数据分部状态：
mysql> SELECT * FROM t2;
+------+---------------------+
| id | date |
+------+---------------------+
| 1 | 2013-05-23 12:59:39 |
| 2 | 2013-05-23 12:59:43 |
| 3 | 2013-05-23 12:59:44 |
| 4 | 2013-07-04 19:35:45 |
| 5 | 2014-04-04 19:35:45 |
| 6 | 2014-05-04 19:35:45 |
| 7 | 2015-05-04 19:35:45 |
| 8 | 2015-05-05 19:35:45 |
| 9 | 2017-05-05 19:35:45 |
| 10 | 2018-05-05 19:35:45 |
+------+---------------------+
10 rows in set (0.00 sec)
mysql> SELECT table_name,partition_name,table_rows FROM information_schema.PARTITIONS WHERE table_schema=database() AND table_name='t2';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| t2 | p2013 | 4 |
| t2 | p2014 | 2 |
| t2 | p2015 | 2 |
| t2 | p2016 | 0 |
| t2 | p2017 | 1 |
| t2 | p2099 | 1 |
+------------+----------------+------------+
6 rows in set (0.00 sec)
查看全表扫描行数情况：
mysql> EXPLAIN SELECT * FROM t2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 11
Extra: NULL
1 row in set (0.00 sec)
进行where子句过滤后：
1234567 mysql> EXPLAIN PARTITIONS SELECT * FROM t2 WHERE date >= '2014-03-05 19:00:12' AND date <= '2016-03-05 18:45:12';
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t2 | p2014,p2015,p2016 | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
可以发现，进行分区之后没有全表扫描，扫描的分区也仅仅是在时间范围内的。