Descending Index in MySQL 8.0

Preface

I believe everyone knows that indexes are ordered; however, in previous versions of MySQL, only ascending indexes were supported, not descending indexes, which would cause some problems; in the latest MySQL 8.0 version, descending indexes were finally introduced, and we will take a look at them next.

Descending index

Single column index

(1) View the test table structure

mysql> show create table sbtest1\G
*************************** 1. row ***************************
    Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `k` int unsigned NOT NULL DEFAULT '0',
 `c` char(120) NOT NULL DEFAULT '',
 `pad` char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
1 row in set (0.00 sec)

(2) Execute the SQL statement order by ... limit n. The default is ascending order, and the index can be used.

mysql> explain select * from sbtest1 order by k limit 10;
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+------+------+
| 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 10 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+------+------+
1 row in set, 1 warning (0.00 sec)

(3) Execute the SQL statement order by ... desc limit n. If the order is descending, the index cannot be used. Although the reverse order can be scanned, the performance will be affected.

mysql> explain select * from sbtest1 order by k desc limit 10;
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
| 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 10 | 100.00 | Backward index scan |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)

(4) Create a descending index

mysql> alter table sbtest1 add index k_2(k desc);
Query OK, 0 rows affected (6.45 sec)
Records: 0 Duplicates: 0 Warnings: 0

(5) Execute the SQL statement order by ... desc limit n again, and the descending index can be used.

mysql> explain select * from sbtest1 order by k desc limit 10;
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+------+------+
| 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_2 | 4 | NULL | 10 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+------+------+
1 row in set, 1 warning (0.00 sec)

Multi-column indexes

(1) View the test table structure

mysql> show create table sbtest1\G
*************************** 1. row ***************************
    Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `k` int unsigned NOT NULL DEFAULT '0',
 `c` char(120) NOT NULL DEFAULT '',
 `pad` char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 KEY `k_1` (`k`),
 KEY `idx_c_pad_1` (`c`,`pad`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
1 row in set (0.00 sec)

(2) For multi-column indexes, if there is no descending index, then only SQL 1 can use the index, SQL 4 can scan in reverse order, and the other two SQL statements can only perform a full table scan, which is very inefficient.

SQL 1: select * from sbtest1 order by c,pad limit 10;

SQL 2: select * from sbtest1 order by c,pad desc limit 10;

SQL 3: select * from sbtest1 order by c desc, pad limit 10;

SQL 4: explain select * from sbtest1 order by c desc, pad desc limit 10;

mysql> explain select * from sbtest1 order by c,pad limit 10;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+------+------+
| 1 | SIMPLE | sbtest1 | NULL | index | NULL | idx_c_pad_1 | 720 | NULL | 10 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+------+------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from sbtest1 order by c,pad desc limit 10;
+----+-------------+---------+------------+------+---------------+-----+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-----+---------+------+--------+----------+----------------+
| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 950738 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+-----+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from sbtest1 order by c desc,pad limit 10;
+----+-------------+---------+------------+------+---------------+-----+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-----+---------+------+--------+----------+----------------+
| 1 | SIMPLE | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 950738 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+-----+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from sbtest1 order by c desc,pad desc limit 10;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
| 1 | SIMPLE | sbtest1 | NULL | index | NULL | idx_c_pad_1 | 720 | NULL | 10 | 100.00 | Backward index scan |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)

(3) Create the corresponding descending index

mysql> alter table sbtest1 add index idx_c_pad_2(c,pad desc);
Query OK, 0 rows affected (1 min 11.27 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table sbtest1 add index idx_c_pad_3(c desc,pad);
Query OK, 0 rows affected (1 min 14.22 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table sbtest1 add index idx_c_pad_4(c desc,pad desc);
Query OK, 0 rows affected (1 min 8.70 sec)
Records: 0 Duplicates: 0 Warnings: 0

(4) When SQL is executed again, the descending index can be used, which greatly improves efficiency

mysql> explain select * from sbtest1 order by c,pad desc limit 10;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+------+------+
| 1 | SIMPLE | sbtest1 | NULL | index | NULL | idx_c_pad_2 | 720 | NULL | 10 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+------+------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from sbtest1 order by c desc,pad limit 10;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+------+------+
| 1 | SIMPLE | sbtest1 | NULL | index | NULL | idx_c_pad_3 | 720 | NULL | 10 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+------+------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from sbtest1 order by c desc,pad desc limit 10;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+------+------+
| 1 | SIMPLE | sbtest1 | NULL | index | NULL | idx_c_pad_4 | 720 | NULL | 10 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+------+------+
1 row in set, 1 warning (0.00 sec)

Summarize

The most important function of the descending index introduced in MySQL 8.0 is to solve the problem that the index may not be used for multi-column sorting, thereby covering more application scenarios.

The above is the details of descending index in MySQL 8.0. For more information about MySQL descending index, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

In-depth explanation of hidden fields, a new feature of 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
The three new indexes added in MySQL 8 are hidden, descending, and functions

<<: How to set up URL link in Nginx server

>>: Why do we need Map when we already have Object in JavaScript?