Mysql索引专题

文章目录

    • 1. 数据库索引结构
      • 1.1 Hash结构
      • 1.2 树结构
      • 1.3 Mysql索引怎么提升效率?
    • 2. 执行计划 explain
      • id
      • select_type
      • table
      • type
      • possible_keys
      • key
      • key_len
      • ref
      • rows
      • fitered
      • extra

1. 数据库索引结构

我们都知道mysql数据库的常用存储结构是B+树，为什么是B+树？试想我们要设计数据库存储结构的话，会选择用什么基本数据结构去设计？KV结构，更关键的是怎么合理地安排其中的Key，让其布局更合理，更加符合操作系统的特性

要让我们的布局显得合理，需要了解数据场景和操作系统相关知识。

  1. 数据布局

    • 假设我们的数据库要存储GB级别的数据，假设是8GB，那操作系统会怎么处理？数据是存在磁盘还是内存？分治思想是操作系统存储数据的基本思想。操作系统有两大特性：
    局部性原理：      
时间局部性：之前被访问过的数据很有可能再次被访问      
空间局部性：数据和程序都有聚集成群的倾向

磁盘预读：      
内存跟磁盘在进行交互的时候，有一个最小的逻辑单位，这个单位称之为页，或者叫data page,一般是4KB或者8KB，由操作系统决定，我们在进行数据读取的时候，一般会读取页的整数倍，也就是4K,8K,16K，Innodb存储引擎在进行数据加载的时候读取的是16KB的数据

  2. IO问题

    • 操作系统是hash布局，分段分页的设计，每一次IO是一次硬盘寻址过程，和硬件速度相关，要降低IO带来的影响，可以从两个方面解决，①减少IO读取量②减少IO次数

基于上述两个特性，为KV结构的设计合理的数据结构布局。

1.1 Hash结构

image-20220505205031289

hash表结构的基本查找/读取速度为O(1)，当发生hash碰撞甚至极端的hash碰撞时，会降速到O(n)级别。

同时还有两个问题：

  1. 数据倾斜
  2. 经过hash计算的数据分布是无序的，无法进行范围查询，当需要范围查询的时候，效率比较低

1.2 树结构

树的指针结构非常符合磁盘预读的特性，一个指针指向一个数据块

比较熟悉的树结构有二叉树、BST、AVL、红黑树等。能按照定义结构使数据有序，平衡等，但是考虑到上述的树都是二叉结构，而数据库的值有很多，即使是AVL和红黑树在数据量较大时，树的度数呈线性相关。

所以必须有一种多叉有序树的结构来解决

B树

  • B树的阶：节点的最多子节点个数叫做阶。
  • B树的搜索：从根节点开始，对节点内的元素进行二分查找，如果找到就结束，否则进入查找元素所属范围的子节点再进行二分查找，直到找到或者到达叶子节点；
  • B树的所有节点都会存放数据；

B树的问题是B树的所有节点都会存放数据，不能很好地覆盖范围查询(<)的情况

B+树

image-20220505214142132

1.3 Mysql索引怎么提升效率?

在InnoDB引擎中默认是以B+树作为索引的数据结构，有下面5中分类

  • 主键索引：一种特殊的唯一索引，不允许有空值。
  • 普通索引：最基本的索引，没有任何限制
  • 唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
  • 联合/组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。
  • 全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。

聚簇索引和非聚簇索引

那么这些索引具体的data保存的是什么？

现有(id:int,name:varchar,age:int)结构的数据表user一张，其中id为主键，name为普通索引

那么这时候就有两颗索引B+树，如果每次构造B+树都把整行的数据存储到data中，显然会复制太多的数据，不合适。

所以Mysql规定的表数据结构中，是以主键索引的列为key；如果没有主键，则用唯一键；如果没有唯一键，则自动生成6字节的row_id作为B+树的key，叶子节点保存整行数据。这种和整行数据组织在一起的叫做聚簇索引

而非聚簇索引，叶子节点data保存的是聚簇索引的索引值（即可以是primary key或unique key或row_id）

回表

从这些非聚簇索引，我们可以很快找到聚簇索引中的data值返回，这个过程称为回表

如select * from user where name='张三'先根据name B+树匹配到对应的叶子节点，查询到对应行记录的id值，再根据id去id的B+树检索整行记录，这个过程就称之为回表，回表会造成查询两次索引树，应该尽量避免。

