圖片來自網(wǎng)絡

文/Bruce.Liu1

1.建模簡介

范式：英文名稱是 Normal Form曹锨，它是英國人 E.F.Codd（埃德加·弗蘭克·科德）在上個世紀70年代提出關系數(shù)據(jù)庫模型后總結出來的舵盈，范式是關系數(shù)據(jù)庫理論的基礎毡咏，也是我們在設計數(shù)據(jù)庫結構過程中所要遵循的規(guī)則和指導方法驮宴。目前有跡可尋的共有8種范式，依次是：1NF呕缭，2NF堵泽，3NF，BCNF恢总，4NF迎罗，5NF，DKNF片仿，6NF纹安。通常所用到的只是前三個范式，即：第一范式（1NF），第二范式（2NF）厢岂，第三范式（3NF）

圖片來自網(wǎng)絡

埃德加·弗蘭克·科德（Edgar Frank Codd光督，1923－2003）是密執(zhí)安大學哲學博士，IBM公司研究員咪笑，被譽為“關系數(shù)據(jù)庫之父”可帽，并因為在數(shù)據(jù)庫管理系統(tǒng)的理論和實踐方面的杰出貢獻于1981年獲圖靈獎。1970年窗怒，科德發(fā)表題為“大型共享數(shù)據(jù)庫的關系模型”的論文，文中首次提出了數(shù)據(jù)庫的關系模型蓄拣。由于關系模型簡單明了扬虚、具有堅實的數(shù)學理論基礎，所以一經(jīng)推出就受到了學術界和產(chǎn)業(yè)界的高度重視和廣泛響應球恤，并很快成為數(shù)據(jù)庫市場的主流辜昵。20世紀80年代以來，計算機廠商推出的數(shù)據(jù)庫管理系統(tǒng)幾乎都支持關系模型咽斧，數(shù)據(jù)庫領域當前的研究工作大都以關系模型為基礎堪置。

數(shù)據(jù)庫建模：
在設計數(shù)據(jù)庫時，對現(xiàn)實世界進行分析张惹、抽象舀锨、并從中找出內在聯(lián)系，進而確定數(shù)據(jù)庫的結構宛逗，這一過程就稱為數(shù)據(jù)庫建模坎匿。它主要包括兩部分內容：確定最基本的數(shù)據(jù)結構；對約束建模雷激。

• 1.概念模型的表示方法
  E-R圖主要是由實體替蔬、屬性和聯(lián)系三個要素構成的。在E-R圖中屎暇，使用了下面四種基本的圖形符號承桥。

• 2.確定系統(tǒng)實體、屬性及聯(lián)系
  系統(tǒng)分析階段建立數(shù)據(jù)字典和數(shù)據(jù)流程圖->建立概念模型->邏輯模型->物理模型根悼；利用系統(tǒng)分析階段建立的數(shù)據(jù)字典凶异，并對照數(shù)據(jù)流程圖對系統(tǒng)中的各個數(shù)據(jù)項進行分類、組織番挺，確定系統(tǒng)中的實體唠帝、實體的屬性、標識實體的碼以及實體之間聯(lián)系的類型玄柏。
  在數(shù)據(jù)字典中“數(shù)據(jù)項”是基本數(shù)據(jù)單位襟衰，一般可以作為實體的屬性》嗾“數(shù)據(jù)結構”瀑晒、“數(shù)據(jù)存儲”和“數(shù)據(jù)流”條目都可以作為實體绍坝，因為它們總是包含了若干的數(shù)據(jù)項。作為屬性必須是不可再分的數(shù)據(jù)項苔悦，也就是說在屬性中不能包含其他的屬性轩褐。

• 3.確定局部（分）E-R圖
  根據(jù)上面的分析，可以畫出部分實體－聯(lián)系圖玖详。
  在這些實體中有下畫線的屬性可以作為實體的碼把介，這幾個實體之間存在著1：1、l：n和m：n幾種聯(lián)系蟋座。

• 4.集成完整（總）E-R圖
  各個局部（分）E-R圖畫好以后拗踢，應當將它們合并起來集成為完整（總）E-R圖。在集成時應當注意如下幾點：

  • 消除不必要的冗余實體向臀、屬性和聯(lián)系巢墅。
  • 解決各分E-R圖之間的沖突。
  • 根據(jù)情況修改或重構E-R圖券膀。

2.三范式設計

