数据库索引相关知识点

最新推荐文章于 2024-09-16 20:17:25 发布

墨夜瓴枫

最新推荐文章于 2024-09-16 20:17:25 发布

阅读量367

点赞数

分类专栏：面试宝典文章标签：数据库索引面试回答实战

本文链接：https://blog.csdn.net/adam_swx/article/details/102663350

版权

面试宝典专栏收录该内容

11 篇文章 0 订阅

订阅专栏

索引的概念以及分类
不同索引的使用情况与优缺点
关于索引的面试相关的问题，参考索引面试问答

什么是数据库索引

官方回答：“索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。索引的一个主要目的就是加快检索表中数据的方法，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。”
其实说白了数据库索引就是数据库的一个数据结构，确切的讲就是该数据结构中存储了一张表中的某一列的所有值，也就是说索引是基于数据表中的某一列而创建的，总而言之：一个索引是由表中某一列上的数据组成，并且这些数据存储在某个数据结构中。

索引的分类

1.聚簇索引（聚集索引）

聚簇索引并不是一种索引类型，而是一种数据的存储方式，聚簇索引的索引项的排序方式和表中数据记录排序方式一致。MySQL数据库中innodb存储引擎，B+树索引可以分为聚簇索引（也称聚集索引，clustered index）和辅助索引（有时也称非聚簇索引或二级索引，secondary index，non-clustered index）。这两种索引内部都是B+树，聚集索引的叶子节点存放着一整行的数据。Innobd中的主键索引是一种聚簇索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。
Innodb使用的是聚簇索引，MyISam使用的是非聚簇索引。

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引，Innodb会通过主键聚集数据，如果没有定义主键的话，会选择非空放的唯一索引来代替，如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引

在汉语词典中，用拼音来查汉字就是聚簇索引，正文中字都是按照拼音排序的如上图。
聚集索引的使用场合：
a.查询命令的回传结果是以该字段为排序依据的；
b.查询的结果返回一个区间的值；
c.查询的结果返回某值相同的大量结果集；
聚集索引会降低 insert，和update操作的性能，所以，是否使用聚集索引要全面衡量。

2.非聚簇索引（辅助索引）

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。
比如是汉字和部首并不是排序一致的如下图
在这里插入图片描述

非聚集索引的使用场合为：
　　a.查询所获数据量较少时；
　　b.某字段中的数据的唯一性比较高时；

Innodb聚簇索引和MyIsam非聚簇索引的比较说明

我们知道MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，而Innodb也是使用B+Tree作为索引结构的，但是却与MyIsam完全不同详见B+Tree与B-Tree的区别

主键索引

在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引
在这里插入图片描述
图为InnoDB主索引（同时也是数据文件）的示意图，可以看到叶子节点包含了完整的数据记录，这种索引就叫做聚集索引，因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

Innodb的辅助索引

InnoDB的所有辅助索引都引用主键作为data域，InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引（Secondary Index，也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义、很多索引，则争取尽量把主键定义得小一些。因为InnoDB 不会压缩索引，而聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白
1.为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
2.用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。将主键组织到一棵B+树中，而行数据就储存在叶子节点上，譬如若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

MyISAM索引实现

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址
1）主键索引
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图：
在这里插入图片描述
可以看出MyIsam的索引文件是保存的数据记录的地址
2）辅助索引
在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示
在这里插入图片描述
同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复，由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

为了更形象说明这两种索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。
问题：那么主键索引是聚集索引还是非聚集索引？
答：在Innodb下主键索引是聚集索引，在Myisam下主键索引是非聚集索引
在这里插入图片描述