現象

最近收到一個慢sql工單,慢sql大概是這樣：“select xxx from tabel where type = 1”。

咦，type欄位明明有索引啊，為啥是慢sql呢？

原因

通過執行explain，發現實際上資料庫執行了全表掃描，從而被系統判定為慢sql。這時有一定開發經驗的同事會說：“欄位區分度不夠，這種欄位作單獨索引是沒有意義的”。那麼為什麼會產生索引失效這種情況呢？索引失效都有哪些情況呢？

索引失效概括

下麵總結了若幹索引失效的情況：

不滿足最左匹配

假如表中有個組合索引，idx_start_org_code_start_province_id_trans_type,它的索引順序如下：

start_org_code,

start_province_id,

trans_type

當我們從第二個索引欄位開始查詢時就不會走索引：

因為索引是BTree結構的，不能跳過第一個索引直接走第二個索引

索引列上有計算

當我們用主鍵做條件時，走索引了：

而當id列上面有計算，比如：

可以看到走了全表掃描

索引列上有函數

有時候我們在某條sql語句的查詢條件中，需要使用函數，比如：截取某個欄位的長度：

你有沒有發現，在使用該函數之後，該sql語句竟然走了全表掃描，索引失效了

欄位類型不同

在sql語句中因為欄位類型不同，而導致索引失效的問題，很容易遇到，可能是我們日常工作中最容易忽略的問題。

到底怎麼回事呢？

我們看下表裡的start_org_code欄位，它是varchar字元類型的

在sql語句查詢數據時，查詢條件我們可以寫成這樣：

從上圖中看到，該欄位走了索引

但如果在寫sql時，不小心把引號丟了：

咦，該sql語句居然變成全表掃描了，為什麼索引失效了？

答：因為這個索引列是varchar類型，而傳參的類型是int，mysql在比較兩種不同類型的欄位時會嘗試把這兩個轉化為同一種類型，再進行比較。這樣就可以理解為在欄位上加了函數，根據上面分析，索引列加了函數會索引失效。

比較有意思的是，如果int類型的id欄位，在查詢時加了引號條件，卻還可以走索引：

從圖中看出該sql語句確實走了索引。int類型的參數，不管在查詢時加沒加引號，都能走索引。

這還科學嗎？有沒有王法了？

答：MySQL發現如果是int類型欄位作為查詢條件時，它會自動將該欄位的傳參進行隱式轉換，把字元串轉換成int類型。

MySQL會把上面列子中的字元串12348，轉換成數字12348，所以仍然能走索引。

事實上，索引列上對欄位做任何操作都會導致索引失效，因為mysq認為任何計算或者函數都會改變索引的實際效果，如果繼續使用索引可能會造成結果不准確。

like左邊包含%

這個相信有點編程經驗的同學都知道,這裡就不舉例說明瞭。但是為什麼索引會失效呢？

答：其實很好理解，索引就像字典中的目錄。一般目錄是按字母或者拼音從小到大，從左到右排序，是有順序的。

我們在查目錄時，通常會先從左邊第一個字母進行匹對，如果相同，再匹對左邊第二個字母，如果再相同匹對其他的字母，以此類推。

通過這種方式我們能快速鎖定一個具體的目錄，或者縮小目錄的範圍。

但如果你硬要跟目錄的設計反著來，先從字典目錄右邊匹配第一個字母，這畫面你可以自行腦補一下，你眼中可能只剩下絕望了，哈哈

列對比

假如我們現在有這樣一個需求：過濾出表中某兩列值相同的記錄。例如：

索引失效了吧？驚不驚喜？

答：表裡create_time和update_time都建了索引，單獨查詢某一欄位時都會走索引。但如果把兩個單獨建了索引的列，用來做列對比時索引會失效。這其實和在索引列上加函數一個原理，MySQL認為索引無法滿足需求。

or和in和exist和not in和not exist

這幾個有異曲同工之處，就放一起說了。這裡就不像上面幾種情況100%不走索引了，而是有時候會走索引，有時候不走索引。到底走不走？成本計算說了算。

成本計算

查詢優化器是 MySQL 的核心子系統之一，成本計算又是查詢優化器的核心邏輯。

全表掃描成本作為參照物，用於和表的其它訪問方式的成本做對比。任何一種訪問方式，只要成本超過了全表掃描成本，就不會被使用。

基於全表掃描成本的重要地位，要講清楚 MySQL 的成本計算邏輯，從全表掃描成本計算開始是個不錯的選擇。

全表掃描成本計算

定義

我們先來看一下Mysql源碼里成本計算的定義：

