数据库-索引简介


索引简介

众所周知,索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构,SQL的主流索引结构有B+树以及Hash结构,聚集索引以及非聚集索引用的是B+树索引。这篇文章会总结SQL Server以及MySQL的InnoDB和MyISAM两种SQL的索引

SQL Sever索引类型有:唯一索引,主键索引,聚集索引,非聚集索引。

MySQL 索引类型有:唯一索引,主键(聚集)索引,非聚集索引,全文索引

一、聚集索引

聚集(clustered)索引,也叫聚簇索引。

单单从定义来看是不是显得有点抽象,打个比方,一个表就像是我们以前用的新华字典,聚集索引就像是拼音目录,而每个字存放的页码就是我们的数据物理地址,我们如果要查询一个“哇”字,我们只需要查询“哇”字对应在新华字典拼音目录对应的页码,就可以查询到对应的“哇”字所在的位置,而拼音目录对应的A-Z的字顺序,和新华字典实际存储的字的顺序A-Z也是一样的,如果我们中文新出了一个字,拼音开头第一个是B,那么他插入的时候也要按照拼音目录顺序插入到A字的后面,现在用一个简单的示意图来大概说明一下在数据库中的样子:
在这里插入图片描述
注:第一列的地址表示该行数据在磁盘中的物理地址,后面三列才是我们SQL里面用的表里的列,其中id是主键,建立了聚集索引。

结合上面的表格就可以理解这句话了吧:数据行的物理顺序与列值的顺序相同,如果我们查询id比较靠后的数据,那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同,所以也就只能建立一个聚集索引了

二、非聚集索引

非聚集(unclustered)索引。

定义:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。

其实按照定义,除了聚集索引以外的索引都是非聚集索引,只是人们想细分一下非聚集索引,分成普通索引,唯一索引,全文索引。如果非要把非聚集索引类比成现实生活中的东西,那么非聚集索引就像新华字典的偏旁字典,他结构顺序与实际存放顺序不一定一致。

在这里插入图片描述
非聚集索引叶节点仍然是索引节点,只是有一个指针指向对应的数据块,此如果使用非聚集索引查询,而查询列中包含了其他该索引没有覆盖的列,那么他还要进行第二次的查询,查询节点上对应的数据行的数据
如有以下表t1:
在这里插入图片描述

三、索引存储结构


1. B+Tree(MySQL索引的真正存储结构)

二叉树 >> 红黑树 >> B-Tree >> B+Tree
在介绍B+Tree之前,我们先来看下面两个问题:

  1. 为什么要对BTree继续做优化?

要解答这个疑问需要先了解BTree每个节点结构(上面已经说明)和MySQL数据库它是如何读取索引数据的,索引和表数据在不使用的时候是存储在文件中的,也就是磁盘,当我们执行查询操作时会DBMS(数据库管理系统)首先会先从内存中查找,如果找到直接使用,如果找不到则从磁盘文件中读取;操作系统储存数据的最小单位是页(page),一页假设是4K大小(由操作系统决定),对内存和磁盘读取数据是按一页的整数倍读取的。
在这里插入图片描述

这里我们假设数据库一次IO操作就读取1页4K的数据,再假设图中圈起来的元素就是一个大节点,内含多个小节点的索引和数据,其大小是10MB,那么我们要从磁盘中读取完整个大节点需要进行 10M / 4K = 2500次IO操作,这样就可以看出如果大节点数据总量越大,需要执行的IO操作越多,花费的时间也越长,因此为了提高性能,数据库会建议我们一个大节点只存储一页4K大小的数据,这里的数据包含了索引和表记录,另外我们还能计算出树的度Degree应该设置成多大才合理:

Degree = 内存页大小(4K) / 单个索引值字节大小;

进一步分析,索引值的大小相对于整条记录的大小是很小的,如果我们需要查找的数据刚好是在最后,那么前面遍历过的节点中存储的记录数据是不是对我们来说是没用的,它会占用比索引大得多的空间,导致我们一个大节点里能遍历的索引数量大大减少,需要向下继续遍历的几率就更大,花费更多时间查找,那么有没有办法可以优化呢?看下一个问题。

  1. 相对于BTree,B+Tree做了哪些优化?

B+Tree存储结构,只有叶子节点存储数据
新的B+树结构没有在所有的节点里存储记录数据,而是只在最下层的叶子节点存储,上层的所有非叶子节点只存放索引信息,这样的结构可以让单个节点存放下更多索引值,增大度Degree的值,提高命中目标记录的几率。

这种结构会在上层非叶子节点存储一部分冗余数据,但是这样的缺点都是可以容忍的,因为冗余的都是索引数据,不会对内存造成大的负担。

在这里插入图片描述

每个叶子节点都指向下一个叶子节点

在这里插入图片描述

这点优化有什么用呢?我们直接看下面的B+Tree结构,如果我们进行范围查找where id > 4的记录,我们只需要先找到id = 4的记录后自然就能通过叶子节点间的双向指针方便地查询出大于4的所有记录。

参考原文: 聚集索引与非聚集索引
参考原文: mysql存储结构

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
索引是一种数据结构,它可以帮助加快数据库表中数据的检索速度。在 MySQL 数据库中,索引通常被称为 B-tree 索引,它可以加速 SELECT、UPDATE 和 DELETE 操作的速度。在本文中,我们将介绍 MySQL 中的索引,包括索引的类型、如何创建索引、如何使用索引以及索引的优化。 ## 索引的类型 MySQL 支持多种类型的索引,包括以下几种常见的类型: - PRIMARY KEY 索引:用于唯一标识数据库表中的每一行记录。 - UNIQUE 索引:用于确保表中某一列的值是唯一的。 - INDEX 索引:用于加速表中的数据检索操作。 - FULLTEXT 索引:用于全文搜索操作。 ## 如何创建索引 在 MySQL 中,可以使用 CREATE INDEX 语句来创建索引。例如,下面的语句创建一个名为 idx_last_name 的索引,用于加速对 employees 表中 last_name 列的检索: ``` CREATE INDEX idx_last_name ON employees (last_name); ``` 需要注意的是,创建索引可能会增加数据库表的插入、更新和删除操作的时间开销。因此,应该谨慎地考虑是否需要创建索引,以及应该创建哪些索引。 ## 如何使用索引 在 MySQL 中,可以使用 EXPLAIN 语句来查看查询语句的执行计划。如果查询语句使用索引,则在执行计划中会显示使用索引名称。例如,下面的语句使用 EXPLAIN 来查看对 employees 表进行 last_name 列检索的执行计划: ``` EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith'; ``` 如果查询语句没有使用索引,则可以使用 FORCE INDEX 语句来强制使用指定的索引。例如,下面的语句强制使用 idx_last_name 索引来对 employees 表进行 last_name 列检索: ``` SELECT * FROM employees FORCE INDEX (idx_last_name) WHERE last_name = 'Smith'; ``` 需要注意的是,强制使用索引可能会导致性能下降。因此,应该仅在必要时使用强制索引。 ## 索引的优化 在 MySQL 中,可以使用 OPTIMIZE TABLE 语句来优化数据库表。优化表可以帮助减少表中的碎片,提高表的查询性能。例如,下面的语句优化 employees 表: ``` OPTIMIZE TABLE employees; ``` 此外,还可以使用 ANALYZE TABLE 语句来分析表中的数据分布情况,以便优化索引。例如,下面的语句分析 employees 表的数据分布情况: ``` ANALYZE TABLE employees; ``` 需要注意的是,索引的性能可能会受到数据分布的影响。如果表中的数据分布不均匀,则可能需要重新设计索引或优化查询语句以提高性能。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值