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:
|
<<: How to set up URL link in Nginx server
>>: Why do we need Map when we already have Object in JavaScript?
Recommend
How to recover data after accidentally deleting ibdata files in mysql5.7.33
Table of contents 1. Scenario description: 2. Cas...
A simple way to implement Vue's drag screenshot function
Drag the mouse to take a screenshot of the page (...
Detailed explanation of the meaning of N and M in the MySQL data type DECIMAL(N,M)
A colleague asked me what N and M mean in the MyS...
JavaScript implements random generation of verification code and verification
This article shares the specific code of JavaScri...
Two ways to introduce svg icons in Vue
How to introduce svg icons in Vue Method 1 of int...
CSS3+Bezier curve to achieve scalable input search box effect
Without further ado, here are the renderings. The...
Vue realizes the palace grid rotation lottery
Vue implements the palace grid rotation lottery (...
Modify the default color of the input box placeholder text -webkit-input-placeholder method
HTML5 adds a native placeholder attribute for inp...
IE8 compatibility notes I encountered
1. IE8's getElementById only supports id, not ...
A brief discussion on MySQL user permission table
MySQL will automatically create a database named ...
Detailed explanation of the error problem of case when statement
Preface In the MySQL database, sometimes we use j...
A Brief Analysis of CSS Selector Grouping
Selector Grouping Suppose you want both the h2 el...
Detailed explanation of using Alibaba Cloud image repository to build foreign Docker images
Download foreign images using Alibaba Cloud Image...
Detailed explanation of HTML form elements (Part 1)
HTML forms are used to collect different types of...
Analyze the usage and principles of Vue's provide and inject
First, let's talk about why we use provide/in...