Hello, I'm glacier~~

Today, let's talk about MySQL 8 Three new indexes in version X. MySQL 8. Three indexing methods have been added to x, as shown below.

• Hide index
• descending index
• Functional index

If the article is helpful to you, let's start by praising, collecting, commenting and sharing~~

1, Hide index

1. Overview of hidden indexes

• MySQL 8.0 supports invisible indexes and invisible indexes.
• Hidden indexes are not used by the optimizer, but they still need to be maintained.
• Application scenario: soft deletion, grayscale publishing.

In the previous version of MySQL, the index can only be deleted explicitly. If the index is deleted incorrectly, the deleted index can only be added back by creating an index. If the amount of data in the database is very large or the table is relatively large, the cost of this operation is very high.

In MySQL 8.0, you only need to set the index as a hidden index to make the query optimizer no longer use the index. However, at this time, the index still needs to be maintained in the MySQL background. When you confirm that setting the index as a hidden index will not affect the system, delete the index completely. This is the soft delete function.

Grayscale publishing means that when creating an index, first set the index as a hidden index, modify the switch of the query optimizer to make the hidden index visible to the query optimizer, test the index through explain to confirm that the index is valid, and set it as a visible index for some queries to complete the effect of grayscale publishing.

2. Hide index operation

(1) Log in to MySQL, create 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. when creating a hidden index, you only need to add the invisible keyword after the index creation statement, 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 in the t1 table, 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, one is j_idx，i_ The Visible attribute of IDX is YES, indicating that the index is Visible; j_ The Visible attribute of IDX is NO, indicating that the index is invisible.

(5) View the usage of the two indexes by the query optimizer.
First, use the field i to query, 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 will use i Optimize the index of the field.
Next, use the fields j Query, 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)

You can see that the query optimizer does not use the hidden index on the j field, but uses the full table scan to query the data.

(6) Make hidden indexes visible to the optimizer
In MySQL 8 X provides a new test method. You can turn on a setting through a switch of the optimizer to make the hidden index visible to the query optimizer.
View the switch for the query optimizer, 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 attribute value:

use_invisible_indexes=off

Indicates whether the optimizer uses invisible indexes. The default value is off.
Next, make the query optimizer use invisible indexes at the session level of MySQL, as shown below.

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

Next, review the switch settings of the query optimizer 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_ invisible_ Indexes = on, indicating that the hidden index is visible to the query optimizer.

Analyze the query data using the j field of the t1 table again, 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)

As you can see, the query optimizer optimizes the query using the hidden index on the j field.

(7) Sets the visibility and invisibility of the index
Set the hidden index on field j to be 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 to 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 shown below

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

You can see that the SQL statement reports an error, and the primary key cannot be set as an invisible index.

2, Descending index

1. Overview of descending index

• MySQL 8.0 really supports descending index.
• Only InnoDB storage engine supports descending index and only BTREE descending index.
• MySQL 8.0 no longer implicitly sorts GROUP BY operations

2. Descending index operation

(1) Syntax supported in MySQL 5.7
First, create the test database testdb in MySQL 5.7, and create the 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 fields in the index are sorted in ascending order and the c2 fields are 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)

You can see that in MySQL version 5.7, there is no sorting information for fields c1 and c2 in the information of creating tables. They are in ascending order by default.

(2) Syntax supported in MySQL 8.0
In MySQL 8 The t2 table is also created in X, as shown below

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 In X, there is sorting information of fields in the created index.

(3) Index usage by 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 table t2 was successfully inserted.

Next, check the usage of the index by the query optimizer. Here, the query statements are in ascending order according to the c1 field and in descending order according to 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)

You can see that in MySQL 5.7, the c2 field is sorted in descending order, and no index is used.

(4) Usage of descending index by query optimizer in MySQL 8.x.
View query optimizer usage 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 table t2 was successfully inserted.

If an ascending index is created in MySQL, the query can only be specified in the way of ascending index when specifying the query, so that the ascending index can be used.

Next, check the usage of the index by the query optimizer. Here, the query statements are in ascending order according to the c1 field and in descending order according to 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 In X, it is sorted in descending order according to the c2 field, and the index is used.

