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
Detailed explanation of the example of exporting data from a specified table in MySQL
Detailed explanation of MySQL exporting data from...
Detailed explanation of Nginx version smooth upgrade solution
Table of contents background: Nginx smooth upgrad...
Using iframe techniques to obtain visitor QQ implementation ideas and sample code
Today at work, a friend I added temporarily asked ...
How to construct a table index in MySQL
Table of contents Supports multiple types of filt...
js realizes a gradually increasing digital animation
Table of contents background Achieve a similar ef...
mysql8.0.23 msi installation super detailed tutorial
1. Download and install MySql Download MySql data...
SQL Practice Exercise: Online Mall Database Product Category Data Operation
Online shopping mall database-product category da...
Flex layout achieves fixed number of rows per line + adaptive layout
This article introduces the flex layout to achiev...
Detailed explanation of how to use amoeba to implement read-write separation of MySQL database
There are many read-write separation architecture...
Summary of Linux user groups and permissions
User Groups In Linux, every user must belong to a...
Tutorial on building nextcloud personal network disk with Docker
Table of contents 1. Introduction 2. Deployment E...
Troubleshooting and solutions for MySQL auto-increment ID oversize problem
introduction Xiao A was writing code, and DBA Xia...
SQL group by to remove duplicates and sort by other fields
need: Merge identical items of one field and sort...
Vue3 Vue CLI multi-environment configuration
Table of contents 1. Introduction 2. Switching 1....
Detailed explanation of MySQL and Spring's autocommit
1 MySQL autocommit settings MySQL automatically c...