数据库索引详解

数据库索引介绍

可能在家在后端学习的时候对数据库一些底层了解不够，只学习了基础的增删改查语句，我为大家介绍一下索引的基本原理：

• 缺点：如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。
• 优点：对于数据库来说，每进入一层，就要从硬盘读取一次数据，这非常致命，因为硬盘的读取时间远远大于数据处理时间，数据库读取硬盘的次数越少越好。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的张一，然后直接向右遍历所有张开头的人，直到条件不满足为止。

也就是说，我们找到第一个满足条件的人之后，直接向右遍历就可以了，由于索引是有序的，所有满足条件的人都会聚集在一起。

而这种定位到最左边，然后向右遍历寻找，就是我们所说的最左前缀原则。