mysql8添加索引

三种新的索引方式

1、隐藏索引

MySQL8.0 支持隐藏索引(invisible index)，不可见索引

隐藏索引不会被优化器使用，但需要维护。

应用场景：软删除、灰度发布。

软删除：不确定当前索引是否需要删除的时候，软删除，不会彻底删除，可以恢复索引，不需要重新创建，但需要维护。

灰度发布：测试索引，对当前数据库不会参生太多影响，确认有效后，可以取消隐藏，改变为正常索引。

操作：

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;

-- 有一个参数Visible为NO

show index from app_user;

-- 查询优化器对索引的使用情况

-- 会使用索引

explain select * from app_user where age=18;

-- 不会使用索引

explain select * from app_user where pkid=1;

-- 查询优化器的隐藏索引的开关

select @@optimizer_switch\G

-- 查询优化器使用隐藏索引，在当前会话中

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);

-- 查询优化器使用索引的情况，会发现使用当前索引，但不用额外的排序(using filesort)操作

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

-- 反顺序查询，只会出现反向索引扫描(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、函数索引

MySQL8.0支持在索引中使用函数(表达式)的值。

支持降序索引，支持JSON数据索引。

函数索引基于虚拟列功能实现。

create table t1(

c1 varchar(100),

c2 varchar(100)

);

create index idx1 on t1(c1);

-- 创建函数索引

create index fun_idx2 on t1(UPPER(c1);

show index from t1\G

-- 当使用函数时候，就不会走当前普通索引

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

-- 走当前函数索引

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

-- 添加一个 计算列,并未该列实现索引，虚拟列实现函数索引，

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

-- 创建JSON数据索引测试，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';