什么是MySQL索引下推

本篇內(nèi)容主要講解“什么是MySQL索引下推”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“什么是MySQL索引下推”吧!

目錄

• 1、最左前綴原則

• 2、回表

• 3、索引下推

前言：

索引下推(ICP)是針對(duì)MySQL使用索引從表中檢索數(shù)據(jù)行的情況的優(yōu)

• 在沒(méi)有索引下推的情況下，MySQL通過(guò)存儲(chǔ)引擎遍歷索引來(lái)定位表中的數(shù)據(jù)行并將它們返回給MySQl服務(wù)器，服務(wù)器再進(jìn)行WHERE條件的判斷，確認(rèn)是否將數(shù)據(jù)行加入結(jié)果集。

• 開(kāi)啟索引下推，且WHERE條件部分可以僅使用索引中的列來(lái)評(píng)估，這時(shí)MySQL服務(wù)器會(huì)將這部分WHERE條件下推到存儲(chǔ)引擎，接著存儲(chǔ)引擎使用索引條目評(píng)估推送的索引條件，僅當(dāng)滿足該條件時(shí)才從表中進(jìn)行讀取
  

索引下推可以減少存儲(chǔ)引擎訪問(wèn)數(shù)據(jù)表的次數(shù)以及MySQL服務(wù)器訪問(wèn)存儲(chǔ)引擎的次數(shù)。

是不是還有點(diǎn)懵呢，那就對(duì)了，毫無(wú)疑問(wèn)上面這段話理解起來(lái)相當(dāng)費(fèi)勁，但請(qǐng)不要灰心，我將用最通俗易懂的語(yǔ)言來(lái)帶你了解索引下推。

總結(jié)一下：

• 最左前綴原則

• 回表

1、最左前綴原則

??MySQL在建立聯(lián)合索引時(shí)會(huì)遵循最左前綴原則，比如現(xiàn)在User表建立了聯(lián)合索引（id，name，age）根據(jù)最左前綴原則只有在SQL的條件部分命中(id)、(id，name)或者(id, name, age)時(shí)才能使用到這個(gè)聯(lián)合索引。

能使用該索引的情況如下：

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

對(duì)于聯(lián)合索引mysql會(huì)一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配。

2、回表

MySQL在InnoDB引擎下支持兩種索引

• 聚集索引 ：索引里（B+樹(shù)的葉子結(jié)點(diǎn)上）存儲(chǔ)的是數(shù)據(jù)行（真實(shí)的數(shù)據(jù)）

• 普通索引 ：索引里（B+樹(shù)的葉子結(jié)點(diǎn)上）存儲(chǔ)的是主鍵

這里著重說(shuō)一下聚集索引，官方文檔有以下描述

• 在有主鍵的表，InnoDB將主鍵作為聚集索引

• 沒(méi)有主鍵的表，InnoDB使用第一個(gè)唯一索引作為聚集索引

• 即沒(méi)有主鍵也沒(méi)有唯一索引時(shí)，MySQL將生成一個(gè)隱藏的6字節(jié)大小的row ID字段作為聚集索引
  

??MySQL通過(guò)普通索引沒(méi)法一次性將數(shù)據(jù)拿全的情況下，通過(guò)普通索引獲取主鍵值，再通過(guò)主鍵值到聚集索引中定位到記錄，這個(gè)過(guò)程就叫回表?？梢酝ㄟ^(guò)建立覆蓋索引來(lái)減少回表，比如現(xiàn)在要通過(guò)身份證號(hào)查姓名，那就建立身份證號(hào)和姓名的聯(lián)合索引（id，name），當(dāng)查詢時(shí)可以通過(guò)這個(gè)索引直接拿到姓名name得值，不再需要去聚集索引里查找了，這就是覆蓋索引。

3、索引下推

首先創(chuàng)建一個(gè)用戶表

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;

//這張表增加一個(gè)復(fù)合索引
 (`name`,`age`)

給表插入數(shù)據(jù)

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');

查詢插入的數(shù)據(jù)如下

接下來(lái)explain下面這個(gè)SQL

explain select * from student where name like 'peng%' and age = 23;

可以看到Extra字段顯示為USING INDEX CONDITION，這就表明這個(gè)SQL使用了索引下推，我們分析下上面這個(gè)SQL語(yǔ)句：

在MySQL5.6之前，只能從name字段中找出符合條件的行然后開(kāi)始回表，到聚集索引上找出數(shù)據(jù)行，再對(duì)age字段進(jìn)行對(duì)比，把符合條件的數(shù)據(jù)加入到結(jié)果集中。

在MySQL5.6引入了索引下推優(yōu)化，在索引的遍歷過(guò)程中，對(duì)索引中包含字段先做判斷，這里對(duì)age字段進(jìn)行判斷。直接將age字段不滿足的數(shù)據(jù)行排除，從而減少回表的次數(shù)。

問(wèn)答區(qū)

問(wèn)題1 當(dāng)復(fù)合索引列為（name，age，address）時(shí) 以下SQL能使用索引嗎？

select * from student where name like 'peng%' and age = 23;

??可以，遇到like會(huì)中斷后續(xù)元素的匹配，但只能使用name這個(gè)字段，mysql會(huì)一直向右匹配直到遇到范圍查詢（>、<、between、like）就停止匹配。范圍列可以用到索引，但是范圍列后面的列無(wú)法用到索引。即索引最多用于一個(gè)范圍列，因此如果查詢條件中有兩個(gè)范圍列則無(wú)法全用到索引。

問(wèn)題2 索引下推只能存在聯(lián)合索引里嗎？

是的，非聯(lián)合索引無(wú)法使用索引下推。

問(wèn)題3 索引下推在哪些情況下無(wú)法使用？

下推條件遇到子查詢

下推條件遇到函數(shù)

非InnoDB表和MyISAM表

問(wèn)題4 索引下推如何開(kāi)啟和關(guān)閉？

// 索引下推默認(rèn)是開(kāi)啟的
set optimizer_switch='index_condition_pushdown=off'; // 關(guān)閉
set optimizer_switch='index_condition_pushdown=on'; // 開(kāi)啟

到此，相信大家對(duì)“什么是MySQL索引下推”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！