通過分區（Partition）提升MySQL性能 - fanqiang.com

?

通過分區（Partition）提升MySQL性能
作者：foxcai 來源:foxcai 的 Blog (2006-05-08 14:30:34)
? 通過分區（Partition）提升MySQL性能 ?????????????? ——MySQL5.1新特性翻譯系列 幾年前，俺寫過一篇題為“The Foundation of Excellent Performance”的文章(現在仍然可以在 http://www.tdan.com/i016fe03.htm 看到)，俺對SQL語句是影響數據庫驅動系統性能的第一要素的觀點有點質疑。其實在那時我在文章中就堅信數據庫的物理設計在對高級數據庫的性能影響上遠比其他因素重要。同時俺還給大家看了Oracle的研究，他們解釋了為什么拙劣的物理設計是數據庫停機（無論是有計劃的還是沒計劃的）背后的主要原因。這么多年都過來啦（幸好沒多少人朝俺扔磚頭），俺的觀點是改變了一些，但在這點上俺還是堅持DBA如果想要高性能的數據庫就必須在數據庫的物理設計上多思考的觀點，這樣才能減少響應時間使終端用戶滿意而不是引來罵聲一片。（陳朋奕語：不要那么嚴肅，嘿嘿） 俺今天這么激動又想寫文章的原因是MySQL5.1的發布帶來了設計超強動力數據庫的強有力的武器，任何MySQL的DBA都應該盡快學習并使用它。俺覺得如果能很好滴使用這個5.1版帶來的新特性，DBA可以使自己管理的VLDB（不知道什么是VLDB？告訴你，是好大好大的數據庫的意思，Very Large DB）或數據倉庫奇跡般的獲得巨大的性能提升。 什么是數據庫分區？ 數據庫分區是一種物理數據庫設計技術，DBA和數據庫建模人員對其相當熟悉。雖然分區技術可以實現很多效果，但其主要目的是為了在特定的SQL操作中減少數據讀寫的總量以縮減響應時間。 分區主要有兩種形式：//這里一定要注意行和列的概念（row是行，column是列） 水平分區（Horizontal Partitioning） 這種形式分區是對表的行進行分區，通過這樣的方式不同分組里面的物理列分割的數據集得以組合，從而進行個體分割（單分區）或集體分割（1個或多個分區）。所有在表中定義的列在每個數據集中都能找到，所以表的特性依然得以保持。 舉個簡單例子：一個包含十年發票記錄的表可以被分區為十個不同的分區，每個分區包含的是其中一年的記錄。（朋奕注：這里具體使用的分區方式我們后面再說，可以先說一點，一定要通過某個屬性列來分割，譬如這里使用的列就是年份） 垂直分區（Vertical Partitioning） 這種分區方式一般來說是通過對表的垂直劃分來減少目標表的寬度，使某些特定的列被劃分到特定的分區，每個分區都包含了其中的列所對應的行。 舉個簡單例子：一個包含了大text和BLOB列的表，這些text和BLOB列又不經常被訪問，這時候就要把這些不經常使用的text和BLOB了劃分到另一個分區，在保證它們數據相關性的同時還能提高訪問速度。 在數據庫供應商開始在他們的數據庫引擎中建立分區（主要是水平分區）時，DBA和建模者必須設計好表的物理分區結構，不要保存冗余的數據（不同表中同時都包含父表中的數據）或相互聯結成一個邏輯父對象（通常是視圖）。這種做法會使水平分區的大部分功能失效，有時候也會對垂直分區產生影響。 在MySQL 5.1中進行分區 ?????MySQL5.1中最激動人心的新特性應該就是對水平分區的支持了。這對MySQL的使用者來說確實是個好消息，而且她已經支持分區大部分模式： ????????? Range（范圍） – 這種模式允許DBA將數據劃分不同范圍。例如DBA可以將一個表通過年份劃分成三個分區，80年代（1980's）的數據，90年代（1990's）的數據以及任何在2000年（包括2000年）后的數據。? ????????? Hash（哈希） – 這中模式允許DBA通過對表的一個或多個列的Hash Key進行計算，最后通過這個Hash碼不同數值對應的數據區域進行分區，。例如DBA可以建立一個對表主鍵進行分區的表。? ????????? Key（鍵值） – 上面Hash模式的一種延伸，這里的Hash Key是MySQL系統產生的。? ????????? List（預定義列表） – 這種模式允許系統通過DBA定義的列表的值所對應的行數據進行分割。例如：DBA建立了一個橫跨三個分區的表，分別根據2004年2005年和2006年值所對應的數據。? ???????? ?Composite（復合模式） - 很神秘吧，哈哈，其實是以上模式的組合使用而已，就不解釋了。舉例：在初始化已經進行了Range范圍分區的表上，我們可以對其中一個分區再進行hash哈希分區。 ????分區帶來的好處太多太多了，有多少？俺也不知道，自己猜去吧，要是覺得沒有多少就別用，反正俺也不求你用。不過在這里俺強調兩點好處： 性能的提升（Increased performance） - 在掃描操作中，如果MySQL的優化器知道哪個分區中才包含特定查詢中需要的數據，它就能直接去掃描那些分區的數據，而不用浪費很多時間掃描不需要的地方了。需要舉個例子？好啊，百萬行的表劃分為10個分區，每個分區就包含十萬行數據，那么查詢分區需要的時間僅僅是全表掃描的十分之一了，很明顯的對比。同時對十萬行的表建立索引的速度也會比百萬行的快得多得多。如果你能把這些分區建立在不同的磁盤上，這時候的I/O讀寫速度就“不堪設想”（沒用錯詞，真的太快了，理論上100倍的速度提升啊，這是多么快的響應速度啊，所以有點不堪設想了）了。 對數據管理的簡化（Simplified data management） - 分區技術可以讓DBA對數據的管理能力提升。通過優良的分區，DBA可以簡化特定數據操作的執行方式。例如：DBA在對某些分區的內容進行刪除的同時能保證余下的分區的數據完整性(這是跟對表的數據刪除這種大動作做比較的)。 此外分區是由MySQL系統直接管理的，DBA不需要手工的去劃分和維護。例如：這個例如沒意思，不講了，如果你是DBA，只要你劃分了分區，以后你就不用管了就是了。 站在性能設計的觀點上，俺們對以上的內容也是相當感興趣滴。通過使用分區和對不同的SQL操作的匹配設計，數據庫的性能一定能獲得巨大提升。下面咱們一起用用這個MySQL 5.1的新功能看看。 下面所有的測試都在Dell Optiplex box with a Pentium 4 3.00GHz processor, 1GB of RAM機器上（炫耀啊……），Fedora Core 4和MySQL 5.1.6 alpha上運行通過。 如何進行實際分區 看看分區的實際效果吧。我們建立幾個同樣的MyISAM引擎的表，包含日期敏感的數據，但只對其中一個分區。分區的表（表名為part_tab）我們采用Range范圍分區模式，通過年份進行分區： mysql> CREATE TABLE part_tab ??? ->?? ???(?c1 int default NULL, ??? ->?c2 varchar(30) default NULL, ??? ->?c3 date default NULL ??? -> ??? ->????? ) engine=myisam ??? ->????? PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995), ??? ->????? PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , ??? ->????? PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , ??? ->????? PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , ??? ->????? PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , ??? ->????? PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), ??? ->????? PARTITION p11 VALUES LESS THAN MAXVALUE ); Query OK, 0 rows affected (0.00 sec) 注意到了這里的最后一行嗎？這里把不屬于前面年度劃分的年份范圍都包含了，這樣才能保證數據不會出錯，大家以后要記住啊，不然數據庫無緣無故出錯你就爽了。那下面我們建立沒有分區的表（表名為no_part_tab）： mysql> create table no_part_tab ??? -> (c1 int(11) default NULL, ??? -> c2 varchar(30) default NULL, ??? -> c3 date default NULL) engine=myisam; Query OK, 0 rows affected (0.02 sec) 下面咱寫一個存儲過程（感謝Peter Gulutzan給的代碼，如果大家需要Peter Gulutzan的存儲過程教程的中文翻譯也可以跟我要，chenpengyi◎gmail.com），它能向咱剛才建立的已分區的表中平均的向每個分區插入共8百萬條不同的數據。填滿后，咱就給沒分區的克隆表中插入相同的數據： mysql> delimiter // mysql> CREATE PROCEDURE load_part_tab() ??? -> begin ??? ->?declare v int default 0; ??? ->????????? while v < 8000000 ??? ->?do ??? ->?insert into part_tab ??? ->?values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652)); ??? ->?set v = v + 1; ??? ->?end while; ??? -> end ??? -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call load_part_tab(); Query OK, 1 row affected (8 min 17.75 sec) mysql> insert into no_part_tab select * from part_tab; Query OK, 8000000 rows affected (51.59 sec) Records: 8000000?Duplicates: 0?Warnings: 0 表都準備好了。咱開始對這兩表中的數據進行簡單的范圍查詢吧。先分區了的，后沒分區的，跟著有執行過程解析（MySQL Explain命令解析器），可以看到MySQL做了什么： mysql> select count(*) from no_part_tab where ??? -> c3 > date '1995-01-01' and c3 < date '1995-12-31'; +----------+ | count(*) | +----------+ |?? 795181 | +----------+ 1 row in set (38.30 sec) ? mysql> select count(*) from part_tab where ??? -> c3 > date '1995-01-01' and c3 < date '1995-12-31'; +----------+ | count(*) | +----------+ |?? 795181 | +----------+ 1 row in set (3.88 sec) ? mysql> explain select count(*) from no_part_tab where ??? -> c3 > date '1995-01-01' and c3 < date '1995-12-31'\G *************************** 1. row *************************** ?????????? id: 1 ?select_type: SIMPLE ??????? table: no_part_tab ???????? type: ALL possible_keys: NULL ????????? key: NULL ????? key_len: NULL ????????? ref: NULL ???????? rows: 8000000 ??????? Extra: Using where 1 row in set (0.00 sec) ? mysql> explain partitions select count(*) from part_tab where ??? -> c3 > date '1995-01-01' and c3 < date '1995-12-31'\G *************************** 1. row *************************** ?????????? id: 1 ?select_type: SIMPLE ??????? table: part_tab ?? partitions: p1 ???????? type: ALL possible_keys: NULL ????????? key: NULL ????? key_len: NULL ????????? ref: NULL ???????? rows: 798458 ???? ???Extra: Using where 1 row in set (0.00 sec) 從上面結果可以容易看出，設計恰當表分區能比非分區的減少90％的響應時間。而命令解析Explain程序也告訴我們在對已分區的表的查詢過程中僅對第一個分區進行了掃描，其他都跳過了。 嗶厲吧拉，說阿說……反正就是這個分區功能對DBA很有用拉，特別對VLDB和需要快速反應的系統。 對Vertical Partitioning的一些看法 雖然MySQL 5.1自動實現了水平分區，但在設計數據庫的時候不要輕視垂直分區。雖然要手工去實現垂直分區，但在特定場合下你會收益不少的。例如在前面建立的表中，VARCHAR字段是你平常很少引用的，那么對它進行垂直分區會不會提升速度呢？咱們看看測試結果： mysql> desc part_tab; +-------+-------------+------+-----+---------+-------+ | Field | Type??????? | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | c1??? | int(11)???? | YES?|???? | NULL??? |?????? | | c2??? | varchar(30) | YES?|???? | NULL??? |?????? | | c3??? | date??????? | YES?|???? | NULL??? |?????? | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.03 sec) ? mysql> alter table part_tab drop column c2; Query OK, 8000000 rows affected (42.20 sec) Records: 8000000?Duplicates: 0?Warnings: 0 ? mysql> desc part_tab; +-------+---------+------+-----+---------+-------+ | Field | Type??? | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c1??? | int(11) | YES?|???? | NULL??? |?????? | | c3??? | date??? | YES?|???? | NULL??? |?????? | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) ? mysql> select count(*) from part_tab where ??? -> c3 > date '1995-01-01' and c3 < date '1995-12-31'; +----------+ | count(*) | +----------+ |?? 795181 | +----------+ 1 row in set (0.34 sec) 在設計上去掉了VARCHAR字段后，不止是你，俺也發現查詢響應速度上獲得了另一個90％的時間節省。所以大家在設計表的時候，一定要考慮，表中的字段是否真正關聯，又是否在你的查詢中有用？ 補充說明? 這么簡單的文章肯定不能說全MySQL 5.1 分區機制的所有好處和要點（雖然對自己寫文章水平很有信心），下面就說幾個感興趣的： 支持所有存儲引擎(MyISAM, Archive, InnoDB, 等等) 對分區的表支持索引，包括本地索引local indexes，對其進行的是一對一的視圖鏡像，假設一個表有十個分區，那么其本地索引也包含十個分區。 關于分區的元數據Metadata的表可以在INFORMATION_SCHEMA數據庫中找到，表名為PARTITIONS。 All SHOW 命令支持返回分區表以及元數據的索引。 對其操作的命令和實現的維護功能有（比對全表的操作還多）： ADD PARTITION DROP PARTITION COALESCE PARTITION REORGANIZE PARTITION ANALYZE PARTITION CHECK PARTITION OPTIMIZE PARTITION REBUILD PARTITION REPAIR PARTITION 站在性能主導的觀點上來說，MySQL 5.1的分區功能能給數據性能帶來巨大的提升的同時減輕DBA的管理負擔，如果分區合理的話。如果需要更多的資料可以去 http://dev.mysql.com/doc/refman/5.1/en/partitioning.html 或 http://forums.mysql.com/list.php?106 獲得相關資料。 關于MySQL分區的使用方法很快發布上來，這里有什么錯誤歡迎指出，或給我來信 ——2006-05-05陳朋奕 (http://www.fanqiang.com)

通過分區（Partition）提升MySQL性能