【轉】MySQL 建表的優化策略 小結

mysql 資料庫建表經驗總結，用做優化表結構的參考 目錄 
1. 字符集的選擇 1 
2. 主鍵 1 
3. 外來鍵 2 
4. 索引 2 
4.1. 以下情況適合於建立索引 2 
4.2. 以下的情況下不適合建立索引 3 
4.3. 聯合索引 3 
4.4. 索引長度 4 
5. 特殊欄位 4 
5.1. 冗餘欄位 4 
5.2. 分割欄位 4 
5.3. BLOB和CLOB 5 
6. 特殊 5 
6.1. 表格分割 5 
6.2. 使用非事務表型別 5 
1. 字符集的選擇 
如果確認全部是中文，不會使用多語言以及中文無法表示的字元，那麼GBK是首選。 
採用UTF-8編碼會佔用3個位元組，而GBK只需要2個位元組。 
2. 主鍵 
儘可能使用長度短的主鍵 
系統的自增型別AUTO_INCREMEN, 而不是使用類似uuid()等型別。如果可以使用外來鍵做主鍵，則更好。比如1：1的關係，使用主表的id作為從表的主鍵。 
主鍵的欄位長度需要根據需要指定。 
tinyint 從 2的7次方-1 ：-128 到 127 
smallint 從 2的15次方-1 ：-32768 到 32767 
mediumint 表示為 2的23次方-1： 從 -8388608 到8388607 
int 表示為 2的31次方-1 
bigint 表示為 2的63次方-1 
在主鍵上無需建單獨的索引，因為系統內部為主鍵建立了聚簇索引。 
允許在其它索引上包含主鍵列。 
3. 外來鍵 
外來鍵會影響插入和更新效能，對於批量可靠資料的插入，建議先遮蔽外來鍵檢查。 
對於資料量大的表，建議去掉外來鍵，改由應用程式進行資料完整性檢查。 
儘可能用選用對應主表的主鍵作作為外來鍵，避免選擇長度很大的主表唯一鍵作為外來鍵。 
外來鍵是預設加上索引的 
4. 索引 
建立索引，要在適當的表，適當的列建立適當數量的適當索引。在查詢優先和更新優先之間做平衡。 
4.1. 以下情況適合於建立索引 
在經常需要搜尋的列上，可以加快搜尋的速度 
在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構 
在經常用在連線的列上，這些列主要是一些外來鍵，可以加快連線的速度 
在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的 
在經常需要排序的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間 
在經常使用在WHERE子句中的列上面建立索引，加快條件的判斷速度。 
4.2. 以下的情況下不適合建立索引 
對於那些在查詢中很少使用或者參考的列不應該建立索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，並不能提高查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增大了空間需求。 
對於那些只有很少資料值的列也不應該增加索引。這是因為，由於這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的資料行佔了表中資料行的很大比例，即需要在表中搜尋的資料行的比例很大。增加索引，並不能明顯加快檢索速度。 
對於那些定義為text, image和bit資料型別的列不應該增加索引。這是因為，這些列的資料量要麼相當大，要麼取值很少。 
當修改效能遠遠大於檢索效能時，不應該建立索引。這是因為，修改效能和檢索效能是互相矛盾的。 
如果表資料很少，比如每個省按市做彙總的表，一般低於2000，且資料量基本沒有變化。此時增加索引無助於查詢效能，卻會極大的影響更新效能。 
當增加索引時，會提高檢索效能，但是會降低修改效能。當減少索引時，會提高修改效能，降低檢索效能。因此，當對修改效能的要求遠遠大於檢索效能時，不應該建立索引。 
4.3. 聯合索引 
在特定查詢裡，聯合索引的效果高於多個單一索引，因為當有多個索引可以使用時，MySQL只能使用其中一個。 
在查詢裡，同時用到了聯合索引包含的前幾個列名，都會使用到聯合索引，否則將部分或不會用到。比如我們有一個firstname、 lastname、age列上的多列索引，我們稱這個索引為fname_lname_age。當搜尋條件是以下各種列的組合時，MySQL將使用 fname_lname_age索引： 
firstname，lastname，age 
firstname，lastname 
firstname 
從另一方面理解，它相當於我們建立了(firstname，lastname，age)、(firstname，lastname)以及(firstname)這些列組合上的索引。 
4.4. 索引長度 
對於CHAR或者Varchar的列，索引可以根據資料的分佈情況，用列的一部分參與建立索引。 
create index idx_t_main on t_main(name(3)); 
這裡就是指定name的前三個字元參與索引，而不是全部 
最大允許的長度為1000個位元組，對已GBK編碼則是500個漢字 
5. 特殊欄位 
5.1. 冗餘欄位 
就是用空間換取時間。如果大表查詢裡經常要join某個基礎表，且這個資料基本不變，比如人的姓名，城市的名字等。一旦基礎表發生變動，則需要更新所有涉及到的冗餘表。 
5.2. 分割欄位 
如果經常出現以某個欄位的某個區域性進行檢索和彙總(substring())，可以考慮將這一部分獨立出來。 
比如統計姓名裡，每種姓氏的人數，可以考慮實現就按照姓和名分別儲存，而不是一個欄位。 
還有就是某些上下級結構的實現，也可以考慮將不同的級別放在不同的欄位裡。 
5.3. BLOB和CLOB 
此類欄位一般資料量很大，建議設計上資料庫可以只儲存其外部連線，而資料以其它方式儲存，比如系統檔案。 
6. 特殊 
6.1. 表格分割 
如果一個表有許多的列，但平時參與查詢和彙總的列卻並不是很多，此時可以考慮將表格拆分成2個表，一個是常用的欄位，另一個是很少用到的欄位。 
6.2. 使用非事務表型別 
MySQL支援多種表型別，其中InnoDB型別是支援事物的，而MyISAM型別是不支援的，但MyISAM速度更快。對於某些資料，比如地理行政劃分，民族等不可能參與事務的資料，可以考慮用MyISAM型別的表格。 
但InnoDB的表，將無法用MyISAM表資料做外來鍵約束了。 
MyISAM表參與的事務，其InnoDB表可以正常的提交和回滾，但不影響MyISAM表。