MySQL8.0特性之不可見索引講析

本文主要給大家介紹MySQL8.0特性之不可見索引講析，希望可以給大家補充和更新些知識，如有其它問題需要了解的可以持續在億速云行業資訊里面關注我的更新文章的。

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

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

注意：

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

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

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

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、修改索引為可見狀態，也就是可使用的狀態

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上的索引在行上放置了相同的約束，可以作為主鍵）

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、增加一個主鍵，如下：

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索引設置為不可見狀態是可以的，如：

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

看了以上關于MySQL8.0特性之不可見索引講析，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，如有需要更加專業的解答，可在官網聯系我們的24小時售前售后，隨時幫您解答問題的。