MySQL8.0新特性(六)索引增强

一、隐藏索引

1.1 概述

MySQL 8.0开始支持隐藏索引 (invisible index)，不可见索引。

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

语法：

-- 创建隐藏索引
CREATE INDEX idx_invisible ON table_name(col) INVISIBLE;
 
-- 修改索引可见性
ALTER TABLE table_name ALTER INDEX idx_name VISIBLE|INVISIBLE;

1.2 应用场景

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

• 软删除：就是我们在线上会经常删除和创建索引，如果是以前的版本，我们如果删除了索引，后面发现删错了，我又需要创建一个索引，这样做的话就非常影响性能。在MySQL8中我们可以这么操作，把一个索引变成隐藏索引（索引就不可用了，查询优化器也用不上），最后确定要进行删除这个索引我们才会进行删除索引操作。
• 灰度发布：也是类似的，我们想在线上进行一些测试，可以先创建一个隐藏索引，不会影响当前的生产环境，然后我们通过一些附加的测试，发现这个索引没问题，那么就直接把这个索引改成正式的索引，让线上环境生效。

1.3 使用案例

使用案例（灰度发布）：

create table t1(i int, j int);  --创建一张t1表
create index i_idx on t1(i);  --创建一个正常索引
create index j_idx on t1(j) invisible;  --创建一个隐藏索引

show index from t1\G        --查看索引信息

使用查询优化器看下：

explain select * from t1 where i=1;
explain select * from t1 where j=1;

这里可以看到隐藏索引不会用上。

这里可以通过优化器的开关，打开一个设置，方便我们对隐藏索引进行设置。

select @@optimizer_switch\G;   --查看 各种参数

红色的部分就是默认查询优化器对隐藏索引不可见，我们可以通过参数进行修改。确保我们可以用隐藏索引进行测试。

set session optimizer_switch="use_invisible_indexes=on';   --在会话级别设置查询优化器可以看到隐藏索引

再使用查询优化器看下：

explain select * from t1 where j=1;

把隐藏索引变成可见索引（正常索引）

alter table t1 alter index j_idx visible;   --变成可见
alter table t1 alter index j_idx invisible;   --变成不可见(隐藏索引)

最后一点，不能把主键设置成不可见的索引（隐藏索引）（MySQL做了限制）

二、降序索引

2.1 概述

MySQL 8.0开始真正支持降序索引 (descendingindex) 。只有InnoDB存储引擎支持降序索引，只支持BTREE降序索引。另外MySQL8.0不再对GROUP BY操作进行隐式排序。

CREATE INDEX idx_desc ON orders(order_date DESC, customer_id ASC);

2.2 案例

在MySQL中创建一个t2表

create table t2(c1 int,c2 int,index idx1(c1 asc,c2 desc));
show create table t2\G

如果是5.7中，则没有显示升序还是降序信息

我们插入一些数据，给大家演示下降序索引的使用

insert into t2(c1,c2) values(1,100),(2,200),(3,150),(4,50);

看下索引使用情况

explain select * from t2 order by c1,c2 desc;

我们在5.7对比一下

这里说明，这里需要一个额外的排序操作，才能把刚才的索引利用上。

我们把查询语句换一下

explain select * from t2 order by c1 desc,c2 ;

MySQL8中使用了

另外还有一点，就是group by语句在8之后不再默认排序

select count(*),c2 from t2 group by c2;

在8要排序的话，就需要手动把排序语句加上

select count(*),c2 from t2 group by c2 order by c2;

三、函数索引

3.1 概述

之前我们知道，如果在查询中加入了函数，索引不生效，所以MySQL8引入了函数索引。

MySQL 8.0.13开始支持在索引中使用函数（表达式)的值。支持降序索引，支持JSON数据的索引。函数索引基于虚拟列功能实现。

语法：

-- 基于函数创建索引
CREATE INDEX idx_name_upper ON employees((UPPER(last_name)));
 
-- 使用虚拟列创建索引
ALTER TABLE employees ADD COLUMN name_upper VARCHAR(255) AS (UPPER(last_name));
CREATE INDEX idx_name_upper ON employees(name_upper)

3.2 使用案例

使用函数索引（表达式）

create table t3(c1 varchar(10),c2 varchar(10));
create index idx_c1 on t3(c1);   --普通索引
create index func_idx on t3( (UPPER(c2)) );   --一个大写的函数索引

show index from t3\G

explain select * from t3 where upper(c1)='ABC' ;
explain select * from t3 where upper(c2)='ABC' ;

使用函数索引（JSON）

create table t4(data json，index((CAST(data->>'$.name' as char(25)) )));
explain select * from t4 where CAST(data->>'$.name' as char(25)) = 'lijin ';

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

函数索引在MySQL中相当于新增了一个列，这个列会根据你的函数来进行计算结果，然后使用函数索引的时候就会用这个计算后的列作为索引。