MySQL 8.x で追加された 3 つの新しいインデックスメソッドの詳細な分析 (概要共有)-mysql チュートリアル-php.cn

この記事では、MySQL 8.x バージョンに追加された 3 つの新しいインデックスに関する知識を提供します。 MySQL 8.x には、隠しインデックス、降順インデックス、関数インデックスという 3 つの新しいインデックスメソッドが追加されました。

MySQL 8.x で追加された 3 つの新しいインデックスメソッドの詳細な分析 (概要共有)

#1. 隠しインデックス

1. 隠しインデックスの概要

非表示のインデックスはオプティマイザでは使用されませんが、維持する必要があります。
アプリケーションシナリオ: ソフト削除、グレースケール公開。

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 キーワードを追加するだけです。 (4) 以下に示すように、t1 テーブルのインデックスの状況を確認します。

mysql> create index j_idx on t1(j) invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

ログイン後にコピー

t1 テーブルには、i_idx と j_idx の 2 つのインデックスがあることがわかります。i_idx の Visible 属性は、 YES はこのインデックスが表示されることを示し、 j_idx Visibles 属性は NO はこのインデックスが表示されないことを示します。

(5) クエリオプティマイザーによるこれら 2 つのインデックスの使用状況を確認します。

まず、以下に示すように、フィールド i を使用してクエリを実行します。

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)

ログイン後にコピー

クエリオプティマイザーは j フィールドの非表示インデックスを使用せず、テーブル全体のスキャンを使用してデータをクエリしていることがわかります。

(6) 非表示のインデックスをオプティマイザに表示する

MySQL 8.x では、新しいテスト方法が提供されており、オプティマイザのスイッチを通じて特定の設定をオンにして、非表示のインデックスを作成できます。インデックスが表示されます。クエリオプティマイザーに表示されます。

以下に示すように、クエリオプティマイザースイッチを確認します。

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)

ログイン後にコピー

ここでは、次の属性値が表示されます:

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)

ログイン後にコピー

オプティマイザが非表示のインデックスを使用するかどうかを示します。デフォルトはオフで、使用されません。

次に、次のようにクエリオプティマイザーが MySQL セッションレベルで非表示のインデックスを使用できるようにします。

use_invisible_indexes=off

ログイン後にコピー

次に、以下に示すように、クエリオプティマイザーのスイッチ設定を再度確認します。

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

ログイン後にコピー

この時点で、use_invisible_indexes=on が表示され、非表示のインデックスがクエリに表示されることを示します。オプティマイザ。

次に示すように、t1 テーブルの j フィールドを使用してクエリデータを再度分析します。

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)

ログイン後にコピー

クエリオプティマイザーが j フィールドの非表示インデックスを使用してクエリを最適化していることがわかります。

(7) 表示インデックスと非表示インデックスの設定

以下に示すように、フィールド 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 のインデックスを非表示に設定します。

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

ログイン後にコピー

(8) MySQL の主キーを非表示インデックスとして設定することはできません。

MySQL では、主キーを非表示インデックスとして設定できないことに注意してください。

以下に示すように、testdb データベースにテストテーブル t2 を作成します。

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

ログイン後にコピー

次に、以下に示すように、t2 テーブルに非表示の主キーを作成します。

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

ログイン後にコピー

この時点で SQL ステートメントがエラーを報告し、主キーを設定できないことがわかります。目に見えない指標として。

2. 降順インデックス

1. 降順インデックスの概要

MySQL 8.0 では、降順インデックスのサポートが開始されます。

MySQL 8.0 は、GROUP BY 操作で暗黙的なソートを実行しなくなりました
2. 降順インデックス操作

(1)MySQL 5.7 でサポートされる構文

まず、以下に示すように、MySQL 5.7 でテストデータベース testdb を作成し、データベース testdb 内にテストテーブル t2 を作成します。

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

ログイン後にコピー

このうち、t2 テーブルに idx1 というインデックスが作成され、インデックス内の c1 フィールドが昇順、c2 フィールドが降順にソートされます。

次に、以下に示すように、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)

ログイン後にコピー

MySQL 5.7 バージョンでは、テーブル作成情報にフィールド c1 と c2 のソート情報がないことがわかります。デフォルトでは、すべてが昇順です。

(2) MySQL 8.0 でサポートされる構文

以下に示すように、MySQL 8.x にも 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)

ログイン後にコピー

次に、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 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视频教程

以上がMySQL 8.x で追加された 3 つの新しいインデックスメソッドの詳細な分析 (概要共有)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。