≡
  • 网络编程
  • 数据库
  • CMS技巧
  • 软件编程
  • PHP笔记
  • JavaScript
  • MySQL
位置：首页 > 数据库 > MySQL

MySQL8.0降序索引的简单示例

人气：248 时间：2021-07-16

这篇文章主要为大家详细介绍了MySQL8.0降序索引的简单示例，具有一定的参考价值，可以用来参考一下。

感兴趣的小伙伴，下面一起跟随四海网的小玲来看看吧！

前言

相信大家都知道，索引是有序的；不过，在MySQL之前版本中，只支持升序索引，不支持降序索引，这会带来一些问题；在最新的MySQL 8.0版本中，终于引入了降序索引，接下来我们就来看一看。

降序索引

 

 

 

单列索引

 

（1）查看测试表结构

代码如下：



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)

MySQL8.0中的降序索引

（2）执行SQL语句order by ... limit n，默认是升序，可以使用到索引

代码如下：



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)

MySQL8.0中的降序索引

（3）执行SQL语句order by ... desc limit n，如果是降序的话，无法使用索引，虽然可以相反顺序扫描，但性能会受到影响

代码如下：



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)

MySQL8.0中的降序索引

（4）创建降序索引

代码如下：



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

MySQL8.0中的降序索引

（5）再次执行SQL语句order by ... desc limit n，可以使用到降序索引

代码如下：



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)

MySQL8.0中的降序索引

 

多列索引

 

（1）查看测试表结构

代码如下：



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)

MySQL8.0中的降序索引

（2）对于多列索引来说，如果没有降序索引的话，那么只有SQL 1才能用到索引，SQL 4能用相反顺序扫描，其他两条SQL语句只能走全表扫描，效率非常低

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)

MySQL8.0中的降序索引

（3）创建相应的降序索引

代码如下：



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

MySQL8.0中的降序索引

（4）再次执行SQL，均能使用到降序索引，效率大大提升

代码如下：



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)

MySQL8.0中的降序索引

 

总结

 

MySQL 8.0引入的降序索引，最重要的作用是，解决了多列排序可能无法使用索引的问题，从而可以覆盖更多的应用场景。

以上就是MySQL8.0中的降序索引的详细内容，更多关于MySQL 降序索引的资料请关注四海网其它相关文章！

本文来自：http://www.q1010.com/177/19577-0.html

注：关于MySQL8.0降序索引的简单示例的内容就先介绍到这里，更多相关文章的可以留意四海网的其他信息。

关键词：

您可能感兴趣的文章

上一篇：CentOS7.5 安装MySQL的简单示例
下一篇：MySQL group by和order by示例示例
热门文章
  • mysql 修改character_set_server为utf-8的简单示例
  • 解决MySQL丢失文件localhost.localdomain.pid、mysql.sock的示例
  • MySQL 数据类型binary和varbinary的简单示例
  • MySQL:reading initial communication packet问题解决方法
  • MySql 表类型MYISAM、InnoDB区别
  • bash: mysql: command not found 的解决方法
  • MYSQL默认用户名ROOT修改方法
  • MySQL 常用命令菜鸟教程
  • MySQL 使用命令行新建用户并授予权限
  • MySql 数据库物理文件存放位置查看示例
  • 最新文章
    • MySQL查看死锁与解除死锁的简单示例
    • MySQL 慢查询的功能实例
    • MySQL查看死锁与去除死锁的简单示例
    • MySQL找出未提交事务的SQL的简单示例
    • MySQL锁阻塞的的简单示例
    • MySQL中的binary类型使用操作的示例
    • SQL优化教程之in与range查询的简单示例
    • MySQL 的 21 个规范、优化最佳实践！
    • MySQL 字符类型大小写敏感的简单示例
    • 解决mybatis-plus分页传入参数后sql where条件没有limit分页信息的问题

四海网收集整理一些常用的php代码,JS代码,数据库mysql等技术文章。