MySQL索引下推詳細
前言:
索引下推(ICP)是針對MySQL使用索引從表中檢索數據行的情況的優
- 在沒有索引下推的情況下,MySQL通過存儲引擎遍歷索引來定位表中的數據行並將它們返回給MySQl服務器,服務器再進行WHERE條件的判斷,確認是否將數據行加入結果集。
- 開啟索引下推,且WHERE條件部分可以僅使用索引中的列來評估,這時MySQL服務器會將這部分WHERE條件下推到存儲引擎,接著存儲引擎使用索引條目評估推送的索引條件,僅當滿足該條件時才從表中進行讀取
索引下推可以減少存儲引擎訪問數據表的次數以及MySQL服務器訪問存儲引擎的次數。
是不是還有點懵呢,那就對瞭,毫無疑問上面這段話理解起來相當費勁,但請不要灰心,我將用最通俗易懂的語言來帶你瞭解索引下推。
總結一下:
- 最左前綴原則
- 回表
1、最左前綴原則
MySQL
在建立聯合索引時會遵循最左前綴原則,比如現在User表建立瞭聯合索引(id,name,age)根據最左前綴原則隻有在SQL的條件部分命中(id)、(id,name)或者(id
, name
, age
)時才能使用到這個聯合索引。
能使用該索引的情況如下:
SELECT * FROM USER WHERE id = 1 SELECT * FROM USER WHERE id = 1 and name = 'zhangsan' SELECT * FROM USER WHERE id = 1 and name = 'zhangsan' and age = 18
不能使用該索引的情況如下:
SELECT * FROM USER WHERE name = 'zhangsan' SELECT * FROM USER WHERE age = 18 SELECT * FROM USER WHERE name = 'zhangsan' and age = 18
對於聯合索引mysql
會一直向右匹配直到遇到范圍查詢(>、<、between
、like
)就停止匹配。
2、回表
MySQL
在InnoDB
引擎下支持兩種索引
- 聚集索引 :索引裡(B+樹的葉子結點上)存儲的是數據行(真實的數據)
- 普通索引 :索引裡(B+樹的葉子結點上)存儲的是主鍵
這裡著重說一下聚集索引,官方文檔有以下描述
- 在有主鍵的表,
InnoDB
將主鍵作為聚集索引 - 沒有主鍵的表,
InnoDB
使用第一個唯一索引作為聚集索引 - 即沒有主鍵也沒有唯一索引時,MySQL將生成一個隱藏的6字節大小的
row ID
字段作為聚集索引
MySQL
通過普通索引沒法一次性將數據拿全的情況下,通過普通索引獲取主鍵值,再通過主鍵值到聚集索引中定位到記錄,這個過程就叫回表。可以通過建立覆蓋索引來減少回表,比如現在要通過身份證號查姓名,那就建立身份證號和姓名的聯合索引(id
,name
),當查詢時可以通過這個索引直接拿到姓名name
得值,不再需要去聚集索引裡查找瞭,這就是覆蓋索引。
3、索引下推
首先創建一個用戶表
CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int DEFAULT 0, `class` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_two` (`name`,`age`) ) ENGINE=InnoDB; //這張表增加一個復合索引 (`name`,`age`)
給表插入數據
INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 21, '1'); INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 22, '2'); INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 23, '3'); INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 24, '4'); INSERT INTO `student` (`name`, `age`, `class`) VALUES ('pengpeng', 25, '5');
查詢插入的數據如下
接下來explain下面這個SQL
explain select * from student where name like 'peng%' and age = 23;
可以看到Extra字段顯示為USING INDEX CONDITION,這就表明這個SQL使用瞭索引下推,我們分析下上面這個SQL語句:
在MySQL5.6之前,隻能從name字段中找出符合條件的行然後開始回表,到聚集索引上找出數據行,再對age字段進行對比,把符合條件的數據加入到結果集中。
在MySQL5.6引入瞭索引下推優化,在索引的遍歷過程中,對索引中包含字段先做判斷,這裡對age字段進行判斷。直接將age字段不滿足的數據行排除,從而減少回表的次數。
問答區
問題1 當復合索引列為(name,age,address)時 以下SQL能使用索引嗎?
select * from student where name like 'peng%' and age = 23;
可以,遇到like會中斷後續元素的匹配,但隻能使用name這個字段,mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配。范圍列可以用到索引,但是范圍列後面的列無法用到索引。即索引最多用於一個范圍列,因此如果查詢條件中有兩個范圍列則無法全用到索引。
問題2 索引下推隻能存在聯合索引裡嗎?
是的,非聯合索引無法使用索引下推。
問題3 索引下推在哪些情況下無法使用?
下推條件遇到子查詢
下推條件遇到函數
非InnoDB表和MyISAM表
問題4 索引下推如何開啟和關閉?
// 索引下推默認是開啟的 set optimizer_switch='index_condition_pushdown=off'; // 關閉 set optimizer_switch='index_condition_pushdown=on'; // 開啟
總結
索引下推在非主鍵索引上的優化,可以有效減少回表的次數,大大提升瞭查詢的效率,在平時工作中可以根據業務情況通過優化索引來達到使用索引下推,提高業務吞吐量。
到此這篇關於MySQL索引下推詳細的文章就介紹到這瞭,更多相關MySQL索引下推內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!