浅析MySQL-索引篇01

news/2024/7/8 11:05:59 标签: mysql, 数据库

什么是索引?

索引是帮助存储引擎快速获取数据的一种数据结构,类似于数据的目录。

索引的分类

按数据结构分类:

MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。

Innodb是MySQL5.5之后的默认存储引擎,B+Tree索引类型也是MySQL采用的最多索引类型。

在创建表时,InnoDB存储引擎会根据不同的场景选择不同的列作为索引:

  • 如果有主键,默认会使用主键作为聚簇索引的索引键;
  • 如果没有主键,就选择一个唯一列作为聚簇索引的索引键;
  • 如果两个都没有,将自动生成一个隐式自增 id 列作为聚簇索引的索引键;

其他索引都属于二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的都是B+tree索引。

按物理存储分类:

索引分为聚簇索引、非聚簇索引。

聚簇索引的B+tree的叶子节点存放的是实际数据,所有完整的数据记录都存放在聚簇索引的B+Tree的叶子节点里;

非聚簇索引的B+Tree的叶子节点存放的是主键值,不是实际数据记录

因此,在查询时使用了非聚簇索引,如果查询的数据字段能在非聚簇索引里查询到,那么就不需要回表,这个过程称作覆盖索引。如果查询的数据字段不在非聚簇索引中,就会先检索非聚簇索引,找到对应的叶子节点,获取到主键值后,然后在检索聚簇索引,就能查到数据了,这个过程就称作回表。

按字段特性分类:

索引分为主键索引、唯一索引、普通索引、前缀索引。

这里说明下前缀索引:

前缀索引指的是对字符类型(char、varchar)字段的前几个字符建立的索引,而不是在整个字段上建立索引。使用此类索引可以检索索引占用的存储空间,提升查询效率。

create index idx_name_prefix on tbl_user(name(3));

按字段个数分类:

分为单列索引、联合索引。

这里说明下联合索引,它就是将多个字段组合成一个索引。

索引的结构

比如在tb_user中添加idx_name_age(name,age)联合索引

CREATE INDEX idx_name_age ON tbl_user(name, age);

下图就是联合索引idx_name_age中B+Tree形式的大致结构:

从上面的图可以看出,联合索引的非叶子节点用两个字段的值作为B+Tree的key值。当在联合索引查询数据时,先按name字段比较,在name字段相同的情况下在按age字段比较。也就是说先按name字段进行排序,然后再name字段相同的情况再按age字段排序。

因此,使用联合索引时,就会存在最左匹配原则。如果查询条件不遵守「最左匹配原则」联合索引会失效,查询就无法利用到索引进行快速查询。

为什么选择B+Tree?

  • B+ 树的非叶子节点不存放实际的记录数据,仅存放索引,因此数据量相同的情况下,相比存储即存索引又存记录的 B 树,B+树的非叶子节点可以存放更多的索引,因此 B+ 树可以比 B 树更「矮胖」,查询底层节点的磁盘 I/O次数会更少。

  • B+ 树有大量的冗余节点(所有非叶子节点都是冗余索引),这些冗余索引让 B+ 树在插入、删除的效率都更高,比如删除根节点的时候,不会像 B 树那样会发生复杂的树的变化;

  • B+ 树叶子节点之间用链表连接了起来,有利于范围查询,而 B 树要实现范围查询,因此只能通过树的遍历来完成范围查询,这会涉及多个节点的磁盘 I/O 操作,范围查询效率不如 B+ 树。

索引的优化

下面举例说明几种常见的优化索引手段:

  • 前缀索引优化;
  • 覆盖索引优化;
  • 主键索引最好是自增的;
  • 防止索引失效;

前缀索引优化

使用某个字段中字符串的前几个字符串建立索引,为什么需要使用前缀来建立索引呢?

目前是为了减小索引字段大小,可以增加一个索引页中存储的索引值,有效提高索引的查询速度。因为会存在大字符串的字段作为索引,这个场景就适合使用前缀索引方式来减小索引项的大小

缺点:①order by无法使用前缀索引 ②无法把前缀索引用作覆盖索引

覆盖索引优化

指的是SQL中查询的所有字段,在索引B+Tree的叶子节点都能找得到,从非聚簇索引中查询得到记录,而不需要通过聚簇索引查询获得,避免了回表的操作。

主键索引是自增

建表的时候,我们一般把主键设置成自增,为什么这么做呢?

Innodb引擎中,以聚簇索引为例,数据存放在叶子节点中,也就是说,同一个叶子节点内的各个数据都是按主键顺序存放的,因此当有一条新数据要插入时,数据库会根据主键将其插入到对应的叶子节点中。

如果使用自增主键,那么每次插入的新数据就会按顺序添加到当前索引节点的位置,不需要移动已有数据,当页写满,就会自动开辟一个新页。因为每次插入一条新纪录,都是追加操作,不需要重新移动数据,因此这种插入数据的方法效率非常高。

如果使用非自增主键,那么每次插入主键的索引值都是随机的,每次插入新的数据时,就可能会插入到现有数据页中间的某个位置,这将不得不移动其他数据来满足新数据的插入,甚至需要从一个页复制数据到另外一个页,这种情况我们称为 页分裂。页分裂可能会导致造成大量的内存碎片,导致索引节后不紧凑,影响查询效率。

索引最好设置为 NOT NULL

  • 第一:索引列存在NULL就会导致优化器在做索引选择的时候更加复杂,难以优化。比如进行索引统计,count会省略之为NULL的行
  • 第二: NULL是一个没有意义的值,但是它会占用物理空间,所以会带来存储空间的问题。如果表中存在允许为NULL的字段,那么行格式中至少会用1字节空间存储NULL值列表。

