mysql 分区表测试_MYSQL表分区测试

参考：https://blog.csdn.net/fengnp/article/details/81239286

-- 1. 创建分区表

CREATE TABLE part_tab(

c1 INT DEFAULT NULL,

c2 VARCHAR(30) DEFAULT NULL,

c3 DATE NOT NULL

) 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 DEFAULT NULL,

c2 VARCHAR(30) DEFAULT NULL,

c3 DATE NOT NULL

);

-- 3. 查询

SELECT * FROM part_tab;

SELECT * FROM no_part_tab;

-- 4. 存储过程插入数据

DROP PROCEDURE IF EXISTS load_part_tab;

DELIMITER //

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;

//

CALL load_part_tab;

INSERT INTO no_part_tab SELECT * FROM part_tab;

-- 5. 测试查询效率

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

-- 6. 通过explain语句来分析执行情况

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

转载于:https://my.oschina.net/u/2559275/blog/3068360

点赞

收藏

分享

文章举报

chizhi9661

发布了0 篇原创文章 · 获赞 0 · 访问量 232

私信

关注