索引覆盖

可以通过直接查询索引字段来避免回表，如select id,name from user where name='张三'，这个过程称为索引覆盖(using index)。可以把一些常用的列设置为联合索引来优化sql

image-20220505225502278

联合索引与最左匹配，索引失效

创建索引的时候可以选择多个列来共同组成索引，此时叫做组合索引或者联合索引

要遵循最左匹配原则

id,name,age; id主键，name,age组合索引
select * from table where name='zhangsan' and age=12(用索引)
select * from table where name='zhangsan'(用索引)
select * from table where age=12(索引失效)
select * from table where age=12 and name='zhangsan'(优化器)

image-20220505230422254

多列的联合索引怎么确定顺序？

索引下推

name和age是联合索引
explain select * from user WHERE name LIKE '张%' AND age=12;

没有索引下推之前：先根据name从存储引擎中拉取数据到mysql的server层，然后在server层对age进行数据过滤
有了索引下推之后：根据name和age两个条件进行数据筛选，将筛选之后的结果返回给server层所谓下推，就是指server层该做的下推到存储引擎。索引下推是数据库自带的，减少了server层的数据量

判断条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；

image-20220508191840470

索引失效

  1. 不满足最左匹配

    索引idx_code_age_name，索引顺序为code,age,name
explain select * from user_fail where code='101' and age=21 and name='zhangsan';
explain select * from user_fail where code='101' and name='zhangsan';
explain select * from user_fail where code='1%';
explain select * from user_fail where code='%1';
explain select * from user_fail where age=21 and name='zhangsan';

    image-20220508213412439

  2. 索引列上有计算

    explain select * from user_fail where id+1=2;

    image-20220508213509746

  3. 索引列使用了函数

    explain select * from user_fail where SUBSTR(height,1,2)=17;

    image-20220508213547534

  4. 字段类型不同(隐式转换)

    code的类型为varchar
explain select * from user_fail where code=101;
explain select * from user_fail where code='101';

    image-20220508214135453

  5. 列对比

    explain select * from user_fail where id=height

    image-20220508214324616

  6. 使用or关键字

    # id是主键索引,height是普通索引,address无索引
explain select * from user_fail where id=1 or height='175';
explain select * from user_fail where id=1 or height='175' or address='a2';

    image-20220508215021647

    image-20220508215329149

  7. not in 和 not exists

    explain select * from user_fail where height in (173,174,175,176);
explain select * from user_fail t1 where exists (select 1 from user_fail t2 where t2.height=173 and t1.id=t2.id)
explain select * from user_fail where height not in (173,174,175,176);
explain select * from user_fail t1 where not exists (select 1 from user_fail t2 where t2.height=173 and t1.id=t2.id)

    image-20220508215947041

    image-20220508215924674

  8. order by

    explain select * from user_fail order by code limit 100;
explain select * from user_fail where code='101'order by name;

    image-20220508220325180

2. 执行计划 explain

当我们需要了解sql语句的执行，像有没有使用索引，使用情况，join语句的执行过程等，我们可以通过explain + sql的方式查看执行计划。

官网

image-20220505225502278

这其中最常用的信息是type,possible_keys,key,extra

image-20220505230657779

id

查询执行顺序栈：
id 值相同时表示从上向下执行
id 值相同被视为一组
如果是子查询，id 值会递增，id 值越高，优先级越高

select_type

查询类型

1.simple：表示查询中不包含子查询或者 union
EXPLAIN select * from actor where id=1;

image-20220506000631364

2.primary：当查询中包含任何复杂的子部分，最外层的查询被标记成 primary
3.derived：在 from 的列表中包含的子查询被标记成 derived
4.subquery：在 select 或 where 列表中包含了子查询，则子查询被标记成 subquery

用个例子来了解primary、subquery和derived
set session optimizer_switch='derived_merge=off';#关闭mysql5.7新特性对衍生表的合并优化

explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

set session optimizer_switch='derived_merge=on'; #还原默认配置

image-20220506000742143

5.union：
两个 select 查询时前一个标记为 PRIMARY，后一个标记为 UNION。
union 出现在 from 从句子查询中，外层 select 标记为 PIRMARY。
explain select 1 union all select 1;

image-20220506000852209

table

显示这一行的数据是关于哪张表的。
当 from 子句中有子查询时，table列是 <derive[id]>，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

type

表示存储引擎使用了哪种访问类型。
从最好到最差的连接类型为 system > const > eq_reg > ref > range > index > ALL。
一般来说，得保证查询达到range级别，最好达到ref以上

1. ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。
explain select * from actor;

image-20220506001348398

2. system：表中只有一行数据。属于 const 的特例。
3. const ：查询结果最多有一个匹配行。因为只有一行，所以可以被视为常量。const 查询速度非常快，因为只读一次。一般情况下把主键或唯一索引作为唯一条件的查询都是 const
explain select * from actor WHERE id=1;

image-20220506001435711

4.eq_ref：查询时查询外键表全部数据。且只能查询主键列或关联列。且外键表中外键列中数据不能有重复数据，且这些数据都必须在主键表中有对应数据（主键表中数据可以有没有用到的）
explain select * from film_actor left join film on film_actor.film_id = film.id

image-20220506003153130

5.ref：比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
explain select * from film where name = 'kungfu';

image-20220506003344797

关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分
explain select film_id from film left join film_actor on film.id = film_actor.film_id;

image-20220506003524567

6. range：把这个列当作条件只检索其中一个范围。常见 where 从句中出现 between、<、>、>=、in 等。主要应用在具有索引的列中
explain select * from actor where id > 1;

image-20220506003612994

7. index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些
explain select * from film;

image-20220506003731477

注意第一个例子和最后一个例子

possible_keys

查询条件字段涉及到的索引，可能没有使用。
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果

key

实际使用的索引。如果为 NULL，则没有使用索引。

key_len

表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len 是根据表定义计算而得的，不是通过表内检索出的。

1. 字符串，char(n)或varchar(n),n代表字符数，而不是字节数，如utf-8中，一个数字或字母占1个字节，一个汉字占3个字节
char(n):如果存汉字长度就是3n字节
varchar(n):如果存汉字则长度是3n+2字节，加2表示存储字符串的长度
2. 数值类型
tinyint:1字节
smallint:2字节
int:4字节
bigint:8字节
3. 时间类型
date: 3字节
timestramp: 4字节
datetime:8字节
索引最大长度是768字节，超过限制会被mysql用left函数截断

ref

查询结果可能找到多个符合条件的行，如果可能的话，通常是一个常量 const。

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
这个不是结果集里的行数。

fitered

filtered表示通过查询条件获取的最终记录行数占通过type字段指明的搜索方式搜索出来的记录行数的百分比。当比较低的时候，可以考虑加索引
EXPLAIN SELECT * FROM user_fail WHERE CODE LIKE '%2';

image-20220508234552996

extra

using filesort: sql通过排序操作获取结果时，会出现using filesort标记，这里虽然叫filesort但是不一定用了磁盘文件进行排序，具体看数据的量或存储引擎。
using temporatry: 用临时表保存中间结果，常见用group by操作
using index: 使用了索引覆盖
using index condition: 发生了索引下推的情况
using where: 全表扫描时，mysql服务层应用where条件过滤数据。使用索引访问数据时，而where子句中有除了索引包含的字段之外的条件时会出现
distinct: 优化distince操作，在找到第一匹配的数据后即停止同样值的操作

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

邮件代发API发送邮件如何使用？操作指南？

邮件代发API发送邮件如何使用？操作指南？

邮件代发邮箱API发送邮件的步骤&#xff1f;代发有哪些注意事项&#xff1f; 在自动化办公、批量营销等场景中&#xff0c;手动发送邮件往往显得效率低下&#xff0c;这时候&#xff0c;邮件代发API就显得尤为重要。那么&#xff0c;邮件代发API发送邮件究竟如何使用呢&#x…
阅读更多...
买婴儿洗衣机怎么选择？四大绝佳好用婴儿洗衣机分享

买婴儿洗衣机怎么选择？四大绝佳好用婴儿洗衣机分享

幼龄时期的宝宝的衣物&#xff0c;是比较需要注意的时候。可能一不注意宝宝穿在身上就会有不适宜症状发生。所以宝妈们真的要随时观察&#xff0c;然后在宝宝洗衣服的这上面多下点功夫&#xff0c;不要让宝宝受到这种无谓的伤害。小婴儿的抵抗力比我们差很多。有些细菌、病毒可…
阅读更多...
Hadoop大数据处理技术-Linux相关命令

Hadoop大数据处理技术-Linux相关命令

​7.Linux常用命令 1&#xff09;Windows中的dir&#xff1a;列出当前目录下所有的文件和目录 2&#xff09;cd&#xff1a;改变当前目录 cd命令并不能直接实现这种跳跃转换目录的功能 它只能让你在当前目录和其子目录之间来回切换 就像在一张平面地图上移动一样 如果想跨目录…
阅读更多...
如何挑选护眼灯？分享护眼灯排行榜前十名

如何挑选护眼灯？分享护眼灯排行榜前十名

许多家长肯定都有这样的烦恼&#xff0c;家中的孩子自从上学后&#xff0c;每天回家后的学习作写阅读时总会在不知不觉间越来越贴近书本&#xff0c;后来还会时不时眯着眼睛看东西&#xff0c;但其实这种用眼习惯的最大原因是孩子没有足够光线和舒适的环境光线导致的&#xff0…
阅读更多...
硬件设备杂记——12G SDI及 AES67/EBU

硬件设备杂记——12G SDI及 AES67/EBU

常见的 SDI线缆规格&#xff0c;HD-SDI又被称为1.5G-SDI&#xff0c;具体参数以秋叶原的参数为例 AES67/EBU 目前音频网络标准主要集中在OSI网络体系的第二层和第三层。 第二层音频标准的弊端在于构建音频网络时需要专用的交换机&#xff0c;无法利用现有的以太网络&#xff0c…
阅读更多...
SpringBoot基于redis zset实现滑动窗口限流

SpringBoot基于redis zset实现滑动窗口限流

通过Redis zset实现滑动窗口限流算法 在开发高并发系统时有三把利器用来保护系统&#xff1a;缓存、降级和限流。限流可以认为服务降级的一种&#xff0c;限流通过限制请求的流量以达到保护系统的目的。 一般来说&#xff0c;系统的吞吐量是可以计算出一个阈值的&#xff0c;…
阅读更多...
【leetcode面试经典150题】59. 合并两个有序链表（C++）

【leetcode面试经典150题】59. 合并两个有序链表（C++）

【leetcode面试经典150题】专栏系列将为准备暑期实习生以及秋招的同学们提高在面试时的经典面试算法题的思路和想法。本专栏将以一题多解和精简算法思路为主&#xff0c;题解使用C语言。&#xff08;若有使用其他语言的同学也可了解题解思路&#xff0c;本质上语法内容一致&…
阅读更多...
【Java框架】Spring框架（四）——Spring中的Bean的创建与生命周期

【Java框架】Spring框架（四）——Spring中的Bean的创建与生命周期

目录 SpringBean的创建步骤后置处理器(PostProcessor)BeanFactoryPostProcessorBeanPostProcessorInstantiationAwareBeanPostProcessorpostProcessBeforeInstantiationpostProcessAfterInstantiationpostProcessProperties SmartInstantiationAwareBeanPostProcessordetermine…
阅读更多...
空心电抗器的matlab建模与性能仿真分析

空心电抗器的matlab建模与性能仿真分析

目录 1.课题概述 2.系统仿真结果 3.核心程序与模型 4.系统原理简介 5.完整工程文件 1.课题概述 空心电抗器是一种无铁芯的电感元件&#xff0c;主要由一圈或多圈导线绕制在非磁性材料制成的空心圆筒或其他形状的骨架上构成。其工作原理基于法拉第电磁感应定律&#xff0c;…
阅读更多...
Maui 开始笔记

Maui 开始笔记

1&#xff0c;仿真器硬件加速&#xff0c;需要安装 2&#xff0c;刚创建的maui 不添加的话&#xff0c;启动可能时会自动退出&#xff0c;不退出&#xff0c;可以不加次配置 MauiApp1.csproj 文件中配置 在 PropertyGroup 元素下添加 <WindowsAppSdkDeploymentManagerIniti…
阅读更多...
【Qt】常用控件(LCD Number/进度条/日历)

【Qt】常用控件(LCD Number/进度条/日历)

