MySQL分区（Partition）实例

mysql分区有以下5种，本实例中为range分区。 RANGE分区 LIST分区 HASH分区 KEY分区 子分区

此为MySQL5.1新特性,通过MySQL分区（Partition）实例,你可以体验它带来效率上的提升。

以下命令可以知道你的数据库是否支持

SHOW VARIABLES LIKE '%partition%';

have_partition_engine 为 yes，表示有分区功能。

1,建表,

part_tab有分区表

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

no_part_tab无分区表

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

2,插入数据:

利用存储过程load_part_tab(),它能向表中平均的向每个分区插入共8百万条不同的数据

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `load_part_tab`$$

CREATE DEFINER=`root`@`localhost` 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$$

DELIMITER ;

call load_part_tab();

//这里大约用了7分钟，

将数据转入no_part_tab表，

insert into no_part_tab select * from part_tab;

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

3，现在可以体验查询了，

首先是无分区表

SELECT COUNT(*) FROM no_part_tab WHERE
c3 > DATE '1995-01-01' AND c3 < DATE '1995-12-31';

//大约用时3秒,

然后是有分区表

SELECT COUNT(*) FROM part_tab WHERE
c3 > DATE '1995-01-01' AND c3 < DATE '1995-12-31';

//大约用时0.3秒

这时你应该可以感受到分区带来的效率上的提升。

4,用explain命令来分析查询，

EXPLAIN SELECT COUNT(*) FROM no_part_tab WHERE
c3 > DATE '1995-01-01' AND c3 < DATE '1995-12-31';

分析显示，查询no_part_tab，扫描了800,0000行，

EXPLAIN PARTITIONS SELECT COUNT(*) FROM part_tab WHERE
c3 > DATE '1995-01-01' AND c3 < DATE '1995-12-31';

而part_tab表，只扫描了80，0000行左右。