本篇文章給大家?guī)砹薓ySQL 8.x版本中新增的三大索引的相關(guān)知識(shí)。MySQL 8.x中新增了三種索引方式隱藏索引、降序索引和函數(shù)索引，希望對(duì)大家有幫助。

一、隱藏索引

1.隱藏索引概述

• MySQL 8.0開始支持隱藏索引（invisible index），不可見索引。
• 隱藏索引不會(huì)被優(yōu)化器使用，但仍然需要進(jìn)行維護(hù)。
• 應(yīng)用場(chǎng)景：軟刪除、灰度發(fā)布。

在之前MySQL的版本中，只能通過顯式的方式刪除索引，如果刪除后發(fā)現(xiàn)索引刪錯(cuò)了，又只能通過創(chuàng)建索引的方式將刪除的索引添加回來，如果數(shù)據(jù)庫中的數(shù)據(jù)量非常大，或者表比較大，這種操作的成本非常高。

在MySQL 8.0中，只需要將這個(gè)索引先設(shè)置為隱藏索引，使查詢優(yōu)化器不再使用這個(gè)索引，但是，此時(shí)這個(gè)索引還是需要MySQL后臺(tái)進(jìn)行維護(hù)，當(dāng)確認(rèn)將這個(gè)索引設(shè)置為隱藏索引系統(tǒng)不會(huì)受到影響時(shí)，再將索引徹底刪除。這就是軟刪除功能。

灰度發(fā)布，就是說創(chuàng)建索引時(shí)，首先將索引設(shè)置為隱藏索引，通過修改查詢優(yōu)化器的開關(guān)，使隱藏索引對(duì)查詢優(yōu)化器可見，通過explain對(duì)索引進(jìn)行測(cè)試，確認(rèn)這個(gè)索引有效，某些查詢可以使用到這個(gè)索引，就可以將其設(shè)置為可見索引，完成灰度發(fā)布的效果。

2.隱藏索引操作

（1）登錄MySQL，創(chuàng)建testdb數(shù)據(jù)庫，并在數(shù)據(jù)庫中創(chuàng)建一張測(cè)試表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上創(chuàng)建索引，如下所示。

mysql> create index i_idx on t1(i); Query OK, 0 rows affected (0.34 sec) Records: 0  Duplicates: 0  Warnings: 0

（3）在字段j上創(chuàng)建隱藏索引，創(chuàng)建隱藏索引時(shí)，只需要在創(chuàng)建索引的語句后面加上invisible關(guān)鍵字，如下所示

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表中有兩個(gè)索引，一個(gè)是i_idx，一個(gè)是j_idx，i_idx的Visible屬性為YES，表示這個(gè)索引可見； j_idx的Visibles屬性為NO,表示這個(gè)索引不可見。

（5）查看查詢優(yōu)化器對(duì)這兩個(gè)索引的使用情況。
首先，使用字段i進(jìn)行查詢，如下所示。

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) 可以看到，查詢優(yōu)化器會(huì)使用i字段的索引進(jìn)行優(yōu)化。 接下來，使用字段j進(jìn)行查詢，如下所示。 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)

可以看到，查詢優(yōu)化器并沒有使用j字段上的隱藏索引，會(huì)使用全表掃描的方式查詢數(shù)據(jù)。

（6）使隱藏索引對(duì)優(yōu)化器可見
在MySQL 8.x 中提供了一種新的測(cè)試方式，可以通過優(yōu)化器的一個(gè)開關(guān)來打開某個(gè)設(shè)置，使隱藏索引對(duì)查詢優(yōu)化器可見。
查看查詢優(yōu)化器的開關(guān)，如下所示。

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)

這里，可以看到如下一個(gè)屬性值：

use_invisible_indexes=off

表示優(yōu)化器是否使用不可見索引，默認(rèn)為off不使用。
接下來，在MySQL的會(huì)話級(jí)別使查詢優(yōu)化器使用不可見索引，如下所示。

mysql> set session optimizer_switch="use_invisible_indexes=on"; Query OK, 0 rows affected (0.00 sec)

接下來，再次查看查詢優(yōu)化器的開關(guān)設(shè)置，如下所示

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)

