Java基础、中级、高级、架构面试资料

MySQL8中新增了三大索引,隐藏索引、降序索引、函数索引

SQL herman 2433浏览
公告:“业余草”微信公众号提供免费CSDN下载服务(只下Java资源),关注业余草微信公众号,添加作者微信:xttblog2,发送下载链接帮助你免费下载!
本博客日IP超过2000,PV 3000 左右,急需赞助商。
极客时间所有课程通过我的二维码购买后返现24元微信红包,请加博主新的微信号:xttblog2,之前的微信号好友位已满,备注:返现
受密码保护的文章请关注“业余草”公众号,回复关键字“0”获得密码
所有面试题(java、前端、数据库、springboot等)一网打尽,请关注文末小程序
视频教程免费领
腾讯云】1核2G5M轻量应用服务器50元首年,高性价比,助您轻松上云

今天我们一起来聊聊 MySQL 8.x 版本中新增的三大索引。MySQL 8.x 中新增了三种索引方式,如下所示。

  • 隐藏索引
  • 降序索引
  • 函数索引
MySQL8新特性

一、隐藏索引

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 索引。

业余草公众号

最后,欢迎关注我的个人微信公众号:业余草(yyucao)!可加作者微信号:xttblog2。备注:“1”,添加博主微信拉你进微信群。备注错误不会同意好友申请。再次感谢您的关注!后续有精彩内容会第一时间发给您!原创文章投稿请发送至532009913@qq.com邮箱。商务合作也可添加作者微信进行联系!

本文原文出处:业余草: » MySQL8中新增了三大索引,隐藏索引、降序索引、函数索引