Mysql分区表Demo

知识点

存储过程参考博客：https://www.cnblogs.com/nnngu/p/8463399.html
分区表参考博客：https://www.cnblogs.com/duanxz/p/3825155.html
   　　　　　　https://www.cnblogs.com/pejsidney/p/10074980.html

　#查看数据目录
　　show VARIABLES like '%datadir%';　

　　#查看分区的sql执行
　　explain partitons select xxxxx

　　#根据时间进行数据统计
　　https://www.cnblogs.com/applelife/p/10855395.html

　　#分区表使用
　　https://blog.csdn.net/an74520/article/details/83273101

1、创建分区表

CREATE TABLE part_tab( 
c1 int NOT 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、创建存储过程

##存储过程
CREATE PROCEDURE load_part_tab()
    begin
    declare v int default 0;
    while v < 8000000
    do
        insert into part_tab
        values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));
        set v = v + 1;
    end while;
end;

4、调用存储过程

call  load_part_tab();

5、将分区表数据拉取至普通表

insert into no_part_tab  select * from part_tab;

6、分别查询分区表和普通表，查看性能

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

Mysql分区表Demo

相关推荐