MySQL 8 新特性之Invisible Indexes

背景

索引是把双刃剑,在提升查询速度的同时会减慢DML的操作。毕竟,索引的维护需要一定的成本。所以,对于索引,要加上该加的,删除无用的。前者是加法,后者是减法。但在实际工作中,大家似乎更热衷于前者,而很少进行后者。究其原因,在于后者,难。难的不是操作本身,而是如何确认一个索引是无用的。

如何确认无用索引

在不可见索引出现之前,大家可以通过sys.schema_unused_indexes来确定无用索引。在MySQL 5.6中,即使没有sys库,也可通过该视图的基表来进行查询。

mysql> show create table sys.schema_unused_indexes\G
*************************** 1. row ***************************
        View: schema_unused_indexes
    Create View: CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`schema_unused_indexes` (
`object_schema`,`object_name`,`index_name`) AS select `t`.`OBJECT_SCHEMA` AS `object_schema`,`t`.`OBJECT_NAME` AS `object_name`,`t`.`INDEX_NAME` AS `index_name` from (`performance_schema`.`table_io_waits_summary_by_index_usage` `t` join `information_schema`.`STATISTICS` `s` on(((`t`.`OBJECT_SCHEMA` = convert(`s`.`TABLE_SCHEMA` using utf8mb4)) and (`t`.`OBJECT_NAME` = convert(`s`.`TABLE_NAME` using utf8mb4)) and (convert(`t`.`INDEX_NAME` using utf8) = `s`.`INDEX_NAME`)))) where ((`t`.`INDEX_NAME` is not null) and (`t`.`COUNT_STAR` = 0) and (`t`.`OBJECT_SCHEMA` <> 'mysql') and (`t`.`INDEX_NAME` <> 'PRIMARY') and (`s`.`NON_UNIQUE` = 1) and (`s`.`SEQ_IN_INDEX` = 1)) order by `t`.`OBJECT_SCHEMA`,`t`.`OBJECT_NAME`character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set, 1 warning (0.00 sec)

但这种方式也有不足,

1. 如果实例发生重启,performance_schema中的数据就会清零。

2. 如果基于上面的查询删除了索引,查询性能突然变差,怎么办?

不可见索引的出现,可有效弥补上述不足。将index设置为invisible,会导致优化器在选择执行计划时,自动忽略该索引,即便使用了FORCE INDEX。

当然,这个是由optimizer_switch变量中use_invisible_indexes选项决定的,默认为off。如果想看一个查询在索引调整前后执行计划的差别,可在会话级别调整use_invisible_indexes的值,如,

mysql> show create table slowtech.t1\G
*************************** 1. row ***************************
   Table: t1
