MySQL8.0中你必須要知道的索引新特性

更新時間：2022年11月08日 15:16:26 作者：JAVA旭陽

MySQL8.0索引新特性,支持降序索引,降序索引以降序存儲鍵值,下面這篇文章主要給大家介紹了關于MySQL8.0中你必須要知道的索引新特性的相關資料,需要的朋友可以參考下

前言

MySQL中的索引可以為提高我們的查詢效率，相比較于低版本, MySQL 8.0中針對索引做了不少的優(yōu)化，本文主要分享下MySQL8.0中關于索引的兩個新特性，這兩個新特性都非常好用，希望大家可以在日常的開發(fā)中根據(jù)實際場景用起來。

支持索引降序排序

降序索引說明

在建立的索引可以指定索引的順序，命令如下：

ALTER TABLE table_name ADD [UNIQUE] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]

ASC表示升序、DESC表示索引降序排序
從MySQL4版本中就開始支持DESC的語法，但是實際上會忽略它，指導MySQL8.x innoDB存儲引擎才支持降序排序。

例子：在MySQL5.x中創(chuàng)建DESC降序索引無效

在MySQL5.7.16中創(chuàng)建降序索引

create table student (
    id_ int(10) AUTO_INCREMENT,
    student_no varchar(50),
    student_name varchar(200),
    update_time datetime,
    primary key (id_),
    unique index idx_no(student_no desc)
)

創(chuàng)建了降序索引idx_no

我們用show create table student;查看表的信息。

同樣，我們在MySQL8中查看表的信息，如下圖：

降序索引好處

你可能疑惑了，那我為什么要設置索引為降序，有什么好處呢？

其實這和我們實際的業(yè)務場景息息相關，比如我們的場景中如果存在一個查詢，需要對多個列進行排序，且順序要求不一致，那么使用降序索引將會避免數(shù)據(jù)庫使用額外的文件排序操作，從而提高性能。如果只對單個列進行排序，降序索引的意義不是太大，無論是升序還是降序，升序索引完全可以應付。詳細內(nèi)容請參考文章：http://www.dbjr.com.cn/article/143464.htm。

那么降序索引的底層數(shù)據(jù)結(jié)構是什么樣的呢？下圖是基于student表建立的降序索引對應的B+樹結(jié)構：

頁10和頁12是對應的數(shù)據(jù)頁，存放數(shù)據(jù)，其中update_time字段對應的值是降序排序，這也是DESC的體現(xiàn)。
頁20是目錄頁，存放索引，它也是降序排序的。

索引數(shù)據(jù)結(jié)構詳細請參考：一步步帶你設計MySQL索引數(shù)據(jù)結(jié)構。

支持索引的隱藏

隱藏索引的說明

MySQL 8.0 支持了 Invisible Indexes 隱藏索引這個特性，可以把某個索引設置為對優(yōu)化器不可見，生成查詢計劃時便不使用這個索引了，但這個索引還是被正常維護的，例如表數(shù)據(jù)變更后還是會更新索引。

我們可以通過在創(chuàng)建索引的時候設置索引的隱藏屬性，如下：

ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;

也可以對已經(jīng)存在的索引切換顯示或者隱藏，語法如下：

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切換成隱藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切換成非隱藏索引

隱藏索引的好處

眾所周知，索引的維護是需要極大的成本的，數(shù)據(jù)量越大，建立索引花費的成本也就越大。但是，往往我們在調(diào)優(yōu)的時候有這樣的需求，我們想要看看禁用掉這個索引對查詢性能的影響，如果在8.0版本以前，都是要刪除這個索引，發(fā)現(xiàn)這個索引有用的，又要加回來，極大的增加了操作成本。

所以隱藏索引最明顯的一個作用類似索引回收站。

例如數(shù)據(jù)庫長時間運行后，會積累很多索引，做數(shù)據(jù)庫優(yōu)化時，想清理掉沒什么用的多余的索引，但可能刪除某個索引后，數(shù)據(jù)庫性能下降了，發(fā)現(xiàn)這個索引時有用的，就要重新建立。這時候隱藏索引就派上用場了。