共計(jì) 2777 個(gè)字符，預(yù)計(jì)需要花費(fèi) 7 分鐘才能閱讀完成。

自動(dòng)寫代碼機(jī)器人，免費(fèi)開通

這篇文章主要介紹 MySQL 中分區(qū)表是什么意思，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

對(duì)于用戶而言，分區(qū)表是一個(gè)獨(dú)立的邏輯表，但是在底層由多個(gè)物理子表組成。實(shí)現(xiàn)分區(qū)的代碼實(shí)際上是對(duì)一組底層表的句柄對(duì)象的封裝，對(duì)分區(qū)表的請(qǐng)求都會(huì)通過句柄對(duì)象轉(zhuǎn)化成對(duì)存儲(chǔ)引擎的接口調(diào)用

意義

MySQL 在創(chuàng)建表的時(shí)候可以通過使用 PARTITION BY 子句定義每個(gè)分區(qū)存放的數(shù)據(jù)。在執(zhí)行查詢的時(shí)候，優(yōu)化器根據(jù)分區(qū)定義過濾那些沒有我們需要的數(shù)據(jù)的分區(qū)，這樣查詢就可以無需掃描所有分區(qū)——只需要查找包含需要數(shù)據(jù)的分區(qū)即可。

分區(qū)的一個(gè)主要目的是 將數(shù)據(jù)按照一個(gè)較粗的粒度分別存放在不同的表中。這樣做可以將相關(guān)的數(shù)據(jù)存放在一起，另外，當(dāng)我們想要一次批量刪除整個(gè)分區(qū)的數(shù)據(jù)也會(huì)變得很方便。

在以下的場景中，分區(qū)可以起到很大的作用：

表非常大以至于無法全部都放在內(nèi)存中，或者只在表的最后部分有熱點(diǎn)數(shù)據(jù)其他均是歷史數(shù)據(jù)

分區(qū)表的數(shù)據(jù)更容易維護(hù)

分區(qū)表的數(shù)據(jù)可以分布在不同的物理設(shè)備上

可以使用分區(qū)表來避免某些特殊的瓶頸

如果需要，可以備份和回復(fù)獨(dú)立的分區(qū)

分區(qū)表本身也有一些限制，下面幾點(diǎn)尤為重要：

一張表最多只能有 1024 個(gè)分區(qū)

在 MySQL5.1 中，分區(qū)表達(dá)式必須是整數(shù)，或者是返回整數(shù)的表達(dá)式。在 MySQL5.5 中，某些場景可以直接使用列來進(jìn)行分區(qū)

分區(qū)表中無法使用外鍵約束

如果分區(qū)字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進(jìn)來

分區(qū)表的原理

存儲(chǔ)引擎管理分區(qū)的各個(gè)底層表和管理普通表并沒有什么區(qū)別（所有的底層表都必須使用相同的存儲(chǔ)引擎）
，分區(qū)表的索引只是在各個(gè)底層表上各自加上一個(gè)完全相同的索引。從存儲(chǔ)引擎的角度看，底層表和一個(gè)普通表并沒有什么區(qū)別，存儲(chǔ)引擎也無需知道這是一個(gè)普通表還是一個(gè)分區(qū)表的一部分。

分區(qū)表上的操作按照下面的操作邏輯進(jìn)行：

SELECT 查詢

當(dāng)查詢一個(gè)分區(qū)表的時(shí)候，分區(qū)層先打開并鎖住所有的底層表，優(yōu)化器先判斷是否可以過濾部分分區(qū)，然后再調(diào)用對(duì)應(yīng)的存儲(chǔ)引擎接口訪問各個(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ù)并更新，再判斷更新后的數(shù)據(jù)應(yīng)該放在哪個(gè)分區(qū)，最后對(duì)底層表進(jìn)行寫入操作，并對(duì)原數(shù)據(jù)所在的底層表進(jìn)行刪除操作。

這些操作都是支持過濾的。

雖然每個(gè)操作都會(huì)“先打開并鎖住所有的底層表”，但這并不是說分區(qū)表在處理過程中是鎖住全表的。如果存儲(chǔ)引擎能夠自己實(shí)現(xiàn)行級(jí)鎖，則會(huì)在分區(qū)層釋放對(duì)應(yīng)表鎖。這個(gè)加鎖和解鎖過程與普通 InnoDB 上的查詢類似。

分區(qū)表的類型

MySQL 支持多種分區(qū)表，我們看到最多的就是根據(jù)范圍進(jìn)行分區(qū)，每個(gè)分區(qū)存儲(chǔ)落在某個(gè)范圍內(nèi)的記錄。分區(qū)表達(dá)式可以是列，也可以是包含列的表達(dá)式。

例如，如下表就將每一年的銷售額都存放在不同的分區(qū)中：

CREATE TABLE sales(
 order_date DATETIME NOT NULL,
 ....
)ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date))(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;
)

PARTITION 分區(qū)子句中可以使用各種函數(shù)。但是有一個(gè)要求，表達(dá)式返回的值必須是一個(gè)確定的整數(shù)，且不能是一個(gè)常數(shù)。

