数据库索引介绍
可能在家在后端学习的时候对数据库一些底层了解不够,只学习了基础的增删改查语句,我为大家介绍一下索引的基本原理:
- 缺点:如果给表中多个字段加上索引 , 那么就会出现多个独立的索引结构,每个索引(非聚集索引)互相之间不存在关联。每次给字段建一个新索引, 字段中的数据就会被复制一份出来, 用于生成索引。 因此, 给表添加索引,会增加表的体积, 占用磁盘存储空间。
- 优点:对于数据库来说,每进入一层,就要从硬盘读取一次数据,这非常致命,因为硬盘的读取时间远远大于数据处理时间,数据库读取硬盘的次数越少越好。B树的设计思想是,将相关数据尽量集中在一起,以便一次读取多个数据,减少硬盘操作次数。
B树的特点也有三个。
(1)一个节点可以容纳多个值。
(2)除非数据已经填满,否则不会增加新的层。也就是说,B树追求"层"越少越好。
(3)子节点中的值,与父节点中的值,有严格的大小对应关系。一般来说,如果父节点有a个值,那么就有a+1个子节点。如果父节点有两个值(7和16),就对应三个子节点,第一个子节点都是小于7的值,最后一个子节点都是大于16的值,中间的子节点就是7和16之间的值。
B树一个节点可以存储多个元素,相对于完全平衡二叉树整体的树高降低了,磁盘IO效率提高了。
而B+树是B树的升级版,只是把非叶子节点冗余一下,这么做的好处是为了提高范围查找的效率。提高了的原因也无非是会有指针指向下一个节点的叶子节点。
B+树中一个节点为一页或页的倍数最为合适:
如果一个节点的大小小于1页,那么读取这个节点的时候其实也会读出1页,造成资源的浪费。
如果一个节点的大小大于1页,比如1.2页,那么读取这个节点的时候会读出2页,也会造成资源的浪费。
所以为了不造成浪费,所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。
1. 主键
建表的时候都会为表加上主键, 在某些关系数据库中, 如果建表时不指定主键,数据库会拒绝建表的语句执行。 事实上, 一个加了主键的表,并不能被称之为「表」。一个没加主键的表,它的数据无序的放置在磁盘存储器上,一行一行的排列的很整齐, 跟我认知中的「表」很接近。如果给表上了主键,那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,也就是上面说的「平衡树」(B+树)结构,换句话说,就是整个表就变成了一个索引。没错, 再说一遍, 整个表变成了一个索引,也就是所谓的「聚集索引」。
这就是为什么一个表只能有一个主键, 一个表只能有一个「聚集索引」,因为主键的作用就是把「表」的数据格式转换成「索引(平衡树)」的格式放置。
所以,主键,是一种特殊的索引。
- 建立主键就是建立了聚集索引
- 建立非主键索引就是建立了非聚集索引
2. 索引查询
-
在查询主键索引的时候,叶子节点存放的是整行数据的值,我们只需要根据主键按照B+树查询原则(通过聚集索引)找到对应的数据值,即可获得查询结果
-
在查询非主键索引的时候,叶子节点存放的是主键的值。比如,给一个普通字段name加上索引,先通过非聚集索引找到对应的主键的值,再用主键的值通过聚集索引找到需要查询的数据。不管以任何方式查询表, 最终都会利用主键通过聚集索引来定位到数据, 聚集索引(主键)是通往真实数据所在的唯一路径。这种查询方式叫做回表
-
避免回表的方法:覆盖索引,如果我们只需要查询主键id,那么在通过name索引查询的时候就已经拿到了,就不需要回表了。或者我们可以把一个字段上的索引写成双字段的覆盖索引,比如在name字段上再加上age,那么查询name索引的时候不仅返回了主键值id,还返回了age,这就是覆盖索引。
-
查找次数:以树的分叉数为底,记录总数的对数,用公式来表示就是:
3. 最左匹配原则
例如对于下面这一张表
如果我们按照 name 字段来建立索引的话,采用B+树的结构,大概的索引结构如下
如果我们要进行模糊查找,查找name 以“张"开头的所有人的ID,即 sql 语句为
select ID from table where name like '张%'
由于在B+树结构的索引中,索引项是按照索引定义里面出现的字段顺序排序的,索引在查找的时候,可以快速定位到 ID 为 100的张一,然后直接向右遍历所有张开头的人,直到条件不满足为止。
也就是说,我们找到第一个满足条件的人之后,直接向右遍历就可以了,由于索引是有序的,所有满足条件的人都会聚集在一起。
而这种定位到最左边,然后向右遍历寻找,就是我们所说的最左前缀原则。