mysql查詢優化以及索引使用

如果應用程式使用了mysql，其中包含一些查詢速度慢的sql，我們要去優化它們，優化的思路需要如何進行呢？主要是以下兩點：

1、應用程式是否在檢索大量超過需要的資料（行、列）；

2、mysql伺服器層是否在分析大量超過需要的資料行。

前者比較好進行，主要看開發者的細心以及缜密邏輯、流程分析；後者就需要一些資料庫方面的知識、優化以及實踐技巧。對于後一點，首先需要厘清一個概念，就是掃描行數與傳回行數的差別，後者是我們實際取得的資料，而前者是mysql得出後者所需要掃描的資料量。

如果發現查詢大量的資料但隻傳回少數的行，那麼通常可以嘗試下面的技巧：1、使用索引覆寫索引；2、改變庫表結構，如使用單獨的彙總表；3、重寫這個複雜的查詢。

下面針對查詢優化提供一些技巧：

1、分解關聯查詢。

這個主要針對這種查詢：關聯查詢了多個表，這種情況下可能出現原本可以通過索引實作的order by失效，資料需要在到達mysql伺服器後再進行排序；并且多表關聯，mysql實作的方式是一次掃描取一個表的資料，最後再處理合并，這些都需要消耗mysql的資源。當然，關聯查詢也有一些好處，比如隻需要通路一次mysql，減少網絡請求。當弊大于利時，我們可以采取這樣的優化措施，将主表的資料先查詢出來，其他一些資訊，在代碼裡拼湊好條件，一次性查詢出來，再進行屬性合并等操作。

2、當表a和表b用列c關聯，如果優化器的關聯順序是b、a，那麼就不需要在b的對應列上建立索引

3、確定任何group by和order by中的表達式隻涉及一個表中的列（最好是優化器掃描的第一個表中的），這樣mysql才能使用索引來優化過程

4、group by表達式，如果沒有顯式的order by表達式，預設會對後面的字段進行排序，如果排序字段沒有用上索引，将是一個很大的性能消耗，尤其當有聯表時，需要通過臨時表（using temporary）實作。一個優化的技巧，是加上order by null。

4、mysql總是通過建立并填充臨時表的方式來執行union查詢。是以很多優化政策在union查詢中都沒法很好地使用，經常需要手工地将where limit order by 等子句"下推"到union的各子查詢中，以便優化器可以充分利用這些條件進行優化。

除非确實需要伺服器消除重複的行，否則就一定要使用union all。原因是union操作需要取出兩個表的資料，通過排序排除重複的行，會消耗mysql資源，如果資料量大的還要用到磁盤排序。

5、盡量使用update（通過條件過濾來保證資料一緻性等）代替先select for update再update的寫法，因為事務送出的速度越快，持有的鎖時間就越短，可以大大減少競争和加速串行執行效率。

6、有些查詢是無法優化的，可以考慮使用别的查詢或者政策來實作相同的目的。

7、通過近似計算等方法，先過濾縮小範圍（使用索引），然後再精确過濾。（這種是精确過濾用不上索引時的處理政策）

8、需要的時候，盡可能讓程式完成一些計算。（比如結果集中字元串的拼拼湊湊）

    在優化查詢的過程中，索引的建立、使用扮演着非常重要的角色。建立索引時需要全局考慮所有的查詢，而不僅僅是目前要處理、優化的查詢，不能因為要優化目前的查詢而嚴重影響其他查詢的執行效率。建立索引時需要考慮兩點：1、出現頻率高的查詢條件及其順序，2、索引列的選擇性，要講選擇性高的列放到索引的最前列。索引列的選擇性是指：不重複的索引值和資料表的記錄總數的比值，選擇性越高則查詢效率越高。通常主要考慮第一點，因為它對查詢的效率影響較大。

備援索引，是指這種情況,index1(a),index2(a,b)，index1是index2的最左字首，它的作用也就可以被index2來代替（在使用b-tree索引的時候）。大多數情況下都不需要備援索引，應該盡量盡量擴充已有的索引而不是建立新索引。但也有時候處于性能方面的考慮需要備援索引，因為擴充已有的索引會導緻其變得太大，進而影響其他使用該索引的查詢性能。

    索引使用中需要注意：

1、隻有當索引的列順序和order by子句的順序完全一緻，并且所有列的排序方向（倒序或正序）都一樣時，mysql才能夠使用索引對結果做排序。如果查詢需要關聯多張表，則隻有當order by子句引用的字段全部為第一個表（mysql優化器優化後實際執行時的第一個表）時，才能使用索引做排序。

order by子句和查找型查詢的限制是一樣的，需要滿足索引的最左字首的要求；否則mysql都需要執行排序操作，無法利用索引排序。有一種情況，order by子句可以不滿足索引的最左字首的要求；就是前導列為常量的時候。如果where子句或者join子句對這些列指定了常量，就可以彌補索引的不足。

2、對于一個表的一次掃描中最多隻能用到它的一個索引

3、盡量将需要做範圍查詢的列放到索引的後面，以便優化器能夠使用盡可能多的索引列

 松散索引掃描與緊湊索引掃描：

兩者的差別：在松散索引掃描方式下，分組操作和範圍預測（如果有的話）一起執行完成。在緊湊索引掃描下，先對索引執行範圍掃描（range search），再對結果元組進行分組。

松散索引掃描的條件：

1)、查詢在單一表上

2)、group by指定的列是索引的一個最左字首，并且沒有其他的列

3)、如果在選擇清單select list中存在聚合函數，隻能使用min()和max()兩個聚合函數，并且指定的是同一列

4)、如果查詢中存在除了group by指定的列之外的索引其他部分，那麼必須以常量的形式出現

5)、索引中的列必須索引整個資料列的值，而不是一個字首索引（注意不是索引字首，字首索引是指索引中的某些列不是某個字段，而是某個字段的字首部分）

從5.5開始，松散索引的掃描條件放寬了：

1)、select中的聚合函數除了min()和max()之外，還支援avg(distinct)、sum(distinct)、count(distinct)

2)、查詢中沒有group by和distinct條件

判斷一個查詢是否使用松散索引掃描的方法:執行計劃中有using index for group-by

緊湊索引掃描起作用的條件：

在查詢中存在常量相等等where條件字段（索引中的字段），且該字段在group by指定的字段的前面或者中間。來自相等條件的常量能夠填充搜尋keys中的gaps，因而能夠構成一個索引的完整字首。索引字首能夠使用者索引查找。如果要求對group by的結果進行排序，并且查找字段組成一個索引字首，那麼mysql同樣可以避免額外的排序操作。

查詢使用了緊湊索引掃描的判斷方法：執行計劃中有using index