普通、唯一索引的选择与实践

唯一索引，普通索引选择

查询过程（微乎其微影响）

假设维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号:

select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';

可以给id_card上面加索引，身份证号字段比较大，不建议你把身份证号当做主键。(非主键索引会存储主键索引的值，因此推荐选用短字段当主键索引)

1. 对于普通索引来说，会多一次判断，需要查找下一个记录判断是否也满足匹配条件，不满足则终止扫描。
2. 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。（是数据页最后一个记录概率小，计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计）

更新过程 (普通>唯一)

change buffer概念：
写缓冲 这个change buffer属于buffer pool的一种。

1. 数据在内存中就直接更新，没有在内存中就InnoDB更新操作缓存在 change buffer 中这样就不需要从磁盘中读入这个数据页了
2. 触发merge的时机： 1.访问数据页，将changebuffer和bufferpool中的数据合并 2.定期merge 3.关闭线程之前，merge

什么条件下可以使用 change buffer 呢？
对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

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

性能比较：

更新的目标页在内存中 (微乎其微)

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

更新的目标页在不在内存中

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

change buffer 的使用场景

change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

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

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

索引选择和实践

• 普通索引和唯一索引的选择上，主要不同点在更新操作上面，尽量选择普通索引
• 更新后面，马上伴随着对这个记录的查询，应该关闭 change buffer
• 机械硬盘时，change buffer 这个机制的收效是非常显著的
• 当有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

change buffer 和 redo log

现在，我们要在表上执行这个插入语句：
mysql> insert into t(id,k) values(id1,k1),(id2,k2);

这里，我们假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。图 所示是带 change buffer 的更新状态图。

(图中的两个虚线箭头，是后台操作，不影响更新的响应时间)

步骤：

1. Page 1 在内存中，直接更新内存；
2. Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
3. 将上述两个动作记入 redo log 中

涉及了四个部分：内存、redo log（ib_log_fileX）、 数据表空间（t.ibd）、系统表空间（ibdata1）。

数据表空间：就是一个个的表数据文件，对应的磁盘文件就是“表名.ibd”； 系统表空间：用来放系统信息，如数据字典等，对应的磁盘文件是“ibdata1”

change_buffer读过程

• 读 Page 1 的时候，直接从内存返回。不用redo log 里面把数据更新以后才可以返回。
• 读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果（Merge过程）

简单地对比这两个机制在提升更新性能上的收益的话

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