什么是范式? 范式是“符合某一種級別的關系模式的集合君纫，表示一個關系內部各屬性之間的聯(lián)系的合理化程度”。很晦澀吧芹彬？實際上你可以把它粗略地理解為一張數(shù)據(jù)表的表結構所符合的某種設計標準的級別蓄髓。就像家里裝修買建材，最環(huán)保的是E0級雀监，其次是E1級双吆，還有E2級等等。數(shù)據(jù)庫范式也分為1NF会前，2NF好乐，3NF，BCNF瓦宜，4NF蔚万，5NF。一般在我們設計關系型數(shù)據(jù)庫的時候临庇，最多考慮到BCNF就夠反璃。符合高一級范式的設計，必定符合低一級范式假夺，例如符合2NF的關系模式靶累，必定符合1NF档痪。

1.1.第一范式(1NF)

• 第一范式（1NF）：屬性原子性約束
  屬性是原子性的到腥，即不可在拆分

考慮這樣一張表：【聯(lián)系人】（姓名雁比，性別，電話） 。
如果在實際場景中裁眯，一個聯(lián)系人有家庭電話和公司電話鹉梨，那么這種表結構設計就沒有達到 1NF。要符合 1NF 我們只需把列（電話）拆分穿稳，即：【聯(lián)系人】（姓名存皂，性別，家庭電話逢艘，公司電話）

1.2.第二范式(1NF)

• 第二范式（2NF）：主鍵約束
  一是表必須有一個主鍵旦袋；二是沒有包含在主鍵中的列必須完全依賴于主鍵，而不能只依賴于主鍵的一部分它改。

考慮一個訂單明細表：【OrderDetail】（OrderID猜憎，ProductID，UnitPrice搔课，Discount，Quantity截亦，ProductName）爬泥。
因為我們知道在一個訂單中可以訂購多種產(chǎn)品，所以單單一個 OrderID 是不足以成為主鍵的崩瓤，主鍵應該是（OrderID袍啡，ProductID）。顯而易見 Discount（折扣）却桶，Quantity（數(shù)量）完全依賴（取決）于主鍵（OderID境输，ProductID），而 UnitPrice（單價）颖系，ProductName 只依賴于 ProductID嗅剖。所以 OrderDetail 表不符合 2NF。
可以把【OrderDetail】表拆分為【OrderDetail】（OrderID嘁扼，ProductID信粮，Discount，Quantity）和【Product】（ProductID趁啸，UnitPrice强缘，ProductName）來消除原訂單表中UnitPrice，ProductName多次重復的情況不傅。

1.3.第三范式(1NF)

• 第三范式（3NF）：冗余性約束
  任何字段不能由其他字段派生出來旅掂。要求字段沒有冗余。

考慮一個訂單表【Order】（OrderID访娶，OrderDate商虐，CustomerID，CustomerName，CustomerAddr称龙，CustomerCity）主鍵是（OrderID）留拾。
其中 OrderDate，CustomerID鲫尊，CustomerName痴柔，CustomerAddr，CustomerCity 等非主鍵列都完全依賴于主鍵（OrderID）疫向，所以符合 2NF咳蔚。不過問題是 CustomerName，CustomerAddr搔驼，CustomerCity 直接依賴的是 CustomerID（非主鍵列）谈火，而不是直接依賴于主鍵，它是通過傳遞才依賴于主鍵舌涨，多個訂單中有同一個用戶下單糯耍，就會產(chǎn)生數(shù)據(jù)冗余； 3NF中說的傳遞依賴囊嘉，就出現(xiàn)了温技，所以不符合 3NF。
通過拆分【Order】為【Order】（OrderID扭粱，OrderDate舵鳞，CustomerID）和【Customer】（CustomerID，CustomerName琢蛤，CustomerAddr蜓堕，CustomerCity）從而達到 3NF。

總結：通常來說博其，在數(shù)據(jù)庫建模設計中范式設計是指導思想套才，實際實踐中往往還要結合業(yè)務場景適當?shù)倪M行調整。

3.反范式設計

denormalization?字面上就是做范式的反義詞贺奠，事實上也是霜旧。遵循范式總體上來說是為了保證數(shù)據(jù)的integrity和減少冗余，但是儡率，從直覺上我們就可以知道挂据，一個完全按照范式設計的冗余極低的數(shù)據(jù)庫，很可能在性能上會輸給冗余相對多一些的數(shù)據(jù)庫（比如說3NF的數(shù)據(jù)庫儿普，表多崎逃，關系復雜，數(shù)據(jù)庫的IO次數(shù)很多眉孩，性能會收到影響）个绍。增加冗余而提高性能勒葱，這便是denormalization的意義。至于說很多可能的異常巴柿，規(guī)避或者減輕的的手段有很多凛虽，是否遵循范式并不是唯一因素，denormalization也并不會導致出現(xiàn)異常广恢。

