MySQL学习笔记 - 2 - 索引相关

• 哈希表
  • 只适用于等值查询场景
• 有序数组
  • 等职查询，范围查询性能优秀
• 搜索树
  • N叉树读写性能优秀， 适配磁盘访问模式 ，减少单次查询磁盘的次数

InnoDB索引模型

• 表的数据是根据主键顺序以索引的形式存放（索引组织表）
• 使用B+树索引模型，一个索引就是一棵B+树
• 索引类型
  • 主键索引（聚簇索引）
    • 主键索引的叶子节点存放的是整行数据
  • 非主键索引
    • 非主键索引的叶子节点内容就是主键的值

基于非主键索引的查询，需要额外扫面主键索引，这个过程叫做回表

覆盖索引

• 一个索引覆盖了查询语句需要的所有字段
• 可以减少树的搜索次数，是一个常用的性能优化手段

直接返回索引上的字段，无需回表查询

索引下推

• MySQL5.6引入索引下推优化
• 再索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表的次数

change buffer

减少读磁盘， 仅适用于普通索引

• InnoDB的数据是按数据页为单位来读写，数据页大小默认为 16KB

当需要都一条记录的时候，并不是将这条记录从磁盘中读出，而是以页为单位，整页数据读入内存

• 当需要更新一个数据页的时候，如果 数据页在内存中就直接更新 ；而如果数据页不在内存中，InnoDB会在不影响数据一致性的前提下，将 更新操作缓存进change buffer中 ，下次访问数据页的时候，将数据页读入内存中，再执行change buffer中跟这页有关的操作（避免占用内存，提高内存利用率，内存命中率）
• 将change buffer中的操作应用到原数据页，得到最新的结果的过程称为merge
  • 访问数据页会触发merge
  • 系统有后台线程定期merge
  • 数据库正常关闭的过程，也会执行merge操作
• 唯一索引无法使用change buffer
  • 对于唯一索引，所有更新操作都需要判断是否违反唯一约束，所以在插入一条记录的时候，要判断记录是否已存在，此时必须将数据页读入内存，所以无需再使用change buffer
• change buffer是可以持久化的，可以被写到磁盘中
• change buffer 适合写多读少的业务 （写完以后，马上被访问的概率比较小），例如：账单类，日志类的系统

如果业务场景是写完以后马上读，这样随机读次数不会减少，反而要增加change buffer的维护代价（起了副作用）

change buffer和redo log对比

• redo log 主要节省的是随机写磁盘的IO消耗
• change buffer 主要节省的是随机读磁盘的IO

索引选择

• 选择索引是优化器的工作，优化器选择最优的执行方案（成本最低）去执行语句

扫描行数是影响执行成本的因素之一，此外还有是否使用临时表，是否 排序 等因素

判断扫描行数

• 判断扫描行数是根据统计信息来估算的
• 统计信息是指索引的区分度，一个索引上不同的值越多，区分度越高
• 一个索引上不同的值的个数称为“基数”
  • 通过 show index from table_name 可以查看索引的信息

索引的基数统计方式 - 采样统计

analyze table

索引统计信息的存储方式

• 通过设置参数innodb_stats_presistent的值来选择
  • （默认）设置为on：表示统计信息会持久化存储，此时N = 20， M = 10（默认值）
  • 设置为off：表示统计信息只会存储在内存中，此时N=8，M=16（默认值）
• explain 执行计划中的rows字段表示预估扫描行数

优化器错误选择索引的处理方式

• 使用force index（不推荐）
• 修改SQL，诱导优化器使用期望的索引（推荐）
• 新建合适索引，删除误用的索引（特定场景下适用）