当前位置: 首页 > news >正文

【MySQL】索引使用规则——(覆盖索引,单列索引,联合索引,前缀索引,SQL提示,数据分布影响,查询失效情况)

前言

大家好吖,欢迎来到 YY 滴MySQL系列 ,热烈欢迎! 本章主要内容面向接触过C++的老铁
主要内容含:
在这里插入图片描述

欢迎订阅 YY滴C++专栏!更多干货持续更新!以下是传送门!

  • YY的《C++》专栏
  • YY的《C++11》专栏
  • YY的《Linux》专栏
  • YY的《数据结构》专栏
  • YY的《C语言基础》专栏
  • YY的《单片机》专栏
  • YY的《STM32》专栏
  • YY的《数据库》专栏

    目录

    • 一.索引使用规则
      • ※.验证索引效率提升
      • 1.覆盖索引——查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到
      • 2.单列索引&联合索引
      • 3.前缀索引——解决冗长字符串与索引问题
        • 【1】前缀索引&索引选择性的介绍
        • 【2】前缀索引创建演示:
      • 5.SQL提示——指定某个索引/忽略索引/强制索引
      • 6.数据分布影响——MySQL自我评估
      • 7.查询失效的几种情况
        • 【1】违背——最左前缀法则(联合索引)
        • 【2】范围查询右侧失效
        • 【3】用or分割开的条件,or后面没索引,所有索引失效
        • 【4】索引列上进行运算操作,索引失效
        • 【5】字符串类型字段使用时,不加引号,索引失效
        • 【6】头部模糊匹配,索引失效

目录

  • 一.索引使用规则
    • ※.验证索引效率提升
    • 1.覆盖索引——查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到
    • 2.单列索引&联合索引
    • 3.前缀索引——解决冗长字符串与索引问题
      • 【1】前缀索引&索引选择性的介绍
      • 【2】前缀索引创建演示:
    • 5.SQL提示——指定某个索引/忽略索引/强制索引
    • 6.数据分布影响——MySQL自我评估
    • 7.查询失效的几种情况
      • 【1】违背——最左前缀法则(联合索引)
      • 【2】范围查询右侧失效
      • 【3】用or分割开的条件,or后面没索引,所有索引失效
      • 【4】索引列上进行运算操作,索引失效
      • 【5】字符串类型字段使用时,不加引号,索引失效
      • 【6】头部模糊匹配,索引失效

一.索引使用规则

※.验证索引效率提升

在这里插入图片描述

  • 在未建立索引之前,执行如下SOL语句,查看SQL的耗时
  • 耗时11s
    在这里插入图片描述
  • 利用主键索引(id)查,耗时0s
    在这里插入图片描述
  • 利用第二个字段sn查,耗时21s,性能极低
    在这里插入图片描述
  • 针对字段sn创建索引,然后再次执行相同的SQL语句,再次查看SQL的耗时
  • 从21s变成0.01s,性能大大提升

-
在这里插入图片描述

1.覆盖索引——查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到

  • 尽量使用覆盖索引 (查询使用了素引,并且需要返回的列,在该索引中已经全部能够找到) ,减少select *
    在这里插入图片描述

演示:

  • 我们查看索引
    在这里插入图片描述
  • 紫色部分:上面比下面的效率好
    在这里插入图片描述
  • 多出的蓝色部分(返回的列),不能够在在该索引中找到, 上部分的效率比下部分高
  • 上部分:usingindexcondition:查找使用了索引,但是需要回表查询数据
  • 下部分:using where;using index:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据在这里插入图片描述

2.单列索引&联合索引

  • 单列索引:即一个索引只包含单个列。
  • 联合家引:即一个索引包含了多个列。
  • 在业务场景中,如果存在多个查询负件,考虑针对于查询字段建立索引时, 建议优先建立联合索引,而非单列引。
  • 多条件联合查询时,MySQL优化器会评估哪个字段的索引效率更高,会选择该索引完成本次查询。 要强制就用可视日志。

演示:

  • name和phone字段,都是单列索引,但只用到一个字段索引
    在这里插入图片描述
  • 我们给name和phone字段创建联合索引,MySQL优化器会评估哪个字段的索引效率更高。如果我们要主动设置SQL语句用的索引,涉及到下文我们提到的SQL提示
    在这里插入图片描述

