一、 mysql分区简介 

        数据库分区 

        数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于应用来说分区完全是透明的。 

        MYSQL的分区主要有两种形式:水平分区和垂直分区 

        水平分区(Horizontal Partitioning) 

        这种形式的分区是对根据表的行进行分区，通过这样的方式不同分组里面的物理列分割的数据集得以组合，从而进行个体分割(单分区)或集体分割(1个或多个分区)。 所有在表中定义的列在每个数据集中都能找到，所以表的特性依然得以保持。水平分区一定要通过某个属性列来分割。常见的比如年份，日期等。 

        垂直分区(Vertical Partitioning) 

        这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个分区都包含了其中的列所对应所有行。 

        可以用 show variables like '%partition%'; 命令查询当前的mysql数据库版本是否支持分区。 

        分区的作用:数据库性能的提升和简化数据管理 

        在扫描操作中，mysql优化器只扫描保护数据的那个分区以减少扫描范围获得性能的提高。 分区技术使得数据管理变得简单，删除某个分区不会对另外的分区造成影响，分区有系统直接管理不用手工干预。 

        mysql从5.1版本开始支持分区。每个分区的名称是不区分大小写。同个表中的分区表名称要唯一。 

        二、 mysql分区类型 

        根据所使用的不同分区规则可以分成几大分区类型。 

        RANGE 分区: 

        基于属于一个给定连续区间的列值，把多行分配给分区。 

        LIST 分区: 

        类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。 

        HASH分区: 

        基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。 

        KEY分区:类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。 

        复合分区: 基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。 

        三、 mysql分区表常用操作示例 以部门员工表为例子: 

        1) 创建range分区 

        create table emp 

        (empno varchar(20) not null , 

        empname varchar(20), 

        deptno int, 

        birthdate date, 

        salary int 

        ) 

        partition by range(salary) ( 

        partition p1 values less than (1000), partition p2 values less than (2000), partition p3 values less than maxvalue 

        ); 

        以员工工资为依据做范围分区。 

        create table emp 

        (empno varchar(20) not null , 

        empname varchar(20), 

        deptno int, 

        birthdate date not null, 

        salary int 

        ) 

        partition by range(year(birthdate)) ( 

        partition p1 values less than (1980), partition p2 values less than (1990), partition p3 values less than maxvalue 

        ); 

        以year(birthdate)表达式(计算员工的出生日期)作为范围分区依据。这里最值得注意的是表达式必须有返回值。 

        2) 创建list分区 

        create table emp 

        (empno varchar(20) not null , 

        empname varchar(20), 

        deptno int, 

        birthdate date not null, 

        salary int 

        ) 

        partition by list(deptno) 

        ( 

        partition p1 values in (10), 

        partition p2 values in (20), 

        partition p3 values in (30) 

        ); 

        以部门作为分区依据，每个部门做一分区。 

        3) 创建hash分区 

        HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;而在HASH分区中，MySQL自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。 

        create table emp 

        (empno varchar(20) not null , 

        empname varchar(20), 

        deptno int, 

        birthdate date not null, 

        salary int 

        ) 

        partition by hash(year(birthdate)) 

        partitions 4; 

        4) 创建key分区 

        按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的哈希函数是由MySQL 服务器提供，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。“CREATE TABLE ... PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。 create table emp 

        (empno varchar(20) not null , 

        empname varchar(20), 

        deptno int, 

        birthdate date not null, 

        salary int 

        ) 

        partition by key(birthdate) 

        partitions 4; 

        5) 创建复合分区 

        range - hash(范围哈希)复合分区 

        create table emp 

        (empno varchar(20) not null , empname varchar(20), 

        deptno int, 

        birthdate date not null, 

        salary int 

        ) 

        partition by range(salary) subpartition by hash(year(birthdate)) 

        subpartitions 3 

        ( 

        partition p1 values less than (2000), partition p2 values less than maxvalue 

        ); 

        range- key复合分区 

        create table emp 

        (empno varchar(20) not null , empname varchar(20), 

        deptno int, 

        birthdate date not null, 

        salary int 

        ) 

        partition by range(salary) subpartition by key(birthdate) subpartitions 3 

        ( 

        partition p1 values less than (2000), partition p2 values less than maxvalue 

        ); 

        list - hash复合分区 

        CREATE TABLE emp ( 

        empno varchar(20) NOT NULL, 

        empname varchar(20) , 

        deptno int, 

        birthdate date NOT NULL, 

        salary int 

        ) 

        PARTITION BY list (deptno) 

        subpartition by hash(year(birthdate)) subpartitions 3 

        ( 

        PARTITION p1 VALUES in (10), PARTITION p2 VALUES in (20) 

        ) ; 

        list - key 复合分区 

        CREATE TABLE empk ( 

        empno varchar(20) NOT NULL, 

        empname varchar(20) , 

        deptno int, 

        birthdate date NOT NULL, 

        salary int 

        ) 

        PARTITION BY list (deptno) subpartition by key(birthdate) subpartitions 3 

        ( 

        PARTITION p1 VALUES in (10), PARTITION p2 VALUES in (20) 

        ) ; 

        6) 分区表的管理操作 

        删除分区: 

        alter table emp drop partition p1; 

        不可以删除hash或者key分区。 

        一次性删除多个分区，alter table emp drop partition p1,p2; 

        增加分区: 

        alter table emp add partition (partition p3 values less than (4000)); 

        alter table empl add partition (partition p3 values in (40)); 

        分解分区: 

        Reorganize partition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。分解前后分区的整体范围应该一致。 alter table te 

        reorganize partition p1 into 

        ( 

        partition p1 values less than (100), partition p3 values less than (1000) ); ----不会丢失数据 

        合并分区: 

        Merge分区:把2个分区合并为一个。 

        alter table te 

        reorganize partition p1,p3 into (partition p1 values less than (1000)); ----不会丢失数据 

        重新定义hash分区表: 

        Alter table emp partition by hash(salary) partitions 7; 

        ----不会丢失数据 

        重新定义range分区表: 

        Alter table emp partition by range(salary) 

        ( 

        partition p1 values less than (2000), partition p2 values less than (4000) ); ----不会丢失数据 

        删除表的所有分区: 

        Alter table emp remove partitioning;--不会丢失数据 

        重建分区: 

        这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果。它可用于整理分区碎片。 

        ALTER TABLE emp rebuild partition p1,p2; 

        优化分区: 

        如果从分区中删除了大量的行，或者对一个带有可变长度的行(也就是说，有VARCHAR，BLOB，或TEXT类型的列)作了许多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。 

        ALTER TABLE emp optimize partition p1,p2; 

        分析分区: 

        读取并保存分区的键分布。 

        ALTER TABLE emp analyze partition p1,p2; 

        修补分区: 

        修补被破坏的分区。 

        ALTER TABLE emp repair partition p1,p2; 

        检查分区: 

        可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。 ALTER TABLE emp CHECK partition p1,p2; 

        这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况，

        使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。 

        【mysql分区表的局限性】 

        1. 在5.1版本中分区表对唯一约束有明确的规定，每一个唯一约束必须包含在分区表的分区键(也包括主键约束)。 

        CREATE TABLE emptt ( 

        empno varchar(20) NOT NULL , 

        empname varchar(20)， 

        deptno int, 

        birthdate date NOT NULL, 

        salary int , 

        primary key (empno) 

        ) 

        PARTITION BY range (salary) 

        ( 

        PARTITION p1 VALUES less than (100), 

        PARTITION p2 VALUES less than (200) 

        ); 

        这样的语句会报错。MySQL Database Error: A PRIMARY KEY must include all columns in the table's partitioning function; 

        CREATE TABLE emptt ( 

        empno varchar(20) NOT NULL , 

        empname varchar(20) , 

        deptno int(11), 

        birthdate date NOT NULL, 

        salary int(11) , 

        primary key (empno，salary) 

        ) 

        PARTITION BY range (salary) 

        ( 

        PARTITION p1 VALUES less than (100), 

        PARTITION p2 VALUES less than (200) 

        ); 

        在主键中加入salary列就正常。 

        2. MySQL分区处理NULL值的方式 

        如果分区键所在列没有not null约束。 

        如果是range分区表，那么null行将被保存在范围最小的分区。 如果是list分区表，那么null行将被保存到list为0的分区。 

        在按HASH和KEY分区的情况下，任何产生NULL值的表达式mysql都视同它的返回值为0为了避免这种情况的产生，建议分区键设置成NOT NULL。 

        3. 分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是当分区类型为KEY分区的时候，可以使用其他类型的列作为分区键( BLOB or TEXT 列除外)。 

        4. 对分区表的分区键创建索引，那么这个索引也将被分区,分区键没有全局索引一说。 

        5. 只有RANG和LIST分区能进行子分区，HASH和KEY分区不能进行子分区。 6. 临时表不能被分区。 

        四、 获取mysql分区表信息的几种方法 

        1. show create table 表名 

        可以查看创建分区表的create语句 

        2. show table status 

        可以查看表是不是分区表 

        3. 查看information_schema.partitions表 

        select 

        partition_name part, 

        partition_expression expr, 

        partition_description descr, 

        table_rows 

        from information_schema.partitions where 

        table_schema = schema() 

        and table_name='test'; 

        可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息 4. explain partitions select语句通过此语句来显示扫描哪些分区，及他们是如何使用的. 

        五、 分区表性能比较 

        1. 创建两张表: part_tab(分区表),no_part_tab(普通表) 

        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) ); CREATE TABLE no_part_tab 

        ( c1 int default NULL, c2 varchar(30) default NULL, c3 date not null); 

        2. 用存储过程插入800万条数据 

        CREATE PROCEDURE load_part_tab() 

        begin 

        declare v int default 0; 

        while v < 50000000 

        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; 

        insert into no_part_tab select * from part_tab; 

        3. 测试sql性能 

        查询分区表: 

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

        | count(*) | 

        +----------+ 

        | 795181 | 

        ----------+ +

        1 row in set (2.62 sec) 

        查询普通表: 

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

        | count(*) | 

        +----------+ 

        | 795181 | 

        +----------+ 

        1 row in set (7.33 sec) 

        分区表的执行时间比普通表少70%。 

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

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

        +----+-------------+----------+------+---------------+------+---------+------+---------+-------------+ 

        | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 

        +----+-------------+----------+------+---------------+------+---------+------+---------+-------------+ 

        | 1 | SIMPLE | part_tab | ALL | NULL | NULL | NULL | NULL | 7980796 | Using where | 

        +----+-------------+----------+------+---------------+------+---------+------+---------+-------------+ 

        1 row in set 

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

        +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+ 

        | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 

        +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+ 

        | 1 | SIMPLE | no_part_tab | ALL | NULL | NULL | NULL | NULL | 8000206 

        | Using where | 

        +----+-------------+-------------+------+---------------+------+---------+------+--

        -------+-------------+ 

        1 row in set 

        mysql > 

        分区表执行扫描了7980796行，而普通表则扫描了8000206行。 

        DROP PROCEDURE IF EXISTS test // 

        CREATE PROCEDURE test /* 存储过程

        名 */ (IN inparms INT, OUT outparams varchar(32)) /* 输入参

        数 */ BEGIN /* 语句块

        头 */ DECLARE var CHAR(10); /* 变量声

        明 */ IF inparms = 1 THEN /* IF条件开始

        */ SET var = 'hello'; /* 赋值 */ ELSE 

        SET var = 'world'; 

        END IF; /* IF结束 */ INSERT INTO t1 VALUES (var); /* SQL

        语句 */ SELECT name FROM t1 LIMIT 1 INTO outparams; END