二级索引 -＞ 普通索引 与 唯一索引

唯一索引和普通索引使用的结构都是B-tree,执行时间复杂度都是O(log n)。

非主键的都是二级索引, 二级索引又包括了唯一索引和普通索引

1、普通索引

• 普通索引 -> 由关键字KEY或INDEX定义的索引
• 作用 : 普通索引的唯一任务是加快对数据的访问速度
• 适用场景 :
  • 查询条件为（WHERE column）
  • 排序条件为（ORDER BY column）中的数据列创建索引
  • 只要有可能，就应该选择一个数据最整齐、最紧凑的数据列来创建索引。

2、唯一索引

• 普通索引允许被索引的数据列包含重复的值
• 如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。
• 优点 :
  • 一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；
  • 二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。
  • 唯一索引可以保证数据记录的唯一性
  • 事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

3、在不同业务情况下 唯一索引 与 普通索引 的选择

场景 : 维护与一个市民系统 :

• 每一个人都有对应的且唯一的身份证号码, 而且业务代码保证不会写入两个重复的身份证号码, 如果需要查询市民的姓名, 就会执行类似下面的 SQL 语句 :

  select name from CityUser where id_card = 'xxxxxxxyyyyyyzzzzz';
  

• id_card 索引建立考虑

  • id_card 字段创建唯一索引
  • 创建一个普通索引

• 在逻辑上, 这两个选择都是正确的, 但在性能方面该如何做出选择呢 ?

查询过程

• 执行查询的语句

select id from Table where k = 5;

• 存储结构

• 普通索引 :
• 查找到满足条件的第一个记录 (5, 500)后, 需要查找下一个记录, 直到碰到第一个不满足 k = 5 的记录
• 唯一索引 :
  • 由于唯一索引具有唯一性, 查找第一个满足条件的记录后, 就会停止继续检索
• 性能差距
  • 对性能的影响 - > 微乎其微
  • 两者的性能差距在于 普通索引相对于 唯一索引 需要多进行一次 “查找和判断下一条记录”, 就相当于多一次指针寻找和计算
  • InnoDB 的数据是安数据页为单位进行读写的, 也就是说, 当需要读一条记录的时候, 是以页为单位, 将其整体读如内存, 在 InnoDB 中 页的大小为 16KB
  • 考虑到如果 k = 5 的数据为这一页的最后一个数据, 那么查询的时候会出现跨页情况, 同时相对于 16KB 数据来说, 最后一个数据的概率基本可以达到忽略不计, 对 CPU 操作来说这个成本基本上可以忽略不计
• 结论 : 在查询过程中 普通索引 的性能与 唯一索引 的性能只存在微小的差距, 可以忽略不计

更新过程 :

• 插入一条数据 (4, 400), InnoDB 的处理流程
• 更新数据页在内存中 :
  • 唯一索引 : 找到目的位置 -> 判断有无冲突 -> 插入数据 -> 执行结束
  • 普通索引 : 找到目的位置 -> 插入数据 -> 执行结束
  • 区别 : 唯一索引 对比 普通索引 多了一次判断过程, 但只是对 CPU 消耗极其微小的时间
• 更新数据页不在内存中 :
  • change buffer 底下面有解释
  • 唯一索引 : 将数据页读入内存 -> 找到目的位置 -> 判断有无冲突 -> 插入数据 -> 执行结束
  • 普通索引 : 将更新记录在 change buffer -> 执行结束
  • 区别 : 唯一索引会进行大量的读磁盘操作, 较严重的影响了数据的写入效率, 在这个方面性能比普通索引低很多

总结 :

• 其实, 这两类索引在查询能力上是没有差别的, 主要考虑的是对更新的性能的影响, 所以建议尽量选择普通索引

• 普通索引对大表的更新优化还是很明显的

• 普通索引 与 唯一索引, 普通索引在更新时速度更快, 尽量选择普通索引

• 更新之后就是查询时, 不使用 change buffer

• change buffer 更适合普通索引

