MySQL5.5怎么查看分区表的执行策划
发布时间：2022-01-15 19:47:45 所属栏目：MySql教程 来源：互联网
导读：本篇内容介绍了MySQL5.5怎么查看分区表的执行计划的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ --创建测试表 mysql CREATE TABLE trb1 (id INT, na
|
本篇内容介绍了“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怎么查看分区表的执行计划”的内容就介绍到这里了，感谢大家的阅读。 （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
