?

數(shù)據(jù)庫設(shè)計(jì)原則

標(biāo)準(zhǔn)化和規(guī)范化 數(shù)據(jù)庫設(shè)計(jì)范式(3NF)

第一范式

數(shù)據(jù)屬性唯一標(biāo)示

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

第二范式

行信息唯一標(biāo)示?
第二范式（2NF）是在第一范式（1NF）的基礎(chǔ)上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。第二范式（2NF）要求數(shù)據(jù)庫表中的每個(gè)實(shí)例或行必須可以被唯一地區(qū)分。為實(shí)現(xiàn)區(qū)分通常需要為表加上一個(gè)列，以存儲(chǔ)各個(gè)實(shí)例的唯一標(biāo)識(shí)。?
員工信息表中加上了員工編號(hào)（emp_id）列，因?yàn)槊總€(gè)員工的員工編號(hào)是唯一的，因此每個(gè)員工可以被唯一區(qū)分。這個(gè)唯一屬性列被稱為主關(guān)鍵字或主鍵、主碼。?
第二范式（2NF）要求實(shí)體的屬性完全依賴于主關(guān)鍵字。所謂完全依賴是指不能存在僅依賴主關(guān)鍵字一部分的屬性，如果存在，那么這個(gè)屬性和主關(guān)鍵字的這一部分應(yīng)該分離出來形成一個(gè)新的實(shí)體，新實(shí)體與原實(shí)體之間是一對(duì)多的關(guān)系。為實(shí)現(xiàn)區(qū)分通常需要為表加上一個(gè)列，以存儲(chǔ)各個(gè)實(shí)例的唯一標(biāo)識(shí)。簡(jiǎn)而言之，第二范式就是非主屬性非部分依賴于主關(guān)鍵字。

第三范式

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

反范式設(shè)計(jì)

數(shù)據(jù)庫設(shè)計(jì)要嚴(yán)格遵守范式，這樣設(shè)計(jì)出來的數(shù)據(jù)庫，雖然思路很清晰，結(jié)構(gòu)也很合理，但是，有的時(shí)候，卻要在一定程度上打破范式設(shè)計(jì)。?
這里其實(shí)并不矛盾，因?yàn)榉妒皆礁撸O(shè)計(jì)出來的表可能越多，關(guān)系可能越復(fù)雜，但是性能卻不一定會(huì)很好，因?yàn)楸硪欢啵驮黾恿岁P(guān)聯(lián)性。這一點(diǎn)表現(xiàn)得很明顯。?
最明顯的打破范式的設(shè)計(jì)方法就是冗余法，以空間換取時(shí)間的做法，把數(shù)據(jù)冗余在多個(gè)表中，當(dāng)查詢時(shí)可以減少或者是避免表之間的關(guān)聯(lián)。

數(shù)據(jù)驅(qū)動(dòng)

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

考慮各種變化和記錄數(shù)據(jù)的基本信息

在設(shè)計(jì)數(shù)據(jù)庫的時(shí)候考慮到哪些數(shù)據(jù)字段將來可能會(huì)發(fā)生變更。?
舉例 數(shù)據(jù)的添加時(shí)間,更新時(shí)間 用戶的 注冊(cè)ip 和 登錄ip 等

數(shù)據(jù)庫建立

數(shù)據(jù)庫表名

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

數(shù)據(jù)庫表字段名

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

字段索引名稱

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

字段結(jié)構(gòu)

進(jìn)行表結(jié)構(gòu)設(shè)計(jì)時(shí)，應(yīng)當(dāng)做到恰到好處，反復(fù)推敲，從而實(shí)現(xiàn)最優(yōu)的數(shù)據(jù)存儲(chǔ)體系? 短小 ? 精悍

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

SQL優(yōu)化

優(yōu)化目標(biāo)

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

常見誤區(qū)

count(1)和count(primary_key) 優(yōu)于 count(*)

很多人為了統(tǒng)計(jì)記錄條數(shù)，就使用 count(1) 和 count(primary_key) 而不是 count( ) ，他們認(rèn)為這樣性能更好，其實(shí)這是一個(gè)誤區(qū)。對(duì)于有些場(chǎng)景，這樣做可能性能會(huì)更差，應(yīng)為數(shù)據(jù)庫對(duì) count( ) 計(jì)數(shù)操作做了一些特別的優(yōu)化。

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

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

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

