普通索引和唯一索引如何选择

• 查询过程

  • 普通索引和唯一索引的性能差别不大。

    • 因为引擎是按页读写的，按条件查询的话它所在的数据页就都在内存里了，即使普通索引还有其他的操作，但是我们计算平均性能差异时，仍可以认为这个操作成本对于现在的CPU来说可以忽略不计。

  • 尽量选择普通索引

• 更新过程

  • 当需要更新一个数据页时，如果数据页在内存中就直接更新。

  • 如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。

  • change buffer是可以持久化的数据，在内存中有拷贝，也会被写入到磁盘上。

    • change buffer 中的操作应用到原数据页，得到最新结果的过程称为merge

    • 触发merge的情况

      • 访问数据页

      • 后台线程定期运行

      • 数据库正常关闭

    • 优点：减少读磁盘，语句的执行速度会得到明显的提升；避免占用内存，提高内存利用率；

    • change buffer用的是buffer pool里的内存，因此不能无限增大。

    • 使用 change buffer 的条件

      • 普通索引可以使用，唯一索引的更新就不能使用

        • 唯一索引不能使用的原因：唯一索引所有的更新操作都要先判断这个操作是否违反唯一性约束，这必须要将数据页读入内存才能判断，如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

    • innodb处理的流程举例

      • 如果要在这张表中插入一个新记录(4,400)的话，InnoDB的处理流程是怎样的。

        • 这个记录要更新的目标页在内存中

          • 对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；

          • 对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。

        • 这个记录要更新的目标页不在内存中

          • 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；

          • 对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。

    • change buffer的使用场景

      • 写多读少的业务，业务模型常见的就是账单类、日志类的系统。

      • 因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

• 索引的选择和实践

  • 所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer。而在其他情况下，change buffer都能提升更新性能。

• redo log

  • 在MySQL里也有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。

  • WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘

  • 更新过程

    • 当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做

  • InnoDB的redo log是固定大小的，写到末尾就又回到开头循环写。

  • write pos是当前记录的位置，一边写一边后移

  • checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

  • write pos和checkpoint之间的是空着的部分，可以用来记录新的操作。

  • 如果write pos追上checkpoint，表示满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。

  • crash-safe ：可以保证即使数据库发生异常重启，之前提交的记录都不会丢失

• change buffer和redo log之间的对比

  • redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

• 小结

  • 问题1：系统表空间跟数据表空间这两个概念各是什么意思.

    • 系统表空间就是用来放系统信息的，比如数据字典什么的，对应的磁盘文件是ibdata1

    • 数据表空间就是一个个的表数据文件，对应的磁盘文件就是 表名.ibd

  • 问题2：change buffer一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致change buffer丢失呢？

    • 会导致change buffer丢失，会导致本次未完成的操作数据丢失，但不会导致已完成操作的数据丢失。

    • change buffer中分两部分，一部分是本次写入未写完的，一部分是已经写入完成的。

    • 针对未写完的，此部分操作，还未写入redo log，因此事务还未提交，所以没影响。

    • 针对已经写完成的，可以通过redo log来进行恢复。