文章目錄
- 為什麼要分表和分區？
- 一、分表
-
- 分表的幾種方式
-
-
- 1.mysql叢集
- 2.預先估計會出現大資料量并且通路頻繁的表，将其分為若幹個表
- 3.利用merge存儲引擎來實作分表
-
- 二、分區
-
- RANGE分區
-
- 分區表和未分區表性能測試
- 将不同分區放到不同存儲位置
- LIST分區
- HASH分區
- KEY分區
- COLUMNS分區
- 總結
-
- mysql分表和分區有什麼差別呢
- mysql分表和分區有什麼聯系？
為什麼要分表和分區？
我們的資料庫資料越來越大，随之而來的是單個表中資料太多。以至于查詢速度變慢，而且由于表的鎖機制導緻應用操作也搜到嚴重影響，出現了資料庫性能瓶頸。
mysql中有一種機制是表鎖定和行鎖定，是為了保證資料的完整性。表鎖定表示你們都不能對這張表進行操作，必須等我對表操作完才行。行鎖定也一樣，别的sql必須等我對這條資料操作完了，才能對這條資料進行操作。當出現這種情況時，我們可以考慮分表或分區。
一、分表
什麼是分表？
分表是将一個大表按照一定規劃的分解成多張具有獨立存儲空間的實體表，每個表都對應三個檔案，MYD資料檔案，.MYI索引檔案，.frm表結構檔案。這些表可以分布在同一塊磁盤上，也可以在不同的機器上。app讀寫的時候根據事先定義好的規則得到對應的表名，然後去操作它。
将單個資料庫表進行拆分，拆分成多個資料表，然後使用者通路的時候，根據一定的算法（如用hash的方式，也可以用求餘（取模）的方式），讓使用者通路不同的表，這樣資料分散到多個資料表中，減少了單個資料表的通路壓力。提升了資料庫通路性能。分表的目的就在于此，減小資料庫的負擔，縮短查詢時間。
Mysql分表分為垂直切分和水準切分
垂直切分是指資料表列的拆分
，把一張列比較多的表拆分為多張表 通常我們按以下原則進行垂直拆分: 把不常用的字段單獨放在一張表; 把text，blob（binary large object，二進制大對象）等大字段拆分出來放在附表中;經常組合查詢的列放在一張表中; 垂直拆分更多時候就應該在資料表設計之初就執行的步驟，然後查詢的時候用join關鍵起來即可。
水準拆分是指資料表行的拆分
，把一張的表的資料拆成多張表來存放。 水準拆分原則 通常情況下，我們使用hash、取模等方式來進行表的拆分 比如一張有400W的使用者表users，為提高其查詢效率我們把其分成4張表users1，users2，users3，users4 通過用ID取模的方法把資料分散到四張表内Id%4= [0,1,2,3] 然後查詢,更新,删除也是通過取模的方法來查詢 部分業務邏輯也可以通過地區，年份等字段來進行歸檔拆分; 進行拆分後的表，這時我們就要限制使用者查詢行為。比如我們是按年來進行拆分的,這個時候在頁面設計上就限制使用者必須要先選擇年,然後才能進行查詢。
分表的幾種方式
1.mysql叢集
它并不是分表，但起到了和分表相同的作用。叢集可分擔資料庫的操作次數，将任務分擔到多台資料庫上。叢集可以讀寫分離，減少讀寫壓力。進而提升資料庫性能。
2.預先估計會出現大資料量并且通路頻繁的表，将其分為若幹個表
根據一定的算法（如用hash的方式，也可以用求餘（取模）的方式）讓使用者通路不同的表。 例如論壇裡面發表文章的表，時間長了這張表肯定很大，幾十萬，幾百萬都有可能。聊天室裡面資訊表，幾十個人在一起一聊一個晚上，時間長了，這張表的資料肯定很大。像這樣的情況很多。是以這種能預估出來的大資料量表，我們就事先分出個N個表，這個N是多少，根據實際情況而定。以聊天資訊表為例：我們事先建100個這樣的表，message_00,message_01,message_02…message_98,message_99.然後根據使用者的ID來判斷這個使用者的聊天資訊放到哪張表裡面，可以用hash的方式來獲得，也可以用求餘的方式來獲得，方法很多。 或者可以設計每張表容納的資料量是N條，那麼如何判斷某張表的資料是否容量已滿呢？可以在程式段對于要新增資料的表，在插入前先做統計表記錄數量的操作，當<N條資料，就直接插入，當已經到達閥值，可以在程式段新建立資料庫表（或者已經事先建立好），再執行插入操作）。
3.利用merge存儲引擎來實作分表
如果要把已有的大資料量表分開比較痛苦，最痛苦的事就是改代碼，因為程式裡面的sql語句已經寫好了，用merge存儲引擎來實作分表, 這種方法比較适合。 merge分表，分為主表和子表，主表類似于一個殼子，邏輯上封裝了子表，實際上資料都是存儲在子表中的。 我們可以通過主表插入和查詢資料，如果清楚分表規律，也可以直接操作子表。 下面我們來實作一個簡單的利用merge存儲引擎來實作分表的示範： 建立一個完整表存儲着所有的成員資訊（表名為member）
//注：必須是myisam引擎的表才可以
mysql> create database qin;
Query OK, 1 row affected (0.00 sec)
mysql> use qin;
Database changed
mysql> create table member (
-> id bigint auto_increment primary key,
-> name varchar(33),
-> sex tinyint not null default '0')
-> engine=myisam default charset=utf8 auto_increment=1;
Query OK, 0 rows affected (0.01 sec)
//在建立表裡添加資料
mysql> insert into member(name,sex) values('tom',1);
Query OK, 1 row affected (0.06 sec)
mysql> insert into member(name,sex) select name,sex from member;
//多執行幾次這條語句就有很多内容了
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from member;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | tom | 1 |
| 2 | tom | 1 |
| 3 | tom | 1 |
| 4 | tom | 1 |
| 5 | tom | 1 |
| 6 | tom | 1 |
| 7 | tom | 1 |
| 8 | tom | 1 |
| 9 | tom | 1 |
| 10 | tom | 1 |
| 11 | tom | 1 |
| 12 | tom | 1 |
| 13 | tom | 1 |
| 14 | tom | 1 |
| 15 | tom | 1 |
| 16 | tom | 1 |
+----+------+-----+
16 rows in set (0.00 sec)
下面我們進行分表，這裡我們吧member分兩個表分别為tb_member1,tb_member2
//建立tb_member1,tb_member2兩個表，表結構要和要分離的表（member）結構一樣
mysql> create table tb_member1 ( id bigint auto_increment primary key, name varchar(33), sex tinyint not null default '0') engine=myisam default charset=utf8 auto_increment=1;
Query OK, 0 rows affected (0.01 sec)
mysql> create table tb_member2 ( id bigint auto_increment primary key, name varchar(33), sex tinyint not null default '0') engine=myisam default charset=utf8 auto_increment=1;
Query OK, 0 rows affected (0.00 sec)
//建立主表tb_member
主表引擎使用merge，使用union将兩個子表（tb_member1,tb_member2）連結起來
mysql> create table tb_member ( id bigint auto_increment primary key, name varchar(33), sex tinyint not null default '0')
-> engine=merge union=(tb_member1,tb_member2) insert_method=last charset=utf8;
Query OK, 0 rows affected (0.01 sec)
INSERT_METHOD參數解釋:
INSERT_METHOD = NO 表示該表不能做任何寫入操作隻作為查詢使用。
INSERT_METHOD = LAST表示插入到最後的一張表裡面。
INSERT_METHOD = first表示插入到第一張表裡面。
//檢視主表結構
mysql> desc tb_member;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(33) | YES | | NULL | |
| sex | tinyint(4) | NO | | 0 | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
注意：檢視子表與主表的字段定義要一緻
//接下來，我們把資料分到兩個子表中去
mysql> insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0;
#将member表中id%2=0的資料插入到 tb_member1表中
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;
#将member表中id%2=1的資料插入到 tb_member2表中
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
//檢視兩個子表的資料
mysql> select * from tb_member1;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 2 | tom | 1 |
| 4 | tom | 1 |
| 6 | tom | 1 |
| 8 | tom | 1 |
| 10 | tom | 1 |
| 12 | tom | 1 |
| 14 | tom | 1 |
| 16 | tom | 1 |
+----+------+-----+
8 rows in set (0.00 sec)
mysql> select * from tb_member2;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | tom | 1 |
| 3 | tom | 1 |
| 5 | tom | 1 |
| 7 | tom | 1 |
| 9 | tom | 1 |
| 11 | tom | 1 |
| 13 | tom | 1 |
| 15 | tom | 1 |
+----+------+-----+
8 rows in set (0.00 sec)
//檢視主表資料
mysql> select * from tb_member;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 2 | tom | 1 |
| 4 | tom | 1 |
| 6 | tom | 1 |
| 8 | tom | 1 |
| 10 | tom | 1 |
| 12 | tom | 1 |
| 14 | tom | 1 |
| 16 | tom | 1 |
| 1 | tom | 1 |
| 3 | tom | 1 |
| 5 | tom | 1 |
| 7 | tom | 1 |
| 9 | tom | 1 |
| 11 | tom | 1 |
| 13 | tom | 1 |
| 15 | tom | 1 |
+----+------+-----+
16 rows in set (0.00 sec)
注意：主表隻是一個外殼，存取資料發生在一個一個的子表裡面。 注意：每個子表都有自已獨立的相關表檔案，而主表隻是一個殼，并沒有完整的相關表檔案
二、分區
分區和分表相似，都是按照規則分解表。不同在于分表将大表分解為若幹個獨立的實體表，而分區是将資料分段劃分在多個位置存放，分區後，表還是一張表，但資料散列到多個位置了。app讀寫的時候操作的還是表名字，db自動去組織分區的資料。
分區主要有兩種形式：
水準分區
（Horizontal Partitioning）這種形式分區是對表的行進行分區，所有在表中定義的列在每個資料集中都能找到，是以表的特性依然得以保持。
舉個簡單例子：一個包含十年發票記錄的表可以被分區為十個不同的分區，每個分區包含的是其中一年的記錄。
垂直分區
（Vertical Partitioning）這種分區方式一般來說是通過對表的垂直劃分來減少目标表的寬度，使某些特定的列被劃分到特定的分區，每個分區都包含了其中的列所對應的行。
舉個簡單例子：一個包含了大text和BLOB列的表，這些text和BLOB列又不經常被通路，這時候就要把這些不經常使用的text和BLOB了劃分到另一個分區，在保證它們資料相關性的同時還能提高通路速度。
//分區技術支援 在5.6之前，使用這個參數檢視當将配置是否支援分區
mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+---------------+
|Variable_name | Value |
+-----------------------+---------------+
| have_partition_engine | YES |
+-----------------------+------------------+
如果是yes表示你目前的配置支援分區
//在5.6及以采用後，則采用如下方式進行檢視
在顯示結果中，可以看到partition是ACTIVE的，表示支援分區
RANGE分區
RANGE分區 基于屬于一個給定連續區間的列值，把多行配置設定給分區。這些區間要連續且不能互相重疊，使用VALUES LESS THAN操作符來進行定義。
mysql> create table user (
-> id int not null auto_increment,
-> name varchar(30) not null default '',
-> sex int(10) not null default '0',
-> primary key(id))
-> default charset=utf8 auto_increment=1
-> partition by range(id) ( #針對id分區
-> partition p0 values less than(3), #id小于3的放到p0分區
-> partition p1 values less than(6), #id小于6的放到p1分區，以此類推
-> partition p2 values less than(9),
-> partition p3 values less than(12),
-> partition p4 values less than maxvalue #其他的放到p4分區
-> );
Query OK, 0 rows affected (0.03 sec)
//插入資料
mysql> insert into user(name,sex)values ('tom1','0'),('tom2','0'),('tom3','0'),('tom4','0'),('tom5','0'),('tom6','0'),('tom7','0'),('tom8','0'),('tom9','0'),('tom1','10'),('tom1','11'),('tom1','12'),('tom1','13'),('tom1','14');
Query OK, 14 rows affected (0.00 sec)
Records: 14 Duplicates: 0 Warnings: 0
mysql> select * from user;
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | tom1 | 0 |
| 2 | tom2 | 0 |
| 3 | tom3 | 0 |
| 4 | tom4 | 0 |
| 5 | tom5 | 0 |
| 6 | tom6 | 0 |
| 7 | tom7 | 0 |
| 8 | tom8 | 0 |
| 9 | tom9 | 0 |
| 10 | tom1 | 10 |
| 11 | tom1 | 11 |
| 12 | tom1 | 12 |
| 13 | tom1 | 13 |
| 14 | tom1 | 14 |
+----+------+-----+
14 rows in set (0.00 sec)
到存放資料庫表檔案的地方看一下
//檢視分區資訊
從information_schema系統庫中的partitions表中檢視分區資訊
mysql> select * from information_schema.partitions where table_schema='qin' and table__name='user'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: qin
TABLE_NAME: user
PARTITION_NAME: p0
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: RANGE
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: id
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: 3
TABLE_ROWS: 2
AVG_ROW_LENGTH: 8192
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2021-01-20 22:36:33
UPDATE_TIME: 2021-01-20 22:44:04
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 2. row ***************************
TABLE_CATALOG: def
.......省略
從某個分區中查詢資料
mysql> select * from qin.user partition(p1); #檢視p1分區内容
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 3 | tom3 | 0 |
| 4 | tom4 | 0 |
| 5 | tom5 | 0 |
+----+------+-----+
3 rows in set (0.00 sec)
//删除分區
當删除了一個分區，也同時删除了該分區中所有的資料。
mysql> alter table qin.user drop partition p4;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
//新增分區
mysql> alter table qin.user add partition (partition tp4 values less than maxvalue);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
//合并分區
下面的SQL，将p1 – p3合并為2個分區p01– p02
mysql> alter table qin.user
-> reorganize partition p1,p2,p3 into
-> (partition p01 values less than (8),
-> partition p02 values less than (12)
-> );
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
分區表和未分區表性能測試
//建立一個未分區表
mysql> create table tab1 (c1 int , c2 varchar(22),c3 date);
Query OK, 0 rows affected (0.01 sec)
//建立分區表,按日期的年份拆分
mysql> create table tab2 (c1 int,c2 varchar(22),c3 date)
-> partition by range(year(c3)) (
-> partition p0 values less than(1995),
-> partition p1 values less than (1996),partition p2 values less than(1997),
-> partition p3 values less than (1998),partition p4 values less than(1999),
-> partition p5 values less than (2000),partition p6 values less than(2001),
-> partition p7 values less than (2002),partition p8 values less than(2003),
-> partition p9 values less than (2004),partition p10 values less than(2010),
-> partition p11 values less than maxvalue
-> );
Query OK, 0 rows affected (0.04 sec)
//通過存儲過程插入200萬條測試資料模拟大資料
建立存儲過程
mysql> delimiter $$
mysql> CREATE PROCEDURE load_t1()
-> begin
-> declare i int default 0;
-> while i < 2000000
-> do
-> insert into tab1 values (i,'testing partitions',adddate('1995-01-01',(rand(i)*36520) mod 3652));
-> set i = i + 1;
-> end while;
-> end
-> $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call load_t1();
//向tab2表中插入資料
//測試sql性能
mysql> select count(*) from tab1 where c3 > '1995-01-01' and c3 < '1995-12-31';
+----------+
| count(*) |
+----------+
| 198799 |
+----------+
1 row in set (1.25 sec)
mysql> select count(*) from tab2 where c3 > '1995-01-01' and c3 < '1995-12-31';
+----------+
| count(*) |
+----------+
| 198799 |
+----------+
1 row in set (0.14 sec)
結果表明分區表比未分區表的執行時間少很多。
//通過explain語句來分析執行情況
mysql> explain select count(*) from tab1 where c3 > '1995-01-01' and c3 < '1995-12-331'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1896888 #未分區掃描
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(*) from tab2 where c3 > '1995-01-01' and c3 < '1995-12-331'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab2
partitions: p1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 199332 #分區掃描
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
explain語句顯示了SQL查詢要處理的記錄數目可以看出分區表比未分區表的明顯掃描的記錄要少很多。
//建立索引後情況測試
mysql> create index index_t1 on tab1(c3);
Query OK, 0 rows affected (2.66 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index index_t2 on tab2(c3);
Query OK, 0 rows affected (2.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from tab1 where c3 > '1995-01-01' and c3 < '1995-12-31';
+----------+
| count(*) |
+----------+
| 198799 |
+----------+
1 row in set (0.11 sec)
mysql> select count(*) from tab2 where c3 > '1995-01-01' and c3 < '1995-12-31';
+----------+
| count(*) |
+----------+
| 198799 |
+----------+
1 row in set (0.12 sec)
建立索引後分區表比未分區表相差不大（資料量越大差别會明顯些）
将不同分區放到不同存儲位置
//建表時，提前建立好存儲目錄，并授權給mysql
[[email protected] ~]# mkdir -p /data/area{1..3}
[[email protected] ~]# ls /data/
area1 area2 area3
[[email protected] ~]# chown -R mysql:mysql /data/
[[email protected] ~]# ls -l /data/
total 0
drwxr-xr-x. 2 mysql mysql 6 Jan 22 17:49 area1
drwxr-xr-x. 2 mysql mysql 6 Jan 22 17:49 area2
drwxr-xr-x. 2 mysql mysql 6 Jan 22 17:49 area3
//建立表格
注：使用mysql預設的存儲引擎inodb時候，隻需要指定data directory 就可以，因為inodb的資料和索引在一個檔案中。但是建立表格時指定engine=myisam時，修改分區的存儲位置，需要同時指定datadirectory和index directory。
mysql> create table user1(id int not null auto_increment,
-> name varchar(22) not null default '',
-> primary key(id))
-> default charset=utf8 auto_increment=1
-> partition by range(id)(
-> partition p1 values less than (3) data directory '/data/area1',
-> partition p2 values less than (6) data directory '/data/area2',
-> partition p3 values less than (9) data directory '/data/area3'
-> );
Query OK, 0 rows affected (0.06 sec)
//檢視分區情況
[[email protected] ~]# tree -C /data/
/data/
├── area1
│ └── qin
│ └── user1#P#p1.ibd
├── area2
│ └── qin
│ └── user1#P#p2.ibd
└── area3
└── qin
└── user1#P#p3.ibd
6 directories, 3 files
//檢視預設資料存儲位置檔案
LIST分區
類似于按RANGE分區，差別在于LIST分區是基于列值比對一個離散值集合中的某個值來進行選擇。LIST分區通過使用“PARTITION BY LIST(expr)”來實作，其中“expr” 是某列值或一個基于某個列值、并傳回一個整數值的表達式，然後通過“VALUES IN (value_list)”的方式來定義每個分區，其中“value_list”是一個通過逗号分隔的整數清單。 要按照屬于同一個地區商店的行儲存在同一個分區中的方式來分割表，可以使用下面的“CREATETABLE”語句。
mysql> create table employees (
-> id int not null,
-> fname varchar(22),
-> lname varchar(22),
-> hired date not null default '1970-01-01',
-> separated date not null default '9999-12-31',
-> job_code int ,
-> store_id int)
-> partition by list(store_id)(
-> partition pp1 values in (3,5,6,9,17), #store_id為(3,5,6,9,17)存放在pp1分區，以此類推
-> partition pp2 values in (1,2,10,11,19,20),
-> partition pp3 values in (4,12,13,14,18),
-> partition pp4 values in (7,8,15,16)
-> );
Query OK, 0 rows affected (0.03 sec)
//插入資料測試
mysql> insert into employees values(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 4);
Query OK, 1 row affected (0.00 sec)
//插入store_id為4的資料
mysql> select * from employees partition(pp3);
+-----+-------+----------+------------+------------+----------+----------+
| id | fname | lname | hired | separated | job_code | store_id |
+-----+-------+----------+------------+------------+----------+----------+
| 224 | Linus | Torvalds | 2002-05-01 | 2004-10-12 | 42 | 4 |
+-----+-------+----------+------------+------------+----------+----------+
1 rows in set (0.00 sec)
要點：如果試圖插入列值不在分區值清單中的一行時，那麼“INSERT”查詢将失敗并報錯。例如，假定LIST分區的采用上面的方案，下面的插入将失敗。
mysql> mysql> insert into employees values(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);
ERROR 1526 (HY000): Table has no partition for value 21
這是因為“store_id”列值21不能在用于定義分區的值清單中找到。要重點注意的是，LIST分區沒有類似如“VALUES LESS THAN MAXVALUE”這樣的包含其他值在内的定義。将要比對的任何值都必須在值清單中找到。
HASH分區
這種模式允許DBA通過對表的一個或多個列的Hash Key進行計算，最後通過這個Hash碼不同數值對應的資料區域進行分區。 hash分區的目的是将資料均勻的分布到預先定義的各個分區中，保證各分區的資料量大緻一緻。在RANGE和LIST分區中，必須明确指定一個給定的列值或列值集合應該儲存在哪個分區中；而在HASH分區中，MYSQL自動完成這些工作，使用者所要定一個列值或者表達式，以及指定被分區的表将要被分割成的分區數量。
mysql> create table t_hash (
-> a int,b datetime)
-> partition by hash(year(b))
-> partitions 4; #表示為4個分區
Query OK, 0 rows affected (0.02 sec)
hash的分區函數頁需要傳回一個整數值。partitions子句中的值是一個非負整數，不加的partitions子句的話，預設為分區數為1。
//插入資料
mysql> insert into t_hash values(1,'2020-05-21');
Query OK, 1 row affected (0.01 sec)
//檢視資料存放在那個分區裡面
mysql> select mod(year('2020-05-21'),4);
+---------------------------+
| mod(year('2020-05-21'),4) |
+---------------------------+
| 0 |
+---------------------------+
1 row in set (0.00 sec)
mysql> select * from information_schema.partitions where table_schema='qin' and table_name='t_hash'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: qin
TABLE_NAME: t_hash
PARTITION_NAME: p0
SUBPARTITION_NAME: NULL
PARTITION_ORDINAL_POSITION: 1
SUBPARTITION_ORDINAL_POSITION: NULL
PARTITION_METHOD: HASH
SUBPARTITION_METHOD: NULL
PARTITION_EXPRESSION: year(b)
SUBPARTITION_EXPRESSION: NULL
PARTITION_DESCRIPTION: NULL
TABLE_ROWS: 1
AVG_ROW_LENGTH: 16384
DATA_LENGTH: 16384
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: 0
DATA_FREE: 0
CREATE_TIME: 2021-01-22 21:30:08
UPDATE_TIME: 2021-01-22 21:32:34
CHECK_TIME: NULL
CHECKSUM: NULL
PARTITION_COMMENT:
NODEGROUP: default
TABLESPACE_NAME: NULL
*************************** 2. row ***************************
.....省略
可以看到P2分區有一條記錄。目前這個例子并不能把資料均勻的分布到各個分區，因為按照YEAR函數進行的，該值本身是離散的。如果對連續的值進行HASH分區，如自增長的主鍵，則可以較好地将資料平均分布。
//檢視
mysql> select * from t_hash partition(p0);
+------+---------------------+
| a | b |
+------+---------------------+
| 1 | 2020-05-21 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)
KEY分區
key分區和hash分區相似，不同在于hash分區是使用者自定義函數進行分區，key分區使用mysql資料庫提供的函數進行分區，NDB cluster使用MD5函數來分區，對于其他存儲引擎mysql使用内部的hash函數。
mysql> create table t_key (
-> a int ,b datetime)
-> partition by key(b)
-> partitions 4;
Query OK, 0 rows affected (0.02 sec)
COLUMNS分區
mysql-5.5開始支援COLUMNS分區，可視為RANGE和LIST分區的進化，COLUMNS分區可以直接使用非整形資料進行分區。COLUMNS分區支援以下資料類型： 所有整形，如INT SMALLINTTINYINT BIGINT。FLOAT和DECIMAL則不支援。 日期類型，如DATE和DATETIME。其餘日期類型不支援。
字元串類型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT類型不支援。 COLUMNS可
以使用多個列進行分區。
總結
mysql分表和分區有什麼差別呢
1.實作方式上
a）mysql的分表是真正的分表，一張表分成很多表後，每一個小表都是完整的一張表，都對應三個檔案，一個.MYD資料檔案，.MYI索引檔案，.frm表結構檔案。
b）分區不一樣，一張大表進行分區後，他還是一張表，不會變成二張表，但是他存放資料的區塊變多了。
2.資料處理上
a）分表後，資料都是存放在分表裡，總表隻是一個外殼，存取資料發生在一個一個的分表裡面。
b）分區呢，不存在分表的概念，分區隻不過把存放資料的檔案分成了許多小塊，分區後的表呢，還是一張表，資料處理還是由自己來完成。
3. 提高性能上
a）分表後，單表的并發能力提高了，磁盤I/O性能也提高了。并發能力為什麼提高了呢，因為查尋一次所花的時間變短了，如果出現高并發的話，總表可以根據不同的查詢，将并發壓力分到不同
的小表裡面。
b）mysql提出了分區的概念，主要是想突破磁盤I/O瓶頸，想提高磁盤的讀寫能力，來增加mysql性能。在這一點上，分區和分表的測重點不同，分表重點是存取資料時，如何提高mysql并發能力上；而分區呢，如何突破磁盤的讀寫能力，進而達到提高mysql性能的目的。
4.實作的難易度上
a）分表的方法有很多，用merge來分表，是最簡單的一種方式。這種方式跟分區難易度差不多，并且對程式代碼來說可以做到透明的。如果是用其他分表方式就比分區麻煩了。
b）分區實作是比較簡單的，建立分區表，根建平常的表沒什麼差別，并且對開代碼端來說是透明的。
mysql分表和分區有什麼聯系？
- 都能提高mysql的性高，在高并發狀态下都有一個良好的表現。
- 分表和分區不沖突，可以互相配合的，對于那些大通路量，并且表資料比較多的表，我們可以采取分表和分區結合的方式，通路量不大，但是表資料很多的表，我們可以采取分區的方式等。
- 分表技術是比較麻煩的，需要手動去建立子表，app服務端讀寫時候需要計算子表名。采用merge好一些，但也要建立子表和配置子表間的union關系。
- 表分區相對于分表，操作友善，不需要建立子表。