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

lhrbest發表於2020-11-05

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月

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

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

      小麥苗的微店https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

      ● 小麥苗出版的資料庫類叢書: 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), 學習最實用的資料庫技術。

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

       

       



      來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26736162/viewspace-2732471/,如需轉載,請註明出處,否則將追究法律責任。

      相關文章