快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

【数据库】—详解mysql索引的数据结构

请联系QQ:1793040 索取软件

索引通过将无序数据转换为相对有序的数据来提高查询速度。

使用索引的好处:

  1. 通过创建唯一索引,可以确保数据库表中每一行数据的唯一性。
  2. 可以大大加快数据检索速度
  3. 将随机IO更改为顺序IO(顺序IO不需要多个磁盘搜寻,因此它比随机IO快得多,并且在进行groupby查询时无需排序)
  4. 可以加快表之间的连接速度,特别是在实现数据的引用完整性方面

注意:

创建索引后,在添加,删除和修改表中的数据时,还必须动态维护索引,这会降低数据维护的速度。

索引需要物理空间。除了数据表占用的数据空间外,每个索引还占用一定数量的物理空间。如果要使用简历聚集索引,则所需空间将更大

创建和维护索引需要花费时间。时间随着数据量的增加而增加。

索引使用情况:

在需要经常搜索的列上使用索引可以加快搜索速度。

在经常使用where子句来加速条件判断的列上创建索引

在经常需要排序的列上使用索引非常有效,但是非常大的表的维护开销非常大,因此不适合创建索引。

在经常使用的连接列上,这些列是一些外键,可以加快连接速度。

在以下情况下避免建立索引:

避免将函数应用到where子句中的字段,这将导致索引未命中。

使用InnoDB时,请使用与业务无关的自增量主键作为索引(即逻辑主键),而不要使用业务主键

将要索引的列设置为非null,否则将导致引擎放弃使用索引并执行全表扫描

删除长期未使用的索引,未使用的索引将导致不必要的性能消耗

当极限偏移量查询缓慢时,您可以使用索引来提高性能

避免冗余索引:如果索引具有相同的功能,则肯定会命中。如果(name,city)和(name)是冗余索引,则通常应扩展现有索引,而不要为其创建新的一个索引。

覆盖索引:

如果索引包含所有需要查询的字段的值,我们称其为覆盖索引。在InnoDB存储引擎中,如果它不是主键索引,则叶节点将存储主键+列值,最终它必须返回表,即通过主键再次找到该表。速度较慢,并且覆盖索引是检查索引是否对应,并且不会返回到表。

最左前缀原则:MySQL可以按一定顺序引用多个列。该索引称为联合索引。如果查询条件与索引<左侧的一列或多列完全匹配,(如果跳过左列以匹配右列,则不会命中,但是仅当顺序不同,但是查询时使用索引中的列,查询引擎将自动优化查询顺序)。可以使用该列。因此,列的顺序确定可以命中索引的列数。

MySQL的基本存储结构是页面(记录存储在页面中)。每个数据页可以形成一个双向链接列表。每个数据页都会为存储在其中的记录生成一个页面目录。内容可以形成单向链接列表。

数据页面和页面目录之间的关系如下:

页面”>

通过主键查找记录的过程:

从第一页开始,沿着双向链接列表搜索每个页面。每个数据页面将为存储在其中的记录生成一个页面目录。通过主键搜索特定记录时,可以在目录页面上使用<二分法快速找到相应的插槽,然后遍历插槽中的相应分组记录以快速找到指定的记录。

常规查询过程:

当查询未经任何优化的SQL语句时,我们将首先遍历双向链表以找到它所在的页面。由于它不是基于主键查询的,因此我们将从最小的记录开始遍历页面所在的单个链接列表中的每个记录,然后比较每个记录是否为复合搜索条件。

表存储结构

记录头信息中的record_type属性,每个值的含义如下:

0:普通用户记录

1:目录项记录

2:最低记录

3:最高记录

>

当我们使用主键索引时,存储引擎将根据主键的大小按顺序将页面中的记录安排到一个单向链接列表中

然后根据每页的最大主键值对页面进行排序,以形成有序的双向链接列表,要求下一页中的主键值必须大于主键值,因此在双向链接列表中,页码基本上是不连续的。

为了快速找到页面,通过在每个页面上添加一条记录,该记录仅包含两个值,即:页面用户记录中最小的主键值键,以及页码page_no

然后,使用page方法将这些页面记录连接到称为目录条目记录的页面中的单链接列表中。目录条目记录页面通过页面记录的主键值生成页面目录(页面目录)以加快页面查询速度,通过这些主键值,然后将这些目录条目的顺序连接成双链表。

如果数据非常大,将逐步生成目录项目记录页面,直到获得顶层目录页面。这是B +树。因此,我们的实际用户记录实际上存储在B +树的最低节点上。

