快捷導(dǎo)航

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

更新時間：2023年03月03日 10:12:24 作者：格格步入

索引條件下推,也叫索引下推,英文全稱Index?Condition?Pushdown,簡稱ICP,索引下推是MySQL5.6新添加的特性,用于優(yōu)化數(shù)據(jù)的查詢,下面這篇文章主要給大家介紹了關(guān)于MySQL索引下推的相關(guān)資料,需要的朋友可以參考下

前言

本文圍繞這三個話題來學(xué)習(xí)索引下推：

SELECT 語句執(zhí)行過程
什么是索引下推？
索引下推限制

SELECT 語句執(zhí)行過程

MySQL 數(shù)據(jù)庫由 Server 層和 Engine 層組成：

Server 層： 有 SQL 分析器、SQL 優(yōu)化器、SQL 執(zhí)行器，用于負(fù)責(zé) SQL 語句的具體執(zhí)行過程。
Engine 層： 負(fù)責(zé)存儲具體的數(shù)據(jù)，如最常使用的 InnoDB 存儲引擎，還有用于在內(nèi)存中存儲臨時結(jié)果集的 TempTable 引擎。

通過客戶端/服務(wù)器通信協(xié)議與 MySQL 建立連接。
查詢緩存：
- 如果開啟了 Query Cache 且在查詢緩存過程中查詢到完全相同的 SQL 語句，則將查詢結(jié)果直接返回給客戶端；
- 如果沒有開啟 Query Cache 或者沒有查詢到完全相同的 SQL 語句則會由解析器進(jìn)行語法語義解析，并生成解析樹。
分析器生成新的解析樹。
查詢優(yōu)化器生成執(zhí)行計劃。
查詢執(zhí)行引擎執(zhí)行 SQL 語句，此時查詢執(zhí)行引擎會根據(jù) SQL 語句中表的存儲引擎類型，以及對應(yīng)的 API 接口與底層存儲引擎緩存或者物理文件的交互情況，得到查詢結(jié)果，由 MySQL Server 過濾后將查詢結(jié)果緩存并返回給客戶端。

若開啟了 Query Cache，這時也會將 SQL 語句和結(jié)果完整地保存到 Query Cache 中，以后若有相同的 SQL 語句執(zhí)行則直接返回結(jié)果。

Tips：MySQL 8.0 已去掉 query cache（查詢緩存模塊）。

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

什么是索引下推？

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

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

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

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

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

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

有這些記錄：

不開啟 ICP 時索引掃描是如何進(jìn)行的：

通過索引元組，定位讀取對應(yīng)數(shù)據(jù)行。（實際上：就是回表）
對 WHERE 中字段做判斷，過濾掉不滿足條件的行。

使用 ICP，索引掃描如下進(jìn)行：

獲取索引元組。
對 WHERE 中字段做判斷，在索引列中進(jìn)行過濾。
對滿足條件的索引，進(jìn)行回表查詢整行。
對 WHERE 中字段做判斷，過濾掉不滿足條件的行。

動手實驗：

實驗：使用 MySQL 版本 8.0.16

-- 表創(chuàng)建
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 '用戶表';

-- 創(chuàng)建索引
CREATE INDEX idx_name_age ON user (name, age);

-- 新增數(shù)據(jù)
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;

新增數(shù)據(jù)如下：

關(guān)閉 ICP，再調(diào)用 EXPLAIN 查看語句：

-- 將 ICP 關(guān)閉
SET optimizer_switch = 'index_condition_pushdown=off';
-- 查看確認(rèn)
show variables like 'optimizer_switch';

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

開啟 ICP，再調(diào)用 EXPLAIN 查看語句：

-- 將 ICP 打開
SET optimizer_switch = 'index_condition_pushdown=on';
-- 查看確認(rèn)
show variables like 'optimizer_switch';

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

由上實驗可知，區(qū)別是否開啟 ICP： Exira 字段中的 Using index condition

更進(jìn)一步，來看下 ICP 帶來的性能提升：

通過訪問數(shù)據(jù)文件的次數(shù)

-- 1. 清空 status 狀態(tài)
flush status;
-- 2. 查詢
SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
-- 3. 查看 handler 狀態(tài)
show status like '%handler%';

對比開啟 ICP 和關(guān)閉 ICP： 關(guān)注 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)


-- 關(guān)閉 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 次。
關(guān)閉 ICP：Handler_read_next 等于 3，回表查 3 次。

這實驗跟上面的栗子就對應(yīng)上了。

索引下推限制

根據(jù)官網(wǎng)可知，索引下推 受以下條件限制：

當(dāng)需要訪問整個表行時，ICP 用于 range、 ref、 eq_ref 和 ref_or_null
ICP可以用于 InnoDB 和 MyISAM 表，包括分區(qū)表 InnoDB 和 MyISAM 表。
對于 InnoDB 表，ICP 僅用于二級索引。ICP 的目標(biāo)是減少全行讀取次數(shù)，從而減少 I/O 操作。對于 InnoDB 聚集索引，完整的記錄已經(jīng)讀入 InnoDB 緩沖區(qū)。在這種情況下使用 ICP 不會減少 I/O。
在虛擬生成列上創(chuàng)建的二級索引不支持 ICP。InnoDB 支持虛擬生成列的二級索引。
引用子查詢的條件不能下推。
引用存儲功能的條件不能被按下。存儲引擎不能調(diào)用存儲的函數(shù)。
觸發(fā)條件不能下推。
不能將條件下推到包含對系統(tǒng)變量的引用的派生表。（MySQL 8.0.30 及更高版本)。

小結(jié)下：

ICP 僅適用于 二級索引。
ICP 目標(biāo)是 減少回表查詢。
ICP 對聯(lián)合索引的部分列模糊查詢非常有效。

拓展：虛擬列

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

列 cellphone ：就是一個虛擬列，它是由后面的函數(shù)表達(dá)式計算而成，本身這個列不占用任何的存儲空間，而索引 idx_cellphone 實質(zhì)是一個函數(shù)索引。

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

舉個栗子：查詢手機號

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

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

總結(jié)

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

您可能感興趣的文章:

亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

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

目錄

前言

SELECT 語句執(zhí)行過程

什么是索引下推？

動手實驗：

索引下推限制

拓展：虛擬列

總結(jié)

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

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

目錄

前言

SELECT 語句執(zhí)行過程

什么是索引下推？

動手實驗：

索引下推限制

拓展：虛擬列

總結(jié)

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

什么是索引下推？