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

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

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

在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)
);
特别注意
如果比里面有主键(唯一键),则创建分区的字段必须是主键(复合主键)(唯一键)或主键(唯一键)的一部分 .

打赏 支付宝打赏 微信打赏

未经允许不得转载!

评论列表 0

访客
取消