怎么進(jìn)行MySQL性能優(yōu)化中的索引優(yōu)化

本篇文章為大家展示了怎么進(jìn)行MySQL性能優(yōu)化中的索引優(yōu)化，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過(guò)這篇文章的詳細(xì)介紹希望你能有所收獲。

大家都知道索引對(duì)于數(shù)據(jù)訪問(wèn)的性能有非常關(guān)鍵的作用，都知道索引可以提高數(shù)據(jù)訪問(wèn)效率。

為什么索引能提高數(shù)據(jù)訪問(wèn)性能？他會(huì)不會(huì)有“副作用”？是不是索引創(chuàng)建越多，性能就越好？到底該如何設(shè)計(jì)索引，才能最大限度的發(fā)揮其效能？

這里主要是帶著上面這幾個(gè)問(wèn)題來(lái)做一個(gè)簡(jiǎn)要的分析，同時(shí)排除了業(yè)務(wù)場(chǎng)景所帶來(lái)的特殊性，請(qǐng)不要糾結(jié)業(yè)務(wù)場(chǎng)景的影響。

• 索引為什么能提高數(shù)據(jù)訪問(wèn)性能？
  很多人只知道索引能夠提高數(shù)據(jù)庫(kù)的性能，但并不是特別了解其原理，其實(shí)我們可以用一個(gè)生活中的示例來(lái)理解。

  
  

  我們讓一位不太懂計(jì)算機(jī)的朋友去圖書(shū)館確認(rèn)一本叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計(jì)》的書(shū)是否在藏，這樣對(duì)他說(shuō)：“請(qǐng)幫我借一本計(jì)算機(jī)類(lèi)的數(shù)據(jù)庫(kù)書(shū)籍，是屬于 MySQL 數(shù)據(jù)庫(kù)范疇的，叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計(jì)》”。朋友會(huì)根據(jù)所屬類(lèi)別，前往存放“計(jì)算機(jī)”書(shū)籍區(qū)域的書(shū)架，然后再尋找“數(shù)據(jù)庫(kù)”類(lèi)存放位置，再找到一堆講述“MySQL”的書(shū)籍，最后可能發(fā)現(xiàn)目標(biāo)在藏（也可能已經(jīng)借出不在書(shū)架上）。

  在這個(gè)過(guò)程中： “計(jì)算機(jī)”->“數(shù)據(jù)庫(kù)”->“MySQL”->“在藏”->《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計(jì)》其實(shí)就是一個(gè)“根據(jù)索引查找數(shù)據(jù)”的典型案例，“計(jì)算機(jī)”->“數(shù)據(jù)庫(kù)”->“MySQL”->“在藏” 就是朋友查找書(shū)籍的索引。

  假設(shè)沒(méi)有這個(gè)索引，那查找這本書(shū)的過(guò)程會(huì)變成怎樣呢？朋友只能從圖書(shū)館入口一個(gè)書(shū)架一個(gè)書(shū)架的“遍歷”，直到找到《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計(jì)》這本書(shū)為止。如果幸運(yùn)，可能在第一個(gè)書(shū)架就找到。但如果不幸呢，那就慘了，可能要將整個(gè)圖書(shū)館所有的書(shū)架都找一遍才能找到我們想要的這本書(shū)。

  注：這個(gè)例子中的“索引”是記錄在朋友大腦中的，實(shí)際上，每個(gè)圖書(shū)館都會(huì)有一個(gè)非常全的實(shí)際存在的索引系統(tǒng)（大多位于入口顯眼處），由很多個(gè)貼上了明顯標(biāo)簽的小抽屜構(gòu)成。這個(gè)索引系統(tǒng)中存放這非常齊全詳盡的索引數(shù)據(jù)，標(biāo)識(shí)出我們需要查找的“目標(biāo)”在某個(gè)區(qū)域的某個(gè)書(shū)架上。而且每當(dāng)有新的書(shū)籍入庫(kù)，舊的書(shū)籍銷(xiāo)毀以及書(shū)記信息修改，都需要對(duì)索引系統(tǒng)進(jìn)行及時(shí)的修正。

