唯一索引,普通索引选择
查询过程(微乎其微影响)
假设维护一个市民系统,每个人都有一个唯一的身份证号,而且业务代码已经保证了不会写入两个重复的身份证号:
select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';
可以给id_card上面加索引,身份证号字段比较大,不建议你把身份证号当做主键。
(非主键索引会存储主键索引的值,因此推荐选用短字段当主键索引)
- 对于普通索引来说,会多一次判断,需要查找下一个记录判断是否也满足匹配条件,不满足则终止扫描。
- 对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。(是数据页最后一个记录概率小,计算平均性能差异时,仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计)
更新过程 (普通>唯一)
change buffer
概念:
写缓冲 这个change buffer属于buffer pool的一种。
- 数据在内存中就直接更新,没有在内存中就InnoDB更新操作缓存在 change buffer 中这样就不需要从磁盘中读入这个数据页了
- 触发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 消耗。