溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章將為大家詳細講解有關MySQL 8.0的不可見索引有哪些，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。
MySQL 8.0 從第一版release 到現在已經走過了4個年頭了，8.0版本在功能和代碼上做了相當大的改進和重構。和DBA圈子里的朋友交流，大部分還是5.6 ，5.7的版本，少量的走的比較靠前采用了MySQL 8.0。為了緊追數據庫發展的步伐，能夠盡早享受技術紅利，我們準備將MySQL 8.0引入到有贊的數據庫體系。
落地之前 我們會對MySQL 8.0的新特性和功能，配置參數，升級方式，兼容性等等做一系列的學習和測試。以后陸陸續續會發布文章出來。本文算是MySQL 8.0新特性學習的第一篇吧，聊聊 不可見索引。
不可見索引
不可見索引中的不可見是針對優化器而言的，優化器在做執行計劃分析的時候(默認情況下)是會忽略設置了不可見屬性的索引。
為什么是默認情況下，如果 optimizer_switch設置use_invisible_indexes=ON 是可以繼續使用不可見索引。
話不多說，我們先測試幾個例子
如何設置不可見索引
我們可以通過帶上關鍵字VISIBLE|INVISIBLE的create table,create index,alter table 設置索引的可見性。
mysql> create table t1 (i int, > j int, > k int, > index i_idx (i) invisible) engine=innodb; Query OK, 0 rows affected (0.41 sec) mysql> create index j_idx on t1 (j) invisible; Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add index k_idx (k) invisible; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select index_name,is_visible from information_schema.statistics where table_schema='test' and table_name='t1'; +------------+------------+ | INDEX_NAME | IS_VISIBLE | +------------+------------+ | i_idx | NO | | j_idx | NO | | k_idx | NO | +------------+------------+ 3 rows in set (0.01 sec) mysql> alter table t1 alter index i_idx visible; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select index_name,is_visible from information_schema.statistics where table_schema='test' and table_name='t1'; +------------+------------+ | INDEX_NAME | IS_VISIBLE | +------------+------------+ | i_idx | YES | | j_idx | NO | | k_idx | NO | +------------+------------+ 3 rows in set (0.00 sec)
不可見索引的作用
面對歷史遺留的一大堆索引，經過數輪新老交替開發和DBA估計都不敢直接將索引刪除，尤其是遇到比如大于100G的大表，直接刪除索引會提升數據庫的穩定性風險。
有了不可見索引的特性，DBA可以一邊設置索引為不可見，一邊觀察數據庫的慢查詢記錄和thread running 狀態。如果數據庫長時間沒有相關慢查詢 ，thread_running比較穩定，就可以下線該索引。反之，則可以迅速將索引設置為可見，恢復業務訪問。
Invisible Indexes 是 server 層的特性，和引擎無關，因此所有引擎（InnoDB, TokuDB, MyISAM, etc.）都可以使用。
設置完不可見索引，執行計劃無法使用索引
mysql> show create table t2 \G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`i` int NOT NULL AUTO_INCREMENT,
`j` int NOT NULL,
PRIMARY KEY (`i`),
UNIQUE KEY `j_idx` (`j`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
mysql> insert into t2(j) values(1),(2),(3),(4),(5),(6),(7);
Query OK, 7 rows affected (0.04 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> explain select * from t2 where j=3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7
filtered: 14.29
Extra: Using where
1 row in set, 1 warning (0.01 sec)
mysql> alter table t2 alter index j_idx visible;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from t2 where j=3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: const
possible_keys: j_idx
key: j_idx
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.01 sec)使用不可見索引的注意事項
The feature applies to indexes other than primary keys (either explicit or implicit).
不可見索引是針對非主鍵索引的。主鍵不能設置為不可見，這里的 主鍵 包括顯式的主鍵或者隱式主鍵(不存在主鍵時，被提升為主鍵的唯一索引) ，我們可以用下面的例子展示該規則。
mysql> create table t2 ( >i int not null, >j int not null , >unique j_idx (j) >) ENGINE = InnoDB; Query OK, 0 rows affected (0.16 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) ### 沒有主鍵的情況下，唯一鍵被當做隱式主鍵，不能設置 不可見。 mysql> alter table t2 alter index j_idx invisible; ERROR 3522 (HY000): A primary key index cannot be invisible mysql> mysql> alter table t2 add primary key (i); Query OK, 0 rows affected (0.44 sec) Records: 0 Duplicates: 0 Warnings: 0 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.01 sec) mysql> alter table t2 alter index j_idx invisible; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 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.01 sec)
force /ignore index(index_name) 不能訪問不可見索引，否則報錯。
mysql> select * from t2 force index(j_idx) where j=3; ERROR 1176 (42000): Key 'j_idx' doesn't exist in table 't2'
設置索引為不可見需要獲取MDL鎖，遇到長事務會引發數據庫抖動
唯一索引被設置為不可見，不代表索引本身唯一性的約束失效
mysql> select * from t2; +---+----+ | i | j | +---+----+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 6 | | 7 | 7 | | 8 | 11 | +---+----+ 8 rows in set (0.00 sec) mysql> insert into t2(j) values(11); ERROR 1062 (23000): Duplicate entry '11' for key 't2.j_idx'
關于MySQL 8.0的不可見索引有哪些就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
