高性能MySql設計之分區表

高性能MySql設計之表設計

當數據量非常大時（表的容量到達GB或者是TB），如果仍然采用索引的方式來優化查詢，由於索引本生的消耗以及大量的索引碎片的產生，查詢的過程會 導致大量的隨機I/O的產生，在這種場景下除非可以很好的利用覆蓋索引，否則由於在查詢的過程中需要根據索引回數據表查詢，會導致性能受到很大的影響，這 時可以考慮通過分區表的策略來提高查詢的性能。

不同的數據庫管理系統對分區的實現可能有所區別，本文主要以MYSQL為基礎

1 、分區的類型

1.1、RANGE分區

按照RANGE分區的表是通過如下一種方式進行分區的，每個分區包含那些分區表達式的值位於一個給定的連續區間內的行。這些區間要連續且不能相互重 疊，使用VALUES LESS THAN操作符來進行定義假定你想基於每個雇員離開公司的年份來分割表，也就是說，YEAR(separated)的值。實現這種分區模式的CREATE TABLE 語句的一個例子如下所示。

例如，你可能決定通過添加一個PARTITION BY RANGE子句把這個表分割成4個區間 

[sql] 

CREATE TABLE employees (        id INT NOT NULL,        fname VARCHAR(30),        lname VARCHAR(30),        hired DATE NOT NULL DEFAULT '1970-01-01',        separated DATE NOT NULL DEFAULT '9999-12-31',        job_code INT,        store_id INT    )    PARTITION BY RANGE (YEAR(separated)) (        PARTITION p0 VALUES LESS THAN (1991),        PARTITION p1 VALUES LESS THAN (1996),        PARTITION p2 VALUES LESS THAN (2001),        PARTITION p3 VALUES LESS THAN MAXVALUE    )；

插入一些測試數據後發現P1的數據文件明顯增大 

[sql] 

mysql> DELIMITER $$    

mysql> DROP PROCEDURE IF EXISTS SampleProc$$    Query OK, 0 rows affected (0.00 sec)    

mysql> CREATE PROCEDURE SampleProc()        -> BEGIN        ->  DECLARE x INT;        ->  SET x = 1000;        ->  WHILE x<= 2000 DO        ->          insert into employees(id,fname,lname,hired,separated,job_code,store_id) values(x,concat('firstname',x),concat('ai',x),'1994-01-01','1995-01-01',10,20);        ->          SET  x = x + 1;        ->  END WHILE;        -> END$$    Query OK, 0 rows affected (0.00 sec)    

mysql> call SampleProc() $$    Query OK, 1 row affected (22.55 sec)    

mysql> delimiter ;  

RANGE分區在如下場合特別有用：

當需要刪除“舊的”數據時。如果你使用上面最近的那個例子給出的分區方案，你只需簡單地使用 “ALTER TABLEemployees DROP PARTITION p0；”來刪除所有在1991年前就已經停止工作的雇員相對應的所有行。對於有大量行的表，這比運行一個如“DELETE FROM employees WHERE YEAR(separated) <= 1990；”這樣的一個DELETE查詢要有效得多。

想要使用一個包含有日期或時間值，或包含有從一些其他級數開始增長的值的列。·             

經常運行直接依賴於用於分割表的列的查詢。例如，當執行一個如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BYstore_id；”這樣的查詢時，MySQL可以很迅速地確定只有分區p2需要掃描，這是因為余下的分區不可能包含有符合該WHERE子句的任何記 錄。

分區表達式可以是MySQL 中有效的任何函數或其他表達式，只要它們返回一個既非常數、也非隨機數的整數

1.2、LIST分區

