Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "what indexing methods have been added in MySQL 8.x". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Hide index 1. Overview of hidden indexes

MySQL 8.0 began to support hidden indexes (invisible index), invisible indexes.

Hidden indexes are not used by the optimizer, but still need to be maintained.

Application scenarios: soft deletion, grayscale publishing.

In the previous version of MySQL, the index could only be deleted explicitly. If the index is deleted incorrectly, the deleted index can only be added back by creating the 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 first, so that the query optimizer will no longer use the index. However, at this time, the index still needs to be maintained by the MySQL background. When you confirm that setting the index to the hidden index system will not be affected, delete the index completely. This is the soft delete function.

Grayscale publishing, that is, 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 some queries can use this index, you can set it to a visible index to complete the grayscale publishing effect.

two。 Hide index operation

(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.58sec) mysql > use testdb;Database changedmysql > create table if not exists T1 (I int, j int); Query OK, 0 rows affected (0.05sec)

(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 statement that creates 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 indexes in the T1 table, as shown below

Mysql > show index from T1\ Cardinality * 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: NULL2 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 YES, which means that the index is visible, and the Visibles property of j_idx is NO, which means that the index is not visible.

(5) check the usage of these two indexes by the query optimizer.

First, use field I to query, as shown below.

Mysql > explain select * from T1 where I = 1\ row * * id: 1 select_type: SIMPLE table: T1 partitions: NULL type: refpossible_keys: i_idx key: i_idx key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set 1 warning (0.02 sec) can be seen The query optimizer uses the index of the I field for optimization. Next, use field j to query, as shown below. Mysql > explain select * from T1 where j = 1\ row * * id: 1 select_type: SIMPLE table: T1 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)

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

(6) make the hidden index visible to the optimizer

A new way of testing is provided in MySQL 8.x, where you can turn on a setting through a switch of the optimizer to make the hidden index visible to the query optimizer.

Look at 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=on1 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, which are not used by off by default.

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, look at the switch settings for the query optimizer again, as follows

Mysql > select @ @ optimizer_switch\ gateway * 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=on1 row in set (0.00 sec)

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

Again analyze the query data using the j field of the T1 table, as shown below.

Mysql > explain select * from T1 where j = 1\ row * * id: 1 select_type: SIMPLE table: T1 partitions: NULL type: refpossible_keys: j_idx key: j_idx key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set 1 warning (0.00 sec)

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

(7) set the visibility and invisibility 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) Primary key cannot be set to invisible index in MySQL

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.01sec)

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

As you can see, the SQL statement reports an error at this time, and the primary key cannot be set to an invisible index.

Second, descending index 1. Overview of descending indexing

MySQL 8.0 has really supported descending indexing (descending index).

Only the InnoDB storage engine supports descending indexes and only BTREE descending indexes.

MySQL 8.0 no longer implicitly sorts GROUP BY operations

two。 Descending index operation

(1) Syntax supported in MySQL 5.7

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

Mysql > create database if not exists testdb;Query OK, 0 rows affected (0.71 sec) mysql > use testdb;Database changedmysql > create table if not exists T2 (C1 int, c2 int, index idx1 (C1 asc, c2 desc); Query OK, 0 rows affected (0.71 sec)

Among them, an index named idx1 is created in the T2 table, in which C1 fields are sorted in ascending order and C2 fields in descending order.

Next, look at the creation information for the T2 table, as shown below

Mysql > show create table T2\ Gateway * 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)

As you can see, in MySQL version 5.7, there is no sorting information for fields C1 and c2 in the creation table, and the default is ascending order.

(2) syntax supported in MySQL 8.0

Also create the T2 table in MySQL 8.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, look at the creation information for the T2 table, as shown below

Mysql > show create table T2\ gateway * 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)

As you can see, in MySQL 8.x, there is sorting information for fields in the index created.

(3) query optimizer's use of index 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, take a look at the query optimizer's use of the index. Here, the query statement follows the ascending order of the C1 field and the descending order of the C2 field, as shown below.

Mysql > explain select * from T2 order by C1 C2 desc\ gateway * 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)

As you can see, in MySQL 5.7, the sort is sorted descending by the c2 field, and the index is not used.

(4) query optimizer's use of descending indexes in MySQL 8.x.

View the use of descending indexes by the query optimizer.

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 are creating an ascending index in MySQL, when you specify a query, you can only specify the query as an ascending index, so that an ascending index can be used.

Next, take a look at the query optimizer's use of the index. Here, the query statement follows the ascending order of the C1 field and the descending order of the C2 field, as shown below.

Mysql > explain select * from T2 order by C1 C2 desc\ gateway * 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)

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

Use 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\ indexpossible_keys * 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)

As you can see, indexes can still be used in MySQL 8.x, and reverse scanning of indexes is used.

(5) implicit sorting of GROUP BY is no longer performed in MySQL 8.x.

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

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, sorting is performed on the c2 field.

Execute the following command in MySQL 8.x to query the number of records in each group by grouping according to the c2 field.

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, there is no sort operation on the c2 field.

If you need to sort the c2 fields in MySQL 8.x, you need to specify the collation explicitly using the order by statement, 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 indexing

MySQL 8.0.13 began to support the use of values of functions (expressions) in indexes.

Support for descending indexing and indexing of JSON data

Implementation of functional index based on virtual column function

two。 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.01sec)

(2) create a general 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\ Sub_part * 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.01sec)

(5) check the usage of the query optimizer for the two indexes

First, check to see if 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'\ gateway * 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)

As you can see, a full table scan is performed without using the index.

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: refpossible_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)

As you can see, a functional index is used.

(6) functional indexing on 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 statement is interpreted as follows:

The length of JSON data is not fixed. If you index JSON data directly, it may exceed the index length. In general, only part of the JSON data will be intercepted for indexing.

CAST () type conversion function to convert data to type char (30). The mode of use is CAST (data as data type).

Data-> >'$.name 'represents the operator of JSON

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

Next, look at the indexes in the emp table, as shown below.

Mysql > show index from emp\ gateway * 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, look at the information in 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)

The general index is established on C1 and the functional index is established on c2.

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

The c3 column is a calculated column, and the value of the c3 field is always converted to an uppercase result using the C1 field.

Next, insert a piece of data into the T3 table, where the c3 column is a calculated column, and the values of the c3 field are always converted to uppercase results using the C1 field. When inserting data, there is no need 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 the 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, which is the uppercase result data of the C1 field.

If you want to simulate the effect of a functional index, you can use the following methods.

First, add the index on column c3, 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 to see if 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'\ gateway * 1. Row * * id: 1 select_type: SIMPLE table: T3 partitions: NULL type: refpossible_keys: idx3 key: idx3 key_ Len: 43 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set 1 warning (0.00 sec)

At this point, the idx3 index is used.

This is the end of the content of "what indexing methods have been added in MySQL 8.x". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.