数据库.索引Vs树

索引.介绍

索引问题其实就是一个查找问题.
数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。
索引的实现通常使用B树及其变种B+树。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，
这样就可以在这些数据结构上实现高级查找算法。
这种数据结构，就是索引。

d0ddf9f643294324598aea724a773761.png

索引为什么高效

05cc825dca1377899d6218ed75cb1144.png
现在有一张表，想要找到col2的值为89所在的那一行，方法有很多，
最简单的就是从头开始扫描，一行一行的向下遍历，经过6次扫描后就可以找到了，
显然这样效率是不高的，如果用二分搜索树存储呢？

0bfb62c9daa6ea8eb19331038c1eb7f3.png

可以看到下图将col2作为索引列，其实就是把clo2的所有值存到二叉树中，通过这颗树找到89只需要1次I/O。
同时每一行数据在内存中都会存在一个地址值（最左侧16进制表示），
通过这个地址值就可以找到这行所对应的所有数据，
找到了89也就能拿到0X77，拿到0X77就能获取到这一行其他列的数据，这就是索引。

索引分类

其实一刚开始我对索引的分类很模糊,
也感觉有点多

  • 数据结构:B树索引,B+树索引,Hash索引,Full-Index全文索引,R-Tree索引
  • 逻辑层次:主键索引,普通索引/单列索引,复合索引/多列索引,唯一索引,空间索引
  • 物理层次:聚集索引,非聚集索引

今天说到的篇幅
其它<聚集索引,非聚集索引<Hash索引<BTree索引

Hash索引

a81f16f6b7368e6c9c634a157e0a0334.png

简单地说，哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，
检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

哈希索引优点
  ①.快速查询：参与索引的字段只要进行Hash运算之后就可以快速定位到该记录，时间复杂度约为1

哈希索引缺点
  ①.哈希索引只包含哈希值和行指针，所以不能用索引中的值来避免读取行
  ②.哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序和范围查询
  ③.哈希索引也不支持部分索引列查询，因为哈希索引始终是使用索引列的全部数据进行哈希计算的。
  ④.哈希索引只支持等值比较查询，如=，IN()，<=>操作
  ⑤.如果哈希冲突较多，一些索引的维护操作的代价也会更高

SQL Server 2014推出的的新索引类型叫做 hash index。
介绍hash index之前一定要介绍哈希函数这样会让大家更明白哈希索引的原理
当一个key-value键值对传递给一个哈希函数的时候，经过哈希函数的计算之后，
根据结果会把key-value键值对放在合适的hash buckets（哈希存储桶）里

树.定义

df77d9eddbe06f5b027c3695377f3b1c.png

树（Tree）是n（n>=0)个结点的有限集。n=0时称为空树。在任意一颗非空树中：
1）有且仅有一个特定的称为根（Root）的结点；
2）当n>1时，其余结点可分为m(m>0)个互不相交的有限集T1、T2、......、Tn，其中每一个集合本身又是一棵树，并且称为根的子树。

树.结点.称呼

26981be1f803111e350818c2611b6c3f.png
A 节点就是 B 节点的父节点，B 节点是 A 节点的子节点。
B、C、D 这三个节点的父节点是同一个节点，所以它们之间互称为兄弟节点。
我们把没有父节点的节点叫作根节点，也就是图中的节点 E。
我们把没有子节点的节点叫作叶子节点或者叶节点，比如图中的 G、H、I、J、K、L 都是叶子节点。

树.结点.度

结点拥有的子树数目称为结点的度。
655b903a93f608f40df8b5c20d9980f6.png

树.结点.关系

结点子树的根结点为该结点的孩子结点。相应该结点称为孩子结点的双亲结点。
上图中，A为B的双亲结点，B为A的孩子结点。
同一个双亲结点的孩子结点之间互称兄弟结点。
上图中，结点B与结点C互为兄弟结点。

树.结点.层次

从根开始定义起，根为第一层，根的孩子为第二层，以此类推。
树的层次关系如图所示
5c435dd630d8ae5cff06eb33691a257b.png

树.深度

树中结点的最大层次数称为树的深度或高度。上图所示树的深度为4。

二叉树.定义

二叉树是n(n>=0)个结点的有限集合，该集合或者为空集（称为空二叉树），
或者由一个根结点和两棵互不相交的、分别称为根结点的左子树和右子树组成。
a754f033f362abc0ba6bffe99d7733ff.png
二叉树(Binary Tree)是一个有根树，并且每个节点最多只有2科子树(即二叉树中不存在度大于2的结点)，
并且二叉树的子树有左右之分，其次序不能任意颠倒。

