The three new indexes added in MySQL 8 are hidden, descending, and functions

The three new indexes added in MySQL 8 are hidden, descending, and functions

Hidden, descending, and functional indexes in MySQL 8

1. Hidden Index

1. Overview of hidden indexes

  • MySQL 8.0 began to support invisible indexes.
  • Hidden indexes are not used by the optimizer, but still need to be maintained.
  • Application scenarios: soft deletion and grayscale release.

In previous versions of MySQL, indexes could only be deleted explicitly. If it was discovered that the wrong index was deleted, the deleted index could only be added back by creating an index. If the amount of data in the database was very large or the table was large, the cost of this operation was very high.

In MySQL 8.0, you only need to set this index as a hidden index so that the query optimizer no longer uses this index. However, this index still needs to be maintained by the MySQL background. When it is confirmed that setting this index as a hidden index will not affect the system, delete the index completely. This is the soft delete feature.

Grayscale release means that when creating an index, first set the index as a hidden index, and then make the hidden index visible to the query optimizer by modifying the query optimizer switch. Then test the index through explain to confirm that the index is valid. If the index can be used in certain queries, it can be set as a visible index to achieve the grayscale release effect.

2. Hide index operations

(1) Log in to MySQL, create a testdb database, and create a test table t1 in the database

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) Create an index on field i as shown below.

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

(3) Create a hidden index on field j. To create a hidden index, just add the invisible keyword after the statement to create the index, as shown below:

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

(4) Check the index status in table t1, as shown below

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)

You can see that there are two indexes in the t1 table, one is i_idx and the other is j_idx. The Visible attribute of i_idx is YES, indicating that this index is visible; the Visibles attribute of j_idx is NO, indicating that this index is invisible.

(5) Check how the query optimizer uses these two indexes.

First, query using field i as shown below.

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)

As you can see, the query optimizer uses the index of the i field for optimization.
Next, query using field j as shown below.

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)

It can be seen that the query optimizer does not use the hidden index on the j field and uses a full table scan to query data.

(6) Make hidden indexes visible to the optimizer

In MySQL 8.x, a new testing method is provided, which can turn on a setting through a switch of the optimizer to make hidden indexes visible to the query optimizer.
View the query optimizer switches as shown below.

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)

Here, you can see the following property value:

use_invisible_indexes=off

Indicates whether the optimizer uses invisible indexes. The default is off.
Next, enable the query optimizer to use the invisible index at the MySQL session level as shown below.

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

Next, check the query optimizer switch settings again, as shown below

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)

At this point, you can see use_invisible_indexes=on, which means that the hidden index is visible to the query optimizer.

Let’s analyze the data again using the j field of the t1 table, as shown below.

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)

It can be seen that the query optimizer uses the hidden index on the j field to optimize the query.

(7) Set the visibility of the index

Set the hidden index on field j to visible as shown below.

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

Set the index on field j to invisible as shown below.

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

(8) The primary key in MySQL cannot be set as an invisible index

It is worth noting that in MySQL, the primary key cannot be set to invisible.
Create a test table t2 in the testdb database as shown below.

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

Next, create an invisible primary key in the t2 table as follows

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

It can be seen that the SQL statement reports an error at this time, and the primary key cannot be set as an invisible index.

2. Descending index

1. Overview of descending index

  • MySQL 8.0 began to truly support descending indexes.
  • Only the InnoDB storage engine supports descending indexes, and only BTREE descending indexes are supported.
  • MySQL 8.0 no longer performs implicit sorting on GROUP BY operations

2. Descending index operation

(1) Syntax supported in MySQL 5.7

First, create a test database testdb in MySQL 5.7, and create a test table t2 in the database testdb, as shown below.

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)

An index named idx1 is created in the t2 table. The c1 field in the index is sorted in ascending order, and the c2 field is sorted in descending order.

Next, view the creation information of the t2 table, as shown below

