查找mysql的分区情况_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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2114775/，如需转载，请注明出处，否则将追究法律责任。