一篇文章帶你了解MySQL索引下推
前言
本文圍繞這三個話題來學習索引下推：
SELECT語句執(zhí)行過程- 什么是索引下推？
- 索引下推限制
SELECT 語句執(zhí)行過程
MySQL 數(shù)據(jù)庫由 Server 層和 Engine 層組成：
- Server 層： 有
SQL分析器、SQL優(yōu)化器、SQL執(zhí)行器，用于負責SQL語句的具體執(zhí)行過程。 - Engine 層： 負責存儲具體的數(shù)據(jù)，如最常使用的
InnoDB存儲引擎，還有用于在內(nèi)存中存儲臨時結(jié)果集的TempTable引擎。
通過客戶端/服務器通信協(xié)議與
MySQL建立連接。查詢緩存：
- 如果開啟了
Query Cache且在查詢緩存過程中查詢到完全相同的SQL語句，則將查詢結(jié)果直接返回給客戶端； - 如果沒有開啟
Query Cache或者沒有查詢到完全相同的SQL語句則會由解析器進行語法語義解析，并生成解析樹。
- 如果開啟了
分析器生成新的解析樹。
查詢優(yōu)化器生成執(zhí)行計劃。
查詢執(zhí)行引擎執(zhí)行
SQL語句，此時查詢執(zhí)行引擎會根據(jù)SQL語句中表的存儲引擎類型，以及對應的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 服務層訪問存儲引擎的次數(shù)。
索引下推 VS 覆蓋索引： 其實都是 減少回表的次數(shù)，只不過方式不同
覆蓋索引： 當索引中包含所需要的字段（
SELECT XXX），則不再回表去查詢字段。索引下推： 對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表的行數(shù)。
要了解 ICP 是如何工作的，先從一個查詢 SQL 開始：
舉個栗子：查詢名字 la 開頭、年齡為 18 的記錄
SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
有這些記錄：
不開啟 ICP 時索引掃描是如何進行的：
- 通過索引元組，定位讀取對應數(shù)據(jù)行。（實際上：就是回表）
- 對
WHERE中字段做判斷，過濾掉不滿足條件的行。
使用 ICP，索引掃描如下進行：
- 獲取索引元組。
- 對
WHERE中字段做判斷，在索引列中進行過濾。 - 對滿足條件的索引，進行回表查詢整行。
- 對
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ù)如下：
- 關閉 ICP，再調(diào)用 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，再調(diào)用 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;
由上實驗可知，區(qū)別是否開啟 ICP： Exira 字段中的 Using index condition
更進一步，來看下 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 和 關閉 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 次。
這實驗跟上面的栗子就對應上了。
索引下推限制
根據(jù)官網(wǎng)可知，索引下推 受以下條件限制：
當需要訪問整個表行時，
ICP用于range、ref、eq_ref和ref_or_nullICP可以用于InnoDB和MyISAM表，包括分區(qū)表InnoDB和MyISAM表。對于
InnoDB表，ICP僅用于二級索引。ICP的目標是減少全行讀取次數(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目標是 減少回表查詢。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ù)表達式計算而成，本身這個列不占用任何的存儲空間，而索引 idx_cellphone 實質(zhì)是一個函數(shù)索引。
好處： 在寫 SQL 時可以直接使用這個虛擬列，而不用寫冗長的函數(shù)。
舉個栗子： 查詢手機號
-- 不用虛擬列 SELECT * FROM UserLogin WHERE loginInfo->>"$.cellphone" = '13988888888' -- 使用虛擬列 SELECT * FROM UserLogin WHERE cellphone = '13988888888'
總結(jié)
到此這篇關于MySQL索引下推的文章就介紹到這了,更多相關MySQL索引下推內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要為大家分享了RPM方式安裝MySQL5.6源碼,具有一定的參考價值，感興趣的小伙伴們可以參考一下2016-10-10
這篇文章主要帶大家從一個主從延遲問題開始回顧主從復制原理,并思考主從延遲造成的原因和解決方案,文中的示例代碼講解詳細,感興趣的可以了解下2024-02-02
Mysql數(shù)據(jù)庫之sql基本語句小結(jié)
這篇文章主要介紹了Mysql數(shù)據(jù)庫之sql基本語句,結(jié)合實例形式總結(jié)分析了MySQL數(shù)據(jù)庫連接、登錄、查看以及數(shù)據(jù)庫、數(shù)據(jù)表等常見操作技巧,需要的朋友可以參考下2019-11-11
復制記錄,采用自查詢方式2009-12-12
在軟件開發(fā)中,時間記錄是不可或缺的功能,如記錄操作時間、交易時間等,通常不建議使用字符串存儲日期,因為它占用空間大,并且效率低下,MySQL提供的Datetime和Timestamp是常用的時間存儲類型,Datetime沒有時區(qū)信息,而Timestamp與時區(qū)有關2024-10-10
這篇文章主要分享一個 20 秒 SQL 慢查詢優(yōu)化的經(jīng)歷與處理方案，頁面無法正確獲取數(shù)據(jù)，經(jīng)排查原來是接口調(diào)用超時，而最后發(fā)現(xiàn)是因為SQL查詢長達到20多秒而導致了問題的發(fā)生，下面來看問題具體介紹吧2022-01-01
在本篇文章里小編給各位整理的是一篇關于MySQL模式 Strict Mode知識點詳解內(nèi)容，需要的朋友們參考下。2020-03-03
簡單實現(xiàn)SQLServer轉(zhuǎn)MYSQL的方法
SqlServer數(shù)據(jù)轉(zhuǎn)換成mysql數(shù)據(jù)，可以說是一個老生常談了，網(wǎng)上也有很多的方法，今天我們來看一種不一樣的方法，而且也非常的簡單，雖然有點小缺陷，但還是不失為一種很好的方法，當然如果結(jié)合mss2sql那就非常完美了2014-08-08
由于最近一直接觸php+mysql，會遇到一些問題，所以把這些問題整理下2008-06-06
本文主要介紹了Mysql臨時變量的具體使用,臨時變量有分為用戶變量和會話變量,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2024-08-08