索引數(shù)據(jù)在內(nèi)存里的命中率通常比表數(shù)據(jù)高是為什么？

索引比數(shù)據(jù)更小、更緊湊、訪問頻率更高，所以它在內(nèi)存里的“存活率”天然比表數(shù)據(jù)高。

下面從幾個維度拆解原因：

索引比表數(shù)據(jù)“小”，同樣內(nèi)存能裝更多

• 表數(shù)據(jù)：一行記錄包含所有字段（可能有很多列，包括大字段如 TEXT、BLOB），一頁（16KB）能存放的行數(shù)有限。
• 索引：只存索引列的值 + 主鍵值（如果是二級索引），每條記錄很小，一頁能存放的索引條目數(shù)遠(yuǎn)多于表數(shù)據(jù)行數(shù)。

結(jié)果：同樣大小的 Buffer Pool，能緩存的索引頁數(shù)量遠(yuǎn)多于數(shù)據(jù)頁數(shù)量，索引頁被淘汰的概率更低。

查詢總是先經(jīng)過索引（索引的“入口”地位）

幾乎所有的查詢（除了全表掃描）都是先走索引：

1. 先在索引樹里定位到主鍵值。
2. 再通過主鍵去聚簇索引拿數(shù)據(jù)。

因此：

• 索引頁 被訪問的次數(shù)更多（每條查詢都可能訪問）。
• 數(shù)據(jù)頁 只有在回表時才被訪問。

結(jié)果：索引頁的訪問頻率天然高于數(shù)據(jù)頁，LRU 算法會把高頻頁留在內(nèi)存里。

索引訪問更集中（熱點(diǎn)更集中）

• 索引：往往是樹狀結(jié)構(gòu)，根節(jié)點(diǎn)和上層中間節(jié)點(diǎn)被幾乎所有查詢訪問（因?yàn)槿魏尾樵兌家獜母伦撸?。這些節(jié)點(diǎn)一旦進(jìn)入內(nèi)存，幾乎永遠(yuǎn)不會被淘汰，成為“永久熱數(shù)據(jù)”。
• 數(shù)據(jù)：分散在各處，即使同一張表，不同行的訪問頻率差異很大（有的行很少被查）。

結(jié)果：索引的熱點(diǎn)集中在少數(shù)頁上，更容易長期駐留內(nèi)存；數(shù)據(jù)的熱點(diǎn)分散，很多頁雖然被訪問但頻率不夠高，容易被淘汰。

索引的訪問模式更“規(guī)律”

• 索引掃描：往往是有序的（比如范圍查詢），訪問的頁相對連續(xù)，預(yù)讀機(jī)制（read-ahead）能提前把相鄰索引頁加載進(jìn)來，進(jìn)一步提高命中率。
• 數(shù)據(jù)回表：是隨機(jī)的（根據(jù)主鍵值去散落的數(shù)據(jù)頁里找），很難預(yù)讀，數(shù)據(jù)頁的加載更被動。

結(jié)果：索引頁更容易被批量加載并留在內(nèi)存里。

舉例對比

假設(shè)一張 user 表有 1000 萬行，每行 200 字節(jié)，主鍵是 id，有一個二級索引 idx_name。

• 數(shù)據(jù)頁：每頁 16KB，約能存 80 行，共約 12.5 萬頁。
• 索引頁（二級索引）：每條 (name, id) 約 30 字節(jié)，每頁能存約 500 條，共約 2 萬頁。

如果 Buffer Pool 能存 10 萬頁：

• 可以完全緩存所有索引頁（2萬頁）還有大量剩余空間。
• 但只能緩存約 80% 的數(shù)據(jù)頁（10萬 vs 12.5萬）。

實(shí)際運(yùn)行時，索引頁全部在內(nèi)存，數(shù)據(jù)頁部分在內(nèi)存，命中率自然不一樣。

總結(jié)

