【数据库】--- 索引及其原理(面试常考点)

一、索引简介

索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。可以理解为“排好序的快速查找数据结构”，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据,这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆法拉利的话，那么没有设计和使用索引的MySQL就是一辆自行车。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

二、索引原理

索引有两种数据结构：Hash和B+
我们在设计索引的时候，会发现索引类型是可以选择的。

1.Hash

关于哈希的简介可查看上节讲解：哈希及哈希冲突

注意字段值所对应的数组下标是哈希算法随机算出来的，所以可能出现哈希冲突。

那么对于这样一个索引结构，现在来执行下面的sql语句：

select * from sanguo where name='鸡蛋'

可以直接对‘鸡蛋’按哈希算法算出来一个数组下标，然后可以直接从数据中取出数据并拿到所对应那一行数据的地址，进而查询那一行数据， 那么如果现在执行下面的sql语句：

select * from sanguo where name>'鸡蛋'

则无能为力，因为哈希表的特点就是可以快速的精确查询，但是不支持范围查询。

如果做成了索引，那速度也是很慢的，要全部扫描。

Hash缺陷及应用场景

缺陷：有序的适合静态数据，因为如果我们新增、删除、修改数据的时候就会改变他的结构。比如你新增一个，那在你新增的位置后面所有的节点都会后移，成本很高。

应用场景：等值查询的场景，就只有KV（Key，Value）的情况，例如Redis、Memcached等这些NoSQL的中间件，较为适合于做静态数据存储引擎，用来保存静态数据，例如，2019年某银行的用户存取款记录等等。

2.B+

什么是B树？

B树原理：

B+ 树是一种数据结构，一种查找树，我们知道，这一类树（比如二叉查找树，红黑树等等）最初生成的目的都是为了解决某种系统中，查找效率低的问题。B树也是如此，它最初启发于二叉查找树，二叉查找树的特点是每个非叶节点都只有两个孩子节点。然而这种做法会导致当数据量非常大时，二叉查找树的深度过深，搜索算法自根节点向下搜索时，需要访问的节点也就变的相当多。如果这些节点存储在外存储器中，每访问一个节点，相当于就是进行了一次I/O操作，随着树高度的增加，频繁的I/O操作一定会降低查询的效率。

这里有一个基本的概念，就是说我们从外存储器中读取信息的步骤，简单来分，大致有两步：

• 找到存储这个数据所对应的磁盘页面，这个过程是机械化的过程，需要依靠磁臂的转动，找到对应磁道，所以耗时长。
• 读取数据进内存，并实施运算，这是电子化的过程，相当快。

综上，对于外存储器的信息读取最大的时间消耗在于寻找磁盘页面。那么一个基本的想法就是能不能减少这种读取的次数，在一个磁盘页面上，多存储一些索引信息。B树的基本逻辑就是这个思路，它要改二叉为多叉，每个节点存储更多的指针信息，以降低I/O操作数。

一个m阶的B树具有如下几个特征：

1.根结点至少有两个子女。

2.每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m

3.每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m

4.所有的叶子结点都位于同一层。

5.每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。

一个标准的B树如下：

什么是B+树？

B+树是B树的一种变形，它更适合实际应用中操作系统的文件索引和数据库索引；

一个m阶的B+树具有如下几个特征：

1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。

2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

一棵标准的B+树如下：

B树和B+树的区别？

B+树和B树相比，主要的不同点在以下3项：

• 内部节点中，关键字的个数与其子树的个数相同，不像B树种，子树的个数总比关键字个数多1个。
• 所有指向文件的关键字及其指针都在叶子节点中，不像B树，有的指向文件的关键字是在内部节点中。换句话说，B+树中，内部节点仅仅起到索引的作用。
• 在搜索过程中，如果查询和内部节点的关键字一致，那么搜索过程不停止，而是继续向下搜索这个分支。

B+树的相对于B树的优势？

根据B+树的结构，我们可以发现B+树相比于B树，在文件系统，数据库系统当中，更有优势，原因如下：

1. B+树的磁盘读写代价更低
   B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说I/O读写次数也就降低了。
2. B+树的查询效率更加稳定
   由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
3. B+树更有利于对数据库的扫描范围
   B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题，而B+树只需要遍历叶子节点就可以解决对全部关键字信息的扫描，所以对于数据库中频繁使用的range query，B+树有着更高的性能。

三、总结

1.创建索引的好处

①通过创建索引,可以在查询的过程中,提高系统的性能

②通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性

③在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间

2.创建索引的坏处

①创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大

②索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大

③在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护

3.应该在哪些列上创建索引呢

①经常需要搜索的列上

②作为主键的列上

③经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度

④经常需要根据范围进行搜索的列上

⑤经常需要排序的列上

⑥经常使用在where子句上面的列上

4.不应该在哪些列上创建索引

①查询中很少用到的列

②对于那些具有很少数据值的列.比如人事表的性别列,bit数据类型的列

③对于那些定义为text,image的列.因为这些列的数据量相当大

④当对修改性能的要求远远大于搜索性能时.因为当增加索引时,会提高搜索性能,但是会降低修改性能

Mysql选用B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。