快捷導(dǎo)航

詳解MySQL 8.0 之不可見(jiàn)索引

更新時(shí)間：2020年10月27日 14:21:04 作者：用戶1278550

這篇文章主要介紹了MySQL 8.0 之不可見(jiàn)索引的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)新版本的MySQL，感興趣的朋友可以了解下

言

MySQL 8.0 從第一版release 到現(xiàn)在已經(jīng)走過(guò)了4個(gè)年頭了，8.0版本在功能和代碼上做了相當(dāng)大的改進(jìn)和重構(gòu)。和DBA圈子里的朋友交流，大部分還是5.6 ，5.7的版本，少量的走的比較靠前采用了MySQL 8.0。為了緊追數(shù)據(jù)庫(kù)發(fā)展的步伐，能夠盡早享受技術(shù)紅利，我們準(zhǔn)備將MySQL 8.0引入到有贊的數(shù)據(jù)庫(kù)體系。

落地之前我們會(huì)對(duì)MySQL 8.0的新特性和功能，配置參數(shù)，升級(jí)方式，兼容性等等做一系列的學(xué)習(xí)和測(cè)試。以后陸陸續(xù)續(xù)會(huì)發(fā)布文章出來(lái)。本文算是MySQL 8.0新特性學(xué)習(xí)的第一篇吧，聊聊不可見(jiàn)索引。

不可見(jiàn)索引

不可見(jiàn)索引中的不可見(jiàn)是針對(duì)優(yōu)化器而言的，優(yōu)化器在做執(zhí)行計(jì)劃分析的時(shí)候(默認(rèn)情況下)是會(huì)忽略設(shè)置了不可見(jiàn)屬性的索引。

為什么是默認(rèn)情況下，如果 optimizer_switch設(shè)置use_invisible_indexes=ON 是可以繼續(xù)使用不可見(jiàn)索引。

話不多說(shuō)，我們先測(cè)試幾個(gè)例子

如何設(shè)置不可見(jiàn)索引

我們可以通過(guò)帶上關(guān)鍵字VISIBLE|INVISIBLE的create table,create index,alter table 設(shè)置索引的可見(jiàn)性。

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)

不可見(jiàn)索引的作用

面對(duì)歷史遺留的一大堆索引，經(jīng)過(guò)數(shù)輪新老交替開(kāi)發(fā)和DBA估計(jì)都不敢直接將索引刪除，尤其是遇到比如大于100G的大表，直接刪除索引會(huì)提升數(shù)據(jù)庫(kù)的穩(wěn)定性風(fēng)險(xiǎn)。

有了不可見(jiàn)索引的特性，DBA可以一邊設(shè)置索引為不可見(jiàn)，一邊觀察數(shù)據(jù)庫(kù)的慢查詢記錄和thread running 狀態(tài)。如果數(shù)據(jù)庫(kù)長(zhǎng)時(shí)間沒(méi)有相關(guān)慢查詢，thread_running比較穩(wěn)定，就可以下線該索引。反之，則可以迅速將索引設(shè)置為可見(jiàn)，恢復(fù)業(yè)務(wù)訪問(wèn)。

Invisible Indexes 是 server 層的特性，和引擎無(wú)關(guān)，因此所有引擎（InnoDB, TokuDB, MyISAM, etc.）都可以使用。

設(shè)置完不可見(jiàn)索引，執(zhí)行計(jì)劃無(wú)法使用索引

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)

使用不可見(jiàn)索引的注意事項(xiàng)

The feature applies to indexes other than primary keys (either explicit or implicit).

不可見(jiàn)索引是針對(duì)非主鍵索引的。主鍵不能設(shè)置為不可見(jiàn)，這里的主鍵包括顯式的主鍵或者隱式主鍵(不存在主鍵時(shí)，被提升為主鍵的唯一索引) ，我們可以用下面的例子展示該規(guī)則。

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)

### 沒(méi)有主鍵的情況下，唯一鍵被當(dāng)做隱式主鍵，不能設(shè)置 不可見(jiàn)。
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) 不能訪問(wèn)不可見(jiàn)索引，否則報(bào)錯(cuò)。

mysql> select * from t2 force index(j_idx) where j=3;
ERROR 1176 (42000): Key 'j_idx' doesn't exist in table 't2'

設(shè)置索引為不可見(jiàn)需要獲取MDL鎖，遇到長(zhǎng)事務(wù)會(huì)引發(fā)數(shù)據(jù)庫(kù)抖動(dòng)

唯一索引被設(shè)置為不可見(jiàn)，不代表索引本身唯一性的約束失效

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'

小結(jié)

其實(shí)沒(méi)啥說(shuō)的，祝大家用的愉快。

-The End-

以上就是詳解MySQL 8.0 之不可見(jiàn)索引的詳細(xì)內(nèi)容，更多關(guān)于MySQL 8.0 不可見(jiàn)索引的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: