資料庫效能最佳化策略__資料庫

有資料表明：使用者可以承受的最大等待時間為8秒。
之前曾見過某個產品的一個列表頁，40秒左右才能載入出來，幾乎沒有進行任何最佳化措施。
沒有索引，沒有緩衝機制，沒有進行sql最佳化(sql語句很長，並且各種left join表關聯)。
資料庫最佳化策略有很多，設計初期，建立好的資料結構對於後期效能最佳化至關重要。因為資料庫結構是系統的基石，基礎打不好，使用各種最佳化策略，也不能達到很完美的效果。 一：正常化與反正常化

大家都聽說過：資料庫設計三大範式.
1．第一範式(確保每列保持原子性)
第一範式是最基本的範式。如果資料庫表中的所有欄位值都是不可分解的原子值，就說明該資料庫表滿足了第一範式。

2．第二範式(確保表中的每列都和主鍵相關)
第二範式在第一範式的基礎之上更進一層。第二範式需要確保資料庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關（主要針對聯合主鍵而言）。也就是說在一個資料庫表中，一個表中只能儲存一種資料，不可以把多種資料儲存在同一張資料庫表中。

3．第三範式(確保每列都和主鍵列直接相關,而不是間接相關)
第三範式需要確保資料表中的每一列資料都和主鍵直接相關，而不能間接相關。

沒有最好的設計，只有最合適的設計，所以不要過分注重理論。三範式可以作為一個基本依據，不要生搬硬套。
資料庫操作中最為耗時的操作就是 IO 處理，大部分資料庫操作 90% 以上的時間都花在了 IO 讀寫上面。所以儘可能減少 IO 讀寫量，可以在很大程度上提高資料庫操作的效能。 二:最佳化策略：

在設計表時應同時考慮對某些表進行反正常化，方法有以下幾種:

一是分割表。
分割表可分為水平分割表和垂直分割表兩種:
水平分割是按照行將一個表分割為多個表，這可以提高每個表的查詢速度，但查詢、更新時要選擇不同的表，統計時要匯總多個表，因此應用程式會更複雜。
垂直分割是對於一個列很多的表，若某些列的訪問頻率遠遠高於其它列，就可以將主鍵和這些列作為一個表，將主鍵和其它列作為另外一個表。通過減少列的寬度，增加了每個資料頁的行數，一次I/O就可以掃描更多的行，從而提高了訪問每一個表的速度。但是由於造成了多表串連，所以應該在同時查詢或更新不同分割表中的列的情況比較少的情況下使用。

二是保留冗餘列。當兩個或多個表在查詢中經常需要串連時，可以在其中一個表上增加若干冗餘的列，以避免表之間的串連過於頻繁，一般在冗餘列的資料不經常變動的情況下使用。

三是增加衍生的資料行。衍生的資料行是由表中的其它多個列的計算所得，增加衍生的資料行可以減少統計運算，在資料匯總時可以大大縮短運算時間。

在資料庫的設計中，資料應當按兩種類別進行組織:頻繁訪問的資料和頻繁修改的資料。
對於頻繁訪問但是不頻繁修改的資料，內部設計應當物理不正常化。
對於頻繁修改但並不頻繁訪問的資料，內部設計應當物理正常化。
有時還需將正常化的表作為邏輯資料庫設計的基礎，然後再根據整個應用系統的需要，物理地非正常化資料。
規範與反規範都是建立在實際的操作基礎之上的約束，脫離了實際兩者都沒有意義。只有把兩者合理地結合在一起，才能相互補充，發揮各自的優點。

適當拆分
有些時候，我們可能會希望將一個完整的對象對應於一張資料庫表，這對於應用程式開發來說是很有好的，但是有些時候可能會在效能上帶來較大的問題。

當我們的表中存在類似於 TEXT 或者是很大的 VARCHAR類型的大欄位的時候，如果我們大部分訪問這張表的時候都不需要這個欄位，我們就該義無反顧的將其拆分到另外的獨立表中，以減少常用資料所佔用的儲存空間。這樣做的一個明顯好處就是每個資料區塊中可以儲存的資料條數可以大大增加，既減少物理 IO 次數，也能大大提高記憶體中的快取命中率。

適度冗餘
為什麼我們要冗餘?這不是增加了每條資料的大小，減少了每個資料區塊可存放記錄條數嗎?
確實，這樣做是會增大每條記錄的大小，降低每條記錄中可存放資料的條數，但是在有些情境下我們仍然還是不得不這樣做：
1.被頻繁引用且只能通過 Join 2張(或者更多)大表的方式才能得到的獨立小欄位。
2.這樣的情境由於每次Join僅僅只是為了取得某個小欄位的值，Join到的記錄又大，會造成大量不必要的 IO，完全可以通過空間換取時間的方式來最佳化。不過，冗餘的同時需要確保資料的一致性不會遭到破壞，確保更新的同時冗餘欄位也被更新。 三：其他技巧：

1:欄位類型最佳化
下面的這些關於欄位類型的最佳化建議主要適用於記錄條數較多，資料量較大的情境，因為精細化的資料類型設定可能帶來維護成本的提高，過度最佳化也可能會帶來其他的問題：

(1)數字類型
非萬不得已不要使用DOUBLE，不僅僅只是儲存長度的問題，同時還會存在精確性的問題。同樣，固定精度的小數，也不建議使用DECIMAL。
非萬不得已不要使用DOUBLE，不僅僅只是儲存長度的問題，同時還會存在精確性的問題。同樣，固定精度的小數，也不建議使用DECIMAL
(2)字元類型
非萬不得已不要使用 TEXT 資料類型，其處理方式決定了他的效能要低於char或者是varchar類型的處理。定長欄位，建議使用 CHAR 類型，不定長欄位盡量使用 VARCHAR，且僅僅設定適當的最大長度，而不是非常隨意的給一個很大的最大長度限定，因為不同的長度範圍，MySQL也會有不一樣的儲存處理。

(3)時間類型
盡量使用TIMESTAMP類型，因為其儲存空間只需要 DATETIME 類型的一半。對於只需要精確到某一天的資料類型，建議使用DATE類型，因為他的儲存空間只需要3個位元組，比TIMESTAMP還少。不建議通過INT類型類儲存一個unix timestamp 的值，因為這太不直觀，會給維護帶來不必要的麻煩，同時還不會帶來任何好處。

2:合理使用索引

3：緩衝機制

4：用EXPLAIN使你的SELECT查詢更加清晰

5：利用LIMIT 1取得唯一行

6： 盡量避免SELECT *命令

7：使用ENUM而不是VARCHAR

8:儘可能的使用NOT NULL
　NULL 類型比較特殊，SQL 難最佳化。雖然 MySQL NULL類型和 Oracle 的NULL 有差異，會進入索引中，但如果是一個複合式索引，那麼這個NULL 類型的欄位會極大影響整個索引的效率。此外，NULL 在索引中的處理也是特殊的，也會佔用額外的存放空間。

　很多人覺得 NULL 會節省一些空間，所以盡量讓NULL來達到節省IO的目的，但是大部分時候這會適得其反，雖然空間上可能確實有一定節省，倒是帶來了很多其他的最佳化問題，不但沒有將IO量省下來，反而加大了SQL的IO量。所以盡量確保 DEFAULT 值不是 NULL，也是一個很好的表結構設計最佳化習慣。