前言

MySQL是一種關(guān)系數(shù)據(jù)庫管理系統(tǒng)，關(guān)系數(shù)據(jù)庫將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個(gè)大倉庫內(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í)，***步需要確定合適的大類型：數(shù)字，字符串，時(shí)間等。下一步是選擇具體類型。很多MySQL的數(shù)據(jù)類型可以存儲(chǔ)相同類型的數(shù)據(jù)，只是存儲(chǔ)的長(zhǎng)度和范圍不一樣，允許的精度不一樣，或者需要的物理空間(磁盤和內(nèi)存空間)不同。相同大類型的不同子類型數(shù)據(jù)有時(shí)也有一些特殊的行為和屬性。 

更小的通常更好 

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

簡(jiǎn)單就好 

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

盡量避免使用NULL 

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

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

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

***范式 

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

第二范式 

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

第三范式 

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

總結(jié) 

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

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

范式缺點(diǎn)： 

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

schema設(shè)計(jì)簡(jiǎn)單原則 

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

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

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

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

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

高性能的索引策略 

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

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

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

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

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

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

對(duì)于如何建立索引列的順序有一個(gè)經(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ù)檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間。e. 通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。 

索引的缺點(diǎn) 

a. 創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加。b. 索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立聚集索引那么需要的空間就會(huì)更大。c. 當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(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)化器生成對(duì)應(yīng)的執(zhí)行計(jì)劃； 

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

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

SHOW FULL PROCESSLIST可查看當(dāng)前狀態(tài)；sleep:線程正在等待客戶端發(fā)送新的請(qǐng)求；Query:線程正在執(zhí)行查詢或者正在將結(jié)果發(fā)送給客戶端；Locked:該線程正在等待表鎖；Analyzing and statistics:線程正在收集存儲(chǔ)引擎的統(tǒng)計(jì)信息，并生產(chǎn)查詢的執(zhí)行計(jì)劃；Coping to tmp table:線程正在執(zhí)行查詢，并將其結(jié)果復(fù)制到臨時(shí)表中；Sorting result:線程正在對(duì)結(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)時(shí)返回全部列c. 總是取出全部列d. 重復(fù)查詢相同的數(shù)據(jù) 

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

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

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

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

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

a. 一個(gè)復(fù)雜查詢還是多個(gè)簡(jiǎn)單查詢；

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

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