3.1.三范式優(yōu)缺點

三范式的優(yōu)點：

• 減少冗余
• 減少異常（delete凯旋，update，insert）
• 讓數(shù)據(jù)組織的更加和諧（愚以為僅僅對于系統(tǒng)來說钉迷，對于人可完全不是這么回事）

三范式的缺點：

范式等級與復雜度是遞進的至非；通過范式的不斷升級，我們會發(fā)現(xiàn)應用的范式等級越高糠聪，則表越多荒椭。表多會帶來很多問題：

• 查詢時要連接多個表，增加了查詢的復雜度
• 查詢時需要連接多個表舰蟆，降低了數(shù)據(jù)庫查詢性能
• 而現(xiàn)在的情況趣惠，磁盤空間成本基本可以忽略不計，所以數(shù)據(jù)冗余所造成的問題也并不是應用數(shù)據(jù)庫范式的理由身害。

3.2.反范式場景

在單庫中基本上都可以以三范式作為數(shù)據(jù)庫關系設計的核心思想信卡，but在數(shù)據(jù)大爆炸的互聯(lián)網(wǎng)浪潮中，三范式就顯得力不從心题造。

• 數(shù)據(jù)庫水平拆分時，要通過數(shù)據(jù)冗余的方式減少join操作猾瘸。
• 數(shù)據(jù)庫垂直拆分時界赔，同樣三范式也滿足不了需求。
• 應用程序解耦時牵触，需要數(shù)據(jù)庫層進行分離淮悼。
• 分布式數(shù)據(jù)庫場景中，如何匯總數(shù)據(jù)揽思。

4.開發(fā)規(guī)范

4.1.表設計

1. 庫名袜腥、表名、字段名使用小寫字母钉汗，”_”分割羹令，不超過 12 個字符，使用名詞且見名知意
2. 默認使用 innodb 存儲引擎损痰，使用其他引擎必須注明緣由【FAQ】
3. 存儲精確浮點數(shù)使用 DECIMAL,替代 FLOAT 和 DOUBLE
4. 使用 int unsigned 存儲 IP 地址【FAQ】
5. 根據(jù)字段長度選擇合適的字段類型福侈，如數(shù)字類型有 tinyint，smallint卢未，mediuint肪凛，int堰汉，bigint 五種類型，分別占用 1byte伟墙，2byte翘鸭，3byte，4byte戳葵，8byte就乓。需要特別注意，int（10）和 int（2）無區(qū)別譬淳，應該采用 tinyint（2）替代 int（2）.
6. 盡量使用 tinyint 代替 enum 和 set 類型档址，減少后臺類型轉換
7. 盡量避免使用 text、blob 字段類型
8. Varchar(N)中邻梆，N 表示的是字符數(shù)不是字節(jié)數(shù)守伸，如 varchar（255），可以最大存儲255 個漢字浦妄。N 值應盡可能小尼摹，單表 varchar 字段最大長度為 65536 個字節(jié)，在排序和創(chuàng)建臨時表等內存操作時剂娄，會使用 N 值來申請內存蠢涝，而非存儲值的實際長度
9. 表字符集統(tǒng)一使用 utf8
10. 存儲年使用 year 類型，存儲日期使用 date 類型阅懦，存儲時間（精確到秒）使用timestamp 類型和二，而非 datetime 類型。因為 timestamp 使用 4 個字節(jié)耳胎，而 datetime 使用 8 個字節(jié)【FAQ】.
11. 字段全部定義為 NOT NULL【FAQ】
12. 將過大字段拆分到其他表中惯吕，不在數(shù)據(jù)庫中存儲圖片、文件等內容
13. 固定長度的表會更快【FAQ】
14. 盡量避免使用外鍵【FAQ】
15. 字段盡量全部定義為 NOT NULL：
    通常情況下最好指定列為not null ,除非真的需要存儲NULL值怕午。如果查詢中包含可為NULL的列废登，對于MySQL來說更難優(yōu)化，因為可為NULL的列使得索引郁惜、索引統(tǒng)計和值比較都更復雜堡距。可為NULL的列會使用更多的存儲空間兆蕉，在MySQL里也需要特殊處理羽戒。當可為NULL的列被索引時，每個索引記錄需要一個額外的字節(jié)字節(jié)虎韵，在MyISAM里還可能導致固定大小的索引（只有一個int列的索引）變成可變大小的索引半醉。
    通常把已經(jīng)上線的null改為not null帶來的性能提升比較小，所以調優(yōu)時沒有必要優(yōu)先糾正這種設計劝术。但是如果計劃在列上建索引缩多，就應該盡量把該列設計為not null
    當然也有例外呆奕，指的一提的是，InnoDB使用單獨的位(bit)來存儲null值衬吆，所以對于稀疏數(shù)據(jù)（很多值為null梁钾，只有少數(shù)行為非null)的情況下有很好的空間效率。
    int 類型可以定義為 not null default 0,
    varchar 類型可以定義為 notnull default ''
