索引常见模型
- 哈希表
- 只适用于等值查询场景
- 有序数组
- 等值查询，范围查询性能优秀
- 搜索树
- 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可以查看索引的信息
- 通过
索引的基数统计方式 - 采样统计
- InnoDB默认会选择N个数据页，统计这些页面上不同的值，得到一个平均值，然后乘以这个索引的页面数，得出索引的基数
- 当变更行超过1/M的时候，会自动重新统计一次索引信息
- 通过
analyze table可以手动触发重新统计索引信息
索引统计信息的存储方式
- 通过设置参数innodb_stats_presistent的值来选择
- （默认）设置为on：表示统计信息会持久化存储，此时N = 20， M = 10（默认值）
- 设置为off：表示统计信息只会存储在内存中，此时N=8，M=16（默认值）
- explain 执行计划中的rows字段表示预估扫描行数
优化器错误选择索引的处理方式
- 使用force index（不推荐）
- 修改SQL，诱导优化器使用期望的索引（推荐）
- 新建合适索引，删除误用的索引（特定场景下适用）
