MySQL优化第一课：深入理解MySQL索引底层数据结构与算法

索引

• 索引是帮助MySQL高效获取数据的排好序的数据结构
• 索引的数据结构
  • 二叉树（特定条件下退化成链表）
  • 红黑树（树的高度不可控，太高了）
  • Hash表
  • B-Tree

帮助学习数据结构：数据结构可视化网址

底层数据结构

B树

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

B+树（B树变种）

• 非叶子节点不存储data，只存储索引（冗余），可以放更多索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问（范围查找）的性能
  

MySQL采用B+树作为索引的数据结构

• 查看B+树一行能放多少个字节(即文件页大小）（16K）：SHOW GLOBAL STATUS like 'Innodb_page_size; 不推荐修改这个值
  
  假设用BigInteger（8个字节）存储键key，一个文件页就能放16384 / (6(地址大小) + 8) = 1150个键
  高度为3的B+树能存的数据 = 1150 * 1150 * 16（叶子节点1个数据大概1K，能放16K）
  大概是千万数据，所以建了索引比不建索引快了非常多

• 为什么文件页大小选择16K
  大量实践证明比较好

• 为什么选B+树不选B树
  因为B树带了数据，因此一个文件页只能存16个数据
  假设2000万数据，B树的高度为log16(2000万)，结果肯定是远远大于B+树的高度

Hash

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

索引实现

MyISAM索引文件和数据文件是分离的（非聚集）

磁盘上会有3个文件：.frm（frame表结构 文件） .MYD（数据文件） MYI（索引文件）

• 非聚集索引：数据在另一个文件里，叶子节点只存储对应的地址
  

InnoDB索引实现（聚集）

InnoDB在磁盘上有两个文件：.frm（表结构文件） .ibd（数据文件）

• 表数据文件本身就是按B+树组织的一个索引结构文件
• 聚集索引：叶子节点包含了完整的数据记录，只有一个聚集索引（主键索引）
• 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
  • 是为了建立对应的B+树，如果没有主键，MySQL内部会自己遍历所有列，找到一列没有相同元素的作为B+树的key，如果找不到，就会创建一个隐藏列，类似于UUID，唯一表示每一行
  • 但是MySQL的资源很宝贵，不应该浪费在这种事情上
  • 而整型比较大小时间快，从而查找效率高，所以推荐使用整型
  • 插入非自增的键时，B+树需要分裂以及平衡，而自增的键则不需要
• 为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）
  • 维护索引的时候只需修改主键索引
  • 节约空间
    

索引最左前缀原则

• 联合索引的底层存储结构长什么样？
  联合主键索引如下：