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

浅谈MySQL索引

目录

1.索引的定义

2.索引的原理

3.Hash索引与B+ Tree索引

4.索引的分类

5.建立索引的注意事项


1.索引的定义

索引是存储引擎用于快速找到数据记录的一种数据结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引一般存于磁盘中,是一种以空间换时间的方案

简单来说索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查。但同时,为了这个查询速度,字典表就要多花几页纸来存储音序表。

同时,尽管索引可以大大提高查询速度,但当对表进行增加、删除、修改时,由于索引也要动态维护,索引会降低更新表的速度。

针对以上问题,一种解决方案则是需要大量更新数据时,先删除索引,再进行数据的更新。

2.索引的原理

其本质是不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机事件变为顺序的事件。也就是说,索引可以帮助我们总是用同一种查找方式来锁定数据。

简单来说,就是把数据分成页,比如第一页存储第1到第100条数据,第2页存储101到200条数据(MySQL中每一页最大为16k,存满一页就新增一页)......当需要查询第150条数据时,根据索引会最终直接分页到第2页进行查询。这样在查找数据时就能去除大多数无用的数据。

在MySQL中,基本的数据页模型如下:

每一页数据中包括record_type(2位最小记录,3位最大,0位普通用户数据,1位为B+ tree结构中非页节点的目录项)、next_record指向下一条记录的地址、用户数据。

数据与数据之间形成单链表,从小指向大。

3.Hash索引与B+ Tree索引

MySQL的存储引擎主要使用B+ Tree和Hash两类数据结构作为索引的存储结构。

(1)Memory表(只存于内存中,断电会消失,适用于临时表)默认索引类型为Hash索引。

Hash索引把数据以hash形式组织起来,每个键只对应一个值,因此查找一条记录时,经过一次哈希计算即可找到对应的键值,速度非常快。但也由于散列进行分布,所以Hash索引不支持范围查找和排序功能。

(2)B+ Tree是innoDB和MyISAM存储引擎模式的索引类型。

B+树索引中,非叶节点仅存放目录项(即非叶子节点上仅存储键值),所有数据均存储在叶子节点,叶子结点之间组成链表(双向链表,既能左遍历又能右遍历)。如下图(图中数据页结构为简化版)所示:

根据上图B+ Tree结构,如果需要查找id=5的数据:

  1. 从根节点找到页1开始查询,加载关键字1、6、12,判断1<5,5<6,根据指针p1找到页2;
  2. 加载关键字1、4、6,判断5>1,5>4,5<6,根据指针p2找到页6;
  3. 到达叶节点,在关键字链表中命中最终结果5,读取数据行;

相对于Hash索引,B+ Tree在查找单条记录时,由于需要从根节点到叶节点逐级寻找,速度较慢,更适合范围查询和排序操作。

(3)为什么使用B+ Tree

B+树是一种矮壮型的树形结构,这就意味着他的层级较小,存储的叶子节点更多,这样的话就可以大大的降低查询时检索的次数,进行IO的次数越少,从而提高查询效率。

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

最后,由于B+ 树叶子节点之间用链表连接了起来,有利于范围查询。

4.索引的分类

MySQL根据其物理实现方式分为聚簇索引和非聚簇索引,关键在于数据跟索引是否存储在一起。数据绑定一起的是聚簇索引,存储的数据为全量的用户数据;否则为非聚簇索引。

聚簇索引是根据主键搭建起来的B+ Tree,innodb会自动帮我们创建。其对于主键的范围查找和排序速度都非常快。非聚簇索引键为条件进行查询时,找到叶子节点的数据之后,再通过叶子节点的id,再去聚簇索引中查询一遍,才能拿到所有字段。

MySQL的默认存储引擎Innodb在进行数据插入时,数据必须要指定一个索引(主键>唯一键>rowid)存储在一起。而为了避免数据冗余存储,其他的索引的叶子节点存储的是聚簇索引的key值。所以,innodb中既有聚簇索引,又有非聚簇索引。

MySIAM存储引擎中没有聚簇索引。

根据实现的功能,创建的索引又分为:

1)NORMAL:普通索引,MySQL中最基本的索引,任何一列上都可创建。该类索引创建时没有任何限制条件,只是为了加快查询的速度。

2)UNIQUE:唯一索引,该索引列的所有值都只能出现一次,即必须唯一。主键索引是不允许值为空的唯一索引,用于唯一标识一条记录。

3)FULLTEXT:全文索引,主要用来查找文本中的关键字,判断字段是否包含,只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。

4)SPATIAL:空间索引,对空间数据类型的字段建立的索引,主要用于地理空间数据类型 GEOMETRY,只能在存储引擎为 MyISAM 的表中创建。

