Analisis terperinci tentang tiga kaedah indeks baharu yang ditambahkan dalam MySQL 8.x (perkongsian ringkasan)

Artikel ini membawa anda pengetahuan tentang tiga indeks baharu yang ditambahkan dalam versi MySQL 8.x. Terdapat tiga kaedah indeks baharu dalam MySQL 8.x: indeks tersembunyi, indeks menurun dan indeks fungsi Saya harap ia akan membantu semua orang.

1. Indeks tersembunyi

1 Gambaran keseluruhan indeks tersembunyi

• MySQL 8.0 mula menyokong indeks tersembunyi. (invisible) index), invisible index.
• Indeks tersembunyi tidak digunakan oleh pengoptimum, tetapi masih perlu dikekalkan.
• Senario aplikasi: pemadaman lembut, penerbitan skala kelabu.

Dalam versi MySQL sebelumnya, indeks hanya boleh dipadamkan secara eksplisit Jika indeks yang salah ditemui selepas pemadaman, indeks yang dipadamkan hanya boleh ditambah semula dengan membuat indeks jika jumlah data masuk pangkalan data adalah sangat besar, atau jadualnya agak besar, kos operasi ini sangat tinggi.

Dalam MySQL 8.0, anda hanya perlu menetapkan indeks ini sebagai indeks tersembunyi terlebih dahulu supaya pengoptimum pertanyaan tidak lagi menggunakan indeks ini Namun, indeks ini masih perlu dikekalkan oleh latar belakang MySQL indeks ini akan Apabila sistem pengindeksan ditetapkan untuk disembunyikan dan tidak akan terjejas, padamkan indeks sepenuhnya. Ini ialah ciri padam lembut.

Penerbitan skala kelabu bermakna apabila membuat indeks, mula-mula tetapkan indeks kepada indeks tersembunyi, jadikan indeks tersembunyi kelihatan kepada pengoptimum pertanyaan dengan mengubah suai suis pengoptimum pertanyaan dan uji indeks melalui explain untuk mengesahkan ini. Jika indeks itu sah dan pertanyaan tertentu boleh menggunakan indeks ini, anda boleh menetapkannya sebagai indeks yang boleh dilihat untuk mencapai kesan penerbitan skala kelabu.

2. Operasi indeks tersembunyi

(1) Log masuk ke MySQL, cipta pangkalan data testdb dan buat jadual ujian t1 dalam pangkalan data

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 ) Dalam Buat indeks pada medan i seperti yang ditunjukkan di bawah.

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

(3) Cipta indeks tersembunyi pada medan j Apabila mencipta indeks tersembunyi, anda hanya perlu menambah kata kunci yang tidak kelihatan selepas pernyataan yang mencipta indeks, seperti yang ditunjukkan di bawah

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

(4) Semak situasi indeks dalam jadual t1, seperti yang ditunjukkan di bawah

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)

Anda boleh melihat bahawa terdapat dua indeks dalam jadual t1, satu ialah i_idx, satu lagi ialah j_idx, atribut Visible i_idx ialah YA, Menunjukkan bahawa indeks ini boleh dilihat; atribut Visible bagi j_idx ialah NO, yang menunjukkan bahawa indeks ini tidak kelihatan.

(5) Semak penggunaan pengoptimum pertanyaan bagi kedua-dua indeks ini.

Pertama, gunakan medan i untuk membuat pertanyaan, seperti yang ditunjukkan di bawah.

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)

Seperti yang anda lihat, pengoptimum pertanyaan tidak menggunakan indeks tersembunyi pada medan j, tetapi menggunakan imbasan jadual penuh untuk menanyakan data.

(6) Jadikan indeks tersembunyi kelihatan kepada pengoptimum

Kaedah ujian baharu disediakan dalam MySQL 8.x Anda boleh menghidupkan tetapan tertentu melalui suis pengoptimum untuk menjadikan indeks tersembunyi kelihatan . Kelihatan kepada pengoptimum pertanyaan.
Lihat suis pengoptimum pertanyaan seperti yang ditunjukkan di bawah.

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)

