本篇内容介绍了“MySQL 8.x中新增了哪些索引方式”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

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

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

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

在之前MySQL的版本中，只能通过显式的方式删除索引，如果删除后发现索引删错了，又只能通过创建索引的方式将删除的索引添加回来，如果数据库中的数据量非常大，或者表比较大，这种操作的成本非常高。

在MySQL 8.0中，只需要将这个索引先设置为隐藏索引，使查询优化器不再使用这个索引，但是，此时这个索引还是需要MySQL后台进行维护，当确认将这个索引设置为隐藏索引系统不会受到影响时，再将索引彻底删除。这就是软删除功能。

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

（1）登录MySQL，创建testdb数据库，并在数据库中创建一张测试表t1

mysql>createdatabaseifnotexiststestdb;QueryOK,1rowaffected(0.58sec)mysql>usetestdb;Databasechangedmysql>createtableifnotexistst1(iint,jint);QueryOK,0rowsaffected(0.05sec)

（2）在字段i上创建索引，如下所示。

mysql>createindexi_idxont1(i);QueryOK,0rowsaffected(0.34sec)Records:0Duplicates:0Warnings:0

（3）在字段j上创建隐藏索引，创建隐藏索引时，只需要在创建索引的语句后面加上invisible关键字，如下所示

mysql>createindexj_idxont1(j)invisible;QueryOK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:0

（4）查看t1表中的索引情况，如下所示

mysql>showindexfromt1\G***************************1.row***************************Table:t1Non_unique:1Key_name:i_idxSeq_in_index:1Column_name:iCollation:ACardinality:0Sub_part:NULLPacked:NULLNull:YESIndex_type:BTREEComment:Index_comment:Visible:YESExpression:NULL***************************2.row***************************Table:t1Non_unique:1Key_name:j_idxSeq_in_index:1Column_name:jCollation:ACardinality:0Sub_part:NULLPacked:NULLNull:YESIndex_type:BTREEComment:Index_comment:Visible:NOExpression:NULL2rowsinset(0.02sec)

可以看到t1表中有两个索引，一个是i_idx，一个是j_idx，i_idx的Visible属性为YES，表示这个索引可见； j_idx的Visibles属性为NO,表示这个索引不可见。

（5）查看查询优化器对这两个索引的使用情况。
首先，使用字段i进行查询，如下所示。

mysql>explainselect*fromt1wherei=1\G***************************1.row***************************id:1select_type:SIMPLEtable:t1partitions:NULLtype:refpossible_keys:i_idxkey:i_idxkey_len:5ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.02sec)可以看到，查询优化器会使用i字段的索引进行优化。接下来，使用字段j进行查询，如下所示。mysql>explainselect*fromt1wherej=1\G***************************1.row***************************id:1select_type:SIMPLEtable:t1partitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:1filtered:100.00Extra:Usingwhere1rowinset,1warning(0.00sec)

可以看到，查询优化器并没有使用j字段上的隐藏索引，会使用全表扫描的方式查询数据。

（6）使隐藏索引对优化器可见
在MySQL 8.x 中提供了一种新的测试方式，可以通过优化器的一个开关来打开某个设置，使隐藏索引对查询优化器可见。
查看查询优化器的开关，如下所示。

mysql>select@@optimizer_switch\G***************************1.row***************************@@optimizer_switch:index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on1rowinset(0.00sec)

这里，可以看到如下一个属性值：

use_invisible_indexes=off

表示优化器是否使用不可见索引，默认为off不使用。
接下来，在MySQL的会话级别使查询优化器使用不可见索引，如下所示。

mysql>setsessionoptimizer_switch="use_invisible_indexes=on";QueryOK,0rowsaffected(0.00sec)

接下来，再次查看查询优化器的开关设置，如下所示

mysql>select@@optimizer_switch\G***************************1.row***************************@@optimizer_switch:index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=on,skip_scan=on,hash_join=on1rowinset(0.00sec)

此时，可以看到use_invisible_indexes=on，说明隐藏索引对查询优化器可见了。

再次分析使用t1表的j字段查询数据，如下所示。

