MySQL的分區(qū)（一）

發(fā)布時(shí)間：2020-08-11 19:14:47 來(lái)源：ITPUB博客閱讀：227 作者：to_be_Dba 欄目：MySQL數(shù)據(jù)庫(kù)

mysql支持范圍分區(qū)（range）、列表分區(qū)（list）、column分區(qū)、哈希分區(qū)（hash）、key分區(qū)、字段列表分區(qū)等

以timestamp類(lèi)型字段作為分區(qū)鍵進(jìn)行范圍分區(qū)，有兩種方式：

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范圍分區(qū)表只能使用上面兩種格式，使用to_days可能觸發(fā)bug。

date類(lèi)型的分區(qū)：

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

);

使用列表分區(qū)的實(shí)例：

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關(guān)鍵字，可以在插入多條數(shù)據(jù)時(shí)忽略沒(méi)有匹配分區(qū)的數(shù)據(jù)，不報(bào)錯(cuò)：

mysql> CREATE TABLE h3 (

-> 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 h3 VALUES (3, 5);

ERROR 1525 (HY000): Table has no partition for value 3

mysql> INSERT IGNORE INTO h3 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 h3;

+------+------+

| c1 | c2 |

+------+------+

| 7 | 5 |

| 1 | 9 |

| 2 | 5 |

+------+------+

3 rows in set (0.00 sec)

可以在定義表時(shí)指定分區(qū)屬性，也可以使用alter table進(jìn)行修改：

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不支持表達(dá)式，僅支持一或多個(gè)列名。

由于字符集character sets和collations的排列順序不同，當(dāng)進(jìn)行數(shù)據(jù)遷移或者修改庫(kù)、表、列的字符集時(shí)，

有可能因此而出現(xiàn)報(bào)錯(cuò)。比如對(duì)于大小寫(xiě)不敏感的collation，and排列順序在Andersen之前，

但對(duì)于大小寫(xiě)敏感的collation就不是。

使用多個(gè)字段分區(qū)時(shí)，是按照字段順序進(jìn)行比較的，以下語(yǔ)句正確：

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)

);

但不建議使用此分區(qū)方式。

哈希分區(qū)實(shí)例：

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;

限制：

分區(qū)鍵必須是數(shù)字類(lèi)型（integer)

分區(qū)鍵必須是主鍵和所有唯一鍵的一部分

需要指定分區(qū)數(shù)，否則默認(rèn)是1

數(shù)據(jù)放入哪個(gè)分區(qū)是固定且可以提前計(jì)算的。比如：

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)

PARTITION BY HASH( YEAR(col3) )

PARTITIONS 4;

如果col3的值為'2005-09-15',數(shù)據(jù)放入哪個(gè)分區(qū)的計(jì)算公式為：

MOD(YEAR('2005-09-01'),4)

= MOD(2005,4)

= 1

即放入第一個(gè)分區(qū)

線(xiàn)性分區(qū)（LINER HASH PARTITION），與普通hash分區(qū)的區(qū)別是其采用線(xiàn)性二次冪算法，公式為：

V = POWER(2, CEILING(LOG(2, num)))

語(yǔ)句舉例：

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分區(qū)

與hash分區(qū)類(lèi)似，只是算法不同。對(duì)于NDB cluster，使用md5()函數(shù)，其他引擎使用類(lèi)似password（）函數(shù)進(jìn)行分區(qū)。

舉例：

CREATE TABLE tm1 (

s1 CHAR(32) PRIMARY KEY

)

PARTITION BY KEY(s1)

PARTITIONS 10;

key鍵必須是主鍵的一部分。當(dāng)存在主鍵或非空唯一鍵時(shí)，也可以為空。

復(fù)合分區(qū)/子分區(qū)

mysql5.7中分區(qū)類(lèi)型為range或list，子分區(qū)可以使用hash或key分區(qū)。

有以下限制：

每個(gè)分區(qū)中的子分區(qū)數(shù)量必須一樣；

子分區(qū)名稱(chēng)不能重復(fù)；

################################

分區(qū)表中NULL的處理

在range分區(qū)表中，NULL被認(rèn)為小于所有值，被存放在第一個(gè)分區(qū)中；

