mysql8.0新特性--隱藏索引

我們有時候想刪除掉冗餘索引，但是又怕刪除之後影響到查詢效能，這時候再回退就需要一定的時間。MySQL8.0開始支援隱藏索引（invisible indexes），隱藏索引不會被最佳化器使用，如果你想驗證某個索引刪除之後的查詢效能影響，就可以暫時先隱藏該索引。但是有一點主鍵不能被設定為隱藏索引，當表中沒有顯式主鍵時，表中第一個唯一非空索引會成為隱式主鍵，也不能設定為隱藏索引。

索引預設是可見的，在使用CREATE TABLE,，CREATE INDEX或者ALTER TABLE等語句時可以透過VISIBLE或者INVISIBLE關鍵詞設定索引的可見性。

建立新表時指定隱藏索引

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `id_card` int NOT NULL,
  `name` varchar(10) NOT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_id_card` (`id_card`) /*!80000 INVISIBLE */
) ENGINE=InnoDB

我匯入了4萬條資料，透過以下執行計劃可以看到，最佳化器並沒有使用索引，而是使用的全表掃描。

mysql> explain select * from student where id_card=100;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 39231 |     0.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

alter修改隱藏索引為可見。

alter table student alter index idx_id_card visible;

再次檢視執行計劃，這次最佳化器選擇了idx_id_card索引。

mysql> explain select * from student where id_card=100;
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_id_card   | idx_id_card | 4       | const |    4 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

alter修改普通索引為隱藏索引。

alter table student alter index idx_id_card invisible;

查詢某個表中的索引是否為隱藏索引

mysql> SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'student';
+-------------+------------+
| INDEX_NAME  | IS_VISIBLE |
+-------------+------------+
| idx_id_card | NO         |
| PRIMARY     | YES        |
+-------------+------------+
2 rows in set (0.00 sec)

系統變數optimizer_switch中的use_invisible_indexes控制了最佳化器在構建執行計劃時是否使用隱藏索引，如果設定為off(預設)最佳化器會忽略隱藏索引。如果設定為on，即使隱藏索引不可見，最佳化器在生成執行計劃時仍會考慮使用隱藏索引。

參考了MySQL官方文件《refman-8.0-en.a4》。

About Me

........................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在個人微 信公眾號（
DB寶）上有同步更新

● QQ群號：
230161599
、618766405，微信群私聊

● 個人QQ號（646634621），微 訊號（db_bao），註明新增緣由

● 於 2020年11月完成

● 最新修改時間：2020年11月

● 版權所有，歡迎分享本文，轉載請保留出處

........................................................................................................................

● 
小麥苗的微店：

● 小麥苗出版的資料庫類叢書：
http://blog.itpub.net/26736162/viewspace-2142121/

● 
小麥苗OCP、OCM、高可用、DBA學習班：
http://blog.itpub.net/26736162/viewspace-2148098/

● 資料庫筆試面試題庫及解答：
http://blog.itpub.net/26736162/viewspace-2134706/ 

........................................................................................................................
請掃描下面的二維碼來關注小麥苗的微 信公眾號（
DB寶）及QQ群（230161599、618766405）、新增小麥苗微 信（db_bao），
學習最實用的資料庫技術。 


........................................................................................................................

mysql8.0新特性--隱藏索引

mysql8.0新特性--隱藏索引

相關文章