首頁 >資料庫 >mysql教程 >MySQL效能調優之分區表(總結分享)

MySQL效能調優之分區表(總結分享)

WBOY
WBOY轉載
2022-04-30 09:00:172382瀏覽

本篇文章為大家帶來了關於mysql的相關知識,其中主要介紹了關於效能調優的相關問題,主要介紹了分區表的相關內容,對於使用者而言,分區表是一個獨立的邏輯表,但是底層是由多個物理子表組成,下面一起來看一下,希望對大家有幫助。

MySQL效能調優之分區表(總結分享)

推薦學習:mysql影片教學

#對使用者而言,分割表是一個獨立的邏輯表,但是底層是由多個物理子表組成。分區表對於使用者而言是一個完全封裝底層實現的黑盒子,對使用者而言是透明的,從檔案系統中可以看到多個使用#分隔命名的表檔案。
mysql在建立表格時使用partition by子句定義每個分割區存放的數據,在執行查詢的時候,最佳化器會根據分割區定義過濾那些沒有我們需要資料的分割區,這樣查詢就無須掃描所有分割區。
分區的主要目的是將資料安好一個較粗的力度分在不同的表中,這樣可以將相關的資料存放在一起。
接下來,我將從以下6個面向來談談分區表,分別是分區表的應用場景、分區表的限制、分區表的原理、分區表的類型、如何使用分區表、在使用分區表的時候需要注意的問題。

一、分區表的應用場景

1、表非常大以至於無法全部都放在記憶體中,或只在表的最後部分有熱點數據,其他都是歷史數據。

2、分區表的資料更容易維護

(1)批次刪除大量資料可以使用清除整個分割區的方式

(2)對一個獨立分割區進行最佳化、檢查、修復等操作

3、分區表的資料可以分佈在不同的實體設備上,從而高效地利用多個硬體設備

#4、可以使用分區表來避免某一些特殊的瓶頸

(1)innodb的單一索引的互斥存取

(2)ext3檔案系統的inode鎖定競爭

#5、可以備份和恢復獨立的分區

二、分區表的限制

1、一個表最多只能有1024個分區,在5.7版本的時候可以支援8196個分區

2、在早期的mysql中,分區表達式必須是整數或是傳回整數的表達式,在mysql5.5中,某些場景可以直接使用列來進行分割。

3、如果分區欄位中有主鍵或唯一索引的列,那麼所有主鍵列和唯一索引列都必須包含進來。

4、分區表無法使用外鍵約束

三、分區表的原理

分區表由多個相關的底層表實現,這個底層表也是由句柄物件標識,我們可以直接存取各個分區。儲存引擎管理分區的各個底層表和管理普通表一樣(所有的底層表都必須使用相同的儲存引擎),分區表的索引知識在各個底層表上各自加上一個完全相同的索引。從儲存引擎的角度來看,底層表和普通表沒有任何不同,儲存引擎也無須知道這是一個普通表還是一個分區表的一部分。分區表的操作按照以下的操作邏輯進行:

1、select查詢

當查詢一個分區表的時候,分區層先打開並鎖住所有的底層表,優化器先判斷是否可以過濾部分分區,然後再調用對應的儲存引擎介面存取各個分區的資料

2、insert操作

當寫入一筆記錄的時候,分區層先開啟並鎖住所有的底層表,然後確定哪個分區接受這條記錄,再將記錄寫入對應底層表。

3、delete操作

當刪除一筆記錄時,分割區層先開啟並鎖住所有的底層表,然後確定資料對應的分割區,最後對對應底層表進行刪除操作。

4、update操作

當更新一筆記錄時,分割區層先開啟並鎖住所有的底層表,mysql先確定需要更新的記錄再哪個分割區,然後取出資料並更新,再判斷更新後的資料應該再哪個分區,最後對底層表進行寫入操作,並對來源資料所在的底層表進行刪除操作。

有些操作時支援過濾的,例如,當刪除一筆記錄時,MySQL需要先找到這條記錄,如果where條件恰好和分區表達式匹配,就可以將所有不包含這條記錄的分區都過濾掉,這對update同樣有效。如果是insert操作,本身就是只命中一個分區,其他分區都會被過濾掉。 mysql先確定這條記錄屬於哪個分區,再將記錄寫入對應到曾分區表,無須對任何其他分區進行操作。

雖然每個操作都會“先打開並鎖住所有的底層表”,但這並不是說分區表在處理過程中是鎖住全表的,如果存儲引擎能夠自己實現行級鎖,例如innodb ,則會在分區層釋放對應表鎖定。

推薦學習:mysql影片教學

#

以上是MySQL效能調優之分區表(總結分享)的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:csdn.net。如有侵權,請聯絡admin@php.cn刪除