資料庫效能優化-索引與sql相關優化

一：前言

     什麼是索引？

     索引是幫助MySQL高效獲取資料的資料結構。索引是在儲存引擎中實現的，所以每種儲存引擎中的索引都不一樣。如MYISAM和InnoDB儲存引擎只支援BTree索引；MEMORY儲存引擎可以支援HASH和BTREE索引。

     首先，一些注意點:

• mysql每次只使用一個索引

• mysql只有在查詢中量資料時才會使用索引,查詢絕大部分資料會拒絕使用索引,從而進行全表掃描，對於極少量的資料，mysql也會優化為不使用索引

• 對於聯合索引“a b c”,在B+樹中單獨看b是無序的,在a等值匹配下,看b部分是有序的

• 索引不會包含null值的列

• 每次對資料進行操作,資料庫也會對索引進行相應的操作

• 索引優化，注意回表問題！！！！回表問題，請移步：https://blog.csdn.net/csdn___lyy/article/details/81329020

• 並不是所有索引對查詢都有效，SQL是根據表中資料來進行查詢優化的，當索引列有大量資料重複時，SQL查詢可能不會去利用索引，如一表中有欄位 sex，male、female幾乎各一半，那麼即使在sex上建了索引也對查詢效率起不了作用。

• 索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數較好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有 必要。

---

 二、優化相關（ 僅針對InnoDB儲存引擎所支援的BTree索引）

1.索引的設計原則

• 選擇唯一性索引 

  • 唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。例如，學生表中學號是具有唯一性的欄位。為該欄位建立唯一性索引可以很快的確定某個學生的資訊。如果使用姓名的話，可能存在同名現象，從而降低查詢速度。

• 為經常需要排序、分組和聯合操作的欄位建立索引

  • 經常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的欄位，排序操作會浪費很多時間。如果為其建立索引，可以有效地避免排序操作。

• 為常作為查詢條件的欄位建立索引

  • 如果某個欄位經常用來做查詢條件，那麼該欄位的查詢速度會影響整個表的查詢速度。因此，為這樣的欄位建立索引，可以提高整個表的查詢速度。

• 限制索引的數目

  • 索引的數目不是越多越好。每個索引都需要佔用磁碟空間，索引越多，需要的磁碟空間就越大。修改表時，對索引的重構和更新很麻煩。越多的索引，會使更新表變得很浪費時間。

• 儘量使用資料量少的索引

  • 如果索引的值很長，那麼查詢的速度會受到影響。例如，對一個CHAR（100）型別的欄位進行全文檢索需要的時間肯定要比對CHAR（10）型別的欄位需要的時間要多。a

• 儘量使用字首來索引

  • 如果索引欄位的值很長，最好使用值的字首來索引。例如，TEXT和BLOG型別的欄位，進行全文檢索會很浪費時間。如果只檢索欄位的前面的若干個字元，這樣可以提高檢索速度。

• 刪除不再使用或者很少使用的索引

  • 表中的資料被大量更新，或者資料的使用方式被改變後，原有的一些索引可能不再需要。資料庫管理員應當定期找出這些索引，將它們刪除，從而減少索引對更新操作的影響。

• 選取不經常修改的列

  • 對索引列的修改在索引檔案中也會進行操作

• 聯合索引中，最好將分辨度大的欄位放在前面

  • 計算分辨度公式：select distinct(name)/count(name) from table_name 

  • 值越大說明分辨度越大，則應該放在聯合索引的前面

　　注意：選擇索引的最終目的是為了使查詢的速度變快。上面給出的原則是最基本的準則，但不能拘泥於上面的準則。需要根據應用的實際情況進行分析和判斷，選擇最合適的索引方式。

2.sql不使用索引的情況 

• 參與算術運算的索引

• 參與函式運算的索引

• like中“%aaa%”型別索引,而“aaa%”型別使用索引

• 型別顯式隱式轉換

• 如果mysql估計全表掃描比使用索引快時,也不會使用索引

• 不滿足最左匹配原則

• 用or分割開的條件，or前條件有索引，or後的列沒有索引

• order by在select 中查詢的列，包含索引沒有包含的列，也會不使用索引

