MySQL 8.0新增的三种索引方式

MySQL 8.x中新增了三种索引方式：隐藏索引、降序索引 及 函数索引。

1. 隐藏索引

隐藏索引又叫不可见索引，主要应用于索引的 软删除 和 灰度发布。

在之前MySQL的版本中，只能通过显式的方式删除索引，如果删除后发现索引删错了，又只能通过创建索引的方式将删除的索引添加回来，如果数据库中的数据量非常大，或者表比较大，这种操作的成本非常高。在MySQL 8.0中，只需要将这个索引先设置为隐藏索引，使查询优化器不再使用这个索引，但是，此时这个索引还是需要MySQL后台进行维护，当确认将这个索引设置为隐藏索引系统不会受到影响时，再将索引彻底删除。这就是索引软删除功能。 

灰度发布，就是说创建索引时，首先将索引设置为隐藏索引，通过修改查询优化器的开关，使隐藏索引对查询优化器可见，通过explain对索引进行测试，确认这个索引有效，某些查询可以使用到这个索引，就可以将其设置为可见索引，完成灰度发布的效果。

创建隐藏索引具体代码如下（只需要在创建索引的后面加上invisible即可）：

create index 索引名称 on 表名称('索引字段名') invisible;

此时创建的索引就是隐藏索引，我们可以查看当前表中索引列表：

show index from 表名称 \G;

\G：是格式化的意思

显示的索引中有一个Visible属性，YES表示这个索引可见，NO表示不可见。接下来我们在使用explain分析，发现刚刚创建的隐藏索引不生效。在灰度发布的场景中我们需要同过explain分析隐藏索引是否对某些SQL有帮助，但是隐藏索引又不生效，怎么办呢？

在MySQL8 中提供了一种新的测试方式，可以通过优化器的一个开关来打开某个设置，使隐藏索引对查询优化器可见。我们可以通过如下代码查看这个开关是否开启：

select @@optimizer_switch \G;

查询结果如下所示：

use_invisible_indexes=off

这个开关默认是off，off表示关闭，ON表示开启。

我们可以通过如下SQL在当前会话中开启和关闭（不影响其他会话进程）：

set session optimizer_switch="use_invisible_indexes=on";

现在我们可以通过explain分析隐藏索引是否对某些SQL有帮助。

注意：主键不能设置隐藏索引哦

2. 降序索引

MySQL 8.0开始真正支持降序索引（descending index），并且只有InnoDB存储引擎支持降序索引，只支持BTREE降序索引。MySQL 8.0不再对GROUP BY操作进行隐式排序。

说实话，我个人绝对这个没有什么实际应用场景，一般我们查询数据都需要where条件，故而一般都会优先优化where条件字段的索引问题。不过我们还是来说说具体操作吧！

create table if not exists test(
c1 int, 
c2 int, 
index c1_c2(c1 asc, c2 desc)
);

如上所示，创建表的时候创建了索引 c1_c2 ，并且在索引中指定了c1是升序，c2是降序。

当我们使用如下SQL查询时：

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

在MySQL5.7中按照c2字段进行降序排序，并没有使用索引。但是在MySQL8中使用索引，并使用了索引的反向扫描。

我们再来看看下面这条SQL：

explain select * from test order by c1, c2 asc;

此时索引就不起作用了，是不是感觉有点鸡肋。如果我们使用了where条件查询，MySQL优化器会使用where条件中的字段优化查询，因此我感觉这个降序索引没有什么作用。

3. 函数索引

在前的版本中，我们的SQL中使用了某个函数（例如：upper，json相关函数），那么就不会走索引查询，为此MySQL8新增了一个函数索引，以此解决函数不走索引的问题。

3.1 创建函数索引，下面以转大写函数为例：

create index 索引名称 on 表名称( ( UPPER( 字段名 ) ) );

3.2 测试函数索引

explain select * from test where upper(c1) = 'ABC'

test是测试表，并且在c1字段上添加了小写转大写的函数索引

分析结果表明：当前SQL使用了函数索引。

3.3 再来看看对json的支持

创建测试表，如下所示：

create table if not exists test(
  data json, 
  index( (CAST(data->>'$.name' as char(30) ) ) )
);

JSON数据长度不固定，如果直接对JSON数据进行索引，可能会超出索引长度，通常，会只截取JSON数据的一部分进行索引。

CAST()类型转换函数，把数据转化为char(30)类型。使用方式为CAST(数据 as 数据类型)。

data ->> '$.name'表示JSON的运算符

简单的理解为，就是取name节点的值，将其转化为char(30)类型