一篇文章帶你瞭解MySQL索引下推

2022-10-12 14:02:23

前言

本文圍繞這三個話題來學習索引下推：

SELECT 語句執行過程
什麼是索引下推？
索引下推限制

SELECT 語句執行過程

MySQL 資料庫由 Server 層和 Engine 層組成：

Server 層： 有 SQL 分析器、SQL 優化器、SQL 執行器，用於負責 SQL 語句的具體執行過程。
Engine 層： 負責儲存具體的資料，如最常使用的 InnoDB 儲存引擎，還有用於在記憶體中儲存臨時結果集的 TempTable 引擎。

通過使用者端/伺服器通訊協定與 MySQL 建立連線。
查詢快取：
- 如果開啟了 Query Cache 且在查詢快取過程中查詢到完全相同的 SQL 語句，則將查詢結果直接返回給使用者端；
- 如果沒有開啟 Query Cache 或者沒有查詢到完全相同的 SQL 語句則會由解析器進行語法語意解析，並生成解析樹。
分析器生成新的解析樹。
查詢優化器生成執行計劃。
查詢執行引擎執行 SQL 語句，此時查詢執行引擎會根據 SQL 語句中表的儲存引擎型別，以及對應的 API 介面與底層儲存引擎快取或者物理檔案的互動情況，得到查詢結果，由 MySQL Server 過濾後將查詢結果快取並返回給使用者端。

若開啟了 Query Cache，這時也會將 SQL 語句和結果完整地儲存到 Query Cache 中，以後若有相同的 SQL 語句執行則直接返回結果。

Tips：MySQL 8.0 已去掉 query cache（查詢快取模組）。

因為查詢快取的命中率會非常低。查詢快取的失效非常頻繁：只要有對一個表的更新，這個表上所有的查詢快取都會被清空。

什麼是索引下推？

索引下推(Index Condition Pushdown)： 簡稱 ICP，通過把索引過濾條件下推到儲存引擎，來減少 MySQL 儲存引擎存取基表的次數和 MySQL 服務層存取儲存引擎的次數。

索引下推 VS 覆蓋索引： 其實都是 減少回表的次數，只不過方式不同

覆蓋索引： 當索引中包含所需要的欄位（SELECT XXX），則不再回表去查詢欄位。
索引下推： 對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表的行數。

要了解 ICP 是如何工作的，先從一個查詢 SQL 開始：

舉個栗子：查詢名字 la 開頭、年齡為 18 的記錄

SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

有這些記錄：

不開啟 ICP 時索引掃描是如何進行的：

通過索引元組，定位讀取對應資料行。（實際上：就是回表）
對 WHERE 中欄位做判斷，過濾掉不滿足條件的行。

使用 ICP，索引掃描如下進行：

獲取索引元組。
對 WHERE 中欄位做判斷，在索引列中進行過濾。
對滿足條件的索引，進行回表查詢整行。
對 WHERE 中欄位做判斷，過濾掉不滿足條件的行。

動手實驗：

實驗：使用 MySQL 版本 8.0.16

