MySQL分区(Partition)功能
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL分区(Partition)功能,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5289字,纯文字阅读大概需要8分钟。
内容图文
![MySQL分区(Partition)功能](/upload/InfoBanner/zyjiaocheng/498/b295f20ad9454f7598349e1afd3a529b.jpg)
- mysql> CREATE TABLE part_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=myisam
- PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
- PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
- PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
- PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
- PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
- PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
- PARTITION p11 VALUES LESS THAN MAXVALUE );
注意最后一行,考虑到可能的最大值
*创建未分区表
- mysql> create table no_part_tab (c1 int(11) default NULL,c2 varchar(30) default NULL,c3 date default NULL) engine=myisam;
*通过存储过程灌入800万条测试数据
mysql> set sql_mode=‘‘; /* 如果创建存储过程失败,则先需设置此变量, bug? */
mysql> delimiter // /* 设定语句终结符为 //,因存储过程语句用;结束 */
[sql] view plain copy
- mysql> CREATE PROCEDURE load_part_tab()
- begin
- declare v int default 0;
- while v < 8000000
- do
- insert into part_tab
- values (v,‘testing partitions‘,adddate(‘1995-01-01‘,(rand(v)*36520) mod 3652));
- set v = v + 1;
- end while;
- end
- //
- mysql> delimiter ;
- mysql> call load_part_tab();
Query OK, 1 row affected (8 min 17.75 sec)
- mysql> insert into no_part_tab select * from part_tab;
Query OK, 8000000 rows affected (51.59 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
* 测试SQL性能
[sql] view plain copy
- mysql> select count(*) from part_tab where c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘;
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.55 sec)
[sql] view plain copy
- mysql> select count(*) from no_part_tab where c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘;
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (4.69 sec)
结果表明分区表比未分区表的执行时间少90%。
* 通过explain语句来分析执行情况
- mysql > explain select count(*) from no_part_tab where c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘\G
/* 结尾的\G使得mysql的输出改为列模式 */
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: no_part_tab
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8000000
Extra: Using where
1 row in set (0.00 sec)
[sql] view plain copy
- mysql> explain select count(*) from part_tab where c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: part_tab
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 798458
Extra: Using where
1 row in set (0.00 sec)
explain语句显示了SQL查询要处理的记录数目
* 试验创建索引后情况
- mysql> create index idx_of_c3 on no_part_tab (c3);
Query OK, 8000000 rows affected (1 min 18.08 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
- mysql> create index idx_of_c3 on part_tab (c3);
Query OK, 8000000 rows affected (1 min 19.19 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
创建索引后的数据库文件大小列表:
2008-05-24 09:23 8,608 no_part_tab.frm
2008-05-24 09:24 255,999,996 no_part_tab.MYD
2008-05-24 09:24 81,611,776 no_part_tab.MYI
2008-05-24 09:25 0 part_tab#P#p0.MYD
2008-05-24 09:26 1,024 part_tab#P#p0.MYI
2008-05-24 09:26 25,550,656 part_tab#P#p1.MYD
2008-05-24 09:26 8,148,992 part_tab#P#p1.MYI
2008-05-24 09:26 25,620,192 part_tab#P#p10.MYD
2008-05-24 09:26 8,170,496 part_tab#P#p10.MYI
2008-05-24 09:25 0 part_tab#P#p11.MYD
2008-05-24 09:26 1,024 part_tab#P#p11.MYI
2008-05-24 09:26 25,656,512 part_tab#P#p2.MYD
2008-05-24 09:26 8,181,760 part_tab#P#p2.MYI
2008-05-24 09:26 25,586,880 part_tab#P#p3.MYD
2008-05-24 09:26 8,160,256 part_tab#P#p3.MYI
2008-05-24 09:26 25,585,696 part_tab#P#p4.MYD
2008-05-24 09:26 8,159,232 part_tab#P#p4.MYI
2008-05-24 09:26 25,585,216 part_tab#P#p5.MYD
2008-05-24 09:26 8,159,232 part_tab#P#p5.MYI
2008-05-24 09:26 25,655,740 part_tab#P#p6.MYD
2008-05-24 09:26 8,181,760 part_tab#P#p6.MYI
2008-05-24 09:26 25,586,528 part_tab#P#p7.MYD
2008-05-24 09:26 8,160,256 part_tab#P#p7.MYI
2008-05-24 09:26 25,586,752 part_tab#P#p8.MYD
2008-05-24 09:26 8,160,256 part_tab#P#p8.MYI
2008-05-24 09:26 25,585,824 part_tab#P#p9.MYD
2008-05-24 09:26 8,159,232 part_tab#P#p9.MYI
2008-05-24 09:25 8,608 part_tab.frm
2008-05-24 09:25 68 part_tab.par
* 再次测试SQL性能
[sql] view plain copy
- mysql> select count(*) from no_part_tab where c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘;
<span style="font-family: "微软雅黑&qu 本文系统来源:https://www.cnblogs.com/gzyx1988/p/8320018.html
内容总结
以上是互联网集市为您收集整理的MySQL分区(Partition)功能全部内容,希望文章能够帮你解决MySQL分区(Partition)功能所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。