所以，你在調(diào)優(yōu)數(shù)據(jù)庫時，如果發(fā)現(xiàn) Buffer Pool 不夠大，優(yōu)先保證索引能被完全緩存，這是提升查詢性能的關(guān)鍵。

這個 SQL 在做什么？

EXPLAIN SELECT last_name, first_name, hire_date
FROM employees
WHERE last_name = '鴨';

• SELECT last_name, first_name, hire_date：要查詢的列是 last_name、first_name、hire_date。
• FROM employees：從 employees 表里查。
• WHERE last_name = '鴨'：條件是 last_name 這個字段的值等于字符串 '鴨'（這里用鴨舉例，實(shí)際是某個具體的姓氏）。

加上 EXPLAIN 后，MySQL 不會返回真正的員工數(shù)據(jù)，而是返回一張表，描述它執(zhí)行計(jì)劃的每一步。其中有一列叫 Extra，如果這一列顯示 Using index，就意味著這個查詢使用了覆蓋索引，顯示Using index condition就意味著使用了索引下推，需要回表但次數(shù)減少了。

要理解索引下推，必須先看 MySQL 的“大樓”是怎么分工的。

Server 層（大腦/指揮官）

這是 MySQL 的上層部分，包含了連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等。

• 職責(zé)： 它負(fù)責(zé)邏輯層面的事。比如：這條 SQL 語法對不對？怎么查最快（生成執(zhí)行計(jì)劃）？最后拿到的數(shù)據(jù)是不是還要過濾一下？
• 它不直接碰數(shù)據(jù)： 它只負(fù)責(zé)下達(dá)指令，不負(fù)責(zé)去磁盤上翻找文件。

存儲引擎層（手腳/倉庫管理員）

這是 MySQL 的下層部分，最常用的是 InnoDB。

• 職責(zé)： 它負(fù)責(zé)數(shù)據(jù)的存儲和提取。
• 它聽指令辦事： Server 層說“給我找索引是 A 的數(shù)據(jù)”，它就去翻磁盤，把數(shù)據(jù)找出來交給 Server 層。

什么是“回表”？（背景知識）

在理解下推前，要懂回表。
假設(shè)你有一個聯(lián)合索引 (name, age)，但你查詢的是 select *。

1. 引擎先去索引樹里找到 name 匹配的記錄。
2. 但索引樹里通常不包含所有字段（比如沒有地址、電話），于是引擎要根據(jù)主鍵 ID，回主表再查一次，拿到完整記錄。這就是回表。回表是很耗時的（涉及磁盤 I/O）。

什么是索引下推（ICP）？
索引下推的核心就是：能讓存儲引擎在找數(shù)據(jù)的時候多做點(diǎn)事，盡量減少回表的次數(shù)。

1. 沒有索引下推時（MySQL 5.6 之前）

• Server 層指令： “給我找所有姓‘張’的人。”
• 存儲引擎： “好，我找到 100 個姓張的人，把他們的主鍵 ID 全給你。”
• Server 層： “拿到 100 個 ID 了。我現(xiàn)在挨個去回表，把這 100 人的完整數(shù)據(jù)取回來。取完后，我再自己判斷：這 100 人里誰是‘男’的？哦，只有 10 個。”
• 缺點(diǎn)： 引擎明明知道索引里可能有性別/年齡信息，但它不管，一股腦把 100 個 ID 全傳回去，導(dǎo)致 Server 層做了 100 次回表，而實(shí)際上只有 10 個是有用的。

2. 有了索引下推時（MySQL 5.6 之后）

• Server 層指令： “給我找姓‘張’，且性別為‘男’的人。過濾條件我‘下推’給你了。”
• 存儲引擎： “好。我在找索引的時候，看到姓‘張’的就順便看看他是不是‘男’（因?yàn)檫@個信息就在聯(lián)合索引里）。如果不匹配，我直接扔掉，不告訴你。”
• 存儲引擎： “最后只找到了 10 個匹配的，把這 10 個 ID 給你。”
• Server 層： “太好了，我只需要做 10 次回表。”
• 優(yōu)點(diǎn)： 減少了 90 次回表操作，大大降低了磁盤 I/O，速度飛快。