注:联合索引是基于多个字段下的以上索引。

5.建立索引的注意事项

1)除了主键(自带主键索引)和常用于group by、order by、distance等推荐建立索引的字段外,尽量选择类型小的创建索引,比如int等整数类型。其对应的索引占用空间也小,一页中放置的记录就更多,I/O损耗就更少。

2)使用字符串前缀创建索引。即需要为一个存放了很长字符串的字段需要建立索引时(该字段作为where中的查询条件),可以取该字段的前若干字符创建索引。既节省空间,又减少了字符比较的时间。

(注:基于以上规则,使用varchar类型字段建立索引时,必须根据区分度指定索引长度,区分度公式:count(distinct left(列名,索引长度))/count(*),越小越好)

比如,使用address字段建立索引,根据以下代码查询区分度,选择最小的作为索引长度:

select count(distinct left(address,6))/count(*)as s1,
count(distinct left(address,8))/count(*)as s2,
count(distinct left(address,10))/count(*)as s3
from table

3)多个字段需要建立索引时,建议建立联合索引优于单值索引。

4)建立联合索引时,建议把使用最频繁的列放在联合索引的左侧(联合索引是使用多列索引的第一列(最左)构建的 B+ Tree)。

5)每张表上索引数量一般不超过6个

相关文章:

浅谈MySQL索引

目录 1.索引的定义 2.索引的原理 3.Hash索引与B Tree索引 4.索引的分类 5.建立索引的注意事项 1.索引的定义 索引是存储引擎用于快速找到数据记录的一种数据结构&#xff0c;它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 索…...

安装包UI美化之路-通过nsNiuniuSkin来做Electron程序的打包、发布与升级

nsNiuniuSkin从发布之初&#xff0c;因其简单、简洁、高效&#xff0c;受到了非常多公司的青睐&#xff0c;现在已经越来越多的公司采用我们的这套解决方案来制作安装包了&#xff01; 从一个安装包UI插件&#xff0c;逐步演化成一套集美观、安全、简洁、自动化为一体的完整的…...

飞鹅打印机怎么样?飞鹅打印机好用吗?飞鹅打印机怎么知道订单是否漏单?

外卖打印机怎么选?飞鹅打印机好用吗&#xff1f;飞鹅智能云打印机产品专注于云打印的解决方案和技术服务提供。2019 年飞鹅已经成为国内先进的云打印服务提供商&#xff0c;主要是服务美团、饿了么客户&#xff0c;产品主要优势&#xff1a;自动接单、自动打印&#xff0c;无需…...

网络协议(八):传输层-TCP(三次握手、四次挥手原理)

网络协议系列文章 网络协议(一)&#xff1a;基本概念、计算机之间的连接方式 网络协议(二)&#xff1a;MAC地址、IP地址、子网掩码、子网和超网 网络协议(三)&#xff1a;路由器原理及数据包传输过程 网络协议(四)&#xff1a;网络分类、ISP、上网方式、公网私网、NAT 网络…...

最新OpenMVG编译安装与逐命令运行增量式和全局式SfM教程

openmvg是一个轻便的可以逐步运行的SfM开源库&#xff0c;它同时实现了增量式和全局式两种算法。 说明文档地址&#xff1a;https://openmvg.readthedocs.io/en/latest/ github主页地址&#xff1a;https://github.com/openMVG/openMVG 1 编译安装 openmvg的安装比较简单&…...

数据结构与算法系列之插入排序

&#x1f497; &#x1f497; 博客:小怡同学 &#x1f497; &#x1f497; 个人简介:编程小萌新 &#x1f497; &#x1f497; 如果博客对大家有用的话&#xff0c;请点赞关注再收藏 &#x1f31e; 什么是插入排序 有一个已经有序的数据序列&#xff0c;要求在这个已经排好的数…...

Text to image论文精读ALR-GAN:文本到图像合成的自适应布局优化

ALR-GAN是北京工业大学学者提出的一种自适应布局优化生成对抗网络&#xff0c;其可以在没有任何辅助信息的情况下自适应地优化合成图像的布局。 文章发表于2023年&#xff0c;IEEE Transactions on Multimedia&#xff08;TMM&#xff09;期刊&#xff08;CCF B&#xff0c;JCR…...

windows版 redis在同一局域网下互联

项目场景&#xff1a; 同一局域网下各个主机互相连接同一个redis 问题描述 无法连接 原因分析&#xff1a; 没有放行对方的地址 解决方案&#xff1a; 修改配置文件 最重要的一步如下 然后把 redis.windows.conf的文件也照上面的修改一下保持一致 然后安装一下redis服务这…...