3.前缀索引——解决冗长字符串与索引问题

【1】前缀索引&索引选择性的介绍

  • 当字段类型为字符串(varchar,text等)时, 有时候需要索引很长的字符串 ,这会让索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率。
  • 此时可以只将字符串的 一部分前缀,建立索引 ,这样可以大大节约索引空间,从而提高索引效率。
    在这里插入图片描述
  • 如下图,计算可得 字段选择性是1
    在这里插入图片描述
  • 不断调整substring截取部分,可得到不同选择性
    在这里插入图片描述

【2】前缀索引创建演示:

  • 针对email字段截取字符串,建立前缀索引,降低索引体积
    在这里插入图片描述
  • 建立前五个字符构成的前缀索引
    在这里插入图片描述
  • 查询发现用到了创建的前五个字符构成的前缀索引
    在这里插入图片描述

5.SQL提示——指定某个索引/忽略索引/强制索引

  • SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
  • 例如下图SQL执行计划,可能索引有两个,但是最终应用的索引只有一个,某些情况下我们就是要 指定用某个索引
    在这里插入图片描述

在这里插入图片描述

演示:

  • 有这样一张表,我们看下这段SQL的执行计划,索引是 复合索引idx_user_pro_age_sta
    在这里插入图片描述
  • 我们针对profession创建一个单列索引
    在这里插入图片描述
  • 我们想要用这个单列索引,我们就建议索引use XX——MySQL不一定接受,要强制用force XX
    在这里插入图片描述

6.数据分布影响——MySQL自我评估

  • 如果MySQL评估使用索引比全表 更慢 ,则不使用索引

演示:

  • 有一张表,我们关注其phone字段
    在这里插入图片描述
  • 当我们进行不同的范围查询时,MySQL会自己选择用不用索引
  • 例如绿色部分用了联合索引,而红色部分要查找的数目已经大于总数一半了,此时MySQL自己选择全表扫描
    在这里插入图片描述

7.查询失效的几种情况

【1】违背——最左前缀法则(联合索引)

  • 如果索引了多列(联合索引),要遵守最左前缀法则。
  • 最左前缀法则指的是查询从索引的最左列开始 (最左列存在即可),并且不跳过索引中的列,索引才不会失效
  • 如果跳跃某一列 ,索引将部分失效 (后面的字段索引失效)

演示:

  • 有如下表
    在这里插入图片描述
  • 查看索引,有一个age字段和status字段的联合索引idx_user_pro_age_sta
    在这里插入图片描述
  • 联合索引生效,索引长度为54在这里插入图片描述
  • 去掉status条件后,索引长度为49,因此可以判断status部分对应的索引长度为5
    在这里插入图片描述
  • 去掉status和age条件后,索引长度为47,因此可以判断age部分对应索引长度为2
    在这里插入图片描述

索引失效:

  • 索引的最左列不存在,key为null,不走索引,走全表扫描
    在这里插入图片描述

  • 去除掉age,也会走联合索引,但是长度只有47, 只有profession部分走索引 ,部分失效
    -

  • 注: 索引的最左列只要存在即可,顺序无所谓
    在这里插入图片描述

【2】范围查询右侧失效

  • 联合索引中,出现范围查询(>,<), 查询范围右侧的列索引失效
  • 实际中应该规避(>,<),在业务允许的范围下使用(>=,<=),(>=,<=)不会失效

演示:

  • 如下图,联合索引正常长度应该是54,图中是49,说明没有走status索引——status索引失效了
    在这里插入图片描述
  • 如下图,采用>=,索引长度为54,说明 >=的范围查询不会让右侧失效
    在这里插入图片描述

【3】用or分割开的条件,or后面没索引,所有索引失效

  • 如果or前的条件中的列有索引,而后面的列中没有索引,那么 涉及的索引都不会被用到
  • 例如:age无索引,但是主键索引仍然不会生效
    在这里插入图片描述

【4】索引列上进行运算操作,索引失效

  • 不要在索引列上进行运算操作, 索引将失效
    在这里插入图片描述

演示:

  • 有这样一张表,我们关注phone字段
    在这里插入图片描述
  • phone是单列索引,发现用上了
    在这里插入图片描述
  • 我们想要找手机号最后两位是15的,利用substring函数运算截取,第十位开始,截2位
  • 索引失效
    在这里插入图片描述

