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) );
特别注意
如果比里面有主键(唯一键),则创建分区的字段必须是主键(复合主键)（唯一键）或主键（唯一键）的一部分 .
![多行文字溢出[...]的实现(text-overflow: ellipsis)](http://yanzhihong23.iteye.com/images/icon_star.png){kind=icon}
