博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
MySQL 5.5 查看分区表的执行计划
阅读量:2496 次
发布时间:2019-05-11

本文共 3342 字,大约阅读时间需要 11 分钟。

--创建测试表
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/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2114775/

你可能感兴趣的文章
2017-03-05 CentOS中结合Nginx部署dotnet core Web应用程序
查看>>
并发与同步、信号量与管程、生产者消费者问题
查看>>
cdoj 71 I am Lord Voldemort 水题
查看>>
UVA 12898 And Or 数学暴力
查看>>
Codeforces Round #345 (Div. 2) E. Table Compression 并查集
查看>>
第二周读书笔记《构建之法》
查看>>
注册用户
查看>>
TZC Intercommunication System
查看>>
HDU 4571 SPFA+DP
查看>>
【转】大数据之数据清洗
查看>>
1、hadoop HA分布式集群搭建
查看>>
Wget下载终极用法和15个详细的例子
查看>>
centos 创建以日期为名的文件夹
查看>>
20155305乔磊2016-2017-2《Java程序设计》第一周学习总结
查看>>
最长回文字符串计算
查看>>
JSON一些简单应用
查看>>
海量数据处理 - 10亿个数中找出最大的10000个数(top K问题)(转)
查看>>
word交叉引用公式编号时和连公式一起引用
查看>>
P1163 第K极值 - Smart Online Judge
查看>>
Unity3D 新版本场景转换
查看>>