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)

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中查詢優化器對降序索引的使用情況。

檢視查詢優化器對降序索引的使用情況。

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

在MySQL中如果建立的是升序索引，則指定查詢的時候，隻能按照升序索引的方式指定查詢，這樣才能使用升序索引。

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> 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)

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

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

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)

c3列是一個計算列，c3字段的值總是使用c1字段轉化為大寫的結果。

接下來，向t3表中插入一條資料，其中，c3列是一個計算列，c3字段的值總是使用c1字段轉化為大寫的結果，在插入資料的時候，不需要為c3列插入資料，如下所示。

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

查詢t3表中的資料，如下所示。

mysql> insert into t3(c1, c2) values ('abc', 'def');
Query OK, 1 row affected (0.00 sec)

可以看到，并不需要向c3列中插入資料，c3列的資料為c1字段的大寫結果資料。

如果想模拟函數索引的效果，則可以使用如下方式。

首先，在c3列上添加索引，如下所示。

mysql> select * from t3;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
| abc  | def  | ABC  |
+------+------+------+
1 row in set (0.00 sec)

接下來，再次檢視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索引。

你知道MySQL 8.0中的索引有哪些新特性嗎？看這一篇就夠了！！！三、函數索引

三、函數索引

1.函數索引概述

2.函數索引操作

繼續閱讀

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

vue-cli簡介（中文翻譯）

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

Ajax發送和擷取json資料到Spring mvc 1.spring mvc後端2.web前段

SequoiaDB巨杉資料庫C++驅動概述

JSONObject包導入異常 java.lang.NoClassDefFoundErrorweb項目的導入包的問題