class Cost_estimate {
 private:
  // cost of I/O operations
  double io_cost;
  // cost of CPU operations
  double cpu_cost;
  // cost of remote operations
  double import_cost;
  // memory used (bytes)
  double mem_cost;
  ......
}

從上面代碼可以看到，MySQL 成本計算模型定義了四種成本：

• IO 成本：從磁碟或記憶體讀取數據頁的成本。
• CPU 成本：訪問記錄需要消耗的 CPU 成本。
• 導入成本：這一項一直沒被使用，先忽略。
• 記憶體成本：這一項指的是占用記憶體位元組數，計算 MRR（Multi Range Read）方式讀取數據的成本時才會用到，也先忽略。

全表掃描的成本就只剩 IO 成本、CPU 成本這兩項了

計算成本

我們先從整體計算公式開始，然後逐步拆解。

全表掃描成本 =io_cost+ 1.1 +cpu_cost+ 1。

io_cost 後面的1.1是硬編碼直接加到 IO 成本上的；cpu_cost 後面的1也是硬編碼的，直接加到 CPU 成本上。代碼里長這樣：

int test_quick_select(...) {
  ......
  double scan_time =
    cost_model->row_evaluate_cost(static_cast<double>(records)) 
    + 1 /* cpu_cost 後面的 + 1 */; 
  Cost_estimate cost_est = table->file->table_scan_cost();
  
  // io_cost 後面的 + 1.1
  cost_est.add_io(1.1);
  ......
}

關於這兩個硬編碼的值，代碼里沒有註釋為什麼要加，不過它們是個固定值，不影響我們理解成本計算邏輯，先忽略它們。

io_cost =cluster_page_count*avg_single_page_cost。

cluster_page_count 是主鍵索引數據頁數量，從表的統計信息中得到，在統計信息小節會介紹。

avg_single_page_cost 是讀取一個數據頁的平均成本，通過計算得到，公式如下：

avg_single_page_cost =pages_in_memory_percent 0.25 +pages_on_disk_percent 1.0**。

pages_in_memory_percent 是主鍵索引已經載入到 Buffer Pool中的葉結點占所有葉結點的比例，用小數表示（取值範圍 0.0 ~ 1.0），例如：80% 表示為 0.8。數據頁在記憶體中的比例小節會介紹具體計算邏輯。

pages_on_disk_percent 是主鍵索引在磁碟文件中的葉結點占所有葉結點的比例，通過1 - pages_in_memory_percent計算得到。

0.25是成本常數 memory_block_read_cost的預設值，表示從 Buffer Pool 中的一個數據頁讀取數據的成本。

1.0是成本常數io_block_read_cost的預設值，表示把磁碟文件中的一個數據頁載入到 Buffer Pool 的成本，加上從 Buffer Pool 中的該數據頁讀取數據的成本。

cpu_cost = n_rows * 0.1。

n_rows 是表中記錄的數量，從表的統計信息中得到，在統計信息小節會介紹。

0.1是成本常數row_evaluate_cost的預設值，表示訪問一條記錄的 CPU 成本。

有了上面這些公式，我們通過一個具體例子走一遍全表掃描成本計算的過程。

假設一個表有 15228 條記錄，主鍵索引數據頁的數量為 739，主鍵索引數據頁已經全部載入到 Buffer Pool（pages_in_memory_percent = 1.0），下麵我們開始計算過程：

• pages_on_disk_percent = 1 -pages_in_memory_percent(1.0) =0.0。
• avg_single_page_cost =pages_in_memory_percent(1.0) *0.25+pages_on_disk_percent(0.0) *1.0=0.25。
• io_cost =cluster_page_count(739) *avg_single_page_cost(0.25) =184.75。
• cpu_cost =n_rows(15228) * 0.1 =1522.8。
• 全表掃描成本 =io_cost(184.75) +1.1+cpu_cost(1522.8) +1=1709.55

統計信息

全表掃描成本計算過程中，用到了主鍵索引數據頁數量、表中記錄數量，這兩個數據都來源 InnoDB 的表統計信息。

clustered_index_size就是主鍵索引數據頁數量，n_rows是表中記錄數量。

數據頁在記憶體中的比例

avg_single_page_cost =pages_in_memory_percent 0.25 +pages_on_disk_percent 1.0**。

上面的公式用於計算讀取一個數據頁的平均成本，pages_in_memory_percent 是主鍵索引已經載入到 Buffer Pool 中的葉結點占所有葉結點的比例。

計算代碼如下：

