MySQL分区实验
一、准备
1、创建分区表（按日期的年份拆分）

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 );

注意最后一行，考虑到可能的最大值

2、创建未分区表

create table no_part_tab (c1 int(11) default NULL,c2 varchar(30) default NULL,c3 date default NULL) engine=myisam;

3、插入测试数据
快速插入数据使用存储——通过存储创建800万条测试数据

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

declare作用在存储begin和 end之间声明局部变量

4、执行存储

call load_part_tab();

5、将分区表数据插入未分区表

insert into no_part_tab select * from part_tab;

二、测试
1、对比两表查询性能

select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';

select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';

对比两表查询时间

2、分析查询

explain select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'

explain select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'

对比两表rows行数

3、创建索引

create index idx_of_c3 on no_part_tab (c3);

create index idx_of_c3 on part_tab (c3);

4、再次执行步骤1，对比之前的执行时间
几乎与分区速度相当

5、测试无索引字段查询

select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1996-12-31' and c2='hello';

select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1996-12-31' and c2='hello';