跳到主要内容

09-15

09 | 普通索引和唯一索引，应该怎么选择？​

  • change buffer: 当需要更新一个数据页的时候，如果数据页在内存中就直接更新，如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了，在下次查询需要访问这个数据页时，将数据页读入内存，然后执行 change buffer 中与这个数据页有关的操作，依次保证这个数据逻辑的正确性
    change buffer 实际上是可持久化的数据，也就是在内存中有拷贝，也会写入到磁盘上
    将 change buffer 中的操作应用到原数据页得到最新结果的过程称为 merge，除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge，在数据库正常关闭(shutdown)的过程中也会执行 merge 操作
    将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显提升，而且数据读入内存是需要占用 buffer pool 的，因此这种方式还能避免占用内存，提高内存利用率
    使用 change buffer 的条件: 对于唯一索引来说，所有的更新操作都得先判断是否违反唯一性约束，此时就需要将数据页读入内存才能判断，此时没必要使用 change buffer，因此也只有普通索引可以使用 change buffer
    插入新记录(4,400)时 InnoDB 的处理流程:

    • 情况1: 这个记录要更新的目标页在内存中，处理流程如下
      • 对于唯一索引，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束
      • 对于普通索引，找到 3 和 5 之间的位置，插入这个值，语句执行结束
    • 情况2: 这个记录要更新的目标页不在内存中，处理流程如下
      • 对于唯一索引，需要将数据页读入内存，判断没有冲突，插入这个值，语句执行结束
      • 对于普通索引，则是将更新记录在 change buffer，语句执行就结束了

    将数据从磁盘读入内存涉及到随机 IO 的访问，是数据库里面成本最高的操作之一，change buffer 因为减少了随即磁盘访问，所以对更新性能的提升十分明显

  • change buffer 的使用场景: merge 的时候是真正进行数据更新的时候，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多(这个数据页上要更新的次数越多)，收益越大
    因此对于写多读少的业务来说，页面写完以后马上被访问的概率比较小，此时 change buffer 的使用效果最好，常见于账单类、日志类的系统
    反之，如果一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但由于马上要访问这个数据页，会立即触发 merge 过程，这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价，对于这种业务模式，change buffer 起了反作用

  • 索引选择和实践: 普通索引和唯一索引在查询能力上是没区别的，主要考虑对更新性能的影响，因此尽量选择普通索引
    如果在所有更新后面都马上伴随着对这个记录的查询，那么应该关闭 change buffer，而其他情况下 change buffer 都能提升更新性能

  • change buffer 和 redo log: redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写)，而 change buffer 主要节省的则是随机读磁盘的 IO 消耗

10 | MySQL为什么有时候会选错索引？​

  • 优化器的逻辑
    选择索引是优化器的工作，优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句，在数据库里面，扫描行数是影响执行代价的因素之一，扫描的行数越少意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少(扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断)
  • 如何判断扫描行数