MySQL分区技术

分区介绍

基本概念，把一个表，从逻辑上分成多个区域，便于存储数据。
采用分区的前提，数据量非常大。
如果数据表的记录非常多，比如达到上亿条，数据表的活性就大大降低，数据表的运行速度就比较慢、效率低下，影响mysql数据库的整体性能，就可以采用分区解决，分区是mysql本身就支持的技术 . 是从逻辑上进行分区,仍然是一张表 .

以上的结构，在创建（修改）表时，可以指定表，可以被分成几个区域。

利用表选项：partition 完成。

create table  表名(
	字段信息，
    索引，
)表选项partition by 分区算法（分区字段）（
	分区选项
）；
分区算法：
条件分区：list (列表) range(范围)  取模轮询（hash,key）

注意

一般情况下list和range分区用的比较多.

list分区

list :条件值为一个数据列表。

算法 :

根据"字段的内容值"是否在某个"列表"中进行分区，通过预定义的列表的值来对数据进行分割。

例:
假如你创建一个如下的一个表，该表保存有全国20家分公司的职员记录，这20家分公司的编号从1到20.而这20家分公司分布在全国4个区域，如下表所示：

职员表 :

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| name     | varchar(32) | YES  |     | NULL    |       |
| store_id | int(11)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

分区信息

北部    1,4,5,6,17,18
南部    2,7,9,10,11,13
东部    3,12,19,20
西部    8,14,15,16

建表语法 :

create table p_list(                    
                id int,    
                name varchar(32),
                store_id int
                )engine innodb charset utf8
                partition by list (store_id)(
		//分区字段 store_id    
		partition p_north values in (1,4,5,6,17,18),    
		partition p_east values in(2,7,9,10,11,13),    
		partition p_south values in(3,12,19,20),    
		partition p_west values in(8,14,15,16)
);

分区文件信息

查询测试

语法:

explain partitions select * from 表名  where  分区字段 =  条件\G

结果:

          id: 1  
          select_type: SIMPLE        
          table: p_list   
          partitions: p_east				//使用到了分区         
          type: ALL
          possible_keys: NULL          
          key: NULL      
          key_len: NULL          
          ref: NULL         
          rows: 1     
          filtered: 100.00        
          Extra: Using where

不使用分区字段作为条件进行查询

explain partitions select * from 表名  where  其他字段 =  条件\G

结果

           id: 1  
           select_type: SIMPLE        
           table: p_list   
           partitions: p_north,p_east,p_south,p_west			//查找了所有分区          
           type: ALLpossible_keys: NULL          
           key: NULL      
           key_len: NULL          
           ref: NULL         
           rows: 3     
           filtered: 33.33        
           Extra: Using where

注意：在使用分区时，where后面的字段必须是分区字段，才能使用到分区.

算法

根据"字段内容"是否在 某个范围进行分区，通过预定义的范围的值来对数据进行分割. 这种模式允许将数据划分不同范围。例如可以将一个表通过月份划分成若干个分区 .

语法

create table p_range( 
       id int,
       name varchar(32),
       birthday date
       )engine innodb charset utf8
       partition by range (month(birthday))(	//用month() 获得月份值    
       		partition p_1 values less than (3),    
       		partition p_2 values less than(6),    
       		partition p_3 values less than(9),    
       		partition p_4 values less than MAXVALUE
);

less than : 小于等于 ;
MAX VALUE : 可能的最大值 ;

文件结构

测试

 explain partitions select * from user where age > 20\G

结果:

           id: 1  
           select_type: SIMPLE        
           table: user   
           partitions: p_3,p_4				//使用了分区,          
           type: range
           possible_keys: age          
           key: age							//给字段加索引效果更好      
           key_len: 2          
           ref: NULL         
           rows: 3     
           filtered: 100.00        
           Extra: Using index condition

hash

可以这么理解：
Hash是基于分区个数的取模（%）运算，根据余数将数据分配到不同的分区。
注意：hash分区只能针对整数进行hash！

这种模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

语法

create table  p_hash(
	id  int,
	name varchar(20),
	birthday date
	)engine innodb charset utf8 
partition by hash(month(birthday)) partitions 5;		//这里说明分成五个区

文件结构

测试

这里使用的测试数据和上面的创建数据不一致,这里是使用主键作为分区字段的.

explain partitions select * from user where id = 30000\G

结果 :

           id: 1  
           select_type: SIMPLE        
           table: user   
           partitions: p0					//自动进行了分区         
           type: const
           possible_keys: PRIMARY          
           key: PRIMARY      
           key_len: 4          
           ref: const         
           rows: 1     
           filtered: 100.00        
           Extra: NULL

key(键值)

面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
可以简单理解为，通过“求余”算法把记录分配到不同的分区。

语法

create table p_key(
    id int,
        name varchar(32),
    birthday date
    )engine myisam charset utf8
    partition by key (id) partitions 5;

文件夹结构

测试

explain partitions select * from p_key where id = 2000\G

结果:

           id: 1  
           select_type: SIMPLE        
           table: p_key   
           partitions: p1				//使用了自动分区         
           type: const
           possible_keys: PRIMARY          
           key: PRIMARY      
           key_len: 4          
           ref: const         
           rows: 1     
           filtered: 100.00        
           Extra: NULL

分区管理

具体就是对已经存在的分区进行增加、减少操作。

删除分区

1. 在key/hash领域不会造成数据丢失(删除分区后数据会重新整合到剩余的分区去).

2. 在range/list领域会造成数据丢失 .

语法 :

求余方式(key/hash):

alter table 表名 coalesce partition 数量;	//不能删除所有的分区

范围方式(range/list):

alter table 表名 drop partition 分区名称;		//对应的分区上的数据会被删除

增加分区

语法 :

求余方式： key/hash

alter table 表名  add  partition partitions  数量;

范围方式： range/list

alter table 表名 add partition( 
  partition 名称 values less than (常量)
   或   partition 名称 values in (n,n,n)
);

特别注意

如果比里面有主键(唯一键),则创建分区的字段必须是主键(复合主键)（唯一键）或主键（唯一键）的一部分 .