MySQL 8.0 實(shí)現(xiàn)了索引的隱藏屬性。當(dāng)然這個(gè)特性很多商業(yè)數(shù)據(jù)庫(kù)早就有了，比如ORACLE，在11g中就實(shí)現(xiàn)了。我來(lái)介紹下這個(gè)小特性。

介紹

INVISIBLE INDEX，不可見(jiàn)索引或者叫隱藏索引。就是對(duì)優(yōu)化器不可見(jiàn)，查詢的時(shí)候優(yōu)化器不會(huì)把她作為備選。

其實(shí)以前要想徹底不可見(jiàn)，只能用開(kāi)銷較大的drop index；現(xiàn)在有了新的方式，可以改變索引的屬性，讓其不可見(jiàn)，這一操作只更改metadata，開(kāi)銷非常小。

使用場(chǎng)景

我大概描述下有可能使用隱藏索引的場(chǎng)景：

1. 比如，我有張表t1，本來(lái)已經(jīng)有索引idxf1，idxf2，idxf3。我通過(guò)數(shù)據(jù)字典檢索到idxf3基本沒(méi)有使用過(guò)，那我是不是可以判斷這個(gè)索引直接刪掉就好了？那如果刪掉后突然有新上的業(yè)務(wù)要大量使用呢？難道我要頻繁的drop index/add index嗎？這個(gè)時(shí)候選擇開(kāi)銷比較小的隱藏索引就好了。

2. 我的業(yè)務(wù)只有一個(gè)可能每個(gè)月固定執(zhí)行一次的SQL用到這個(gè)索引，那選擇隱藏索引太合適不過(guò)了。

3. 又或者是我想要測(cè)試下新建索引對(duì)我整個(gè)業(yè)務(wù)的影響程度。如果我直接建新索引，那我既有業(yè)務(wù)涉及到這個(gè)字段的有可能會(huì)收到很大影響。那這個(gè)時(shí)候隱藏索引也是非常合適的。

舉例

下面我來(lái)簡(jiǎn)單舉例如何使用隱藏索引：

表結(jié)構(gòu)mysql>?create?table?f1?(id?serial?primary?key,?f1?int,f2?int?);Query?OK,?0?rows?affected?(0.11?sec)

創(chuàng)建兩個(gè)索引，默認(rèn)可見(jiàn)。

索引1，mysql>?alter?table?f1?add?key?idx_f1(f1),?add?key?idx_f2(f2);Query?OK,?0?rows?affected?(0.12?sec)Records:?0??Duplicates:?0??Warnings:?0

索引2，mysql>?show?create?table?f1\G***************************?1.?row?***************************???????Table:?f1Create?Table:?CREATE?TABLE?`f1`?(??`id`?bigint(20)?unsigned?NOT?NULL?AUTO_INCREMENT,??`f1`?int(11)?DEFAULT?NULL,??`f2`?int(11)?DEFAULT?NULL,??PRIMARY?KEY?(`id`),??UNIQUE?KEY?`id`?(`id`),??KEY?`idx_f1`?(`f1`),??KEY?`idx_f2`?(`f2`))?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4?COLLATE=utf8mb4_0900_ai_ci1?row?in?set?(0.00?sec)

簡(jiǎn)單寫個(gè)造數(shù)據(jù)的存儲(chǔ)過(guò)程。DELIMITER $$

USE`ytt`$$

CREATE PROCEDURE`sp_generate_data_f1`(

IN f_cnt INT

)

BEGIN

DECLARE i,j INT DEFAULT0;

SET@@autocommit=0;

WHILE i

SET i=i+1;

IF j=100THEN

SET j=0;

COMMIT;

ENDIF;

SET j=j+1;

INSERT INTO f1(f1,f2)SELECT CEIL(RAND()*100),CEIL(RAND()*100);

ENDWHILE;

COMMIT;

SET@@autocommit=1;

END$$

DELIMITER;

生成1W條記錄mysql>?call?sp_generate_data_f1(10000);Query?OK,?0?rows?affected?(5.42?sec)

我們把f2列上的索引變?yōu)椴豢梢?jiàn)，結(jié)果瞬間完成。mysql>?alter?table?f1?alter?index?idx_f2?invisible;Query?OK,?0?rows?affected?(0.05?sec)Records:?0??Duplicates:?0??Warnings:?0

在看下表結(jié)構(gòu)，此時(shí)索引標(biāo)記為Invisible。mysql>?show?create?table?f1?\G***************************?1.?row?***************************???????Table:?f1Create?Table:?CREATE?TABLE?`f1`?(??`id`?bigint(20)?unsigned?NOT?NULL?AUTO_INCREMENT,??`f1`?int(11)?DEFAULT?NULL,??`f2`?int(11)?DEFAULT?NULL,??PRIMARY?KEY?(`id`),??UNIQUE?KEY?`id`?(`id`),??KEY?`idx_f1`?(`f1`),??KEY?`idx_f2`?(`f2`)?/*!80000?INVISIBLE?*/)?ENGINE=InnoDB?AUTO_INCREMENT=10001?DEFAULT?CHARSET=utf8mb4?COLLATE=utf8mb4_0900_ai_ci1?row?in?set?(0.00?sec)

給一條有f2列過(guò)濾的SQL， 發(fā)現(xiàn)優(yōu)化器用不到這個(gè)索引了。mysql>?explain?select?count(*)?from?f1?where?f2?=?52\G***************************?1.?row?***************************???????????id:?1??select_type:?SIMPLE????????table:?f1???partitions:?NULL?????????type:?ALLpossible_keys:?NULL??????????key:?NULL??????key_len:?NULL??????????ref:?NULL?????????rows:?9991?????filtered:?1.00????????Extra:?Using?where1?row?in?set,?1?warning?(0.00?sec)

用force index 強(qiáng)制使用，直接報(bào)錯(cuò)。mysql>?explain?select?count(*)?from?f1?force?index?(idx_f2)?where?f2?=?52\GERROR?1176?(42000):?Key?'idx_f2'?doesn't?exist?in?table?'f1'

那 MySQL 8.0 的優(yōu)化器開(kāi)關(guān)里可以告訴它，有的時(shí)候可以用隱藏索引。來(lái)打開(kāi)看看。mysql>?set?@@optimizer_switch?=?'use_invisible_indexes=on';Query?OK,?0?rows?affected?(0.00?sec)

那這條SQL 現(xiàn)在可以用idx_f2了。mysql>?explain?select?count(*)?from?f1?where?f2?=?52\G***************************?1.?row?***************************???????????id:?1??select_type:?SIMPLE????????table:?f1???partitions:?NULL?????????type:?refpossible_keys:?idx_f2??????????key:?idx_f2??????key_len:?5??????????ref:?const?????????rows:?121?????filtered:?100.00????????Extra:?Using?index1?row?in?set,?1?warning?(0.00?sec)

總結(jié)

INVISIBLE INDEX 的確是一個(gè)很有用的小特性，給索引增加了第三個(gè)額外的開(kāi)關(guān)選項(xiàng)。想要了解更多的建議閱讀MySQL手冊(cè)。

總結(jié)

以上是生活随笔為你收集整理的mysql force index报错_新特性解读 | MySQL 8.0 索引特性4-不可见索引的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

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