前言

MySQL是一種關(guān)系數(shù)據(jù)庫管理系統(tǒng)，關(guān)系數(shù)據(jù)庫將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個大倉庫內(nèi)，這樣就增加了速度并提高了靈活性。由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點(diǎn)，一般中小型網(wǎng)站的開發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫。 

由于其社區(qū)版的性能卓越，搭配 PHP 和 Apache 可組成良好的開發(fā)環(huán)境。 

常用技巧 

優(yōu)化的數(shù)據(jù)類型 

優(yōu)先確認(rèn)數(shù)據(jù)類型 

在為列選擇數(shù)據(jù)類型時，***步需要確定合適的大類型：數(shù)字，字符串，時間等。下一步是選擇具體類型。很多MySQL的數(shù)據(jù)類型可以存儲相同類型的數(shù)據(jù)，只是存儲的長度和范圍不一樣，允許的精度不一樣，或者需要的物理空間(磁盤和內(nèi)存空間)不同。相同大類型的不同子類型數(shù)據(jù)有時也有一些特殊的行為和屬性。 

更小的通常更好 

一般情況下，應(yīng)該盡量使用可以正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型。更小的數(shù)據(jù)類型通常更快，因?yàn)樗鼈冋加酶俚拇疟P，內(nèi)存和CPU緩存，并且處理時需要的CPU周期也更少。但是要確保沒有低估需要存儲的值得范圍。 

簡單就好 

簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期。例如，整型比字符操作代價更低，因?yàn)樽址托σ?guī)則(排序規(guī)則)使字符比整型比較更復(fù)雜。 

盡量避免使用NULL 

很多表都包含可為NULL（空值）的列，即使應(yīng)用程序并不需要保存NULL也是如此，這是因?yàn)榭蔀镹ULL是列的默認(rèn)屬性。通常情況下***指定列為NOT NULL，除非真的需要存儲NULL值。如果查詢中包含可為NULL的列，對MySQL來說更難優(yōu)化，因?yàn)榭蔀镹ULL的列使得索引，索引統(tǒng)計和值比較都更復(fù)雜?？蔀镹ULL的列會使用更多的存儲空間，在MySQL里也需要特殊處理，當(dāng)可為NULL的列被索引時，每個索引記錄需要一個額外的字節(jié)。如果計劃在列上建索引，就應(yīng)該避免設(shè)計成可為NULL的列。 

備注：例如：DATETIME和TIMESTAMP列都可以存儲相同類型的數(shù)據(jù)：時間和日期，精確到秒。然而TIMESTAMP只使用DATETIME一半的存儲空間，并且會根據(jù)時區(qū)變化，具有特殊的自動更新能力。另一方面，TIMESTAMP允許的時間范圍要小很多，有時候它的特殊能力會成為障礙。 

遵循數(shù)據(jù)庫設(shè)計的三大范式 

***范式 

確保每列的原子性(強(qiáng)調(diào)的是列的原子性，即列不能夠再分成其他幾列). 如果每列(或者每個屬性)都是不可再分的最小數(shù)據(jù)單元(也稱為最小的原子單元),則滿足***范式. 例如:顧客表(姓名、編號、地址、……)其中"地址"列還可以細(xì)分為國家、省、市、區(qū)等。 

第二范式 

在***范式的基礎(chǔ)上更進(jìn)一層,目標(biāo)是確保表中的每列都和主鍵相關(guān)(一是表必須有一個主鍵；二是沒有包含在主鍵中的列必須完全依賴于主鍵，而不能只依賴于主鍵的部分) 如果一個關(guān)系滿足***范式,并且除了主鍵以外的其它列,都依賴于該主鍵,則滿足第二范式. 例如:訂單表(訂單編號、產(chǎn)品編號、定購日期、價格、……)，"訂單編號"為主鍵，"產(chǎn)品編號"和主鍵列沒有直接的關(guān)系，即"產(chǎn)品編號"列不依賴于主鍵列，應(yīng)刪除該列。 

第三范式 