MySQL中的LIST分區在很多方面類似於RANGE分區。和按照RANGE分區一樣，每個分區必須明確定義。它們的主要區別在於，LIST分區 中每個分區的定義和選擇是基於某列的值從屬於一個值列表集中的一個值，而RANGE分區是從屬於一個連續區間值的集合。LIST分區通過使用 “PARTITION BY LIST(expr)”來實現，其中“expr” 是某列值或一個基於某個列值、並返回一個整數值的表達式，然後通過“VALUES IN (value_list)”的方式來定義每個分區，其中“value_list”是一個通過逗號分隔的整數列表。假定有20個音像店，分布在4個有經銷權 的地區，如下表所示：地區商店ID 號北區3, 5, 6, 9, 17東區1, 2, 10, 11, 19, 20西區4, 12, 13, 14, 18中心區7, 8, 15, 16要按照屬於同一個地區商店的行保存在同一個分區中的方式來分割表，可以使用下面的“CREATETABLE”語句

[sql] 

CREATE TABLE employees (        id INT NOT NULL,        fname VARCHAR(30),        lname VARCHAR(30),        hired DATE NOT NULL DEFAULT '1970-01-01',        separated DATE NOT NULL DEFAULT '9999-12-31',        job_code INT,        store_id INT    )    PARTITION BY LIST(store_id)        PARTITION pNorth VALUES IN (3,5,6,9,17),        PARTITION pEast VALUES IN (1,2,10,11,19,20),        PARTITION pWest VALUES IN (4,12,13,14,18),        PARTITION pCentral VALUES IN (7,8,15,16)    )；  

在MySQL 5.1中，當使用LIST分區時，有可能只能匹配整數列表。

如果試圖插入列值（或分區表達式的返回值）不在分區值列表中的一行時，那麼“INSERT”查詢將失敗並報錯。例如，假定LIST分區的采用上面的方案，下面的查詢將失敗：

LIST分區沒有類似如“VALUESLESS THAN MAXVALUE”這樣的包含其他值在內的定義。將要匹配的任何值都必須在值列表中找到

1.3 、HASH分區

 HASH分區主要用來確保數據在預先確定數目的分區中平均分布。在RANGE和LIST分區中，必須明確指定一個給定的列值或列值集合應該保存在 哪個分區中；而在HASH分區中，MySQL 自動完成這些工作，你所要做的只是基於將要被哈希的列值指定一個列值或表達式，以及指定被分區的表將要被分割成的分區數量。 例如，下面的語句創建了一個 使用基於“store_id”列進行哈希處理的表，該表被分成了4個分區：

 [sql]

CREATE TABLE employees (        id INT NOT NULL,        fname VARCHAR(30),        lname VARCHAR(30),        hired DATE NOT NULL DEFAULT '1970-01-01',        separated DATE NOT NULL DEFAULT '9999-12-31',        job_code INT,        store_id INT    )    PARTITION BY HASH(store_id)    PARTITIONS 4；  

如果沒有包括一個PARTITIONS子句，那麼分區的數量將默認為1 

1.4、KEY分區

KEY進行分區類似於按照HASH分區，除了HASH分區使用的用戶定義的表達式，而KEY分區的 哈希函數是由MySQL 服務器提供。MySQL 簇（Cluster）使用函數MD5()來實現KEY分區；對於使用其他存儲引擎的表，服務器使用其自己內部的 哈希函數，這些函數是基於與PASSWORD()一樣的運算法則。“CREATETABLE ... PARTITION BY KEY”的語法規則類似於創建一個通過HASH分區的表的規則。它們唯一的區別在於使用的關鍵字是KEY而不是HASH，並且KEY分區只采用一個或多個 列名的一個列表。通過線性KEY分割一個表也是可能的。下面是一個簡單的例子： 

[sql] 

CREATE TABLE tk (        col1 INT NOT NULL,        col2 CHAR(5),        col3 DATE    )     PARTITION BY LINEAR KEY (col1)    PARTITIONS 3;  

在KEY分區中使用關鍵字LINEAR和在HASH分區中使用具有同樣的作用，分區的編號是通過2的冪（powers-of-two）算法得到，而不是通過模數算法

1.5、更多的知識上面列出的僅僅是常用的分區策略的用法，當創建了分區後往往還要對分區進行維護，

