Loading [MathJax]/jax/output/CommonHTML/config.js
前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >【建议收藏】MySQL 三万字精华总结 —索引(二)[通俗易懂]

【建议收藏】MySQL 三万字精华总结 —索引(二)[通俗易懂]

作者头像
全栈程序员站长
发布于 2022-07-18 09:24:59
发布于 2022-07-18 09:24:59
5880
举报

大家好,又见面了,我是全栈君。

四、索引

❝ 说说你对 MySQL 索引的理解? 数据库索引的原理,为什么要用 B+树,为什么不用二叉树? 聚集索引与非聚集索引的区别? InnoDB引擎中的索引策略,了解过吗? 创建索引的方式有哪些? 聚簇索引/非聚簇索引,mysql索引底层实现,为什么不用B-tree,为什么不用hash,叶子结点存放的是数据还是指向数据的内存地址,使用索引需要注意的几个地方?

  • MYSQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构,所以说索引的本质是:数据结构
  • 索引的目的在于提高查询效率,可以类比字典、 火车站的车次表、图书的目录等 。
  • 可以简单的理解为“排好序的快速查找数据结构”,数据本身之外,数据库还维护者一个满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。下图是一种可能的索引方式示例。

左边的数据表,一共有两列七条记录,最左边的是数据记录的物理地址。为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值,和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取到对应的数据,从而快速检索出符合条件的记录。

  • 索引本身也很大,不可能全部存储在内存中,一般以索引文件的形式存储在磁盘上
  • 平常说的索引,没有特别指明的话,就是B+树(多路搜索树,不一定是二叉树)结构组织的索引。其中聚集索引,次要索引,覆盖索引,符合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。此外还有哈希索引等。

基本语法:

  • 创建:
    • 创建索引:CREATE [UNIQUE] INDEX indexName ON mytable(username(length)); 如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
    • 修改表结构(添加索引):ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)
  • 删除:DROP INDEX [indexName] ON mytable;
  • 查看:SHOW INDEX FROM table_name\G –可以通过添加 \G 来格式化输出信息。
  • 使用ALERT命令
    • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
    • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
    • ALTER TABLE tbl_name ADD INDEX index_name (column_list) 添加普通索引,索引值可出现多次。
    • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)该语句指定了索引为 FULLTEXT ,用于全文索引。

优势

  • 提高数据检索效率,降低数据库IO成本
  • 降低数据排序的成本,降低CPU的消耗

劣势

  • 索引也是一张表,保存了主键和索引字段,并指向实体表的记录,所以也需要占用内存
  • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段, 都会调整因为更新所带来的键值变化后的索引信息

MySQL索引分类

数据结构角度
  • B+树索引
  • Hash索引
  • Full-Text全文索引
  • R-Tree索引
从物理存储角度
  • 聚集索引(clustered index)
  • 非聚集索引(non-clustered index),也叫辅助索引(secondary index) 聚集索引和非聚集索引都是B+树结构
从逻辑角度
  • 主键索引:主键索引是一种特殊的唯一索引,不允许有空值
  • 普通索引或者单列索引:每个索引只包含单个列,一个表可以有多个单列索引
  • 多列索引(复合索引、联合索引):复合索引指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用复合索引时遵循最左前缀集合
  • 唯一索引或者非唯一索引
  • 空间索引:空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建

❝ 为什么MySQL 索引中用B+tree,不用B-tree 或者其他树,为什么不用 Hash 索引 聚簇索引/非聚簇索引,MySQL 索引底层实现,叶子结点存放的是数据还是指向数据的内存地址,使用索引需要注意的几个地方? 使用索引查询一定能提高查询的性能吗?为什么?

MySQL索引结构

首先要明白索引(index)是在存储引擎(storage engine)层面实现的,而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型,它们的实现和行为也可能有所差别。

B+Tree索引

MyISAM 和 InnoDB 存储引擎,都使用 B+Tree的数据结构,它相对与 B-Tree结构,所有的数据都存放在叶子节点上,且把叶子节点通过指针连接到一起,形成了一条数据链表,以加快相邻数据的检索效率。

先了解下 B-Tree 和 B+Tree 的区别(下节补充)