B树.定义

B 树(B-tree)是一种树状数据结构，能够用来存储排序后的数据。
这种数据结构能够让查找数据、循序存取、插入数据及删除的动作，都在对数时间内完成。
B 树，概括来说是一个一般化的二叉查找树，可以拥有多于2个子节点。
与自平衡二叉查找树不同，B 树为系统最优化大块数据的读和写操作。
B 树算法减少定位记录时所经历的中间过程，从而加快存取速度。
这种数据结构常被应用在数据库和文件系统的实作上。

在 B 树中查找给定关键字的方法是，首先把根结点取来，在根结点所包含的关键字 K1,…,Kn 查找给定的关键字(可用顺序查找或二分查找法)，
若找到等于给定值的关键字，则查找成功；否则，一定可以确定要查找的关键字在 Ki 与 Ki+1 之间，
Pi 为指向子树根节点的指针，此时取指针 Pi 所指的结点继续查找，直至找到，或指针 Pi 为空时查找失败。

B树也是一种用于查找的平衡树，但是它不是二叉树。
B 树作为一种多路搜索树，它或者是空树，或者是满足下列性质的树：

  • 定义任意非叶子结点最多只有 m 个儿子；且 m>2；
  • 根结点的儿子数为[2, m]；
  • 除根结点以外的非叶子结点的儿子数为[M/2, M]；
  • 每个结点存放至少m/2-1（取上整）和至多m-1个关键字；（至少2个关键字）
  • 非叶子结点的关键字个数等于指向儿子的指针个数-1；
  • 非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
  • 非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[m]指向关键字大于K[m-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
  • 所有叶子结点位于同一层。

c947c036fb66853f9a0760e2d49b103c.png
图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。

  • 图中的每个节点称为页。

从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，
并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。

B树.阶数

描述一颗 B树时需要指定它的阶数，阶数表示此树的结点 最多有多少个孩子结点（子树），一般用字母 M 表示阶数。

M 阶的B树 ：以【子树】讨论
上限：
每个节点最多有 M 个子树
下限：
根节点至少2个子树，
非根节点至少有[M /2]个子树
所以也称 M 阶B树 为 ( [M /2] , M ) 树 ，即超级节点（除根节点）的子树数的上下限 。

注： 关键字的个数 = 节点子树数 - 1 。

例：

M = 4 阶，（2, 4）树。 最多含有 3个关键字 和 4个子树
M = 5 阶，（3, 5）树。 最多含有 4个关键字 和 5个子树
M = 6 阶，（3, 6）树。 最多含有 5个关键字 和 6个子树

所以，M阶 可理解为M树，即内含（M-1）个关键字 和 M 个子树。

根据"M阶B树为 ( [M /2] , M ) 树"...其实就是M阶就是最多可以开几个叉.

3阶B树↓
7db882a0ae3e5b6d83080f4af3f06bcf.png

B+树

B+ 树是 B 树的变体，也是一种多路搜索树：

  • 其定义基本与 B 树相同；
  • 为所有叶子结点增加一个链指针；
  • 所有关键字都在叶子结点出现。
    0c806cbc01b1d6d62bd0635e5c0802c3.png

B+树.查找过程

27e315d3f5ed71f7871dfbba42575fe1.png

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，
在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，
通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，
29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，
同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，
如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

综合.B树和B+树区别

9d679e0e4d46f228e979c5096934213a.png
如图所示，区别有以下两点：

  • B+树中只有叶子节点会带有指向记录的指针（ROWID），而B树则所有节点都带有，在内部节点出现的索引项不会再出现在叶子节点中。
  • B+树中所有叶子节点都是通过指针连接在一起，而B树不会。

  1. B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。
    另外，B+树的阶数是等于键值的数量的，如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO。

  2. 因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。
    有心的读者可能还发现上图B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。
    其实上面的B树我们也可以对各个节点加上链表。其实这些不是它们之前的区别，是因为在mysql的innodb存储引擎中，索引就是这样存储的。也就是说上图中的B+树索引就是innodb中B+树索引真正的实现方式，准确的说应该是聚集索引（聚集索引和非聚集索引下面会讲到）。

B+树的优点：

  • 非叶子节点不会带上ROWID，这样，一个块中可以容纳更多的索引项，一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点。
  • 叶子节点之间通过指针来连接，范围扫描将十分简单，而对于B树来说，则需要在叶子节点和内部节点不停的往返移动。

B树的优点：

  • 对于在内部节点的数据，可直接得到，不必根据叶子节点来定位。

综合.Hash树和B+树

  • 如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。
    如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
  • 如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
  • 同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
  • 哈希索引也不支持多列联合索引的最左匹配规则；
  • B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

通常，B+树索引结构适用于绝大多数场景，像下面这种场景用哈希索引才更有优势：

聚集索引查找数据

0c806cbc01b1d6d62bd0635e5c0802c3.png

还是这张B+树索引图，现在我们应该知道这就是聚集索引，表中的数据存储在其中。
现在假设我们要查找id>=18并且id<40的用户数据。
对应的sql语句为select * from user where id>=18 and id <40，其中id为主键。

具体的查找过程如下：

    1. 一般根节点都是常驻内存的，也就是说页1已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。
      从内存中读取到页1，要查找这个id>=18 and id <40或者范围值，我们首先需要找到id=18的键值。
      从页1中我们可以找到键值18，此时我们需要根据指针p2，定位到页3。
    1. 要从页3中查找数据，我们就需要拿着p2指针去磁盘中进行读取页3。
      从磁盘中读取页3后将页3放入内存中，然后进行查找，我们可以找到键值18，然后再拿到页3中的指针p1，定位到页8。
    1. 同样的页8页不在内存中，我们需要再去磁盘中将页8读取到内存中。
      将页8读取到内存中后。
      因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值18。
      此时因为已经到数据页了，此时我们已经找到一条满足条件的数据了，就是键值18对应的数据。
      因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页8中的键值依次进行遍历查找并匹配满足条件的数据。
      我们可以一直找到键值为22的数据，然后页8中就没有数据了，此时我们需要拿着页8中的p指针去读取页9中的数据。
    1. 因为页9不在内存中，就又会加载页9到内存中，并通过和页8中一样的方式进行数据的查找，直到将页12加载到内存中，发现41大于40，此时不满足条件。
      那么查找到此终止。
      最终我们找到满足条件的所有数据为：
      (18,kl),(19,kl),(22,hj),(24,io),(25,vg),(29,jk),(31,jk),(33,rt),(34,ty),(35,yu),(37,rt),(39,rt)。
      总共12条记录。

fb8e03050097b4a0b3c5d3964b007b9d.png

非聚集索引查找数据

d67b05ac545374bda798f798430fd312.png
首先，这个非聚集索引表示的是用户luckyNum这一列的索引，此时表结构是这样的。

id name luckyNum
1 zs 23
2 ls 7

在叶子节点中，不在存储所有的数据了，存储的是键值和主键。

对于叶子节点中的x-y，比如1-1。左边的1表示的是索引的键值，右边的1表示的是主键值。
如果我们要找到幸运数字为33的用户信息，对应的sql语句为select * from user where luckNum=33。

查找的流程跟聚集索引一样，这里就不详细介绍了。我们最终会找到主键值47，
找到主键后我们需要再到聚集索引中查找具体对应的数据信息，此时又回到了聚集索引的查找流程。

f22f6e3997b84932e5e9055091b46349.png

为什么官方建议使用自增主键作为索引

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，
即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。
总之就是减少分裂和移动的频率。

索引.创建场景

索引虽然好,但是毕竟也是有资源消耗的,所以不能因为说了一大堆索引的好处,就创建无效的索引,
那样只会浪费资源.

√.一般来说，应该在这些列上创建索引：

  • 在经常需要搜索的列上，可以加快搜索的速度；
  • 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
  • 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
  • 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
  • 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
  • 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

×.一般来说，不应该创建索引的的这些列具有下列特点:

  • 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
  • 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
  • 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
  • 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

索引.缺点

  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

  • 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大

  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

联想一下生活中,为了方便买了很多东西.
比如,运动器材,乒乓球拍 + 网,羽毛球.
去年还经常玩,但是现在这个生活中的运动索引,我觉得可以删除了.
比如还买了一个显示器,虽然视觉体验上上升了一个层次,
但是毕竟占了很多空间.

待整理

  • 树的搜索
  • 树.节点插入
  • 树.节点删除

树名词

二叉树
|--完全二叉树
|--满二叉树
二叉排序树
|--伸展树
平衡二叉树(AVL树)

平衡树
红黑树
Treap

B树
B+树
B*树
Trie树

摘抄文档

posted @ 2020-08-24 21:47  zh89233  阅读(257)  评论(0)  编辑  收藏  举报