Near-Optimal Bayesian Online Assortment of Reusable Resources

摘要 受租赁服务在电子商务中的应用的激励&#xff0c;我们考虑为不同类型的到达消费者提供可重复使用资源的在线分类的收入最大化。我们针对贝叶斯环境中的最优在线策略设计了具有竞争力的在线算法&#xff0c;其中类型随时间独立于已知的异构分布绘制。在初始库存最小值cmin…...

数据库复习2

一. 简答题&#xff08;共1题&#xff0c;100分&#xff09; 1. (简答题) 存在数据库test&#xff0c;数据库中有如下表&#xff1a; 1.学生表 Student(Sno,Sname,Sage,Ssex) --Sno 学号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 主键Sno 2.教师表 Teacher(Tno,Tname) --T…...

公众号运营之竞品分析,教你拆解公众号

知己知彼&#xff0c;百战不殆&#xff0c;公众号运营亦是如此。 当运营者只关注自己账号的时候&#xff0c;很容易陷入某个误区中出不来。这个时候就要拓宽我们的视野&#xff0c;多去看看“外面的世界”&#xff0c;不要只局限于自己的一片小天地中。 看看同领域优秀公众号…...

python常见问题详解

Python python 没有多态&#xff0c;而是鸭子类型 多继承&#xff0c;没有接口&#xff0c;可通过语法糖实现接口的作用 lambda中只能有一句 "/"表示之前的参数是必须是位置参数&#xff0c;”**“表示是后面的必须是关键字参数 Python多进程 Python 多线程是伪多线…...

MyBatis-常用SQL操作

一、动态SQL 1.概述】 1.1动态SQL&#xff1a; 是 MyBatis 的强大特性之一&#xff0c;解决拼接动态SQL时候的难题&#xff0c;提高开发效 1.2分类&#xff1a; if choose(when,otherwise) trim(where,set) foreach 2.if 2.1 做 where 语句后面条件查询的,if 语句是可以…...

DSPE-PEG-TCO;磷脂-聚乙二醇-反式环辛烯科研用化学试剂简介

中文名称 磷脂-聚乙二醇-反式环辛烯 英文名称 DSPE-PEG-TCO 外观&#xff1a;粉末或半固体&#xff0c;取决于分子量。 溶剂&#xff1a;溶于大部分有机溶剂&#xff0c;如&#xff1a;DCM、DMF、DMSO、THF等等。在水中有很好的溶解性 稳定性&#xff1a;冷藏保存&#xff…...

华为OD机试真题Java实现【最小施肥机能效】真题+解题思路+代码(20222023)

最小施肥机能效 某农场主管理了一大片果园,fields[i]表示不同果林的面积,单位:( m 2 m^2 m2),现在要为所有的果林施肥且必须在 n 天之内完成,否则影响收成。 小布是果林的工作人员,他每次选择一片果林进行施肥,且一片果林施肥完后当天不再进行施肥作业。 假设施肥机的…...

【问题记录】【排查问题的方法总结】vue3中数据失去响应式?为什么数据变了,视图只更新了一次就不再更新了?

一、问题概述&#xff1a; 持续请求的数据变动之后&#xff0c;控制台输出绑定的响应式变量 mapObj 的确变了&#xff0c;但是视图上只更新了一次&#xff0c;后续就不再更新了。 二、排查过程&#xff1a; PC上用定时器setInterval模拟数据(全是小于0的数据)更新&#xff0…...

基于遗传算法的柔性生产调度研究(Matlab代码实现)

&#x1f468;‍&#x1f393;个人主页&#xff1a;研学社的博客&#x1f4a5;&#x1f4a5;&#x1f49e;&#x1f49e;欢迎来到本博客❤️❤️&#x1f4a5;&#x1f4a5;&#x1f3c6;博主优势&#xff1a;&#x1f31e;&#x1f31e;&#x1f31e;博客内容尽量做到思维缜密…...

Heroku的12条准则

I. Codebase One codebase tracked in revision control, many deploys 要有代码仓库&#xff0c;多版本控制&#xff0c;如使用git来管理代码仓库。 II. Dependencies Explicitly declare and isolate dependencies 明确声明依赖&#xff0c;隔离依赖。强依赖往往会导致连…...

Qt图片定时滚动

目录参考结构PicturePlay.promain.cpppictureplay.hpictureplay.cpppictureplay.ui效果参考 Qt图片浏览器 QT制作一个图片播放器 Qt中自适应的labelpixmap充满窗口后&#xff0c;无法缩小只能放大 可以显示jpg、jpeg、png、bmp。可以从电脑上拖动图到窗口并显示出来或者打开文件…...

