MySQL8.0索引新特性

文章目录

      • 1 支持降序索引
      • 2 隐藏索引


在这里插入图片描述

在这里插入图片描述


1 支持降序索引

image.png
举例：分别在MySQL 5.7版本和MySQL 8.0版本中创建数据表ts1，结果如下：

CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc)
);

在MySQL 5.7版本中查看数据表ts1的结构，从结果可以看出，索引仍然是默认的升序。

在MySQL 8.0版本中查看数据表ts1的结构，从结果可以看出，索引已经是降序了。下面继续测试降序索引在执行计划中的表现。

分别在MySQL 5.7版本和MySQL 8.0版本的数据表ts1中插入800条随机数据，执行语句如下：

DELIMITER //
CREATE PROCEDURE ts_insert()
BEGINDECLARE i INT DEFAULT 1;WHILE i < 800DOinsert into ts1 select rand()80000,rand()80000;SET i = i + 1;END WHILE;commit;
END //
DELIMITER ;# 调用
CALL ts_insert();


在MySQL 5.7版本中查看数据表ts1的执行计划，结果如下：

EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;

从结果可以看出，执行计划中扫描数为799，而且使用了Using filesort。
提示 Using filesort是MySQL中一种速度比较慢的外部排序，能避免是最好的。多数情况下，管理员
可以通过优化索引来尽量避免出现Using filesort，从而提高数据库执行速度。

在MySQL 8.0版本中查看数据表ts1的执行计划。从结果可以看出，执行计划中扫描数为5，而且没有使用
Using filesort。
注意 降序索引只对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低。例如，上述
查询排序条件改为order by a desc, b desc，MySQL 5.7的执行计划要明显好于MySQL 8.0。

将排序条件修改为order by a desc, b desc后，下面来对比不同版本中执行计划的效果。 在MySQL 5.7版本
中查看数据表ts1的执行计划，结果如下：

EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;

在MySQL 8.0版本中查看数据表ts1的执行计划。
从结果可以看出，修改后MySQL 5.7的执行计划要明显好于MySQL 8.0。

2 隐藏索引

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能
通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较
大，这种操作就会消耗系统过多的资源，操作成本非常高。

从MySQL 8.x 开始支持 隐藏索引（invisible indexes） ，只需要将待删除的索引设置为隐藏索引，使
查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），
确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。 这种通过先将索引设置为隐藏索
引，再删除索引的方式就是软删除 。

同时你想验证某个索引删除之后的 查询性能影响就可以暂时先隐藏起来。

注意：主键不能被设置为隐藏索引。当表中没有显示主键时，表中的第一个唯一非空索引会成为隐式主键，也不能设置为隐藏索引。

image.png

  1. 创建表时直接创建

在MySQL中创建隐藏索引通过SQL语句INVISIBLE来实现，其语法形式如下：

CREATE TABLE tablename(
propname1 type1[CONSTRAINT1],
propname2 type2[CONSTRAINT2],
……
propnamen typen,
INDEX [indexname](propname1 [(length)]) INVISIBLE
);

上述语句比普通索引多了一个关键字INVISIBLE，用来标记索引为不可见索引。

  1. 在已经存在的表上创建
    可以为已经存在的表设置隐藏索引，其语法形式如下：
CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;
  1. 通过ALTER TABLE语句创建
    语法形式如下：
ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;
  1. 切换索引可见状态 已存在的索引可通过如下语句切换可见状态：
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

如果将index_cname索引切换成可见状态，通过explain查看执行计划，发现优化器选择了index_cname索
引。
注意 当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐
藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

通过设置隐藏索引的可见性可以查看索引对调优的帮助。

  1. 使隐藏索引对查询优化器可见
    在MySQL 8.x版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开关
    （use_invisible_indexes）来打开某个设置，使隐藏索引对查询优化器可见。如果 use_invisible_indexes
    设置为off(默认)，优化器会忽略隐藏索引。如果设置为on，即使隐藏索引不可见，优化器在生成执行计
    划时仍会考虑使用隐藏索引。