具體請參見：http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html#partitioning-hash 

無論使用何種類型的分區，分區總是在創建時就自動的順序編號，且從0開始記錄，記住這一點非常重要。當有一新行插入到一個分區表中時，就是使用這些分區編號來識別正確的分區。例如，如果你的表使用4個分區，那麼這些分區就編號為0, 1, 2, 和3。

對於RANGE和LIST分區類型，確認每個分區編號都定義了一個分區，很有必要。對HASH分區，使用的用戶函數必須返回一個大於0的整數值。對於KEY分區，這個問題通過MySQL服務器內部使用的 哈希函數自動進行處理。

2 、使用分區表的限制

一個數據表最多只能有1024個分區· 

在MYSQL5.1 版本中分區表達式的結果必須是整數，在MYSQL5.5分區表達式可以使用列·  

如果分區字段中有主鍵或者是唯一索引列，則所有的主鍵或者是唯一索引列必須全部包含進來· 

分區表無法使用外鍵·  

對於同一個表的各個分區表必須使用相同的存儲引擎· 

分區函數有限制，只可以是MySQL 中有效的任何函數或其他表達式，且它們返回一個既非常數、也非隨機數的整數·  

某些存儲引擎不支持分區

3 、使用分區表的“陷阱”

在數據量非常大的時候使用分區表可以使性能有較好的改善，但是前提是必須能有效的規避下面列出的一些陷阱·  

NULL值MySQL 中的分區在禁止空值（NULL）上沒有進行處理，無論它是一個列值還是一個用戶定義表達式的值。一般而言，對於NULL,或者是當表達式接收非法值時 （e.g. YEAR(‘asdf-12-12’)）返回的結果都是NULL,在這種情況下MySQL 把NULL視為0,如果大量的記錄存在這種情況，最終會導致大量的記錄都集中在一個分區中，也也就違背了分區的初衷。如果你希望回避這種做法，你應該在設 計表時不允許空值；最可能的方法是，通過聲明列“NOT NULL”來實現這一點。

分區列和索引列不匹配如果定義的索引列和分區列不匹配，則會導致查詢無法進行分區過濾。例如在列a上定義分區，在列b上定義索引，因為每個分區都有獨立的索引，所以掃描索引時需要掃描每個分區。應該避免建立和分區列不匹配的索引，除非查詢中包含了可以過濾分區的條件。

選擇分區的成本很高對於Range 和list類型的分區，每次進行操作時都需要遍歷所有的分區條件，以判斷相關的記錄是屬於哪個分區，如果分區的數量很多，會在選擇分區上浪費較多的資 源 為了避免這種情況 可以限制分區的數目（<100）,或是選擇hash分區 · 

鎖住底層表的成本很高在查詢訪問分區表的時候，MYSQL會打開並鎖住所有的底層表，該操作時再分區過濾之前發生而且和分區類型無關，會影響所有的 分區查詢。 可以通過批量更新的方式來降低該操作的次數，同時也需要限制分區的數目 ·        維護成本高增加/刪除分區很快捷，但是重組或者是 alter分區的過程類似於alter table,會進行大量的數據復制操作，效率很低。 

4 、優化查詢語句

對於分區表的訪問，最重要的一點是要在where條件中包含分區列，即使看起來是多余的，只有這樣才能過濾不需要的分區，否則會訪問所以的分區表。 看一個簡單的例子。Employees表使用store_id作為范圍分區的條件，如果不使用store_id作為where條件，會查詢所有的分區

Store_id作為where條件時，只查詢對應的分區 關於where條件中的表達式有幾點需要注意· 

單純的使用分區列Where條件中分區列必須是未經函數處理的，如果where條件寫成where YEAR(store_id), 則分區過濾會失效，且查詢時會檢查所有的分區。這一點和索引類似 

關聯查詢如果分區表是關聯操作的第二張表，且關聯條件是分區建，則MYSQL只會在對應的分區裡進行匹配