MySQL分区（Partition）实例

素颜马尾好姑娘i 2022-06-09 01:21 161阅读 0赞

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

此为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行左右。**

[RANGE]: http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html#partitioning-range
[LIST]: http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html#partitioning-list
[HASH]: http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html#partitioning-hash
[KEY]: http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html#partitioning-key
[Link 1]: http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html#partitioning-subpartitions