MySQL中的索引 B+Tree

MySQL中的索引 B+Tree

1. 常见索引的数据结构

示意图均从www.cs.usfca.edu生成

• 哈希(Hash)
  • 示意图
    
  • 效率高，但是因为Hash算法的特性，数据无序，不能进行范围搜索
  • MySQL支持Hash索引
• 二分搜索树(Binary Search Tree)
  • 示意图
    
  • 数据有序，能够按范围查找，但是容易造成部分数据倾斜，导致该部分数据查询较慢
• 平衡二分搜索树(Balanced Binary Search Tree, AVL Tree)
  • 示意图
    
  • 是二分搜索树的优化版，解决了数据倾斜的问题，但是需要维护树平衡的额外开销
• B树(B Tree)
  • 示意图
    
  • 每个节点可以存多条数据，子节点数量也可以大于2，优化了树的深度
• B+树(B+Tree)
  • 示意图
    
  • 叶子节点会额外存储指向相邻叶子节点的指针，优化B树范围查询效率，且更加稳定
  • 最底层存实际的数据（只有叶子节点存数据）
• B*树
  • 是B+树的变体，在B+树的非根、非叶子节点再增加指向兄弟节点的指针

2. MySQL中B+Tree的使用

• MySQL中索引主要采用B+Tree（Memory引擎采用Hash索引），其关键点如下：
  • 非叶子节点存索引，控制树的深度
  • 叶子节点存数据，以及指向相邻叶子节点的指针
  • 数据存入硬盘，需控制每一份数据的大小，以适应硬盘分页
• 那么每一份数据应该存多大呢？
  • MySQL每一份数据是硬盘分页大小的倍数
    • 系统从硬盘中进行读取操作，是按页读取的
    • 如果MySQL每一份数据的大小比硬盘一页的量小，那么每次硬盘读取就可能会读取一些不需要的数据，影响性能
  • 通过 SHOW GLOBAL STATUS LIKE 'Innodb_page_size';即可查看MySQL每份数据的大小
  • MySQL-5.7中Innodb_page_size=16kb

3. MySQL中两大主要引擎的索引

• MyISAM（非聚集索引）
  • 索引(*.MYI)和数据(*.MYD)分开存
    • *.MYI文件中B+Tree的叶子节点存储数据地址，地址对应*.MYD中的数据
  • 主键索引(Primary Key): 直接根据索引找到值
  • 辅助索引(Secondary Key): 直接根据索引找到值
  • 其他
    • 支持表锁
    • 不支持外键
    • 不支持事务
    • 不支持全文索引
• InnoDB（聚集索引）
  • 索引和数据存一起(*.ibd)
    • 必须有一个索引，没有会建一个默认的长整型索引（一般推荐设置一个整型自增主键，随机主键会导致B+Tree频繁分裂）
    • *.ibd文件中包含B+Tree索引，以及具体的数据（存在B+Tree的叶子节点）
  • 主键索引(Primary Key): 直接根据索引找到值
  • 辅助索引(Secondary Key): 先用索引找到主键，再根据主键找到值
  • 其他
    • 支持表锁、行锁
    • 支持外键
    • 事务安全
    • 支持全文索引

4. 适合创建索引的字段

• 主键自动创建索引（推荐自增整型主键）
• 频繁作为查询条件的字段
• 经常与其他表关联的字段
• 经常用作统计、聚合分析的字段
• 经常用作排序的字段
• 单键/组合索引，满足业务条件下，优先选择组合索引（最左前缀原则）
• 注意点
  • 使用索引时，应遵守最左前缀原则
  • 不要在索引列上做任何操作（计算、函数等等），否则会导致索引失效
  • !=、<>、IS NULL、IS NOT NULL会导致索引列失效
  • 关于like查询，LIKE '%word%'会可能导致索引列失效，LIKE 'word%'仍能使用索引
  • 如果where中存在范围条件，那么该条件右边的列的索引会失效
  • 尽量使用覆盖索引（只访问索引）
  • OR可能会导致索引失效