快捷導(dǎo)航

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

更新時(shí)間：2022年11月08日 15:16:26 作者：JAVA旭陽(yáng)

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

前言

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

支持索引降序排序

降序索引說(shuō)明

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

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

ASC表示升序、DESC表示索引降序排序
從MySQL4版本中就開始支持DESC的語(yǔ)法，但是實(shí)際上會(huì)忽略它，指導(dǎo)MySQL8.x innoDB存儲(chǔ)引擎才支持降序排序。

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

在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;查看表的信息。

同樣，我們?cè)贛ySQL8中查看表的信息，如下圖：

降序索引好處

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

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

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

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

索引數(shù)據(jù)結(jié)構(gòu)詳細(xì)請(qǐng)參考：一步步帶你設(shè)計(jì)MySQL索引數(shù)據(jù)結(jié)構(gòu)。

支持索引的隱藏

隱藏索引的說(shuō)明

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

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

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

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

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

隱藏索引的好處

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

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

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