09-普通索引和唯一索引

概述

普通索引：索引列的值是唯一的，但可能存在重复的索引值。
唯一索引：索引列的值是唯一的，不允许有重复的索引值。

查询过程

首先，MySQL 会扫描索引树，找到所有符合索引条件的记录。
如果索引是唯一索引，则 MySQL 可以直接定位到唯一索引值对应的记录。
如果索引是普通索引，则 MySQL 需要根据索引列的值，在索引树中进行二分查找，定位到所有符合索引条件的记录。
然后，MySQL 会根据查询条件，对定位到的记录进行过滤。
最后，MySQL 返回符合查询条件的记录。

更新过程

如果索引是唯一索引，则 MySQL 无法直接更新索引值对应的记录。
如果索引是普通索引，则 MySQL 可以根据索引列的值，在索引树中进行二分查找，定位到所有符合索引条件的记录。
然后，MySQL 会根据更新条件，对定位到的记录进行更新。
最后，MySQL 返回更新后的记录。

change buffer概念

二级索引的更新操作需要change buffer.

change buffer 是 MySQL 为了提高更新效率而引入的一种机制。change buffer 是一个内存结构，它在内存中维护一个更新列表，当有更新操作发生时，MySQL 会将更新操作记录在 change buffer 中，而不是直接将更新操作写入磁盘。

当内存中的 change buffer 占用内存达到一定比例时，MySQL 会将 change buffer 中的更新操作批量写入磁盘。

change buffer和 buffer pool 区别

Change Buffer 是在 Buffer Pool 中的。

change buffer 是在内存中维护的更新列表，buffer pool 是 MySQL 用来缓存数据的内存区域。
change buffer 仅在内存中，buffer pool 既可以在内存中，也可以在磁盘中。
change buffer 仅用于维护更新列表，buffer pool 用于缓存数据。
change buffer 的大小是固定的，buffer pool 的大小可以根据 MySQL 配置进行调整。
change buffer 仅在内存中，因此速度更快，但是数据丢失风险高。buffer pool 既可以缓存热数据，也可以缓存冷数据。
change buffer 仅用于维护更新列表，因此对查询的影响最小。buffer pool 用于缓存数据，因此对查询的影响也会有所增加。

change buffer是持久化数据，在内存中有拷贝，也会写到磁盘上。当更新数据页时，如数据页在内存中直接更新。如果不在，在不影响数据一致性的前提下，innodb会将更新操作先缓存到change buffer中，当下次查询该数据页时，执行change buffer中与该页相关的操作。该操作称为merge，除了该情况，系统后台线程也会定期merge，数据库正常关闭也会merge。 change buffer可以减少读磁盘，而且数据读入内存会占用buffer pool。

什么条件下可以使用 change buffer 呢？

对于唯一索引，更新操作都需要判断操作是否违反唯一约束，所以需要将数据都读入到内存，所以会直接更新内存。所以只有普通索引会使用change buffer。

change buffer使用buffer pool里的内存，参数innodb_change_buffer_max_size设置为50时，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

当更新记录的目标页不在内存中时，InnoDB 的处理流程如下：

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。
所以这种情况，唯一索引会导致磁盘大量随机IO的访问（机械硬盘瓶颈）。
但这种情况不是绝对的，写多读少的场景change buffer记录的变更多，收益越大。常见业务模型账单类、日志类的系统。
对于写完马上读取的情况，会立即触发merge，反而增加了维护change buffer的成本。

所以尽量选择普通索引。

定义与作用

Change buffer 是一种特殊的缓存结构，用于存储对非唯一二级索引页的修改操作。当数据库执行插入、更新或删除操作时，如果涉及到的非唯一二级索引页不在内存中，InnoDB 不会立即从磁盘读取该页到内存进行修改，而是先将修改记录在 change buffer 中。这样可以减少磁盘 I/O 操作，提高数据库的性能。

工作原理

写入过程：

当有针对非唯一二级索引的修改操作时，InnoDB 会首先检查对应的索引页是否在缓冲池中。如果不在，就将修改记录到 change buffer 中。记录的内容包括修改的类型（插入、更新或删除）以及相关的索引键值和记录指针等信息。

合并过程：

在未来的某个时刻，当需要访问该索引页时，InnoDB 会将 change buffer 中针对该页的修改合并到内存中的索引页上。这个过程称为 change buffer 的合并（merge）操作。此外，InnoDB 也会在后台定期进行 change buffer 的合并操作，以确保修改最终能持久化到磁盘上的索引文件中。

优点

减少磁盘 I/O：

通过将对非唯一二级索引的修改先缓存起来，避免了大量的随机磁盘读操作。特别是对于一些写入密集型的工作负载，如电商系统中的订单插入、用户行为日志记录等场景，能够显著提高数据库的写入性能。

适用场景：

写入密集型应用：对于那些有大量插入、更新操作的数据库应用，如社交媒体平台的用户动态发布、金融系统的交易记录等，change buffer 能够发挥很好的性能优化作用。

配置与管理：

在 MySQL 中，可以通过参数innodb_change_buffer_max_size来配置 change buffer 占用缓冲池的最大比例，默认值是 25%。