专栏首页数据和云两千字揭密 MySQL 8.0.19 三大索引新功能:隐藏索引,降序索引,函数索引

两千字揭密 MySQL 8.0.19 三大索引新功能:隐藏索引,降序索引,函数索引

导读:本文详细介绍 MySQL 8.0.19 三大索引新功能,隐藏索引,降序索引,函数索引,结合其他同仁的技术应用案例,进一步进行验证改编,最后总结心得,希望对大家有帮助。

MySQL 8.0 版本带来了3大索引新功能:隐藏索引,降序索引,函数索引,看字面意义,大致也都能猜到那些功能,下面测试实际了解一下。

隐藏索引


MySQL 8.0 支持隐藏索引(invisible index),也称为不可见索引。隐藏索引不会被优化器使用。主键不能设置为隐藏(包括显式设置或隐式设置)

CREATE TABLE `tb_index` (
  `id` bigint NOT NULL ,
  `name` varchar(255) DEFAULT NULL,
  `age` tinyint DEFAULT '10',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `addr` varchar(30) DEFAULT NULL,
  `sex` enum('M','F') DEFAULT NULL,
  PRIMARY KEY(id),
  UNIQUE uni_age (age),
  INDEX idx_addr (addr) ,
  INDEX idx_sex (sex) INVISIBLE,
  FULLTEXT KEY `fullindex_name` (`name`)
) ;

##两种方式验证

mysql>SHOW INDEX FROM `tb_index`;
mysql>SELECT INDEX_NAME, IS_VISIBLE
 FROM INFORMATION_SCHEMA.STATISTICS
 WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 'tb_index'

##隐藏和可见操作

mysql>ALTER TABLE tb_index ALTER INDEX idx_addr INVISIBLE;
mysql>ALTER TABLE tb_index ALTER INDEX idx_addr VISIBLE;

除了主键,其他索引都可以。

对于唯一建:例外情况: 没有主键的情况下,第一个唯一建 不可隐藏,第二个唯一建可隐藏。 从这里可以了解到 MySQL在没有主键的情况下 是把第一个唯一建做为主键。

