对 B+ 树与索引在 MySQL 中的认识

概述

• 本质：数据库维护某种数据结构以某种方式引用（指向）数据
• 索引取舍原则：索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数

B树

满足的条件

• d为大于1的一个正整数，称为B-Tree的度
• h为一个正整数，称为B-Tree的高度
• 每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d
• 每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null
• 所有叶节点具有相同的深度，等于树高h
• key和指针互相间隔，节点两端是指针
• 一个节点中的key从左到右非递减排列
• 所有节点组成树结构
• 每个指针要么为null，要么指向另外一个节点
• 一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2),检索一个key查找节点的个数的渐进复杂度为logd(N)

更新后的操作

• 插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质

B+树

• 
• 每个节点的指针上限为2d而不是2d+1
• 内节点不存储data，只存储key
• 叶子节点不存储指针
• 在经典B+树的基础上，增加了顺序访问指针-->提高区间访问的性能

为什么使用B/B+树？

主存读取

• 当系统需要读取主存时，则将地址信号放到地址总线上传给主存
• 主存读到地址信号后，解析信号并定位到指定存储单元，然后将此存储单元数据放到数据总线上，供其它部件读取
• 主存存取的时间仅与存取次数呈线性关系，因为不存在机械操作，两次存取的数据的“距离”不会对时间有任何影响

磁盘存取原理

• 磁盘转动，每个磁头不动，负责读取内容
• 不过已经有了多磁头独立技术
• 局部性原理
• 磁盘预读：长度一般以页的整数倍为单位

MyISAM索引实现

• 
• 使用B+树作为索引结构，data存放数据记录的地址
• 索引文件与数据文件分离
• 主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复
• 非聚集：MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录

.MYI文件的组成

• 整个索引文件的基本信息state
• 各索引的限制信息base
• 各索引的定义信息keydef
• 各索引记录的概要信息recinfo

读取索引的流程

• query请求，直接读取key cache中的cache block，有就返回
• 没有就到.MYI文件中以file block方式读取数据
• 再以相同的格式存取key cache
• 再将key cache中的数据返回

InnoDB索引实现

• 也是使用B+树
• 

第一个与MyISAM的不同点

• 第一个重大区别是InnoDB的数据文件本身就是索引文件，表数据文件本身就是按B+Tree组织的一个索引结构
• InnoDB的数据文件本身要按主键聚集
• 所以InnoDB要求表必须有主键（MyISAM可以没有）
• 没有显式指定，自动选择唯一标识列
• 不存在的话，生成6个字节长整型的隐含字段

第二个与MyISAM的不同点

• InnoDB的辅助索引data域存储相应记录主键的值而不是地址
• 换句话说，InnoDB的所有辅助索引都引用主键作为data域
• 辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录

得出的优化点

• 不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大
• 用非单调的字段作为主键在InnoDB中也不好，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键就很不错了
• 聚簇索引键被更新造成的成本除了索引数据可能会移动，相关的所有记录数据也要移动

索引使用策略及优化

全列匹配

• 按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到
• 理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引

最左前缀匹配

• 当查询条件精确匹配索引的左边连续一个或几个列时，索引可以被用到

查询条件用到了索引中列的精确匹配，但是中间某个条件未提供

• 只能用到索引中，从中间断开前的列
• 应对
• 可以增加辅助索引
• 当中间条件选项较少时，用隔离列的方式，使用IN包含
• 看情况，比较建立

查询条件没有指定索引第一列

• 不满足使用索引的条件

匹配某列的前缀字符串

• 可以使用索引
• 如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀

范围查询

• 范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引
• 同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引
• 仅用explain可能无法区分范围索引和多值匹配

查询条件中含有函数/表达式

• 一般不使用哦
• 手工算好再代入

索引选择性与前缀索引

MyISAM与InnoDB基数统计方式

• MyisAM索引的基数值（Cardinality，show index 命令可以看见）是精确的，InnoDB则是估计值
• MyisAM统计信息是保存磁盘中，在alter表或Analyze table操作更新此信息
• 而InnoDB则是在表第一次打开的时候估计值保存在缓存区内

不建议建立索引的情况

• 表记录比较少
• 索引的选择性低：不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值

前缀索引

• 用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销

缺点

• 不能用于ORDER BY和GROUP BY操作
• 也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）

InnoDB主键选择与插入优化

• 如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键
• InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上
• 这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）
• 如果使用非自增主键，每次插入近似随机，容易引起数据的移动，重新读目标页面，碎片也多了，虽然也可以用OPTIMIZE TABLE重建优化，但麻烦啊

参考资料

• 图片来源网络
• 《高性能MySQL》

1、窥探 MySQL 索引

2、一位前同事猝死了

3、这个 MySQL bug 99% 的人会踩坑!