二、總體感受：
使用了mysql的分區(qū)，覺得最不爽的是mysql partition 限制：
A UNIQUE INDEX must include all columns in the table's partitioning function
A PRIMARY KEY must include all columns in the table's partitioning function
意思就是：用于分區(qū)的column 必須是主鍵列，或者主鍵的其中幾個(gè)列，或者是唯一鍵列。無論創(chuàng)建何種類型的分區(qū)，如果表中存在主鍵或唯一索引時(shí)，分區(qū)列必須是唯一索引的一個(gè)組成部分。也不清楚作者這樣設(shè)計(jì)的初衷是什么。比如以下的寫法就是有語法錯(cuò)誤：
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;
這樣會(huì)大大限制mysql分區(qū)使用范圍。
三、mysql 分區(qū)的種類
1.range 分區(qū)，頻繁使用。
基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。
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 分區(qū)，比較少使用。類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來進(jìn)行選擇。
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)
);
注意看上面，都是沒有主鍵，沒有唯一鍵的。
3.HASH分區(qū)，頻繁使用：基于用戶定義的表達(dá)式的返回值來進(jìn)行選擇的分區(qū)，該表達(dá)式使用將要插入到表中的這些行的列值進(jìn)行計(jì)算。這個(gè)函數(shù)可以包含MySQL 中有效的、產(chǎn)生非負(fù)整數(shù)值的任何表達(dá)式。
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分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。
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;
上面兩個(gè)例子你會(huì)覺得很奇怪，都沒有指定分區(qū)列。mysql默認(rèn)就使用了唯一鍵來做了分區(qū)。
四、表分區(qū)的優(yōu)點(diǎn)
1）當(dāng)數(shù)據(jù)量很大(過T)時(shí)，肯定不能把數(shù)據(jù)再如到內(nèi)存中，這樣查詢一個(gè)或一定范圍的item是很耗時(shí)。另外一般這情況下，歷史數(shù)據(jù)或不常訪問的數(shù)據(jù)占很大部分，最新或熱點(diǎn)數(shù)據(jù)占的比例不是很大。這時(shí)可以根據(jù)有些條件進(jìn)行表分區(qū)。
2）分區(qū)表的更易管理，比如刪除過去某一時(shí)間的歷史數(shù)據(jù)，直接執(zhí)行truncate，或者狠點(diǎn)drop整個(gè)分區(qū)，這比detele刪除效率更高
3）當(dāng)數(shù)據(jù)量很大，或者將來很大的，但單塊磁盤的容量不夠，或者想提升IO效率的時(shí)候，可以把沒分區(qū)中的子分區(qū)掛載到不同的磁盤上。
4）使用分區(qū)表可避免某些特殊的瓶頸，例如Innodb的單個(gè)索引的互斥訪問..
5）單個(gè)分區(qū)表的備份很恢復(fù)會(huì)更有效率，在某些場(chǎng)景下
6）涉及到例如SUM()和COUNT()這樣聚合函數(shù)的查詢，可以很容易地進(jìn)行并行處理。
這種查詢的一個(gè)簡(jiǎn)單例子如
“SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。
通過“并行”，這意味著該查詢可以在每個(gè)分區(qū)上同時(shí)進(jìn)行，最終結(jié)果只需通過總計(jì)所有分區(qū)得到的結(jié)果。
五、表分區(qū)的查詢優(yōu)化
表分區(qū)了，查詢where必須帶上分區(qū)鍵，否則使用不到分區(qū)的好處了。我們來看下例子：
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 導(dǎo)入數(shù)據(jù)到 no_part_tab
insert into no_part_tab select * from part_tab;創(chuàng)建了2個(gè)表，數(shù)據(jù)都是800萬。
一個(gè)表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)
查詢時(shí)間和掃描的行數(shù)，高下可判。
六、mysql分區(qū)操作，在mysql8測(cè)試通過
| 序號(hào) |
常見操作 |
舉例 |
備注 |
| 1 |
刪除分區(qū) |
1) aher table emp drop partition p1;
2) 一次性刪除各個(gè)區(qū)：alter table emp drop partition p1,p2;
3) 刪除表的所有分區(qū):Alter table emp remove partitioning; |
1)不可以刪除hash或者kev分區(qū)。
2)刪除分區(qū)會(huì)刪除數(shù)據(jù)，但是刪除表的所有分區(qū)--不會(huì)丟失數(shù)據(jù)（驗(yàn)證ok) |
| 2 |
增加分區(qū) |
alter table emp add partition (partition p1 values less than (24));
alter table emp add partition partition p3 values in (40)); |
1)增加分區(qū)的值只能增加，不能比現(xiàn)在所擁有的分區(qū)值低 |
| 3 |
分解分區(qū) |
alter table emp reorganize partition p2 into
(partition p1 values less than (6),
partition p2 values less than (16)); |
reorganize partition關(guān)鍵字可以對(duì)表的部分分區(qū)或全部分區(qū)進(jìn)行修
改，并且不會(huì)丟失數(shù)據(jù)。分解前后分區(qū)的整體范圍應(yīng)該一致。 |
| 4 |
合并分區(qū) |
alter table emp reorganize partition p1,p3 into (partition p1 values less than (1000)); |
不會(huì)丟失數(shù)據(jù) |
| 5 |
重新定義分區(qū) |
重新定義Hash分區(qū)：Alter table emp partition by hash(salary) partitions 7;
重新定義Range分區(qū)：
Alter table emp partition by range(id) (partition p1 values less than (2000), partition p2 values less than (4000)); |
相當(dāng)于刪除重建。 |
當(dāng)前名稱：mysql8.0.17分區(qū)特性測(cè)試
文章來源：http://weahome.cn/article/pssjhg.html
重慶分公司
重慶分公司
