MySQL 8.0 雖然發(fā)布很久了，但可能大家都停留在 5.7.x，甚至更老，其實(shí) MySQL 8.0 新增了許多重磅新特性，比如棧長(zhǎng)今天要介紹的 "隱藏索引" 或者 "不可見(jiàn)索引"。

隱藏索引是什么鬼？

隱藏索引 字面意思就是把索引進(jìn)行隱藏，即不可見(jiàn)，它不是用來(lái)查詢優(yōu)化的，所以它不會(huì)被優(yōu)化器使用到。隱藏索引適用于除主鍵索引（顯示或者隱式設(shè)置）之外的索引，意味著主鍵索引是不能通過(guò)任何方式隱藏的。

MySQL 數(shù)據(jù)庫(kù)默認(rèn)創(chuàng)建的索引都是可見(jiàn)的，要顯式控制一個(gè)索引的可見(jiàn)性，可以在 CREATE TABLE，CREATE INDEX 或 ALTER TABLE 的索引定義命令中使用 VISIBLE 或 INVISIBLE 關(guān)鍵字。

如下面示例所示：

CREATE?TABLE?javastack?(age?INT,weight?INT,tall?INT,INDEX?age_idx?(age)?INVISIBLE )?ENGINE?=?InnoDB; CREATE?INDEX?weight_idx?ON?javastack?(weight)?INVISIBLE; ALTER?TABLE?javastack?ADD?INDEX?tall_idx?(tall)?INVISIBLE;

要變更現(xiàn)有索引的可見(jiàn)性，可以在 ?ALTER TABLE ... ALTER INDEX 命令中使用 VISIBLE 或 INVISIBLE 關(guān)鍵字。

年齡索引變更為不可見(jiàn)（隱藏）：

ALTER?TABLE?javastack?ALTER?INDEX?age_idx?INVISIBLE;

年齡索引變更為可見(jiàn)：

ALTER?TABLE?javastack?ALTER?INDEX?age_idx?VISIBLE;

怎么知道一個(gè)表中的索引是可見(jiàn)還是不可見(jiàn)，可以從 INFORMATION_SCHEMA.STATISTICS 表，或者 SHOW INDEX 命令輸出中獲得。例如：

mysql>?SELECT?INDEX_NAME,?IS_VISIBLEFROM?INFORMATION_SCHEMA.STATISTICSWHERE?TABLE_SCHEMA?=?'db1'?AND?TABLE_NAME?=?'javastack'; +------------+------------+ |?INDEX_NAME?|?IS_VISIBLE?| +------------+------------+ |?age_idx??????|?YES????????| |?weight_idx???|?NO?????????| |?tall_idx?????|?NO?????????| +------------+------------+

隱藏索引有什么用？

從上面隱藏索引介紹我們知道，隱藏索引可以不被優(yōu)化器所使用，那么我們可以把某個(gè)表的某個(gè)索引設(shè)置隱藏，然后再測(cè)試 SQL 語(yǔ)句的查詢性能。

這樣就可以利用隱藏索引快速測(cè)試刪除索引后對(duì) SQL 查詢性能的影響，而無(wú)需進(jìn)行索引刪除、重建操作，如果需要該索引，再設(shè)置可見(jiàn)就好了，這在大表測(cè)試中無(wú)疑非常有用，因?yàn)閷?duì)于大表索引的刪除和重新添加很耗性能，甚至影響表的正常工作。

隱藏索引設(shè)置

如果一個(gè)索引被設(shè)置成隱藏了，但實(shí)際上又需要被優(yōu)化器所使用，有幾種表索引情況缺失對(duì)查詢?cè)斐傻挠绊?#xff1a;

1）SQL 查詢語(yǔ)句中包含了索引提示指向不可見(jiàn)索引會(huì)發(fā)生錯(cuò)誤；

2）性能模式數(shù)據(jù)中顯示了受影響 SQL 查詢語(yǔ)句的負(fù)載增高；

3）SQL 查詢語(yǔ)句進(jìn)行 EXPLIAN 時(shí)出現(xiàn)了不同的執(zhí)行計(jì)劃；

4）SQL 查詢語(yǔ)句出現(xiàn)在了慢查詢?nèi)罩局?#xff08;之前沒(méi)有出現(xiàn)）；

系統(tǒng)變量 optimizer_switch 的 use_invisible_indexes 標(biāo)志的值，控制了優(yōu)化器執(zhí)行計(jì)劃構(gòu)建時(shí)是否使用隱藏索引。

