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


    前言

    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降序索引无效

    1. 在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

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

    1. 同样,我们在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中关于索引的两个新特性,一个是支持降序索引,另外一个是支持隐藏索引,他们都是有着非常强的实用价值的。如果本文对你有帮助,请留下一个赞吧。

  • 相关阅读:
    智能运维应用之道,告别企业数字化转型危机
    2022年亚太杯APMCM数学建模大赛D题储能系统中传热翅片的结构优化求解全过程文档及程序
    early_init_dt_scan_memory
    第八章《Java高级语法》第10节:注解
    .net生鲜超市销售系统
    Python-数据爬取(爬虫)
    ASP.NET Core 3 高级编程(第8版) 学习笔记 03
    从字符串中删除指定字符
    异常:no transaction is in progress
    View体系简析
  • 原文地址:https://blog.csdn.net/Java_LingFeng/article/details/127763573