MySQL 5.5 檢視分割槽表的執行計劃

--建立測試表

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)