• not in

• is null \ is not null ： 用其它相同功能的操作運算代替，如：a is not null 改為 a>0 或a>’’等。不允許欄位為空，而用一個預設值代替空值，如申請中狀態欄位不允許為空，預設為申請。

• 記住, 索引只能告訴你什麼存在於表中, 而不能告訴你什麼不存在於表中.

3.sql優化

• 儘量減少訪問資料庫次數，將一些邏輯放在後臺程式碼中處理

  • 在不影響業務的情況下，整合簡單,無關聯和有關聯的資料庫訪問。

  • 資料庫在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 繫結變數 , 讀資料塊等。沒訪問一次資料庫便會消耗這部分資源。(從快取讀取資料情況除外)

• in和exists的不同使用狀況 

  • 如果子查詢得出的結果集記錄較少，主查詢中的表較大且又有索引時應該用in, 反之如果外層的主查詢記錄較少，子查詢中的表大，又有索引時使用exists。

  • 其實我們區分in和exists主要是造成了驅動順序的改變(這是效能變化的關鍵)，如果是exists，那麼以外層表為驅動表，先被訪問，如果是in，那麼先執行子查詢，所以我們會以驅動表的快速返回為目標，那麼就會考慮到索引及結果集的關係了 ，另外in時不對NULL進行處理。

  • in 是把外表和內表作hash 連線，而exists是對外表作loop迴圈，每次loop迴圈再對內表進行查詢。

• union（去重複）與union all （不去重複）

  • 所以union all比union效率高，都滿足的情況下儘量使用union all。

• where語句後面的條件順序

  • 這一個網路上很多說有用，但我感覺沒有作用，因為資料庫都會自動優化查詢，如果連where後面的條件順序都不能優化的話也太差勁了，並且通過我個人的測試，在千萬數量級的表中，順序並沒有影響，不知道在網上為什麼那麼多說有影響的，以前的版本不可以？有人測試過這方面，有不同的看法，歡迎在評論區討論

• select 避免使用“*：

  • 因為在mysql中，伺服器響應給使用者的資料通常會很多，由多個資料包組成。但是當伺服器響應客戶端請求時，客戶端必須完整的接收整個返回結果，而不能簡單的只取前面幾條結果，然後讓伺服器停止傳送。因而在實際開發中，儘量保持查詢簡單且只返回必需的資料，減小通訊間資料包的大小和數量是一個非常好的習慣。

  • 使用*可能會導致order by不適用索引。

• 用truncate替代delete

  • Truncate是一個能夠快速清空資料表內所有資料的SQL語法。並且能針對具有自動遞增值的欄位，做計數重置歸零重新計算的作用。

  • 另外，當你不再需要該表時， 用 drop；當你仍要保留該表，但要刪除所有記錄時， 用 truncate；當你要刪除部分記錄時（always with a WHERE clause), 用 delete.

• having只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷。

• 用>=替代>    ：

  • 兩者的區別在於, 前者DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄。

  • 如一個表有100萬記錄，一個數值型欄位A，30萬記錄的A=0，30萬記錄的A=1，39萬記錄的A=2，1萬記錄的A=3。那麼執行A>2與A>=3的效果就有很大的區別了，因為A>2時ORACLE會先找出為2的記錄索引再進行比較，而A>=3時ORACLE則直接找到=3的記錄索引。

• join 代替 子查詢

  • MySQL從4.1版開始支援子查詢（一個查詢的結果作為另一個select子句的條件），子查詢雖然靈活但執行效率不高，因為使用子查詢時，MySQL需要為內層查詢語句的查詢結果建立一個臨時表，然後外層查詢語句從臨時表中查詢記錄，查詢完畢後 再撤銷這些臨時表，因此子查詢的速度會相應的受到影響。而連線查詢不需要建立臨時表其查詢速度快於子查詢！

• 使用表的別名(Alias)

  • 當在SQL語句中連線多個表時, 請使用表的別名並把別名字首於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤。

• 用UNION替換OR (適用於索引列)

  • 通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR可能造成全表掃描. 注意, 以上規則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低.