下面我們通過(guò)上面這個(gè)生活中的小示例，來(lái)分析一下索引，看看能的出哪些結(jié)論？

• 索引有哪些“副作用”？

1. 圖書(shū)的變更（增，刪，改）都需要修訂索引，索引存在額外的維護(hù)成本

2. 查找翻閱索引系統(tǒng)需要消耗時(shí)間，索引存在額外的訪問(wèn)成本

3. 這個(gè)索引系統(tǒng)需要一個(gè)地方來(lái)存放，索引存在額外的空間成本

• 索引是不是越多越好？

1. 如果我們的這個(gè)圖書(shū)館只是一個(gè)進(jìn)出中轉(zhuǎn)站，里面的新書(shū)進(jìn)來(lái)后很快就會(huì)轉(zhuǎn)發(fā)去其他圖書(shū)館而從這個(gè)館藏中“清除”，那我們的索引就只會(huì)不斷的修改，而很少會(huì)被用來(lái)查找圖書(shū)
   所以，對(duì)于類(lèi)似于這樣的存在非常大更新量的數(shù)據(jù)，索引的維護(hù)成本會(huì)非常高，如果其檢索需求很少，而且對(duì)檢索效率并沒(méi)有非常高的要求的時(shí)候，我們并不建議創(chuàng)建索引，或者是盡量減少索引。

2. 如果我們的書(shū)籍量少到只有幾本或者就只有一個(gè)書(shū)架，索引并不會(huì)帶來(lái)什么作用，甚至可能還會(huì)浪費(fèi)一些查找索引所花費(fèi)的時(shí)間。
   所以，對(duì)于數(shù)據(jù)量極小到通過(guò)索引檢索還不如直接遍歷來(lái)得快的數(shù)據(jù)，也并不適合使用索引。

3. 如果我們的圖書(shū)館只有一個(gè)10平方的面積，現(xiàn)在連放書(shū)架都已經(jīng)非常擁擠，而且館藏還在不斷增加，我們還能考慮創(chuàng)建索引嗎？
   所以，當(dāng)我們連存儲(chǔ)基礎(chǔ)數(shù)據(jù)的空間都捉襟見(jiàn)肘的時(shí)候，我們也應(yīng)該盡量減少低效或者是去除索引。

• 索引該如何設(shè)計(jì)才高效？

1. 如果我們僅僅只是這樣告訴對(duì)方的：“幫我確認(rèn)一本數(shù)據(jù)庫(kù)類(lèi)別的講述 MySQL 的叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計(jì)》的書(shū)是否在藏”，結(jié)果又會(huì)如何呢？朋友只能一個(gè)大類(lèi)區(qū)域一個(gè)大類(lèi)區(qū)域的去尋找“數(shù)據(jù)庫(kù)”類(lèi)別，然后再找到 “MySQL”范疇，再看到我們所需是否在藏。由于我們少說(shuō)了一個(gè)“計(jì)算機(jī)類(lèi)”，朋友就必須到每一個(gè)大類(lèi)去尋找。
   所以，我們應(yīng)該盡量讓查找條件盡可能多的在索引中，盡可能通過(guò)索引完成所有過(guò)濾，回表只是取出額外的數(shù)據(jù)字段。

2. 如果我們是這樣說(shuō)的：“幫我確認(rèn)一本講述 MySQL 的數(shù)據(jù)庫(kù)范疇的計(jì)算機(jī)叢書(shū)，叫做《MySQL性能調(diào)優(yōu)與架構(gòu)設(shè)計(jì)》，看是否在藏”。如果這位朋友并不知道計(jì)算機(jī)是一個(gè)大類(lèi)，也不知道數(shù)據(jù)庫(kù)屬于計(jì)算機(jī)大類(lèi)，那這位朋友就悲劇了。首先他得遍歷每個(gè)類(lèi)別確認(rèn)“MySQL”存在于哪些類(lèi)別中，然后從包含 “MySQL” 書(shū)籍中再看有哪些是“數(shù)據(jù)庫(kù)”范疇的（有可能部分是講述PHP或者其他開(kāi)發(fā)語(yǔ)言的），然后再排除非計(jì)算機(jī)類(lèi)的（雖然可能并沒(méi)有必要），然后才能確認(rèn)。
   所以，字段的順序?qū)M合索引效率有至關(guān)重要的作用，過(guò)濾效果越好的字段需要更靠前。