防止索引失效

对索引使用左或者左右模糊匹配

当我们使用左或者左右模糊匹配的时候,都会造成索引失效

select * from tbl_score where name like '%王';
select * from tbl_score where name like '%王%';

执行计划中的 type=ALL 就代表了全表扫描,而没有走索引。

 

如果查询的是右模糊的话,会走索引。

select * from  tbl_score like '王%';

执行计划中的type=range表示走了索引扫描。

为什么 like 关键字左或者左右模糊匹配无法走索引呢?

因为索引结构是B+Tree,它是按照「索引值」有序排序存储的,只能根据前缀进行比较。

对索引使用函数

如果查询条件中对索引字段使用函数,就会导致索引失效。

select * from tbl_score where mod(score, 2) = 0;

执行计划中type=ALL,代表未走索引。

为什么对索引使用函数,就无法走索引了呢? 

因为索引保存的是索引字段的原始值,而不是经过函数计算后的值,因此肯定没法走索引。但是在8.0版本后,增加了函数索引。即可这对函数计算后的值建立索引,也就是说索引的值是函数计算后的值。

alter table tbl_score add key idx_score_mod ((mod(score,2)));

添加完后,执行计划如下: 

 

 

对索引进行表达式计算

在查询条件中对索引进行表达式计算,也是无法走索引的。

select * from tbl_score where age + 2=10;

执行计划如下,type=ALL未走索引

修改查询方式 

select * from tbl_score where age = 10 - 2;

执行计划如下,type=ref走了索引

对索引隐式类型转换

如果索引字段是字符串类型,但是在条件查询中,输入的参数是整型的话,你会在执行计划的结果发现这条语句会走全表扫描

在tbl_score中存在一个字段status 类型是varchar(4)

select * from tbl_score where status=1;

 执行计划中,type=ALL未走索引。

修改方式:

select * from tbl_score where status='1';

 执行计划中,type=ref表示已走索引。

 

联合索引非最左匹配

联合索引要能正确使用需要遵循最左匹配原则,也就是按照最左优先的方式进行索引的匹配

select * from tbl_score where score= 10;

执行计划中type=ALL未走索引。

为什么联合索引不遵循最左匹配原则就会失效?

在联合索引的情况下,数据是按照索引第一列排序,第一列数据相同时才会按照第二列排序。

也就是说,如果我们想使用联合索引中尽可能多的列,查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索,肯定无法走索引

WHERE 子句中的 OR

在 WHERE 子句中,如果在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效

但是or查询条件中都有字段都是索引字段,并不一定走索引。还需要看优化器怎么决定。


http://www.niftyadmin.cn/n/5537117.html

相关文章

如何在Qt使用uchardet库

如何在 Qt 中使用 uchardet 库 文章目录 如何在 Qt 中使用 uchardet 库一、简介二、uchardet库的下载三、在Qt中直接调用四、编译成库文件后调用4.1 编译工具下载4.2 uchardet源码编译4.3 测试编译文件4.4 Qt中使用 五、一些小问题5.1 测试文件存在的问题5.2 uchardet库相关 六…

【MySQL】MySQL 9.0悄悄的来了

MySQL 9.0.0 中的变化 MySQL 9.0 中的新功能 JavaScript 存储程序 MySQL 企业版现在支持用 JavaScript 编写的存储程序,例如使用 CREATE FUNCTION下面显示的语句和 JavaScript 代码创建的这个简单示例: CREATE FUNCTION gcd(a INT, b INT) RETURNS …

Redis基础教程(十四):Redis连接

💝💝💝首先,欢迎各位来到我的博客,很高兴能够在这里和您见面!希望您在这里不仅可以有所收获,同时也能感受到一份轻松欢乐的氛围,祝你生活愉快! 💝&#x1f49…

LangChain4j系列:带你入门LangChain4j框架

本文所有示例均在 langchain4j 0.31.0 版本之上进行,langchain4j也是在快速发展的阶段。 LangChain4j 框架是什么? LangChain4j 于 2023 年初在 ChatGPT 的炒作中开始开发。思想来源于 Python 和 JavaScript LLM 库,并加入创新思想&#xff…

云原生之使用Docker部署RabbitMQ消息中间件

云原生之使用Docker部署RabbitMQ消息中间件 一、RabbitMQ介绍1.1 RabbitMQ简介1.2 RabbitMQ特点1.3 RabbitMQ使用场景 二、检查Docker环境2.1 检查Docker版本2.2 检查操作系统版本2.3 检查Docker状态 三、下载RabbitMQ镜像四、部署RabbitMQ服务4.1创建挂载目录4.2 运行RabbitMQ…

香蕉云编+uniapp打包ios的开发包和生产包

登录香蕉云编,找到 云编-ios证书生成,新建CSR文件,并下载csr文件。 登录苹果开发者中心,进入证书页面。 1.创建一个证书,选择ios Distribution类型即可,这是个通用的证书,既能用来打正式包又能…

光伏电站数据采集方案(基于工业路由器部署)

​ 一、方案概述 本方案采用星创易联SR500工业路由器作为核心网关设备,实现对光伏电站现场数据的实时采集、安全传输和远程监控。SR500具备多接口、多功能、高可靠性等特点,能够满足光伏电站数据采集的各种需求。(key-iot.com/iotlist/sr500…

9 redis,memcached,nginx网络组件

课程目标: 1.网络模块要处理哪些事情 2.reactor是怎么处理这些事情的 3.reactor怎么封装 4.网络模块与业务逻辑的关系 5.怎么优化reactor? io函数 函数调用 都有两个作用:io检测 是否就绪 io操作 1. int clientfd = accept(listenfd, &addr, &len); 检测 全连接队列…