【常见索引使用】⭐️Mysql中索引的类型以及使用方式和失效场景

news/2024/4/24 16:54:11/文章来源:https://blog.csdn.net/TM007_/article/details/136505048

目录

一、前言

二、数据准备

三、索引的分类

四、索引示例

示例1、主键索引(Primary Key Index)与 唯一索引(Unique Index)

示例2、前缀索引(Prefix Index)

示例3、联合索引(复合索引)

五、索引失效场景(较多,演示两个实例)


一、前言

        朋友们大家好啊,在数据库的性能优化和调优过程中,索引起到了不可小觑的作用,并且索引分为了很多种,本文是在InnoDB存储引擎下测试索引的使用

二、数据准备

        1.创建用例表(这里随便从本地库中找张表,挑了个xxljob的日志表,补充点数据)

CREATE TABLE `xxl_job_log` (`id` bigint NOT NULL AUTO_INCREMENT,`job_group` int NOT NULL COMMENT '执行器主键ID',`job_id` int NOT NULL COMMENT '任务,主键ID',`executor_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '执行器地址,本次执行的地址',`executor_handler` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '执行器任务handler',`executor_param` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '执行器任务参数',`executor_sharding_param` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '执行器任务分片参数,格式如 1/2',`executor_fail_retry_count` int NOT NULL DEFAULT '0' COMMENT '失败重试次数',`trigger_time` datetime DEFAULT NULL COMMENT '调度-时间',`trigger_code` int NOT NULL COMMENT '调度-结果',`trigger_msg` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '调度-日志',`handle_time` datetime DEFAULT NULL COMMENT '执行-时间',`handle_code` int NOT NULL COMMENT '执行-状态',`handle_msg` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '执行-日志',`alarm_status` tinyint NOT NULL DEFAULT '0' COMMENT '告警状态:0-默认、1-无需告警、2-告警成功、3-告警失败',PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

        2、需要借助SQL的explain命令来查看数据执行的结果,先来看下每个参数

  • id: 表示查询中执行 SELECT 语句或子查询的序列号。

  • select_type: 表示查询的类型,常用的值有 SIMPLE、PRIMARY、SUBQUERY、DERIVED 等。

  • table: 指示查询涉及的表名。

  • partitions: 表示查询涉及的分区信息,如果查询中涉及到了分区表,则会显示分区的信息。

  • type: 表示 MySQL 在表中找到所需行的方式,常见的值有 ALL、index、range 等。

  • possible_keys: 表示可能应用在这张表中的索引。

  • key: 实际使用的索引。如果为 NULL,则表示没有使用索引;如果为 PRIMARY,则表示使用了主键索引。

  • key_len: 表示 MySQL 在索引键部分使用的字节数。

  • ref: 显示索引的哪一列被使用了,如果可能的话,是一个常数。

  • rows: 表示 MySQL 从表中找到所需行所需读取的行数。

  • filtered: 表示在表的数据行中,通过条件过滤后,剩下的行所占的比例,范围是 0 到 100。

  • Extra: 提供关于 MySQL 执行查询时的额外信息,比如是否使用了临时表、使用了文件排序等等。

三、索引的分类

四、索引示例

示例1、主键索引(Primary Key Index)与 唯一索引(Unique Index)
  • 主键索引是一种特殊的唯一索引,具有以下特点:
    • 一张表只能有一个主键,用来唯一标识每一行数据。
    • 主键列的值不能为 NULL,确保每行数据都有一个唯一的标识。
    • 主键索引是表的物理排序顺序,通常会自动创建一个主键索引。
    • 主键索引在查询中的速度很快,可以通过主键快速定位到唯一的行。
  • 唯一索引 (Unique Index):

    • 唯一索引是一种约束,确保索引列的值在整个表中是唯一的。
    • 一张表可以有多个唯一索引,不同于主键索引的唯一之处在于允许 NULL 值(除非定义了 NOT NULL 约束)。
    • 唯一索引可以用来确保数据的完整性,防止重复值的插入。
  • 主键索引在定义上有更多的限制和特殊性,用途也不完全相同。主键索引一般用于唯一标识每一行数据,而唯一索引则用于确保某一列或多列的取值唯一性。

        这里根据主键id查找,查看explain命令返回结果,挑两个参数看下

  1. possible_keys: 显示了可能被查询用到的索引,这里显示了 PRIMARY,即主键索引。
  2. type: const,表示使用了常量连接,这是最有效率的一种查询方式。
  3. key: 显示实际使用的索引,也是 PRIMARY,即使用了主键索引。
  4. Extra: 显示了其他额外信息,这里是 "Using index",表示查询过程中使用了索引加速。

示例2、前缀索引(Prefix Index)

        比如 trigger_msg是一个很长的文本字段,通常按照前缀进行查询,可以创建一个前缀索引

未加索引前查询

创建一个前缀索引,再次执行查询看看结果,显示走了索引

alter table xxl_job_log add index i_trigger_msg_prefix (trigger_msg(10));

  1. type: range,表示在索引上进行范围扫描。
  2. possible_keys: 可能被查询用到的索引是 i_trigger_msg_prefix
  3. key: 实际使用的索引是 i_trigger_msg_prefix
示例3、联合索引(复合索引)

先对于group,id这两个列创建一个联合索引,看下结果是走了索引

alter table xxl_job_log add index i_job_group_job_id (job_group,job_id);

  1. type: ref,表示使用了某个索引进行查找,返回匹配某个值的所有行。
  2. possible_keys: 可能被查询用到的索引是 i_job_group_job_id
  3. key: 实际使用的索引是 i_job_group_job_id

注意:

        (1)最左前缀匹配原则:如果查询条件不是按照索引定义的顺序依次使用索引中的列,那么索引失效

比如,只用到了索引的第二个列,索引失效

        如果只查第一个列 正常走索引

        (2)覆盖索引:查询的列均有使用索引

        这个查询使用了索引 i_job_group_job_id,通过索引进行了 ref 查找,同时使用了覆盖索引(Using index),这意味着查询可以直接从索引中获取所需的数据,而无需回表到主表,从而提高了查询效率,这就实现了覆盖索引,也是为什么不推荐 select * ... 的原因。覆盖索引能够显著减少磁盘I/O操作,从而极大提升查询性能。

五、索引失效场景(较多,演示两个实例)

        1、主键索引:对主键列进行了计算操作

        2、前缀索引:like通配符在开头

        3、使用or关键字

        4、使用范围查询中的not in/exists 

        5、索引列使用函数

文章到这里就结束了

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

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

相关文章

Unity3D学习之XLua实践——背包系统

文章目录 1 前言2 新建工程导入必要资源2.1 AB包设置2.2 C# 脚本2.3 VSCode 的环境搭建 3 面板拼凑3.1 主面板拼凑3.2 背包面板拼凑3.3 格子复合组件拼凑3.4 常用类别名准备3.5 数据准备3.5.1 图集准备3.5.2 json3.5.3 打AB包 4 Lua读取json表及准备玩家数据5 主面板逻辑6 背包…

什么是工业协议网关?作用是什么?

在工业自动化和智能制造领域,数据的采集、传输和处理是实现设备监控、远程控制和优化的关键。而工业协议网关,作为连接工业设备与上层管理系统的桥梁,发挥着至关重要的作用。今天,我们就来深入解析一下HiWoo Box这一工业协议网关的…

【three.js】22. Imported Models导入模型

22. Imported Models导入模型 介绍 Three.js 可以让你创建很多原始几何体,但是当涉及到更复杂的形状时,我们最好使用专用的 3D 软件建模。 在本课中,我们将使用已经制作好的模型,但我们将在以后的课程中学习如何完全在 3D 软件中…

《汇编语言》第3版 (王爽)第10章检测点解析

第10章 检测点 检测点10.1:补全程序,实现从内存1000:0000处开始执行指令。 解析: 我们知道retf指令是用栈中的数据,同时修改CS和IP寄存器中的内容,实现远转移,而且是先出栈的数据放入IP中,后出栈的数据放入…

IPSEC VPN 网关模式实验

要求:FW1与FW3建立IPSEC通道,保证10.0.2.0/24网段能访问192.168.1.0/24网段 因为FW1与FW3都处于边界,所以使用网关部署模式来建立IPSEC VPN FW1 这里选择主模式跟隧道模式 FW3与FW1配置类似,与FW1的源目地址反过来,…

[Unity实战]使用NavMeshAgent做玩家移动

其实除了Character Controller, Rigidbody,我们还可以使用NavMeshAgent去做。这么做的好处是能避免玩家去莫名其妙的地方(毕竟基于烘焙过的导航网格),一般常见于元宇宙应用和mmo。 根据Unity手册,NavMeshAgent 也有和…

STM32 (1)

1.基本信息 stm32是由ST公司生产的一种32位微控制器(单片机)。 1.1 各种型号 stm32是32位单片机的总称,有多种不同的系列。 32即用32个比特位表示一个地址,寻址范围:0x00000000 --0xffffffff (4GB) 1.2 存储密度 …

网络编程作业day6

数据库操作的增、删、改完成 #include <myhead.h>//查询的回调函数 int callback(void* data,int count,char** argv, char** columnName) {//count是字段数//argv是字段内容//columnName是字段名称for(int i0;i<count;i) {printf("%s%s\n", columnName[…

【unity实战】事件(Event)的基本实战使用

文章目录 最终效果前言一、素材二、角色金币交互1. 拾取金币2. 显示金币数UI 完结 最终效果 前言 之前我们介绍过委托的用法&#xff0c;具体可以跳转&#xff1a;【unity小技巧】委托&#xff08;Delegate&#xff09;的基础使用和介绍 这期来讲讲事件&#xff0c;使用你会发…

Java线程状态解析:多线程编程指南

&#x1f31f; 欢迎来到 我的博客&#xff01; &#x1f308; &#x1f4a1; 探索未知, 分享知识 !&#x1f4ab; 本文目录 &#x1f31f;1. 引言&#x1f31f;2. Java线程的生命周期&#x1f4a1;2.1 <font color "skyblue">新建&#xff08;New&#xff09;&…

网络安全-appcms-master

一、环境 gethub上面自己找appcms-master 二、分析一下源码以及闯关思路 首先是有一个函数循环以及函数过滤&#xff0c;我们的post会将我们所传的所有val值去进行一个循环&#xff0c;之后通过htmlspecialchars这个函数进行过滤和转换所以val值不能通过单双引号闭合注入的方…

HarmonyOS NEXT应用开发案例——全屏登录页面

全屏登录页面 介绍 本例介绍各种应用登录页面。 全屏登录页面&#xff1a;在主页面点击跳转到全屏登录页后&#xff0c;显示全屏模态页面&#xff0c;全屏模态页面从下方滑出并覆盖整个屏幕&#xff0c;模态页面内容自定义&#xff0c;此处分为默认一键登录方式和其他登录方…

ospf虚链路实验简述

1、ospf虚链路实验简述 ospf虚链路配置 为解决普通区域不在骨干区域旁&#xff0c;通过配置Vlink-peer实现不同区域网络设备之间建立逻辑上的连接。 实验拓扑图 r1: sys sysname r1 undo info enable int loopb 0 ip add 1.1.1.1 32 ip add 200.200.200.200 32 quit int e0/0/…

蓝桥杯物联网竞赛_STM32L071_11_知识体系的查漏与补缺

太久没学单片机了&#xff0c;再重新过一遍查漏补缺&#xff0c;对其中之前没怎么在意的&#xff0c;而现在又发觉的问题进行再分析与补充 1. debug serial wire是干什么用的 这个东西我勾选不勾选都对我的程序没有什么影响&#xff0c;我很好奇是干什么用的&#xff0c;网上查…

什么样的项目适合Web自动化测试

&#x1f525; 交流讨论&#xff1a;欢迎加入我们一起学习&#xff01; &#x1f525; 资源分享&#xff1a;耗时200小时精选的「软件测试」资料包 &#x1f525; 教程推荐&#xff1a;火遍全网的《软件测试》教程 &#x1f4e2;欢迎点赞 &#x1f44d; 收藏 ⭐留言 &#x1…

好物周刊#43:设计素材下载

https://yuque.com/cunyu1943 村雨遥的好物周刊&#xff0c;记录每周看到的有价值的信息&#xff0c;主要针对计算机领域&#xff0c;每周五发布。 一、项目 1. frp 一个专注于内网穿透的高性能的反向代理应用&#xff0c;支持 TCP、UDP、HTTP、HTTPS 等多种协议&#xff0c…

浅谈Linux进程隐藏

浅谈Linux进程隐藏 文章目录 浅谈Linux进程隐藏前言环境变量的绕过常用的命令在哪里环境变量的调用前置知识的了解Netstat伪装怎么去甄别 LD_PRELOAD简单实例check.c重载函数设置LD_PERLOAD删除环境变量LD_PERLOAD PS的隐藏readdir函数如何HOOK编译并验证 LD_PRELOAD的其他利用…

【java】后序遍历二叉树

采用递归方式实现 节点类 public class Node {private int value;//父节点private Node fNode;//左节点private Node left;//右节点private Node right;//是否已经打印过private boolean sign false;public Node() {}public boolean isSign() {return sign;}public void setS…

【Leetcode每日一刷】数组|704. 二分查找、27. 移除元素

力扣每日刷题 一、704. 二分查找1.1、题目1.2、解题思路1.3、代码实现——C1.4、 总结&易错 二、27. 移除元素2.1&#xff1a;题目2.2、解题思路2.3、代码实现——C1.4、 总结&易错 一、704. 二分查找 1.1、题目 704. 二分查找 1.2、解题思路 题型&#xff1a;数组…

每日五道java面试题之springMVC篇(一)

目录&#xff1a; 第一题. 什么是Spring MVC&#xff1f;简单介绍下你对Spring MVC的理解&#xff1f;第二题. Spring MVC的优点第三题. Spring MVC的主要组件&#xff1f;第四题. 什么是DispatcherServlet?第五题. 什么是Spring MVC框架的控制器&#xff1f; 第一题. 什么是S…