MySQL 筆記 - 索引優化

xiaoyusilen發表於2018-08-12
MySql筆記索引優化

寫在前面

這篇文章是《高效能 MySQL》第五章的讀書筆記以及總結~旨在幫助我自己梳理並總結書中的知識點和內容~同時也為一些懶得看書的小夥伴提供一個提取好的知識點~覺得水的朋友煩請關閉喲

巧妙的使用 Explain

看一條 SQL 語句的效能，可以使用 explain 關鍵字檢視語句效能，這裡說一下其中的 type 欄位的部分含義，

  • all，即全表掃描，說明這個 SQL 語句沒有使用到索引，有可能是表本身沒有建立索引，也可能是因為 SQL 語句導致沒有使用索引
  • range，說明使用的是有範圍的索引掃描，效能優於 index
  • index，這裡說明使用了索引，這種情況下，如果 extra 列中的值為 Using index，這種情況是索引覆蓋，索引覆蓋的意思是，我們想要查詢的資料，索引中已經都存在啦，這種情況下就不需要再回表取資料了
  • ref，說明條件列使用了索引，但是不是主鍵和 unique，所以這裡即使使用了索引，索引值不唯一，有重複的情況
  • eq_ref，相對於 ref 來說就是使用的是唯一索引，對於每個索引鍵值，只有唯一的一條匹配記錄
  • const/system，單表中最多隻有一條匹配行，查詢起來非常迅速，所以這個匹配行中的其他列中的值可以被優化器在當前查詢中當做常量來處理。例如根據主鍵或者唯一索引進行的查詢
  • index_merge，說明使用了 MySQL 的索引合併的優化方法，當使用合併索引的時候，就需要檢查一下我們所建立的索引是否為多個單列索引

如何有效的優化索引

使用索引的時候，索引必須作為獨立的列出現

作為獨立的列的意思是，索引不能作為表示式的一部分，也不能作為函式的引數出現，否則索引會失效，原因是 MySQL 無法自動解析表示式以及引數，所以也就無法使用索引了，索引失效還有以下幾種情況：

  • 條件中使用 is null 或者 is not null 會導致索引失效，原因是索引中不會儲存 null
  • 使用 %like，因為 MySQL 是左匹配，使用模糊查詢時如果以%開頭會導致全表查詢
  • 使用多列索引的時候，如果索引順序不是建立索引的順序，或者跳過第一個索引直接使用後面的索引，也會導致索引失敗，原因依然是 MySQL 是左匹配~
  • 條件中包含or時，只有所有列都是單獨索引時才會使用索引

