?

數據庫設計原則

標準化和規范化 數據庫設計范式(3NF)

第一范式

數據屬性唯一標示

在任何一個關系數據庫中，第一范式（1NF）是對關系模式的基本要求，不滿足第一范式（1NF）的數據庫就不是關系數據庫。?
所謂第一范式（1NF）是指數據庫表的每一列都是不可分割的基本數據項，同一列中不能有多個值，即實體中的某個屬性不能有多個值或者不能有重復的屬性。如果出現重復的屬性，就可能需要定義一個新的實體，新的實體由重復的屬性構成，新實體與原實體之間為一對多關系。在第一范式（1NF）中表的每一行只包含一個實例的信息。例如，對于圖3-2 中的員工信息表，不能將員工信息都放在一列中顯示，也不能將其中的兩列或多列在一列中顯示；員工信息表的每一行只表示一個員工的信息，一個員工的信息在表中只出現一次。簡而言之，第一范式就是無重復的列。

第二范式

行信息唯一標示?
第二范式（2NF）是在第一范式（1NF）的基礎上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。第二范式（2NF）要求數據庫表中的每個實例或行必須可以被唯一地區分。為實現區分通常需要為表加上一個列，以存儲各個實例的唯一標識。?
員工信息表中加上了員工編號（emp_id）列，因為每個員工的員工編號是唯一的，因此每個員工可以被唯一區分。這個唯一屬性列被稱為主關鍵字或主鍵、主碼。?
第二范式（2NF）要求實體的屬性完全依賴于主關鍵字。所謂完全依賴是指不能存在僅依賴主關鍵字一部分的屬性，如果存在，那么這個屬性和主關鍵字的這一部分應該分離出來形成一個新的實體，新實體與原實體之間是一對多的關系。為實現區分通常需要為表加上一個列，以存儲各個實例的唯一標識。簡而言之，第二范式就是非主屬性非部分依賴于主關鍵字。

第三范式

信息資料唯一存儲?
滿足第三范式（3NF）必須先滿足第二范式（2NF）。簡而言之，第三范式（3NF）要求一個數據庫表中不包含已在其它表中已包含的非主關鍵字信息。例如，存在一個部門信息表，其中每個部門有部門編號（dept_id）、部門名稱、部門簡介等信息。那么在圖3-2的員工信息表中列出部門編號后就不能再將部門名稱、部門簡介等與部門有關的信息再加入員工信息表中。如果不存在部門信息表，則根據第三范式（3NF）也應該構建它，否則就會有大量的數據冗余。簡而言之，第三范式就是屬性不依賴于其它非主屬性。?
滿足第三范式的條件：?
若關系R中存在非平凡FD A1A2A3……An->B,且要么左邊{A1A2A3……An}是超鍵，要么右邊的B屬于某個鍵，則認為關系R屬于第三范式(3NF).

反范式設計

數據庫設計要嚴格遵守范式，這樣設計出來的數據庫，雖然思路很清晰，結構也很合理，但是，有的時候，卻要在一定程度上打破范式設計。?
這里其實并不矛盾，因為范式越高，設計出來的表可能越多，關系可能越復雜，但是性能卻不一定會很好，因為表一多，就增加了關聯性。這一點表現得很明顯。?
最明顯的打破范式的設計方法就是冗余法，以空間換取時間的做法，把數據冗余在多個表中，當查詢時可以減少或者是避免表之間的關聯。

數據驅動

采用數據驅動而非硬編碼的方式，許多策略變更和維護都會方便得多，大大增強系統的靈活性和擴展性。?
舉例，假如用戶界面要訪問外部數據源（文件、XML 文檔、其他數據庫等），不妨把相應的連接和路徑信息存儲在用戶界面支持表里。還有，如果用戶界面執行工作流之類的任務（發送郵件、打印信箋、修改記錄狀態等），那么產生工作流的數據也可以存放在數據庫里。角色權限管理也可以通過數據驅動來完成。事實上，如果過程是數據驅動的，你就可以把相當大的責任推給用戶，由用戶來維護自己的工作流過程。

考慮各種變化和記錄數據的基本信息

