當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL 分区表——《高性能MySQL》

發布時間：2023/12/10 数据库 46 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL 分区表——《高性能MySQL》小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

讀《高性能MySQL》第三版，筆記。

1、分區表

官方手冊：
https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

對用戶來說，分區表是一個獨立的邏輯表，但是底層由多個物理子表組成。實現分區的代碼實際上是對一組底層表的句柄對象（Handler Object）的封裝。對分區表的請求，都會通過句柄對象轉化成對存儲引擎的接口調用。所以分區表對于 SQL 層來說是一個完全封裝底層實現的黑盒子，對應用是透明的，但是從底層的文件系統來看就很容易發現，每個分區表都有一個使用 # 分隔命名的表文件。

MySQL 實現分區表的方式——對底層表的封裝——意味著索引頁是按照分區的子表定義的，而沒有全局索引。

MySQL 在創建表時使用 PARTITION BY 子句定義每個分區存放的數據。在執行查詢的時候，優化器會根據分區定義過濾那些沒有我們需要數據的分區，這樣查詢就無須掃描所有分區。

分區的一個主要目的是將數據按照一個較粗的粒度分在不同的表中。這樣做可以將相關的數據存放在一起，另外，如果想一次批量刪除整個分區的數據也會變得很方便。

應用場景：

表非常大以至于無法全部都放在內存中，或者只在表的最后部分有熱點數據，其他均是歷史數據。
分區表的數據更容易維護。分區獨立優化，檢查，修復等。
分區表的數據可以分布在不同的物理設備上，從而高效地利用多個硬件設備。
可以使用分區表來避免某些特殊的瓶頸。例如 InnoDB 的單個索引的互斥訪問、ext3 文件系統的 inode 鎖競爭等。
如果需要，還可以備份和恢復獨立的分區，這在非常大的數據集的場景下效果非常好。

可以閱讀 MySQL 官方手冊中的 “ 分區 ” 一節：

https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

分區表本身的一些限制：

一個表最多只能有 1024 個分區。
在 MySQL 5.1 中，分區表達式必須是整數，或者是返回整數的表達式。在 MySQL 5.5 中，某些場景中可以直接使用列來進行分區。
如果分區字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進來。
分區表中無法使用外鍵約束。

1.1、分區表的原理

如前所述，分區表由多個相關的底層表實現，這些底層表也是由句柄對象（Handler Object）表示，所以我們也可以直接訪問各個分區。

存儲引擎管理分區的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎），分區表的索引只是在各個底層表上各自加上一個完全相同的索引。

從存儲引擎的角度來看，底層表和普通表沒有任何不同，存儲引擎也無須知道這是一個普通表還是一個分區表的一部分。

分區表上的操作按照下面的操作邏輯進行：

SELECT 查詢

當查詢一個分區表的時候，分區層先打開并鎖住所有的底層表，優化器先判斷是否可以過濾部分分區，然后再調用對應的存儲引擎接口訪問各個分區的數據。

INSERT 操作

當寫入一條記錄時，分區層先打開并鎖住所有的底層表，然后確定哪個分區接收這條記錄，再將記錄寫入對應底層表。

DELETE 操作

當刪除一條記錄時，分區層先打開并鎖住所有的底層表，然后確定數據對應的分區，最后對相應底層表進行刪除操作。

UPDATE 操作

當更新一條記錄時，分區層先打開并鎖住所有的底層表，MySQL 先確定需要更新的記錄在哪個分區，然后取出數據并更新，再判斷更新后的數據應該放在哪個分區，最后對底層表進行寫入操作，并對原數據所在的底層表進行刪除操作。

有些操作是可以支持過濾的。

例如，當刪除一條記錄時，MySQL 需要先找到這條記錄，如果 WHERE 條件恰好和分區表達式匹配，就可以將所有不包含著條記錄的分區都過濾掉。這個對 UPDATE 語句同樣有效。如果是 INSERT 操作，則本身就是只命中一個分區，其他分區都會過濾掉。MySQL 先確定這條記錄屬于哪個分區，再將記錄寫入對應的底層分區表，無須對任何其他分區進行操作。

雖然每個操作都會 “ 先打開并鎖住所有的底層表 ”，但這并不是說分區表在處理過程中是鎖住全表的。如果存儲引擎能夠自己實現行級鎖，例如 InnoDB，則會在分區層釋放對應表鎖。這個加鎖和解鎖過程與普通 InnoDB 上的查詢相似。

1.2、分區表的類型

官方手冊文檔 :

https://dev.mysql.com/doc/refman/5.7/en/partitioning-types.html

MySQL 支持多種分區表。

我們看到最多的是根據范圍進行分區，每個分區表存儲落在某個范圍的記錄，分區表達式可以是列，也可以是包含列的表達式。

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

PARTITION 分區子句中可以使用各種函數。但是返回必須是一個明確的整數，不能是常數。

MySQL 還支持鍵值、哈希和列表分區，這其中有些還支持子分區，不過我們在生產環境中很少見到。

我們還看到的一些其他的分區技術包括：

