【Mysql优化-01】深入理解Mysql索引底层数据结构与算法

索引的本质

• 索引是帮助mysql高效获取数据的排好序的数据结构
• 索引数据结构
  • 二叉树
  • 红黑树
  • Hash表
  • B-Tree
• 在表中看起来每一行数据都是紧挨在一起的，但是实际上在磁盘中，他可能是分开存储的。例如第一行存储后，在第二行储存之前，已经存入了大量数据，那么他们就会在不同位置。
• 上面提及的几个数据结构使用在索引上的缺点：
  • 二叉树（二叉搜索树）：可能一直插入大的值，就等于成了一个链表
  • 红黑树，有可能只会单边增长，这样的话也会出现效率低的情况
  • B-Tree：每一页的索引还有数据，假设数据1k，那一页16kb的话最多只能存16个索引，B+-Tree对此做了优化，除了叶结点以外，其它页都不带数据，则每一页可以存储1170个索引。树的高度是由非叶子结点的存储索引的大小决定的，所以B+-Tree优化很大
• B+-Tree:
  • 非叶子节点不存储data，只存索引，可以放更多的索引.
  • 叶子节点包含所有索引
  • 叶子节点使用指针连接，提高区间访问的性能，方便范围查询，优于B树和HASH结构
  • 查找一个数的过程：
    • 逐层查找，每次查找时都要将节点放入内存中，这个步骤最耗时
    • 每一层使用二分查找找到正确的区间
    • 通过区间找到下一层继续使用二分查找找到正确区间
    • 直到找到叶结点中匹配的数据
    • 疑问：为什么不把所有索引放在一个节点上呢？答：数据量大的时候会被撑爆，搜索效率也会降低。
    • 在mysql中默认的一页数据最大是16k，如果有三层，叶子节点存储了1k的信息，那么三层最多可以容纳两千多万条数据，这样最多三次磁盘IO（不是3次查找索引）就可以找到数据了
    • 有些版本还把非叶子的结点存储在常驻内存，又减少了IO操作
  • 
• Hash结构：
  • 对索引的key进行一次hash计算就可以定位出数据存储的位置
  • 很多时候Hash索引比B+树还要高效
  • 只能满足等于，或者IN查询
  • hash会有 冲突问题
• 引擎是形容数据库表的，以表为单位
• innoDB引擎和myisam引擎的区别：InnoDB是索引和数据一起存储的，myisam是索引存在一个文件中然后没有数据，而是存储它在另一个文件的地址（索引和数据分表）

InnoDB索引实现（聚集）

• 表数据表婶就是按照B+树来组织的一个索引结构文件
• 聚集索引-叶子节点包含了完整的数据记录（myisam就是非聚集索引），就是数据和索引一起储存
• 为什么建议InnoDB表必须要主键，并且推荐使用整型的自增主键
  • InnoDB是数据和索引一起存储
  • 如果没有主键，那是怎么存储的呢？-》选择一列所有元素不都不相等的列-》没有这样的一列数据呢-》mysql帮忙隐藏的构建一列
  • 整型方便比较大小，需要储存的空间比较小
  • 提高插入效率，不然可能会有平衡操作
• 聚集索引会比非聚集索引速度要快，因为非聚集索引需要跨文件搜索
• InnoDB只有一个聚集索引，其它的索引的数据是主键，原因是避免数据重复存储，还有一致性，如果存多个数据，修改数据时候需要维护多个B+树

索引最左前缀原理

• 联合索引的底层存储结构长什么样？
  • 底层会根据创建联合索引的顺序来比较，先对比第一个，然后第二个，以此类推··· 
• 
• 索引从左到右（创建时的）一定要按顺序出现在条件查询语句，否则索引会失效。其原因是？
  • 索引是排好序的，他的顺序就是从联合索引左边字段开始的，如果跳过了左边的字段，第二个字段来说是无序的