此時(shí)，可以看到use_invisible_indexes=on，說明隱藏索引對(duì)查詢優(yōu)化器可見了。

再次分析使用t1表的j字段查詢數(shù)據(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)

可以看到，此時(shí)查詢優(yōu)化器使用j字段上的隱藏索引來優(yōu)化查詢了。

（7）設(shè)置索引的可見與不可見
將字段j上的隱藏索引設(shè)置為可見，如下所示。

mysql> alter table t1 alter index j_idx visible; Query OK, 0 rows affected (0.01 sec) Records: 0  Duplicates: 0  Warnings: 0

將字段j上的索引設(shè)置為不可見，如下所示。

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中主鍵不能設(shè)置為不可見索引
值得注意的是：在MySQL中，主鍵是不可以設(shè)置為不可見的。
在testdb數(shù)據(jù)庫中創(chuàng)建一張測(cè)試表t2，如下所示。

mysql> create table t2(i int not null); Query OK, 0 rows affected (0.01 sec)

接下來，在t2表中創(chuàng)建一個(gè)不可見主鍵，如下所示

mysql> alter table t2 add primary key pk_t2(i) invisible;  ERROR 3522 (HY000): A primary key index cannot be invisible

可以看到，此時(shí)SQL語句報(bào)錯(cuò)，主鍵不能被設(shè)置為不可見索引。

二、降序索引

1.降序索引概述

• MySQL 8.0開始真正支持降序索引（descending index）。
• 只有InnoDB存儲(chǔ)引擎支持降序索引，只支持BTREE降序索引。
• MySQL 8.0不再對(duì)GROUP BY操作進(jìn)行隱式排序

2.降序索引操作

（1）MySQL 5.7中支持的語法
首先，在MySQL 5.7中創(chuàng)建測(cè)試數(shù)據(jù)庫testdb，在數(shù)據(jù)庫testdb中創(chuàng)建測(cè)試表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表中創(chuàng)建了名為idx1的索引，索引中c1字段升序排序，c2字段降序排序。

接下來，查看t2表的創(chuàng)建信息，如下所示

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版本在創(chuàng)建表的信息中，沒有字段c1和c2的排序信息，默認(rèn)都是升序。

（2）MySQL 8.0中支持的語法
在MySQL 8.x中同樣創(chuàng)建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表的創(chuàng)建信息，如下所示

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中，創(chuàng)建的索引中存在字段的排序信息。

（3）MySQL 5.7中查詢優(yōu)化器對(duì)索引的使用情況
首先，在表t2中插入一些數(shù)據(jù)，如下所示。

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表中的數(shù)據(jù)，如下所示。

mysql> select * from t2; +------+------+ | c1   | c2   | +------+------+ |    1 |  100 | |    2 |  200 | |    3 |  150 | |    4 |   50 | +------+------+ 4 rows in set (0.00 sec)

可以看到，t2表中的數(shù)據(jù)插入成功。

接下來，查看查詢優(yōu)化器對(duì)索引的使用情況，這里，查詢語句按照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字段進(jìn)行降序排序，并沒有使用索引。

（4）MySQL 8.x中查詢優(yōu)化器對(duì)降序索引的使用情況。
查看查詢優(yōu)化器對(duì)降序索引的使用情況。
首先，在表t2中插入一些數(shù)據(jù)，如下所示。

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表中的數(shù)據(jù)，如下所示。

mysql> select * from t2; +------+------+ | c1   | c2   | +------+------+ |    1 |  100 | |    2 |  200 | |    3 |  150 | |    4 |   50 | +------+------+ 4 rows in set (0.00 sec)

可以看到，t2表中的數(shù)據(jù)插入成功。

在MySQL中如果創(chuàng)建的是升序索引，則指定查詢的時(shí)候，只能按照升序索引的方式指定查詢，這樣才能使用升序索引。

接下來，查看查詢優(yōu)化器對(duì)索引的使用情況，這里，查詢語句按照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字段進(jìn)行降序排序，使用了索引。

使用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中不再對(duì)GROUP BY進(jìn)行隱式排序

在MySQL 5.7中執(zhí)行如下命令，按照c2字段進(jìn)行分組，查詢每組中數(shù)據(jù)的記錄條數(shù)。

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字段上進(jìn)行了排序操作。

