MySQL底层索引与算法

MySQL底层索引与算法

索引数据结构

索引是帮助MySQL高效获取数据的排好序的数据结构

二叉树：

• 如果索引的结构使用二叉树对col2lie建立索引，索引结构如上图右侧所示。
  比如select * from table where col2=89；
  此时如果不适用索引查询则会逐行查询遍历6次，才能查到对应的值，如果使用索引，则查询2次，便可查询到对应的数据；效率明显提高
  但是如果对于col1之类逐行递增的数据，二叉树形成的数据结构如图所示：
  
• 此时如果执行SQL select * from table where col1=6；则和全表扫面一样，需要扫描6次才能找到对应的数据。所以不可取

红黑树:

• 红黑树可以解决 单列逐行递增的问题，但是会暴露一个新的问题，就是如果数据量过大的情况下，树的高度会变的很高，如果数据位于叶子节点，也需要查询很多次才能查到数据，效率不够高。

B树

1. 叶节点具有相同的深度，叶节点的指针为空
2. 所有索引元素不重复
3. 节点中的数据索引从左到右递增排列

为了降低红黑树，树的高度过高的问题，则考虑使用B树，在一个节点上放多个元素，则可以有效的降低树的高度。

而MySQL则使用的是B+树，在B树的基础上做了些变种

B+树（B树变种）

1. 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
2. 叶子节点包含所有索引字段
3. 叶子节点用指针连接，提高区间访问的性能（可以快速的查到相邻磁盘上的数据）
   
   MySQL对每个叶子的大小分配了16KB的内存（如果每页分配的数据过大，则会降低读取、处理速度），所以降低了树的高度

hash结构

• 对索引的key进行一次hash计算就可以定位出数据存储的位置
• 很多时候Hash索引要比B+ 树索引更高效
• 仅能满足 “=”，“IN”，不支持范围查询
• hash冲突问题
  

为什么不使用B树作为索引结构？

1. B树把data数据放在自己所属的每个分页，导致每个分页能够储存的数据变少，以至于储存同样多的数据，相比于B+树 增加了树的高度。B+树把data数据移到叶子节点，就是为了在肥哦叶子节点上存储更多的索引数据，达到减少树的高度的目的。非叶子节点存放的数据越多，树的高度就越低。
2. B+叶子节点使用指针链接，可以实现范围查询时快速定位到符合条件的数据

聚集索引：叶节点包含了完整的数据记录
非聚集索引：索引文件和数据文件是分离的

数据引擎

• MyISAM索引文件和数据文件是分离的(非聚集)
  MyISAM对应在磁盘上的数据是分为三个表来存储，一个数据表结构文件，一个索引文件，还有一个数据文件。
  • 查找时通过B+ 树遍历到叶子节点，而叶子节点中data存储的时当前这条数据在数据文件中的位置，拿到这个位置之后到数据文件中找到相应的位置。

• InnoDB索引实现(聚集)

  • 表数据文件本身就是按B+Tree组织的一个索引结构文件
  • 聚集索引—叶节点包含了完整的数据记录
  • 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
  1. 因为为了形成B+树的树形结构，如果没有主键的话，InnoDB会从第一列开始选择一列所有元素都不相等的来形成B+树，如果从第一列一直到最后一列都没有选到的话，则会建立一个隐藏列类似rowId，隐藏列会维护一个唯一的id，来形成B+树，组织整张表的数据。 如果有主键的话，则直接使用主键来形成B+树。
  2. 使用整形自增，而不使用UUID，是因为在寻找元素的时候是从根节点开始寻找，会一直存在比大小的操作，显然 整型值比大小 效率高于 UUID字符串比大小。而且整形值所占用的空间也比UUID字符串占用的空间少
  3. 如果插入非自增索引，如果需要插入的B+树叶子节点数据已经放满，则会导致节点分裂，重新平衡，相比于插入自增索引，重新开一个叶子节点效率更低。
  • 为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)

联合索引

最左前缀原理
储存时按照索引的顺序，依次排序，优先对最左边的列进行排序，如果第一列的索引相等再比较第二列，否则不会对第二列进行排序。第三列同理。如果三列都相同，则依次通过主键去聚合索引里面去查找

# 根据上图的索引，以及索引最左前缀原理
select * from table where name='Bill'  and age='30';  # 走索引
select * from table where  age='30' and position='dev'; #不走索引
select * from table where position=''; #不走索引

回表：是指通过二级索引或者符合索引查到到对应的叶子节点上对应的data（主键索引）， 再通过主键索引到聚合索引中查询对应的数据。