16. 適當?shù)牟鸱?冗余
    A.當我們的表中存在類似于 TEXT 或者是很大的 VARCHAR類型的大字段的時候逊抡，如果我們大部分訪問這張表的時候都不需要這個字段姆泻，我們就該義無反顧的將其拆分到另外的獨立表中，以減少常用數(shù)據(jù)所占用的存儲空間冒嫡。這樣做的一個明顯好處就是每個數(shù)據(jù)塊中可以存儲的數(shù)據(jù)條數(shù)可以大大增加拇勃，既減少物理 IO 次數(shù)，也能大大提高內存中的緩存命中率孝凌。
    B.被頻繁引用且只能通過Join 2張（或者更多）大表的方式才能得到的獨立小字段,這樣的場景由于每次Join僅僅只是為了取得某個小字段的值方咆，Join到的記錄又大，會造成大量不必要的IO蟀架，完全可以通過空間換取時間的方式來優(yōu)化瓣赂。不過，冗余的同時需要確保數(shù)據(jù)的一致性不會遭到破壞片拍，確保更新的同時冗余字段也被更新煌集。
17. 控制表的大小
    mysql在處理大表（char的表>500W行，或int表>1000W）時捌省，性能就開始明顯降低苫纤，所以要采用不同的方式控制單表容量

• 根據(jù)數(shù)據(jù)冷熱，對數(shù)據(jù)分級存儲纲缓，歷史歸檔
• 采用分庫/分表/分區(qū)表方面，橫向拆分控制單表容量
• 對于OLTP系統(tǒng)，控制單事務的資源消耗色徘，遇到大事務可以拆解，采用化整為零模式操禀，避免特例影響大眾
• 單庫不要超過500個表
• 單表字段數(shù)不要太多褂策，最多不要大于50個

4.2.索引設計

1. 索引命名規(guī)范：

• 索引名稱全部使用小寫；
• 非唯一索引按照“ix_字段名稱字段名稱[字段名稱]”進行命名
• 唯一索引按照“uq_字段名稱字段名稱[字段名稱]”進行命名

2. 唯一索引由 3 個以下字段組成并且字段都是整形時颓屑，使用唯一索引作為主鍵斤寂。沒有唯一索引
   或唯一索引不符合上述條件時，使用自增 id 作為主鍵揪惦。注意唯一索引不和主鍵重復【FAQ】
3. 單張表的索引數(shù)量控制在字段數(shù)的 20%以內遍搞，至多 5 個，索引數(shù)量過多會導致寫入性能 的顯著下降
4. 合理創(chuàng)建復合索引器腋。首先要避免冗余溪猿，ix_a_b_c 相當于同時創(chuàng)建了 ix-_a,ix_a_b,ix_a_b_c 三個索引钩杰；其次要避免索引過大，建議最大 4 列復合诊县，列數(shù)過多很難提升索引的區(qū)分度讲弄，反而降低索引的性能
5. 合理使用覆蓋索引
6. 對于長度大于 100 的 varchar 字段建立索引時，使用其他方法【FAQ】
7. 使用 EXPLAIN 判斷 SQL 語句是否合理使用索引依痊，盡量避免 extra 列出現(xiàn) FILE SORT,USINGTEMPORARY【FAQ】
8. 索引不只用于 select 查詢避除，update 和 delete 語句也需要根據(jù) where 條件合理設計索引
9. where 條件中的非等值條件（IN,BETWEEN,<,<=,>,>=）會導致后面的條件無法使用索引

4.3.SQL設計

1. 使用 prepared statement，可以提升性能并且避免 SQL 注入【FAQ】
2. 降低 SQL 的復雜度胸嘁，把 MySQL 盡量當做存儲使用：

• 避免在 SQL 語句中進行數(shù)學運算瓶摆、函數(shù)計算、邏輯判斷等操作
• 避免多表 join性宏，盡量拆分成多條查詢群井。如無法避免，在 join 表時應使用相同類型的列衔沼，并且在列上有索引【FAQ】
• 避免使用存儲過程蝌借、觸發(fā)器、函數(shù)等