在設計數據庫的時候考慮到哪些數據字段將來可能會發生變更。?
舉例 數據的添加時間,更新時間 用戶的 注冊ip 和 登錄ip 等

數據庫建立

數據庫表名

1. 表名應具有描述性，杜絕一切拼音或拼音英文混雜的命名方式
2. 表名運行使用字母，數字和下劃線，不允許使用其他字符。表名使用單詞開頭，不運行使用數字和下劃線開頭
3. 表名一律有統一前綴，前綴表名之間下劃線鏈接。使用前綴可以讓同一項目在一個庫中安裝多個。
4. 表名單詞一律小寫，單詞之間使用下劃線鏈接
5. 表名長度不能超過64個字符
6. 所有數據表名稱，只要其名稱是可數名詞，則建議以復數方式命名，例如：xs_users（用戶表）
7. 表名要回避MySQL的保留字

數據庫表字段名

1. 字段名應具有描述性，杜絕一切拼音或拼音英文混雜的命名方式
2. 字段名允許使用字母、數字和下劃線，不允許使用其他字符。字段名鼓勵使用與所在表的內容相關單詞開頭，允許但不鼓勵使用數字和其他字符開頭。
3. 字段名一律小寫，單詞之間使用下劃線鏈接。
4. 字段名長度不能超過64個字符
5. 字符類型和長度在不同數據表中必須保證一致性，不允許出現同一字段在一個表中為整型但在另外一個表中為字符型的情況。
6. 當幾個表間的字段有關連時，要注意表與表之間關連字段命名的統一，如xs_orders表中的uid與xs_carts表中的uid，都保存有xs_users表中的id。
7. 存儲多項內容的字段或代表數量的字段，也應當以復數方式明明，例如views
8. 每個表都建議有一個代表id自增量的字段，可使用全稱的形式，也可以只將其命名為id

字段索引名稱

1. 索引名稱允許使用字母、數字和下劃線，不允許使用其他字符
2. 對任何外鍵采用非成組索引
3. 不要索引text/blob類型的字段，不索引字符過多的字段
4. 根據業務需求建立組合索引
5. 索引長度不能超過64個字符
6. 頻繁進行數據操作的表，不要建立太多的索引

字段結構

進行表結構設計時，應當做到恰到好處，反復推敲，從而實現最優的數據存儲體系? 短小 ? 精悍

1. NULL值的字段，數據庫在進行比較操作時，會先判斷其是否為NULL，非NULL時才進行值的比對。因此基于效率的考慮，所有字段均不能為空，即全部使用NOT NULL的屬性修飾字段；
2. 如果不會使用存儲非負數的字段，必須設置為unsigned類型，能獲得范圍大一倍的數值存儲空間
3. 任何類型的數據表，字段空間應當本著足夠用、不浪費的原則
4. 個別字段類型在數據結構設計的時候需要注意：enum枚舉類型由tinyint類型代替
5. 包含任何varchar、text等變長字段的數據表，即為變長表，反之為定長表。在設計表結構時如果能夠使用定長數據類型，盡量用定長的，因為定長表的查詢、檢索、更新速度都很快。必要時可以把部分關鍵的、承擔頻繁訪問的表拆分，例如定長數據一個表，非定長數據一個表。
6. 更小的字段類型永遠比更大的字段類型處理要快得多。對于字符型，其處理時間與字符串長度直接相關。一般情況下，較小的表處理更快。對于定長表，應該選擇較小的類型，只要能存儲夠節省空間。一個text類型的值用2字節記錄值的長度，而一個longtext則用4字節記錄其值的長度。如果存儲的值的長度不超過64kb，
7. 數值運算一般比字符運串更快，例如比較運算，可在單一運算中對數進行比較。而串運算設計幾個逐步字節的比較，如果穿更長，這種比較要更多。如果字符串列的數值數目有限，應該利用普通整型來獲得數值運算的優越性。

SQL優化

優化目標

