共計 13311 個字符，預計需要花費 34 分鐘才能閱讀完成。
本篇內容介紹了“MySQL 8.x 中新增了哪些索引方式”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
一、隱藏索引 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: t2Create Table: CREATE TABLE `t2` ( `c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
KEY `idx1` (`c1`,`c2`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 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: t2Create 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_ci1 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: indexpossible_keys: NULL
key: idx1
key_len: 10
ref: NULL
rows: 4
filtered: 100.00
Extra: Using index; Using filesort1 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: indexpossible_keys: NULL
key: idx1
key_len: 10
ref: NULL
rows: 4
filtered: 100.00
Extra: Using index1 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: indexpossible_keys: NULL
key: idx1
key_len: 10
ref: NULL
rows: 4
filtered: 100.00
Extra: Backward index scan; Using index1 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: ALLpossible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where1 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: NULL1 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: 0c3 列是一個計算列，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: NULL1 row in set, 1 warning (0.00 sec)此時，就使用了 idx3 索引。
“MySQL 8.x 中新增了哪些索引方式”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！
