理解MySQL分割槽

sayWhat_sayHello發表於2019-04-03

文章目錄

引言

學習一個新知識點的時候，首先要明白的是他是什麼，優點和缺點。再具體到怎麼去使用。所以本文的順序大致如上。

MYSQL分割槽是什麼？

在邏輯上，表的使用不受影響，但是在物理上(體現在檔案上)，原本一個表對應一個資料檔案的，但是分割槽後一個表對應了幾個資料檔案。例如我們用以下語句建立一個表：

mysql> create table test(
    -> id int auto_increment primary key,
    -> message varchar(255)
    -> );
Query OK, 0 rows affected (0.14 sec)

在系統中便會為我們建立一個檔案：

[root@VM_0_6_centos test]# ls
test.ibd

修改資料庫使用分割槽(具體的語句稍後再解釋)：

mysql> alter table test partition by hash(id) partitions 4;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

我們會發現在系統中，檔案發生了一些改變：

[root@VM_0_6_centos test]#  ls
test#P#p0.ibd  test#P#p1.ibd  test#P#p2.ibd  test#P#p3.ibd

也就是在物理上發生了一些變化，然而在邏輯上的使用不會改變，例如查詢表：

mysql> select * from test where id = 1;
Empty set (0.01 sec)

在使用者的邏輯上並未改變，但是對資料庫來說，原本一個表有100行，經過HASH分割槽後，也許均勻點分4個分割槽也就是一個分割槽只有25行，只需要定位到對應的分割槽後檢索這25條資料就行了。

MySQL分割槽的優點

分割槽使得在一個表中儲存比單個磁碟或檔案系統分割槽中儲存更多的資料成為可能。不知道可不可以這麼理解，假如每個檔案上限100M，那麼分割槽4個，就可以存400M！
原本一些有用的資料無效了，通過移除相應的分割槽就可以快捷的移除相應的資料。同樣的為特別的資料新增分割槽也是非常便捷的。
優化查詢。在使用分割槽指定列進行查詢時，資料庫可以根據指定列定位到相應的物理檔案進行查詢。另外MySQL同樣支援顯式的分割槽查詢，例如：SELECT * FROM t PARTITION (p0,p1) WHERE c < 5只會去查詢p0,p1分割槽。

MySQL分割槽的缺點

分割槽表，分割槽鍵設計不太靈活，如果不走分割槽鍵，很容易出現全表鎖。
一旦資料量併發量上來，如果在分割槽表實施關聯，就是一個災難。

MySQL分割槽型別

RANGE分割槽
LIST分割槽
HASH分割槽
KEY分割槽
COLUMNS分割槽

表上的每個唯一鍵必須使用表的分割槽表示式中的每一列！ 這句話是分割槽鍵設計的一個非常重要的原則，包括很多方面，所以這裡先不詳細解釋。

RANGE分割槽

RANGE分割槽代表的是範圍，區間要連續並且不能互相重疊，使用VALUES LESS THAN進行分割槽定義。以上面的test表的id為例進行分割槽:

mysql> alter table test partition by range(id)(
    -> partition p0 values less than (10),
    -> partition p1 values less than (20),
    -> partition p2 values less than maxvalue);