3. Insert 語句使用 batch 提交（insert into table values （）指蚁，（）菩佑，（），……）,values 的個數(shù)不超過 500凝化；sql 語句中 in 包含的值不超過 500
4. Update稍坯，delete 語句避免使用 limit，如果確實需要分配處理大量數(shù)據(jù)搓劫，可以增加其他字段來限制每次處理的記錄數(shù)瞧哟，比如主鍵 id
5. 避免使用 select *【FAQ】
6. 避免使用 order by rand（），使用其他方式替換【FAQ】
7. 使用合理的分頁方式以提高分頁的效率
8. 統(tǒng)計表中記錄數(shù)時使用 count(*),而不是 count（pk）或 count（1）
9. 數(shù)據(jù)庫默認開啟查詢緩存枪向，合理利用查詢緩存提升 sql 效率【FAQ】
10. 當只需要 1 行數(shù)據(jù)時使用 limit 1【FAQ】
11. 拆分大的 delete 和 insert【FAQ】
12. Where 條件中使用合適的類型勤揩，數(shù)值不加引號，字符加引號秘蛔，避免 MySQL 進行隱式類型轉換陨亡，從而無法使用索引【FAQ】
13. 避免使用 or，對同一個字段將 or 改為 in深员，對不同字段將 or 改為 union【FAQ】
14. 盡量避免負向查詢负蠕，如 NOT、!=倦畅、<>遮糖、!<、!>叠赐、NOT EXISTS欲账、NOT IN屡江、NOT LIKE 等
15. 針對同一張表的 alter table 操作，應該用逗號分隔敬惦，一次完成
16. 注意 MySQL 中 insert ignore into盼理；insert on duplicate key update；replace into 的區(qū)別俄删，在應用中合理使用【FAQ】

4.4.分表設計

1. 單表數(shù)據(jù)量控制在 300w 以下宏怔，如果字段全部為 int 類型，控制在 500w 以下

2. 避免使用 MySQL 自帶的分區(qū)表功能畴椰，單表數(shù)據(jù)量時通過程序來分表臊诊，使用 hash 分表時，
   表名后綴使用 16 進制表示斜脂，如 user_ff;使用日期分表時抓艳，表明后綴使用日期，如user_20130707 或 user_201307

4.5.其他

1. 盡量減小事務：事務使用原則是即開即用帚戳，用完即關 玷或；事務無關操作放到事務外面, 減少鎖資源的占用；在不破壞一致性前提下片任，使用多個短事務代替長事務
2. 可重復讀（repeatable read）是 MySQL 的默認事務隔離級別偏友，解決了臟讀（dirty read）和幻讀（phantom read）的問題，原則上禁止修改事務的隔離級別对供∥凰【FAQ】
3. 盡量使用短連接，完成查詢后要主動釋放連接产场，避免 MySQL 中出現(xiàn)大量 sleep 線程
4. 不管使用連接池還是直連 MySQL鹅髓，執(zhí)行查詢前都需要考慮獲取的 MySQL 連接可能已經(jīng)斷開，如斷開可以重連京景。執(zhí)行查詢后都要檢查查詢是否成功窿冯，如不成功，考慮是否需要重
   新執(zhí)行确徙。

4.6.注解

• FAQ1.2

從安全性和性能兩個角度都建議使用 InnoDB 引擎醒串。首先數(shù)據(jù)量較大的時候，系統(tǒng)崩潰后如何快速恢復是一個重要問題米愿。相對而言，MyISAM 崩潰后發(fā)生損壞的概率比 InnoDB 高
得多鼻吮，而且恢復速度慢育苟，可能丟失數(shù)據(jù)。因此椎木，即使不需要支持事務违柏，也推薦使用 innoDB.另外為改善 InnoDB 的性能博烂，Oracle 投入了大量資源，對 Innodb 內部做了大量優(yōu)化漱竖，使得
其性能在絕大部分場景遠高于 MyISAM禽篱。

• FAQ1.4

使用 INTUNSIGNED 而不是 char(15)來存儲 ipv4 地址，通過 MySQL 函數(shù) inet_ntoa 和
inet_aton 來進行轉化馍惹。Ipv6 地址目前沒有轉化函數(shù)躺率，需要使用 DECIMAL 或者兩個 bigINT 來存儲。
例如：
SELECT INET_ATON('209.207.224.40');
3520061480
SELECT INET_NTOA(3520061480);
209.207.224.40
FAQ1.10
INT[M]万矾，M值代表什么含義悼吱？
注意數(shù)值類型括號后面的數(shù)字只是表示寬度而跟存儲范圍沒有關系，比如INT(3)默認顯示3位良狈，空格補齊后添，超出時正常顯示，python薪丁、java客戶端等不具備這個功能遇西。
為什么建議使用TIMESTAMP來存儲時間而不是DATETIME？
DATETIME和TIMESTAMP都是精確到秒严嗜，優(yōu)先選擇TIMESTAMP粱檀，因為TIMESTAMP只有4個字節(jié)，而DATETIME8個字節(jié)阻问。同時TIMESTAMP具有自動賦值以及自動更新的特性梧税。
如何使用TIMESTAMP的自動賦值屬性？
a) 將當前時間作為ts的默認值：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP称近。
b) 當行更新時第队，更新ts的值：ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP。
c) 可以將1和2結合起來：ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

