MySQL——索引優化實戰

CoderFocus發表於2018-08-08
MySql索引優化

上篇文章中介紹了索引的基本內容，這篇文章我們繼續介紹索引優化實戰。在介紹索引優化實戰之前，首先要介紹兩個與索引相關的重要概念，這兩個概念對於索引優化至關重要。

本篇文章用於測試的user表結構：

mark

索引相關的重要概念

基數

單個列唯一鍵（distict_keys）的數量叫做基數。

SELECT COUNT(DISTINCT name),COUNT(DISTINCT gender) FROM user;

mark

user表的總行數是5，gender 列的基數是 2，說明 gender 列裡面有大量重複值，name 列的基數等於總行數，說明 name列沒有重複值，相當於主鍵。

返回資料的比例：

user表中共有5條資料：

SELECT * FROM user;

mark

查詢滿足性別為0（男）的記錄數：

mark

那麼返回記錄的比例數是：

mark

同理，查詢name為'swj'的記錄數：

mark

返回記錄的比例數是：

mark

現在問題來了，假設name、gender列都有索引，那麼SELECT * FROM user WHERE gender = 0; SELECT * FROM user WHERE name = 'swj';都能命中索引嗎？

user表的索引詳情：

mark

SELECT * FROM user WHERE gender = 0;沒有命中索引，注意filtered的值就是上面我們計算的返回記錄的比例數。

mark

SELECT * FROM user WHERE name = 'swj';命中了索引index_name，因為走索引直接就能找到要查詢的記錄，所以filtered的值為100

mark

結論：

返回表中 30% 內的資料會走索引，返回超過 30% 資料就使用全表掃描。當然這個結論太絕對了，也並不是絕對的30%，只是一個大概的範圍。

回表

當對一個列建立索引之後，索引會包含該列的鍵值及鍵值對應行所在的 rowid。通過索引中記錄的 rowid 訪問表中的資料就叫回表。回表次數太多會嚴重影響 SQL 效能，如果回表次數太多，就不應該走索引掃描，應該直接走全表掃描。

EXPLAIN命令結果中的Using Index意味著不會回表，通過索引就可以獲得主要的資料。Using Where則意味著需要回表取資料。

索引優化實戰

有些時候雖然資料庫有索引，但是並不被優化器選擇使用。

我們可以通過SHOW STATUS LIKE 'Handler_read%';檢視索引的使用情況：

mark

Handler_read_key：如果索引正在工作，Handler_read_key的值將很高。

Handler_read_rnd_next：資料檔案中讀取下一行的請求數，如果正在進行大量的表掃描，值將較高，則說明索引利用不理想。

索引優化規則：

  1. 如果MySQL估計使用索引比全表掃描還慢，則不會使用索引

    返回資料的比例是重要的指標，比例越低越容易命中索引。記住這個範圍值——30%，後面所講的內容都是建立在返回資料的比例在30%以內的基礎上。

  2. 前導模糊查詢查詢不能命中索引

    name列建立普通索引：

    mark

    前導模糊查詢查詢不能命中索引：

    EXPLAIN SELECT * FROM user WHERE name LIKE '%s%';

    mark

    非前導模糊查詢則可以使用索引，可優化為使用非前導模糊查詢：

    EXPLAIN SELECT * FROM user WHERE name LIKE 's%';

    mark

  3. 資料型別出現隱式轉換的時候不會命中索引，特別是當列型別是字串，一定要將字元常量值用引號引起來

    EXPLAIN SELECT * FROM user WHERE name=1;

    mark

    EXPLAIN SELECT * FROM user WHERE name='1';

    mark

    ​

  4. 複合索引的情況下，查詢條件不包含索引列最左邊部分（不滿足最左原則），不會命中符合索引

    name,age,status列建立複合索引：

    ALTER TABLE user ADD INDEX index_name (name,age,status);

    mark

    user表索引詳情：

    SHOW INDEX FROM user;

    mark

    根據最左原則，可以命中複合索引index_name：

    EXPLAIN SELECT * FROM user WHERE name='swj' AND status=1;

    mark

    注意，最左原則並不是說是查詢條件的順序：

    EXPLAIN SELECT * FROM user WHERE status=1 AND name='swj';

    mark

    而是查詢條件中是否包含索引最左列欄位：

    EXPLAIN SELECT * FROM user WHERE status=2 ;

    mark

  5. union、in、or 都能夠命中索引，建議使用 in。

    union:

    EXPLAIN SELECT * FROM user WHERE status = 1

    UNION ALL

    SELECT * FROM user WHERE status = 2;

    mark

    in:

    EXPLAIN SELECT * FROM user WHERE status IN (1,2);

    mark

    or:

    EXPLAIN SELECT * FROM user WHERE status=1 OR status=2;

    mark

    查詢的CPU消耗：or > in >union

  6. 用or分割開的條件，如果or前的條件中列有索引，而後面的列中沒有索引，那麼涉及到的索引都不會被用到

    EXPLAIN SELECT * FROM payment WHERE customer_id = 203 OR amount = 3.96;

    mark

    因為or後面的條件列中沒有索引，那麼後面的查詢肯定要走全表掃描，在存在全表掃描的情況下，就沒有必要多一次索引掃描增加IO訪問。

  7. 負向條件查詢不能使用索引，可以優化為 in 查詢。

    負向條件有：!=、<>、not in、not exists、not like 等。

    status列建立索引：

    ALTER TABLE user ADD INDEX index_status (status);

    mark

    user表索引詳情：

    SHOW INDEX FROM user;

    mark

    負向條件不能命中快取：

    EXPLAIN SELECT * FROM user WHERE status !=1 AND status != 2;

    mark

    可以優化為 in 查詢，但是前提是區分度要高，返回資料的比例在30%以內：

    EXPLAIN SELECT * FROM user WHERE status IN (0,3,4);

    mark

  8. 範圍條件查詢可以命中索引

    範圍條件有：<、<=、>、>=、between等

    status,age列分別建立索引：

    ALTER TABLE user ADD INDEX index_status (status);

    mark

    ALTER TABLE user ADD INDEX index_age (age);

    mark

    user表索引詳情：

    SHOW INDEX FROM user;

    mark

    範圍條件查詢可以命中索引：

    EXPLAIN SELECT * FROM user WHERE status>5;

    mark

    範圍列可以用到索引（聯合索引必須是最左字首），但是範圍列後面的列無法用到索引，索引最多用於一個範圍列，如果查詢條件中有兩個範圍列則無法全用到索引：

    EXPLAIN SELECT * FROM user WHERE status>5 AND age<24;

    mark

    如果是範圍查詢和等值查詢同時存在，優先匹配等值查詢列的索引：

    EXPLAIN SELECT * FROM user WHERE status>5 AND age=24;

    mark

  9. 資料庫執行計算不會命中索引

    EXPLAIN SELECT * FROM user WHERE age > 24;

    mark

    EXPLAIN SELECT * FROM user WHERE age+1 > 24;

    mark

    計算邏輯應該儘量放到業務層處理，節省資料庫的 CPU的同時最大限度的命中索引。

  10. 利用覆蓋索引進行查詢，避免回表