Di sini, anda boleh melihat nilai atribut berikut:

use_invisible_indexes=off

menunjukkan sama ada pengoptimum menggunakan indeks halimunan lalai dimatikan dan tidak digunakan.

Seterusnya, dayakan pengoptimum pertanyaan menggunakan indeks halimunan pada peringkat sesi dalam MySQL seperti yang ditunjukkan di bawah.

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

Seterusnya, semak tetapan suis pengoptimum pertanyaan sekali lagi, seperti yang ditunjukkan di bawah

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)

Pada masa ini, anda boleh melihat use_invisible_indexes=on, menunjukkan bahawa indeks tersembunyi sangat penting kepada pengoptimum pertanyaan.

Analisis data pertanyaan menggunakan medan j jadual t1 sekali lagi, seperti ditunjukkan di bawah.

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)

Seperti yang anda lihat, pengoptimum pertanyaan menggunakan indeks tersembunyi pada medan j untuk mengoptimumkan pertanyaan.

(7) Tetapkan indeks kelihatan dan tidak kelihatan

Tetapkan indeks tersembunyi pada medan j kepada kelihatan, seperti ditunjukkan di bawah.

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

Tetapkan indeks pada medan j kepada tidak kelihatan seperti yang ditunjukkan di bawah.

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

(8) Kunci utama dalam MySQL tidak boleh ditetapkan sebagai indeks halimunan

Perlu diingat bahawa dalam MySQL, kunci utama tidak boleh ditetapkan sebagai halimunan.
Cipta jadual ujian t2 dalam pangkalan data testdb, seperti ditunjukkan di bawah.

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

Seterusnya, buat kunci utama yang tidak kelihatan dalam jadual t2, seperti yang ditunjukkan di bawah

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

Seperti yang anda lihat, pernyataan SQL melaporkan ralat pada masa ini, dan kunci utama tidak boleh ditetapkan kepada bukan Indeks Kelihatan.

2. Indeks menurun

1. Gambaran keseluruhan indeks menurun

MySQL 8.0 benar-benar menyokong indeks menurun.
• Hanya enjin storan InnoDB menyokong indeks menurun dan hanya indeks menurun BTREE disokong.
• MySQL 8.0 tidak lagi melaksanakan pengisihan tersirat untuk operasi GROUP BY

2 Operasi indeks menurun

(1) Sintaks disokong dalam MySQL 5.7

Pertama, cipta pangkalan data ujian testdb dalam MySQL 5.7, dan cipta jadual ujian t2 dalam pangkalan data testdb, seperti yang ditunjukkan di bawah.

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)

Antaranya, indeks bernama idx1 dicipta dalam jadual t2 Medan c1 dalam indeks diisih dalam tertib menaik, dan medan c2 diisih dalam susunan menurun.

Seterusnya, semak maklumat penciptaan jadual t2, seperti yang ditunjukkan di bawah

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)

Anda boleh melihat bahawa MySQL versi 5.7 tidak mempunyai maklumat pengisihan medan c1 dan c2 dalam jadual maklumat penciptaan , lalai adalah tertib menaik.

(2) Sintaks disokong dalam MySQL 8.0

Cipta jadual t2 dalam MySQL 8.x seperti berikut

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)

Seterusnya, lihat maklumat penciptaan jadual t2 , seperti yang ditunjukkan di bawah

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)

Anda boleh melihat bahawa dalam MySQL 8.x, maklumat pengisihan medan wujud dalam indeks yang dibuat.

(3) Penggunaan indeks oleh pengoptimum pertanyaan dalam MySQL 5.7

Pertama, masukkan beberapa data ke dalam jadual t2, seperti yang ditunjukkan di bawah.

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

Atas ialah kandungan terperinci Analisis terperinci tentang tiga kaedah indeks baharu yang ditambahkan dalam MySQL 8.x (perkongsian ringkasan). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!