MySQL分區表-電商資料庫設計及優化學習筆記

MySQL分區表-電商資料庫設計及優化學習筆記

一、背景

這裡是我的電商資料庫設計及優化學習筆記中關于MySQL分區表部分的内容。

二、MySQL分區表

1、使用分區表需要注意的： 

*确認MySQL伺服器是否支援分區表 

使用mysql>show plugins來檢視 

分區表的特點： 

*在邏輯上為一個表，在實體上存儲在多個檔案中 

2、常用的分區表的類型： 

2.1 哈希類型的分區： 

主要特點： 

根據MOD（分區鍵，分區數）的值把資料行存儲到表的不同分區中，這樣的分區稱為哈希分區。 

hash分區的鍵值必須是一個INT類型的資料，或者通過函數可以轉為INT類型 

如何建立哈希分區： 

在建表語句最末尾加上 PARTITION BY HASH(分區鍵) PARITION 分區數 

如 

PARTITION BY HASH(customer_id) PARITION 4； 

分區表和非分區表的差異主要還是在實體檔案上： 

分區表會包含一個以frm結尾的源資料檔案，還有按照分區的id建立的資料檔案 

分區表的使用跟普通表是一樣的 

2.2按範圍分區 

特點： 

根據分區鍵值的範圍把資料行存儲到表的不同分區中 

多個分區的範圍要連續，但是不能重疊 

預設情況下使用VALUES LESS THAN屬性，即每個分區不包括指定的那個值。 

建立RANGE分區： 

在建表語句最末尾加上 PARTITION BY RANGE(customer_id) ( 

PARTITION p0 VALUES LESS THAN(10000), 

PARTITION p1 VALUES LESS THAN(20000), 

… 

PARTITION pn VALUES LESS THAN MAXVALUES, 

); 

這裡表示，分區鍵，也就是customer_id範圍在0-9999的使用者存儲在p0分區，10000-19999的使用者在p1分區，在範圍分區中最好包括一個VALUES LESS THAN MAXVALUES的分區。 

範圍分區适用場景： 

分區鍵為日期或是時間類型 

所有查詢中都包括分區鍵 

定期按照範圍清理曆史資料 

2.3 LIST分區 

按分區鍵取值的清單進行分區 

同範圍分區一樣，個分區清單不能重複 

每一行資料必須能找到對應的分區清單，否則資料插入會失敗 

建立LIST分區 

在文法上和哈希、範圍分區的差別： 

PARTITION BY LIST(login_type)( 

PARTITION p0 VALUES IN(1,3,5,7,9), 

PARTITION p0 VALUES IN(2，4，6，8) 

）；

3.為登入日志表分區 

3.1業務場景分析： 

記錄使用者每次登入的日志，使用者每次登入都會記錄customer_login_log日志 

使用者登入日志儲存一年，一年後可以删除 

3.2表的分區類型及分區鍵 

根據業務場景分析，這裡适合使用範圍分區，選擇分區鍵要盡量避免産生跨分區查詢，在登入日志表中，隻有login_time是日期類型的，而且之後需要根據日期删除，是以選擇login_time作為分區鍵 

3.3分區使用者登入日志表 

CREATE TABLE customer_login_log( 

customer_id INT UNSIGNED NOT NULL, 

login_time DATETIME NOT NULL, 

login_ip INT UNSIGNED NOT NULL, 

login_type TINYINT NOT NULL 

)ENGINE =INNODB 

PARTITION BY RANGE(YEAR(login_time))( 

PARTITION p0 VALUES LESS THAN(2017), 

PARTITION p1 VALUES LESS THAN(2018), 

PARTITION p2 VALUES LESS THAN(2019) 

往表中添加幾條資料 

INSERT INTO customer_login_log(customer_id,login_time,login_ip,login_type)VALUES(1001,’2017-01-25’,0,1),(1001,’2017-07-25’,0,1),(1001,’2018-01-25’,0,1),(1001,’2018-03-25’,0,1),(1001,’2016-01-25’,0,1); 

通過MYSQL系統日志查詢檢視分區情況，語句如下： 

SELECT table_name,partition_name,partition_description,table_rows 

FROM information_schema.

PARTITIONS

WHERE table_name=’customer_login_log’

在這個表中沒有建立MAXVIEW分區是為了維護友善，為了避免插入資料到RANGE分區失敗，要做修改并添加分區的計劃任務。

增加分區的語句 

ALTER TABLE customer_login_log ADD PARTITION (PARTITION p4 VALUES LESS THAN(2020)); 

删除分區 

ALTER TABLE customer_login_log DROP PARTITION P0; 

歸檔過期資料 

除了删除資料，還有可能需要對過期資料進行歸檔 

分區資料歸檔遷移條件： 

1、MYSQL>=5.7 

2、結構相同 

3、歸檔到的資料表一定要是非分區表 

4、非臨時表，不能有外鍵限制 

5、歸檔引擎要是：archive 

步驟1、先建立一個非分區表 

步驟2、資料交換 

ALTER TABLE customer_login_log exchange PARTITION P1 WITH TABLE arch_customer_login_log; 

将分區p1，也就是2017年以前的資料交換到歸檔表中， 

此時日志表中p1分區中已經沒有資料，但是分區還存在，p1分區中的資料已經存在剛剛建的歸檔表中，但是之後再往日志表中插入2017年以前的資料還是會存到p1分區表中，是以此時要對p1分區進行删除。 

語句： ALTER TABLE customer_login_log DROP PARTITION P1; 

步驟3、如果有需要，可以把日志表存儲引擎改為歸檔引擎： 

ALTER TABLE arch_customer_login_log ENGINE=archive 

歸檔引擎比innodb占用的記憶體更小，但是歸檔引擎的表隻能進行查詢操作，而不能進行寫操作。

使用分區表的注意事項 

結合業務場景選擇分區鍵，避免跨分區查詢 

對分區表進行查詢最好在where從句中包含分區鍵 

具有主鍵或者唯一索引的表，主鍵或唯一索引必須是分區鍵的一部分 

（是以在使用者登入表中把自增id主鍵去掉了，不然會影響分區）

三、小結

學習了常用的分區類型：哈希分區，範圍分區，LIST分區。還有增加分區、删除分區的方法。 

表分區的優點： 

1、改善查詢性能：對分區對象的查詢可以僅搜尋自己關心的分區，提高檢索速度。 

2、增強可用性：如果表的某個分區出現故障，表在其他分區的資料仍然可用； 

3、維護友善：如果表的某個分區出現故障，需要修複資料，隻修複該分區即可； 

4、均衡I/O：可以把不同的分區映射到磁盤以平衡I/O，改善整個系統性能。 

但是也有缺點： 

分區表相關：已經存在的表沒有方法可以直接轉化為分區表。不過 Oracle 提供了線上重定義表的功能。

使用分區要注意 

原文位址https://blog.csdn.net/qq_40916110/article/details/80782191