LIST分區(qū)表中，必須指定某個(gè)分區(qū)包含NULL值；

在hash或key分區(qū)表中，NULL被當(dāng)作0處理。

#############################

分區(qū)管理

range和list分區(qū)表可以進(jìn)行分區(qū)的增、刪、合并、拆分操作

增刪分區(qū)的邏輯和寫(xiě)法與oracle基本一致。拆分/分裂分區(qū)的語(yǔ)法：

ALTER TABLE members

REORGANIZE PARTITION p0 INTO (

PARTITION n0 VALUES LESS THAN (1970),

PARTITION n1 VALUES LESS THAN (1980)

);

hash和key分區(qū)表不能進(jìn)行刪除，但可以合并，如：

ALTER TABLE clients COALESCE PARTITION 4;

其中4是待刪除的分區(qū)數(shù)量。

添加6個(gè)分區(qū)分區(qū)，如：

ALTER TABLE clients ADD PARTITION PARTITIONS 6;

分區(qū)交換（用于range分區(qū)或子分區(qū)）

類(lèi)似oracle，對(duì)分區(qū)表的某個(gè)分區(qū)與普通表進(jìn)行交換。例如：

ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt;

限制：

表結(jié)構(gòu)一致，包含索引一致

普通表不包含外鍵，也不被其他表做外鍵引用

普通表數(shù)據(jù)的范圍沒(méi)有超過(guò)分區(qū)表定義

如果是innodb引擎，要求row格式一致

未使用data directory選項(xiàng)

需要表的增刪改查權(quán)限

該過(guò)程不會(huì)觸發(fā)觸發(fā)器

交換時(shí)自增值會(huì)被重置

ignore選項(xiàng)無(wú)效

使用without validation選項(xiàng)時(shí)，不再逐條校驗(yàn)數(shù)據(jù)

分區(qū)重建，相當(dāng)于刪除所有數(shù)據(jù)再重新插入：

ALTER TABLE t1 REBUILD PARTITION p0, p1;

##############################################

分區(qū)的維護(hù)

優(yōu)化分區(qū)，用于對(duì)大量數(shù)據(jù)進(jìn)行修改或刪除操作后，可以回收空間并整理碎片

相當(dāng)于運(yùn)行了check partition、analyze partition、repair partition。

可以對(duì)多個(gè)分區(qū)一次性執(zhí)行：

alter table t1 optimize partition p0,p1;

注意：innodb不支持單個(gè)分區(qū)的optimize操作，會(huì)升級(jí)為對(duì)全表的重建，如：

mysql> alter table t4 optimize partition p1;

+-------+----------+----------+---------------------------------------------------------------------------------------------+

+-------+----------+----------+---------------------------------------------------------------------------------------------+

| tl.t4 | optimize | status | OK |

+-------+----------+----------+---------------------------------------------------------------------------------------------+

2 rows in set (4.00 sec)

可以使用ALTER TABLE ... REBUILD PARTITION和ALTER TABLE ... ANALYZE PARTITION 進(jìn)行替代，避免此問(wèn)題。

分析分區(qū)，讀取和存儲(chǔ)分區(qū)的關(guān)鍵屬性信息：

alter table t1 analyze partition p3;

修復(fù)分區(qū)

alter table t1 repair partition p0,p1;

正常執(zhí)行時(shí)如果有重復(fù)鍵值會(huì)報(bào)錯(cuò)；

從5.7.2開(kāi)始，可以使用alter ignore table選項(xiàng)，出現(xiàn)重復(fù)值時(shí)自動(dòng)刪除

檢查分區(qū)

alter table trb3 check partition p1;

檢查p1分區(qū)的數(shù)據(jù)和索引是否有中斷。如果有重復(fù)值，則check操作會(huì)報(bào)錯(cuò)。

從5.7.2開(kāi)始，可以使用alter ignore table選項(xiàng)，出現(xiàn)重復(fù)值時(shí)報(bào)告出來(lái)。

########################################################

獲取分區(qū)信息

show create table

show table status =>是否分區(qū)

information_schema.partitions

explain select

舉例：

mysql> show table status from tl like 't%'

-> ;

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+

| 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 | | |

向AI問(wèn)一下細(xì)節(jié)

MySQL的分區(qū)（一）

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