mysql 所有二级分区 mysql分区实战

一、分区概述

分区有利于管理非常大的表，根据一定的规则，数据库把一个表分解成多个小表。逻辑上只有一个表或一个索引，实际上这个表由多个物理分区对象组成，每个分区都是一个独立的对象。分区对应用来讲是完全透明的，不影响应用的业务逻辑，

其优点主要由以下几个方面

1.可以存储更多数据。

2.优化查询。在where子句中包含分区条件时，可以只扫描必要的一个或几个分区，提高查询效率；同时在涉及SUM()和COUNT()这类聚合函数查询时，可以容易的在每个分区进行并行处理，最终只需汇总所有分区得到结果。

3.对于过期数据或不需要保存的数据可以通过删除分区进行快速删除。

4.跨多个磁盘分散数据查询，以获得更大的查询吞吐量。

二、几个常用命令

1.查询当前mysql是否支持分区。

我们查看是否支持分区时对于5.6以下的版本可以使用如下命令：

show variables like '%partition%';

而在5.6及以上用如上命令会显示empty set，但是并不是表示不支持分区，而是我们应该这样查看：

show plugins;

当看到出现以下内容，代表可以分区

2.查看某个表分区类型

select
    partition_name part,
    partition_expression expr,
    partition_description descr,
    table_rows
from information_schema.partitions where
    table_schema = schema()
    and table_name='part_tab';

会显示分区的相关数据

三、分区类型

• RANGE分区：基于连续的区间范围，把数据分配到不同区。只支持整数分区。
• LIST分区：类似RANGE分区，区别是LIST分区基于给出的枚举的值进行分区，无需按照顺序。只支持整数分区。
• HASH分区：给定分区个数，按照一个散列函数，确定数据进入哪个分区。
• KEY分区，类似HASH分区
• Columns分区：这是mysql5.5以后引入的类型，解决了RANGE，LIST分区之恶能整数分区的限制。
• 细分为：RANGR Columns；LIST Columns。 都支持整数、时间、字符串类型。

3.1RANGE分区基本语法

CREATE TABLE part_tab (
    c1 int default NULL,
    c2 varchar(30) default NULL,
    c3 date default NULL
    )engine=INNODB PARTITION BY RANGE (year(c3) )( 
        PARTITION p3 VALUES LESS THAN (2000) ,
        PARTITION p4 VALUES LESS THAN (2010) ,
        PARTITION p11 VALUES LESS THAN MAXVALUE 
    ) ;

3.2 LIST分区基本语法

CREATE TABLE part_tab (
    c1 int default NULL,
    c2 varchar(30) default NULL,
    c3 date default NULL
    )engine=INNODB PARTITION BY LIST(year(c3) )( 
        PARTITION p1 VALUES IN (1999,2000) ,
        PARTITION p2 VALUES IN (1998,2005) ,
        PARTITION p2 VALUES IN (2010) 
    ) ;

LIST分区需要把所有的列值都写出来

3.3 HASH分区基本语法

CREATE TABLE part_tab (
    c1 int default NULL,
    c2 varchar(30) default NULL,
    c3 date default NULL,
    id INT NOT NULL
    )engine=INNODB PARTITION BY HASH(id) PARITITIONS 4;

相当于对id mod4，放进分区。

 

四、RANGE分区实例

4.1创建分区表part_tab

CREATE TABLE part_tab (
    c1 int default NULL,
    c2 varchar(30) default NULL,
    c3 date default NULL
    ) engine=INNODB 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 
    ) ;

4.2创建普通表no_part_tab

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

43.生成500W测试数据

delimiter //
CREATE PROCEDURE load_part_tab()
begin
    declare v int default 0 ;
    while v < 5000000
    do
        insert into part_tabvalues (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652)) ;
        set v = v + 1 ;
    end while ;
end

delimiter ;
call load_part_tab();

这一步大概几个小时时间，生成后分区表就有了500W测试数据

复制一份数据到普通表

insert into no_part_tab select * from part_tab;

4.4测试

查询不分区表

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

查询分区表

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

我们看到速度有明显的提升。