MySQL数据更新流程原来这么复杂

        要想了解这个问题，需要先清楚MySQL数据库的结构和相关特性，首先看下官方提供的结构图：

        它由这几部分组成

• 连接池组件：用于管理连接、身份认证、建立断开连接、维持连接等
• 管理服务和工具组件：数据的备份恢复、复制、迁移、管理等
• SQL接口组件：进行DML、DDL以及用户SQL命令等
• 查询分析优化器：对SQL进行查询和优化，对预发进行进行分析优化
• 优化器组件：选择最优的查询方案
• 缓存：各种缓存
• 插件式存储引擎：各种存储引擎
• 物理文件：日志文件表空间等

        插件式存储引擎是MySQL区别于其他数据库的一个重要特点。因为工作中主要使用InnoDB存储引擎，所以下边提到个更新流程也是基于InnoDB做介绍的，关于其他存储引擎后边再做详细介绍。

 InnoDB特性

        数据库是基于物理磁盘的文件系统，大家都知道物理磁盘和内存之间的速度差距存在着巨大鸿沟，InnoDB存储引擎也不例外，磁盘的数据库都会通过内存来提升数据库整体性能，通过内存来弥补磁盘对性能的影响。

        数据库的数据在内存中是用页来维护的，将数据放入页中供用户进行CRUD，然后再以一定的评率将页数据刷新到磁盘上。

        这里就引申出了InnoDB的关键特性：Insert Buffer、double write、自适应哈希、异步IO、刷新邻接页，由于本篇文章主要讲解更新流程，其他特性欢迎留言讨论。

 Insert Buffer

        Innodb存储引擎中，主键是唯一的标识符，通常应用程序中行记录的插入顺序是按照主键递增进行插入的，因此插入聚集索引一般是顺序的不存在磁盘的随机读取。但是不可能每张表上只有一个聚集索引，更多的情况是有多个非聚集的辅助索引，在这种情况下，数据页的存放还是按照主键进行顺序存放的，但是对于非聚集索引叶子节点的插入不再是顺序的，这时就需要离散的访问非聚集索引页，由于随机读取的存在导致插入性能的下降。

        Innodb设计了Insert Buffer，对于非聚集索引的操作或更新，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，直接插入；若不在，先放入到一个Insert Buffer对象中。然后再以一定的频率进行insert buffer和辅助索引叶子节点的merge操作，这是通过它将多个插入合并到一个操作中，这就大大提高了非聚集索引插入的性能。

        Insert Buffer的使用需要同时满足两个条件：

• 索引是辅助索引
• 索引不是唯一的：不需要查找数据是否是唯一的，如果去查找唯一性肯定会有离散性的发生，导致Insert Buffer失去意义。

        InnoDB从1.0.x版本引入了Change Buffer，可以将其视为Insert Buffer的升级。那更新数据是是如何工作的呢？

更新流程

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

        将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

        对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

        因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

        普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议尽量选择普通索引。

        欢迎关注留言讨论。

往期精彩内容推荐

MySQL自增主键原来有这么大作用-CSDN博客

MySQL计数优化探秘：COUNT(*)、COUNT(主键)与索引字段，谁是性能王者？-CSDN博客

MySQL中order by原来是这么工作的-CSDN博客