6.MySQL最佳化---進階進階之表的設計及最佳化

標籤：理解   tiny   必須   刪除   常用   沒有   空值   進階   資料   

轉自互連網整理. 最佳化之路進階進階——表的設計及最佳化

最佳化①：建立正常化表，消除資料冗餘

資料庫範式是確保資料庫結構合理，滿足各種查詢需要、避免資料庫操作異常的資料庫設計方式。滿足範式要求的表，稱為正常化表，範式產生於20世紀70年代初，一般表設計滿足前三範式就可以，在這裡簡單介紹一下前三範式。

通俗的給大家解釋一下（可能不是最科學、最準確的理解）

第一範式：屬性(欄位)的原子性約束，要求屬性具有原子性，不可再分割；

第二範式：記錄的惟一性約束，要求記錄有惟一標識，每條記錄需要有一個屬性來做為實體的唯一標識。

第三範式：屬性(欄位)冗餘性的約束，即任何欄位不能由其他欄位派生出來，在通俗點就是：主鍵沒有直接關係的資料列必須消除(消除的辦法就是再建立一個表來存放他們，當然外鍵除外)

當然，其實我們經常打破第三範式。。。且不可避免的，其實就是要在資料冗餘和處理速度之間找到合適的平衡點。

最佳化②：合適的欄位屬性

先舉個例子：

以前我做過的p2p中項目中，關於資金流水類型的欄位的選取。本來資金流水類型總共就那麼十幾種，基本固定死的,那我們就可以選擇tinyint(4)就完全足夠了，對應的是java的byte。 （要知道的是，tinyint的長度就是8位，tinyint(1)和tinyint(4)只是顯示長度）

下面以下給出幾個欄位的建議：

1）數值型欄位的比較比字串的比較效率高得多，所以欄位類型盡量使用最小、最簡單的資料類型。如IP地址可以使用int類型，如我上面的例子。

2）建議不要使用DOUBLE，不僅僅只是儲存長度的問題，同時還會存在精確性的問題。

3）對於整數的儲存，在資料量較大的情況下，建議區分開 TINYINT / INT / BIGINT 的選擇（當然，那已經是很老的事情了，現在其實不差這點效能）

4）char是固定長度，所以它的處理速度比varchar快得多，但缺點是浪費儲存空間，不能在行尾儲存空格。在MySQL中，MyISAM建議使用固定長度代替可變長度列；InnoDB建議使用varchar類型，因為在InnoDB中，內部行儲存格式沒有區分固定長度和可變長度。

5）盡量不要允許NULL，除非必要，可以用NOT NULL+DEFAULT代替。

6）text與blob區別：blob儲存位元據；text儲存字元資料，有字元集。text和blob不能有預設值。

實際情境：text與blob主要區別是:

text用來儲存字元資料（如文章，日記等），blob用來儲存位元據（如照片等）。

blob與text在執行了大量刪除操作時候，有效能問題（產生大量的“空洞“），為提高效能建議定期optimize table 對這類表進行磁碟重組。

7）自增欄位要慎用，不利於資料移轉

8）強烈反對在資料庫中存放 LOB 類型資料，雖然資料庫提供了這樣的功能，但這不是他所擅長的，我們更應該讓合適的工具做他擅長的事情，才能將其發揮到極致。（反正我麼碰到過LOB類型資料）

9）盡量將表欄位定義為NOT NULL約束，這時由於在MySQL中含有空值的列很難進行查詢最佳化，NULL值會使索引以及索引的統計資訊變得很複雜，可以使用0或者Null 字元串來代替。

10）盡量使用TIMESTAMP類型，因為其儲存空間只需要 DATETIME 類型的一半，且日期類型中只有它能夠和實際時區相對應。對於只需要精確到某一天的資料類型，建議使用DATE類型，因為他的儲存空間只需要3個位元組，比TIMESTAMP還少。

最佳化③：索引

索引是一個表最佳化的重要指標，在表最佳化中佔有極其重要的成分，所以上篇索引最佳化詳解沒看過的可以先看看，這裡不再贅敘。

最佳化④：表的拆分（大表拆小表）

1、垂直分割（其實就是列的拆分將原來的一個有很多列的表拆分成多張表）

注意：垂直分割應該在資料表設計之初就執行的步驟，然後查詢的時候用jion關鍵起來即可;

通常我們按以下原則進行垂直分割:

把不常用的欄位單獨放在一張表;

把text，blob等大欄位拆分出來放在附表中;

經常組合查詢的列放在一張表中;

缺點也很明顯，需要使用冗餘欄位，而且需要join操作。

2、水平分割（ 如果你發現某個表的記錄太多，例如超過一千萬條，則要對該表進行水平分割。水平分割的做法是，以該表主鍵的某個值為界線，將該表的記錄水平分割為兩個表。）

當然，我們還可以用增量法。如流水這類不會改變的資料,我們用增量查詢。

1.建立一張日儲值表,記錄每天儲值總額

2.每天用定時器對當前儲值記錄進行結算

3.建立每月儲值表,每月最後一天用定時器計算總額

4.則要查詢總額,則從月報表中匯總，再從日報表查詢當天之前的資料匯總，再加上今天的使用當天流水表記錄今天的流水，三張表加起來,匯總。這樣子效率是極好的！

最佳化⑤：傳說中的‘三少原則’

①：資料庫的表越少越好.

②：表的欄位越少越好.

③：欄位中的組合主鍵、複合式索引越少越好.

當然這裡的少是相對的，是減少資料冗餘的重要設計理念。

6.MySQL最佳化---進階進階之表的設計及最佳化