CREATE TABLE `tb_index2` (
  `id` bigint NOT NULL ,
  `name` varchar(255)  NOT NULL,
  `age` tinyint DEFAULT '10' NOT NULL ,
  `create_time` datetime  NOT NULL,
  `update_time` datetime  NOT NULL,
  `addr` varchar(30)  NOT NULL,
  `sex` enum('M','F')  NOT NULL,
  UNIQUE uni_age (age),
  UNIQUE uni_id(id),
  INDEX idx_addr (addr) ,
  INDEX idx_sex (sex) INVISIBLE,
  FULLTEXT KEY `fullindex_name` (`name`)
) ;
##隐藏和可见操作
```sql
mysql>ALTER TABLE tb_index ALTER INDEX uni_age INVISIBLE;
mysql>ALTER TABLE tb_index ALTER INDEX uni_age VISIBLE;
mysql>ALTER TABLE tb_index ALTER INDEX uni_id INVISIBLE;
mysql>ALTER TABLE tb_index ALTER INDEX uni_id VISIBLE;

执行计划: 系统变量 optimizer_switch 的 use_invisible_indexes 值控制了优化器构建执行计划时是否使用隐藏索引。如果设置为 off (默认值),优化器将会忽略隐藏索引(与引入该属性之前的行为相同)。如果设置为 on,隐藏索引仍然不可见,但是优化器在构建执行计划时将会考虑这些索引。

mysql> SHOW VARIABLES LIKE '%optimizer_switch%';

##当name索引隐藏的时候

mysql>ALTER TABLE tb_index ALTER INDEX idx_name INVISIBLE;

#当name索引显示的时候

mysql>ALTER TABLE tb_index ALTER INDEX idx_name VISIBLE;

#optimizer_switch进行控制

mysql>ALTER TABLE tb_index ALTER INDEX idx_name INVISIBLE;
mysql>set optimizer_switch='use_invisible_indexes=on';
mysql> explain select * from tb_index where age =12 and name ='9961139@qq.com';

好的方面的optimizer_switch=‘use_invisible_indexes=on’ session级别,特定的时间可以通过参数进行使用。

总结:

不可见索引特性可以用于测试删除某个索引对于查询性能的影响,同时又不需要真正删除索引,也就避免了错误删除之后的索引重建。对于一个大表上的索引进行删除重建将会非常耗时,而将其设置为不可见或可见将会非常简单快捷。

如果某个设置为隐藏的索引实际上仍然需要或者被优化器所使用,可以通过以下多种方法发现缺少该索引带来的影响:

  • 索引提示中使用了该索引的查询将会产生错误;
  • 性能模式(Performance Schema)中的数据显示受影响查询的负载升高;
  • EXPLAIN 语句显示了不同的查询执行计划;
  • 慢查询日志中出现了新的查询语句。

需要思考的问题: 增删改操作,本身会进行索引的维护,隐藏索引是否有必要创建。

降序索引


MySQL8.0开始真正支持降序索引,只有InnoDB引擎支持降序索引,且必须是BTREE降序索引,MySQL8.0不在对group by操作进行隐式排序。

查看官方: MySQL支持降序索引:索引定义中的DESC不再被忽略,而是按降序存储键值。以前,可以以相反的顺序扫描索引,但是会导致性能损失。下行索引可以按前向顺序扫描,效率更高。当最有效的扫描顺序混合了某些列的升序和其他列的降序时,降序索引也使优化器能够使用多列索引。

注意:

  • Ddescending索引只支持InnoDB存储引擎
  • 具有降序关键部分的索引不用于对调用聚合函数但没有GROUP BY子句的查询进行MIN()/MAX()优化
  • 降序索引:BTREE支持,但不支持哈希索引,全文索引或空间索引
  • DISTINCT可以使用包含匹配列(包括降序关键部分)的任何索引。
  • 对于所有可用升序索引的数据类型,都支持降序索引。

语法如下,用法简单,需要考虑索引维护的成本和实际使用场景。对于MAX,MIN,DISTINCT 降序索引进行优化。

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

函数索引

MySQL版本需要是5.7及以上版本才支持建立函数索引(虚拟列方式),MySQL 8.0.13 以及更高版本支持函数索引(functional key parts),也就是将表达式的值作为索引的内容,而不是列值或列值前缀。将函数作为索引键可以用于索引那些没有在表中直接存储的内容。 ##引用上面的表tb_index

mysql>ALTER TABLE tb_index  ADD INDEX idx_created (create_time);
Mysql>EXPLAIN SELECT id ,name ,age ,create_time,update_time ,month(create_time)
FROM tb_index
WHERE  month(create_time) =8
mysql>ALTER TABLE tb_index  ADD INDEX idx_created_fun((month(create_time)));
  • 函数索引支持UNIQUE选项。但是,主键不能包含函数列。主键只能使用存储的计算列,但是函数索引使用虚拟计算列实现,而不是存储计算列。
  • 空间SPATIAL 索引和 全文FULLTEXT 索引不支持函数索引。
  • 如果某个表中没有主键,InnoDB 存储引擎自动将第一个 UNIQUE NOT NULL 索引提升为主键。但是对于包含函数列的 UNIQUE NOT NULL 索引不会进行提升。
  • 如果要删除的字段上有函数索引,必须先删除函数索引,才能删除字段。

总结:


以上介绍内容虽然给带来了多样性的功能,但怎样更有效的使用,才是问题关键。特别是隐藏索引,多一个索引就是多出一份维护成本。

墨天轮原文链接:https://www.modb.pro/db/22619

本文分享自微信公众号 - 数据和云(OraNews),作者:崔虎龙

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-03-16

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL史上最全性能优化方式

    MySQL有哪些性能优化方式?这个问题可以涉及到 MySQL 的很多核心知识,就像要考你计算机网络的知识时,问你“输入URL回车之后,究竟发生了什么”一样,看看...

    数据和云
  • 如何理解并正确使用MySql索引

    索引是存储引擎用于快速查找记录的一种数据结构,通过合理的使用数据库索引可以大大提高系统的访问性能,本文主要介绍在MySql数据库中索引类型,以及如何创建出更加合...

    数据和云
  • 你真的会用索引吗?来看看COUNT(*)到底能有多快

    作者简介 ? 案例说明 一个大表的COUNT,究竟能有多快?除类似物化视图的做法,我们所能做到的极限能有多快?这不是一个真实的案例,而是根据笔者在网上发的一篇帖...

    数据和云
  • Oracle索引种类介绍

    逻辑上: Single column 单行索引 Concatenated 多行索引 Unique 唯一索引 NonUnique 非唯一索引 Funct...

    葆宁
  • MySQL InonoDB 索引

    InnoDB支持的哈希索引是自适应的,InnoDB会根据表的使用情况自动为表生成哈希索引,不能人为干预在表中生产哈希索引

    yingzi_code
  • 索引入门:顺序索引

    之前我对索引的了解基本就是主索引和二级索引,此外还经常见到一些其他概念,如聚集索引和非聚集索引,稀疏索引和密集索引等,今天系统整理一下。

    Apache IoTDB
  • 还不懂MySQL索引?这1次彻底搞懂B+树和B-树

    看了很多关于索引的博客,讲的大同小异。但是始终没有让我明白关于索引的一些概念,如B-Tree索引,Hash索引,唯一索引….或许有很多人和我一样,没搞清楚概念就...

    程序员追风
  • Mysql索引使用的正确姿势

    今天给大家简单的介绍一下mysql的索引用法,像在我们日常业务开发中,最核心的其实就是写SQL命令,但是你写的SQL真的用到索引了吗?

    林老师带你学编程
  • 数据库中的联合索引

    用户1637228
  • linux一些基本命令与mysql索引

    在Linux中大部分的操作都是通过终端来实现的,或者直接使用命令行界面,一般使用Linux架构服务器的话,安装的都是命令行界面,不安装图形化界面的原因是因为没有...

    端碗吹水

扫码关注云+社区

领取腾讯云代金券