mysql>explainselect*fromt1wherej=1\G***************************1.row***************************id:1select_type:SIMPLEtable:t1partitions:NULLtype:refpossible_keys:j_idxkey:j_idxkey_len:5ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)

可以看到，此时查询优化器使用j字段上的隐藏索引来优化查询了。

（7）设置索引的可见与不可见
将字段j上的隐藏索引设置为可见，如下所示。

mysql>altertablet1alterindexj_idxvisible;QueryOK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:0

将字段j上的索引设置为不可见，如下所示。

mysql>altertablet1alterindexj_idxinvisible;QueryOK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:0

（8）MySQL中主键不能设置为不可见索引
值得注意的是：在MySQL中，主键是不可以设置为不可见的。
在testdb数据库中创建一张测试表t2，如下所示。

mysql>createtablet2(iintnotnull);QueryOK,0rowsaffected(0.01sec)

接下来，在t2表中创建一个不可见主键，如下所示

mysql>altertablet2addprimarykeypk_t2(i)invisible;ERROR3522(HY000):Aprimarykeyindexcannotbeinvisible

可以看到，此时SQL语句报错，主键不能被设置为不可见索引。

MySQL 8.0开始真正支持降序索引（descending index）。

只有InnoDB存储引擎支持降序索引，只支持BTREE降序索引。

MySQL 8.0不再对GROUP BY操作进行隐式排序

（1）MySQL 5.7中支持的语法
首先，在MySQL 5.7中创建测试数据库testdb，在数据库testdb中创建测试表t2,如下所示。

mysql>createdatabaseifnotexiststestdb;QueryOK,0rowsaffected(0.71sec)mysql>usetestdb;Databasechangedmysql>createtableifnotexistst2(c1int,c2int,indexidx1(c1asc,c2desc));QueryOK,0rowsaffected(0.71sec)

其中，在t2表中创建了名为idx1的索引，索引中c1字段升序排序，c2字段降序排序。

接下来，查看t2表的创建信息，如下所示