（1）在MySQL命令行执行如下命令查看查询优化器的开关设置。

mysql> select @@optimizer_switch \G

在输出的结果信息中找到如下属性配置

use_invisible_indexes=off

此属性配置值为off，说明隐藏索引默认对查询优化器不可见。

（2）使隐藏索引对查询优化器可见，需要在MySQL命令行执行如下命令：

mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)

SQL语句执行成功，再次查看查询优化器的开关设置。

mysql> select @@optimizer_switch \G
*************************** 1. row ***************************
@@optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_
intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_co
st_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on
,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on
,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_ind
exes=on,skip_scan=on,hash_join=on
1 row in set (0.00 sec)

此时，在输出结果中可以看到如下属性配置

use_invisible_indexes=on

use_invisible_indexes属性的值为on，说明此时隐藏索引对查询优化器可见。

（3）使用EXPLAIN查看以字段invisible_column作为查询条件时的索引使用情况。

explain select * from classes where cname = '高一2班';

查询优化器会使用隐藏索引来查询数据。

（4）如果需要使隐藏索引对查询优化器不可见，则只需要执行如下命令即可。

mysql> set session optimizer_switch="use_invisible_indexes=off";
Query OK, 0 rows affected (0.00 sec)

再次查看查询优化器的开关设置

mysql> select @@optimizer_switch \G

此时，use_invisible_indexes属性的值已经被设置为“off”。


MySQL8忘记密码：https://blog.csdn.net/weixin_44714097/article/details/127055715



在这里插入图片描述

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/278211.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

【SpringBoot】请求与响应参数 IoC与DI 总结

【SpringBoot】请求与响应参数 IoC与DI 总结

文章目录 ① —— 请求 ——一、简单参数 RequestParam1.1 参数与形参 命名相同1.2 参数与形参 命名不同 二、实体参数2.1 简单实体对象2.2 复杂实体对象 三、数组集合参数3.1 数组3.2 集合 RequestParam 四、日期参数 DateTimeFormat五、JSON参数 RequestBody六、路径参数 Pat…
阅读更多...
下载chromedrive，使用自动化

下载chromedrive，使用自动化

1、先看一下自己浏览器的版本 2、访问 https://googlechromelabs.github.io/chrome-for-testing/
阅读更多...
QT中dumpcpp以及dumpdoc使用

QT中dumpcpp以及dumpdoc使用

qt中调用COM的方式方法有四种&#xff0c;参考解释在 Qt 中使用 ActiveX 控件和 COM (runebook.dev) 介绍dumpcpp的使用方法Qt - dumpcpp 工具 (ActiveQt) (runebook.dev)&#xff1a; 在安装好了的qt电脑上&#xff0c;通过powershell窗口来实现&#xff0c;powershell比cmd要…
阅读更多...
算法——贪心

算法——贪心

「贪心的本质是选择每一阶段的局部最优&#xff0c;从而达到全局最优」 贪心无套路 1. 分发饼干 贪心策略&#xff1a; &#xff08;1&#xff09;局部最优就是大饼干喂给胃口大的&#xff0c;充分利用饼干尺寸喂饱一个&#xff0c;全局最优就是喂饱尽可能多的小孩 &#xff08…
阅读更多...
广州大彩科技新品发布:大彩科技COF系列2.4寸串口屏发布！

广州大彩科技新品发布:大彩科技COF系列2.4寸串口屏发布！

一、产品介绍 此次发布的是S系列平台2.4寸COF超薄结构串口屏&#xff0c;分辨率为240*320&#xff0c;该平台采用了Cortex-M3内核的处理器&#xff0c;内置了2Mbyte PSRAM和64Mbit FLASH&#xff0c;是专为小尺寸串口屏设计的MCU&#xff0c;精简了外围电路。 该平台默认支持大…
阅读更多...
element ui 中文离线文档(百度云盘下载)