如果 use_invisible_indexes 值設(shè)置為 off 關(guān)閉狀態(tài)（默認(rèn)值），優(yōu)化器默認(rèn)會(huì)忽略隱藏索引，即和加入該參數(shù)之前的效果一樣。

如果 use_invisible_indexes 值設(shè)置為 on 打開(kāi)狀態(tài)，隱藏索引仍然保持不可見(jiàn)，但優(yōu)化器會(huì)把隱藏索引加入到執(zhí)行計(jì)劃的構(gòu)建中。

如果想要在某條單個(gè) SQL 查詢語(yǔ)句上啟用隱藏索引，可以使用 SET_VAR 優(yōu)化器提示來(lái)臨時(shí)更新 optimizer_switch 的值，如下所示：

mysql>?EXPLAIN?SELECT?/*+?SET_VAR(optimizer_switch?=?'use_invisible_indexes=on')?*/>?????age,?weight?FROM?javastack?WHERE?weight?>=?150\G ***************************?1.?row?***************************id:?1select_type:?SIMPLEtable:?javastackpartitions:?NULLtype:?range possible_keys:?weight_idxkey:?weight_idxkey_len:?5ref:?NULLrows:?2filtered:?100.00Extra:?Using?index?conditionmysql>?EXPLAIN?SELECT?age,?weight?FROM?javastack?WHERE?weight?>=?150\G ***************************?1.?row?***************************id:?1select_type:?SIMPLEtable:?javastackpartitions:?NULLtype:?ALL possible_keys:?NULLkey:?NULLkey_len:?NULLref:?NULLrows:?5filtered:?33.33Extra:?Using?where

索引的可見(jiàn)性不會(huì)影響索引的自身維護(hù)，例如，不管索引是可見(jiàn)還是不可見(jiàn)，每次表數(shù)據(jù)行的更改索引都會(huì)更新，并且唯一索引也可防止插入重復(fù)數(shù)據(jù)。

沒(méi)有顯式主鍵的表如果在 NOT NULL 列上有任何一個(gè)唯一索引，則仍可能成為有效的隱式主鍵。在這種情況下，第一個(gè)這樣的索引會(huì)對(duì)表數(shù)據(jù)行施加與顯式主鍵相同的約束，并且該索引不能設(shè)置為不可見(jiàn)。

如以下表的定義：

CREATE?TABLE?javastack?(age?INT?NOT?NULL,weight?INT?NOT?NULL,UNIQUE?weight_idx?(weight) )?ENGINE?=?InnoDB;

該表定義不包含任何顯式主鍵，但是 weight 列為 NOT NULL，在該列上創(chuàng)建的唯一索引在數(shù)據(jù)行上與主鍵具有相同的約束，并且不能使其不可見(jiàn)：

mysql>?ALTER?TABLE?javastack?ALTER?INDEX?weight_idx?INVISIBLE; ERROR?3522?(HY000):?A?primary?key?index?cannot?be?invisible.

假設(shè)現(xiàn)在我們將一個(gè)顯式主鍵添加到表中：

ALTER?TABLE?javastack?ADD?PRIMARY?KEY?(age);

顯式主鍵不能設(shè)置為不可見(jiàn)，此時(shí)，weight 列上的唯一索引不再充當(dāng)隱式主鍵，因此可以使其設(shè)置不可見(jiàn)。

mysql>?ALTER?TABLE?javastack?ALTER?INDEX?weight_idx?INVISIBLE;? Query?OK,?0?rows?affected?(0.03?sec)

總結(jié)

本文介紹了 MySQL 8.0 中的新特性：隱藏（不可見(jiàn)）索引，這個(gè)索引并不是新加的索引類型，而是可以控制索引是否加入到執(zhí)行計(jì)劃的構(gòu)建之中。

在實(shí)際生產(chǎn)中也可以利用隱藏索引進(jìn)行 SQL 語(yǔ)句的性能測(cè)試，或者對(duì)索引進(jìn)行邏輯刪除，以及索引的灰度發(fā)布測(cè)試等，用處還是蠻大的。

參考文檔：

https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html

話說(shuō)你們用的 MySQL 哪個(gè)版本呢？

往期推薦

忘記MySQL密碼怎么辦？一招教你搞定！

2020-10-20

MySQL為Null會(huì)導(dǎo)致5個(gè)問(wèn)題，個(gè)個(gè)致命！

2020-12-31

很實(shí)用的21個(gè)SQL小技巧！

2020-11-03

總結(jié)

以上是生活随笔為你收集整理的学到了！MySQL 8 新增的「隐藏索引」真不错的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

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