深入浅出理解数据库索引原理

我们对索引的了解仅限于“加索引能够使查询变快”这个概念，因为我们说索引就是书的目录，通过书的目录就准确的定位到了书籍具体的内容。但是我们同样需要理解几个问题：

（1）为什么要给表加上主键？

（2）为什么加索引后会使查询变快？

（3）为什么加索引后会使写入，修改，删除变慢？

（4）什么情况下要同时在两个字段上建索引？

想要理解索引原理就必须清楚一种数据结构（平衡树（非二叉），也就是b树或者b+树）。有的数据库也使用哈希桶作为索引的数据结构，但主流的RDBMS 都是把平衡树作为数据表默认的数据结构的。

这个网址是关于平衡树的详细解释：https://blog.csdn.net/jacke121/article/details/78268602

一、理解B树

1. B树：属于多叉树又名平衡多路查找树（查询路径不止两个）；

（1）树种的每个节点最多拥有m个子节点且m>=2，空树除外；

（2）除根节点外，每个节点的ceil（m/2）-1<=关键字数量<=m-1,（ceil向上取整，ceil（1.1）=2）；

（3）所有叶子节点均在同一层，叶子节点除了包含关键字和关键字记录的指针外也有指向其他子节点的指针，只不过其指针地址都为null。

（4）如果一个非叶节点有N个子节点，则该节点的关键字数等于N-1；

（5）所有节点关键字是按照递增次序排列的，并遵循左小由大原则；

2、B树的查询流程： 如上图我要从上图中找到E字母，查找流程如下

（1）获取根节点的关键字进行比较，当前根节点关键字为M，E要小于M（26个字母顺序），所以往找到指向左边的子节点（二分法规则，左小右大，左边放小于当前节点值的子节点、右边放大于当前节点值的子节点）；

（2）拿到关键字D和G，D<E<G 所以直接找到D和G中间的节点；

（3）拿到E和F，因为E=E 所以直接返回关键字和指针信息（如果树结构里面没有包含所要查找的节点则返回null）；

3. B树的插入节点流程

    定义一个5阶树（平衡5路查找树;），现在我们要把3、8、31、11、23、29、50、28 这些数字构建出一个5阶树出来;

遵循规则：

（1）当前是要组成一个5路查找树，那么此时m=5,关键字数必须大于等于cei(5/2)-1小于等于5-1（关键字数小于cei(5/2)-1 就要进行节点合并，大于5-1就要进行节点拆分）；

（2）满足左大右小的排序规则;

4. B树节点的删除

规则：

（1）当前是要组成一个5路查找树，那么此时m=5,关键字数必须大于等于cei(5/2)-1小于等于5-1；

（2）满足左大右小的排序规则;

（3）关键字数小于二时先从子节点取，子节点没有符合条件时就向向父节点取，取中间值往父节点放；

B树相对于平衡二叉树的不同是，每个节点包含的关键字增多了，特别是在B树应用到数据库中的时候，数据库充分利用了磁盘块的原理（磁盘数据存储是采用块的形式存储的，每个块的大小一般为4K，每次IO进行数据读取时，同一个磁盘块的数据可以一次性读取出来）把节点大小限制和充分使用在磁盘块大小范围；把树的节点关键字增多后树的层级比原来的二叉树少了，减少数据查找的次数和复杂度;

二、为什么给表加上主键：

我们平时建表的时候都会为表加上主键，在某些关系数据库中，如果建表时不指定主键，数据库会拒绝建表的语句执行。实施上，一个加了主键的表，并不能被称之为表。一个没加主键的表，它的数据无序的设置在磁盘存储器上，一行一行的排列的很整齐，很我认知中的表很接近。如果给表上了主键，那么表在磁盘中的存储结构就由整齐排列的结构转变成了树状结构（平衡树），换句话说，就是整个表就变成了一个索引。也就是所谓的聚集索引。这就是为什么一个表只能有一个主键，一个表只能有一个聚集索引，因为主键的作用就是按照主键元素值将表的数据格式转换成索引（平衡树）的格式存放。

三、为什么加入索引后查询速度会变快？

上图就是带有主键的表（聚集索引）的结构图。图画的不是很好， 将就着看。其中树的所有结点（底部除外）的数据都是由主键字段中的数据构成，也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。

 假如我们执行一个SQL语句：

select * from table where id = 1256;

首先根据索引定位到1256这个值所在的叶结点，然后再通过叶结点取到id等于1256的数据行。 这里不讲解平衡树的运行细节， 但是从上图能看出，树一共有三层， 从根节点至叶节点只需要经过三次查找就能得到结果。如下图：

假如一张表有一亿条数据 ，需要查找其中某一条数据，按照常规逻辑， 一条一条的去匹配的话， 最坏的情况下需要匹配一亿次才能得到结果，用大O标记法就是O(n)最坏时间复杂度，这是无法接受的，而且这一亿条数据显然不能一次性读入内存供程序使用， 因此， 这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销，以现在磁盘的IO能力和CPU的运算能力， 有可能需要几个月才能得出结果 。如果把这张表转换成平衡树结构（一棵非常茂盛和节点非常多的树），假设这棵树有10层，那么只需要10次IO开销就能查找到所需要的数据， 速度以指数级别提升，用大O标记法就是O(log n)，n是记录总树，底数是树的分叉数，结果就是树的层次数。换言之，查找次数是以树的分叉数为底，记录总数的对数，用公式来表示就是

四、为什么加索引后会使写入、修改、删除变慢？

 因为平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构， 因此，在每次数据改变时， DBMS必须去重新梳理树（索引）的结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。

五、 什么情况下要同时在两个字段上建索引？

讲完聚集索引 ， 接下来聊一下非聚集索引， 也就是我们平时经常提起和使用的常规索引。

非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段， 假如给user表的name字段加上索引 ， 那么索引就是由name字段中的值构成，在数据改变时， DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。 如下图：

 

每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。

非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据，如下图：

不管以任何方式查询表， 最终都会利用主键通过聚集索引来定位到数据， 聚集索引（主键）是通往真实数据所在的唯一路径。

****然而， 有一种例外可以不使用聚集索引就能查询出所需要的数据， 这种非主流的方法 称之为「覆盖索引」查询， 也就是平时所说的复合索引或者多字段索引查询。 文章上面的内容已经指出， 当为字段建立索引以后， 字段中的内容会被同步到索引之中， 如果为一个索引指定两个字段， 那么这个两个字段的内容都会被同步至索引之中。

先看下面这个SQL语句

//建立索引

create index index_birthday on user_info(birthday);

//查询生日在1991年11月1日出生用户的用户名

select user_name from user_info where birthday = '1991-11-1'

这句SQL语句的执行过程如下

首先，通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值

然后，通过得到的主键ID值执行聚集索引查找，找到主键ID值对就的真实数据（数据行）存储的位置

最后， 从得到的真实数据中取得user_name字段的值返回， 也就是取得最终的结果

我们把birthday字段上的索引改成双字段的覆盖索引

create index index_birthday_and_user_name on user_info(birthday, user_name);

这句SQL语句的执行过程就会变为

通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容，然而， 叶节点中除了有user_name表主键ID的值以外， user_name字段的值也在里面， 因此不需要通过主键ID值的查找数据行的真实所在， 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式， 可以省略不使用覆盖索引查找的后面两个步骤， 大大的提高了查询性能，如下图：

以上博客内容来自：https://www.cnblogs.com/aspwebchh/p/6652855.html