這個(gè)誤區(qū)主要存在于大量的開發(fā)人員中，主要原因是對(duì)數(shù)據(jù)庫的存儲(chǔ)原理不是太了解。?
實(shí)際上，大多數(shù)關(guān)系型數(shù)據(jù)庫都是按照行(row)的方式存儲(chǔ)，而數(shù)據(jù)存取操作都是以一個(gè)固定大小的IO單元(被稱作 block 或者 page)為單位，一般為4KB，8KB… 大多數(shù)時(shí)候，每個(gè)IO單元中存儲(chǔ)了多行，每行都是存儲(chǔ)了該行的所有字段(lob等特殊類型字段除外)。?
所以，我們是取一個(gè)字段還是多個(gè)字段，實(shí)際上數(shù)據(jù)庫在表中需要訪問的數(shù)據(jù)量其實(shí)是一樣的。?
當(dāng)然，也有例外情況，那就是我們的這個(gè)查詢?cè)谒饕芯涂梢酝瓿桑簿褪钦f當(dāng)只取 a,b兩個(gè)字段的時(shí)候，不需要回表，而c這個(gè)字段不在使用的索引中，需要回表取得其數(shù)據(jù)。在這樣的情況下，二者的IO量會(huì)有較大差異。

order by 一定需要排序操作

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

基本原則

盡量少使用外鍵關(guān)聯(lián)

數(shù)據(jù)庫的諸多設(shè)計(jì)，帳號(hào)，權(quán)限，約束，觸發(fā)器，都是為 C/S 結(jié)構(gòu)設(shè)計(jì)的，是以 C 端不可信做為假設(shè)前提的。B/S 模式安全邊界前移到 web 服務(wù)層，應(yīng)用與數(shù)據(jù)庫之間是可信的，應(yīng)用自行完成這些功能更加靈活。

盡量少 join

MySQL 的優(yōu)勢(shì)在于簡(jiǎn)單，但這在某些方面其實(shí)也是其劣勢(shì)。MySQL 優(yōu)化器效率高，但是由于其統(tǒng)計(jì)信息的量有限，優(yōu)化器工作過程出現(xiàn)偏差的可能性也就更多。對(duì)于復(fù)雜的多表 Join，一方面由于其優(yōu)化器受限，再者在 Join 這方面所下的功夫還不夠，所以性能表現(xiàn)離 Oracle 等關(guān)系型數(shù)據(jù)庫前輩還是有一定距離。但如果是簡(jiǎn)單的單表查詢，這一差距就會(huì)極小甚至在有些場(chǎng)景下要優(yōu)于這些數(shù)據(jù)庫前輩。

盡量避免 select *

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

盡量用 join 代替子查詢

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

盡量少 or

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

盡量用 union all 代替 union

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

盡量早過濾

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

避免類型轉(zhuǎn)換

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

優(yōu)先優(yōu)化高并發(fā)的 SQL，而不是執(zhí)行頻率低某些“大”SQL

對(duì)于破壞性來說，高并發(fā)的 SQL 總是會(huì)比低頻率的來得大，因?yàn)楦卟l(fā)的 SQL 一旦出現(xiàn)問題，甚至不會(huì)給我們?nèi)魏未⒌臋C(jī)會(huì)就會(huì)將系統(tǒng)壓跨。而對(duì)于一些雖然需要消耗大量 IO 而且響應(yīng)很慢的 SQL，由于頻率低，即使遇到，最多就是讓整個(gè)系統(tǒng)響應(yīng)慢一點(diǎn)，但至少可能撐一會(huì)兒，讓我們有緩沖的機(jī)會(huì)。

從全局出發(fā)優(yōu)化，而不是片面調(diào)整

SQL 優(yōu)化不能是單獨(dú)針對(duì)某一個(gè)進(jìn)行，而應(yīng)充分考慮系統(tǒng)中所有的 SQL，尤其是在通過調(diào)整索引優(yōu)化 SQL 的執(zhí)行計(jì)劃的時(shí)候，千萬不能顧此失彼，因小失大。

盡可能對(duì)每一條運(yùn)行在數(shù)據(jù)庫中的SQL進(jìn)行 explain

優(yōu)化 SQL，需要做到心中有數(shù)，知道 SQL 的執(zhí)行計(jì)劃才能判斷是否有優(yōu)化余地，才能判斷是否存在執(zhí)行計(jì)劃問題。在對(duì)數(shù)據(jù)庫中運(yùn)行的 SQL 進(jìn)行了一段時(shí)間的優(yōu)化之后，很明顯的問題 SQL 可能已經(jīng)很少了，大多都需要去發(fā)掘，這時(shí)候就需要進(jìn)行大量的 explain 操作收集執(zhí)行計(jì)劃，并判斷是否需要進(jìn)行優(yōu)化。

專題

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

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

MYSQL大數(shù)據(jù)量的初步優(yōu)化方案:

mysql只做簡(jiǎn)單的事情，千萬級(jí)的表，不論如何優(yōu)化，同樣的SQL都沒有十萬級(jí)的表訪問快。?
如果設(shè)計(jì)大表，要問自己幾個(gè)問題：?
1.數(shù)據(jù)庫分庫? 摘除數(shù)據(jù)表之間的關(guān)聯(lián) ?
1.水平分表/mysql分區(qū)?
1.垂直拆分

MYSQL 優(yōu)化指南