詳細解析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: 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: 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: NULL1 row in set, 1 warning (0.00 sec)此時，就使用了idx3索引。
推薦學習：
以上就是詳細解析MySQL 8.x中新增了三種索引方式（總結分享）的詳細內容，更多請關注TW511.COM其它相關文章！