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

This article will explain in detail the example analysis of the descending index in MySQL8. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Preface

MySQL 8.0 finally supports descending indexing. In fact, syntactically, MySQL 4 supports it, but as the official documentation says, "they are parsed but ignored" actually creates an ascending index.

No picture, no truth, the same table statement, look at the difference between MySQL 5.7and 8.0.

Create table slowtech.t1 (C1 int,c2 int,index idx_c1_c2 (C1 desc c2))

MySQL 5.7

Mysql > show create table slowtech.t1\ gateway * 1. Row * * Table: t1Create Table: CREATE TABLE `t1` (`c1` int (11) DEFAULT NULL, `c2` int (11) DEFAULT NULL, KEY `idx_c1_ c2` (`c1`, `c2`) ENGINE=InnoDB DEFAULT CHARSET=latin1row in set (0.00 sec)

Although column c2 specifies desc, it is ignored in the actual table-building statement. Let's take a look at the results of MySQL 8.0.

Mysql > show create table slowtech.t1\ gateway * 1. Row * * Table: t1Create Table: CREATE TABLE `t1` (`c1` int (11) DEFAULT NULL, `c2` int (11) DEFAULT NULL, KEY `idx_c1_ c2` (`c1`, `c2` DESC)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_cirow in set (0.00 sec)

Column c2 still retains the desc clause.

The significance of descending index

If you have a query, you need to sort multiple columns, and the order requirements are inconsistent. In this scenario, to avoid the extra sorting of the database-"filesort"-you can only use descending indexes. Or the table above, let's see the difference between a descending index and no index.

MySQL 5.7

Mysql > explain select * from slowtech.t1 order by C1 c2 desc +- -+ | id | select_type | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -+-+ | 1 | SIMPLE | T1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index Using filesort | +-+- -+ row in set 1 warning (0.00 sec)

MySQL 8.0

Mysql > explain select * from slowtech.t1 order by C1 c2 desc +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | T1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index | +- -+ row in set 1 warning (0.00 sec)

By comparing the two, we can see that MySQL 8.0 avoids "filesort" because of the existence of descending indexes.

This is actually the main application scenario for descending indexing. If you sort only a single column, the descending index is not very meaningful, whether it is ascending or descending, the ascending index can cope with it. For the same table, take a look at the query below.

MySQL 5.7

Mysql > explain select * from slowtech.t1 order by C1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | T1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index | +- -+ row in set 1 warning (0.00 sec) mysql > explain select * from slowtech.t1 order by C1 desc +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | T1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index | +- -+ row in set 1 warning (0.00 sec)

Although C1 is an ascending index, in the second query, when it is sorted in descending order, there is no additional sort, and the index is used. Here, it is easy to make a mistake that ascending indexes cannot be used for descending arrangements. in fact, for indexes, MySQL not only supports forward scanning, but also reverse scanning. The performance of reverse scanning is also not bad. The following is the official stress test results for the descending index. The test table has only two columns (a desc,b asc), and a joint index (an index) has been established. Interested children's shoes can take a look at http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/.

In 8.0, for reverse scanning, there is a special word to describe "Backward index scan".

Mysql > explain select * from slowtech.t1 order by C1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | T1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index | +- -+ row in set 1 warning (0.00 sec) mysql > explain select * from slowtech.t1 order by C1 desc +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+-+-- + | 1 | SIMPLE | T1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Backward index scan Using index | +-+- -- + row in set 1 warning (0.00 sec)

Finally no more implicit sorting of group by

Due to the introduction of descending indexes, MySQL 8.0 no longer implicitly sorts group by operations.

Let's take a look at the tests in MySQL 5.7 and 8.

Create table slowtech.t1 (id int); insert into slowtech.t1 values (2); insert into slowtech.t1 values (3); insert into slowtech.t1 values (1)

MySQL 5.7

Mysql > select * from slowtech.t1 group by id;+-+ | id | +-+ | 1 | | 2 | 3 | +-+ rows in set (0.00 sec) mysql > explain select * from slowtech.t1 group by id +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -- +-+ | 1 | SIMPLE | T1 | NULL | ALL | NULL | 3 | 100.00 | Using temporary Using filesort | +-+- -+ row in set 1 warning (0.00 sec)

"Using filesort" means that there is a sort operation in the query, and from the result point of view, the id column is indeed an ascending output.

MySQL 8.0

Mysql > select * from slowtech.t1 group by id;+-+ | id | +-+ | 2 | | 3 | | 1 | +-+ rows in set (0.00 sec) mysql > explain select * from slowtech.t1 group by id +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+-- -+ | 1 | SIMPLE | T1 | NULL | ALL | NULL | 3 | 100.00 | Using temporary | +- -+ row in set 1 warning (0.01 sec)

Not only is the result not output in ascending order, but there is no "Using filesort" in the execution plan.

It can be seen that MySQL 8.0 no longer does implicit sorting for group by operations.

Upgrade from 5. 7 to 8. 0, businesses that rely on group by implicit sorting should be careful.

This is the end of this article on "sample analysis of descending indexes in MySQL8". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.