• FAQ1.11

除非有很特別的原因去使用 NULL 值刨秆，最好總是讓字段保持 NOT NULL凳谦。
首先，確定“Empty”和“NULL”有多大的區(qū)別（如果是 INT衡未，那就是 0 和 NULL）尸执？如果覺得它們沒有什么區(qū)別，那么就不要使用 NULL缓醋。（在 Oracle 里如失，NULL 和 Empty 的字符串
是一樣的！)送粱。NULL 的存儲需要額外的空間褪贵，并且，在進行比較的時候，會增加程序的復雜性脆丁。 當然世舰，這里并不是說就不能使用 NULL 了，現(xiàn)實情況是很復雜的槽卫，依然會有些情況
下跟压，你需要使用 NULL 值。
下面摘自 MySQL 官方文檔：
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

• FAQ1.13

如果表中的所有字段都是“固定長度”的歼培，整個表會被認為是 “static” 或 “fixed-length”震蒋。 例如，表中沒有如下類型的字段： VARCHAR丐怯，TEXT喷好，BLOB。只要包括了其中一
個這些字段读跷，那么這個表就不是“固定長度靜態(tài)表”了梗搅，這樣，MySQL 引擎會用另一種方法來處理效览。固定長度的表會提高性能无切，因為 MySQL 搜尋得會更快一些，因為這些固定的長度是很容易
計算下一個數(shù)據(jù)的偏移量的丐枉，所以讀取的自然也會很快哆键。而如果字段不是定長的，那么瘦锹，每一次要找下一條的話籍嘹，需要程序找到主鍵。并且弯院，固定長度的表也更容易被緩存和重建辱士。不過，唯一的副作用是听绳，固定長度的字段會浪費一些空間颂碘，因為定長的字段無論你用不用，他都是要分配那么多的空間椅挣。

• FAQ1.14

外鍵能夠簡化開發(fā)头岔，但是外鍵在高并發(fā)下帶來嚴重的鎖問題，對性能影響極大鼠证，可以考慮通過程序來保證約束條件峡竣。

• FAQ2.2

建議使用自增 id 作為主鍵，以提升寫入性能量九。以寫入 10 萬行數(shù)據(jù)為例适掰，對比如下：
主鍵字段
Int（10）auto_increment
Varchar（36）：
快的方式。
寫入時間
133 秒
147 秒
小結：InnoDB 是聚集索引，寫入速度嚴重依賴于寫入順序攻谁，按照主鍵的順序寫入是最

• FAQ2.6

可以考慮使用前綴索引或模擬 hash 索引下面的表增加一列url_crc32，然后對url_crc32建立索引弯予，減少索引字段的長度戚宦，提高效率。
CREATE TABLE url(
url VARCHAR(255) NOT NULL DEFAULT ‘’,
url_crc32 INT UNSIGNED NOT NULL DEFAULT 0,
……
index idx_url(url_crc32) )

• FAQ2.7

EXPLAIN 語句（在 MySQL 客戶端中執(zhí)行）可以獲得 MySQL 如何執(zhí)行 SELECT 語句的信息锈嫩。通過對 SELECT 語句執(zhí)行 EXPLAIN受楼，可以知曉 MySQL 執(zhí)行該 SELECT 語句時是否使用了索引、
全表掃描呼寸、臨時表艳汽、排序等信息。盡量避免 MySQL 進行全表掃描对雪、使用臨時表河狐、排序等。詳見官方文檔瑟捣。

• FAQ3.1

Prepared Statements 很像存儲過程馋艺，是一種運行在后臺的 SQL 語句集合，我們可以從使用prepared statements 獲得很多好處迈套，無論是性能問題還是安全問題捐祠。比如可以檢查一些你
綁定好的變量，這樣可以保護你的程序不會受到“SQL 注入式”攻擊桑李。當然踱蛀，你也可以手動地檢查你的這些變量，然而贵白，手動的檢查容易出問題率拒，而且很經(jīng)常會被程序員忘了。當
我們使用一些 framework 或是 ORM 的時候戒洼，這樣的問題會好一些俏橘。在性能方面，當一個相同的查詢被使用多次的時候圈浇，這會為你帶來可觀的性能優(yōu)勢寥掐。你可
以給這些 Prepared Statements 定義一些參數(shù)，而 MySQL 只會解析一次磷蜀。雖然最新版本的 MySQL 在傳輸 Prepared Statements 是使用二進制形勢召耘，所以這會使得網(wǎng)絡傳輸非常有效率。

