数据库索引（Index）实现原理，面试官常问~~~

目录

一.概述    

二.索引的优缺点

三、索引的分类

四、B-Tree索引和B+Tree索引

B-Tree索引

B+Tree索引

B+Tree对比B-Tree的优点

一.概述    

• 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。
• 通俗来讲，索引就相当于一本书的目录，当你查某一个知识点时，你先通过目录查询到对应的页码，然后直接翻到那一页。
• 索引，当你要查询一条数据时，你先通过索引查看到那条数据的内存地址，然后直接指向对应的那条数据。

注意：  

• 索引需要占用磁盘空间，因此在创建索引时要考虑到磁盘空间是否足够
• 创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行
• 关系型数据库一般为  硬盘级索引   

二.索引的优缺点

• 优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；
• 劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

三、索引的分类

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；

2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值

ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；

3、普通索引：用表中的普通列构建的索引，没有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col')；

4、全文索引：用大文本对象的列构建的索引（下一部分会讲解）

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

• 遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。
• 在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

• 表示使用col1的前4个字符和col2的前3个字符作为索引

四、B-Tree索引和B+Tree索引

B-Tree索引

B-Tree是平衡搜索多叉树，那么m阶B-Tree满足以下条件：   

• 根节点至少包括两个孩子
• 树中每个节点最多有m个孩子(m>=2)
•  除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
•  所有叶子节点都在同一层 
•  ki(1=1…n)为关键字,且关键字按顺序升序排列k(i-1) < k               8  < 9
•  关键字的个数n满足：ceil(m/2)-1 <= n <= m-1    (非叶子节点关键字个数比指向孩子的指针少1个)
• 非叶子结点的指针p[1],p[2],…p[m]  其中p1指向关键字小于k[1]的子树    3  < 8

           p[m]指针关键字大于k[m-1]的子树   15  >  12
           p[i]指向关键字属于(k[i-1],k[i])的子树      9,10 是位于8 和 12之间

B-Tree的结构如下：

• 在BTree的机构下，就可以使用二分查找的查找方式，查找复杂度为h*log(n)，一般来说树的高度是很小的，一般为3左右，因此BTree是一个非常高效的查找结构。

B+Tree索引

B+Tree是B-Tree的一个变种，设d为树的度数，h为树的高度，B+Tree和B-Tree的不同主要在于：

•     非叶子节点的子树指针与关键字个数相同
•     非叶子节点的子树指针,指向关键字值[k[i],k[i+1]]的子树   (10 < 18 < 20)
•     非叶子节点仅用来做索引,数据都保存在叶子节点中
•     所有叶子节点均有一个链指针指向下一个 叶子节点

链接起来, 能够方便我们在直接在叶子节点做范围统计，而不是再回到子节点中，一旦定位到某个叶子节点, 便可以从该叶子节点横向的去跨子树去做统计

B+Tree的结构如下：

B+Tree对比B-Tree的优点

1、磁盘读写代价更低

• 一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，即磁盘的最小存储单位是扇区（sector），而操作系统的块（block）通常是整数倍的sector，操作系统以页（page）为单位管理内存，一页（page）通常默认为4K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。

2、查询速度更稳定

• 由于B+Tree非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。
• 带顺序索引的B+TREE：很多存储引擎在B+Tree的基础上进行了优化，添加了指向相邻叶节点的指针，形成了带有顺序访问指针的B+Tree，这样做是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

你知道的越多，你不知道的越多。
有道无术，术尚可求，有术无道，止于术。
如有其它问题，欢迎大家留言，我们一起讨论，一起学习，一起进步