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
Docker Nginx container and Tomcat container to achieve load balancing and dynamic and static separation operations
Download Tomcat8 image [root@localhost ~]# docker...
A Preliminary Study on JSBridge in Javascript
Table of contents The origin of JSBridge The bidi...
Install JDK8 in rpm mode on CentOS7
After CentOS 7 is successfully installed, OpenJDK...
Implementation of CSS border length control function
In the past, when I needed the border length to b...
Calling the search engine in the page takes Baidu as an example
Today, it suddenly occurred to me that it would be...
How to use the Linux more command in Linux common commands
more is one of our most commonly used tools. The ...
Writing methods that should be prohibited in native JS
Table of contents Block-level functions Directly ...
Completely uninstall MySQL database in Windows system to reinstall MySQL
1. In the control panel, uninstall all components...
MySQL permission control detailed explanation
Table of contents mysql permission control Permis...
How to modify mysql to allow remote connections
Regarding the issue of MySQL remote connection, w...
MySQL uses variables to implement various sorting
Core code -- Below I will demonstrate the impleme...
MySQL learning database backup detailed explanation
Table of contents 1.DB,DBMS,SQL 2. Characteristic...
Common naming rules for CSS classes and ids
Public name of the page: #wrapper - - The outer e...
Solution to overflow of html table
If the table is wide, it may overflow. For exampl...
Detailed steps to store emoji expressions in MySQL
Caused by: java.sql.SQLException: Incorrect strin...