需要云服务器等云产品来学习Linux可以移步/-->腾讯云<--/官网&#xff0c;轻量型云服务器低至112元/年&#xff0c;新用户首次下单享超低折扣。 目录 一、LCD Number(LCD显示器) 一个倒计时程序 二、ProgressBar(进度条) 1、创建一个进度条&#xff0c;100ms进度增加…
阅读更多...
hv第一坑:定时器

hv第一坑:定时器

错误代码 重试策略&#xff1a;一次延迟1s,最长30s直至事件成功。 int try_count 0;//do something if(not success)m_loop->setTimerInLoop((try_count > 30 ? 30: try_count) *1000 , cb, INFINITE, 0x100);表现现象 cpu 爆了内存爆了 总结原因 hv内部代码bug&…
阅读更多...
T02.python

T02.python

阅读更多...
C语言—常用字符串函数剖析

C语言—常用字符串函数剖析

字符串函数 cplusplus.com/reference/cstring/ 更多没有总结到的函数大家可以自行查阅 这篇文章只是把最需要知道的函数做一个总结 strlen size_t strlen ( const char * str );字符串已经 ‘\0’ 作为结束标志&#xff0c;strlen函数返回的是在字符串中 ‘\0’ 前面出现的…
阅读更多...
力扣面试150 文本左右对齐 贪心 字符串 满注释版

力扣面试150 文本左右对齐 贪心 字符串 满注释版

Problem: 68. 文本左右对齐 思路 &#x1f469;‍&#x1f3eb; 三叶题解 &#x1f496; Code class Solution { public List<String> fullJustify(String[] words, int maxWidth){List<String> ans new ArrayList<>();// 结果List<String> list …
阅读更多...
【网络安全技术】——网络安全设备（学习笔记）

【网络安全技术】——网络安全设备（学习笔记）

&#x1f4d6; 前言&#xff1a;网络防火墙&#xff08;简称为“防火墙”&#xff09;是计算机网络安全管理中应用最早和技术发展最快的安全产品之一。随着互联应用的迅猛发展&#xff0c;各种安全问题和安全隐患日渐突出。防火墙及相关安全技术能够最大可能地解决各类安全问题…
阅读更多...
SpringBoot整合零一万物模型API进行多轮对话

SpringBoot整合零一万物模型API进行多轮对话

前期准备工作 零一万物官网&#xff1a;https://www.01.ai/cn 零一万物大模型开放平台&#xff1a;https://platform.lingyiwanwu.com/ 选择理由 性价比高很高&#xff0c;模型整体不错&#xff0c;新用户送60元&#xff0c;非常适合研究学习。 开发 只提供了http接口和p…
阅读更多...
测试JAVA 测开

测试JAVA 测开

测试、java测开 1、测试用例要素&#xff08;4个重要要素&#xff09;2、测试用例的好处3、测试用例的设计方法3.1 基于需求设计测试用例3.2 等价类3.3 边界值3.4 判定表 1、测试用例要素&#xff08;4个重要要素&#xff09; 测试环境操作步骤测试数据预期结果 2、测试用例的…
阅读更多...
计算机网络：CSMA/CA协议

计算机网络：CSMA/CA协议

计算机网络&#xff1a;CSMA/CA协议 CSMA/CA概述帧间间隔工作原理退避算法虚拟载波监听 CSMA/CA概述 讲解CSMA/CA之前&#xff0c;我们回顾一下CSMA/CD的三个特性&#xff1a; 多址接入MA&#xff1a;多个主机连接在一条总线上&#xff0c;竞争使用总线 载波监听CS&#xff1a…
阅读更多...
2025考研数学武忠祥强化班视频，百度网盘课程+讲义PDF更新

2025考研数学武忠祥强化班视频，百度网盘课程+讲义PDF更新

25考研的小伙伴们现在应该基础都学习的差不多了吧&#xff01; 是时候进入强化阶段的学习啦。 2025考研数学强化班全程网盘&#xff1a;https://pan.baidu.com/s/1Z029fuCLkyyhIRFqd5QKcg 提取码&#xff1a;p3ue 晚上好&#xff0c;聊聊17堂课的看课攻略。 今年的17堂课还…
阅读更多...
最新文章

Copyright @ 2022~2023