在MySQL 8.x中執(zhí)行如下命令，按照c2字段進(jìn)行分組，查詢每組中數(shù)據(jù)的記錄條數(shù)。

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字段上并沒有進(jìn)行排序操作。

在MySQL 8.x中如果需要對(duì)c2字段進(jìn)行排序，則需要使用order by語句明確指定排序規(guī)則，如下所示。

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)

三、函數(shù)索引

1.函數(shù)索引概述

• MySQL 8.0.13開始支持在索引中使用函數(shù)（表達(dá)式）的值。
• 支持降序索引，支持JSON數(shù)據(jù)的索引
• 函數(shù)索引基于虛擬列功能實(shí)現(xiàn)

2.函數(shù)索引操作

（1）創(chuàng)建測(cè)試表t3
在testdb數(shù)據(jù)庫中創(chuàng)建一張測(cè)試表t3，如下所示。

mysql> create table if not exists t3(c1 varchar(10), c2 varchar(10)); Query OK, 0 rows affected (0.01 sec)

（2）創(chuàng)建普通索引
在c1字段上創(chuàng)建普通索引

mysql> create index idx1 on t3(c1); Query OK, 0 rows affected (0.01 sec) Records: 0  Duplicates: 0  Warnings: 0

（3）創(chuàng)建函數(shù)索引
在c2字段上創(chuàng)建一個(gè)將字段值轉(zhuǎn)化為大寫的函數(shù)索引，如下所示。

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）查看查詢優(yōu)化器對(duì)兩個(gè)索引的使用情況
首先，查看c1字段的大寫值是否等于某個(gè)特定的值，如下所示。

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)

可以看到，沒有使用索引，進(jìn)行了全表掃描操作。

接下來，查看c2字段的大寫值是否等于某個(gè)特定的值，如下所示。

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)

可以看到，使用了函數(shù)索引。

（6）函數(shù)索引對(duì)JSON數(shù)據(jù)的索引
首先，創(chuàng)建測(cè)試表emp，并對(duì)JSON數(shù)據(jù)進(jìn)行索引，如下所示。

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數(shù)據(jù)長度不固定，如果直接對(duì)JSON數(shù)據(jù)進(jìn)行索引，可能會(huì)超出索引長度，通常，會(huì)只截取JSON數(shù)據(jù)的一部分進(jìn)行索引。
• CAST()類型轉(zhuǎn)換函數(shù)，把數(shù)據(jù)轉(zhuǎn)化為char(30)類型。使用方式為CAST(數(shù)據(jù) as 數(shù)據(jù)類型)。
• data ->> '$.name’表示JSON的運(yùn)算符

簡(jiǎn)單的理解為，就是取name節(jié)點(diǎn)的值，將其轉(zhuǎn)化為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）函數(shù)索引基于虛擬列實(shí)現(xiàn)
首先，查看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上建立了函數(shù)索引。

接下來，在t3表中添加一列c3，模擬c2上的函數(shù)索引，如下所示。

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列是一個(gè)計(jì)算列，c3字段的值總是使用c1字段轉(zhuǎn)化為大寫的結(jié)果。

接下來，向t3表中插入一條數(shù)據(jù)，其中，c3列是一個(gè)計(jì)算列，c3字段的值總是使用c1字段轉(zhuǎn)化為大寫的結(jié)果，在插入數(shù)據(jù)的時(shí)候，不需要為c3列插入數(shù)據(jù)，如下所示。

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

查詢t3表中的數(shù)據(jù)，如下所示。

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

可以看到，并不需要向c3列中插入數(shù)據(jù)，c3列的數(shù)據(jù)為c1字段的大寫結(jié)果數(shù)據(jù)。

如果想模擬函數(shù)索引的效果，則可以使用如下方式。
首先，在c3列上添加索引，如下所示。

mysql> create index idx3 on t3(c3); Query OK, 0 rows affected (0.11 sec) Records: 0  Duplicates: 0  Warnings: 0

接下來，再次查看c1字段的大寫值是否等于某個(gè)特定的值，如下所示。

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)

此時(shí)，就使用了idx3索引。

推薦學(xué)習(xí)：mysql視頻教程