mysql索引分析

问题

• 为什么要使用索引？
• 什么样的信息能成为索引？
• 索引的数据结构？
• 密集索引和 稀疏索引的区别？

问题分析

1.为什么要使用索引？

如果不使用索引进行查询的话就会对数据库表进行全表扫描，就会将数据全部或者分批次加载到内存当中，对这些数据在进行轮寻找到想要的数据。这种方式在大多的情况下会是非常的慢。如果数据量很少的情况下这种方式是没有问题的，数据量一大的话就会很慢。
索引查询就会像是使用字典来查询字，一层一层的深入。
什么样的信息能成功索引 ？主键，唯一键以及普通键都是可以的。
索引是有自己的数据结构的，不同的数据结构查询效率也不一定。 就像是字典的按拼单查，按偏旁查类似。
一般数据结构有如下几种：

1. 二叉树
2. B-tree结构
3. B±tree结构
4. Hash结构

2.索引的数据结构

二叉树

结构图

图中X代表父节点，箭头下面的代表子节点。 一个父节点会有一个左子节点和右子节点。左子节点会小于父节点。右子节点会大于父节点。这样进行二分查询，查询时间的繁杂度就会降低。
但是这样会有一个问题，如果我们数据量很大的话。二叉数会变的很深。 我们要查询数据的时候就会对这个很深的树进行二分遍利。也就是说将父节点通过IO读到内存中进行判断 ，在找子节点读到内存中进行判断，依次进行。这样多次IO就会很影响性能。
综合上述得出的结论就是二叉树时间复杂度低，但是IO次数多。因为IO很耗性能，因此不适合创建索引使用。

B-TREE 平衡多路查找树

结构图

特点介绍

• 第一条 根节点至少包括两个孩子，树中每个节点最多含有m个孩子（m>2）
• 第二条 除根节点和叶节点外，其他每个节点至少有ceil(m/2)个孩子。
  注（ceil函数的作用是朝正无穷方向取整，即将m/n的结果向正无穷方向取整，如m/n=3.12，则ceil(m/n)的结果为4。）
• 第三条 所有叶子节点都位于同一层

这样的数据结构，减少树的深度，减少IO的次数，从而提高性能。
第四条：

因为B-tree有以上规定，当数据库中数据有变化的时候，B-tree结构就会跟着相应的变化，会形成线性的。保证树的深度在一定范围定。 他的时间复杂度也是O(logn)。综上所述，这种数据结构适合数据库索引使用。

B±tree

结构图

和B-tree的不同点是

• 非叶子节点的子树指针与关键字数相同，这样树就能存储更多的关键字
• 非叶子节点的子树指针p[i]，指向关键字值(k[i],k[i+1])的子树（就是说10 下面叶子，数是大于10，小于10*（1+1））
• 非叶子节点仅存储索引 ， 数据是保存在叶子节点中的
• 所有叶子节点均有一个链指针指向下一个叶子节点，并按大小顺序链接（也就是图上的Q）

B+tree 更适合用来做存储索引，原因如下

• B+tree 的磁盘读写代价更低，也就是说io次数更少
• B+tree 的查询效率更加稳定，由于普通节点并不是存储数据的节点，只是指向叶子节点的索引，所以任何数据的查询都必须走一条从根节点到叶子节点的路。所以每个数据的查询的时间复杂度是相同的。
• B±tree 更有利于对数据库的全部扫描。 B±tree 只需要遍历叶子节点就可以完成对全表的扫描。对数据经常使用的范围查询有非常有用的。

综上所述， B±tree 才是数据库主流的索引数据结构。

密集索引和 稀疏索引的区别

结构图

• 密集索引文件中的每个索引 码值都对应一个索引值.叶子节点保存的不仅仅是键值，还保存了位于同一行记录里面的其他列的信息。由于索引决定了表的物理排列顺序，一个表只能创建一个密集索引 。
• 稀疏索引文件只为索引码的某些值建立索引项。叶子节点仅保存键位信息，及该行数据的地址，有的稀疏索引仅保存了键位信息，及其主键。