element ui 中文离线文档(百度云盘下载)

一般内网开发上不了网&#xff0c;用离线版本比较方便&#xff0c;下载地址&#xff1a; https://download.csdn.net/download/li836779537/88355878?spm1001.2014.3001.5503 下载后里面有个 index.hrml 双击打开就可以用 效果如下&#xff1a;
阅读更多...
2024.3.14jsp(2)

2024.3.14jsp(2)

一、实验目的 掌握eclipse开发工具的使用&#xff1b;jsp标记、如指令标记&#xff0c;动作标记&#xff1b;变量和方法的声明&#xff1b;Java程序片&#xff1b; 实验&#xff1a;看电影 源代码watchMovie.jsp <% page language"java" contentType"text…
阅读更多...
STM32/GD32——FreeRTOS任务管理与相关机制

STM32/GD32——FreeRTOS任务管理与相关机制

芯片选型 Ciga Device — GD32F470系列 任务管理 任务处理API 操作 API 动态任务创建 xTaskCreate 任务删除 vTaskDelete 静态任务创建 vTaskCreateStatic 挂起任务 vTaskSuspend 恢复任务 vTaskResume 任务创建 BaseType_t xTaskCreate( TaskFunction_t pxTa…
阅读更多...
【GPT-SOVITS-05】SOVITS 模块-残差量化解析

【GPT-SOVITS-05】SOVITS 模块-残差量化解析

说明&#xff1a;该系列文章从本人知乎账号迁入&#xff0c;主要原因是知乎图片附件过于模糊。 知乎专栏地址&#xff1a; 语音生成专栏 系列文章地址&#xff1a; 【GPT-SOVITS-01】源码梳理 【GPT-SOVITS-02】GPT模块解析 【GPT-SOVITS-03】SOVITS 模块-生成模型解析 【G…
阅读更多...
FPGA高端项目：FPGA基于GS2971+GS2972架构的SDI视频收发+HLS多路视频融合叠加，提供1套工程源码和技术支持

FPGA高端项目：FPGA基于GS2971+GS2972架构的SDI视频收发+HLS多路视频融合叠加，提供1套工程源码和技术支持

目录 1、前言免责声明 2、相关方案推荐本博已有的 SDI 编解码方案本方案的SDI接收发送本方案的SDI接收图像缩放应用本方案的SDI接收纯verilog图像缩放纯verilog多路视频拼接应用本方案的SDI接收HLS图像缩放HLS多路视频拼接应用本方案的SDI接收OSD多路视频融合叠加应用本方案的S…
阅读更多...
基于Linux内核的socket编程(TCP)的C语言示例

基于Linux内核的socket编程(TCP)的C语言示例

原文地址&#xff1a;https://www.geeksforgeeks.org/socket-programming-cc/ 服务端&#xff1a; #include <netinet/in.h> #include <stdio.h> #include <stdlib.h> #include <string.h> #include <sys/socket.h> #include <unistd.h>#…
阅读更多...
Annaconda环境下ChromeDriver配置及爬虫编写

Annaconda环境下ChromeDriver配置及爬虫编写

Anaconda环境的chromedriver安装配置_anaconda 配置chromedriver-CSDN博客 Chromedriver驱动( 121.0.6167.85 ) - 知乎 下载好的驱动文件解压&#xff0c;将exe程序复制到Annaconda/Scripts目录以及Chrome/Application目录下 注意要提前pip install selenium包才能运行成功&a…
阅读更多...
【linux深入剖析】操作系统与用户之间的接口：自定义简易shell制作全过程

【linux深入剖析】操作系统与用户之间的接口：自定义简易shell制作全过程

&#x1f341;你好&#xff0c;我是 RO-BERRY &#x1f4d7; 致力于C、C、数据结构、TCP/IP、数据库等等一系列知识 &#x1f384;感谢你的陪伴与支持 &#xff0c;故事既有了开头&#xff0c;就要画上一个完美的句号&#xff0c;让我们一起加油 目录 1.shell2.自定义shell的准…
阅读更多...
理财第一课：炒股词典

理财第一课：炒股词典

文章目录 基础代码规则委比委差量比换手率市盈率市净率 散户亏钱的原因庄家分析炒股战法波浪理论其它 钱者&#xff0c;人生之大事&#xff0c;死生存亡之地&#xff0c;不可不察也。耕田之利&#xff0c;十倍&#xff1b;珠玉之赢&#xff0c;百倍&#xff1b;闹革命&#xff…
阅读更多...
Spring6--基础概念

Spring6--基础概念

1. 概述 1.1. Spring是什么 Spring 是一套广泛应用于 Java 企业级应用开发领域的轻量级开源框架&#xff0c;由 Rod Johnson 创立&#xff0c;旨在显著降低 Java 企业应用的复杂性&#xff0c;缩短开发周期&#xff0c;并提升开发效率。Spring 不仅适用于服务器端开发&#x…
阅读更多...
＜Senior High School Math＞: inequality question

＜Senior High School Math＞: inequality question

( 1 ) . o m i t (1). omit (1).omit ( 2 ) . ( a 2 − b 2 ) ( x 2 a 2 − y 2 b 2 ) ( x 2 y 2 ) − ( a 2 y 2 b 2 b 2 x 2 a 2 ) ≤ x 2 y 2 − 2 x y ( x − y ) 2 (2). (a^2-b^2)(\frac{x^2}{a^2} - \frac{y^2}{b^2})(x^2y^2)-(\frac{a^2y^2}{b^2}\frac{b^2x^2}{a^…
阅读更多...
Mysql 死锁案例4-delete 相邻记录导致死锁

Mysql 死锁案例4-delete 相邻记录导致死锁

死锁复现 CREATE TABLE t (id int(11) NOT NULL,c int(11) DEFAULT NULL,d int(11) DEFAULT NULL,PRIMARY KEY (id),KEY c (c) ) ENGINEInnoDB DEFAULT CHARSETutf8;/*Data for the table t */insert into t(id,c,d) values (0,0,0),(5,5,5),(10,10,10),(15,15,15) 事务1事…
阅读更多...
Python深度学习之路：TensorFlow与PyTorch对比【第140篇—Python实现】

Python深度学习之路：TensorFlow与PyTorch对比【第140篇—Python实现】

&#x1f47d;发现宝藏 前些天发现了一个巨牛的人工智能学习网站&#xff0c;通俗易懂&#xff0c;风趣幽默&#xff0c;忍不住分享一下给大家。【点击进入巨牛的人工智能学习网站】。 Python深度学习之路&#xff1a;TensorFlow与PyTorch对比 在深度学习领域&#xff0c;Tens…
阅读更多...
【数学建模】线性规划

【数学建模】线性规划

针对未来可能的数学建模比赛内容&#xff0c;我对学习的内容做了一些调整&#xff0c;所以先跳过灰色关联分析和模糊综合评价的代码&#xff0c;今天先来了解一下运筹规划类——线性规划模型。 背景&#xff1a; 某数学建模游戏有三种题型&#xff0c;分别是A&#xff0c;B&am…
阅读更多...
Cookie 信息泄露 Cookie未设置http only属性 原理以及修复方法

Cookie 信息泄露 Cookie未设置http only属性 原理以及修复方法

漏洞名称&#xff1a;Cookie信息泄露、Cookie安全性漏洞、Cookie未设置httponly属性 漏洞描述&#xff1a; cookie的属性设置不当可能会造成系统用户安全隐患&#xff0c;Cookie信息泄露是Cookiehttp only配置缺陷引起的&#xff0c;在设置Cookie时&#xff0c;可以设置的一个…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023