根據鍵值進行分區，來減少 InnoDB 的互斥量競爭。
使用數學模函數來進行分區，然后將數據輪詢放入不同的分區。
假設表有一個自增的主鍵列 id，系統根據時間將最近的熱點數據集中存放。那么必須將時間戳包含在主鍵當中才行，而這和主鍵本身的意義相矛盾。這種情況下也可以使用這樣的分區表達式來實現相同的目的：HASH(id DIV 1000000) ，這將為100萬數據建立一個分區。這樣一方面實現了當初的分區目的，另一方面比起使用時間范圍分區還避免了一個問題，就是超過一定閾值時，如果使用時間范圍分區就必須新增分區。

1.3、如何使用分區表

數據量巨大，肯定不能在每次查詢的說話都掃描全表。

在數據量超大的時候，B-Tree 索引就無法起作用了。除非是索引覆蓋查詢，否則數據庫服務器需要根據索引掃描的結果回表，查詢所有符合條件的記錄，如果數據量巨大，這將產生大量隨機 I/O ，隨之，數據庫的響應時間將大到不可接收的成都。另外，索引維護（磁盤空間、I/O 操作）的代價也非常高。

理解分區時還可以將其當作索引的最初形態，以代價非常小的方式定位到需要的數據在哪一片 “ 區域 ”。在這片 “ 區域 ” 中，你可以做順序掃描，可以建索引，還可以將數據緩存大內存，等等。因為分區無須額外的數據結構記錄每個分區有哪些數據——分區不需要精確定位每條數據的位置，也就無須額外的數據結構——所以其代價非常低。只需要一個簡單的表達式就可以表達每個分區存放的是什么數據。

為了保證大數據量的可擴展性，一般有下面兩個策略：

全量掃描數據，不要任何索引。
- 可以使用簡單的分區方式存放表，不要任何索引，根據分區的規則大致定位需要的數據位置。只要能夠使用 WHERE 條件，將需要的數據限制在少數分區中，則效率是很高的。當然，也需要做一些簡單的保證查詢的響應時間能夠滿足需要。使用該策略假設不用講數據完全放入到內存中，同時還假設選喲的數據全都在磁盤上，因為內存相對很小，數據很快會被擠出內存，所以緩存起不了任何作用。這個策略使用于以正常的方式訪問大量數據的時候。警告：必須將查詢需要掃描的分區個數限制在一個很小的數據。
索引數據，并分離熱點。
- 如果數據有明顯的 “ 熱點 ”，而且除了這部分數據，其他數據很少被訪問到，那么可以將這部分熱點數據單獨放在一個分區中，讓這個分區的數據能夠有機會都緩存在內存中。這樣查詢就可以只訪問一個很小的分區表，能夠使用索引，也能夠有效地使用緩存。

僅僅知道這些還不夠，MySQL 的分區表實現還有很多陷阱。

1.4、什么情況下會出問題

上面我們介紹的兩個分區策略都基于兩個非常重要的假設：查詢都能夠過濾（prunning）掉很多額外的分區、分區本身并不會帶來很多額外的代價。而事實證明，這兩個假設在某些情況下會有問題。

下面介紹一些可能會遇到的問題：

NULL 值會使分區過濾無效
分區列和索引列不匹配
選擇分區的成本可能很高
打開并鎖住所有底層表的成本可能很高
維護分區的成本可能很高

如上所述，分區表不是什么 “ 銀彈 ”。下面是目前（5.5）分區實現中的一些其他限制：

所有分區都必須使用相同的存儲引擎。
分區函數中可以使用的函數和表達式也有一些限制。
某些存儲引擎不支持分區。
對于 MyISAM 的分區表，不能使用 LOAD INDEX INTO CACHE 操作。
對于 MyISAM 表，使用分區表時需要打開更多的文件描述符。

1.5、查詢優化

引入分區給查詢優化帶來了一個些新的思路（同時也帶來新的bug）。分區最大的有點就是優化器可以根據分區函數來過濾一些分區。根據粗粒度索引的優勢，通過分區過濾通常可以讓查詢掃描更少的數據（在某些場景下）。

所以，對于訪問分區表來說，很重要的一點是要在 WHERE 條件中帶入分區列，有時候即使看似多余的也要帶上，這樣就可以讓優化器能夠過濾掉無須訪問的分區。如果沒有這些條件，MySQL 就需要讓對應存儲引擎訪問這個表的所有分區，如果表非常大的話，就可能會非常慢。

使用 EXPLAIN PARTITIONS 可以觀察優化器是否執行了分區過濾。

MySQL 只能在使用分區函數的列本身進行比較時才能過濾分區，而不能根據表達式的值來過濾分區，即使這個表達式就是分區函數也不行。

1.6、合并表

合并表（Merge table）是一種早期的、簡單的分區實現，和分區表相比有一些不同的限制，并且缺乏優化。分區表嚴格來說是一個邏輯上的概念，用戶無法訪問底層的各個分區，對用戶來說分區是透明的。但是合并表允許用戶單獨訪問各個子表。合并表是一種被淘汰的技術。

1.7 分表分庫的區別

引用：
https://www.cnblogs.com/GrimMjx/p/11772033.html

總結

以上是生活随笔為你收集整理的MySQL 分区表——《高性能MySQL》的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： Android 第九课常用控件---
下一篇：第六十三期：放下你手里的代码，小心被抓！