• FAQ3.2.2

如果應用程序有很多 JOIN 查詢褐隆，應該確認兩個表中 Join 的字段是被建過索引的污它。這樣，MySQL 內部會啟動為你優(yōu)化 Join 的 SQL 語句的機制。而且衫贬，這些被用來 Join 的字段德澈，應該
是相同的類型的。例如：如果要把 DECIMAL 字段和一個 INT 字段 Join 在一起固惯，MySQL 就無法使用它們的索引梆造。對于那些 STRING 類型，還需要有相同的字符集才行葬毫。（兩個表的字符
集有可能不一樣）
// 在 state 中查找 company
$r = mysql_query("SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id");
// 兩個 state 字段應該是被建過索引的镇辉，而且應該是相當?shù)念愋停嗤淖址?/p>

• FAQ3.5

從數(shù)據(jù)庫里讀出越多的數(shù)據(jù)贴捡，那么查詢就會變得越慢忽肛。并且還會增加網(wǎng)絡傳輸?shù)呢撦d待德。減少使用覆蓋索引完成查詢的可能性攒至。所以刷晋，應該養(yǎng)成需要什么就取什么的好習慣占卧。
// 不推薦
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// 推薦
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
FAQ3.6
想打亂返回的數(shù)據(jù)行华嘹？隨機挑一個數(shù)據(jù)虫溜？方便用法的后面有非澄瘸模可怕的性能問題瘟忱。 如果真的想把返回的數(shù)據(jù)行打亂香缺，有 N 種方法可以達到這個目手销。這樣使用會讓你的數(shù)據(jù)庫
性能呈指數(shù)級的下降，具體問題是：MySQL 會不得不去執(zhí)行 RAND()函數(shù)（很耗 CPU 時間）图张， 而且這是為了每一行記錄去記行锋拖，然后再對其排序。就算是你用了 Limit 1 也無濟于事（因
為要排序祸轮，很耗 IO）
下面的示例是隨機挑一條記錄
// 千萬不要這樣做：
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// 這要會更好：
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

• FAQ3.9

某些查詢語句會讓 MySQL 不使用緩存兽埃。請看下面的示例：
// 不能夠使用緩存
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// 能夠使用查詢緩存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
上面兩條 SQL 語句的差別就是 CURDATE() ，MySQL 的查詢緩存對這個函數(shù)不起作用适袜。所以柄错，像 NOW() 和 RAND() 或是其它的諸如此類的 SQL 函數(shù)都不會開啟查詢緩存，因為這些函數(shù)
的返回是會不定的易變的苦酱。所以售貌，你所需要的就是用一個變量來代替 MySQL 的函數(shù)，從而使用緩存疫萤。

• FAQ3.10

當你查詢表的有些時候颂跨，你已經(jīng)知道結果只會有一條結果， 在這種情況下扯饶，加上 LIMIT 1 可以增加性能恒削。此時池颈，MySQL 數(shù)據(jù)庫引擎會在找到一條數(shù)據(jù)后停止搜索，而不是繼續(xù)往后查少下一條符合記錄的數(shù)據(jù)钓丰。
下面的示例躯砰，只是為了找一下是否有“中國”的用戶，很明顯携丁，后面的會比前面的更有效率弃揽。
// 沒有效率的：
$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
if (mysql_num_rows($r) > 0) {
// ...
}
// 有效率的：
$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
if (mysql_num_rows($r) > 0) {
// ...
}

• FAQ3.11

如果你需要在一個在線的網(wǎng)站上去執(zhí)行一個大的 DELETE 或 INSERT 查詢，你需要非常小心则北， 要避免你的操作讓你的整個網(wǎng)站停止相應。因為這兩個操作是會鎖表的痕慢，表一鎖住了尚揣，別的操作都進不來了。
Apache 會有很多的子進程或線程掖举。所以快骗，其工作起來相當有效率，而我們的服務器也不希望有太多的子進程塔次，線程和數(shù)據(jù)庫鏈接方篮，這是極大的占服務器資源的事情，尤其是內存励负。
如果你把你的表鎖上一段時間藕溅，比如 30 秒鐘，那么對于一個有很高訪問量的站點來說继榆，這 30 秒所積累的訪問進程/線程巾表，數(shù)據(jù)庫鏈接，打開的文件數(shù)略吨，可能不僅僅會讓你泊 WEB 服務 Crash集币，還可能會讓你的整臺服務器馬上掛了。
所以翠忠，如果你有一個大的處理鞠苟，你定你一定把其拆分，使用 LIMIT 條件是一個好的方法秽之。
下面是一個示例：
while (1) {
//每次只做 1000 條
mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
if (mysql_affected_rows() == 0) {
// 沒得可刪了当娱，退出！
break;
}
// 每次都要休息一會兒
usleep(50000);
}