MySQL 還支持鍵值、哈希和列表分區(qū)等。

如何使用分區(qū)表

如果我們希望從一個(gè)非常大的表中查詢出一段時(shí)間的記錄，我們應(yīng)該如何查詢這個(gè)表，如何才能更加高效？

因?yàn)閿?shù)據(jù)量非常大，肯定不能在每次查詢的時(shí)候都掃描全表，考慮到索引在空間和維護(hù)上的消耗，我們也不希望使用索引。即使真的使用索引，也會(huì)發(fā)現(xiàn)數(shù)據(jù)并不是按照想要的方式進(jìn)行聚集，會(huì)產(chǎn)生大量的碎片，最終導(dǎo)致一個(gè)查詢產(chǎn)生成千上萬的隨機(jī) I /O。而事實(shí)上，當(dāng)數(shù)據(jù)量超級(jí)大時(shí)，B-Tree 索引就已經(jīng)無法祈禱作用了。

因此我們可以選擇一些更粗粒度但消耗更少的方式檢索數(shù)據(jù)，例如在大量的數(shù)據(jù)上只索引對(duì)應(yīng)的一小塊元數(shù)據(jù)。

這正是分區(qū)要做的事情，理解分區(qū)可以將其當(dāng)作索引的最初形態(tài)。因?yàn)榉謪^(qū)無需額外的數(shù)據(jù)結(jié)構(gòu)記錄每個(gè)分區(qū)有哪些數(shù)據(jù)——分區(qū)不需要精確定位每條數(shù)據(jù)的位置，也就無須額外的數(shù)據(jù)結(jié)構(gòu)——所以其代價(jià)非常低。只需要一個(gè)簡單的表達(dá)式就可以表達(dá)每個(gè)分區(qū)存放的是什么數(shù)據(jù)。

為了保證大數(shù)據(jù)量的可擴(kuò)展性，一般有以下兩個(gè)策略：

全量掃描數(shù)據(jù)，不需要任何索引：只要能夠使用 WHERE 條件，將需要的數(shù)據(jù)限制在少數(shù)分區(qū)中，則效率是很高的。使用這種策略假設(shè)不用將數(shù)據(jù)完全放入內(nèi)存中，同時(shí)還假設(shè)需要的數(shù)據(jù)全部都在磁盤上。因?yàn)閮?nèi)存相對(duì)較小，數(shù)據(jù)很快會(huì)被擠出內(nèi)存，所以緩存起不了任何作用。這個(gè)策略適用于以正常的方式訪問大量數(shù)據(jù)的時(shí)候。

索引數(shù)據(jù)，并分離熱點(diǎn)：如果數(shù)據(jù)有明顯的“熱點(diǎn)”，而且除了這部分?jǐn)?shù)據(jù)，其他數(shù)據(jù)很少被訪問到，那么可以將這部分熱點(diǎn)數(shù)據(jù)單獨(dú)放在一個(gè)分區(qū)中，讓這個(gè)分區(qū)的數(shù)據(jù)可以有機(jī)會(huì)都緩存在內(nèi)存中。這樣的查詢可以只訪問一個(gè)很小的分區(qū)表，能夠使用索引，也能夠有效的使用緩存。

什么情況下會(huì)出問題

上面介紹的兩個(gè)分區(qū)策略都基于兩個(gè)非常重要的假設(shè)：查詢都能夠過濾掉很多額外的分區(qū)、分區(qū)本身并不會(huì)帶來很多額外的代價(jià)。

事實(shí)證明，這兩個(gè)假設(shè)在某些場景下會(huì)有問題：

分區(qū)列和索引列不匹配：如果定義的索引列和分區(qū)列不匹配，會(huì)導(dǎo)致可查詢無法進(jìn)行分區(qū)過濾。

選擇分區(qū)的成本可能很高：不同類型的分區(qū)的實(shí)現(xiàn)方式也不同，所以它們的性能也各不相同。尤其是范圍分區(qū)，對(duì)于查詢符合條件的行在哪些分區(qū)的成本可能會(huì)非常高，因?yàn)榉?wù)器需要掃描所有的分區(qū)定義的列表來找到正確的答案。

打開并鎖住所有底層表的成本可能很高：當(dāng)查詢訪問分區(qū)表的時(shí)候，MySQL 需要打開并鎖住所有的底層表，這是分區(qū)表的另一個(gè)開銷。

維護(hù)分區(qū)的成本可能很高：某些分區(qū)維護(hù)操作的速度會(huì)非?？?，例如新增或者刪除分區(qū)。而有些操作，比如重組分區(qū)或者類似 ALTER 語句的操作成本可能會(huì)很高，因?yàn)檫@類操作需要復(fù)制數(shù)據(jù)。

以上是“MySQL 中分區(qū)表是什么意思”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！

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

丸趣 TV 網(wǎng) – 提供最優(yōu)質(zhì)的資源集合！