今天我们一起来聊聊 MySQL 8.x 版本中新增的三大索引。MySQL 8.x 中新增了三种索引方式，如下所示。
- 隐藏索引
- 降序索引
- 函数索引
一、隐藏索引
1.隐藏索引概述
- MySQL 8.0 开始支持隐藏索引（invisible index），不可见索引。
- 隐藏索引不会被优化器使用，但仍然需要进行维护。
- 应用场景：软删除、灰度发布。
在之前 MySQL 的版本中，只能通过显式的方式删除索引，如果删除后发现索引删错了，又只能通过创建索引的方式将删除的索引添加回来，如果数据库中的数据量非常大，或者表比较大，这种操作的成本非常高。在 MySQL 8.0 中，只需要将这个索引先设置为隐藏索引，使查询优化器不再使用这个索引，但是，此时这个索引还是需要 MySQL 后台进行维护，当确认将这个索引设置为隐藏索引系统不会受到影响时，再将索引彻底删除。这就是软删除功能。 灰度发布，就是说创建索引时，首先将索引设置为隐藏索引，通过修改查询优化器的开关，使隐藏索引对查询优化器可见，通过 explain 对索引进行测试，确认这个索引有效，某些查询可以使用到这个索引，就可以将其设置为可见索引，完成灰度发布的效果。
2.隐藏索引操作
（1）登录 MySQL，创建 testdb 数据库，并在数据库中创建一张测试表 t1
mysql> create database if not exists testdb;
Query OK, 1 row affected (0.58 sec)
mysql> use testdb;
Database changed
mysql> create table if not exists t1(i int, j int);
Query OK, 0 rows affected (0.05 sec) （2）在字段 i 上创建索引，如下所示。
mysql> create index i_idx on t1(i);
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0 （3）在字段 j 上创建隐藏索引，创建隐藏索引时，只需要在创建索引的语句后面加上 invisible 关键字，如下所示
mysql> create index j_idx on t1(j) invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 （4）查看 t1 表中的索引情况，如下所示
mysql> show index from t1 /G
*************************** 1. row ***************************
Table: t1
Non_unique: 1
Key_name: i_idx
Seq_in_index: 1
Column_name: i
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: t1
Non_unique: 1
Key_name: j_idx
Seq_in_index: 1
Column_name: j
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: NO
Expression: NULL
2 rows in set (0.02 sec) 可以看到 t1 表中有两个索引，一个是 i_idx，一个是 j_idx，i_idx 的 Visible 属性为 YES，表示这个索引可见； j_idx 的 Visibles 属性为 NO，表示这个索引不可见。
（5）查看查询优化器对这两个索引的使用情况。 首先，使用字段i进行查询，如下所示。
mysql> explain select * from t1 where i = 1 /G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: i_idx
key: i_idx
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.02 sec)
可以看到，查询优化器会使用i字段的索引进行优化。
接下来，使用字段j进行查询，如下所示。
mysql> explain select * from t1 where j = 1 /G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec) 可以看到，查询优化器并没有使用 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=on
1 row in set (0.00 sec) 这里，可以看到如下一个属性值：
use_invisible_indexes=off 表示优化器是否使用不可见索引，默认为 off 不使用。 接下来，在 MySQL 的会话级别使查询优化器使用不可见索引，如下所示。
mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec) 接下来，再次查看查询优化器的开关设置，如下所示
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=on
1 row in set (0.00 sec) 此时，可以看到 use_invisible_indexes=on，说明隐藏索引对查询优化器可见了。
再次分析使用 t1 表的 j 字段查询数据，如下所示。
mysql> explain select * from t1 where j = 1 /G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: j_idx
key: j_idx
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec) 可以看到，此时查询优化器使用j字段上的隐藏索引来优化查询了。
（7）设置索引的可见与不可见 将字段 j 上的隐藏索引设置为可见，如下所示。
mysql> alter table t1 alter index j_idx visible;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 将字段 j 上的索引设置为不可见，如下所示。
mysql> alter table t1 alter index j_idx invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 （8）MySQL 中主键不能设置为不可见索引 值得注意的是：在 MySQL 中，主键是不可以设置为不可见的。 在 testdb 数据库中创建一张测试表 t2，如下所示。
mysql> create table t2(i int not null);
Query OK, 0 rows affected (0.01 sec) 接下来，在 t2 表中创建一个不可见主键，如下所示
mysql> alter table t2 add primary key pk_t2(i) invisible;
ERROR 3522 (HY000): A primary key index cannot be invisible 可以看到，此时 SQL 语句报错，主键不能被设置为不可见索引。
二、降序索引
1.降序索引概述
- MySQL 8.0 开始真正支持降序索引（descending index）。
- 只有 InnoDB 存储引擎支持降序索引，只支持BTREE降序索引。
- MySQL 8.0 不再对 GROUP BY 操作进行隐式排序
2.降序索引操作
（1）MySQL 5.7 中支持的语法 首先，在 MySQL 5.7 中创建测试数据库 testdb，在数据库 testdb 中创建测试表 t2，如下所示。
mysql> create database if not exists testdb;
Query OK, 0 rows affected (0.71 sec)
mysql> use testdb;
Database changed
mysql> create table if not exists t2(c1 int, c2 int, index idx1(c1 asc, c2 desc));
Query OK, 0 rows affected (0.71 sec) 其中，在 t2 表中创建了名为 idx1 的索引，索引中 c1 字段升序排序，c2 字段降序排序。
接下来，查看 t2 表的创建信息，如下所示
mysql> show create table t2 /G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
KEY `idx1` (`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.16 sec) 可以看到，MySQL 5.7 版本在创建表的信息中，没有字段 c1 和 c2 的排序信息，默认都是升序。
（2）MySQL 8.0 中支持的语法 在 MySQL 8.x 中同样创建 t2 表，如下所示
mysql> create table if not exists t2(c1 int, c2 int, index idx1(c1 asc, c2 desc));
Query OK, 0 rows affected, 1 warning (0.00 sec) 接下来，查看 t2 表的创建信息，如下所示
mysql> show create table t2 /G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
KEY `idx1` (`c1`,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec) 可以看到，在 MySQL 8.x 中，创建的索引中存在字段的排序信息。
（3）MySQL 5.7 中查询优化器对索引的使用情况 首先，在表 t2 中插入一些数据，如下所示。
mysql> insert into t2(c1, c2) values(1, 100), (2, 200), (3, 150), (4, 50);
Query OK, 4 rows affected (0.19 sec)
Records: 4 Duplicates: 0 Warnings: 0 接下来，查询 t2 表中的数据，如下所示。
mysql> select * from t2;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 100 |
| 2 | 200 |
| 3 | 150 |
| 4 | 50 |
+------+------+
4 rows in set (0.00 sec) 可以看到，t2 表中的数据插入成功。
接下来，查看查询优化器对索引的使用情况，这里，查询语句按照 c1 字段升序，按照 c2 字段降序，如下所示。
mysql> explain select * from t2 order by c1, c2 desc /G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: index
possible_keys: NULL
key: idx1
key_len: 10
ref: NULL
rows: 4
filtered: 100.00
Extra: Using index; Using filesort
1 row in set, 1 warning (0.12 sec) 可以看到，在 MySQL 5.7 中，按照 c2 字段进行降序排序，并没有使用索引。
（4）MySQL 8.x 中查询优化器对降序索引的使用情况。 查看查询优化器对降序索引的使用情况。 首先，在表 t2 中插入一些数据，如下所示。
mysql> insert into t2(c1, c2) values(1, 100), (2, 200), (3, 150), (4, 50);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0 接下来，查询 t2 表中的数据，如下所示。
mysql> select * from t2;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 100 |
| 2 | 200 |
| 3 | 150 |
| 4 | 50 |
+------+------+
4 rows in set (0.00 sec) 可以看到，t2 表中的数据插入成功。
在 MySQL 中如果创建的是升序索引，则指定查询的时候，只能按照升序索引的方式指定查询，这样才能使用升序索引。
接下来，查看查询优化器对索引的使用情况，这里，查询语句按照 c1 字段升序，按照 c2 字段降序，如下所示。
mysql> explain select * from t2 order by c1, c2 desc /G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: index
possible_keys: NULL
key: idx1
key_len: 10
ref: NULL
rows: 4
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec) 可以看到，在 MySQL 8.x 中，按照 c2 字段进行降序排序，使用了索引。
使用 c1 字段降序，c2 字段升序排序，如下所示。
mysql> explain select * from t2 order by c1 desc, c2 /G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: index
possible_keys: NULL
key: idx1
key_len: 10
ref: NULL
rows: 4
filtered: 100.00
Extra: Backward index scan; Using index
1 row in set, 1 warning (0.00 sec) 可以看到，在 MySQL 8.x 中仍然可以使用索引，并使用了索引的反向扫描。
（5）MySQL 8.x 中不再对 GROUP BY 进行隐式排序
在 MySQL 5.7 中执行如下命令，按照 c2 字段进行分组，查询每组中数据的记录条数。
mysql> select count(*), c2 from t2 group by c2;
+----------+------+
| count(*) | c2 |
+----------+------+
| 1 | 50 |
| 1 | 100 |
| 1 | 150 |
| 1 | 200 |
+----------+------+
4 rows in set (0.18 sec) 可以看到，在 MySQL 5.7 中，在 c2 字段上进行了排序操作。
在 MySQL 8.x 中执行如下命令，按照 c2 字段进行分组，查询每组中数据的记录条数。
mysql> select count(*), c2 from t2 group by c2;
+----------+------+
| count(*) | c2 |
+----------+------+
| 1 | 100 |
| 1 | 200 |
| 1 | 150 |
| 1 | 50 |
+----------+------+
4 rows in set (0.00 sec) 可以看到，在 MySQL 8.x 中，在 c2 字段上并没有进行排序操作。
在 MySQL 8.x 中如果需要对 c2 字段进行排序，则需要使用 order by 语句明确指定排序规则，如下所示。
mysql> select count(*), c2 from t2 group by c2 order by c2;
+----------+------+
| count(*) | c2 |
+----------+------+
| 1 | 50 |
| 1 | 100 |
| 1 | 150 |
| 1 | 200 |
+----------+------+
4 rows in set (0.00 sec) 三、函数索引
1.函数索引概述
- MySQL 8.0.13 开始支持在索引中使用函数（表达式）的值。
- 支持降序索引，支持 JSON 数据的索引
- 函数索引基于虚拟列功能实现
2.函数索引操作
（1）创建测试表 t3 在 testdb 数据库中创建一张测试表 t3，如下所示。
mysql> create table if not exists t3(c1 varchar(10), c2 varchar(10));
Query OK, 0 rows affected (0.01 sec) （2）创建普通索引，在 c1 字段上创建普通索引
mysql> create index idx1 on t3(c1);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 （3）创建函数索引 在 c2 字段上创建一个将字段值转化为大写的函数索引，如下所示。
mysql> create index func_index on t3 ((UPPER(c2)));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0 （4）查看 t3 表上的索引信息，如下所示。
mysql> show index from t3 /G
*************************** 1. row ***************************
Table: t3
Non_unique: 1
Key_name: idx1
Seq_in_index: 1
Column_name: c1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: t3
Non_unique: 1
Key_name: func_index
Seq_in_index: 1
Column_name: NULL
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: upper(`c2`)
2 rows in set (0.01 sec) （5）查看查询优化器对两个索引的使用情况。首先，查看 c1 字段的大写值是否等于某个特定的值，如下所示。
mysql> explain select * from t3 where upper(c1) = 'ABC' /G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t3
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec) 可以看到，没有使用索引，进行了全表扫描操作。
接下来，查看 c2 字段的大写值是否等于某个特定的值，如下所示。
mysql> explain select * from t3 where upper(c2) = 'ABC' /G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t3
partitions: NULL
type: ref
possible_keys: func_index
key: func_index
key_len: 43
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec) 可以看到，使用了函数索引。
（6）函数索引对 JSON 数据的索引 首先，创建测试表 emp，并对 JSON 数据进行索引，如下所示。
mysql> create table if not exists emp(data json, index((CAST(data->>'$.name' as char(30)))));
Query OK, 0 rows affected (0.02 sec) 上述 SQL 语句的解释如下：
- JSON 数据长度不固定，如果直接对 JSON 数据进行索引，可能会超出索引长度，通常，会只截取 JSON 数据的一部分进行索引。
- CAST() 类型转换函数，把数据转化为 char(30) 类型。使用方式为 CAST(数据 as 数据类型)。
- data ->> ‘$.name’ 表示 JSON 的运算符
简单的理解为，就是取 name 节点的值，将其转化为 char(30) 类型。
接下来，查看 emp 表中的索引情况，如下所示。
mysql> show index from emp /G
*************************** 1. row ***************************
Table: emp
Non_unique: 1
Key_name: functional_index
Seq_in_index: 1
Column_name: NULL
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: cast(json_unquote(json_extract(`data`,_utf8mb4/'$.name/')) as char(30) charset utf8mb4)
1 row in set (0.00 sec) （7）函数索引基于虚拟列实现 首先，查看 t3 表的信息，如下所示。
mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | varchar(10) | YES | MUL | NULL | |
| c2 | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
在 c1 上建立了普通索引，在 c2 上建立了函数索引。
接下来，在 t3 表中添加一列 c3，模拟 c2 上的函数索引，如下所示。
mysql> alter table t3 add column c3 varchar(10) generated always as (upper(c1));
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0 c3 列是一个计算列，c3 字段的值总是使用 c1 字段转化为大写的结果。
接下来，向 t3 表中插入一条数据，其中，c3 列是一个计算列，c3 字段的值总是使用 c1 字段转化为大写的结果，在插入数据的时候，不需要为 c3 列插入数据，如下所示。
mysql> insert into t3(c1, c2) values ('abc', 'def');
Query OK, 1 row affected (0.00 sec)
查询 t3 表中的数据，如下所示。
mysql> select * from t3;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| abc | def | ABC |
+------+------+------+
1 row in set (0.00 sec) 可以看到，并不需要向 c3 列中插入数据，c3 列的数据为 c1 字段的大写结果数据。
如果想模拟函数索引的效果，则可以使用如下方式。首先，在 c3 列上添加索引，如下所示。
mysql> create index idx3 on t3(c3);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0 接下来，再次查看 c1 字段的大写值是否等于某个特定的值，如下所示。
mysql> explain select * from t3 where upper(c1) = 'ABC' /G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t3
partitions: NULL
type: ref
possible_keys: idx3
key: idx3
key_len: 43
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec) 此时，就使用了 idx3 索引。
： » MySQL8中新增了三大索引，隐藏索引、降序索引、函数索引
原创文章，作者：carmelaweatherly，如若转载，请注明出处：https://blog.ytso.com/252338.html