数据库索引浅析

数据库索引浅析

今天跟着大佬的blog学习数据库的索引。

首先，先了解一下数据库的数据结构基础 B+树。

B＋树

B+tree是一个n叉树，一般用于数据库或操作系统的文件系统中。可以保证数据稳定有序，插入和修改都有稳定的时间复杂度，并且数据自底向上插入，和二叉树不同。

B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树。所有记录节点都是按键值的大小顺序存放在同一层的叶节点中，各叶节点指针进行连接。

每个节点有多个叶子节点，一颗B+树包含根节点，内部节点，叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上的节点。

n棵子tree的节点包含n个关键字，不用来保存数据而是保存索引所有叶子节点包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子节点本身依关键字的大小自小而大顺序连接。所有的非终端节点可以看成是索引部分，节点中仅含其子数最多（或最小）关键字。

比如下图中的B+树：

B+树的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历。

关于B+树的插入和自旋保持数据平衡的blog在这里​​B+树介绍​​ 这里就不再多讲了。

数据库索引

数据库索引的建立是为了提高数据库表的数据访问速度的。 索引是由一个聚集索引和（一个或多个）非聚集索引，来看下他们的概念：

聚集索引：

我们平时在建表的时候都会给表加上主键，比如说student表中给id设置主键，在user_info中给name设置主键。在某些数据库中，如果没有给表加上主键甚至都不能创建。一个没有加主键的表，它的数据无序的放置在磁盘存储器上，一行一行排列的很整齐。如果给表加上了主键，则表在磁盘上的存储结构就构成了由整齐排列的结构变成了树状结构，就是平衡树。整个数据库就变成了一个索引，就是聚集索引。

聚集索引如图所示。

其中树的所有节点（除了根节点以外）都是由主键的字段构成，也就是我们通畅指定主键的id字段。加入我们执行一个SQL语句：

select * from table where id = 2100

首先根据索引定位到2100这个值所在的叶子节点，再通过叶节点取得的id等于2100的行。

搜索过程如上图所示。

当数据达到了百万级、千万级甚至上亿级的时候，这种高效查询非常的重要，因为从图中看的出，时间复杂度为O(logN)，底数为为n叉树的n叉（比如二叉树的底数就是2），N是记录总数，假设如果一条一条去找那么时间达到O（n）也就亿级，而索引能降低到个位级、十位级，数据库查询将有惊人的性能提升。

然而虽然读数据库会很快，但是这种结构写入数据库则不是那么优秀高效，因为B+树在写入数据的时候为了保持树的数据平衡会进行自旋，这个时候就会产生大量的开销了。

非聚集索引：

同样是采用平衡树作为索引的数据结构。B+树中各节点的值来自于表中的索引字段，加入给user表中的name加上索引，则索引就是由name字段中的值构成的，如果给表中加上多个索引，那么就会出现多个独立的索引结构。每个索引之间互不关联。

每次给表中加一个索引，字段中的数据就要复制一份出来，用于生成索引，因此，给表添加索引，会增加表的体积，占用磁盘空间。使用非聚集索引搜索到的并不是具体的值，而是搜索到对应数据的主键值，由主键值通过聚集索引去找到具体的数据。

不管通过如果途径，最终都会通过主键值来定位数据，聚集索引（主键）是通往真实数据所在的唯一途径。

覆盖索引 就是平时所讲的复合索引或者多字段查询，不适用聚集索引就能查询到数据。 刚刚讲到，当为字段建立索引之后，字段中的内容会被同步到索引之中，如果为一个索引指定两个字段，那么这两个字段的内容都会被同步至索引中。

先看看下面的SQL语句： //为birthday建立非聚集索引 create index index_birthday on user_info(birthday); // 查询生日在1991.11.1出生用户的用户名 select user_name from user_info where birthday = ‘1991-11-1’ 上述语句的执行流程如下： 首先，通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键值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_info表主键的id值外，还有user_name的字段也在里面，因此不需要通过主键id值的查找数据的真实位置所在，可以省略不适用覆盖索引查找的后两个步骤，大大提高了查询性能。

数据库建立索引的优点和缺点

建立索引优点：

避免进行数据库全表的扫描，大多数情况下，只需扫描较少的索引页和数据页，而不是查询所有数据页。而且对于非聚集索引，有时候不需要访问数据页即可得到数据聚集索引可以避免数据插入操作，集中于表的最后一个页面在某些情况下，索引可以避免排序操作。

建立索引的缺点：

创建和维护索引都需要比较大的开销，数据量越大开销越大，但越大就越有必要建立索引索引也会占用物理存储空间，所以索引很多的话，占用的空间也很大每次增删改的效率不会很高，因为树要自己维护数据，由自旋维护带来的开销会大一些。

建立索引的原则：

数据量大，至少也要万级以上表中字段建立索引：（1）越小的数据类型会更好：因为越小的数据类型（int的4字节，char的2字节）在磁盘、内存中占的内存会更少。处理起来会更快（2）简单的数据类型更好：比如整型就比字符串好处理（3）尽量避免NULL：最好指定列为NOT NULL。含有空值的列很难进行查询优化，应该用0、特殊的值或空的字符串去代替。（4）尽量用在值唯一的字段：比如’性别’的范围就太广了，你搜一个人，就知道他是个男人，那这样查询优化也没有优化多少呀。所以就失去了索引的意义了。表与表连接用于多表联合查询的约束条件的字段应当建立索引。用于排序的字段可以添加索引，用于分组的字段应当视情况添加索引。有些表注定只会查询全部，那就也没有必要建立索引了。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。