Create Table: CREATE TABLE `t1` (
 `id` int(11) NOT NULL,
 `name` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_name` (`name`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> explain select * from slowtech.t1 where name='a';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra   |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE   | t1  | NULL   | ALL | NULL     | NULL | NULL  | NULL |  6 |  16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from slowtech.t1 where name='a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref | rows | filtered | Extra   |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE   | t1  | NULL   | ref | idx_name   | idx_name | 43   | const |  1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

不可见索引的常见操作

create table t1(id int primary key,name varchar(10),index idx_name (name) invisible);
alter table t1 alter index idx_name visible;
alter table t1 alter index idx_name invisible;

如何查看哪些索引不可见

mysql> select table_schema,table_name,index_name,column_name,is_visible from information_schema.statistics where is_visible='no';
+--------------+------------+------------+-------------+------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | IS_VISIBLE |
+--------------+------------+------------+-------------+------------+
| slowtech  | t1    | idx_name | name    | NO    |
+--------------+------------+------------+-------------+------------+
1 row in set (0.00 sec)

注意

1. 主键索引不可被设置为invisible。

总结

以上所述是小编给大家介绍的MySQL 8 新特性之Invisible Indexes ,希望对大家有所帮助,如果大家有任何疑问欢迎给我留言,小编会及时回复大家的!

您可能感兴趣的文章:

  • MySQL 8.0的关系数据库新特性详解
  • Mysql 8 新特性 window functions 的作用
(0)

相关推荐

  • MySQL 8.0的关系数据库新特性详解

    前言 MySQL 8.0 当前的最新版本是 8.0.4 rc,估计正式版本出来也快了.本文介绍几个 8.0 在关系数据库方面的主要新特性. 你可能已经知道 MySQL 从版本 5.7 开始提供了 NoSQL 存储的功能,在 8.0 中这部分功能也得到了一些改进,但鉴于这个在实际当中用得极少,本人也是没用过,所以本文不会介绍这方面的东西,而是关注其关系数据库方面. 1.隐藏索引 隐藏索引的特性对于性能调试非常有用.在 8.0 中,索引可以被"隐藏"和"显示".当一个索

  • Mysql 8 新特性 window functions 的作用

    MySQL 8.0的新特性包括: 对Unicode 9.0的开箱即用的完整支持 支持窗口函数和递归SQL语法,这在以往是不可能或者很难才能编写这样的查询语句 对原生JSON数据和文档存储功能的增强支持 MySQL 8.0的发布,跳过了多个版本号(从5.5开始),由于6.0修改和7.0是用来保留做MySQL的集群版本,因此采用了8.0的版本号. 1. 问题 Mysql 8.0.2 中新增加了一个主要功能 - 窗口函数 window function 这个功能具体是解决什么问题?下面先看一个SQL查

  • MySQL 8 新特性之Invisible Indexes

    背景 索引是把双刃剑,在提升查询速度的同时会减慢DML的操作.毕竟,索引的维护需要一定的成本.所以,对于索引,要加上该加的,删除无用的.前者是加法,后者是减法.但在实际工作中,大家似乎更热衷于前者,而很少进行后者.究其原因,在于后者,难.难的不是操作本身,而是如何确认一个索引是无用的. 如何确认无用索引 在不可见索引出现之前,大家可以通过sys.schema_unused_indexes来确定无用索引.在MySQL 5.6中,即使没有sys库,也可通过该视图的基表来进行查询. mysql> sh

  • MySQL 8.0新特性之隐藏字段的深入讲解

    前言 MySQL 8.0.23 版本增加了一个新的功能:隐藏字段(Invisible Column),也称为不可见字段.本文给大家介绍一下 MySQL 隐藏字段的相关概念和具体实现. 基本概念 隐藏字段需要在查询中进行显式引用,否则对查询而言是不可见的.MySQL 8.0.23 开始支持隐藏字段,在此之前所有的字段都是可见字段. 考虑以下应用场景,假如一个应用程序使用SELECT *语句访问某个表,并且必需持续不断地进行查询,即使我们为该表增加了一个该应用不需要的新字段时也要求能够正常工作.对于

  • MySQL 5.7新特性介绍

    1. 介绍 身处MySQL这个圈子,能够切身地感受到大家对MySQL 5.7的期待和热情,似乎每个人都迫不及待的想要了解.学习和使用MySQL 5.7.那么,我们不禁要问,MySQL 5.7到底做了哪些改进,引入了哪些新功能,性能又提升了多少,能够让大家翘首以盼,甚至欢呼雀跃呢? 下面就跟随我来一起了解一下MySQL 5.7的部分新功能.想要在一篇文章中介绍完MySQL 5.7的所有改进,几乎是不可能的.所以,我会选择一些有特别意思的.特别有用的功能进行介绍.希望通过这篇文章,能够激发大家对My

  • MySQL 5.6 GTID新特性实践

    GTID简介 什么是GTID GTID(Global Transaction ID)是对于一个已提交事务的编号,并且是一个全局唯一的编号. GTID实际上是由UUID+TID组成的.其中UUID是一个MySQL实例的唯一标识.TID代表了该实例上已经提交的事务数量,并且随着事务提交单调递增.下面是一个GTID的具体形式 3E11FA47-71CA-11E1-9E33-C80AA9429562:23 更详细的介绍可以参见:官方文档 GTID的作用 那么GTID功能的目的是什么呢?具体归纳主要有以下

  • MySQL 8.0 新特性之哈希连接(Hash Join)

    MySQL 开发组于 2019 年 10 月 14 日 正式发布了 MySQL 8.0.18 GA 版本,带来了一些新特性和增强功能.其中最引人注目的莫过于多表连接查询支持 hash join 方式了.我们先来看看官方的描述: MySQL 实现了用于内连接查询的 hash join 方式.例如,从 MySQL 8.0.18 开始以下查询可以使用 hash join 进行连接查询: SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1; Hash join 不需要索引的支

  • MySQL 8.0 新特性之检查约束的实现

    大家好,我是只谈技术不剪发的 Tony 老师.这次我们来介绍一个 MySQL 8.0 增加的新功能:检查约束(CHECK ). SQL 中的检查约束属于完整性约束的一种,可以用于约束表中的某个字段或者一些字段必须满足某个条件.例如用户名必须大写.余额不能小于零等. 我们常见的数据库都实现了检查约束,例如 Oracle.SQL Server.PostgreSQL 以及 SQLite:然而 MySQL 一直以来没有真正实现该功能,直到最新的 MySQL 8.0.16. MySQL 8.0.15 之前

  • MySQL 8.0新特性 — 管理端口的使用简介

    前言 下面这个报错,相信大多数童鞋都遇见过:那么碰到这个问题,我们应该怎么办呢?在MySQL 5.7及之前版本,出现"too many connection"报错,超级用户root也无法登录上去,除了重启实例,没有其他更好的解决办法:不过在MySQL 8.0版本中,是对连接管理做了一些优化,下面我们就来看一下. ERROR 1040 (HY000): Too many connections 连接管理 在MySQL 8.0版本中,对连接管理这一块,是先后做了两个比较大的改变:一个是允许

  • MySQL 8.0新特性 — 检查性约束的使用简介

    前言 在MySQL 8.0版本中,引入了一个非常有用的新特性 - 检查性约束,它可以提高对非法或不合理数据写入的控制能力:接下来我们就来详细了解一下. 检查性约束 创建.删除与查看 (1)可以在建表时,创建检查性约束 mysql> CREATE TABLE t1 -> ( -> CHECK (c1 <> c2), -> c1 INT CHECK (c1 > 10), -> c2 INT CONSTRAINT c2_positive CHECK (c2 >

随机推荐