MySQL5.5怎么查看分区表的执行计划

本篇内容介绍了"MySQL5.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)

"MySQL5.5怎么查看分区表的执行计划"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！