-- 表建立
CREATE TABLE IF NOT EXISTS `user` (
`id` VARCHAR(64) NOT NULL COMMENT '主鍵 id',
`name` VARCHAR(50) NOT NULL COMMENT '名字',
`age` TINYINT NOT NULL COMMENT '年齡',
`address` VARCHAR(100) NOT NULL COMMENT '地址',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '使用者表';

-- 建立索引
CREATE INDEX idx_name_age ON user (name, age);

-- 新增資料
INSERT INTO user (id, name, age, address) VALUES (1, 'tt', 14, 'linhai');
INSERT INTO user (id, name, age, address) VALUES (2, 'lala', 18, 'linhai');
INSERT INTO user (id, name, age, address) VALUES (3, 'laxi', 30, 'linhai');
INSERT INTO user (id, name, age, address) VALUES (4, 'lawa', 40, 'linhai');

-- 查詢語句
SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

新增資料如下：

關閉 ICP，再呼叫 EXPLAIN 檢視語句：

-- 將 ICP 關閉
SET optimizer_switch = 'index_condition_pushdown=off';
-- 檢視確認
show variables like 'optimizer_switch';

-- 用 EXPLAIN 檢視
EXPLAIN SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

開啟 ICP，再呼叫 EXPLAIN 檢視語句：

-- 將 ICP 開啟
SET optimizer_switch = 'index_condition_pushdown=on';
-- 檢視確認
show variables like 'optimizer_switch';

-- 用 EXPLAIN 檢視
EXPLAIN SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

由上實驗可知，區別是否開啟 ICP： Exira 欄位中的 Using index condition

更進一步，來看下 ICP 帶來的效能提升：

通過存取資料檔案的次數

-- 1. 清空 status 狀態
flush status;
-- 2. 查詢
SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
-- 3. 檢視 handler 狀態
show status like '%handler%';

對比開啟 ICP 和關閉 ICP： 關注 Handler_read_next 的值

-- 開啟 ICP
flush status;
SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
show status like '%handler%';
+----------------------------|-------+
| Variable_name              | Value |
+----------------------------|-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |  
| Handler_read_last          | 0     |
| Handler_read_next          | 1     |  <---重點
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------|-------+
18 rows in set (0.00 sec)


-- 關閉 ICP
flush status;
SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
show status like '%handler%';
+----------------------------|-------+
| Variable_name              | Value |
+----------------------------|-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 3     |  <---重點
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------|-------+
18 rows in set (0.00 sec)

由上實驗可知：

開啟 ICP：Handler_read_next 等於 1，回表查 1 次。
關閉 ICP：Handler_read_next 等於 3，回表查 3 次。

這實驗跟上面的栗子就對應上了。

索引下推限制

根據官網可知，索引下推 受以下條件限制：

當需要存取整個錶行時，ICP 用於 range、 ref、 eq_ref 和 ref_or_null
ICP可以用於 InnoDB 和 MyISAM 表，包括分割區表 InnoDB 和 MyISAM 表。
對於 InnoDB 表，ICP 僅用於二級索引。ICP 的目標是減少全行讀取次數，從而減少 I/O 操作。對於 InnoDB 聚集索引，完整的記錄已經讀入 InnoDB 緩衝區。在這種情況下使用 ICP 不會減少 I/O。
在虛擬生成列上建立的二級索引不支援 ICP。InnoDB 支援虛擬生成列的二級索引。
參照子查詢的條件不能下推。
參照儲存功能的條件不能被按下。儲存引擎不能呼叫儲存的函數。
觸發條件不能下推。
不能將條件下推到包含對系統變數的參照的派生表。（MySQL 8.0.30 及更高版本)。

小結下：

ICP 僅適用於 二級索引。
ICP 目標是 減少回表查詢。
ICP 對聯合索引的部分列模糊查詢非常有效。

拓展：虛擬列

CREATE TABLE UserLogin (
userId BIGINT,
loginInfo JSON,
cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
PRIMARY KEY(userId),
UNIQUE KEY idx_cellphone(cellphone)
);

列 cellphone ：就是一個虛擬列，它是由後面的函數表示式計算而成，本身這個列不佔用任何的儲存空間，而索引 idx_cellphone 實質是一個函數索引。

好處： 在寫 SQL 時可以直接使用這個虛擬列，而不用寫冗長的函數。

舉個栗子：查詢手機號

-- 不用虛擬列
SELECT * FROM UserLogin WHERE loginInfo->>"$.cellphone" = '13988888888'

-- 使用虛擬列
SELECT * FROM UserLogin WHERE cellphone = '13988888888'

總結

到此這篇關於MySQL索引下推的文章就介紹到這了,更多相關MySQL索引下推內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！