一条SQL更新语句是如何执行的？ _SQL

这篇文章会通过一条SQL更新语句的执行流程让大家清楚地明白：

什么是InnoDB页？缓存页又是什么？为什么这么设计？
什么是表空间？不同存储引擎的表在文件系统的底层表示上有什么区别？
Buffer Pool是什么？为什么需要？有哪些我们需要掌握的细节？
MySQL的三种日志文件redo日志、undo日志、binlog分别是什么？为什么需要这么多种类型的日志？

正文开始！
之前我们讲过了一条SQL查询语句是如何执行的，那么插入（INSERT）、更新（UPDATE）和删除（DELETE）操作的流程又是什么样子呢？
其实对于MySQL而言，只有两种通常意义的操作，一种是Query（查询），另一种是Update（更新），后者包含了我们平常使用的INSERT、UPDATE和DELETE操作。
那么MySQL的更新流程和查询流程有什么区别呢？
其实基本的流程是一致的，也要经过处理连接、解析优化、存储引擎几个步骤。主要区别在更新操作涉及到了MySQL更多的细节。

文章插图

注：我们接下来的所有描述，针对的都是InnoDB存储引擎，如果涉及到其他存储引擎，将会特殊说明

1. 一些需要知道的概念对于MySQL任何存储引擎来说，数据都是存储在磁盘中的，存储引擎要操作数据，必须先把磁盘中的数据加载到内存中才可以。
那么问题来了，一次性从磁盘中加载多少数据到内存中合适呢？当获取记录时，InnoDB存储引擎需要一条条地把记录从磁盘中读取出来吗？
当然不行！我们知道磁盘的读写速度和内存读写速度差了几个数量级，如果我们需要读取的数据恰好运行在磁盘的不同位置，那就意味着会产生多次I/O操作。
因此，无论是操作系统也好，MySQL存储引擎也罢，都有一个预读取的概念。概念的依据便是统治计算机界的局部性原理。

空间局部性：如果当前数据是正在被使用的，那么与该数据空间地址临近的其他数据在未来有更大的可能性被使用到，因此可以优先加载到寄存器或主存中提高效率

就是当磁盘上的一块数据被读取的时候，我们干脆多读一点，而不是用多少读多少。
1.1 InnoDB页InnoDB存储引擎将数据划分为若干个页，以页作为磁盘和内存之间交互的最小单位。InnoDB中页的大小默认为16KB 。也就是默认情况下，一次最少从磁盘中读取16KB的数据到内存中，一次最少把内存中16KB的内容刷新到磁盘上。

文章插图
对于InnoDB存储引擎而言，所有的数据（存储用户数据的索引、各种元数据、系统数据）都是以页的形式进行存储的。
1.2 表空间为了更好地管理页，MySQL又设计了「表空间」的概念。表空间又有很多类型，具体类型我们不需要知道，我们只需要知道，一个表空间可以划分成很多个InnoDB页，InnoDB表数据都存储在某个表空间的页中。
为了方便我们定位，MySQL贴心地为表空间设计了一个唯一标识——表空间ID（space ID）。同理，InnoDB页也有自己的唯一编号——页号（page number）。
因此，我们可以这么认为。给定表空间ID和页号以及页的偏移量，我们就可以定位到InnoDB页的某条记录，也就是数据库表的某条记录。
1.2.1 数据表在文件系统中的表示为了更好地让大家理解这个抽象的概念，我创建了名为test的数据库，在其下分别创建了3张表t_user_innodb，t_user_myisam，t_user_memory，对应的存储引擎分别为InnoDB、MyISAM、MEMORY 。
进入MySQL的数据目录，找到test目录，看一下test数据库下所有表对应的本地文件目录
drwxr-x--- 2 mysql mysql 4096 Jan 26 09:28 .
drwxrwxrwt 6 mysql mysql 4096 Jan 26 09:24 ..
-rw-r----- 1 mysql mysql 67 Jan 26 09:24 db.opt
-rw-r----- 1 mysql mysql 8556 Jan 26 09:28 t_user_innodb.frm
-rw-r----- 1 mysql mysql 98304 Jan 26 09:28 t_user_innodb.ibd
-rw-r----- 1 mysql mysql 8556 Jan 26 09:27 t_user_memory.frm
-rw-r----- 1 mysql mysql 0 Jan 26 09:28 t_user_myisam.MYD
-rw-r----- 1 mysql mysql 1024 Jan 26 09:28 t_user_myisam.MYI
-rw-r----- 1 mysql mysql 8556 Jan 26 09:28 t_user_myisam.frm
1.2.2 InnoDB是如何存储表数据的「表空间」是InnoDB存储引擎独有的概念。
我们看到t_user_innodb表在数据库对应的test目录下会生成以下两个文件