您好,登錄后才能下訂單哦!
本文小編為大家詳細(xì)介紹“MySQL索引下推是什么”,內(nèi)容詳細(xì),步驟清晰,細(xì)節(jié)處理妥當(dāng),希望這篇“MySQL索引下推是什么”文章能幫助大家解決疑惑,下面跟著小編的思路慢慢深入,一起來(lái)學(xué)習(xí)新知識(shí)吧。
MySQL
數(shù)據(jù)庫(kù)由 Server
層和 Engine
層組成:
Server
層: 有 SQL
分析器、SQL
優(yōu)化器、SQL
執(zhí)行器,用于負(fù)責(zé) SQL
語(yǔ)句的具體執(zhí)行過(guò)程。
Engine
層: 負(fù)責(zé)存儲(chǔ)具體的數(shù)據(jù),如最常使用的 InnoDB
存儲(chǔ)引擎,還有用于在內(nèi)存中存儲(chǔ)臨時(shí)結(jié)果集的 TempTable
引擎。
通過(guò)客戶端/服務(wù)器通信協(xié)議與 MySQL
建立連接。
查詢緩存:
如果開(kāi)啟了 Query Cache
且在查詢緩存過(guò)程中查詢到完全相同的 SQL
語(yǔ)句,則將查詢結(jié)果直接返回給客戶端;
如果沒(méi)有開(kāi)啟 Query Cache
或者沒(méi)有查詢到完全相同的 SQL
語(yǔ)句則會(huì)由解析器進(jìn)行語(yǔ)法語(yǔ)義解析,并生成解析樹(shù)。
分析器生成新的解析樹(shù)。
查詢優(yōu)化器生成執(zhí)行計(jì)劃。
查詢執(zhí)行引擎執(zhí)行 SQL
語(yǔ)句,此時(shí)查詢執(zhí)行引擎會(huì)根據(jù) SQL
語(yǔ)句中表的存儲(chǔ)引擎類型,以及對(duì)應(yīng)的 API
接口與底層存儲(chǔ)引擎緩存或者物理文件的交互情況,得到查詢結(jié)果,由 MySQL Server
過(guò)濾后將查詢結(jié)果緩存并返回給客戶端。
若開(kāi)啟了
Query Cache
,這時(shí)也會(huì)將SQL
語(yǔ)句和結(jié)果完整地保存到Query Cache
中,以后若有相同的SQL
語(yǔ)句執(zhí)行則直接返回結(jié)果。
Tips
:MySQL 8.0
已去掉 query cache
(查詢緩存模塊)。
因?yàn)椴樵兙彺娴拿新蕰?huì)非常低。 查詢緩存的失效非常頻繁:只要有對(duì)一個(gè)表的更新,這個(gè)表上所有的查詢緩存都會(huì)被清空。
索引下推(Index Condition Pushdown
): 簡(jiǎn)稱 ICP
,通過(guò)把索引過(guò)濾條件下推到存儲(chǔ)引擎,來(lái)減少 MySQL
存儲(chǔ)引擎訪問(wèn)基表的次數(shù) 和 MySQL
服務(wù)層訪問(wèn)存儲(chǔ)引擎的次數(shù)。
索引下推 VS 覆蓋索引: 其實(shí)都是 減少回表的次數(shù),只不過(guò)方式不同
覆蓋索引: 當(dāng)索引中包含所需要的字段(SELECT XXX
),則不再回表去查詢字段。
索引下推: 對(duì)索引中包含的字段先做判斷,直接過(guò)濾掉不滿足條件的記錄,減少回表的行數(shù)。
要了解 ICP
是如何工作的,先從一個(gè)查詢 SQL
開(kāi)始:
舉個(gè)栗子:查詢名字 la
開(kāi)頭、年齡為 18
的記錄
SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
有這些記錄:
不開(kāi)啟 ICP
時(shí)索引掃描是如何進(jìn)行的:
通過(guò)索引元組,定位讀取對(duì)應(yīng)數(shù)據(jù)行。(實(shí)際上:就是回表)
對(duì) WHERE
中字段做判斷,過(guò)濾掉不滿足條件的行。
使用 ICP
,索引掃描如下進(jìn)行:
獲取索引元組。
對(duì) WHERE
中字段做判斷,在索引列中進(jìn)行過(guò)濾。
對(duì)滿足條件的索引,進(jìn)行回表查詢整行。
對(duì) WHERE
中字段做判斷,過(guò)濾掉不滿足條件的行。
實(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'); -- 查詢語(yǔ)句 SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
新增數(shù)據(jù)如下:
關(guān)閉 ICP
,再調(diào)用 EXPLAIN
查看語(yǔ)句:
-- 將 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;
開(kāi)啟 ICP
,再調(diào)用 EXPLAIN
查看語(yǔ)句:
-- 將 ICP 打開(kāi) 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ū)別是否開(kāi)啟 ICP
: Exira
字段中的 Using index condition
更進(jìn)一步,來(lái)看下 ICP
帶來(lái)的性能提升:
通過(guò)訪問(wèn)數(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%';
對(duì)比開(kāi)啟 ICP
和 關(guān)閉 ICP
: 關(guān)注 Handler_read_next
的值
-- 開(kāi)啟 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)可知:
開(kāi)啟 ICP
:Handler_read_next
等于 1,回表查 1 次。
關(guān)閉 ICP
:Handler_read_next
等于 3,回表查 3 次。
這實(shí)驗(yàn)跟上面的栗子就對(duì)應(yīng)上了。
根據(jù)官網(wǎng)可知,索引下推 受以下條件限制:
當(dāng)需要訪問(wèn)整個(gè)表行時(shí),ICP
用于 range
、 ref
、 eq_ref
和 ref_or_null
ICP
可以用于 InnoDB
和 MyISAM
表,包括分區(qū)表 InnoDB
和 MyISAM
表。
對(duì)于 InnoDB
表,ICP
僅用于二級(jí)索引。ICP
的目標(biāo)是減少全行讀取次數(shù),從而減少 I/O
操作。對(duì)于 InnoDB
聚集索引,完整的記錄已經(jīng)讀入 InnoDB
緩沖區(qū)。在這種情況下使用 ICP
不會(huì)減少 I/O
。
在虛擬生成列上創(chuàng)建的二級(jí)索引不支持 ICP
。InnoDB
支持虛擬生成列的二級(jí)索引。
引用子查詢的條件不能下推。
引用存儲(chǔ)功能的條件不能被按下。存儲(chǔ)引擎不能調(diào)用存儲(chǔ)的函數(shù)。
觸發(fā)條件不能下推。
不能將條件下推到包含對(duì)系統(tǒng)變量的引用的派生表。(MySQL 8.0.30
及更高版本)。
小結(jié)下:
ICP
僅適用于 二級(jí)索引。
ICP
目標(biāo)是 減少回表查詢。
ICP
對(duì)聯(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è)列不占用任何的存儲(chǔ)空間,而索引 idx_cellphone
實(shí)質(zhì)是一個(gè)函數(shù)索引。
好處: 在寫(xiě) SQL
時(shí)可以直接使用這個(gè)虛擬列,而不用寫(xiě)冗長(zhǎng)的函數(shù)。
舉個(gè)栗子: 查詢手機(jī)號(hào)
-- 不用虛擬列 SELECT * FROM UserLogin WHERE loginInfo->>"$.cellphone" = '13988888888' -- 使用虛擬列 SELECT * FROM UserLogin WHERE cellphone = '13988888888'
讀到這里,這篇“MySQL索引下推是什么”文章已經(jīng)介紹完畢,想要掌握這篇文章的知識(shí)點(diǎn)還需要大家自己動(dòng)手實(shí)踐使用過(guò)才能領(lǐng)會(huì),如果想了解更多相關(guān)內(nèi)容的文章,歡迎關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。