為什么要強(qiáng)調(diào)“主要用在聯(lián)合索引上”？

這是因?yàn)樗饕峦评玫氖?strong>索引中已經(jīng)存在但無法直接通過索引定位（Range Scan）的列。

舉個具體例子：
假設(shè)索引是 (name, age)。
SQL：

SELECT * FROM user WHERE name LIKE '張%' AND age = 20;

• 正常索引定位： 由于 name 是模糊查詢（張%），根據(jù)最左前綴原則，只有 name 這一列能用到索引來快速定位范圍。age = 20 這個條件在定位時是沒法用的。

• 索引下推：雖然 age 不能用來“定位”范圍，但 age 的數(shù)據(jù)就在這個索引樹里啊！

  • 下推前： 引擎把所有姓“張”的都傳給 Server 層回表。
  • 下推后： 引擎在掃描索引時，直接判斷 age 是不是 20，不是 20 的直接過濾，不回表。

但又有一個坑，如果把SQL改為：
SQL：

SELECT * FROM user WHERE name LIKE '%張%' AND age = 20;

就因?yàn)樽竽：樵儯ㄒ?% 開頭）導(dǎo)致索引“失效”了，存儲引擎連快速定位“起點(diǎn)”的機(jī)會都沒有了，無法實(shí)現(xiàn)“索引尋址”（Index Seek）：只能變成“索引掃描”（Index Scan）或全表掃描。
核心原因：B+ 樹是按“順序”排列的

數(shù)據(jù)庫的索引（B+ 樹）就像一本排好序的字典。
如果你建立了一個聯(lián)合索引 (name, age)，在索引樹里，數(shù)據(jù)是這樣排列的：

• 先按 name 排序：安琪拉、曹操、魯班、張飛、張三、諸葛亮……
• name 相同時按 age 排序：張三(20歲)、張三(25歲)……

場景 A：WHERE name LIKE ‘張%’（前綴匹配）

• 過程： 像翻字典一樣，你直接翻到“張”字開頭的頁碼。
• 結(jié)果： 存儲引擎能立刻定位到姓“張”的數(shù)據(jù)范圍。在這個范圍內(nèi)，它可以利用“索引下推”順便檢查 age 是不是 20。

場景 B：WHERE name LIKE ‘%張%’（全模糊/左模糊）

• 過程： 你要找名字里包含“張”的人。它可以是“張三”，也可以是“老張”，還可以是“光頭張”。
• 尷尬點(diǎn)： 在排好序的字典里，“老張”在 L 部，“光頭張”在 G 部，“張三”在 Z 部。
• 結(jié)果： 索引的“順序性”完全沒用了。存儲引擎無法跳過任何數(shù)據(jù)，它必須從頭到尾掃一遍。

為什么這時候“索引下推”救不了場？

雖然理論上，存儲引擎在“全表掃描”或者“全索引掃描”的過程中，也可以順便判斷 age=20，但這種情況通常不叫“索引下推”的典型應(yīng)用，原因如下：

• 性能差距太巨大：
  • name LIKE ‘張%’：利用索引只掃描 10 行（假設(shè)姓張的就 10 個），回表 0-10 次。
  • name LIKE ‘%張%’：必須掃描 100 萬行（假設(shè)表有 100 萬數(shù)據(jù)），這種掃描叫 Full Table Scan (全表掃描) 或 Full Index Scan。
• 優(yōu)化器的“嫌棄”：
  • 當(dāng)優(yōu)化器發(fā)現(xiàn) name LIKE ‘%張%’ 無法通過索引快速定位數(shù)據(jù)時，它會認(rèn)為：“反正都要全表掃描，我干脆直接讀主鍵索引（聚簇索引）拿完整數(shù)據(jù)好了，省得我還得在二級索引和主表之間跳來跳去。”

一旦決定全表掃描，就不存在所謂的“利用二級索引過濾數(shù)據(jù)”了。
再看這樣的例子

