MySQL存儲(chǔ)引擎現(xiàn)狀及發(fā)展趨勢(shì)
MySQL官網(wǎng)有這樣一句話：
As of MySQL 5.7.17, the generic partitioning handler in the MySQL server is deprecated, and is removed in MySQL 8.0, when the storage engine used for a given table is expected to provide its own (“native”) partitioning handler. Currently, only the InnoDB and NDB storage engines do.
大意為MySQL 8.0中將會(huì)移除普通的分區(qū)而只支持存儲(chǔ)引擎內(nèi)置分區(qū)的方式旗国。
如下文所示，即為普通的分區(qū)方式：
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY HASH (uid) PARTITIONS 4 (
PARTITION p0
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2
DATA DIRECTORY = '/data4/data'
INDEX DIRECTORY = '/data5/idx',
PARTITION p3
DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);
這里我們可以有一個(gè)最基本的判斷邏輯注整，假如數(shù)據(jù)庫(kù)表沒有指定存儲(chǔ)引擎能曾，即可認(rèn)為是MySQL不建議使用的分區(qū)方式。當(dāng)在5.7版本中使用時(shí)肿轨，MySQL將提示：
The partition engine, used by table 'table_name', is deprecated and
will be removed in a future release. Please use native partitioning instead.
用戶在這時(shí)為數(shù)據(jù)庫(kù)表指定存儲(chǔ)引擎InnoDB或NDB即可寿冕。
ALTER TABLE table_name ENGINE = INNODB;
分區(qū)表的優(yōu)勢(shì)
- 分區(qū)使得可以將一個(gè)表中的更多數(shù)據(jù)存儲(chǔ)在單個(gè)磁盤或文件系統(tǒng)分區(qū)上。
- 由于滿足給定WHERE子句的數(shù)據(jù)只能存儲(chǔ)在一個(gè)或多個(gè)分區(qū)上椒袍，因此可以將搜索中的剩余分區(qū)自動(dòng)排除驼唱，可以大大優(yōu)化一些查詢。
- 此外驹暑，MySQL 5.7支持查詢的顯式分區(qū)選擇玫恳。 例如，SELECT * FROM t PARTITION（p0优俘，p1）WHERE c <5僅選擇與WHERE條件匹配的分區(qū)p0和p1中的那些行京办。 在這種情況下，MySQL不會(huì)檢查表t的任何其他分區(qū); 當(dāng)您已經(jīng)知道要檢查的分區(qū)或分區(qū)時(shí)帆焕，這可以大大加快查詢速度惭婿。
- 涉及聚合函數(shù)（如SUM（）和COUNT（）的查詢可以輕松并行化。
- 憑借擴(kuò)展數(shù)據(jù)實(shí)現(xiàn)更大的查詢吞吐量，可以查找多個(gè)磁盤审孽。
分區(qū)表的基本概念
對(duì)用戶而言县袱，分區(qū)表是一個(gè)獨(dú)立的邏輯表，但是底層由多個(gè)物理子表組成佑力。實(shí)現(xiàn)分區(qū)的代碼實(shí)際上是對(duì)一組底層表的句柄對(duì)象(Handler Object)的封裝式散。對(duì)分區(qū)表的請(qǐng)求，都會(huì)通過(guò)句柄對(duì)象轉(zhuǎn)化成對(duì)存儲(chǔ)引擎的接口調(diào)用打颤。所以分區(qū)對(duì)于SQL層來(lái)說(shuō)是一個(gè)完全封閉底層實(shí)現(xiàn)的黑盒子暴拄，對(duì)應(yīng)用是透明的，但是從底層的文件系統(tǒng)也就很容易發(fā)現(xiàn)编饺，每個(gè)分區(qū)表都有一個(gè)使用#分隔命名的表文件乖篷。
在下面的場(chǎng)景中，分區(qū)可以起到非常大的作用：
- 表非常大以至于無(wú)法全部放在內(nèi)存中透且，或者只在表的最后部分有熱點(diǎn)數(shù)據(jù)撕蔼，其他均是歷史數(shù)據(jù)。
- 分區(qū)表的數(shù)據(jù)更容易維護(hù)秽誊。例如鲸沮，想批量刪除大量數(shù)據(jù)可以使用清除整個(gè)分區(qū)的方式
- 分區(qū)表的數(shù)據(jù)可以分布在不同的物理設(shè)備上，從而高效地利用多個(gè)硬件設(shè)備锅论。
- 可以使用分區(qū)表來(lái)避免某些特殊的瓶頸讼溺，例如InnoDB的單個(gè)索引的互斥訪問(wèn)、ext3文件系統(tǒng)的inode鎖競(jìng)爭(zhēng)等最易。
分區(qū)表本身也有一些限制：
- 一個(gè)表最多只能有1024個(gè)分區(qū)
- 在MySQL5.1中怒坯，分區(qū)表達(dá)式必須是整數(shù)，或者是返回整數(shù)的表達(dá)式藻懒。在MySQL5.5中剔猿，某些場(chǎng)景中可以直接使用列表來(lái)進(jìn)行分區(qū)。
- 如果分區(qū)字段中有主鍵或者唯一索引的列嬉荆，那么 所有主鍵列和唯一索引列都必須包含進(jìn)來(lái)艳馒。
- 分區(qū)表中無(wú)法使用外鍵約束。
分區(qū)表的原理
分區(qū)表由多個(gè)相關(guān)的底層表實(shí)現(xiàn)员寇，這些底層表也是由句柄對(duì)象(Handler object)表示弄慰，所以我們也可以直接訪問(wèn)各個(gè)分區(qū)。存儲(chǔ)引擎管理分區(qū)的各個(gè)底層表和管理普通表一樣(所有的底層表都必須使用相同的存儲(chǔ)引擎)蝶锋，分區(qū)表的索引只是在各個(gè)底層表上各自加上一個(gè)完全相同的索引陆爽。從存儲(chǔ)引擎的角度來(lái)看，底層表和一個(gè)普通表沒有任何不同扳缕，存儲(chǔ)引擎也無(wú)須知道這是一個(gè)普通表還是分區(qū)表的一部分慌闭。
分區(qū)表的操作邏輯：
SELECT查詢
當(dāng)查詢一個(gè)分區(qū)表的時(shí)候别威，分區(qū)表先打開并鎖住所有的底層表，優(yōu)化器先判斷是否可以過(guò)濾部分分區(qū)驴剔，然后再調(diào)用對(duì)應(yīng)的存儲(chǔ)引擎訪問(wèn)各個(gè)分區(qū)的數(shù)據(jù)省古。
INSERT操作
當(dāng)寫入一條記錄時(shí)，分區(qū)層先打開并鎖住所有的底層表丧失，然后確定哪個(gè)分區(qū)接受這條記錄豺妓，再將記錄寫入對(duì)應(yīng)底層表。
DELETE操作
當(dāng)刪除一條記錄時(shí)布讹，分區(qū)層先打開并鎖住所有的底層表琳拭，然后確定數(shù)據(jù)對(duì)應(yīng)的分區(qū)，最后對(duì)相應(yīng)底層表進(jìn)行刪除操作描验。
UPDATE操作
當(dāng)更新一條記錄時(shí)白嘁，分區(qū)層先打開并鎖住所有的底層表柿扣，MySQL先確定需要更新的記錄在哪個(gè)分區(qū)澈灼，然后取出并更新，再判斷更新后的數(shù)據(jù)應(yīng)該放在哪個(gè)分區(qū)荠锭，最后對(duì)底層表進(jìn)行寫入操作呼股，并對(duì)原數(shù)據(jù)所在的底層表進(jìn)行刪除操作耕魄。
分區(qū)表的類型
MySQL支持多種分區(qū)表。用的最多的是根據(jù)范圍進(jìn)行分區(qū)卖怜；還支持鍵值屎开、哈希和列表分區(qū)阐枣。
例马靠，根據(jù)范圍進(jìn)行分區(qū)：
CREATE TABLE sales (
id INT AUTO_INCREMENT,
amount DOUBLE NOT NULL,
order_day DATETIME NOT NULL,
PRIMARY KEY(id, order_day)
) ENGINE=Innodb PARTITION BY RANGE(YEAR(order_day)) (
PARTITION p_2010 VALUES LESS THAN (2010),
PARTITION p_2011 VALUES LESS THAN (2011),
PARTITION p_2012 VALUES LESS THAN (2012),
PARTITION p_catchall VALUES LESS THAN MAXVALUE);
什么情況下會(huì)出問(wèn)題
- NULL值會(huì)使過(guò)濾無(wú)效
- 分區(qū)列和索引列不匹配
- 選擇分區(qū)的成本可能很高
- 打開并鎖住所有底層表的成本可能很高
- 維護(hù)分區(qū)的成本可能很高
使用分區(qū)表的限制：
- 所有分區(qū)都必須使用相同的存儲(chǔ)引擎
- 分區(qū)函數(shù)中可以使用的函數(shù)和表達(dá)式也有一些限制
- 某些存儲(chǔ)引擎不支持分區(qū)
- 對(duì)于MyISAM的分區(qū)表，不能再使用LOAD INDEX INTO CACHE操作
- 對(duì)于MyISAM表蔼两，使用分區(qū)表時(shí)需要打開更多的文件描述符甩鳄。
參考文獻(xiàn)
- 《高性能MySQL》
- MySQL分區(qū)（官網(wǎng)）
- 原文鏈接