3. 如果我們還有這樣一個(gè)需求（雖然基本不可能）：“幫我將圖書(shū)館中所有的計(jì)算機(jī)圖書(shū)借來(lái)”。朋友如果通過(guò)索引來(lái)找，每次都到索引柜找到計(jì)算機(jī)書(shū)籍所在的區(qū)域，然后從書(shū)架上搬下一格（假設(shè)只能以一格為單位從書(shū)架上取下，類(lèi)比數(shù)據(jù)庫(kù)中以block/page為單位讀取），取出第一本，然后再?gòu)乃饕裾业接?jì)算機(jī)圖書(shū)所在區(qū)域，再搬下一格，取出一本… 如此往復(fù)直至取完所有的書(shū)。如果他不通過(guò)索引來(lái)找又會(huì)怎樣呢？他需要從地一個(gè)書(shū)架一直往后找，當(dāng)找到計(jì)算機(jī)的書(shū)，搬下一格，取出所有計(jì)算機(jī)的書(shū)，再往后，直至所有書(shū)架全部看一遍。在這個(gè)過(guò)程中，如果計(jì)算機(jī)類(lèi)書(shū)籍較多，通過(guò)索引來(lái)取所花費(fèi)的時(shí)間很可能要大于直接遍歷，因?yàn)椴粩嗤鶑?fù)的索引翻閱所消耗的時(shí)間會(huì)非常長(zhǎng)。（延伸閱讀：這里有一篇以前寫(xiě)的關(guān)于Oracle的文章，索引掃描還是全表掃描（Index Scan Or Full Table Scan））
   所以，當(dāng)我們需要讀取的數(shù)據(jù)量占整個(gè)數(shù)據(jù)量的比例較大抑或者說(shuō)索引的過(guò)濾效果并不是太好的時(shí)候，使用索引并不一定優(yōu)于全表掃描。

4. 如果我們的朋友不知道“數(shù)據(jù)庫(kù)”這個(gè)類(lèi)別可以屬于“計(jì)算機(jī)”這個(gè)大類(lèi)，抑或者圖書(shū)館的索引系統(tǒng)中這兩個(gè)類(lèi)別屬性并沒(méi)有關(guān)聯(lián)關(guān)系，又會(huì)怎樣呢？也就是說(shuō)，朋友得到的是2個(gè)獨(dú)立的索引，一個(gè)是告知“計(jì)算機(jī)”這個(gè)大類(lèi)所在的區(qū)域，一個(gè)是“數(shù)據(jù)庫(kù)”這個(gè)小類(lèi)所在的區(qū)域（很可能是多個(gè)區(qū)域），那么他只能二者選其一來(lái)搜索我的需求。即使朋友可以分別通過(guò)2個(gè)索引檢索然后自己在腦中取交集再找，那這樣的效率實(shí)際過(guò)程中也會(huì)比較低下。
   所以，在實(shí)際使用過(guò)程中，一次數(shù)據(jù)訪問(wèn)一般只能利用到1個(gè)索引，這一點(diǎn)在索引創(chuàng)建過(guò)程中一定要注意，不是說(shuō)一條SQL語(yǔ)句中Where子句里面每個(gè)條件都有索引能對(duì)應(yīng)上就可以了。

上述內(nèi)容就是怎么進(jìn)行MySQL性能優(yōu)化中的索引優(yōu)化，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注億速云行業(yè)資訊頻道。