mysql>showcreatetablet2\G***************************1.row***************************Table:t2CreateTable:CREATETABLE`t2`(`c1`int(11)DEFAULTNULL,`c2`int(11)DEFAULTNULL,KEY`idx1`(`c1`,`c2`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb41rowinset(0.16sec)

可以看到，MySQL 5.7版本在创建表的信息中，没有字段c1和c2的排序信息，默认都是升序。

（2）MySQL 8.0中支持的语法
在MySQL 8.x中同样创建t2表，如下所示

mysql>createtableifnotexistst2(c1int,c2int,indexidx1(c1asc,c2desc));QueryOK,0rowsaffected,1warning(0.00sec)

接下来，查看t2表的创建信息，如下所示

mysql>showcreatetablet2\G***************************1.row***************************Table:t2CreateTable:CREATETABLE`t2`(`c1`int(11)DEFAULTNULL,`c2`int(11)DEFAULTNULL,KEY`idx1`(`c1`,`c2`DESC))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci1rowinset(0.00sec)

可以看到，在MySQL 8.x中，创建的索引中存在字段的排序信息。

（3）MySQL 5.7中查询优化器对索引的使用情况
首先，在表t2中插入一些数据，如下所示。

mysql>insertintot2(c1,c2)values(1,100),(2,200),(3,150),(4,50);QueryOK,4rowsaffected(0.19sec)Records:4Duplicates:0Warnings:0

接下来，查询t2表中的数据，如下所示。

mysql>select*fromt2;+------+------+|c1|c2|+------+------+|1|100||2|200||3|150||4|50|+------+------+4rowsinset(0.00sec)

可以看到，t2表中的数据插入成功。

接下来，查看查询优化器对索引的使用情况，这里，查询语句按照c1字段升序，按照c2字段降序，如下所示。

mysql>explainselect*fromt2orderbyc1,c2desc\G***************************1.row***************************id:1select_type:SIMPLEtable:t2partitions:NULLtype:indexpossible_keys:NULLkey:idx1key_len:10ref:NULLrows:4filtered:100.00Extra:Usingindex;Usingfilesort1rowinset,1warning(0.12sec)

可以看到，在MySQL 5.7中，按照c2字段进行降序排序，并没有使用索引。

（4）MySQL 8.x中查询优化器对降序索引的使用情况。
查看查询优化器对降序索引的使用情况。
首先，在表t2中插入一些数据，如下所示。

mysql>insertintot2(c1,c2)values(1,100),(2,200),(3,150),(4,50);QueryOK,4rowsaffected(0.00sec)Records:4Duplicates:0Warnings:0

接下来，查询t2表中的数据，如下所示。

mysql>select*fromt2;+------+------+|c1|c2|+------+------+|1|100||2|200||3|150||4|50|+------+------+4rowsinset(0.00sec)

可以看到，t2表中的数据插入成功。

在MySQL中如果创建的是升序索引，则指定查询的时候，只能按照升序索引的方式指定查询，这样才能使用升序索引。

接下来，查看查询优化器对索引的使用情况，这里，查询语句按照c1字段升序，按照c2字段降序，如下所示。

mysql>explainselect*fromt2orderbyc1,c2desc\G***************************1.row***************************id:1select_type:SIMPLEtable:t2partitions:NULLtype:indexpossible_keys:NULLkey:idx1key_len:10ref:NULLrows:4filtered:100.00Extra:Usingindex1rowinset,1warning(0.00sec)

可以看到，在MySQL 8.x中，按照c2字段进行降序排序，使用了索引。

使用c1字段降序，c2字段升序排序，如下所示。

mysql>explainselect*fromt2orderbyc1desc,c2\G***************************1.row***************************id:1select_type:SIMPLEtable:t2partitions:NULLtype:indexpossible_keys:NULLkey:idx1key_len:10ref:NULLrows:4filtered:100.00Extra:Backwardindexscan;Usingindex1rowinset,1warning(0.00sec)

可以看到，在MySQL 8.x中仍然可以使用索引，并使用了索引的反向扫描。

（5）MySQL 8.x中不再对GROUP BY进行隐式排序

在MySQL 5.7中执行如下命令，按照c2字段进行分组，查询每组中数据的记录条数。

mysql>selectcount(*),c2fromt2groupbyc2;+----------+------+|count(*)|c2|+----------+------+|1|50||1|100||1|150||1|200|+----------+------+4rowsinset(0.18sec)

可以看到，在MySQL 5.7中，在c2字段上进行了排序操作。

在MySQL 8.x中执行如下命令，按照c2字段进行分组，查询每组中数据的记录条数。

mysql>selectcount(*),c2fromt2groupbyc2;+----------+------+|count(*)|c2|+----------+------+|1|100||1|200||1|150||1|50|+----------+------+4rowsinset(0.00sec)

可以看到，在MySQL 8.x中，在c2字段上并没有进行排序操作。

在MySQL 8.x中如果需要对c2字段进行排序，则需要使用order by语句明确指定排序规则，如下所示。

mysql>selectcount(*),c2fromt2groupbyc2orderbyc2;+----------+------+|count(*)|c2|+----------+------+|1|50||1|100||1|150||1|200|+----------+------+4rowsinset(0.00sec)三、函数索引1.函数索引概述

MySQL 8.0.13开始支持在索引中使用函数（表达式）的值。

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

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

（1）创建测试表t3
在testdb数据库中创建一张测试表t3，如下所示。

mysql>createtableifnotexistst3(c1varchar(10),c2varchar(10));QueryOK,0rowsaffected(0.01sec)

（2）创建普通索引
在c1字段上创建普通索引

mysql>createindexidx1ont3(c1);QueryOK,0rowsaffected(0.01sec)Records:0Duplicates:0Warnings:0

（3）创建函数索引
在c2字段上创建一个将字段值转化为大写的函数索引，如下所示。

mysql>createindexfunc_indexont3((UPPER(c2)));QueryOK,0rowsaffected(0.02sec)Records:0Duplicates:0Warnings:0

（4）查看t3表上的索引信息，如下所示。

mysql>showindexfromt3\G***************************1.row***************************Table:t3Non_unique:1Key_name:idx1Seq_in_index:1Column_name:c1Collation:ACardinality:0Sub_part:NULLPacked:NULLNull:YESIndex_type:BTREEComment:Index_comment:Visible:YESExpression:NULL***************************2.row***************************Table:t3Non_unique:1Key_name:func_indexSeq_in_index:1Column_name:NULLCollation:ACardinality:0Sub_part:NULLPacked:NULLNull:YESIndex_type:BTREEComment:Index_comment:Visible:YESExpression:upper(`c2`)2rowsinset(0.01sec)

（5）查看查询优化器对两个索引的使用情况
首先，查看c1字段的大写值是否等于某个特定的值，如下所示。

mysql>explainselect*fromt3whereupper(c1)='ABC'\G***************************1.row***************************id:1select_type:SIMPLEtable:t3partitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:1filtered:100.00Extra:Usingwhere1rowinset,1warning(0.00sec)

可以看到，没有使用索引，进行了全表扫描操作。

接下来，查看c2字段的大写值是否等于某个特定的值，如下所示。

mysql>explainselect*fromt3whereupper(c2)='ABC'\G***************************1.row***************************id:1select_type:SIMPLEtable:t3partitions:NULLtype:refpossible_keys:func_indexkey:func_indexkey_len:43ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)

可以看到，使用了函数索引。

（6）函数索引对JSON数据的索引
首先，创建测试表emp，并对JSON数据进行索引，如下所示。

mysql>createtableifnotexistsemp(datajson,index((CAST(data->>'$.name'aschar(30)))));QueryOK,0rowsaffected(0.02sec)

上述SQL语句的解释如下：

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

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

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

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

接下来，查看emp表中的索引情况，如下所示。

mysql>showindexfromemp\G***************************1.row***************************Table:empNon_unique:1Key_name:functional_indexSeq_in_index:1Column_name:NULLCollation:ACardinality:0Sub_part:NULLPacked:NULLNull:YESIndex_type:BTREEComment:Index_comment:Visible:YESExpression:cast(json_unquote(json_extract(`data`,_utf8mb4\'$.name\'))aschar(30)charsetutf8mb4)1rowinset(0.00sec)

（7）函数索引基于虚拟列实现
首先，查看t3表的信息，如下所示。

mysql>desct3;+-------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+-------------+------+-----+---------+-------+|c1|varchar(10)|YES|MUL|NULL|||c2|varchar(10)|YES||NULL||+-------+-------------+------+-----+---------+-------+2rowsinset(0.00sec)

在c1上建立了普通索引，在c2上建立了函数索引。

接下来，在t3表中添加一列c3，模拟c2上的函数索引，如下所示。

mysql>altertablet3addcolumnc3varchar(10)generatedalwaysas(upper(c1));QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0

c3列是一个计算列，c3字段的值总是使用c1字段转化为大写的结果。

接下来，向t3表中插入一条数据，其中，c3列是一个计算列，c3字段的值总是使用c1字段转化为大写的结果，在插入数据的时候，不需要为c3列插入数据，如下所示。

mysql>insertintot3(c1,c2)values('abc','def');QueryOK,1rowaffected(0.00sec)

查询t3表中的数据，如下所示。

mysql>select*fromt3;+------+------+------+|c1|c2|c3|+------+------+------+|abc|def|ABC|+------+------+------+1rowinset(0.00sec)

可以看到，并不需要向c3列中插入数据，c3列的数据为c1字段的大写结果数据。

如果想模拟函数索引的效果，则可以使用如下方式。
首先，在c3列上添加索引，如下所示。

mysql>createindexidx3ont3(c3);QueryOK,0rowsaffected(0.11sec)Records:0Duplicates:0Warnings:0

接下来，再次查看c1字段的大写值是否等于某个特定的值，如下所示。

mysql>explainselect*fromt3whereupper(c1)='ABC'\G***************************1.row***************************id:1select_type:SIMPLEtable:t3partitions:NULLtype:refpossible_keys:idx3key:idx3key_len:43ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)

此时，就使用了idx3索引。

“MySQL 8.x中新增了哪些索引方式”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！