MyISAM主键索引与辅助索引的结构

MyISAM引擎的索引文件和数据文件是分离的。MyISAM引擎索引结构的叶子节点的数据域,存放的并不是实际的数据记录,而是数据记录的地址。索引文件与数据文件分离,这样的索引称为”非聚簇索引“。MyISAM的主索引与辅助索引区别并不大,只是主键索引不能有重复的关键字。

在MyISAM中,索引(含叶子节点)存放在单独的.myi文件中,叶子节点存放的是数据的物理地址偏移量(通过偏移量访问就是随机访问,速度很快)。

主索引是指主键索引,键值不可能重复;辅助索引则是普通索引,键值可能重复。

通过索引查找数据的流程:先从索引文件中查找到索引节点,从中拿到数据的文件指针,再到数据文件中通过文件指针定位了具体的数据。辅助索引类似。

InnoDB主键索引与辅助索引的结构

InnoDB引擎索引结构的叶子节点的数据域,存放的就是实际的数据记录(对于主索引,此处会存放表中所有的数据记录;对于辅助索引此处会引用主键,检索的时候通过主键到主键索引中找到对应数据行),或者说,InnoDB的数据文件本身就是主键索引文件,这样的索引被称为“聚簇索引”,一个表只能有一个聚簇索引。

主键索引:

我们知道InnoDB索引是聚集索引,它的索引和数据是存入同一个.idb文件中的,因此它的索引结构是在同一个树节点中同时存放索引和数据,如下图中最底层的叶子节点有三行数据,对应于数据表中的id、stu_id、name数据项。

在Innodb中,索引分叶子节点和非叶子节点,非叶子节点就像新华字典的目录,单独存放在索引段中,叶子节点则是顺序排列的,在数据段中。Innodb的数据文件可以按照表来切分(只需要开启innodb_file_per_table),切分后存放在xxx.ibd中,默认不切分,存放在xxx.ibdata中。

辅助(非主键)索引:

这次我们以示例中学生表中的name列建立辅助索引,它的索引结构跟主键索引的结构有很大差别,在最底层的叶子结点有两行数据,第一行的字符串是辅助索引,按照ASCII码进行排序,第二行的整数是主键的值。

这就意味着,对name列进行条件搜索,需要两个步骤:

① 在辅助索引上检索name,到达其叶子节点获取对应的主键;

② 使用主键在主索引上再进行对应的检索操作

这也就是所谓的“回表查询

InnoDB 索引结构需要注意的点

  1. 数据文件本身就是索引文件
  2. 表数据文件本身就是按 B+Tree 组织的一个索引结构文件
  3. 聚集索引中叶节点包含了完整的数据记录
  4. InnoDB 表必须要有主键,并且推荐使用整型自增主键

正如我们上面介绍 InnoDB 存储结构,索引与数据是共同存储的,不管是主键索引还是辅助索引,在查找时都是通过先查找到索引节点才能拿到相对应的数据,如果我们在设计表结构时没有显式指定索引列的话,MySQL 会从表中选择数据不重复的列建立索引,如果没有符合的列,则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,并且这个字段长度为6个字节,类型为整型。

❝ 那为什么推荐使用整型自增主键而不是选择UUID?

  • UUID是字符串,比整型消耗更多的存储空间;
  • 在B+树中进行查找时需要跟经过的节点值比较大小,整型数据的比较运算比字符串更快速;
  • 自增的整型索引在磁盘中会连续存储,在读取一页数据时也是连续;UUID是随机产生的,读取的上下两行数据存储是分散的,不适合执行where id > 5 && id < 20的条件查询语句。
  • 在插入或删除数据时,整型自增主键会在叶子结点的末尾建立新的叶子节点,不会破坏左侧子树的结构;UUID主键很容易出现这样的情况,B+树为了维持自身的特性,有可能会进行结构的重构,消耗更多的时间。

❝ 为什么非主键索引结构叶子节点存储的是主键值?

保证数据一致性和节省存储空间,可以这么理解:商城系统订单表会存储一个用户ID作为关联外键,而不推荐存储完整的用户信息,因为当我们用户表中的信息(真实名称、手机号、收货地址···)修改后,不需要再次维护订单表的用户数据,同时也节省了存储空间。