• 儘量使用數字型欄位

  • 若只含數值資訊的欄位儘量不要設計為字元型，這會降低查詢和連線的效能，並會增加儲存開銷。這是因為引擎在處理查詢和連線時會 逐個比較字串中每一個字元，而對於數字型而言只需要比較一次就夠了。

• 儘量避免使用遊標

  • 因為遊標的效率較差，如果遊標操作的資料超過1萬行，那麼就應該考慮改寫。

• 儘量避免向客戶端返回大資料量，若資料量過大，應該考慮相應需求是否合理。

• 儘量避免大事務操作，提高系統併發能力。

• 不要用儲存過程了，缺點太多了，完全可以由快取或者其他方式代替

• 另外：在優化sql的同時，著重優化系統中的慢查詢sql（sql本身層面優化、業務優化後的sql優化等），慢查詢sql才是系統的瓶頸所在。

---

三：其他

1：order by 優化  (來自網路）

• mysql支援兩種方式的排序，FileSort（不使用索引）和Index（使用索引）。

• Index效率比FileSort高，它指MySQL掃描索引本身完成排序。所以對於order by子句，儘量使用Index方式排序，避免使用FileSort方式排序。　　

• order by 滿足三種情況，會使用Index方式排序

  • order by 語句使用索引最左前列

  • 使用where子句與order by子句條件列組合滿足索引最左前列

  • select 查詢的列需要全部屬於使用索引的索引所包含的列

• 

2:最左字首原則

• 通俗的說

  • 最左匹配原則針對的是聯合索引(name,age,phoneNum) ，B+樹是按照從左到右的順序來建立搜尋樹的。如（'張三',18,'18668247652'）來檢索資料的時候，B+樹會優先匹配name來確定搜尋方向，name匹配成功再依次匹配age、phoneNum，最後檢索到最終的資料。

  • 也就是說這種情況下是有三級索引，當name相同，查詢age，age也相同時，去比較phoneNum；但是如果拿 （18,'18668247652'）來檢索時，B+樹沒有拿到一級索引，根本就無法確定下一步的搜尋方向。（'張三','18668247652'）這種場景也是一樣，當name匹配成功後，沒有age這個二級索引，只能在name相同的情況下，去遍歷所有的phoneNum。

  • B+樹的資料結構決定了在使用聯合索引的時候必須遵守最左字首原則，在建立聯合索引的時候，儘量將經常參與查詢的欄位放在聯合索引的最左邊。

• 原則測試：

    1.測試用表

    2.索引

    3.測試sql與解釋

explain select * from testIndex where bid = 2 and cid = 3 and did = 4 ;

最左匹配原則，沒有使用索引

EXPLAIN select * from testIndex where  bid = 1 and aid = 1 and did =  1;   -- aid,bid,did 只有aid,bid使用索引，did不適用

ref中只有兩個const,表明只有兩個欄位使用了索引

EXPLAIN select * from testIndex where  bid = 1 and aid = 1 and cid =  1;   -- aid,bid,did 都使用索引

測試是否正確：mysql會按照聯合索引從左往右進行匹配，直到遇到範圍查詢，如：>,<,between,like等就停止匹配，a = 1 and b =2 and  c > 3 and d = 4，如果建立（a,b,c,d）順序的索引，d是不會使用索引的。但如果聯合索引是（a,b,d,c）的話，則a b d c都可以使用到索引，只是最終c是一個範圍值。

explain select * from testIndex where aid = 1 and bid = 2 and did = 4 and cid = 3 ; 

通過key_len判斷4個欄位都使用了索引

explain select * from testIndex where aid = 1 and bid = 2 and did > 4 and cid = 3 ;

通過key_len為20判斷都是用了索引，因為mysql會優化sql語句，將did與cid的順序進行了排序後為：where aid = 1 and bid = 2 and cid = 3 and did > 4等同於下一個例項，所以四個欄位都是用索引

explain select * from testIndex where aid = 1 and bid = 2 and cid = 3 and did > 4 ;

explain select * from testIndex where aid = 1 and bid = 2 and cid > 3 and did = 4 ;

只有前三個欄位使用了索引，所以測試的那句話是正確的！