创建分区表
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);
Query OK, 0 rows affected (0.14 sec)
mysql> \d // #由于下面要用到存储过程,这里需要修改结束符为“//”。所谓的存储过程其实也就是众多sql语句的集合。
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
-> //
Query OK, 0 rows affected (0.04 sec)
测试一:
实验之前确保两个表里面的数据是一致的!保证实验的可比性
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.49 sec)
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 (3.94 sec)
mysql> desc 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.09 sec)
ERROR:
No query specified
mysql> desc select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G;
*************************** 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)
mysql> desc select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NO_part_tab
type: range
possible_keys: idx_c3
key: idx_c3
key_len: 4
ref: NULL
rows: 785678
Extra: Using where; Using index
1 row in set (0.16 sec)
mysql> desc 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: index
possible_keys: idx_c3
key: idx_c3
key_len: 4
ref: NULL
rows: 798458
Extra: Using where; Using index
1 row in set (0.14 sec)
ERROR:
No query specified
测试未创建索引字段
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31' and c2='hello';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (4.90 sec)
结论:可以看到如果没通过索引进行检索所耗费的时间将长于通过索引进行检索。
测试4:删除
mysql> delete from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
Query OK, 795181 rows affected (14.02 sec)
mysql> delete from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
Query OK, 795181 rows affected (15.21 sec)