第06章_索引的数据结构

1. 为什么使用索引

数据库在磁盘中以页为单位进行存储，一页的默认大小是16kB(2^14B)
从MysQL的角度讲，数据库数据都是存放在磁盘中的，读取数据库数据就是要去磁盘中读取(为什么不全部存放在内存中？查找都是索引操作，一般来说索引非常大，尤其是关系型数据库，当数据量比较大的时候，索引的大小有可能几个G甚至更多，为了减少索引在内存的占用，数据库索引是存储在外部磁盘上的。当我们利用索引查询的时候，不可能把整个索引全部加载到内存，只能逐一加载，那么MysQL衡量查询效率的标准就是磁盘l0次数)，不得不考虑一个现实问题就是磁盘IO。如果我们能让索引的数据结构尽量减少硬盘的I/o操作，所消耗的时间也就越小。可以说，磁盘的 IO操作次数对索引的使用效率至关重要。

加速查找速度的数据结构有两种：树和hash表

首先Hash表的CRUD时间复杂度是O(1)

树，例如平衡二叉树，查询/插入/修改/删除的平均时间复杂度都是0(log2N);
那为什么不用hash呢？

原因1: Hash索引仅能满足(=) (<>)和IN查询。如果进行范围查询，哈希型的索引，时间复杂度会退化为o(n);而树型的“有序"特性，依然能够保持o(log2N)的高效率。
原因2:Hash 索引还有一个缺陷，数据的存储是没有顺序的，在ORDER BY的情况下，使用Hash索引还需要对数据重新排序。
原因3:对于联合索引的情况，Hash值是将联合索引键合并起来计算的，无法对单独的一个键或者几个索引键进行查询。
原因4∶对于等值查询来说，通常Hash索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这是因为遇到Hash冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。比如年龄，性别

如果是MyIsam或者是InnoDB存储引擎，那么对应的底层的数据结构为B+树，如果是Memory存储引擎，那么对应的底层的数据结构为Hash表。

树，二叉排序树可能会退化成链表，就不存在二分查找，查询时间复杂度O(n)；所以为了控制深度来看看平衡二叉树，能保证查询时间复杂度一定是O(log2N)，但是为了保证平衡，在插入数据的时候必须要旋转，插入性能有所损失。但是一个结点最多只有两个子节点，数据库数据庞大会导致树的深度很大，磁盘 IO 的次数多，性能低；

那么我们变成三叉树，四叉树呢

那如果用红黑树呢?

平衡二叉树的左右子树的高度差绝对值不超过1，但是红黑树在某些时刻可能会超过1，只要符合红黑树的五个条件即可。
二叉树只要不平衡就会进行旋转，而红黑树不符合规则时，有些情况只用改变颜色不用旋转，就能达到平衡，修改性能较高。
但是还是存在一个大问题，就是深度过大，磁盘IO效率低

如果把有序二叉树变成有序多叉树，就能降低树的高度，这个就是演变出来的B树的核心思想。

这就是一个简单的B+树，叶子节点以双链表的形式连接，每个数据页中的记录以单链表的形式连接

1.1 你的表里能存放 1000,0000,0000 条记录吗？为什么用到的B+树都不会超过4层？

首先一个页的大小是16KB，我们假设数据页能存放100条数据，每条数据就是160B(byte)大小，假设目录页能存放1000条数据：

• 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。
• 如果B+树有2层，最多能存放 1000×100=10,0000 条记录。
• 如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录。
• 如果B+树有4层，最多能存1000×1000×1000×100=1000,0000,0000条记录。
  一千亿条数据，4层就足够

2.InnoDB中的索引

3.1 聚簇索引

黄色代表主键，粉色是页号，蓝色、紫色是非主键

• 概念：针对主键构造的一个索引
• 特点：
  • 页内(数据页、目录页) 的记录是按照主键的大小顺序排成一个单向链表 。
  • 各个页 也是根据页中用户记录的主键大小顺序排成一个双向链表。
  • 聚簇索引的叶子节点 存储的是完整的用户记录。
  • 对于InnoDB来说，一定存在一个聚簇索引
• 优点

3.2 非聚簇索引

黄色代表主键，粉色是页号，蓝色是非主键，但这个图是不对的
假如我们有一个表是这样的，c1是主键

以c2为顺序建立非聚簇索引：

如果我们要插入 （9 ，1 ，‘c’）这三个数据，先从根节点查询，发现图中圆框中的两个记录都符合，是插入到页4里面还是页5里面？
所以，非聚簇索引的目录页不仅存放索引列+页号还存放主键

这样插入（9 ，1 ，‘c’），就知道插入到页5中

• 概念：
  • 针对非主键构造的一个索引
  • 回表：我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表 。也就是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！
  • 问：为什么我们还需要一次 回表 操作呢？直接把完整的用户记录放到叶子节点不OK吗？
    • 答：如果把完整的用户记录放到叶子节点是可以不用回表。但是太占地方了，相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。时间换空间？
• 特点：
  • 目录项的每条记录存放索引列+主键+页号，叶子节点-数据项的记录存放索引列+主键
    

3.3 小结

聚簇索引与非聚簇索引的原理不同，在使用上也有一些区别;
1.聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。
2.一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。
3.使用聚簇索引的时候，数据的查询效率高(不需要回表)，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。为什么？因为每次增删改都会影响到聚簇索引，但不一定影响到非聚簇索引。

3.4 联合索引

如果一个表有c1，c2，c3，c4 四个字段，我们以非主键c2、c3(注意顺序)
为条件创建聚簇索引，那么这个聚簇索引的特点是：

• 每条目录项记录都由c2、c3、页号这三个部分组成，各条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序。
• B+树叶子节点处的用户记录由c2、c3和主键c1列组成。

3.5 索引的创建过程

• 每当为某个表创建一个B+树索引(聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
• 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
• 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值)的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

3.MyISAM中的索引

首先我们知道，对于MyISAM存储引擎来说，数据和索引是分开的，数据存在 .MYD 文件中索引存在 .MYI 文件中，索引不分聚簇索引和非聚簇索引
如果一个表是这样：

我们以主键Co1作为条件建立索引

如果插入（3，100，Kik）一条数据，那么在.MYD文件中是直接插入的，不会按主键顺序排序，是按插入顺序排序，而对于.MYI文件来说，需要排序

4. MyISAM的索引 与 InnoDB的索引对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区别：
① 在InnoDB存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录，而在MyISAM 中却需要进行一次 回表 操作，意味着MyISAM中建立的索引相当于全部都是 二级索引 。
② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是 分离的 ，索引文件仅保存数据记录的地址。
③ InnoDB的非聚簇索引data域存储相应记录主键的值 ，而MyISAM索引记录的是 地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
④ MyISAM的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
⑤ InnoDB要求表 必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

5索引的代价

一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。