一、下面所有的測試基于mysql 8.0.17版本。 elcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 34 Server version: 8.0.17 Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. Server version: 8.0.17 Source distribution
CREATE TABLE t1 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2) ) PARTITION BY HASH(col3) PARTITIONS 4; CREATE TABLE t2 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1), UNIQUE KEY (col3) ) PARTITION BY HASH(col1 + col3) PARTITIONS 4; 這樣會大大限制mysql分區使用范圍。
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 NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21) ); 2. list 分區，比較少使用。類似于按RANGE分區，區別在于LIST分區是基于列值匹配一個離散值集合中的某個值來進行選擇。
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) ); 注意看上面，都是沒有主鍵，沒有唯一鍵的。
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( YEAR(hired) ) PARTITIONS 4; 4.KEY分區：類似于按HASH分區，區別在于KEY分區只支持計算一列或多列，且MySQL服務器提供其自身的哈希函數。必須有一列或多列包含整數值。
CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) ) PARTITION BY KEY() PARTITIONS 2; CREATE TABLE k1 ( id INT NOT NULL, name VARCHAR(20), UNIQUE KEY (id) )PARTITION BY KEY() PARTITIONS 2; 上面兩個例子你會覺得很奇怪，都沒有指定分區列。mysql默認就使用了唯一鍵來做了分區。
CREATE TABLE part_tab (c1 int default NULL, c2 varchar(30) default NULL, c3 date not null) 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) ); CREATE TABLE no_part_tab(c1 int default NULL, c2 varchar(30) default NULL, c3 date not null); drop procedure load_part_tab; delimiter $$ CREATE PROCEDURE load_part_tab() begin declare v int default 0; while v < 8000000 do insert into part_tab values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652)); set v = v + 1; end while; end;$$ delimiter ; call load_part_tab(); //從 part_tab 導入數據到 no_part_tab insert into no_part_tab select * from part_tab; 創建了2個表，數據都是800萬。
一個表no_part_tab的大小是428M。在這里另外
mysql> explain select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31'; +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | part_tab | p1 | ALL | NULL | NULL | NULL | NULL | 796215 | 11.11 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31'; +----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | no_part_tab | NULL | ALL | NULL | NULL | NULL | NULL | 7773613 | 11.11 | Using where | +----+-------------+-------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31'; +----------+ | count(*) | +----------+ | 795181 | +----------+ 1 row in set (0.39 sec) mysql> select count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31'; +----------+ | count(*) | +----------+ | 795181 | +----------+ 1 row in set (2.94 sec) 查詢時間和掃描的行數，高下可判。
六、mysql分區操作，在mysql8測試通過 序號 常見操作 舉例 備注 1 刪除分區 1) aher table emp drop partition p1; 2) 一次性刪除各個區：alter table emp drop partition p1,p2; 3) 刪除表的所有分區:Alter table emp remove partitioning; 1)不可以刪除hash或者kev分區。 2)刪除分區會刪除數據，但是刪除表的所有分區--不會丟失數據（驗證ok) 2 增加分區 alter table emp add partition (partition p1 values less than (24)); alter table emp add partition partition p3 values in (40)); 1)增加分區的值只能增加，不能比現在所擁有的分區值低 3 分解分區 alter table emp reorganize partition p2 into (partition p1 values less than (6), partition p2 values less than (16)); reorganize partition關鍵字可以對表的部分分區或全部分區進行修 改，并且不會丟失數據。分解前后分區的整體范圍應該一致。 4 合并分區 alter table emp reorganize partition p1,p3 into (partition p1 values less than (1000)); 不會丟失數據 5 重新定義分區 重新定義Hash分區：Alter table emp partition by hash(salary) partitions 7; 重新定義Range分區： Alter table emp partition by range(id) (partition p1 values less than (2000), partition p2 values less than (4000)); 相當于刪除重建。
