MySQL8.0索引新特性—支持降序索引与支持隐藏索引

关注

原创最新推荐文章于 2025-02-22 09:11:07 发布 1.1k 阅读 AI 写同款· GEO 优化 ›

MySQL 8.0新增的三种索引方式在MySQL 8.0中，只需要将这个索引先设置为隐藏索引，使查询优化器不再使用这个索引，但是，此时这个索引还是需要MySQL后台进行维护，当确认将这个索引设置为隐藏索引系统不会受到影响时，再将索引彻底删除。灰度发布，就是说创建索引时，首先将索引设置为隐藏索引，通过修改查询优化器的开关，使隐藏索引对查询优化器可见，通过explain对索引进行测试，确认这个索引有效，某些查询可以使用到这个索引，就可以将其设置为可见索引，完成灰度发布的效果。隐藏索引又叫不可见索引，主要应用于索引的软删除和灰度发布。阅读详情

前言

MySQL中的索引可以为提高我们的查询效率，相比较于低版本, MySQL 8.0中针对索引做了不少的优化，本文主要分享下MySQL8.0中关于索引的两个新特性，这两个新特性都非常好用，希望大家可以在日常的开发中根据实际场景用起来。

支持索引降序排序

降序索引说明

在建立的索引可以指定索引的顺序，命令如下：

ALTER TABLE table_name ADD [UNIQUE] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC] 复制代码

ASC表示升序、DESC表示索引降序排序
从MySQL4版本中就开始支持DESC的语法，但是实际上会忽略它，指导MySQL8.x innoDB存储引擎才支持降序排序。

例子：在MySQL5.x中创建DESC降序索引无效

在MySQL5.7.16中创建降序索引

create table student ( id_ int(10) AUTO_INCREMENT, student_no varchar(50), student_name varchar(200), update_time datetime, primary key (id_), unique index idx_no(student_no desc) ) 复制代码

创建了降序索引idx_no

我们用show create table student;查看表的信息。

同样，我们在MySQL8中查看表的信息，如下图：

降序索引好处

你可能疑惑了，那我为什么要设置索引为降序，有什么好处呢？

其实这和我们实际的业务场景息息相关，比如我们的场景中如果存在一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能。如果只对单个列进行排序，降序索引的意义不是太大，无论是升序还是降序，升序索引完全可以应付。详细内容请参考文章：www.cnblogs.com/ivictor/p/9…。

那么降序索引的底层数据结构是什么样的呢？下图是基于student表建立的降序索引对应的B+树结构：

页10和页12是对应的数据页，存放数据，其中update_time字段对应的值是降序排序，这也是DESC的体现。
页20是目录页，存放索引，它也是降序排序的。

索引数据结构详细请参考：一步步带你设计MySQL索引数据结构。

支持索引的隐藏

隐藏索引的说明

MySQL 8.0 支持了 Invisible Indexes 隐藏索引这个特性，可以把某个索引设置为对优化器不可见，生成查询计划时便不使用这个索引了，但这个索引还是被正常维护的，例如表数据变更后还是会更新索引。

我们可以通过在创建索引的时候设置索引的隐藏属性，如下：

ALTER TABLE tablename ADD INDEX indexname (propname [(length)]) INVISIBLE; 复制代码

也可以对已经存在的索引切换显示或者隐藏，语法如下：

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引 ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引 复制代码

隐藏索引的好处

众所周知，索引的维护是需要极大的成本的，数据量越大，建立索引花费的成本也就越大。但是，往往我们在调优的时候有这样的需求，我们想要看看禁用掉这个索引对查询性能的影响，如果在8.0版本以前，都是要删除这个索引，发现这个索引有用的，又要加回来，极大的增加了操作成本。

所以隐藏索引最明显的一个作用类似索引回收站。

例如数据库长时间运行后，会积累很多索引，做数据库优化时，想清理掉没什么用的多余的索引，但可能删除某个索引后，数据库性能下降了，发现这个索引时有用的，就要重新建立。这时候隐藏索引就派上用场了。

总结

本文分享了MySQL8.0中关于索引的两个新特性，一个是支持降序索引，另外一个是支持隐藏索引，他们都是有着非常强的实用价值的。如果本文对你有帮助，请留下一个赞吧。

标签

#数据库 #mysql #java #程序人生 #开发语言

本内容遵循 CC 4.0 BY-SA 版权协议

14.7 降序索引 14.7　降序索引从MySQL 4版本开始就已经支持降序索引的语法了，但是直到MySQL 8.x版本才开始真正支持降序索引。本节就对比MySQL 5.7与MySQL 8.x中对降序索引的处理来介绍MySQL 8.x中的降序索引特性。 14.7.1　降序索引概述 MySQL 8.x版本中另一项重要的新特性就是真正开始支持降序索引，但是只有MySQL的InnoDB存储引擎... 阅读详情