后端開發(fā)，公眾號內(nèi)容包括但不限于 python、mysql、數(shù)據(jù)結(jié)構(gòu)和算法、網(wǎng)絡(luò)協(xié)議、Linux。技術(shù)人怎能只有技術(shù)和代碼，如果你對投資理財、保險，英語學(xué)習(xí)、讀書寫作有興趣，都?xì)g迎來公眾號【譚某人】與我交流，你總會有些收獲。

在 InnoDB 中，從二級索引回到主鍵索引查詢數(shù)據(jù)，這個過程稱作回表過程，而且這個回表過程是可以被優(yōu)化的，這個優(yōu)化就是利用覆蓋索引。

先說結(jié)論，如果一個索引的字段包含了所有要查詢的字段，這個索引就稱作覆蓋索引，覆蓋索引可以減少回表過程，能有效提高查詢效率。

前面我們有說過，在 InnoDB 中數(shù)據(jù)都是保存在 B+ 樹上，主鍵索引保存了整行記錄，二級索引保存了主鍵的值。

一次查詢操作，要么是遍歷主鍵索引，要么是遍歷二級索引，要么就是先遍歷二級索引得到主鍵 id 的值，然后再到主鍵索引上通過主鍵 id 查找滿足要求的記錄。

如果只遍歷一次 B+ 樹就能獲取到我們要的數(shù)據(jù)，即沒有回表過程，這個效率顯然是不錯的，這就是覆蓋索引的優(yōu)勢。下面看個具體的例子。

mysql>?create?table?user( id?int(11)?primary?key,? name?varchar(20)?not?null,? age?int(11), sex?int(11), index?(age))?engine=InnoDB;

依然是新建一個表，創(chuàng)建索引，插入一些測試數(shù)據(jù)，注意這里只是為了解釋說明覆蓋索引，并不表示 mysql 的真實執(zhí)行方式，因為會涉及到 mysql 的優(yōu)化器機制，這里暫且不說了，以后再寫。

1????bob?16??1 2????kom?19??0 3????gum?18??1 4????tt??20??1 5????yy??25??1

創(chuàng)建一個 user 表，給 age 字段添加一個二級索引，并插入上面五條數(shù)據(jù)，然后看下面這條查詢語句。

select?name?from?user?where?age?between?18?and?21

我們來分析下這條 sql 的執(zhí)行過程：

1、age 字段上有索引，mysql 會先到 age 字段的 B+ 樹上找到滿足條件的第一個葉子節(jié)點（age=19），這個葉子節(jié)點上保存了對應(yīng)主鍵 id 的值 2，然后再到主鍵索引上找到 id 為 2 的這條記錄，同時把 name 字段拿出來。

2、重復(fù)第一步的操作，繼續(xù)從 age 索引上的葉子節(jié)點往后遍歷找出滿足條件的第二個葉子節(jié)點，同樣回到主鍵上拿出 name 字段的值，直到遍歷到不滿足條件的葉子節(jié)點（age=25）。

也就是說，這條 sql 語句雖然用到了索引，但是 age 索引上并沒有要查詢的 name 字段，所以只能回表到主鍵索引上查出 name 字段，所以這個過程其實是遍歷了個兩個 B+ 樹。

那么我們刪除 age 這個單列索引，創(chuàng)建一個覆蓋索引 (age,name)， 把要查詢的 name 字段也添加到索引中來。

#刪除原索引 drop?INDEX?age?on?USER #新建覆蓋索引 ALTER?TABLE?USER?add?index?age_name(age,name)

由于現(xiàn)在這個覆蓋索引上的字段包含了要查詢的 age 和 name 字段，免去了到主鍵索引上查詢數(shù)據(jù)的過程，其實也就是只遍歷了一個 B+ 樹，可以大大提升查詢效率。

添加索引雖然能提升查詢效率，但索引也是需要占用額外空間的，而且索引還需要維護成本，所以通常加不加索引需要根據(jù)實際需求來權(quán)衡。

總之，在設(shè)計索引或者優(yōu)化 sql 語句的時候，要盡量避免回表操作，所以使用覆蓋索引是一種常用的 sql 優(yōu)化手段。

所以我們平時寫 sql 語句的時候，select 后面只寫查詢需要用到的字段，去掉不需要的字段，避免回表操作。

【End】

聽說你對現(xiàn)在的工作不是特別滿意？別擔(dān)心，老王給你準(zhǔn)備一份「Java最常見200+面試題全解析」，助力大家找到更好的工作，這份面試題包含的模塊：

• Java、JVM?最常見面試題解析

• Spring、Spring?MVC、MyBatis、Hibernate?面試題解析

• MySQL、Redis?面試題解析

• RabbitMQ、Kafka、Zookeeper?面試解析

• 微服務(wù)?Spring?Boot、Spring?Cloud?面試解析

掃描下面二維碼付費閱讀

關(guān)注下方二維碼，訂閱更多精彩內(nèi)容。

轉(zhuǎn)發(fā)朋友圈，是對我最大的支持。

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎勵來咯，堅持創(chuàng)作打卡瓜分現(xiàn)金大獎

總結(jié)

以上是生活随笔為你收集整理的如何让mysql索引更快一点的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。