• 如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

innodb_change_buffer_max_size = 0

change buffer

• Q :

  • 内存大小
  • 运行逻辑
  • 有无满情景 ->
  • 执行时间
  • 执行流程,数据一致性控制

• change buffer 是一个可以持久化的数据, 所以在内存种有拷贝, 当然也会写入磁盘种

• 更新一个数据页

  • 数据页在内存中就直接更新
  • 这个数据页不在内存中, 在不影响数据一致性的前提下, InnoBD 会将这些更新操作缓存在 change buffer 中, 这样就不需要从磁盘中读入这个数据页, 在下次查询需要访问这个数据页的时候, 将数据页读如内存, 然后执行 change buffer 中与 这个页有关的操作, 通过这种方式来保证这个数据逻辑的正确性

• 更新后 - > merge

  • 将 change buffer 中下的操作应用到原数据页, 得到的最新结果为 merge
  • 触发 merge 的情景 :
    • 访问数据页, 加载进入内存的时候
    • 系统后台线程会定期 merge
    • 在数据库正常关闭 ( shutdown ) 的过程中

• 优点 :

  • 减少读磁盘的次数, 语句的执行速度会得到明显的提升
  • 数据读入内存是需要 buffer pool 的, 所以这种方式还能避免占用内存, 提高内存利用率

• 使用情景 :

  • change buffer 只限用于 普通索引, 且不适用 唯一索引
  • 因 merge 的时候是真正进行数据更新的时候, 而 change buffer 的主要目的是将变更动作缓存下来, 在一个数据页做 merge 之前记录的变更越多, change buffer 记录的变更越多收益就越大
  • 对于写多读少的业务来说, 使用 change buffer 的效果最好
  • 对于写完立即读的业务而言, changer buffer 反而会起反作用, 因为这样随机 IO 的次数并不会减少, 反而增加了 change buffer 的维护代价

change buffer 和 redo log 运行情景

• WAL 提升性能的核心机制 -> 尽量减少随机读写

插入数据

// k1 在内存中, k2 不在内存中
mysql> insert into t(id,k) values(id1,k1),(id2,k2);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ok8OykY3-1610175490702)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210109144554266.png)]

• 更新流程 :
  • Page 1 在内存中, 直接更新内存
  • Page 2 不在内存中, 就在内存的 change buffer 中记录操作
  • 将上述两个动作记入 redo log 中 -> 3, 4
  • 事务完成
  • 总的操作 : 写两处内存, 写一次磁盘

读取数据

• 读数据不会涉及到 redo log 所以 redo log 不会参与读的过程中

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fB036Y22-1610175490706)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210109144948698.png)]

• 读数据的流程
  • 读 Page 1 的时候，直接从内存返回
  • 要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的 merge

总结 :

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

change buffer 会不会因为断电而数据丢失呢 ?

• 答案肯定是不会的, 不然肯定不会拿来使用的, 毕竟数据丢失可是很大的问题
• change buffer有一部分在内存有一部分在 ibdata.
  • 做purge操作,应该就会把change buffer里相应的数据持久化到ibdata
• redo log里记录了数据页的修改以及change buffer新写入的信息
• 如果掉电,持久化的change buffer数据已经purge,不用恢复。主要分析没有持久化的数据, 情况又分为以下几种:
  • change buffer 写入,redo log 虽然做了 fsync 但未commit, binlog 未 fsync 到磁盘,这部分数据丢失
  • change buffer 写入,redo log写入但没有commit, binlog以及 fsync 到磁盘,先从 binlog 恢复 redo log,再从 redo log 恢复 change buffer
  • change buffer 写入,redo log 和 binlog 都已经 fsync.那么直接从redo log 里恢复。

参考 : 林晓斌 MySQL实战45讲 https://time.geekbang.org/column/article/70848

[gentleman_hai] (https://home.cnblogs.com/u/gentlemanhai/) https://www.cnblogs.com/gentlemanhai/p/13685295.html