mysql> show create table t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `idx1` (`c1`,`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.16 sec)

It can be seen that in the table creation information of MySQL version 5.7, there is no sorting information for fields c1 and c2, and the default is ascending order.

(2) Syntax supported in MySQL 8.0

In MySQL 8.x, create the t2 table as follows

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)

Next, view the creation information of the t2 table, as shown below

mysql> show create table t2 \G
*************************** 1. row ***************************
       Table: t2
Create 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_ci
1 row in set (0.00 sec)

As you can see, in MySQL 8.x, the created index contains the sorting information of the fields.

(3) Usage of indexes by the query optimizer in MySQL 5.7

First, insert some data into table t2 as shown below.

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

Next, query the data in the t2 table as shown below.

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

As you can see, the data in the t2 table was inserted successfully.

Next, check the query optimizer's use of the index. Here, the query statement is in ascending order by the c1 field and in descending order by the c2 field, as shown below.

mysql> explain select * from t2 order by c1, c2 desc \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx1
      key_len: 10
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.12 sec)

As you can see, in MySQL 5.7, the c2 field is sorted in descending order without using an index.

(4) The usage of descending index by the query optimizer in MySQL 8.x.

Check the query optimizer's use of descending indexes.
First, insert some data into table t2 as shown below.

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

Next, query the data in the t2 table as shown below.

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

As you can see, the data in the t2 table was inserted successfully.

If you create an ascending index in MySQL, when you specify a query, you can only specify the query in ascending order so that the ascending index can be used.

Next, check the query optimizer's use of the index. Here, the query statement is in ascending order by the c1 field and in descending order by the c2 field, as shown below.

mysql> explain select * from t2 order by c1, c2 desc \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx1
      key_len: 10
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

As you can see, in MySQL 8.x, the c2 field is sorted in descending order, using the index.

Sort by using the c1 field in descending order and the c2 field in ascending order, as shown below.

mysql> explain select * from t2 order by c1 desc, c2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx1
      key_len: 10
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Backward index scan; Using index
1 row in set, 1 warning (0.00 sec)

As you can see, the index can still be used in MySQL 8.x, and the reverse scan of the index is used.

(5) MySQL 8.x no longer implicitly sorts GROUP BY statements

Execute the following command in MySQL 5.7 to group by the c2 field and query the number of records in each group.

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)

As you can see, in MySQL 5.7, a sort operation is performed on the c2 field.

Run the following command in MySQL 8.x to group by the c2 field and query the number of records in each group.

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)

As you can see, in MySQL 8.x, no sorting operation is performed on the c2 field.

In MySQL 8.x, if you need to sort the c2 field, you need to use the order by statement to explicitly specify the sorting rules, as shown below.

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)

3. Function Index

1. Overview of Functional Index

  • MySQL 8.0.13 began to support the use of function (expression) values ​​in indexes.
  • Support descending index and JSON data index
  • Functional index is implemented based on virtual column function

2. Function index operation

(1) Create test table t3

Create a test table t3 in the testdb database as shown below.

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

(2) Create a normal index

Create a normal index on the c1 field

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

(3) Create a functional index

Create a functional index on the c2 field that converts the field value to uppercase, as shown below.

mysql> create index func_index on t3 ((UPPER(c2)));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

(4) View the index information on the t3 table, as shown below.

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) Check the query optimizer's use of the two indexes

First, check whether the uppercase value of the c1 field is equal to a specific value, as shown below.

mysql> explain select * from t3 where upper(c1) = 'ABC' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   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)

As you can see, no index is used and a full table scan operation is performed.

Next, check whether the uppercase value of the c2 field is equal to a specific value, as shown below.

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: NULL
1 row in set, 1 warning (0.00 sec)

As you can see, a functional index is used.

(6) Function index for JSON data

First, create a test table emp and index the JSON data as shown below.

mysql> create table if not exists emp(data json, index((CAST(data->>'$.name' as char(30)))));
Query OK, 0 rows affected (0.02 sec)

The above SQL statements are explained as follows:

  • The length of JSON data is not fixed. If you directly index the JSON data, it may exceed the index length. Usually, only a part of the JSON data is intercepted for indexing.
  • CAST() type conversion function converts data into char(30) type. The usage method is CAST (data as data type).
  • data ->> '$.name' represents the JSON operator

Simply put, it takes the value of the name node and converts it to char(30) type.

Next, check the index status in the emp table, as shown below.

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) Functional index is implemented based on virtual columns

First, view the information of the t3 table, as shown below.

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)

A common index is created on c1, and a functional index is created on c2.

Next, add a column c3 to the t3 table to simulate the functional index on c2, as shown below.

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

Column c3 is a calculated column. The value of the c3 field is always the result of converting the c1 field to uppercase.

Next, insert a piece of data into the t3 table. The c3 column is a calculated column. The value of the c3 field is always the result of converting the c1 field to uppercase. When inserting data, there is no need to insert data into the c3 column, as shown below.

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

Query the data in the t3 table as shown below.

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

It can be seen that there is no need to insert data into column c3. The data in column c3 is the uppercase result data of field c1.

If you want to simulate the effect of a functional index, you can use the following method.
First, add an index on the c3 column as shown below.

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

Next, check again whether the uppercase value of the c1 field is equal to a specific value, as shown below.

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: NULL
1 row in set, 1 warning (0.00 sec)

At this time, the idx3 index is used.

This is the end of this article about the three new indexes in MySQL 8: hidden, descending, and function. For more information about hidden, descending, and function indexes in MySQL 8, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • In-depth explanation of hidden fields, a new feature of MySQL 8.0
  • Descending Index in MySQL 8.0
  • Detailed explanation of the underlying implementation of descending index, a new feature of MySQL 8
  • MySQL 8 new features: Descending index details

<<:  Textarea tag in HTML

>>:  Web design skills: iframe adaptive height problem

Recommend

Linux system repair mode (single user mode)

Table of contents Preface 1. Common bug fixes in ...

Detailed explanation of how to migrate a MySQL database to another machine

1. First find the Data file on the migration serv...

Implementation of code optimization for Vue2.x project performance optimization

Table of contents 1 Use of v-if and v-show 2. Dif...

Detailed explanation of EXT series file system formats in Linux

Linux File System Common hard disks are shown in ...

JavaScript microtasks and macrotasks explained

Preface: js is a single-threaded language, so it ...

webpack -v error solution

background I want to check the webpack version, b...

Common array operations in JavaScript

Table of contents 1. concat() 2. join() 3. push()...

Web skills: Multiple IE versions coexistence solution IETester

My recommendation Solution for coexistence of mul...

Detailed explanation of the use principle and calculation method of the umask command under Linux

Table of contents umask Umask usage principle 1. ...

A complete list of frequently asked JavaScript questions for front-end interviews

Table of contents 1. Handwritten instanceof 2. Im...

Using vue3 to implement counting function component encapsulation example

Table of contents Preface 1. The significance of ...

40 web page designs with super large fonts

Today's web designs tend to display very larg...

Notes on using $refs in Vue instances

During the development process, we often use the ...

Docker removes abnormal container operations

This rookie encountered such a problem when he ju...

Tutorial on installing Microsoft TrueType fonts on Ubuntu-based distributions

If you open some Microsoft documents with LibreOf...