MySQL分区

什么是数据库分区：
以mysql为例，mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件：
frm存放表结构
myd存放表数据
myi存表索引
如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能。在执行查询的时候，优化器会根据分区定义过滤哪些没有我们需要数据的分区，这样查询就无须全表扫描所有分区，只查找包含需要数据的分区即可。（查询条件包含了可以过滤分区的条件）

分割后的文件

分区和分表的区别：
一张大表进行分区后，他还是一张表，不会变成二张表，对开代码端来说是透明的，但是他存放数据的区块变多了。分区是根据一定的规则把数据文件和索引文件进行了分割，还多出了一个.par文件，打开.par文件后你可以看出他记录了这张表的分区信息。
分区对于程序来说是透明的。
分区一般只能起到水平切分的作用。

创建分区：
1)分区列必须属于此表所有主键（组合）或唯一键（组合）的交集。关于分区列与主键的具体解释。如果没有主键和唯一键则没有此限制。
2)对于原生的RANGE分区，LIST分区，HASH分区，分区字段必须为整数字段名或返回确定整数的函数。
3)添加COLUMNS（支持RANGE分区，LIST分区），关键字可定义非integer范围及多列范围，不过需要注意COLUMNS括号内只能是列名，不支持函数。
4)在RANGE 分区中如果往分区列中插入NULL值会被当作最小的值来处理，在LIST分区中NULL值必须在枚举列表中否则插入失败，在HASH，KEY分区中NULL值会被当作0来处理。

RANGE分区：
根据范围分区，范围应该连续但是不重叠，不使用COLUMNS关键字时RANGE括号内必须为整数字段名或返回确定整数的函数。

//创建range分区表  
mysql> CREATE TABLE IF NOT EXISTS `user` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',  
 ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',  
 ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女',  
 ->   PRIMARY KEY (`id`)  
 -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
 -> PARTITION BY RANGE (id) (  
 ->     PARTITION p0 VALUES LESS THAN (3),  
 ->     PARTITION p1 VALUES LESS THAN (6),  
 ->     PARTITION p2 VALUES LESS THAN (9),  
 ->     PARTITION p3 VALUES LESS THAN (12),  
 ->     PARTITION p4 VALUES LESS THAN MAXVALUE  
 -> );  
Query OK, 0 rows affected (0.13 sec)  
  