在第二范式的基礎(chǔ)上更進(jìn)一層,目標(biāo)是確保每列都和主鍵列直接相關(guān),而不是間接相關(guān)(另外非主鍵列必須直接依賴于主鍵，不能存在傳遞依賴). 如果一個關(guān)系滿足第二范式,并且除了主鍵以外的其它列都不依賴于主鍵列,則滿足第三范式. 為了理解第三范式，需要根據(jù)Armstrong公里之一定義傳遞依賴。假設(shè)A、B和C是關(guān)系R的三個屬性，如果A-〉B且B-〉C，則從這些函數(shù)依賴中，可以得出A-〉C，如上所述，依賴A-〉C是傳遞依賴。 例如:訂單表(訂單編號，定購日期，用戶編號，用戶姓名，……)，初看該表沒有問題，滿足第二范式，每列都和主鍵列"訂單編號"相關(guān)，再細(xì)看你會發(fā)現(xiàn)"用戶姓名"和"用戶編號"相關(guān)，"用戶編號"和"訂單編號"又相關(guān)，***經(jīng)過傳遞依賴，"用戶姓名"也和"訂單編號"相關(guān)。為了滿足第三范式，應(yīng)去掉"用戶姓名"列，放入用戶表中。 

總結(jié) 

范式優(yōu)點(diǎn)： 

(1) 范式化的更新操作通常比反范式化要快(2)當(dāng)數(shù)據(jù)較好地范式化時，就只有很少或者沒有重復(fù)數(shù)據(jù)，所以只需要修改更少的數(shù)據(jù)(3)范式化的表通常更小，占用更小的內(nèi)存，所以處理速度更快(4)很少有多余的數(shù)據(jù)，意味著檢索列表時更少需要distinct和group by語句時間 

范式缺點(diǎn)： 

符合范式的schema設(shè)計，查詢時通常需要關(guān)聯(lián)查詢 

schema設(shè)計簡單原則 

• 盡量避免過度設(shè)計，例如會導(dǎo)***其復(fù)雜查詢的schema設(shè)計，或者有很多列的表設(shè)計； 
• 使用小而簡單的合適數(shù)據(jù)類型，除非真是數(shù)據(jù)模型中有確切的需要，否則應(yīng)該盡可能地避免使用NULL值 

盡量使用相同的數(shù)據(jù)類型存儲相似或相關(guān)的值，尤其是要在關(guān)聯(lián)條件中使用的列； 

• 注意可變長字符串，其在臨時表或者排序時可能悲觀的按***長度分配內(nèi)存 
• 盡量使用整型標(biāo)識列 
• 避免使用mysql已經(jīng)遺棄的特性，例如指定浮點(diǎn)數(shù)的精度（可用decimal代替），或者整數(shù)的顯示寬度 

小心使用ENUM和SET，盡量避免使用；避免使用BIT； 

創(chuàng)建高性能索引 

高性能的索引策略 

獨(dú)立的列 我們通常會看到一些查詢不當(dāng)?shù)厥褂盟饕?，或者使得MySQL無法使用已有的索引。如果查詢中的列不是獨(dú)立的，則MySQL就不會使用索引。“獨(dú)立的列”是指索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)。 

前綴索引和索引的選擇性 有時候需要索引很長的字符列，這會讓索引變得大且慢。一個策略是前面提到過的模擬哈希索引。通?？梢运饕_始的部分字符，這樣可以大大節(jié)約索引空間，從而提高索引效率，但是也會降低索引的選擇性。（索引選擇性是指不重復(fù)的索引值和數(shù)據(jù)表的記錄總數(shù)的比值）索引的選擇性越高則查詢效率越高。 

多列索引 一個常見的錯誤是：為每個列創(chuàng)建獨(dú)立的索引，或者按照錯誤的順序創(chuàng)建索引。但實(shí)際上，在多個列上建立獨(dú)立的單列索引大部分情況下并不能提高M(jìn)ySQL的查詢性能。5.0和之后的版本引入“索引合并”的策略，一定程度上緩解了這個問題。（但沒有徹底解決） 

索引合并策略有時候是一種優(yōu)化的結(jié)果，但實(shí)際上更多時候則說明了表上的索引很糟糕 

當(dāng)出現(xiàn)服務(wù)器對多個索引做相交操作的時候，意味著需要一個包含所有相關(guān)列的多列索引而不是多個獨(dú)立的單列索引 

