MySQL8.0新特性——不可見索引（Invisible Indexes）

Mysql8.0新特性——不可見索引（Invisible Indexes）

Mysql8.0開始支持看不見的索引。一個看不見的索引根本不被優化器使用，但是通常是保持正常的。默認情況下索引是可見的。不可見的索引使測試在查詢性能上刪除索引的效果成為可能，而不需要在需要索引的情況下進行破壞性的更改。

注意：

該特性適用于除主鍵以外的索引（顯式或隱式）默認情況下索引是可見的！。

將index設置為invisible，會導致優化器在選擇執行計劃時，自動忽略該索引，即便使用了FORCE INDEX

官方文檔：

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

1、創建測試表t1，并創建3個不可見索引

1

2

3

4

5

6

7

8

9

10

11

12

mysql>?CREATE?TABLE?t1?(i?INT,j?INT,k?INT,?INDEX?i_idx?(i)?INVISIBLE)?ENGINE?=?InnoDB;

mysql>?CREATE?INDEX?j_idx?ON?t1?(j)?INVISIBLE;???--創建不可見索引：j_idx

mysql>?ALTER?TABLE?t1?ADD?INDEX?k_idx?(k)?INVISIBLE;??--創建不可見索引：k_idx

mysql>?SELECT?INDEX_NAME,?IS_VISIBLE???FROM?INFORMATION_SCHEMA.STATISTICS???WHERE?TABLE_SCHEMA?=?'test'?AND?TABLE_NAME?=?'t1';???--可以看到3個索引都是不可見狀態

+------------+------------+

|?INDEX_NAME?|?IS_VISIBLE?|

+------------+------------+

|?i_idx??????|?NO?????????|

|?j_idx??????|?NO?????????|

|?k_idx??????|?NO?????????|

+------------+------------+

3?rows?in?set?(0.00?sec)

2、修改索引為可見狀態，也就是可使用的狀態

1

2

3

4

5

6

7

8

9

10

mysql>?ALTER?TABLE?t1?ALTER?INDEX?i_idx?VISIBLE;???--修改索引為可見狀態

mysql>?SELECT?INDEX_NAME,?IS_VISIBLE???FROM?INFORMATION_SCHEMA.STATISTICS???WHERE?TABLE_SCHEMA?=?'test'?AND?TABLE_NAME?=?'t1';??--可以看到狀態為yes了。

+------------+------------+

|?INDEX_NAME?|?IS_VISIBLE?|

+------------+------------+

|?i_idx??????|?YES????????|

|?j_idx??????|?NO?????????|

|?k_idx??????|?NO?????????|

+------------+------------+

3?rows?in?set?(0.00?sec)

注意：

主鍵索引是不能被設為不可見索引的！！！！！

一個沒有顯式主鍵的表可能仍然有一個有效的隱式主鍵，如果它在非空列上有任何惟一的索引。在這種情況下，第一個這樣的索引將同樣的約束放在表中行上，作為一個顯式的主鍵，而該索引不能被忽略。如下：

3、創建測試表：t2，并設置唯一索引：j_idx（這個表沒有明確主鍵，但是在NOT NULL列j上的索引在行上放置了相同的約束，可以作為主鍵）

1

2

3

4

5

6

7

8

9

mysql>?CREATE?TABLE?t2?(i?INT?NOT?NULL,j?INT?NOT?NULL,UNIQUE?j_idx?(j))?ENGINE?=?InnoDB;

Query?OK,?0?rows?affected?(0.12?sec)

mysql>?SELECT?INDEX_NAME,?IS_VISIBLE???FROM?INFORMATION_SCHEMA.STATISTICS???WHERE?TABLE_SCHEMA?=?'test'?AND?TABLE_NAME?=?'t2';

+------------+------------+

|?INDEX_NAME?|?IS_VISIBLE?|

+------------+------------+

|?j_idx??????|?YES????????|

+------------+------------+

1?row?in?set?(0.00?sec)

3.1、將索引設置為不可見索引：會發現報錯

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;

ERROR 3522 (HY000): A primary key index cannot be invisible

（報錯是因為：雖然這個表沒有明確的主鍵，但是在NOT NULL列j上的索引在行上放置了相同的約束，作為主鍵，不能被忽略：）

3.2、增加一個主鍵，如下：

1

2

3

4

5

6

7

8

9

mysql>?ALTER?TABLE?t2?ADD?PRIMARY?KEY?(i);???--增加一個主鍵

mysql>?SELECT?INDEX_NAME,?IS_VISIBLE???FROM?INFORMATION_SCHEMA.STATISTICS???WHERE?TABLE_SCHEMA?=?'test'?AND?TABLE_NAME?=?'t2';???--可以查看到有一個主鍵索引

+------------+------------+

|?INDEX_NAME?|?IS_VISIBLE?|

+------------+------------+

|?j_idx??????|?YES????????|

|?PRIMARY????|?YES????????|

+------------+------------+

2?rows?in?set?(0.00?sec)

3.3、這時候，將j_idx索引設置為不可見狀態是可以的，如：

1

2

3

4

5

6

7

8

9

mysql>?ALTER?TABLE?t2?ALTER?INDEX?j_idx?INVISIBLE;

mysql>?SELECT?INDEX_NAME,?IS_VISIBLE???FROM?INFORMATION_SCHEMA.STATISTICS???WHERE?TABLE_SCHEMA?=?'test'?AND?TABLE_NAME?=?'t2';

+------------+------------+

|?INDEX_NAME?|?IS_VISIBLE?|

+------------+------------+

|?j_idx??????|?NO?????????|

|?PRIMARY????|?YES????????|

+------------+------------+

2?rows?in?set?(0.00?sec)

從上面可以看出，已經存了顯示的主機，而j_idx唯一索引不再充當隱式主鍵，因此可以將其設置為不可見狀態。

4、日常操作：

--創建表指定索引為不可見狀態：

create table t1(id int primary key,name varchar(10),index idx_name (name) invisible);

--修改索引為可見狀態：

alter table t1 alter index idx_name visible;

--修改索引為不可見狀態：

alter table t1 alter index idx_name invisible;

--查看數據庫中有哪些不可見索引：

mysql> select table_schema,table_name,index_name,column_name,is_visible from information_schema.statistics where is_visible='no';

MySQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。