詳解MySQL 8.0 之不可見索引
言
MySQL 8.0 從第一版release 到現(xiàn)在已經(jīng)走過了4個年頭了,8.0版本在功能和代碼上做了相當(dāng)大的改進(jìn)和重構(gòu)。和DBA圈子里的朋友交流,大部分還是5.6 ,5.7的版本,少量的走的比較靠前采用了MySQL 8.0。為了緊追數(shù)據(jù)庫發(fā)展的步伐,能夠盡早享受技術(shù)紅利,我們準(zhǔn)備將MySQL 8.0引入到有贊的數(shù)據(jù)庫體系。
落地之前 我們會對MySQL 8.0的新特性和功能,配置參數(shù),升級方式,兼容性等等做一系列的學(xué)習(xí)和測試。以后陸陸續(xù)續(xù)會發(fā)布文章出來。本文算是MySQL 8.0新特性學(xué)習(xí)的第一篇吧,聊聊 不可見索引。
不可見索引
不可見索引中的不可見是針對優(yōu)化器而言的,優(yōu)化器在做執(zhí)行計劃分析的時候(默認(rèn)情況下)是會忽略設(shè)置了不可見屬性的索引。
為什么是默認(rèn)情況下,如果 optimizer_switch設(shè)置use_invisible_indexes=ON 是可以繼續(xù)使用不可見索引。
話不多說,我們先測試幾個例子
如何設(shè)置不可見索引
我們可以通過帶上關(guān)鍵字VISIBLE|INVISIBLE的create table,create index,alter table 設(shè)置索引的可見性。
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)
不可見索引的作用
面對歷史遺留的一大堆索引,經(jīng)過數(shù)輪新老交替開發(fā)和DBA估計都不敢直接將索引刪除,尤其是遇到比如大于100G的大表,直接刪除索引會提升數(shù)據(jù)庫的穩(wěn)定性風(fēng)險。
有了不可見索引的特性,DBA可以一邊設(shè)置索引為不可見,一邊觀察數(shù)據(jù)庫的慢查詢記錄和thread running 狀態(tài)。如果數(shù)據(jù)庫長時間沒有相關(guān)慢查詢 ,thread_running比較穩(wěn)定,就可以下線該索引。反之,則可以迅速將索引設(shè)置為可見,恢復(fù)業(yè)務(wù)訪問。
Invisible Indexes 是 server 層的特性,和引擎無關(guān),因此所有引擎(InnoDB, TokuDB, MyISAM, etc.)都可以使用。
設(shè)置完不可見索引,執(zhí)行計劃無法使用索引
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).
不可見索引是針對非主鍵索引的。主鍵不能設(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) ### 沒有主鍵的情況下,唯一鍵被當(dāng)做隱式主鍵,不能設(shè)置 不可見。 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'
設(shè)置索引為不可見需要獲取MDL鎖,遇到長事務(wù)會引發(fā)數(shù)據(jù)庫抖動
唯一索引被設(shè)置為不可見,不代表索引本身唯一性的約束失效
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é)
其實沒啥說的,祝大家用的愉快。
-The End-
以上就是詳解MySQL 8.0 之不可見索引的詳細(xì)內(nèi)容,更多關(guān)于MySQL 8.0 不可見索引的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
在Centos7中利用Shell腳本實現(xiàn)MySQL數(shù)據(jù)備份
備份是容災(zāi)的基礎(chǔ),是指為防止系統(tǒng)出現(xiàn)操作失誤或系統(tǒng)故障導(dǎo)致數(shù)據(jù)丟失,而將全部或部分?jǐn)?shù)據(jù)集合從應(yīng)用主機的硬盤或陣列復(fù)制到其它的存儲介質(zhì)的過程,本文將給大家介紹了在Centos7中利用Shell腳本實現(xiàn)MySQL數(shù)據(jù)備份,文中有詳細(xì)的圖文介紹,需要的朋友可以參考下2023-12-12Mysql使用存儲過程快速添加百萬數(shù)據(jù)的示例代碼
這篇文章主要介紹了Mysql使用存儲過程快速添加百萬數(shù)據(jù),本文通過實例代碼給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2021-08-08解決Windows10下mysql5.5數(shù)據(jù)庫命令行中文亂碼問題
重置系統(tǒng)后,很久之前安裝的MySQL數(shù)據(jù)庫出現(xiàn)了控制臺查詢中文亂碼問題,時間太久早已經(jīng)不記得怎么設(shè)置了。下面通過本文給大家分享Windows10下解決MySQL5.5數(shù)據(jù)庫命令行中文亂碼問題,一起看看吧2017-07-07mysql 8.0.15 版本安裝教程 連接Navicat.list
這篇文章主要為大家詳細(xì)介紹了mysql 8.0.15 版本安裝教程,連接Navicat.list,具有一定的參考價值,感興趣的小伙伴們可以參考一下2019-08-08MySQL中無GROUP BY情況下直接使用HAVING語句的問題探究
這篇文章主要介紹了MySQL中無GROUP BY情況下直接使用HAVING語句的問題探究,同時探究了該情況下MAX與MIN功能的使用情況,需要的朋友可以參考下2015-05-05阿里云服務(wù)器手動實現(xiàn)mysql雙機熱備的兩種方式
阿里云服務(wù)器由于不支持keepalive虛擬ip,導(dǎo)致無法通過keepalive來實現(xiàn)mysql的雙機熱備。我們這里要實現(xiàn)阿里云的雙機熱備有兩種方式。感興趣的朋友跟隨小編一起看看吧2019-10-10