Use c1 field to sort in descending order and c2 field to sort 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, in MySQL 8 The index can still be used in X, and the reverse scan of the index is used.

(5) GROUP BY is no longer implicitly sorted in MySQL 8.x

Execute the following commands in MySQL 5.7, group according to c2 field, and query the number of data 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)

You can see that in MySQL 5.7, sorting is performed on the c2 field.

In MySQL 8 Execute the following command in X, group according to c2 field, and query the number of data 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 In X, there is no sort operation on the c2 field.

In MySQL 8 If you need to sort the c2 field in X, you need to use the order by statement to explicitly specify the sorting rule, 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, Functional index

1. Overview of function 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 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 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 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) View the usage of the two indexes by the query optimizer
First, check whether the upper case 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)

You can see that the index is not used and the full table scan operation is performed.

Next, check to see if 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, the functional index is used.

(6) Functional index the index of JSON data
First, create the 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 index JSON data directly, it may exceed the index length. Usually, only a part of JSON data will be intercepted for indexing.
• CAST() type conversion function converts data into char(30) type. The usage mode is cast (data as type).
• data ->> '$. Name 'represents the JSON operator

The simple understanding is to take the value of the name node and convert it into char(30) type.

Next, look at the indexes 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 based on virtual column
First, view the information of 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)

The ordinary index is established on c1 and the functional index is established on c2.

Next, add a column c3 in 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

The c3 column is a calculated column. The value of the c3 field is always converted to uppercase using the c1 field.

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 converted to uppercase by the c1 field. When inserting data, it is not necessary to insert data for 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 t3 table, as shown below.

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

As you can see, there is no need to insert data into the c3 column. The data in the c3 column is the uppercase result data of the c1 field.

If you want to simulate the effect of functional index, you can use the following methods.
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 upper case 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 point, the idx3 index is used.

Write at the end

If you want to enter a big factory, want to be promoted and raised, or are confused about your existing work, you can communicate with me privately. I hope some of my experience can help you~~

Recommended reading:

• <A faster lock than read-write lock in high concurrency scenario. I was completely impressed after reading it!! (recommended Collection)>
• <Summary of the most complete performance optimization of the whole network!! (glacier hematemesis finishing, recommended Collection)>
• <After three days of rolling up MyBatis, please ask!! (glacier hematemesis finishing, recommended Collection)>
• <I advise those students who have just joined the work: if you want to enter the big factory, you must master these concurrent programming knowledge! Complete learning route!! (recommended Collection)>
• <I advise those students who have just joined the work: if you want to enter the big factory, you must master these core skills! Complete learning route!! (recommended Collection)>
• <I advise those students who have just joined the work: the sooner they know the basic knowledge of computers and operating systems, the better! Ten thousand words long text is too top!! (recommended Collection)>
• <I developed a national game suitable for all ages in three days. It supports playing music. Now I open the complete source code and comments (recommended Collection)!!>
• <I am the author of high concurrency programming with the hardest core in the network and the most noteworthy blogger of CSDN. Do you agree? (recommended Collection)>
• <Five years after graduation, from a monthly salary of 3000 to an annual salary of one million, what core skills have I mastered? (recommended Collection)>
• <I invaded the Wifi of my sister next door and found... (actual dry goods in the whole process, collection recommended)>
• <Don't try "panda burning incense" easily. I regret it!>
• <On the Qingming Festival, I secretly trained "panda burning incense". As a result, my computer "died" for the panda!>
• <73000 words liver burst Java 8 new features, I don't believe you can read it! (recommended Collection)>
• <What kind of experience is it to unplug the server during peak business hours?>
• <Summary of the most complete Linux commands in the whole network!! (the most complete in history, recommended Collection)>
• <Write a tool in Python and crack MySQL perfectly!! (recommended Collection)>
• <Why isn't the SimpleDateFormat class thread safe? (six solutions are attached, recommended Collection)>

Well, that's all for today. Let's praise, collect and comment. Let's walk up three times with one button. I'm glacier. I'll see you next time~~