MySQL索引下推有哪些限制

這篇文章主要講解了“MySQL索引下推有哪些限制”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“MySQL索引下推有哪些限制”吧！

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)存中存儲臨時(shí)結(jié)果集的 TempTable 引擎。

• 通過客戶端/服務(wù)器通信協(xié)議與 MySQL 建立連接。

• 查詢緩存：

• 如果開啟了 Query Cache 且在查詢緩存過程中查詢到完全相同的 SQL 語句，則將查詢結(jié)果直接返回給客戶端；

• 如果沒有開啟 Query Cache 或者沒有查詢到完全相同的 SQL 語句則會由解析器進(jìn)行語法語義解析，并生成解析樹。

• 分析器生成新的解析樹。

• 查詢優(yōu)化器生成執(zhí)行計(jì)劃。

• 查詢執(zhí)行引擎執(zhí)行 SQL 語句，此時(shí)查詢執(zhí)行引擎會根據(jù) SQL 語句中表的存儲引擎類型，以及對應(yīng)的 API 接口與底層存儲引擎緩存或者物理文件的交互情況，得到查詢結(jié)果，由 MySQL Server 過濾后將查詢結(jié)果緩存并返回給客戶端。

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

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

因?yàn)椴樵兙彺娴拿新蕰浅５汀?查詢緩存的失效非常頻繁：只要有對一個(gè)表的更新，這個(gè)表上所有的查詢緩存都會被清空。

什么是索引下推？

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

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

• 覆蓋索引： 當(dāng)索引中包含所需要的字段（SELECT XXX），則不再回表去查詢字段。

• 索引下推： 對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表的行數(shù)。

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

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

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

有這些記錄：

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

• 通過索引元組，定位讀取對應(yīng)數(shù)據(jù)行。（實(shí)際上：就是回表）

• 對 WHERE 中字段做判斷，過濾掉不滿足條件的行。

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

• 獲取索引元組。

• 對 WHERE 中字段做判斷，在索引列中進(jìn)行過濾。

• 對滿足條件的索引，進(jìn)行回表查詢整行。

• 對 WHERE 中字段做判斷，過濾掉不滿足條件的行。

動手實(shí)驗(yàn)：

實(shí)驗(yàn)：使用 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;

由上實(shí)驗(yàn)可知，區(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     |  <---重點(diǎn)
| 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     |  <---重點(diǎn)
| 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)

由上實(shí)驗(yàn)可知：

• 開啟 ICP：Handler_read_next 等于 1，回表查 1 次。

• 關(guān)閉 ICP：Handler_read_next 等于 3，回表查 3 次。

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

索引下推限制

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

• 當(dāng)需要訪問整個(gè)表行時(shí)，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 ：就是一個(gè)虛擬列，它是由后面的函數(shù)表達(dá)式計(jì)算而成，本身這個(gè)列不占用任何的存儲空間，而索引 idx_cellphone 實(shí)質(zhì)是一個(gè)函數(shù)索引。

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

舉個(gè)栗子： 查詢手機(jī)號

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

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

感謝各位的閱讀，以上就是“MySQL索引下推有哪些限制”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對MySQL索引下推有哪些限制這一問題有了更深刻的體會，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識點(diǎn)的文章，歡迎關(guān)注！