MySQL8.0新特性-新的索引方式

三种新的索引方式

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

本文地址：MySQL8.0新特性-新的索引方式

推荐
MySQL8.0创建用户及其配置
 MySQL8.0新特性-新的索引方式
 MySQL8.0新特性-通用表表达式（CTE）
MySQL8.0新特性-窗口函数
 MySQL8.0新特性-InnoDB增强
 MySQL8.0新特性-JSON增强
[官方介绍](https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts

最后编辑于：2019.04.08 23:45:01