mysql合并表与分区表

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

链接：https://www.zhihu.com/question/38418707/answer/579911085

1 合并表

合并表其实就是合并了多个子表的逻辑表，子表使用了myisam存储引擎物理子表，合并表使用merge存储引擎，逻辑表和子表的结构完全相同（包括字段、索引等）。
初始化合并表 t12=t1+t2

mysql> create database test;
mysql> create table t1(data int not null primary key) engine=myisam;
mysql> create table t2(data int not null primary key) engine=myisam;
mysql> create table t12(data int not null primary key) engine=merge union=(t1,t2) insert_method=last;

子表插入数据

mysql> insert into t1 values(1),(2),(3);
mysql> insert into t2 values(21),(22),(23);
mysql> select * from t12;
+------+
| data |
+------+
|    1 |
|    2 |
|    3 |
|   21 |
|   22 |
|   23 |
+------+
6 rows in set (0.00 sec)

创建合并表的时候，指定了insert_method为last，意思就是在最后一张物理表的末尾插入真实数据，这里最后一张真实物理表就是t2。此时我们插入一个数据5会发现：t1没有，t2有。

mysql> insert into t12 values(31);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| data |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from t2;
+------+
| data |
+------+
|   21 |
|   22 |
|   23 |
|   31 |
+------+
4 rows in set (0.00 sec)

2 分区表

分区表就是把一张表分开，对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。
分区表的每一个分区都是有索引的独立表。

场景：

（1）表非常大以至无法全部放内存中，或者只在表的最后部分有热点数据，其他均是历史数据。
（2）分区表的数据更容易维护。
（3）分区表的数据可以分布在不同的物理设备上。
（4）可以使用分区表来避免某些特殊的瓶颈，例如InnoDB单个索引的互斥访问。
（5）可以备份和恢复独立的分区，非常大的数据集场景下效果非常好。

分开的方式三种：

（1）水平分区：根据行切分，也就是把记录分开。
（2）垂直分区：根据列切分，也就是把字段分开。
（3）复合分区：水平分区和垂直分区的结合。

2.1 水平分区

2.1.1 range分区

range分区是基于连续的范围值。

mysql> create table  t_range (id int not null,score int) partition by range(score) (partition p1 values less than(60),partition p2 values less than(90),partition p3 values less than maxvalue);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t_range values(1,58),(2,60),(3,85),(4,100) ;
mysql> select * from t_range;
+----+-------+
| id | score |
+----+-------+
|  1 |    58 |
|  2 |    60 |
|  3 |    85 |
|  4 |   100 |
+----+-------+
4 rows in set (0.00 sec)
# 只查某个分区的数据
mysql> select * from t_range  partition(p1);
+----+-------+
| id | score |
+----+-------+
|  1 |    58 |
+----+-------+

查看表文件，可以看到确实分了3个文件存放。

[root@111]# ll /var/lib/mysql/test/ -Sh
-rw-r----- 1 mysql mysql  96K 10月 14 16:12 t_range#P#p1.ibd
-rw-r----- 1 mysql mysql  96K 10月 14 16:12 t_range#P#p2.ibd
-rw-r----- 1 mysql mysql  96K 10月 14 16:12 t_range#P#p3.ibd
-rw-r----- 1 mysql mysql 8.4K 10月 14 16:10 t_range.frm
-rw-r----- 1 mysql mysql 8.4K 10月 14 09:24 t1.frm
-rw-r----- 1 mysql mysql 2.0K 10月 14 09:26 t1.MYI
...

删除某个范围内的用户（例如基于日期时间进行分区），只需删除对应的分区即可。alter形式删除分区比delete形式更加的高效。

mysql> alter table t_range drop partition p2;
mysql> select * from t_range;
+----+-------+
| id | score |
+----+-------+
|  1 |    58 |
|  4 |   100 |
+----+-------+
2 rows in set (0.00 sec)

2.1.2 List分区

range分区是基于连续的范围，list是基于确定值的范围(需要给全集分配到具体分区，否则未指定的数据元素无法更新)。

mysql> create table  t_list (id int not null,score int) partition by list(score) (partition p1 values in (60,100),partition p2 values in (90));
Query OK, 0 rows affected (0.02 sec)

mysql>  insert into t_list values(1,58),(2,60),(3,90),(4,100) ;
ERROR 1526 (HY000): Table has no partition for value 58

mysql>  insert into t_list values(1,60),(2,60),(3,90),(4,100);
Query OK, 4 rows affected (0.00 sec)

mysql> select * from t_list;
+----+-------+
| id | score |
+----+-------+
|  1 |    60 |
|  2 |    60 |
|  4 |   100 |
|  3 |    90 |
+----+-------+
4 rows in set (0.00 sec)

2.1.3 Hash分区

根据hash值取模，确定在哪分区。

mysql> create table t_hash(id int,score int not null) partition by hash(score) partitions 2;
mysql> select * from t_hash;
+------+-------+
| id   | score |
+------+-------+
|    1 |    88 |
|    1 |    99 |
+------+-------+
2 rows in set (0.00 sec)
# 99为奇数，落分区1
mysql> select * from t_hash  partition(p1);
+------+-------+
| id   | score |
+------+-------+
|    1 |    99 |
+------+-------+

2.2 垂直分区

通过key字段名进行划分。（？？？？？？）

mysql> create table t_key(id int not null,name varchar(20),score int) partition by key(id) partitions 3;
mysql> insert into t_key values(1,'hanlin',100);
mysql> insert into t_key values(2,'Lucy',90);

mysql> select * from t_key partition(p1);
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | hanlin |   100 |
+----+--------+-------+
1 row in set (0.00 sec)

mysql> select * from t_key partition(p2);
+----+------+-------+
| id | name | score |
+----+------+-------+
|  2 | Lucy |    90 |
+----+------+-------+
1 row in set (0.00 sec)

分区查看

mysql> use information_schema;
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME,
    -> PARTITION_METHOD,CREATE_TIME from `PARTITIONS` 
    -> where PARTITION_NAME is not null ;
+--------------+------------+----------------+------------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_METHOD | CREATE_TIME         |
+--------------+------------+----------------+------------------+---------------------+
| test         | t_hash     | p0             | HASH             | 2021-10-14 16:44:07 |
| test         | t_hash     | p1             | HASH             | 2021-10-14 16:44:07 |
| test         | t_key      | p0             | KEY              | 2021-10-14 16:56:28 |
| test         | t_key      | p1             | KEY              | 2021-10-14 16:56:28 |
| test         | t_key      | p2             | KEY              | 2021-10-14 16:56:28 |
| test         | t_list     | p1             | LIST             | 2021-10-14 16:27:39 |
| test         | t_list     | p2             | LIST             | 2021-10-14 16:27:39 |
| test         | t_range    | p1             | RANGE            | 2021-10-14 16:18:29 |
| test         | t_range    | p3             | RANGE            | 2021-10-14 16:18:29 |
+--------------+------------+----------------+------------------+---------------------+
9 rows in set (0.03 sec)

参考：https://baijiahao.baidu.com/s?id=1655581234130331974&wfr=spider&for=pc

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