索引下推（ICP）生效的前提是：必須先有“一部分索引”能被用來“定位（Range Scan/Ref）”數(shù)據(jù)的起始范圍。

讓我們對比一下剛才問的例子和圖片里的例子：

1. 剛才的例子：WHERE name LIKE ‘%張%’

• 索引： (name, age)
• 執(zhí)行過程： 索引的第一列 name 就是 % 開頭。由于沒有最左前綴，MySQL 連索引的“門”都進(jìn)不去。
• 結(jié)果： 存儲引擎沒法通過索引定位到任何一個特定的“范圍”，它只能做全表掃描。既然已經(jīng)是全表掃描了，索引下推也就失去了“減少回表”的意義，因?yàn)榇鷥r已經(jīng)封頂了。

2. 圖片里的例子：WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’

• 索引： (zipcode, lastname, firstname)
• 關(guān)鍵點(diǎn)： 這里的 zipcode 是等值查詢（=‘95054’）。
• 執(zhí)行過程：
  1. 索引定位（Index Seek）： 存儲引擎可以利用 zipcode 這個第一列索引，飛快地定位到所有 zipcode=‘95054’ 的索引記錄塊。
  2. 進(jìn)入范圍： 此時，引擎手里已經(jīng)抓住了這 1000 條（假設(shè)）zipcode=‘95054’ 的索引數(shù)據(jù)了。
  3. 索引下推發(fā)揮作用：
     • 如果沒有 ICP： 引擎要把這 1000 條 ID 全部給 Server 層，由 Server 層回表查 1000 次，再過濾 lastname。
     • 如果有 ICP： 引擎發(fā)現(xiàn)這 1000 條索引里已經(jīng)包含了 lastname 這一列的值。雖然 %etrunia% 這種左模糊沒法用來“定位”范圍，但在**已經(jīng)定位好的范圍里做“內(nèi)存過濾”**是可以的！
     • 結(jié)果： 引擎在內(nèi)存里把 1000 條索引過濾一遍，發(fā)現(xiàn)只有 10 條符合 lastname LIKE ‘%etrunia%’，于是只回表 10 次（用戶要的是 SELECT *）。

總結(jié)：兩者的本質(zhì)區(qū)別

• 剛才的例子（只有 %張%）： 你要去圖書館找書名含“張”的書。管理員說：“書是按書名首字母排的，你要找中間含‘張’的，我沒法直接帶你去那一排，你自己從第一排第一本開始一本本翻吧。”（全表掃描，索引失效）
• 圖片例子（郵編 + %張%）： 你找郵政編碼是 95054 且名字含“張”的人。管理員說：“好，郵編是 95054 的都在第 5 貨架，我?guī)氵^去。雖然這排書是按名字排的，我沒法直接定位‘張’，但我可以就在第 5 貨架這幾個格子里幫你翻一下，把名字含‘張’的挑出來給你，你不用把整排書都搬回家查。”（索引下推成功）

結(jié)論：
索引下推是為了在已經(jīng)縮小了的索引范圍內(nèi)，進(jìn)一步利用索引里的其他列進(jìn)行過濾。如果連第一步“縮小范圍”都做不到，索引下推就無從談起。

總結(jié)

1. Server 層： 負(fù)責(zé)決策、過濾、計(jì)算。
2. 存儲引擎層： 負(fù)責(zé)讀寫、根據(jù)索引找 ID。
3. 索引下推（ICP）： 把原本屬于 Server 層的“過濾”邏輯，下放到了存儲引擎層。
4. 核心價值： 減少回表次數(shù)，減少磁盤 I/O，提升查詢性能。
   一句話總結(jié)：
   以前是“把東西都搬回家再挑”，現(xiàn)在是“在倉庫挑好了再搬回家”。

到此這篇關(guān)于MySQL覆蓋索引與索引下推的文章就介紹到這了,更多相關(guān)mysql覆蓋索引與索引下推內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論