InnoDB存储引擎将自动为我们创建聚簇索引,聚簇索引的功能是:

1.使用记录主键值的大小对记录和页面进行排序,其中包括三种含义:

  • 页面中的记录根据主键的顺序排列在单个链接列表中。
  • 每个存储用户记录的页面也根据页面中记录的主键的大小排列在双链表中。
  • 每个存储目录项的页面也根据页面中记录的主键的顺序排列在双链表中。

2. B +树的叶节点存储完整的用户记录

当查询主键时,我们将使用上面的B +树结构进行查询,如果要查询其他列,那么还将为相应的列创建这样的B +树。

但是,页面中的记录以指定的列大小的顺序排列在单个链接列表中。

每个存储用户记录的页面也根据页面中记录的指定列大小的顺序排列在双链表中。

每个存储目录项的页面也根据记录在页面中的指定列大小的顺序排列在双链表中。

该B +树的叶节点不存储完整的用户记录,而仅指定column +主键的两列的值,因此我们必须根据主键的值进入聚簇索引以查找再次完成用户记录此过程也称为返回表。

因此,此B +树也称为二级索引(英文二级索引)或辅助索引。由于我们将c2列的大小用作B +树的排序规则,因此我们也将此B +树称为为c2列创建的索引。

我们还可以为多个列创建索引。存储引擎将首先对最左侧列的记录和页面进行排序。如果最左列中的记录相同,则将对右列进行排序。这就是为什么最左边的前缀原则只能命中左边确切的一列或几列连续列的原因。

当前,大多数数据库系统和文件系统都使用B-Tree或其变体B + Tree作为索引结构

>

B树是一种多向搜索树,其特征是:

  • 关键字分布在整个树中
  • 出现任何关键字,并且仅出现在一个节点中
  • 搜索可能以非叶子节点结束
  • 搜索性能等同于在完整的关键字集中进行二进制搜索
  • 具有自动电平控制

    • B树中的每个内部节点将包含一定数量的键。通常,键的数量在d和2d之间选择。实际上,键值占据了节点中的大部分空间。因子2将确保可以拆分或合并节点。如果内部节点具有2d键值,则将键值添加到此节点的过程将使用2d键值的2d键值拆分该节点,并将此键值添加到父节点。每个拆分节点需要最少数量的密钥。同样,如果内部节点及其邻居都具有d个密钥,则密钥将通过与其邻居合并而被删除。删除此键值将导致该节点具有d-1个键值;与邻居的合并将添加d个键值,再加上从邻居节点的父节点移出的键值。结果是2d个键被完全填充。

B树搜索:从根节点开始,对该节点内的关键字(有序)序列执行二进制搜索,如果命中,它将结束,否则输入查询范围关键字Son的节点;重复直到相应的son指针为空或已经是叶节点;

>

B +树是B树的一种变体,mysql使用B +树实现索引结构

B +树的特征是:

  • 非叶节点的子树指针的数量与关键字的数量相同
  • 所有叶节点都连接到一个链表中,并且链表是有序的
  • 所有关键字都出现在叶节点上,因此不可能在非叶节点上命中
  • 内部节点不存储数据,仅存储密钥
  • 非叶子节点等效于叶子节点的索引,叶子节点等效于存储数据的数据层
  • 适用于文件索引系统()

索引通常以文件形式存储在磁盘上。索引检索需要磁盘I/O操作。由于存储介质的特性,磁盘本身的访问速度比主存储器要慢得多,加上机械移动成本速度通常是主存储器的百分之几,因此为了提高效率,磁盘I/O应该是最小化。为了实现这一点,并不是严格按需读取磁盘,而是每次都会预先读取。即使只需要一个字节,磁盘也将从该位置开始,并按顺序将一定长度的数据读回内存。

在数据库中,B树的一个节点的大小设置为等于一页,因此每个节点仅可以完全加载一个I/O。

MyISAM索引的实现:

InnoDB中的索引是数据,即,聚集索引的B +树的叶子节点已包含所有完整的用户记录,尽管MyISAM的索引方案也使用B +树,但索引和数据已存储分别:

按照插入时间的顺序将记录存储在表中的存储空间中,我们可以通过行号快速访问记录

MyISAM将为表的主键创建一个B +树索引,但这不是存储在B +树的叶节点中的完整用户记录,而是主键值+行号的组合。也就是说,首先通过索引找到相应的行号,然后再通过行号找到相应的记录。因此,MyISAM中的主键查询需要执行表返回操作,这意味着在MyISAM中创建的所有索引都是辅助索引。

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1793040