三種新的索引方式

1、隱藏索引

MySQL8.0 支持隱藏索引(invisible index)，不可見索引

隱藏索引不會被優(yōu)化器使用，但需要維護。

應用場景：軟刪除、灰度發(fā)布。

軟刪除：不確定當前索引是否需要刪除的時候，軟刪除，不會徹底刪除，可以恢復索引，不需要重新創(chuàng)建，但需要維護。

灰度發(fā)布：測試索引，對當前數(shù)據(jù)庫不會參生太多影響，確認有效后，可以取消隱藏，改變?yōu)檎Ｋ饕?/p>

操作：

create table app_user (

pkid int,

age int

);

-- 正常索引

create index age_idx on app_user(age) ;

-- 隱藏索引 ，主鍵不可以設置尾隱藏索引

create index id_idx on app_user(pkid) invisible;

-- 有一個參數(shù)Visible為NO

show index from app_user;

-- 查詢優(yōu)化器對索引的使用情況

-- 會使用索引

explain select * from app_user where age=18;

-- 不會使用索引

explain select * from app_user where pkid=1;

-- 查詢優(yōu)化器的隱藏索引的開關

select @@optimizer_switch\G

-- 查詢優(yōu)化器使用隱藏索引，在當前會話中

set session optimizer_switch="use_invisible_indexes=on";

-- 打開之后可以使用索引

explain select * from app_user where pkid=1;

-- 設置索引可見

alter table app_user index id_idx visiblle;

-- 設置索引隱藏

alter table app_user index id_idx invisiblle;

2、降序索引

MySQL8.0真正支持降序索引(descending index)。

只有InnoDB存儲引擎支持降序索引，只支持BTREE降序索引。

MySQL8.0不再對GROUP BY操作進行隱式排序，也就是說，排序必須要使用ORDER BY。

操作：

create table app_dept

(

pkid int,

num int,

cou int,

index idx1(num asc,cou desc)

);

-- 在5.7中是沒有desc的，只有8.0才會有desc

show cteate table app_dept\G

insert into app_dept values(1,1,300),(2,6,500),(5,1,256),(3,4,400);

-- 查詢優(yōu)化器使用索引的情況，會發(fā)現(xiàn)使用當前索引，但不用額外的排序(using filesort)操作

explain select * from app_dept order by num,cou desc;

-- 反順序查詢，只會出現(xiàn)反向索引掃描(backward index scan)，不會重新排序

explain select * from app_dept order by num desc,cou ;

-- GROUP BY 沒有默認排序

select count(*) ,cou from app_dept group by cou;

3、函數(shù)索引

MySQL8.0支持在索引中使用函數(shù)(表達式)的值。

支持降序索引，支持JSON數(shù)據(jù)索引。

函數(shù)索引基于虛擬列功能實現(xiàn)。

create table t1(

c1 varchar(100),

c2 varchar(100)

);

create index idx1 on t1(c1);

-- 創(chuàng)建函數(shù)索引

create index fun_idx2 on t1(UPPER(c1);

show index from t1\G

-- 當使用函數(shù)時候，就不會走當前普通索引

explain select * from t1 where upper(c1)='A';

-- 走當前函數(shù)索引

explain select * from t1 where upper(c2)='A';

-- 添加一個 計算列,并未該列實現(xiàn)索引，虛擬列實現(xiàn)函數(shù)索引，

alter table t1 add column c3 varchar(100) generated always as (upper(c1));

-- 創(chuàng)建JSON數(shù)據(jù)索引測試，data->>'$.name' as char(30) 意思是取當前name值，類型尾char

create table emp(

data json,

index((CAST(data->>'$.name' as char(30))))

);

show index from emp\G

-- 當前就會使用JSON索引

explain select * from emp where CAST(data->>'$.name' as char(30))='A';

總結

以上是生活随笔為你收集整理的mysql8添加索引_MySQL8.0新特性-新的索引方式的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。