Hash索引
  • 主要就是通过Hash算法(常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法),将数据库字段数据转换成定长的Hash值,与这条数据的行指针一并存入Hash表的对应位置;如果发生Hash碰撞(两个不同关键字的Hash值相同),则在对应Hash键下以链表形式存储。 检索算法:在检索查询时,就再次对待查关键字再次执行相同的Hash算法,得到Hash值,到对应Hash表对应位置取出数据即可,如果发生Hash碰撞,则需要在取值时进行筛选。目前使用Hash索引的数据库并不多,主要有Memory等。 MySQL目前有Memory引擎和NDB引擎支持Hash索引。
full-text全文索引
  • 全文索引也是MyISAM的一种特殊索引类型,主要用于全文索引,InnoDB从MYSQL5.6版本提供对全文索引的支持。
  • 它用于替代效率较低的LIKE模糊匹配操作,而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
  • 同样使用B-Tree存放索引数据,但使用的是特定的算法,将字段数据分割后再进行索引(一般每4个字节一次分割),索引文件存储的是分割前的索引字符串集合,与分割后的索引信息,对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。
R-Tree空间索引

空间索引是MyISAM的一种特殊索引类型,主要用于地理空间数据类型

❝ 为什么Mysql索引要用B+树不是B树?

用B+树不用B树考虑的是IO对性能的影响,B树的每个节点都存储数据,而B+树只有叶子节点才存储数据,所以查找相同数据量的情况下,B树的高度更高,IO更频繁。数据库索引是存储在磁盘上的,当数据量大时,就不能把整个索引全部加载到内存了,只能逐一加载每一个磁盘页(对应索引树的节点)。其中在MySQL底层对B+树进行进一步优化:在叶子节点中是双向链表,且在链表的头结点和尾节点也是循环指向的。

❝ 面试官:为何不采用Hash方式?

因为Hash索引底层是哈希表,哈希表是一种以key-value存储数据的结构,所以多个数据在存储关系上是完全没有任何顺序关系的,所以,对于区间查询是无法直接通过索引查询的,就需要全表扫描。所以,哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树,所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描。

哈希索引不支持多列联合索引的最左匹配规则,如果有大量重复键值得情况下,哈希索引的效率会很低,因为存在哈希碰撞问题。

哪些情况需要创建索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段
  3. 查询中与其他表关联的字段,外键关系建立索引
  4. 单键/组合索引的选择问题,高并发下倾向创建组合索引
  5. 查询中排序的字段,排序字段通过索引访问大幅提高排序速度
  6. 查询中统计或分组字段

哪些情况不要创建索引

  1. 表记录太少
  2. 经常增删改的表
  3. 数据重复且分布均匀的表字段,只应该为最经常查询和最经常排序的数据列建立索引(如果某个数据类包含太多的重复数据,建立索引没有太大意义)
  4. 频繁更新的字段不适合创建索引(会加重IO负担)
  5. where条件里用不到的字段不创建索引

MySQL高效索引