被查詢的列，資料能從索引中取得，而不用通過行定位符 row-locator 再到 row 上獲取，即“被查詢列要被所建的索引覆蓋”，這能夠加速查詢速度。

user表的索引詳情：

mark

因為status欄位是索引列，所以直接從索引中就可以獲取值，不必回表查詢：

Using Index代表從索引中查詢

EXPLAIN SELECT status FROM user where status=1;

mark

當查詢其他列時，就需要回表查詢，這也是為什麼要避免SELECT *的原因之一：

EXPLAIN SELECT * FROM user where status=1;

mark

  1. 建立索引的列，不允許為 null

    單列索引不存 null 值，複合索引不存全為 null 的值，如果列允許為 null，可能會得到“不符合預期”的結果集，所以，請使用 not null 約束以及預設值。

    remark列建立索引：

    ALTER TABLE user ADD INDEX index_remark (remark);

    mark

    IS NULL可以命中索引：

    EXPLAIN SELECT * FROM user WHERE remark IS NULL;

    mark

    IS NOT NULL不能命中索引：

    EXPLAIN SELECT * FROM user WHERE remark IS NOT NULL;

    mark

    雖然IS NULL可以命中索引，但是NULL本身就不是一種好的資料庫設計，應該使用NOT NULL 約束以及預設值

  2. 更新十分頻繁的欄位上不宜建立索引

    因為更新操作會變更B+樹，重建索引。這個過程是十分消耗資料庫效能的。

  3. 區分度不大的欄位上不宜建立索引

    類似於性別這種區分度不大的欄位，建立索引的意義不大。因為不能有效過濾資料，效能和全表掃描相當。另外返回資料的比例在30%以外的情況下，優化器不會選擇使用索引。

  4. 業務上具有唯一特性的欄位，即使是多個欄位的組合，也必須建成唯一索引

    雖然唯一索引會影響insert速度，但是對於查詢的速度提升是非常明顯的。另外，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引，在併發的情況下，依然有髒資料產生。

  5. 多表關聯時，要保證關聯欄位上一定有索引

  6. 建立索引時避免以下錯誤觀念

    • 索引越多越好，認為一個查詢就需要建一個索引。

    • 寧缺勿濫，認為索引會消耗空間、嚴重拖慢更新和新增速度。

    • 抵制唯一索引，認為業務的唯一性一律需要在應用層通過“先查後插”方式解決。

    • 過早優化，在不瞭解系統的情況下就開始優化。

總結

對於自己編寫的SQL查詢語句，要儘量使用EXPLAIN命令分析一下，做一個對SQL效能有追求的程式設計師。衡量一個程式設計師是否靠譜，SQL能力是一個重要的指標。作為後端程式設計師，深以為然。

參考

  • 《深入淺出MySQL》


                                                -----END-----

喜歡本文的朋友們，歡迎掃一掃下圖關注公眾號擼碼那些事，收看更多精彩內容

                                    MySQL——索引優化實戰



相關文章