## 巧妙的使用 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 語句，可以更好的進行優化。