inline double index_pct_cached(const dict_index_t *index) {
  // 索引葉結點數量
  const ulint n_leaf = index->stat_n_leaf_pages;
  ......
  // 已經載入到 Buffer Pool 中的葉結點數量
  const uint64_t n_in_mem =
      buf_stat_per_index->get(index_id_t(index->space, index->id));
  // 已載入到 Buffer Pool 中的葉結點 [除以] 索引葉結點數量
  const double ratio = static_cast<double>(n_in_mem) / n_leaf;
  // 取值只能在 0.0 ~ 1.0 之間
  return (std::max(std::min(ratio, 1.0), 0.0));
}

InnoDB 在記憶體中維護了一個哈希表（buf_stat_per_index->m_store），key 是表名，value 是表的主鍵索引已經載入到 Buffer Pool 中的葉子結點數量。

每次從磁碟載入某個表的主鍵索引的一個葉子結點數據頁到 Buffer Pool 中，該表在buf_stat_per_index->m_store中對應的 value 值就加一。

從 Buffer Pool 的 LRU 鏈表淘汰某個表的主鍵索引葉子結點時，該表在buf_stat_per_index->m_store中對應的 value 值就減一。

還有其它場景，buf_stat_per_index->m_store 中的 value 值也會發生變化，不展開了。

成本常數

memory_block_read_cost 和 io_block_read_cost 這兩個成本常數從系統表mysql.engine_cost中讀取，預設分別是1.0和0.25

索引成本計算

以如下sql為例

列resource_type的搜索條件是 BETWEEN 1 AND 2，形成的掃描區間就是[1,2]。**優化器規定，讀取二級索引的一個掃描區間的IO成本，和讀取一個頁面的IO成本相同，無論它占用多少頁面。(這個是規定，大家記住就好了)因此二級索引頁的IO成本就是1.0。

接下來就是估算二級索引過濾後的記錄數量了，也就是滿足resource_type BETWEEN 1 AND 2的記錄數量。MySQL是這樣預估的：

• 找到索引樹中resource_type=1的第一條記錄，稱為該區間的最左記錄，這個過程是極快的。
• 找到索引樹中resource_type=2的最後一條記錄，稱為該區間的最右記錄，這個過程也是極快的。
• 從最左記錄向右最多讀10個頁面，如果讀到了最右記錄，則精確計算區間的記錄數。
• 如果讀不到最右記錄，說明中間記錄比較多，則採用預估法。對10個頁面中的記錄數取平均值，用平均值乘以區間的頁面數量即可。

索引頁的Page Header部分有PAGE_N_RECS屬性記錄了頁中的記錄數，因此不用遍歷每個頁里的記錄

又帶來一個新的問題，如何計算這個區間的頁面數量呢？還記得B+樹的結構嗎？該區間的第0層的葉子節點數雖然很多，難以統計，但是我們可以看它們的父節點啊，這兩個索引頁的目錄項大概率是會在同一個父節點頁中的，在父節點頁中統計區間內有多少頁面就非常容易了，其實就是統計兩個目錄項之間隔了多少個目錄項記錄。

這裡，我們假設滿足resource_type BETWEEN 1 AND 2的記錄數是15000個，則二級索引記錄的CPU成本是15000 * 0.2 + 0.01 = 3000.01

接下來就是這15000條記錄回表的IO成本了，MySQL規定，每次回表的IO成本相當於讀取一個頁面的IO成本，二級索引過濾出的記錄數量就是回表的次數。因此，回表的IO成本是15000 * 1.0 = 15000.0。

綜上所述，使用索引的執行成本是：

• IO成本：15000.0+1.0= 15001.0
• CPU成本：3000.01
• 總索引成本：15001.0+3000.01=18001.01

通過和全表掃描對比，孰優孰劣一目瞭然。這下是不是清楚多了？

小結

MySQL執行一條查詢語句的流程是這樣的，先找到所有可能用到的索引，然後計算全表掃描的成本，然後分別計算使用不同索引的成本，最終選擇成本最低的方案來執行查詢。這裡說的成本其實是由IO成本和CPU成本組成的，對於InnoDB引擎來說，讀取一個頁的IO成本是1.0，讀取一條記錄並檢測是否符合搜索條件的CPU成本是0.2。全表掃描的成本計算非常簡單，根據表的統計數據即可預估出聚簇索引占用的頁面數和表的總記錄數。對於二級索引的輔助查詢，除了過濾二級索引本身的IO成本+CPU成本，還有回表的IO成本+CPU成本，

作者：京東物流 劉海運

來源：京東雲開發者社區 自猿其說Tech 轉載請註明來源