減少 IO 次數,IO永遠是數據庫最容易瓶頸的地方，這是由數據庫的職責所決定的，大部分數據庫操作中超過90%的時間都是 IO 操作所占用的，減少 IO 次數是 SQL 優化中需要第一優先考慮，當然，也是收效最明顯的優化手段。?
降低 CPU 計算,除了 IO 瓶頸之外，SQL優化中需要考慮的就是 CPU 運算量的優化了。order by, group by,distinct … 都是消耗 CPU 的大戶(這些操作基本上都是 CPU 處理內存中的數據比較運算)。當我們的 IO 優化做到一定階段之后，降低 CPU 計算也就成為了我們 SQL 優化的重要目標

常見誤區

count(1)和count(primary_key) 優于 count(*)

很多人為了統計記錄條數，就使用 count(1) 和 count(primary_key) 而不是 count( ) ，他們認為這樣性能更好，其實這是一個誤區。對于有些場景，這樣做可能性能會更差，應為數據庫對 count( ) 計數操作做了一些特別的優化。

count(column) 和 count(*) 是一樣的

這個誤區甚至在很多的資深工程師或者是 DBA 中都普遍存在，很多人都會認為這是理所當然的。實際上，count(column) 和 count( ) 是一個完全不一樣的操作，所代表的意義也完全不一樣。?
count(column) 是表示結果集中有多少個column字段不為空的記錄?
count( ) 是表示整個結果集有多少條記錄

select a,b from … 比 select a,b,c from … 可以讓數據庫訪問更少的數據量

這個誤區主要存在于大量的開發人員中，主要原因是對數據庫的存儲原理不是太了解。?
實際上，大多數關系型數據庫都是按照行(row)的方式存儲，而數據存取操作都是以一個固定大小的IO單元(被稱作 block 或者 page)為單位，一般為4KB，8KB… 大多數時候，每個IO單元中存儲了多行，每行都是存儲了該行的所有字段(lob等特殊類型字段除外)。?
所以，我們是取一個字段還是多個字段，實際上數據庫在表中需要訪問的數據量其實是一樣的。?
當然，也有例外情況，那就是我們的這個查詢在索引中就可以完成，也就是說當只取 a,b兩個字段的時候，不需要回表，而c這個字段不在使用的索引中，需要回表取得其數據。在這樣的情況下，二者的IO量會有較大差異。

order by 一定需要排序操作

我們知道索引數據實際上是有序的，如果我們的需要的數據和某個索引的順序一致，而且我們的查詢又通過這個索引來執行，那么數據庫一般會省略排序操作，而直接將數據返回，因為數據庫知道數據已經滿足我們的排序需求了。?
實際上，利用索引來優化有排序需求的 SQL，是一個非常重要的優化手段?
延伸閱讀: http://blog.csdn.net/zzxian/article/details/7927810

基本原則

盡量少使用外鍵關聯

數據庫的諸多設計，帳號，權限，約束，觸發器，都是為 C/S 結構設計的，是以 C 端不可信做為假設前提的。B/S 模式安全邊界前移到 web 服務層，應用與數據庫之間是可信的，應用自行完成這些功能更加靈活。

盡量少 join

MySQL 的優勢在于簡單，但這在某些方面其實也是其劣勢。MySQL 優化器效率高，但是由于其統計信息的量有限，優化器工作過程出現偏差的可能性也就更多。對于復雜的多表 Join，一方面由于其優化器受限，再者在 Join 這方面所下的功夫還不夠，所以性能表現離 Oracle 等關系型數據庫前輩還是有一定距離。但如果是簡單的單表查詢，這一差距就會極小甚至在有些場景下要優于這些數據庫前輩。

盡量避免 select *

很多人看到這一點后覺得比較難理解，上面不是在誤區中剛剛說 select 子句中字段的多少并不會影響到讀取的數據嗎? 是的，大多數時候并不會影響到 IO 量，但是當我們還存在 order by 操作的時候，select 子句中的字段多少會在很大程度上影響到我們的排序效率。此外，上面誤區中不是也說了，只是大多數時候是不會影響到 IO 量，當我們的查詢結果僅僅只需要在索引中就能找到的時候，還是會極大減少 IO 量的。

盡量用 join 代替子查詢

