选择普通索引还是唯一索引?
对于查询过程来说:
a, 普通索引, 查到满足条件的第一个记录后, 继续查找下一个记录, 知道第一个不满足条件的记录
b, 唯一索引, 由于索引唯一性, 查到第一个满足条件的记录后, 停止检索
但是, 两者的性能差距微乎其微. 因为 InnoDB 根据数据页来读写的.
对于更新过程来说:
概念: change buffer
当需要更新一个数据页, 如果数据页在内存中就直接更新, 如果不在内存中, 在不影响数据一致性的前提下, InnoDB 会将这些更新操作缓存在 change buffer 中. 下次查询需要访问这个数据页的时候, 将数据页读入内存, 然后执行 change buffer 中的与这个页有关的操作.
change buffer 是可以持久化的数据. 在内存中有拷贝, 也会被写入到磁盘上
merge: 将 change buffer 中的操作应用到原数据页上, 得到最新结果的过程, 成为 merge
访问这个数据页会触发 merge, 系统有后台线程定期 merge, 在数据库正常关闭的过程中, 也会执行 merge
唯一索引的更新不能使用 change buffer, 因为需要从磁盘读数据页判断是否唯一
change buffer 用的是 buffer pool 里的内存, change buffer 的大小, 可以通过参数 innodb_change_buffer_max_size 来动态设置. 这个参数设置为 50 的时候, 表示 change buffer 的大小最多只能占用 buffer pool 的 50%.
将数据从磁盘读入内存涉及随机 IO 的访问, 是数据库里面成本最高的操作之一.
change buffer 因为减少了随机磁盘访问, 所以对更新性能的提升很明显.
change buffer 使用场景
在一个数据页做 purge 之前, change buffer 记录的变更越多, 收益就越大.
对于写多读少的业务来说, 页面在写完以后马上被访问到的概率比较小, 此时 change buffer 的使用效果最好. 这种业务模型常见的就是账单类, 日志类的系统.
反过来, 假设一个业务的更新模式是写入之后马上会做查询, 那么即使满足了条件, 将更新先记录在 change buffer, 但之后由于马上要访问这个数据页, 会立即触发 purge 过程.
这样随机访问 IO 的次数不会减少, 反而增加了 change buffer 的维护代价. 所以, 对于这种业务模式来说, change buffer 反而起到了副作用.
索引的选择和实践:
尽可能使用普通索引.
redo log 主要节省的是随机写磁盘的 IO 消耗 (转成顺序写), 而 change buffer 主要节省的则是随机读磁盘的 IO 消耗.
change buffer 一开始是写内存的, 那么如果这个时候机器掉电重启, 会不会导致 change buffer 丢失呢? change buffer 丢失可不是小事儿, 再从磁盘读入数据可就没有了 merge 过程, 就等于是数据丢失了. 会不会出现这种情况呢?
1.change buffer 有一部分在内存有一部分在 ibdata.
做 merge 操作, 应该就会把 change buffer 里相应的数据持久化到 ibdata
2.redo log 里记录了数据页的修改以及 change buffer 新写入的信息
如果掉电, 持久化的 change buffer 数据已经 merge, 不用恢复. 主要分析没有持久化的数据
情况又分为以下几种:
(1)change buffer 写入, redo log 虽然做了 fsync 但未 commit,binlog 未 fsync 到磁盘, 这部分数据丢失
(2)change buffer 写入, redo log 写入但没有 commit,binlog 以及 fsync 到磁盘, 先从 binlog 恢复 redo log, 再从 redo log 恢复 change buffer
(3)change buffer 写入, redo log 和 binlog 都已经 fsync. 那么直接从 redo log 里恢复.
来源: http://www.bubuko.com/infodetail-3399997.html