Query OK, 0 rows affected (0.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

這裡相當於把id分成了3個區間： <10的，10 ~ 20的，20 ~ 正整數最大值(相當於>=20的)。

我們看一下id=15的資料查詢計劃會發現：partition是p1！

mysql> explain select * from test where id = 15;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p1         | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

LIST分割槽

LIST分割槽會根據事先我們預定好的集合進行分割槽，例如我們如下修改分割槽：

mysql> alter table test partition by list(id)(
    -> partition p0 values in (1,2),
    -> partition p1 values in (3));
ERROR 1526 (HY000): Table has no partition for value 4

當id不在1,2,3中為4時，資料庫不知道該怎麼進行分割槽，所以會報錯。我們做一點修改：

mysql> alter table test partition by list(id%2)( partition p0 values in (0), partition p1 values in (1));
Query OK, 30 rows affected (0.48 sec)
Records: 30  Duplicates: 0  Warnings: 0

這樣我們便將表按id是否偶數進行了分割槽。

HASH分割槽

HASH分割槽的語法很簡單：

mysql> alter table test partition by hash(id) partitions 5;
Query OK, 30 rows affected (1.11 sec)
Records: 30  Duplicates: 0  Warnings: 0

上面這條語句代表使用的是求餘的hash演算法，當我們查詢id = 15時可以發現分割槽為 15%5 == 0，p0分割槽：

mysql> explain select * from test where id = 15;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p0         | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

取餘的演算法有點弊端在於當我們要新增新分割槽時需要對原本的分割槽進行重新hash。MySQL用線性hash作為一種優化，這裡大致講一下定位，例如我們查詢是15，分割槽個數還是5。那麼這種演算法如下：

先找比5大的2次方的冪V：也就是8。然後將id & (V - 1),也就是 15 & 7 = 7(設為N)。如果(N)7 >= 5(分割槽數)，那麼7(N) & （8(V)/2 - 1）= 3;

mysql> alter table test partition by linear hash(id) partitions 5;
Query OK, 30 rows affected (0.82 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> explain select * from test where id = 15;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p3         | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

如果我們把分割槽數改成6，同理：

先找比6大的2次方的冪V：也就是8。然後將id & (V - 1),也就是 15 & 7 = 7(設為N)。如果(N)7 >= 6(分割槽數)，那麼7(N) & （8(V)/2 - 1）= 3;

mysql> alter table test partition by linear hash(id) partitions 6;
Query OK, 30 rows affected (1.00 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> explain select * from test where id = 15;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p3         | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

linear hash在分割槽維護時相比直接hash要更加快，但是不如直接hash均勻。

KEY分割槽

HASH分割槽允許使用者自定義的表示式，而KEY分割槽不允許。和HASH分割槽不同，建立KEY分割槽時不指定分割槽鍵預設會使用主鍵作為分割槽鍵，在沒有主鍵的情況下使用非空唯一主鍵，否則會報錯。

mysql> alter table test partition by key();
Query OK, 30 rows affected (0.53 sec)
Records: 30  Duplicates: 0  Warnings: 0

COLUMN分割槽

COLUMN分為RANGE COLUMN 和 LIST COLUMN兩種， COLUMN分割槽支援所有整數型別、date和datetime型別、字元型別char,varchar,binary和varbinary；除此之外還支援多列分割槽。

在這裡我們不能直接通過test表來演示多列分割槽，因為在定義表的時候我們定義了主鍵id。而表上的每個唯一鍵必須使用表的分割槽表示式中的每一列！ 所以我們接下來用一個新的建表例子：

mysql> create table t1(
    -> a int,
    -> b int,
    -> c char(1))
    -> partition by range columns(a,c)(
    -> partition p0 values less than (10, 'k'),
    -> partition p1 values less than (20, 'r'),
    -> partition p2 values less than (maxvalue, 'z'));
Query OK, 0 rows affected (0.17 sec)

RANGE COLUMN是按元組進行比較的，就上面的例子來說會先比較a的值，如果a的值相同，這時候才會比較第二位，若還有更多的值，同樣是按這樣的道理進行比較。

mysql> alter table t1 partition by list columns(a,c)( 
			 partition p0 values in((1,'a'),(3,'c')),
			 partition p1 values in((2,'b')));
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

LIST COLUMN也和LIST大致相同，不過也支援多列。

子分割槽

子分割槽是對分割槽表中每個分割槽的再次分割，對RANGE和LIST分割槽進行HASH分割槽或者KEY分割槽。例如用回我們的test表進行舉例：

mysql> alter table test partition by range(id) 
    -> subpartition by hash(id)
    -> subpartitions 5
    -> ( partition p0 values less than (10),
    ->   partition p1 values less than (20),
    ->   partition p2 values less than maxvalue);
Query OK, 30 rows affected (1.44 sec)
Records: 30  Duplicates: 0  Warnings: 0

我們將按RANGE分為3個分割槽，然後在某個小分割槽內劃分HASH子分割槽，子分割槽大小為5。當我們查詢id = 15時，首先顯而易見的會到p1分割槽去，然後p1分割槽在進行取餘的雜湊，我們可以得到在子分割槽內為0。

mysql> explain select * from test where id = 15;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p1_p1sp0   | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

再給個例子：

mysql> explain select * from test where id = 29;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p2_p2sp4   | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

我們使用下列語句檢視這些子分割槽的分佈(id 從1 到 30)：

mysql> select PARTITION_NAME,SUBPARTITION_NAME,TABLE_ROWS from information_schema.PARTITIONS where TABLE_NAME = 'test';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          0 |
| p0             | p0sp1             |          2 |
| p0             | p0sp2             |          2 |
| p0             | p0sp3             |          2 |
| p0             | p0sp4             |          2 |
| p1             | p1sp0             |          2 |
| p1             | p1sp1             |          2 |
| p1             | p1sp2             |          2 |
| p1             | p1sp3             |          2 |
| p1             | p1sp4             |          2 |
| p2             | p2sp0             |          3 |
| p2             | p2sp1             |          2 |
| p2             | p2sp2             |          2 |
| p2             | p2sp3             |          2 |
| p2             | p2sp4             |          2 |
+----------------+-------------------+------------+
15 rows in set (0.00 sec)

這裡我個人感覺有點問題，按道理說第一行table_rows應該是1！因為當id = 5時：

mysql> explain select * from test where id = 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | p0_p0sp0   | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

分割槽管理

//todo

參考文獻

https://dev.mysql.com/doc/refman/8.0/en/partitioning-pruning.html
https://blog.csdn.net/w892824196/article/details/80227835
《深入淺出MySQL》

mysql 分割槽
2019-07-15
MySql
MySQL分割槽
2015-01-21
MySql
搞懂MySQL分割槽
2019-03-14
MySql
【MYSQL】分割槽表
2021-09-09
MySql
MySql建立分割槽
2022-04-12
MySql
MySQL 子分割槽
2016-11-07
MySql
MySQL KEY分割槽
2016-11-06
MySql
MySQL HASH分割槽
2016-11-05
MySql
MySQL COLUMNS分割槽
2016-11-04
MySql
MySQL LIST分割槽
2016-11-04
MySql
MySQL RANGE分割槽
2016-07-12
MySql
MySQL 分割槽表
2015-04-01
MySql
MySQL分割槽表
2015-06-24
MySql
mysql分割槽null
2015-09-23
MySqlNull
mysql 分割槽示例
2011-12-29
MySql
oracle表分割槽自我理解
2011-02-23
Oracle
MySql資料分割槽操作之新增分割槽操作
2021-09-09
MySql
MySQL 分割槽表 partition線上修改分割槽欄位
2013-12-06
MySql
理解 MySQL（4）：並行資料庫與分割槽(Partition)
2015-05-25
MySql並行資料庫
MySQL的分割槽（一）
2020-06-20
MySql
MySQL的分割槽（二）
2020-06-20
MySql
MySQL 分割槽表探索
2019-01-28
MySql
mysql 分表分割槽
2017-07-02
MySql
MySQL分割槽學習
2017-07-08
MySql
MySQL 分割槽建索引
2017-01-13
MySql索引
MySQL分割槽介紹
2016-07-06
MySql
mysql 分割槽表用法
2015-04-01
MySql
MySQL表分割槽管理
2014-10-24
MySql
MySQL分割槽表的分割槽原理和優缺點
2017-04-19
MySql
將mysql非分割槽錶轉換為分割槽表
2011-03-14
MySql
MySQL分割槽如何遷移
2019-03-11
MySql
mysql 進行表分割槽
2019-01-04
MySql
Mysql表分割槽實現
2020-10-25
MySql
Mysql 的分割槽型別
2021-12-09
MySql型別
mysql分割槽表筆記
2017-12-08
MySql筆記
MySQL入門--分割槽表
2017-01-18
MySql
MySQL分割槽（Partition）詳解
2013-12-16
MySql
mysql —— 分表分割槽（1）
2013-09-26
MySql