當(dāng)服務(wù)器需要對多個索引做聯(lián)合操作時，通常需要耗費(fèi)大量的CPU和內(nèi)存資源在算法上的緩存、排序和合并。優(yōu)化其不會把這些計算到“查詢成本”中，優(yōu)化器只關(guān)心隨機(jī)頁面的讀取。這會使得查詢的成本被“低估”，導(dǎo)致該執(zhí)行計劃還不如直接走全表掃描。選擇合適的索引列順序。 正確的順序依賴于使用該索引的查詢，并且同時需要考慮如何更好地滿足排序和分組的需要。 在一個多列B-TREE中，索引列的順序意味著索引首先要按照最左列進(jìn)行排序，其次是第二列，以此類推。 

對于如何建立索引列的順序有一個經(jīng)驗(yàn)法則：將選擇性***的列放到索引最前面。 

索引的優(yōu)點(diǎn) 

a. 通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。b. 可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。c. 可以加速表和表之間的連接，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。d. 在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間。e. 通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。 

索引的缺點(diǎn) 

a. 創(chuàng)建索引和維護(hù)索引要耗費(fèi)時間，這種時間隨著數(shù)據(jù)量的增加而增加。b. 索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果要建立聚集索引那么需要的空間就會更大。c. 當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時候，索引也要動態(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。 

備注：因?yàn)樗饕浅Ｕ純?nèi)存，所以索引也需要謹(jǐn)慎添加，那些字段需要索引。

mysql查詢生命周期 

• 客戶端發(fā)送一條查詢給服務(wù)器 ；
• 服務(wù)器先查詢緩存，如果***了緩存，直接返回結(jié)果；否則，進(jìn)入下一步； 
• 服務(wù)器進(jìn)行sql解析、預(yù)處理，再由優(yōu)化器生成對應(yīng)的執(zhí)行計劃； 

mysql根據(jù)優(yōu)化器生成的執(zhí)行計劃，再調(diào)用存儲引擎API來執(zhí)行查詢； 

將查詢結(jié)果返回給客戶端； 

SHOW FULL PROCESSLIST可查看當(dāng)前狀態(tài)；sleep:線程正在等待客戶端發(fā)送新的請求；Query:線程正在執(zhí)行查詢或者正在將結(jié)果發(fā)送給客戶端；Locked:該線程正在等待表鎖；Analyzing and statistics:線程正在收集存儲引擎的統(tǒng)計信息，并生產(chǎn)查詢的執(zhí)行計劃；Coping to tmp table:線程正在執(zhí)行查詢，并將其結(jié)果復(fù)制到臨時表中；Sorting result:線程正在對結(jié)果集進(jìn)行排序；Sending data:線程可能在多種狀態(tài)之間傳送數(shù)據(jù)，或者正在生成結(jié)果集，或者正在向客戶端發(fā)送數(shù)據(jù)； 

查詢性能優(yōu)化 

1、慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)訪問 

低效查詢分析方法： 

a. 確認(rèn)應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù)。通常意味著訪問了太多的行，也有可能訪問太多的列。b. 確認(rèn)mysql服務(wù)器層是否在分析大量超過需要的數(shù)據(jù)行。 

低效查詢典型案列： 

a. 查詢不需要的記錄b. 多表關(guān)聯(lián)時返回全部列c. 總是取出全部列d. 重復(fù)查詢相同的數(shù)據(jù) 

衡量查詢開銷的三個指標(biāo)： 

a. 響應(yīng)時間 響應(yīng)時間包括服務(wù)時間和排隊時間；服務(wù)時間：是指數(shù)據(jù)庫處理這個查詢真正花了多長時間，排隊時間：服務(wù)器因?yàn)榈却承┵Y源而沒有真正執(zhí)行查詢的時間（可能是IO，行鎖等等）；

b. 掃描的行數(shù)；

c. 返回的行數(shù) 較短的行的訪問速度更快，內(nèi)存中的行比磁盤中的行訪問速度更快；較短的行數(shù)，是在內(nèi)存中查詢，當(dāng)行數(shù)較多時則在磁盤中查詢； 

將查詢方式進(jìn)行重構(gòu) 

a. 一個復(fù)雜查詢還是多個簡單查詢；

b. 切分查詢（大查詢分為小查詢，例如：大掃描行數(shù)查詢切分成多個小掃描行數(shù)的查詢）；

c. 分解關(guān)聯(lián)查詢，優(yōu)點(diǎn)：讓緩存效率更高；讓單個查詢減少鎖競爭；在應(yīng)用層做關(guān)聯(lián)，容易對數(shù)據(jù)庫進(jìn)行拆分，提高系統(tǒng)性能；減少冗余記錄的查詢；