【5】字符串类型字段使用时,不加引号,索引失效

  • 字符串类型字段使用时,不加引号, 索引失效

演示:

  • phone是varchar类型,不加单引号,也能查
  • 但是由于存在隐式类型转换,索引会失效
    -

【6】头部模糊匹配,索引失效

  • 如果仅仅是 尾部模糊匹配,索引不会失效
  • 如果是 头部模糊匹配,索引失效
    在这里插入图片描述

相关文章:

  • 北京网站建设多少钱?
  • 辽宁网页制作哪家好_网站建设
  • 高端品牌网站建设_汉中网站制作
  • python07-单元测试框架unittest1-1
  • JS都有哪些操作数组的方法
  • JS简介 JS特点
  • 用ceres实现lio-sam平面点匹配
  • python开发VTK入门
  • Amazon QuickSight 实验
  • 秋招突击——算法练习——8/26——图论——200-岛屿数量、994-腐烂的橘子、207-课程表、208-实现Trie
  • Python画笔案例-030 实现打点之斜正方
  • 1306. 跳跃游戏 III
  • 【GIT】idea中实用的git操作,撤回commit,撤回push、暂存区使用
  • Oracle中数据类型 NVARCHAR2 与 VARCHAR2 的对比
  • 【嵌入式】内存未对齐导致程序崩溃(铺获信号量SIGBUS,数值7)
  • Ubuntu上qt使用SSH样式表
  • zm-tree-org 数据量过大时,全部展开后,根节点点击收缩,树形消失
  • 第一章 Spring Boot入门
  • 【跃迁之路】【463天】刻意练习系列222(2018.05.14)
  • CNN 在图像分割中的简史:从 R-CNN 到 Mask R-CNN
  • CSS实用技巧干货
  • Java Agent 学习笔记
  • java2019面试题北京
  • Zsh 开发指南(第十四篇 文件读写)
  • 从0搭建SpringBoot的HelloWorld -- Java版本
  • 大主子表关联的性能优化方法
  • 前端相关框架总和
  • 前端之React实战:创建跨平台的项目架构
  • 要让cordova项目适配iphoneX + ios11.4,总共要几步?三步
  • 源码安装memcached和php memcache扩展
  • 运行时添加log4j2的appender
  • 主流的CSS水平和垂直居中技术大全
  • Mac 上flink的安装与启动
  • ​软考-高级-系统架构设计师教程(清华第2版)【第20章 系统架构设计师论文写作要点(P717~728)-思维导图】​
  • # Maven错误Error executing Maven
  • #define,static,const,三种常量的区别
  • #include<初见C语言之指针(5)>
  • #Linux(Source Insight安装及工程建立)
  • #LLM入门|Prompt#2.3_对查询任务进行分类|意图分析_Classification
  • #NOIP 2014# day.2 T2 寻找道路
  • #我与Java虚拟机的故事#连载02:“小蓝”陪伴的日日夜夜
  • $NOIp2018$劝退记
  • (C#)获取字符编码的类
  • (C语言)二分查找 超详细
  • (ISPRS,2021)具有遥感知识图谱的鲁棒深度对齐网络用于零样本和广义零样本遥感图像场景分类
  • (zhuan) 一些RL的文献(及笔记)
  • (含react-draggable库以及相关BUG如何解决)固定在左上方某盒子内(如按钮)添加可拖动功能,使用react hook语法实现
  • (经验分享)作为一名普通本科计算机专业学生,我大学四年到底走了多少弯路
  • ... fatal error LINK1120:1个无法解析的外部命令 的解决办法
  • .net core控制台应用程序初识
  • .NET 表达式计算:Expression Evaluator
  • .NET开源纪元:穿越封闭的迷雾,拥抱开放的星辰
  • .net使用excel的cells对象没有value方法——学习.net的Excel工作表问题
  • .vimrc php,修改home目录下的.vimrc文件,vim配置php高亮显示
  • .xml 下拉列表_RecyclerView嵌套recyclerview实现二级下拉列表,包含自定义IOS对话框...
  • /proc/stat文件详解(翻译)
  • @Documented注解的作用
  • @EnableConfigurationProperties注解使用