深度学习引言

动手学深度学习pytorch版-笔记原文链接日常生活中的机器学习机器学习中的关键组件数据模型目标函数优化算法各种机器学习问题监督学习回归分类标记问题搜索推荐系统序列学习无监督学习与环境互动强化学习特点小结原文链接 动手学深度学习pytorch中文版 日常生活中的机器学习 …...

变量 varablie 声明- Rust 变量 let mut 声明与 C/C++ 变量声明对比分析

一、变量声明设计&#xff1a;let 与 mut 的哲学解析 Rust 采用 let 声明变量并通过 mut 显式标记可变性&#xff0c;这种设计体现了语言的核心哲学。以下是深度解析&#xff1a; 1.1 设计理念剖析 安全优先原则&#xff1a;默认不可变强制开发者明确声明意图 let x 5; …...

日语AI面试高效通关秘籍:专业解读与青柚面试智能助攻

在如今就业市场竞争日益激烈的背景下&#xff0c;越来越多的求职者将目光投向了日本及中日双语岗位。但是&#xff0c;一场日语面试往往让许多人感到步履维艰。你是否也曾因为面试官抛出的“刁钻问题”而心生畏惧&#xff1f;面对生疏的日语交流环境&#xff0c;即便提前恶补了…...

K8S认证|CKS题库+答案| 11. AppArmor

目录 11. AppArmor 免费获取并激活 CKA_v1.31_模拟系统 题目 开始操作&#xff1a; 1&#xff09;、切换集群 2&#xff09;、切换节点 3&#xff09;、切换到 apparmor 的目录 4&#xff09;、执行 apparmor 策略模块 5&#xff09;、修改 pod 文件 6&#xff09;、…...

五年级数学知识边界总结思考-下册

目录 一、背景二、过程1.观察物体小学五年级下册“观察物体”知识点详解&#xff1a;由来、作用与意义**一、知识点核心内容****二、知识点的由来&#xff1a;从生活实践到数学抽象****三、知识的作用&#xff1a;解决实际问题的工具****四、学习的意义&#xff1a;培养核心素养…...

Python 包管理器 uv 介绍

Python 包管理器 uv 全面介绍 uv 是由 Astral&#xff08;热门工具 Ruff 的开发者&#xff09;推出的下一代高性能 Python 包管理器和构建工具&#xff0c;用 Rust 编写。它旨在解决传统工具&#xff08;如 pip、virtualenv、pip-tools&#xff09;的性能瓶颈&#xff0c;同时…...

第7篇:中间件全链路监控与 SQL 性能分析实践

7.1 章节导读 在构建数据库中间件的过程中&#xff0c;可观测性 和 性能分析 是保障系统稳定性与可维护性的核心能力。 特别是在复杂分布式场景中&#xff0c;必须做到&#xff1a; &#x1f50d; 追踪每一条 SQL 的生命周期&#xff08;从入口到数据库执行&#xff09;&#…...

Java求职者面试指南:Spring、Spring Boot、Spring MVC与MyBatis技术解析

Java求职者面试指南&#xff1a;Spring、Spring Boot、Spring MVC与MyBatis技术解析 一、第一轮基础概念问题 1. Spring框架的核心容器是什么&#xff1f;它的作用是什么&#xff1f; Spring框架的核心容器是IoC&#xff08;控制反转&#xff09;容器。它的主要作用是管理对…...

【Linux】Linux安装并配置RabbitMQ

目录 1. 安装 Erlang 2. 安装 RabbitMQ 2.1.添加 RabbitMQ 仓库 2.2.安装 RabbitMQ 3.配置 3.1.启动和管理服务 4. 访问管理界面 5.安装问题 6.修改密码 7.修改端口 7.1.找到文件 7.2.修改文件 1. 安装 Erlang 由于 RabbitMQ 是用 Erlang 编写的&#xff0c;需要先安…...

Vue3 PC端 UI组件库我更推荐Naive UI

一、Vue3生态现状与UI库选择的重要性 随着Vue3的稳定发布和Composition API的广泛采用&#xff0c;前端开发者面临着UI组件库的重新选择。一个好的UI库不仅能提升开发效率&#xff0c;还能确保项目的长期可维护性。本文将对比三大主流Vue3 UI库&#xff08;Naive UI、Element …...

拟合问题处理

在机器学习中&#xff0c;核心任务通常围绕模型训练和性能提升展开&#xff0c;但你提到的 “优化训练数据解决过拟合” 和 “提升泛化性能解决欠拟合” 需要结合更准确的概念进行梳理。以下是对机器学习核心任务的系统复习和修正&#xff1a; 一、机器学习的核心任务框架 机…...