MySQL优化~分区表的管理

MySQL优化~分区表的管理

初探

很长时间没写博客了，这两天一直在学习Mysql分区，总结下:
Mysql支持水平分区，并不支持垂直分区;
水平分区：指将同一表中不同行的记录分配到不同的物理文件中；
垂直分区：指将同一表中不同列的记录分配到不同的物理文件中；
其中CSV、FEDORATED、MERGE等引擎不支持分区，MYISAM、InnoDB、NDB等引擎支持分区

目的

将一个表或索引分解为多个更小、更可管理的部分，从逻辑上讲，只有一个表或者索引，但是物理上这个表或者索引可能由数十个物理分区组成；没个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理（如果分区表很大，亦可以将分区分配到不同的磁盘上去）；在执行查询的时候，优化器会根据分区定义过滤哪些没有我们需要数据的分区，这样查询就无须全表扫描所有分区，只查找包含需要数据的分区即可

适用场景

1、表非常大以至于无法全部都放到内存，或者只在表的最后部分有热点数据，其他均为历史数据 2、分区表数据更容易维护（可独立对分区进行优化、检查、修复及批量删除大数据可以采用drop分区的形式等） 3、分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备 4、分区表可以避免某些特殊的瓶颈（ps: InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等） 5、可以备份和恢复独立的分区，非常适用于大数据集的场景

分区表限制

1. 单表最多支持1024个分区
2. MySQL5.1只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列;MySQL5.5的RANGE LIST类型可以直接使用列进行分区
3. 如果分区字段中有主键或唯一索引的列，那么所有的主键列和唯一索引列都必须包含进来
4. 分区表无法使用外键约束
5. 分区必须使用相同的Engine
6. 对于MyISAM分区表，不能在使用LOAD INDEX INTO CACHE操作
7. 对于MyISAM分区表，使用时会打开更多的文件描述符（单个分区是一个独立的文件）

分区策略

1. 全量扫描数据，不需要任何索引：通过where条件大概定位哪个分区，必须将查询所需要扫描的分区个数限制在很小的数量
2. 建立分区索引，分离热点：如将明显的热点数据分离到一个分区，使其尽量缓存到内存中，这样就能充分使用索引和缓存
   注意：以上策略均以查询得到过滤，丢掉额外的分区，分区本身不产生额外的代价为准则】

分区表使用过程的坑坑

1. NULL值会使分区过滤无效:
   分表的表达式的值可以是NULL，第一个分区为特殊分区存放NULL或者非法值
   如： PARTITION BY RANGE YEAR(order_date)进行分区，那么order_date为NULL或者非法值，记录存放在第一个分区:
   WHERE order_date BETWEEN ‘2014-01-01’ AND ‘2014-01-31’查询时会检查两个分区：
   第一个分区及1月份分区，避免第一分区数据过大时造成查询代价过高，可以使用：建立第一分区专门存放order_date为NULL和非法值记录 PARTITION p_nulls VALUES LESS THAN(0)
   MySQL5.5以后可以才用一下语法解决问题： PARTITION BY RANGE COLUMNS(order_date)

2. 分区列和索引列不匹配
   此种情况下查询无法进行分区过滤，分区失效除非查询中包含了可以过滤分区的条件

3. RANGE类型分区随着分区数量增加会对MYSQL额外增加查询分区定义列表（符合条件行在哪个分区）的压力，尽量限制适当的分区数量;key和hash类型分区不存在此问题

4. 重组分区或者类似alter语句可能会造成很大的开销
   新建或者删除分区操作很快，重组分区或者类似ALTER语句操作会先创建一个临时的分区，将数据复制其中，然后在删除原分区

分区表类型

1. RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区
   MySQL5.5开始支持RANGE COLUMNS的分区（引入Columns分区解决了MySQL 5.5版本之前RANGE分区和LIST分区只支持整数分区，从而导致需要额外的函数计算得到整数或者通过额外的转换表来转换为整数再分区的问题。Columns分区可以细分为RANGE Columns分区和LIST Columns分区，RANGE Columns分区和LIST Columns分区都支持整数、日期时间、字符串三大数据类型）

2. LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
   MySQL5.5开始支持RANGE COLUMNS的分区

3. HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数 4. KEY分区：根据MySQLS数据库提供的哈希函数来进行分区 【注：无论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分】

一、如何管理RANGE和LIST分区

以该分区表为例

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1970),
    PARTITION p1 VALUES LESS THAN (1980),
    PARTITION p2 VALUES LESS THAN (1990)
);

1. 删除分区

ALTER TABLE members DROP PARTITION p1;

注意：如果删掉了某分区，则该分区内的数据将全部丢失，不仅如此，在用show create table members\G;命令查看该表的创建语句时将无法看到被删除分区的任何信息。

         对于RANGE分区来说，如果删除了p1分区，在插入数据时，如果日期在1970到1980区间之内，则该数据将会被分配到下一个分区，即p2,。

         对于LIST分区，如果删除了某一分区，在插入数据时，如果数据属于这一分区，则插入会报错。

         如果只是删除数据而不删除该分区的信息，可使用truncate命令

ALTER TABLE members TRUNCATE PARTITION p1;

 

2. 添加分区

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

注意：使用ADD命令来添加分区，只能在分区列表的末尾添加，具体到本例中，只能添加到1990年以后。

当然，在实际生产环境中，这样的局限性太大，譬如我想将p0分区之前添加一个分区，区间为1960，或者将p1之间再添加一个1975的分区，这时候，用ADD将无法满足此类需求，可使用ALTER TABLE ... REORGANIZE PARTITION命令。

譬如：

ALTER TABLE members REORGANIZE PARTITION p0 INTO (
    PARTITION s0 VALUES LESS THAN (1960),
    PARTITION s1 VALUES LESS THAN (1970)
);

REORGANIZE命令其实是相当灵活的，不仅能拆分分区，还可以用来合并分区，譬如：

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES LESS THAN (2000)
);

注意：

1> 不能使用REORGANIZE PARTITION命令修改表的分区类型，只能通过ALTER TABLE ... PARTITION BY ....语句，譬如：

ALTER TABLE members
    PARTITION BY HASH( YEAR(dob) )
    PARTITIONS 8;

2> REORGANIZE PARTITION语法如下：

ALTER TABLE tbl_name
    REORGANIZE PARTITION partition_list
    INTO (partition_definitions);

partition_definitions中分区的范围必须要涵盖partition_list中的分区范围。

 

二、如何管理HASH和KEY分区

以该分区表为例

CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;

对于HASH分区和KEY分区，并不支持上述RANGE和LIST分区的语法，譬如DROP，TRUNCATE，REORGANIZE分区。

事实上，它只支持一种类型的“分区调整”。

ALTER TABLE clients COALESCE PARTITION 4;

该命令的作用在于将clients表的分区剪裁4个，从12个剪裁到8个。

ALTER TABLE clients ADD PARTITION PARTITIONS 6;

同样，该命令为clients表增加了6个分区，从12个到18个。

 

参考

http://blog.51yip.com/mysql/1013.html
https://dev.mysql.com/doc/refman/5.6/en/partitioning-maintenance.html
http://dev.mysql.com/doc/refman/5.6/en/index.html