雖然 Join 性能并不佳，但是和 MySQL 的子查詢比起來還是有非常大的性能優勢。MySQL 的子查詢執行計劃一直存在較大的問題，雖然這個問題已經存在多年，但是到目前已經發布的所有穩定版本中都普遍存在，一直沒有太大改善。雖然官方也在很早就承認這一問題，并且承諾盡快解決，但是至少到目前為止我們還沒有看到哪一個版本較好的解決了這一問題。?
MYSQL 5.6已經優化子查詢? http://www.linuxidc.com/Linux/2012-08/67606.htm

盡量少 or

當 where 子句中存在多個條件以“或”并存的時候，MySQL 的優化器并沒有很好的解決其執行計劃優化問題，再加上 MySQL 特有的 SQL 與 Storage 分層架構方式，造成了其性能比較低下，很多時候使用 union all 或者是union(必要的時候)的方式來代替“or”會得到更好的效果。

盡量用 union all 代替 union

union 和 union all 的差異主要是前者需要將兩個(或者多個)結果集合并后再進行唯一性過濾操作，這就會涉及到排序，增加大量的 CPU 運算，加大資源消耗及延遲。所以當我們可以確認不可能出現重復結果集或者不在乎重復結果集的時候，盡量使用 union all 而不是 union。?
擴展閱讀:? http://jingyan.baidu.com/article/2d5afd69e8dfd285a3e28e66.html

盡量早過濾

這一優化策略其實最常見于索引的優化設計中(將過濾性更好的字段放得更靠前)。?
在 SQL 編寫中同樣可以使用這一原則來優化一些 Join 的 SQL。比如我們在多個表進行分頁數據查詢的時候，我們最好是能夠在一個表上先過濾好數據分好頁，然后再用分好頁的結果集與另外的表 Join，這樣可以盡可能多的減少不必要的 IO 操作，大大節省 IO 操作所消耗的時間。

避免類型轉換

這里所說的“類型轉換”是指 where 子句中出現 column 字段的類型和傳入的參數類型不一致的時候發生的類型轉換：?
人為在column_name 上通過轉換函數進行轉換直接導致 MySQL(實際上其他數據庫也會有同樣的問題)無法使用索引，如果非要轉換，應該在傳入的參數上進行轉換由數據庫自己進行轉換?
如果我們傳入的數據類型和字段類型不一致，同時我們又沒有做任何類型轉換處理，MySQL 可能會自己對我們的數據進行類型轉換操作，也可能不進行處理而交由存儲引擎去處理，這樣一來，就會出現索引無法使用的情況而造成執行計劃問題。

優先優化高并發的 SQL，而不是執行頻率低某些“大”SQL

對于破壞性來說，高并發的 SQL 總是會比低頻率的來得大，因為高并發的 SQL 一旦出現問題，甚至不會給我們任何喘息的機會就會將系統壓跨。而對于一些雖然需要消耗大量 IO 而且響應很慢的 SQL，由于頻率低，即使遇到，最多就是讓整個系統響應慢一點，但至少可能撐一會兒，讓我們有緩沖的機會。

從全局出發優化，而不是片面調整

SQL 優化不能是單獨針對某一個進行，而應充分考慮系統中所有的 SQL，尤其是在通過調整索引優化 SQL 的執行計劃的時候，千萬不能顧此失彼，因小失大。

盡可能對每一條運行在數據庫中的SQL進行 explain

優化 SQL，需要做到心中有數，知道 SQL 的執行計劃才能判斷是否有優化余地，才能判斷是否存在執行計劃問題。在對數據庫中運行的 SQL 進行了一段時間的優化之后，很明顯的問題 SQL 可能已經很少了，大多都需要去發掘，這時候就需要進行大量的 explain 操作收集執行計劃，并判斷是否需要進行優化。

專題

MYSQL的查詢、子查詢及連接查詢

http://www.cnblogs.com/rollenholt/archive/2012/05/15/2502551.html

MYSQL大數據量的初步優化方案:

mysql只做簡單的事情，千萬級的表，不論如何優化，同樣的SQL都沒有十萬級的表訪問快。?
如果設計大表，要問自己幾個問題：?
1.數據庫分庫? 摘除數據表之間的關聯 ?
1.水平分表/mysql分區?
1.垂直拆分

MYSQL 優化指南