如何建立有效的索引

  • 如果需要索引很長的字串，此時需要考慮字首索引
    • 字首索引即選擇所需字串的一部分字首作為索引，這時候，需要引入一個概念叫做索引選擇性，索引選擇性是指不重複的索引值與資料表的記錄總數的比值，可以看出索引選擇性越高則查詢效率越高，當索引選擇性為1時，效率是最高的，但是在這種場景下，很明顯索引選擇性為1的話我們會付出比較高的代價，索引會很大，這時候我們就需要選擇字串的一部分字首作為索引，通常情況下一列的字首作為索引選擇性也是很高的
    • 如何選擇字首
      • 計算該列完整列的選擇性，使得字首選擇性接近於完整列的選擇性
  • 使用多列索引
    • 儘量不要為多列上建立單列索引，因為這樣的情況下最多隻能使用一星索引，這樣的話，不如去建立一個全覆蓋索引，在多列上建立單列索引大部分情況下並不能提高 MySQL 的查詢效能，MySQL 5.0 中引入了合併索引，在一定程度上可以表內多個單列索引來定位指定的結果，但是 5.0 以前的版本，如果 where 中的多個條件是基於多個單列索引，那麼 MySQL 是無法使用這些索引的，這種情況下，還不如使用 union
  • 選擇合適的索引列順序
    • 經驗是將選擇性最高的列放到索引最前列，可以在查詢的時候過濾出更少的結果集
    • 但這樣並不總是最好的，如果考慮到 group by 或者 order by 等情況，再比如考慮到一些特別場景下的 guest 賬號等資料情況，上面的經驗法則可能就不是最適用的
  • 覆蓋索引
    • 所謂覆蓋索引就是指索引中包含了查詢中的所有欄位，這種情況下就不需要再進行回表查詢了
    • MySQL 中只能使用 B-Tree 索引做覆蓋索引，因為雜湊索引等都不儲存索引的列的值，覆蓋索引對於 MyISAM 和 InnoDB 都非常有效，可以減少系統呼叫和資料拷貝等時間
    • Tips：減少 select * 操作
  • 使用索引掃描來做排序
    • MySQL 生成有序的結果有兩種方法：通過排序操作，或者按照索引順序掃描；使用排序操作需要佔用大量的 CPU 和記憶體資源，而使用 index 效能是很好的，所以，當我們查詢有序結果時，儘量使用索引順序掃描來生成有序結果集
    • 怎樣保證使用索引順序掃描：
      • 索引列順序和 ORDER BY 順序一致
      • 所有列的排序方向一致
      • 如果關聯多表，那麼只有當 ORDER BY 子句引用的欄位全部為第一張表時，才能使用索引做排序，限制依然是需要滿足索引的最左字首要求
  • 壓縮索引
    • 上一篇將索引結構的文章提到了，MyISAM 中使用了字首壓縮技術，會減少索引的大小，可以在記憶體中儲存更多的索引，這部分優化預設也是隻針對字串的，但是可以自定義對整數做壓縮
    • 這個優化在一定情況下效能比較好，但是對於某些情況可能會導致更慢，因為字首壓縮決定了每個關鍵字都必須依賴於前面的值，所以無法使用二分查詢等，只能順序掃描，所以如果查詢的是逆序那麼效能可能不佳
  • 減少重複、冗餘以及未使用的索引
    • MySQL 的唯一限制和主鍵限制都是通過索引實現的，所以不需要在同一列上增加主鍵、唯一限制再建立索引，這樣是重複索引
    • 再舉個例子，如果已經建立了索引（A，B），那麼再建立索引（A）的話，就屬於重複索引，因為 MySQL 索引是最左字首，所以索引（A，B）本身就可以使用索引（A），但是建立索引（B）的話不屬於重複索引
    • 儘量減少新增索引，而應該擴充套件已有的索引，因為新增索引可能會導致 INSERT、UPDATE、DELETE 等操作更慢
    • 可以考慮刪除沒有使用到的索引，定位未使用的索引，有兩個辦法，在 Percona Server 或者 MariaDB 中開啟 userstates 伺服器變數，然後等伺服器執行一段時間後，通過查詢 INFORMATION_SCHEMA.INDEX_STATISTICS 就可以查詢到每個索引的使用頻率
  • 索引和鎖
    • 之前講索引結構的時候說過，InnoDB 支援行鎖和表鎖，預設使用行鎖，而 MyISAM 使用的是表鎖，所以使用索引可以讓查詢鎖定更少的行，這樣也會提升查詢的效能，如果查詢中鎖定了1000行，但實際只是用了100行，那麼在 5.1 之前都需要提交事務之後才能釋放這些鎖，5.1 之後可以在伺服器端過濾掉行之後就釋放鎖，不過依然會導致一些鎖衝突
  • 減少索引和資料碎片
    • 首先我們需要了解一下為什麼會產生碎片，比如 InnoDB 刪除資料時，這一段空間就會被留空，如果一段時間內大量刪除資料，就會導致留空的空間比實際的儲存空間還要大，這時候如果進行新的插入操作時，MySQL 會嘗試重新使用這部分空間，但是依然無法徹底佔用，這樣就會產生碎片
    • 產生碎片帶來的後果當然是，降低查詢效能，因為這種情況會導致隨機磁碟訪問
    • 可以通過 OPTIMIZE TABLE 或者重新匯入資料表來整理資料

總結

資料庫的索引這部分要講的話實在是太多了~絕大部分情況都需要結合實際情況，如果我們可以更多的瞭解資料庫索引本身的一些原理，那麼對於優化會有一些幫助~巧妙地使用 explain 分析自己所寫的 SQL 語句，可以更好的進行優化。

相關文章