淺談MySQL數據庫設計性能提高的若干辦法

基本上在設計數據庫表的時候，首先考慮設計要滿足功能需求，這是最根本的，其次是滿足性能需求，再次則是滿足擴展性需求，這一點在大規模系統中是必須要考慮的。功能性需求比較容易滿足，下面我主要談談對性能和擴展性需求的一些設計方法。
沒人不想速度更快，但是怎樣才能更快呢。設計高性能的表，我認為主要需要做好：設計精簡合理的結構、減小數據量，具體的做法下面逐個分析。
合理利用字段類型和長度。字段類型盡可能反映真實的數據含義，滿足功能外字段應該盡可能的短。 比 如能用int字段的就不要用bigint，如果在某一個關系表里只有兩個id字段，那么bigint類型顯然比int類型的大了一倍。不同的數據庫系統里 面varchar和text類型在數據長度限制上不一樣，性能上也不一樣，選取要謹慎。標記位字段如果有bit就用bit類型，否則就用byte，用 int就很浪費了（下面有一種特例）。

選取高效的主鍵和索引。關于主鍵的選取，特別需要注意，因為對表中數據的讀取都直接或間接通過主鍵，所以應該根據應用的特性設計滿足最接近數據存取順序的主鍵。例如數據讀取按照r1、r2、r3的順序，那么他們的主鍵也最好是1、2、3的順序。有些人喜歡在關系表里面也另外加一個主鍵字段，我認為這樣算是浪費空間，而用關系ID作聯合主見更合理。

    索引的大小基本上由字段來決定，所以需要建立索引的字段應該簡化到最小。但是有些字段必須建立索引卻又無法簡化，這時候可以考慮用hash算法計算出較小的值作為索引。例如url字段不適合做索引，但是可以用一個url_md5字段來存儲url的md5值來作為索引，有效降低鍵值長度。
精簡表結構。一個表復雜了不光處理起來更麻煩，而其性能也不好。如果一個表里面有多部分（幾個字段合起來為一部分）的字段并不同時存取，那么這多部分字段應該根據存取特性分開為多個表，這樣避免并發操作的鎖競爭。如果實在無法再分并且還是字段眾多，那么可以把描述同一個對象的字段合并成一個字段存儲，有效降低字段數目，如果空字段較多時，這樣更能節省資源。例 如，在customer表里面company_name，company_phone等字段可以合并為company字段，當然這樣做的前提是 company_name字段不需要單獨作為查詢條件（如果使用數據庫的xml技術，conpmay_name也可以作為查詢條件）。
其實影響數據庫性能的還有包括磁盤IO、內存、數據庫鎖、系統配置、數據庫配置、CPU性能等其他因素，但是這些并不在本文范疇。在大規模系統中，除了性能，可擴展性也是設計的關鍵字點，而數據庫表擴展性主要包含表邏輯結構、功能字段的增加、分表等。
對 于表的邏輯結構我遵循的設計原則：一個表只包含一個主要實體，如果主要實體中包含從屬實體數據，并且多個主要實體共享一個從屬實體，則把從屬實體單獨設計 為表，與主要實體關聯，這樣增加一個從屬實體增加單獨的表就行，不會影響以前的功能。如果主要實體不共享從屬實體，把從屬實體多個字段打包合并為一個字 段。合并字段的方式在上面也有提及，它不僅減少字段數目，而且讓在合并的字段中增加數據字段變得非常容易。

在數據庫里面經常用到標記位字段，取值只有0/1（true/false），有時候一個表里有很多這樣的字段，這種情況下我認為把所有標記 為字段合并到一個數字字段更好，數字中的每一位就表示一個標記位，例如用一個int型字段可以表示32個標記位。這可能帶來一些使用上的不便，不過卻大大 增加了可擴展性。例如當16個標記位字段合并到int型字段后，還留下了16位的擴展余地。并且用byte、int還是bigint可以隨取所需。
增 加表字段，好像也并不是難事，一條SQL而已。但是如果在Mysql里面，修改表結構后引擎會導出再導入數據，在大數據量下（比如1000w、1億）增加 字段變得幾乎不可能。對于這個問題，有人喜歡提前在表里面多加一到多個保留字段，我個人比較反對這樣的做法：一是擴展性有限、二是命名太奇怪、三是類型不 一定合適。我的設計原則：小表（比如50w行、100MB數據以內的表）不用特別考慮此擴展性問題，設計時只需要設計符合當前需求就可以，因為即使以后對 結構修改，也可以在很快的時間內完成。關系表等結構很穩定的表也不用考慮此問題。復雜的大表里，首先確定核心的業務實體字段、外鍵和索引，而其他的字段則 根據情況包合并到一個extra（xml或者字符串類型）的字段里，這樣也就可以滿足了以后的擴展需求，因為字符串或者xml結構里增加數據字段是很容易 的事情。
分表（非分區，分區后并不會產生多個表，在部署上和分表會有不同，并非所有的數據庫版本都支持），也就是對表垂直切分，得到結構相同的多 個小表，是提升大表性能的首選方案。分表最基本的方法就是，固定法：根據ID特性把表拆分成固定的N個表、動態增長法：根據ID值分成等值區間任意多表、 外鍵劃分法：根據外鍵值得特性劃分。如果ID增長沒有規律，那么分表可采用固定法，基本算法為：用ID對N取模或者獲取HASH（ID）的某部分字符串作 為表名的一部分。如果ID連續變化，則采用而動態增長法，基本算法為：測試單表最合理的數據行數N，然后根據N作為區間長度對ID拆分，拆分結果為1- N，N+1-2N…。外鍵劃分法是根據外鍵值對表進行劃分，基本的方法也就是固定法和動態增長法。不同的分表方法是由數據的特性和數據之間的關系決定的， 例如需要根據URL查詢到文章，由于URL是無規律的，那么分表方法可以為固定法，按照URL的MD5值對表進行劃分。例如論壇的帖子可以按照論壇板塊 ID來分表，每個板塊一個表多個板塊一個表，這是外鍵劃分法。如果論壇和帖子是多對多關系，那么帖子可以采用動態增長法分表，然后再把帖子和板塊關系表采 用外鍵劃分法來分。這里描述的方法算是比較基本的方法，而真實系統中分表情況要復雜的多，例如用戶表里如果根據ID分表，但是又需要根據Email/密碼 登錄，如果有10個用戶表，登錄操作顯然是很昂貴的，怎么辦呢？分表，不是簡單的事情。