MySQL分區表

標籤：

 

分區表是一種粗粒度,簡易的索引策略,適用於大資料的過濾情境.最適合的情境是,沒有合適的索引時,對其中幾個分區表進行全表掃描.或者只有一個分區表和索引是熱點,而且這個分區和索引能夠全部儲存在記憶體中.限制單表分區數不要超過150個,並且注意某些導致無法做分區過濾的細節,分區表對於單條記錄的查詢沒有優勢,需要注意這類查詢的效能.

 

分區表文法

　　分區表分為RANGE,LIST,HASH,KEY四種類型,並且分區表的索引是可以局部針對分區表建立的

　　建立分區表

CREATE TABLE sales (    id INT AUTO_INCREMENT,    amount DOUBLE NOT NULL,    order_day DATETIME NOT NULL,    PRIMARY KEY(id, order_day)) ENGINE=Innodb PARTITION BY RANGE(YEAR(order_day)) (    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);

　　這段語句表示將表內資料按照order_dy的年份範圍進行分區,2010年一個區,2011一個,2012一個,剩下的一個.

　　要注意如果這麼做,則order_day必須包含在主鍵中,且會產生一個問題,就是當年份超過閾值,到了2013,2014時,需要手動建立這些分區

　　替代方法就是使用HASH

　　

CREATE TABLE sales (    id INT PRIMARY KEY AUTO_INCREMENT,    amount DOUBLE NOT NULL,    order_day DATETIME NOT NULL) ENGINE=Innodb PARTITION BY HASH(id DIV 1000000);

　　這種分區表示每100W條資料建立一個分區,且沒有閾值範圍的影響

 

對於大資料而言

　　對於大資料(如10TB)而言,索引起到的作用相對小,因為索引的空間與維護成本很高,另外如果不是索引覆蓋查詢,將導致回表,造成大量磁碟IO.那麼對於這種情況的解決方案策略是:

　　1.全量掃描資料,不要任何索引

　　通過分區表運算式將資料定位在少量的分區表,然後正常訪問這些分區表的資料

　　2.分離熱點,索引資料

　　將熱點資料分離出來在一個小的分區,並對分區建立索引,對熱點資料的查詢提高效率.

 

分區表的問題

　　1.NULL值使分區過濾無效

　　假設按照RANGE YEAR(order_date)分區,那麼如果這個運算式計算出來的時NULL值,記錄就會被存放到第一個分區.所以在查詢時加入查詢條件有可能出現NULL值,那麼就會去檢查第一個分區.解決的方法可以是將第一個分區建立為NULL分區 PARTITION p_nulls VALUES LESS THAN (0),或者在MySQL5.5以後,直接使用COLUMN建立分區 PARTITION BY RANGE COLUMNS(order_date)

　　2. 選擇分區的成本

　　每插入一行資料都需要按照運算式篩選插入的分區地址

　　3. 分區列和索引列不匹配

　　如果索引列和分區列不匹配,且查詢中沒有包含過濾分區的條件,會導致無法進行分區過濾,那麼將會導致查詢所有分區.

　　4. 開啟並鎖住所有底層表

　　分區表的的查詢策略是在分區過濾之前,開啟並鎖住所有底層表,這會造成額外的開銷,解決問題的方法是盡量使用大量操作,例如LOAD DATA INFILE,或者一次刪除多行資料.

 

過濾分區表的要點

　　過濾分區表的WHERE條件必須是切分分區表的列,而不能帶有函數,例如只能是order_day,而不能是YEAR(order_day)

MySQL分區表