MySQL8.0?索引優(yōu)化invisible?index詳情

更新時間：2022年09月27日 08:45:45 作者：阿常囈語

這篇文章主要介紹了MySQL8.0?索引優(yōu)化invisible?index詳情，文章圍繞主題展開詳細的內容介紹，具有一定的參考價值，需要的小伙伴可以參考一下

前言

MySQL8.0 開始支持不可見索引。優(yōu)化器根本不使用不可見索引，但會以其他的方式正常維護。

默認情況下索引是可見的。通過不可見索引，可以方便數(shù)據(jù)庫管理人員檢查索引對查詢性能的影響，而不會進行破壞性的更改。

應用場景： 軟刪除，灰度發(fā)布

-- 創(chuàng)建測試表
mysql> create table t1(i int ,j int);
Query OK, 0 rows affected (0.03 sec)



-- 創(chuàng)建索引 
mysql>
mysql> create index idx_i on t1(i);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> -- 創(chuàng)建 一個隱藏索引
mysql> create index idx_j on t1(j) invisible ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0



-- 查看索引
mysql> show index from t1\G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: idx_i
 Seq_in_index: 1
  Column_name: i
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: idx_j
 Seq_in_index: 1
  Column_name: j
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: NO
   Expression: NULL
2 rows in set (0.00 sec)

上面對 i,j 分別創(chuàng)建了索引，其中j 為不可見索引，即默認優(yōu)化器不會使用到該索引的。

索引使用情況測試

mysql> explain  select * from t1 where i= 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_i
          key: idx_i
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> explain  select * from t1 where j= 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

如何臨時讓優(yōu)化器可以看到這個索引呢？

mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on
1 row in set (0.00 sec)

-- 設置開關
mysql> set session optimizer_switch='use_invisible_indexes=on';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=on,skip_scan=on
1 row in set (0.00 sec)

-- 此時可以看到 優(yōu)化器已經(jīng)可以看到這個索引啦
mysql> explain  select * from t1 where j= 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_j
          key: idx_j
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

修改索引的可見性

mysql> ALTER TABLE t1 ALTER INDEX idx_j INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ALTER INDEX idx_j  VISIBLE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意: 主鍵索引是不能設置不可見的。

create  table t2(`id` int not null auto_increment, primary key (`id`));


mysql> show index from t2\G
*************************** 1. row ***************************
        Table: t2
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.01 sec)

mysql> alter table t2 alter index PRIMARY key(`id`) invisible;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY invisible' at line 1

測試添加主鍵為不可見索引:

mysql> create  table t3(id int not null);
Query OK, 0 rows affected (0.03 sec)

mysql> alter table t3 add primary key pk_t3(id)  invisible;
ERROR 3522 (HY000): A primary key index cannot be invisible

primary key 是必須可見的，不能設置為不可見索引。

在生產(chǎn)環(huán)境中也可以利用隱藏索引進行 SQL 語句的性能測試，或者對索引進行邏輯刪除，索引的灰度發(fā)布測試，以及軟刪除索引等。

到此這篇關于MySQL8.0 索引優(yōu)化invisible index詳情的文章就介紹到這了,更多相關MySQL索引優(yōu)化內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: