【Mysql8.0新特性 学习笔记】- 第二章 优化器索引

二、Mysql8.0带来的新的索引方式

2.1、隐藏索引

如上图所示的，隐藏索引的好处在于，我们可以类似像逻辑删除一样，将我们的需要删除的索引先隐藏掉，待确认没有问题的情况下再将该索引删除。方便了我们的维护。

2.1.1、创建一个测试库

2.1.2、查看已创建的索引

-- mysql中的\G表示让查询结果字段按行显示；
select index from t1\G

2.1.3、测试查询并展示我们所使用的索引

-- 使用explain可以展示执行的sql使用索引的情况
explian select * from t1 where i = 1;

下图中，由于我们在创建的时候给i字段加了一个索引，所以可以看到有提示到使用了i_idx。

查看我们设置的另一个隐藏索引“j_idx”；可以看到当我们使用j字段作为查询条件的时候，查询优化器并没有使用到索引，使用了全表扫描。

2.1.4、查看查询优化器的配置

2.1.5、在会话级别设置查询优化器的不可见索引为开启状态

-- 设置当前会话的查询优化器使用不可见索引
set session optimizer_switch ="use_invisible_indexes=on";
-- 查询查询优化器配置
select @@optimizer_switch\G

2.1.6、测试隐藏索引在查询的时候是否生效

-- 再次查询使用我们设置了隐藏索引的条件
explain select * from t1 where j=1

可以看到下图中所示，在查询的时候使用到了“j_idx”索引。

2.1.7、设置表中的索引显示

-- 设置t1表中的j_idx索引显示
alter table t1 alter index j_idx visible;

2.1.8、设置表中的索引隐藏

-- 设置t1表中的j_idx索引隐藏
alter table t1 alter index j_idx invisible;

2.1.9、不可为表中的主键设置隐藏索引

2.2、降序索引

2.2.1、在MySQL5.7中创建一张表，并设置索引的排序

-- 创建表t2，并未两个字段分别设置索引升序和降序；
create table t2(c1 int ,c2 int,index idx1(c1 asc,c2 desc));

-- 查询表t2的创建语句；
show create table t2\G;

从下图中我们可以看到在MySQL5.7中虽然指定了索引的排序，但是在创建表的语句中并没有设置。

2.2.2、在MySQL8.0中创建一张表，并设置索引的排序

从下图中可以看到，在MySQL8.0上真正创建了一个降序索引；

2.2.3、测试使用降序索引

-- 添加测试数据
insert into t2(c1,c2) values(1,100),(2,200),(3,150),(4,50);

2.2.3.1、在MySQL8.0上测试使用索引

-- 查看索引使用情况
explain select * from t2 order by c1,c2 desc;

如下图所示，可以看到该查询使用了索引；

2.2.3.2、在MySQL5.7上测试使用索引

-- 查看索引使用情况
explain select * from t2 order by c1,c2 desc;

从下图中可以看到，也使用了索引，并且使用了Using filesort；说明还是需要使用order by排序操作，不能直接通过索引排序；

2.2.3.3、在MySQL8.0上测试使用索引反查

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

如下图所示，查询优化器使用了索引反向扫描的方式；

2.2.4、在MySQL8.0中由于引入了降序索引，不在对group by操作默认降序

如果需要排序，需要自己指定

2.2.4.1、测试MySQL8.0下的group by之后排序方式

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

如下图所示，我们可以看到查询数据没有排序；

2.2.4.2、测试MySQL5.7下的group by之后排序方式

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

由下图可见，在MySQL5.7版本中的group by之后默认对c2字段进行了降序排序。

2.3、函数索引

2.3.1、创建测试数据

-- 创建测试表t3
create table t3(c1 varchar(10), c2 varchar(10));
-- 创建一个普通的索引
create index idx1 on t3(c1);
-- 创建一个函数索引
create index func_idx on t3( (OPPER(c2) );

2.3.2、查看创建的两个索引差别

-- 展示t3表的索引
show index from t3\G;

我们看到idx1索引的数据行中，有个字段未Expression，表示表达式为null；

在func_idx索引的数据行上，发现Expression的值为：upper(‘c2’)；为创建索引时设置的函数表达式；

2.3.3、测试两个索引的实际使用情况

-- 查看使用c1查询条件的索引使用情况
explain select * from t3 where upper(c1) = 'ABC';

如下表所示，当我们通过将c1字段的值转大写后作为查询条件的时候，没有使用到索引，而是全表扫描的方式。

-- 查看使用c2查询条件的索引使用情况
explain select * from t3 where upper(c1) = 'ABC';

从下图中，可以看到查询的时候使用了func_inx索引；

2.3.4、针对Json字段的索引

2.3.4.1、创建测试数据

-- 创建测试表emp
create table emp(data json, index((CAST(data->>'$.name' as char(30))));

2.3.4.2、查看创建的索引

-- 查看创建的索引
show index from emp\G

2.3.4.3、测试使用索引查询json数据字段

-- 测试使用索引查询json数据字段
explain select * from emp where CAST(data->>'$.name' as char(30)) = 'abc';

2.3.5、在MySQL5.7中模拟函数索引

-- 修改t3，增加c3计算列字段
alter table t3 add column c3 varchar(10) generated always as (upper(c1));
-- 添加测试数据
insert into t3(c1,c2) values('abc','abc');
-- 查询添加的测试数据
select * from t3;

-- 为c3字段创建索引
create index idx3 on t3(c3);

-- 测试
explain select * from t3 where upper(c1)='ABC';

从下图可以发现，虽然查询的时候使用了c1。但是添加了计算列c3，且c3的取值就是upper(c1)，并且为c3添加了索引。所以上面的sql其实又可以表现为

explain select * from t3 where c3 = 'ABC'