简单测试MySQL 5.1 表分区功能_MySQL
【测试环境】
操作系统:Windows XP SP2
MySQL: 5.1.19-beta-community-nt-debug
【测试过程】
查看MySQL版本:
mysql> select version();
+--------------------------------+
| version() |
+--------------------------------+
| 5.1.19-beta-community-nt-debug |
+--------------------------------+
1 row in set (0.00 sec)
建立分区表:
mysql> create table p1 (
-> id int(10) not null auto_increment,
-> username varchar(32) default '',
-> email varchar(64) default '',
-> created datetime default '0000-00-00 00:00:00',
-> primary key (id)
-> ) engine=myisam
-> partition by range(id)(
-> partition p1 values less than (10000),
-> partition p2 values less than (20000),
-> partition p3 values less than (30000),
-> partition p4 values less than (40000),
-> partition p5 values less than (50000),
-> partition p6 values less than (60000),
-> partition p7 values less than (70000),
-> partition p8 values less than (80000),
-> partition p9 values less than (90000),
-> partition p10 values less than maxvalue
-> );
Query OK, 0 rows affected (0.08 sec)
建立不分区(普通表):
mysql> create table p2 (
-> id int(10) not null auto_increment,
-> username varchar(32) default '',
-> email varchar(64) default '',
-> created datetime default '0000-00-00 00:00:00',
-> primary key (id)
-> ) engine=myisam;
Query OK, 0 rows affected (0.00 sec)
插入数据的存储过程:
mysql> delimiter //
mysql> create procedure load_tab()
-> begin
-> declare v int default 0;
-> while v -> do
-> insert into p1 (username,email,created) values ('jack', 'jack@example.com', now());
-> set v = v + 1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
给两个表分别插入数据:
mysql> delimiter ;
mysql> call load_tab();
Query OK, 1 row affected (11.09 sec)
mysql> insert into p2 select * from p1;
Query OK, 100001 rows affected (1.30 sec)
Records: 100001 Duplicates: 0 Warnings: 0
查询结果比较:
mysql> select count(1) from p1;
+----------+
| count(1) |
+----------+
| 100001 |
+----------+
1 row in set (0.00 sec)
mysql> select count(1) from p2;
+----------+
| count(1) |
+----------+
| 100001 |
+----------+
1 row in set (0.00 sec)
mysql> select * from p1;
100001 rows in set (0.41 sec)
mysql> select * from p2;
100001 rows in set (0.42 sec)
【总结】
看来这个简单数据和在Windows平台上面,数据量太小,还无法看出采用分区表的优势,但是表分区比普通表还是要快 一点点,大数据量没有测试,因为没有安装了MySQL 5.1 的服务器,所以就简单的测试下,当然,还应该选择不同的分区方式,比如hash,混合之类的,这里只是简单的范围分区。
这个测试结果不太靠谱,姑且看看,建议需要使用的还是自己测试看看才是正道
下一篇: 优化PHP程序