MySQL学习笔记-优化器选索引的因素

MySQL学习笔记-优化器选索引的因素

1.笔记图

2.优化器的逻辑

• 功能作用：
• 选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句
• 在数据库里面，扫描行数是影响执行代价的因素之一
• 扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少
• 选择索引考虑的因素：
• 扫描行数：
  • MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，只能根据统计信息来估算记录数
  • 把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择采样统计
  • 采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数
  • 数据表是会持续更新的，索引统计信息也会变，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计
• 两种存储索引统计的方式：参数 innodb_stats_persistent
  • 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10
  • 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16
• 是否使用临时表
• 是否回表
• 是否排序
  由于 MVCC 多版本并发控制，不同事务看到的总行数可能不一样，每次全表扫描统计准确值代价太大，扫描行数是抽样统计数据，这个抽样统计数据值会存在不准确的情况，这就使得 MySQL 在某些情况下误判选错索引。

Tips：由于 MVCC 多版本并发控制，不同事务看到的总行数不一样，每次全表扫描统计代价太大，所以扫描行数是抽样统计数据，这个抽样统计值在某些情况下会不准确，所以 MySQL 选错索引是在判断扫描行数时不准确造成的。

3.索引选择异常的优化

• 第一种方法
• 采用 force index 强行选择一个索引
• MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中依次判断每个索引需要扫描多少行
• 如果 force index 指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价
• 第二种方法
• 可以考虑修改语句，引导 MySQL 使用我们期望的索引
• 如修改 limit、order by
• 第三种方法
• 在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引

扫码关注