MySQL的分割槽(一)
mysql支援範圍分割槽(range)、列表分割槽(list)、column分割槽、雜湊分割槽(hash)、key分割槽、欄位列表分割槽等
以timestamp型別欄位作為分割槽鍵進行範圍分割槽,有兩種方式:
CREATE TABLE quarterly_report_status (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
在mysql5.7中timestamp範圍分割槽表只能使用上面兩種格式,使用to_days可能觸發bug。
date型別的分割槽:
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
PARTITION p0 VALUES LESS THAN ('1960-01-01'),
PARTITION p1 VALUES LESS THAN ('1970-01-01'),
PARTITION p2 VALUES LESS THAN ('1980-01-01'),
PARTITION p3 VALUES LESS THAN ('1990-01-01'),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
使用列表分割槽的例項:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
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 pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
使用ignore關鍵字,可以在插入多條資料時忽略沒有匹配分割槽的資料,不報錯:
mysql> CREATE TABLE h2 (
-> c1 INT,
-> c2 INT
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (1, 4, 7),
-> PARTITION p1 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO h2 VALUES (3, 5);
ERROR 1525 (HY000): Table has no partition for value 3
mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected (0.00 sec)
Records: 5 Duplicates: 2 Warnings: 0
mysql> SELECT * FROM h2;
+------+------+
| c1 | c2 |
+------+------+
| 7 | 5 |
| 1 | 9 |
| 2 | 5 |
+------+------+
3 rows in set (0.00 sec)
可以在定義表時指定分割槽屬性,也可以使用alter table進行修改:
ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname) (
PARTITION p0 VALUES LESS THAN ('g'),
PARTITION p1 VALUES LESS THAN ('m'),
PARTITION p2 VALUES LESS THAN ('t'),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
range columns不支援表示式,僅支援一或多個列名。
由於字符集character sets和collations的排列順序不同,當進行資料遷移或者修改庫、表、列的字符集時,
有可能因此而出現報錯。比如對於大小寫不敏感的collation,and排列順序在Andersen之前,
但對於大小寫敏感的collation就不是。
使用多個欄位分割槽時,是按照欄位順序進行比較的,以下語句正確:
CREATE TABLE rc4 (
a INT,
b INT,
c INT
)
PARTITION BY RANGE COLUMNS(a,b,c) (
PARTITION p0 VALUES LESS THAN (0,25,50),
PARTITION p1 VALUES LESS THAN (10,20,100),
PARTITION p2 VALUES LESS THAN (10,30,50)
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);
但不建議使用此分割槽方式。
雜湊分割槽例項:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
限制:
分割槽鍵必須是數字型別(integer)
分割槽鍵必須是主鍵和所有唯一鍵的一部分
需要指定分割槽數,否則預設是1
資料放入哪個分割槽是固定且可以提前計算的。比如:
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;
如果col3的值為'2005-09-15',資料放入哪個分割槽的計算公式為:
MOD(YEAR('2005-09-01'),4)
= MOD(2005,4)
= 1
即放入第一個分割槽
線性分割槽(LINER HASH PARTITION),與普通hash分割槽的區別是其採用線性二次冪演算法,公式為:
V = POWER(2, CEILING(LOG(2, num)))
語句舉例:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;
key分割槽
與hash分割槽類似,只是演算法不同。對於NDB cluster,使用md5()函式,其他引擎使用類似password()函式進行分割槽。
舉例:
CREATE TABLE tm1 (
s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;
key鍵必須是主鍵的一部分。當存在主鍵或非空唯一鍵時,也可以為空。
複合分割槽/子分割槽
mysql5.7中分割槽型別為range或list,子分割槽可以使用hash或key分割槽。
有以下限制:
每個分割槽中的子分割槽數量必須一樣;
子分割槽名稱不能重複;
################################
分割槽表中NULL的處理
在range分割槽表中,NULL被認為小於所有值,被存放在第一個分割槽中;
LIST分割槽表中,必須指定某個分割槽包含NULL值;
在hash或key分割槽表中,NULL被當作0處理。
#############################
分割槽管理
range和list分割槽表可以進行分割槽的增、刪、合併、拆分操作
增刪分割槽的邏輯和寫法與oracle基本一致。拆分/分裂分割槽的語法:
ALTER TABLE members
REORGANIZE PARTITION p0 INTO (
PARTITION n0 VALUES LESS THAN (1970),
PARTITION n1 VALUES LESS THAN (1980)
);
hash和key分割槽表不能進行刪除,但可以合併,如:
ALTER TABLE clients COALESCE PARTITION 4;
其中4是待刪除的分割槽數量。
新增6個分割槽分割槽,如:
ALTER TABLE clients ADD PARTITION PARTITIONS 6;
分割槽交換(用於range分割槽或子分割槽)
類似oracle,對分割槽表的某個分割槽與普通表進行交換。例如:
ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt;
限制:
表結構一致,包含索引一致
普通表不包含外來鍵,也不被其他表做外來鍵引用
普通表資料的範圍沒有超過分割槽表定義
如果是innodb引擎,要求row格式一致
未使用data directory選項
需要表的增刪改查許可權
該過程不會觸發觸發器
交換時自增值會被重置
ignore選項無效
使用without validation選項時,不再逐條校驗資料
分割槽重建,相當於刪除所有資料再重新插入:
ALTER TABLE t1 REBUILD PARTITION p0, p1;
##############################################
分割槽的維護
優化分割槽,用於對大量資料進行修改或刪除操作後,可以回收空間並整理碎片
相當於執行了check partition、analyze partition、repair partition。
可以對多個分割槽一次性執行:
alter table t1 optimize partition p0,p1;
注意:innodb不支援單個分割槽的optimize操作,會升級為對全表的重建,如:
mysql> alter table t4 optimize partition p1;
+-------+----------+----------+---------------------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------+----------+----------+---------------------------------------------------------------------------------------------+
| tl.t4 | optimize | note | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. |
| tl.t4 | optimize | status | OK |
+-------+----------+----------+---------------------------------------------------------------------------------------------+
2 rows in set (4.00 sec)
可以使用ALTER TABLE ... REBUILD PARTITION和ALTER TABLE ... ANALYZE PARTITION 進行替代,避免此問題。
分析分割槽,讀取和儲存分割槽的關鍵屬性資訊:
alter table t1 analyze partition p3;
修復分割槽
alter table t1 repair partition p0,p1;
正常執行時如果有重複鍵值會報錯;
從5.7.2開始,可以使用alter ignore table選項,出現重複值時自動刪除
檢查分割槽
alter table trb3 check partition p1;
檢查p1分割槽的資料和索引是否有中斷。如果有重複值,則check操作會報錯。
從5.7.2開始,可以使用alter ignore table選項,出現重複值時報告出來。
########################################################
獲取分割槽資訊
show create table
show table status =>是否分割槽
information_schema.partitions
explain select
舉例:
mysql> show table status from tl like 't%'
-> ;
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| t1 | InnoDB | 10 | Dynamic | 0 | 0 | 65536 | 0 | 65536 | 0 | 1 | 2020-06-18 15:35:43 | NULL | NULL | utf8_general_ci | NULL | partitioned | |
| t2 | InnoDB | 10 | Dynamic | 12 | 6826 | 81920 | 0 | 81920 | 0 | 30 | 2020-06-18 15:57:08 | 2020-06-18 16:01:59 | NULL | utf8_general_ci | NULL | partitioned | |
| t3 | InnoDB | 10 | Dynamic | 9 | 1820 | 16384 | 0 | 16384 | 0 | 1 | 2020-06-18 15:55:24 | 2020-06-18 16:01:59 | NULL | utf8_general_ci | NULL | | |
| t4 | InnoDB | 10 | Dynamic | 21 | 3900 | 81920 | 0 | 81920 | 0 | 30 | 2020-06-19 18:23:20 | NULL | NULL | utf8_general_ci | NULL | partitioned | |
| t5 | InnoDB | 10 | Dynamic | 0 | 0 | 49152 | 0 | 49152 | 0 | 30 | 2020-06-18 16:20:24 | 2020-06-18 16:27:30 | NULL | utf8_general_ci | NULL | partitioned | |
| tt | InnoDB | 10 | Dynamic | 3 | 5461 | 16384 | 0 | 0 | 0 | NULL | 2020-06-17 23:23:00 | 2020-06-17 23:28:35 | NULL | utf8_general_ci | NULL | | |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26451536/viewspace-2699580/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql 分割槽MySql
- MySQL分割槽MySql
- MySQL的分割槽(二)MySql
- MySQL分割槽表的分割槽原理和優缺點MySql
- 理解MySQL分割槽MySql
- 搞懂MySQL分割槽MySql
- 【MYSQL】 分割槽表MySql
- MySql建立分割槽MySql
- MySQL 子分割槽MySql
- MySQL KEY分割槽MySql
- MySQL HASH分割槽MySql
- MySQL COLUMNS分割槽MySql
- MySQL LIST分割槽MySql
- MySQL RANGE分割槽MySql
- MySQL 分割槽表MySql
- MySQL分割槽表MySql
- mysql分割槽nullMySqlNull
- mysql 分割槽示例MySql
- Mysql 的分割槽型別MySql型別
- MySQL的List分割槽表MySql
- MySql資料分割槽操作之新增分割槽操作MySql
- MySQL 分割槽表 partition線上修改分割槽欄位MySql
- MySQL 分割槽表探索MySql
- mysql 分表 分割槽MySql
- MySQL分割槽學習MySql
- MySQL 分割槽建索引MySql索引
- MySQL分割槽介紹MySql
- mysql 分割槽表用法MySql
- MySQL表分割槽管理MySql
- MySQL分割槽的實現方式MySql
- mysql的分割槽和分表MySql
- mysql的分割槽與分表MySql
- 將mysql非分割槽錶轉換為分割槽表MySql
- MySQL分割槽如何遷移MySql
- mysql 進行表分割槽MySql
- Mysql表分割槽實現MySql
- mysql分割槽表筆記MySql筆記
- MySQL入門--分割槽表MySql