//插入一些数据  
mysql> INSERT INTO `test`.`user` (`name` ,`sex`)VALUES ('tank', '0')  
 -> ,('zhang',1),('ying',1),('张',1),('映',0),('test1',1),('tank2',1)  
 -> ,('tank1',1),('test2',1),('test3',1),('test4',1),('test5',1),('tank3',1)  
 -> ,('tank4',1),('tank5',1),('tank6',1),('tank7',1),('tank8',1),('tank9',1)  
 -> ,('tank10',1),('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1);  
Query OK, 25 rows affected (0.05 sec)  
Records: 25  Duplicates: 0  Warnings: 0  
  
//到存放数据库表文件的地方看一下，my.cnf里面有配置，datadir后面就是  
[root@BlackGhost test]# ls |grep user |xargs du -sh  
4.0K    user#P#p0.MYD  
4.0K    user#P#p0.MYI  
4.0K    user#P#p1.MYD  
4.0K    user#P#p1.MYI  
4.0K    user#P#p2.MYD  
4.0K    user#P#p2.MYI  
4.0K    user#P#p3.MYD  
4.0K    user#P#p3.MYI  
4.0K    user#P#p4.MYD  
4.0K    user#P#p4.MYI  
12K    user.frm  
4.0K    user.par  
  
//取出数据  
mysql> select count(id) as count from user;  
+-------+  
| count |  
+-------+  
|    25 |  
+-------+  
1 row in set (0.00 sec)  
  
//删除第四个分区  
mysql> alter table user drop partition p4;  
Query OK, 0 rows affected (0.11 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
/**存放在分区里面的数据丢失了，第四个分区里面有14条数据，剩下的3个分区 
只有11条数据，但是统计出来的文件大小都是4.0K，从这儿我们可以看出分区的 
最小区块是4K 
*/  
mysql> select count(id) as count from user;  
+-------+  
| count |  
+-------+  
|    11 |  
+-------+  
1 row in set (0.00 sec)  
  
//第四个区块已删除  
[root@BlackGhost test]# ls |grep user |xargs du -sh  
4.0K    user#P#p0.MYD  
4.0K    user#P#p0.MYI  
4.0K    user#P#p1.MYD  
4.0K    user#P#p1.MYI  
4.0K    user#P#p2.MYD  
4.0K    user#P#p2.MYI  
4.0K    user#P#p3.MYD  
4.0K    user#P#p3.MYI  
12K    user.frm  
4.0K    user.par  
  
/*可以对现有表进行分区,并且会按規则自动的将表中的数据分配相应的分区 
中，这样就比较好了，可以省去很多事情，看下面的操作*/  
mysql> alter table aa partition by RANGE(id)  
 -> (PARTITION p1 VALUES less than (1),  
 -> PARTITION p2 VALUES less than (5),  
 -> PARTITION p3 VALUES less than MAXVALUE);  
Query OK, 15 rows affected (0.21 sec)   //对15数据进行分区  
Records: 15  Duplicates: 0  Warnings: 0  
  
//总共有15条  
mysql> select count(*) from aa;  
+----------+  
| count(*) |  
+----------+  
|       15 |  
+----------+  
1 row in set (0.00 sec)  
  
//删除一个分区  
mysql> alter table aa drop partition p2;  
Query OK, 0 rows affected (0.30 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
//只有11条了，说明对现有的表分区成功了  
mysql> select count(*) from aa;  
+----------+  
| count(*) |  
+----------+  
|       11 |  
+----------+  
1 row in set (0.00 sec)

RANGE COLUMNS分区
添加COLUMNS关键字可定义非integer范围及多列范围，不过需要注意COLUMNS括号内只能是列名，不支持函数；多列范围时，多列范围应该连续但是不重叠。

CREATE TABLE rc3 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,20),
    PARTITION p2 VALUES LESS THAN (10,30),
    PARTITION p3 VALUES LESS THAN (10,35),
    PARTITION p4 VALUES LESS THAN (20,40),
    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
 );

LIST分区
数值必须被所有分区覆盖且不重叠，否则插入一个不属于任何一个分区的数值会报错。不使用COLUMNS关键字时List括号内必须为整数字段名或返回确定整数的函数。

//这种方式失败  
mysql> CREATE TABLE IF NOT EXISTS `list_part` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',  
 ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省',  
 ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',  
 ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女',  
 ->   PRIMARY KEY (`id`)  
 -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
 -> PARTITION BY LIST (province_id) (  
 ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
 ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  
 ->     PARTITION p2 VALUES IN (13,14,15,19),  
 ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  
 -> );  
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function 
 
//这种方式成功 
mysql> CREATE TABLE IF NOT EXISTS `list_part` ( 
 ->   `id` int(11) NOT NULL  COMMENT '用户ID', 
 ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', 
 ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', 
 ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女'  
 -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
 -> PARTITION BY LIST (province_id) (  
 ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
 ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  
 ->     PARTITION p2 VALUES IN (13,14,15,19),  
 ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  
 -> );  
Query OK, 0 rows affected (0.33 sec)

LIST COLUMNS分区
与Range分区相同，添加COLUMNS关键字可支持非整数和多列。

CREATE TABLE customers_1 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
    PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
    PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
    PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
    PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);

HASH分区
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。
Hash括号内只能是整数列或返回确定整数的函数，实际上就是使用返回的整数对分区数取模。
传统Hash可扩展性差。MySQL也提供了一个类似于一致Hash的分区方法，线性Hash分区。

mysql> CREATE TABLE IF NOT EXISTS `hash_part` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID',  
 ->   `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT '评论',  
 ->   `ip` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  
 ->   PRIMARY KEY (`id`)  
 -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
 -> PARTITION BY LINEAR HASH(id)  
 -> PARTITIONS 3;  
Query OK, 0 rows affected (0.06 sec)

key分区
类似HASH分区，但使用的用户定义的表达式，可支持非整数。KEY分区使用的哈希函数是由MySQL 服务器提供。也有对应与线性Hash的线性Key分区方法。

mysql> CREATE TABLE IF NOT EXISTS `key_part` (  
 ->   `news_id` int(11) NOT NULL  COMMENT '新闻ID',  
 ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',  
 ->   `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  
 ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'  
 -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
 -> PARTITION BY HASH(YEAR(create_time))  
 -> PARTITIONS 3;  
Query OK, 0 rows affected (0.07 sec)

新增分区：

//range添加新分区  
mysql> alter table user add partition(partition p4 values less than MAXVALUE);  
Query OK, 0 rows affected (0.06 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
//list添加新分区  
mysql> alter table list_part add partition(partition p4 values in (25,26,28));  
Query OK, 0 rows affected (0.01 sec)  
Records: 0  Duplicates: 0  Warnings: 0  

//range重新分区  
mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);  
Query OK, 11 rows affected (0.08 sec)  
Records: 11  Duplicates: 0  Warnings: 0  
  
//list重新分区  
mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));  
Query OK, 0 rows affected (0.28 sec)  
Records: 0  Duplicates: 0  Warnings: 0
  
