实践篇

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

查询过程

• 对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录；

• 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

这两个查询操作操作在性能上差别是很小的，因为 InnoDB 存储引擎是以页为单位来读写数据的，一页的数据为 16KB，当需要读取一条记录时，并不是将一条记录从磁盘上读到内存中，而是将记录所在的一页数据全部读入内存，所以在查询某一条记录时，将一页数据读入内存，那么它的下一条数据很大概率上也已经在内存里了。

更新过程

change buffer

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

change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程成为 merge。触发 merge 操作的时机：

• 数据页被访问，从磁盘加载到内存；

• 系统有后台线程会定期 merge；

• 数据库正常关闭（shutdown）的过程中。

change buffer 优势：

• 如果能够将更新操作操作先记录到 change buffer，减少读磁盘，语句的执行速度会得到明显提升；

• 数据读入内存是需要占用 buffer pool 的，change buffer 的方式还能够避免占用内存，提高内存利用率。

change buffer 的作用场景：

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

所以唯一索引的更新是不能使用 change buffer 的，只有普通索引可以使用到 change buffer。

change buffer 使用的是 buffer pool 的内存，所以不能无限放大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

如果要新增一条记录，如果新增记录所在数据页在内存中，普通索引和唯一索引插入操作上区别不大（唯一索引需要判断记录是否存在，由于数据页已在内存中，这个操作影响很小）；如果新增记录所在数据页不在内存中，普通索引和唯一索引插入操作上区别很大，普通索引只要将更新内容记录到 change buffer 中，执行语句就结束了，而唯一索引，必须要将数据从磁盘读到内存，判断有没有冲突，然后插入数据操作完成。

将数据从磁盘读入内存是一次随机 IO 操作，是数据库中成本非常高昂的操作，change buffer 因为减少了随机磁盘访问，所以能明细提升更新性能。

change buffer 的使用场景

change buffer 只适用于普通索引，不适用于唯一索引。

change buffer 目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

对于写多读少的业务，数据写后马上被访问的概率很小，因此 change buffer 的作用效果很好。反之，如果写入数据后马上要执行查询操作，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。

索引选择和实践

对于查询操作来说，普通索引和唯一索引的差别不大；但是对于更新操作影响是比较大的。

如果数据更新操作之后马上涉及到数据查询操作，应该关闭 change buffer 功能，在其他情况下，change buffer 对于性能提升都会有一定作用。

普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。

如果使用的是普通”机械硬盘“存储数据，开启 change buffer 对于提升性能效果还是很明显的。

change buffer 和 redo log

WAL 提升性能的核心机制，也的确是尽量减少随机读写。change buffer 也有类似的效果。

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

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

在数据库里，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘的行数越少，消耗的 CPU 资源越少。

一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

• 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10；

• 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

索引选择异常和处理

采用 force index 强行选择一个索引。弊端：实现不优雅；索引名字改变需要修改 SQL 语句；迁移数据库可能语法不支持。

对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决。