覆盖索引(Covering Index),或者叫索引覆盖, 也就是平时所说的不需要回表操作

  • 就是select的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖
  • 索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据,当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引包含(覆盖)满足查询结果的数据就叫做覆盖索引。
  • 判断标准 使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询,显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/111644.html原文链接:https://javaforall.cn

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2022年2月1,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
MySQL 三万字精华总结 + 面试100 问,吊打面试官绰绰有余(收藏系列)
和其它数据库相比,MySQL有点与众不同,它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上,插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
帅地
2020/06/03
2.8K1
MySQL 三万字精华总结 + 面试100 问,吊打面试官绰绰有余(收藏系列)
【肝帝一周总结:全网最全最细】☀️Mysql 索引数据结构详解与索引优化☀️《❤️记得收藏❤️》
在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储数据结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
苏州程序大白
2021/09/06
8570
12道Mysql常见的面试题
在 MySQL 数据库中,支持上面四种隔离级别,默认的为 Repeatable read (可重复读);而在 Oracle 数据库中,只支持 Serializable (串行化)级别和 Read committed (读已提交)这两种级别,其中默认的为 Read committed 级别。
wscats
2020/06/06
4280
一文搞懂MySQL索引(清晰明了)[通俗易懂]
索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。
全栈程序员站长
2022/09/12
1.4K0
一文搞懂MySQL索引(清晰明了)[通俗易懂]
阿里二面:MySQL索引是怎么支撑千万级表的快速查找?
在 MySQL 官方提到,改善操作性能的最佳方法 SELECT 在查询中测试的一个或多个列上创建索引。索引条目的作用类似于指向表行的指针,从而使查询可以快速确定哪些行与WHERE子句中的条件匹配,并检索这些行的其他列值。所有MySQL数据类型都可以建立索引。
Java程序猿
2022/06/10
1.1K0
MySQL进阶 1:存储引擎、索引
最上层是一些客户端和链接服务,包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
寻求出路的程序媛
2024/05/16
2000
MySQL进阶 1:存储引擎、索引
2021春招 | 一口气搞懂MySQL索引所有知识点
国庆期间看了数据库的很多资料和书籍,这点我在总结的数据库文章里面也提过了,然后我发现我对索引的介绍不全,所以整理了一下自己的笔记,决定来个索引完整版,老规矩可能还是没我正常文章风格那么跳,但是干货一定也能让你有所收获。
王炸
2020/10/29
6420
2021春招 | 一口气搞懂MySQL索引所有知识点
2024年java面试准备--mysql(1)
数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库中表的数据。索引的实现通常使用B树和变种的B+树(MySQL常用的索引就是B+树)。除了数据之外,数据库系统还维护为满足特定查找算法的数据结构,这些数据结构以某种方式引用数据,这种数据结构就是索引。简言之,索引就类似于书本,字典的目录。
终有救赎
2023/10/16
2230
2024年java面试准备--mysql(1)
MySQL索引原理——B树
1、MyISAM是MySQL 5.5之前版本默认的存储引擎,从5.5之后,InnoDB开始成为MySQL默认的存储引擎。MyISAM和InnoDB都是使用B+树实现主键索引、唯一索引和非主键索引。
saintyyu
2021/11/22
6830
MySQL索引原理——B树
MySQL索引实现原理分析
目前大部分数据库系统及文件系统都采用 B-Tree(B 树)或其变种 B+Tree(B+树)作为索引结构。B+Tree 是数据库系统实现索引的首选数据结构。
desperate633
2018/08/23
6840
MySQL学习笔记(5) 增删改查,高级查询,和索引
索引是一个单独存储在磁盘上的数据库结构,它们存储着对数据表里的数据记录的应用指针。
张云飞Vir
2021/06/29
7040
彻底搞懂MySQL的索引
MyISAM和InnoDB是MySQL最常用的两个存储引擎,本文将进行详尽的介绍和对比。对于MySQL其余几种存储引擎,请读者自行搜索学习。
全菜工程师小辉
2019/08/16
9210
Mysql中的索引
Mysql索引类型 Primary key/主键索引,Innodb 中又叫聚簇索引,InnoDB存储引擎的表会存在主键(唯一非null),如果建表的时候没有指定主键,则会使用第一非空的唯一索引作为聚集索引,否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。 单列索引:索引中只包含一个列。 组合索引:在多个字段上建立的索引,只有在查询条件中顺序的使用了这些索引,索引才有效果。使用组合索引遵循最左前缀原则。 Unique(唯一索引):索引列必须唯一,但允许有空值,若是组合索
入门笔记
2022/06/02
3.5K0
Mysql中的索引
不懂就问,MySQL索引是啥?
索引是帮助数据库高效获取数据的一种数据结构,是基于数据表创建的,它包含了一个表中某些列的值以及记录对应的地址,并且把这些值存在一个数据结构中,常见的有使用哈希表、B+树作为索引。
唔仄lo咚锵
2022/05/08
1.3K0
不懂就问,MySQL索引是啥?
相关推荐
MySQL 三万字精华总结 + 面试100 问,吊打面试官绰绰有余(收藏系列)
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档