//hash重新分区  
mysql> alter table hash_part add partition partitions 4;  
Query OK, 0 rows affected (0.12 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
//key重新分区  
mysql> alter table key_part add partition partitions 4;  
Query OK, 1 row affected (0.06 sec)    //有数据也会被重新分配  
Records: 1  Duplicates: 0  Warnings: 0

原有数据会按照新的分区重新分区。

查询优化：
一般来说，就是在where条件中加入分区列。
5.7以前的版本显示分区的执行计划使用：explain PARTITIONS；5.7以后直接执行：explain

要点：
1.一张大表进行分区后，他还是一张表，不会变成二张表，对开代码端来说是透明的。
2.分区列必须属于此表所有主键（组合）或唯一键（组合）的交集。
3.对于原生的RANGE分区，LIST分区，HASH分区，分区字段必须为整数字段名或返回确定整数的函数。
4.RANGE：行数据基于属于一个给定连续区间的列值放入分区。LIST：和RANGE分区类型一样，只是LIST分区面向的是离散的值。
5.添加COLUMNS（支持RANGE分区，LIST分区），关键字可定义非integer范围及多列范围。
6.分区表在清理数据归档时非常有用，比如按天分区，如果要按天清理过期数据，只需要删除分区即可，速度快，消耗资源少。

使用 PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (3),
PARTITION p1 VALUES LESS THAN (6),
...
);

参考：
http://blog.51yip.com/mysql/1013.html
http://haitian299.github.io/2016/05/26/mysql-partitioning/

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 160,026评论 4赞 364
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 67,655评论 1赞 296
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 109,726评论 0赞 244
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 44,204评论 0赞 213
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 52,558评论 3赞 287
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 40,731评论 1赞 222
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 31,944评论 2赞 314
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,698评论 0赞 203
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,438评论 1赞 246
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,633评论 2赞 247
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,125评论 1赞 260
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,444评论 3赞 255
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,137评论 3赞 238
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,103评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,888评论 0赞 197
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 35,772评论 2赞 276
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,669评论 2赞 271

推荐阅读更多精彩内容

  • MySQL 分区
    分区是指根据一定的规则，数据库把一个表分解成多个更小的，更容易管理的部分。就访问数据库的应用而言，逻辑上只有一个表...
    微日月阅读 1,478评论 0赞 7
  • MySQL分区与传统的分库分表(转)
    原文链接 http://blog.csdn.net/kobejayandy/article/details/547...
    lucode阅读 870评论 0赞 0
  • ［MySQL］MySQL分区与传统的分库分表
    传统的分库分表 传统的分库分表都是通过应用层逻辑实现的，对于数据库层面来说，都是普通的表和库。 分库 分库的原因 ...
    haitian299阅读 3,186评论 0赞 25
  • mysql分区详解
    一，什么是数据库分区前段时间写过一篇关于mysql分表的的文章，下面来说一下什么是数据库分区，以mysql为例。m...
    MrKai平凡之路阅读 1,111评论 0赞 5
  • 如何培养读书习惯
    我们一生中每天都在不停的输入和输出，有身体上的，有智力上的。写作、培训、演讲等这些活动属于信息输出。有输出，必然有...
    正零阅读 2,117评论 0赞 5