读mysql45讲-普通索引和唯一索引

普通索引和唯一索引的选择：

假设你在维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写 入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，就会执行类似这样的SQL语 句：

select name from CUser where id_card = '555';

为了提高查询效率，可以考虑在id_card上加索引。id_card字段比较大，不适合作为主键；那可以给id_card上添加唯一索引，或者添加普通索引。
上面查询语句的大概过程

• 普通索引：先在索引树上找到id_card=555，继续寻找下一条记录，直到下一个记录的id_card ！= 555；因为普通索引是可以有重复的，所以需要判断是否有多个。
• 唯一索引： 在索引树上找到id=555的值后，因为是唯一索引，不会有重复的，所以直接返回。

这两个的性能差距是很小的。

InnoDB的数据是按数据页读取的，也就是当需要读一条记录的时候， 并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每 个数据页的大小默认是16KB。

因为引擎是按页读写的，所以说，当找到k=5的记录的时候，它所在的数据页就都在内存里了。 那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针 寻找和一次计算。 当然，如果k=5这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下 一个数据页，这个操作会稍微复杂一些。 对于整型字段，一个数据页可以放近千个key，因此出现这种情况的概 率会很低。

change buffer

在更新数据或者插入数据的时候，如果修改的数据页已经在内存中了，那就直接修改；如果需要修改的数据页不在内存中，在不影响数据的一致性前提下，InnoDB会将操作先记录下来存在change buffer中。这样就不需要每次都将更新数据页的操作写入到磁盘中。
在下次的查询中，如果查询的数据页在change buffer记录的操作有关联，就执行操作，就可以保证数据的正确。

将change buffer的中操作执行到对应的数据页得到正确结果的过程叫merge。除了查询这个数据页会触发merge，系统后台线程也会定期merge，在数据库正常关闭的时候也会merge。change buffer先将操作过程记录下来，减去了直接读取数据页过程，减少了读磁盘的操作，可以提高效率。

对于唯一索引来说，所有的更新操作都需要判断这个操作有没有违反唯一性原则；比如插入一条记录，需要判断这条记录的唯一索引字段有没有已经存在，但是不能仅仅在内存中的数据页中判断，必须要在插入记录在索引树的正确位置所在的数据页中判断才行，这样就需要将对应的数据页读入到内存中，如果数据页都已经读入到内存中，就没有必要再记录在change buffer中了。

唯一索引的更新不能使用change buffer

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

执行一个插入操作，如果要更改的数据页在内存中，唯一索引需要先判断是否违反唯一性规范，没有则直接插入；普通索引则直接插入数据；如果需要更改的数据页不在内存中，唯一索引就需要将对应的数据页先读入到内存中，然后进行插入操作，普通索引则直接将插入操作记录在chage buffer中。

change buffer的使用场景

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

但是如果更新一次数据页，记录在change buffer中，然后来了一个查询，就是查询更新的数据页，那就会触发merge操作，这种就会得不偿失。所以change buffer适合写多读少的场景，比如 记录日志，录入账单等等，写完之后不会频繁查看的场景。

change buffer和redo log的区别

change buffer和redo log都是先将操作记录下，之后再去更新数据，记录到磁盘中。区别就是change buffer是为了在内存中少的去读取磁盘的数据而将操作记录下来；redo log则是将每次的操作记录下来，省去了每次都要写入磁盘的过程，统一批次将一段时间内的操作一起写入到磁盘中。所以最大区别就是change buffer是为了减少读磁盘的小号，redo log是为了减少写磁盘的消耗。