--创建测试表
mysql> CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
-> PARTITION BY RANGE(id)
-> (
-> PARTITION p0 VALUES LESS THAN (3),
-> PARTITION p1 VALUES LESS THAN (7),
-> PARTITION p2 VALUES LESS THAN (9),
-> PARTITION p3 VALUES LESS THAN (11)
-> );
Query OK, 0 rows affected (0.34 sec)
mysql> INSERT INTO trb1 VALUES
-> (1, 'desk organiser', '2003-10-15'),
-> (2, 'CD player', '1993-11-05'),
-> (3, 'TV set', '1996-03-10'),
-> (4, 'bookcase', '1982-01-10'),
-> (5, 'exercise bike', '2004-05-09'),
-> (6, 'sofa', '1987-06-05'),
-> (7, 'popcorn maker', '2001-11-22'),
-> (8, 'aquarium', '1992-08-04'),
-> (9, 'study desk', '1984-09-16'),
-> (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.04 sec)
Records: 10 Duplicates: 0 Warnings: 0
--使用EXPLAIN PARTITION查看分区表的执行计划
mysql> EXPLAIN PARTITIONS SELECT * FROM trb1;
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | trb1 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 10 | |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
--EXPLAIN语句显示不出使用的分区
mysql> EXPLAIN SELECT * FROM trb1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | trb1 | ALL | NULL | NULL | NULL | NULL | 10 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | trb1 | p0,p1 | ALL | NULL | NULL | NULL | NULL | 6 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.06 sec)
mysql> ALTER TABLE trb1 ADD PRIMARY KEY (id);
Query OK, 10 rows affected (0.18 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | trb1 | p0,p1 | range | PRIMARY | PRIMARY | 4 | NULL | 4 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26506993/viewspace-2114775/,如需转载,请注明出处,否则将追究法律责任。