• FAQ3.12

因為 MySQL 進行隱式類型轉化之后考榨，可能會將索引字段類型轉化成”=號”右邊值的類型趾访， 導致使用不到索引.

• FAQ3.13

OR 的時間復雜度為 0(n),in 的時間復雜度為 0(log n),也就是說用 in 效率更高.
例子：
使用 in 提升性能
Select * from opp WHERE phone=‘12347856' or phone=‘42242233';修改為
Select * from opp WHERE phone in ('12347856' , '42242233')
Merge index 效果不好，使用 union all 提升性能董虱。
例子:
Select * from opp WHERE phone='010-88886666' or cellPhone='13800138000'; ? 修改為
Select * from opp WHERE phone='010-88886666' union all Select * from opp WHERE
cellPhone='13800138000';

• FAQ3.16

1.insert ignore into
當插入數(shù)據(jù)時扼鞋，如出現(xiàn)錯誤時申鱼，如重復數(shù)據(jù)，將不返回錯誤云头，只以警告形式返回捐友。所以使 用 ignore 請確保語句本身沒有問題，否則也會被忽略掉溃槐。
2.on duplicate key update
當 primary key 或者 unique key 重復時匣砖，則執(zhí)行 update 語句, 注意如果多個行匹配，只更新 1 行昏滴，所以應避免對帶有多個 unique key 的表使用 ON DUPLICATE KEY 子句
3.replace into
如果存在 primary key or unique key 相同的記錄猴鲫，則先全部刪除掉，再插入新記錄谣殊。

• FAQ5.2

大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認隔離級別是提交讀（READ COMMITTED）拂共，但 MySQL 是可重復讀 （repeatable read）。提交讀這個事務隔離級別也叫做不可重復讀姻几。如果業(yè)務場景確實因為
大量的并發(fā)插入導致鎖問題嚴重宜狐，理論上可以通過降低隔離級別到提交讀，減少鎖爭用蛇捌， 但是降級一方面會產(chǎn)生幻讀的問題抚恒，另一方面必須工作在 MySQL 的 binlog 格式是 row 的情
況下，否則會造成主從同步中斷络拌。所以俭驮，從數(shù)據(jù)安全性角度出發(fā)，原則上禁止修改事務的隔離級別春贸，性能問題盡量通過優(yōu)化業(yè)務邏輯來解決表鳍。
FAQ5.3 MySQL中innodb表需要指定primary key?
.基于聚集索引的增、刪祥诽、改譬圣、查的效率相對是最高的；
.如果我們定義了主鍵(PRIMARY KEY)雄坪，那么InnoDB會選擇其作為聚集索引厘熟；
.如果沒有顯式定義主鍵，則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引维哈；
.如果也沒有這樣的唯一索引绳姨，則InnoDB會選擇內置6字節(jié)長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個ROWID不像ORACLE的ROWID那樣可引用阔挠，是隱含的)飘庄。
綜上總結，如果InnoDB表的數(shù)據(jù)寫入順序能和B+樹索引的葉子節(jié)點順序一致的話购撼，這時候存取效率是最高的跪削，也就是下面這幾種情況的存取效率最高：
a.使用自增列(INT/BIGINT類型)做主鍵谴仙，這時候寫入順序是自增的，和B+數(shù)葉子節(jié)點分裂順序一致碾盐；
b.該表不指定自增列做主鍵晃跺，同時也沒有可以被選為主鍵的唯一索引(上面的條件)，這時候InnoDB會選擇內置的ROWID作為主鍵毫玖，寫入順序和ROWID增長順序一致掀虎；
c.除此以外，如果一個InnoDB表又沒有顯示主鍵付枫，又有可以被選擇為主鍵的唯一索引烹玉，但該唯一索引可能不是遞增關系時(例如字符串、UUID阐滩、多字段聯(lián)合唯一索引的情況)二打，該表的存取效率就會比較差。

• 附錄

1. 建模簡介
2. 三范式設計
3. 反范式設計
4. 開發(fā)規(guī)范