MySQL学习笔记：索引2

序言

《MySQL45讲》

change buffer

下文中说的更新：insert和update操作。

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

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

change buffer使用场景

基于上面的特性：写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

普通索引和唯一索引的区别？

查询操作上，两种效率相当。由于唯一索引需要判断是否唯一，可能性能稍次些，不过可以忽略不计。

更新操作上，因为有change buffer的存在。所以普通索引在更新时，如果数据页在内存，那么就直接更新，不在，那就缓存到change buffer中。而唯一索引因为需要判断是否唯一，不在内存的数据是一定要加载进内存的，所以无法使用change buffer的。
因此，在更新的效率上面，普通索引要好于唯一索引。

change buffer 和 redo log区别？

简单地对比这两个机制在提升更新性能上的收益的话，redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

Q：上面这句话怎么理解呢？
A：redo：一般是更新操作时，会产生redo，所以redo log里面记录的数据越多，那么写磁盘时，就越有利，因为是顺序写。change buffer也是缓存更新操作，不过它除了减少随机写的问题之外，还可以减少随机读。

MySQL索引选错的案例

1. 建立一个简单表，里面有个字段a。并且对a字段建立了索引。
2. 插入10万条数据。
3. 执行：explain select * from t where a between 10000 and 20000; 查看执行计划，会发现选择了a索引。
4. 接着开启两个事务A，事务B。事务A先开启，start transaction with consistent snapshot。
5. 事务B中，先删除表中全部数据，然后再插入10万条数据，id和之前一样。
6. 在事务B中，执行：explain select * from t where a between 10000 and 20000; 查看执行计划，会发现不再走a索引，走的是全表扫描。如果我们强制指定a索引：explain select * from t force index(a) where a between 10000 and 20000;会发现执行时间更快。
7. 这就说明MySQL自己选错索引了。

影响索引选择的因素有：扫描行锁、是否使用临时表、是否排序、回表等因素的综合考虑。

扫描行数判断

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。
统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

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

所以采用统计的结果是会影响索引的选择的。

# 可以重新统计索引信息
analyze table t 命令

上述案例中，索引选错的原因：事务A开启后的情况下，事务B先删除数据，后插入数据。但是删除不是立马删除，只是标记deleted并且事务A的存在，所以undo中的数据也不会删除。所以当在事务B中再次插入数据时，此时每一行数据都有两个版本。
这就导致采样统计时，出现了错误判断。

解决办法：

1. 使用force index(索引名)，强制指定。
2. 调整SQL语句
3. 创建一个合适的索引或者删除错误索引。（推荐）

字符串创建索引的思路

思路一：先看数据量大不大，低于5千万的数据，都可以将完整的字段创建为索引。
思路二：如果字段前缀区分度很明显，可以考虑创建前缀索引；注意事项：前缀索引需要定义好长度，才能既节省空间，又不用额外增加太多的查询成本。
思路三：如果字段前缀区分度不明显，但是字段后面区分度明显（比如身份证），可以考虑倒序索引，将字段保存前进行倒序插入。
思路四：在思路三的情况下，对字符串进行hash，在表里再创建一个字段，用来保存hash值。

思路三和思路四：倒序索引和hash的异同点：

相同点：

1. 都不支持范围查询。

不同点：

1. 存储消耗方面：倒序索引和hash，其实是差不多的。当倒序的前缀索引大于4个字节时，和hash就差不多了。
2. 倒序：每次读写，都需要reverse函数，而hash需要调用hash函数，从这两个函数效率来说，reverse消耗要小些；不过这种消耗依然可以忽略不计。
3. 从查询效率来看，使用hash的方式，查询性能会稳定些，因为倒序的方式，会增加扫描次数。而hash方式虽然会存在hash冲突，但是这种概率极低。

参考地址：

https://time.geekbang.org/column/article/70848