MySQL原理，看这一篇就够了（InnoDB、MVCC、索引、SQL优化）

对于一位后端开发者，或者说Java后端开发者，每天都需要和数据库打交道，而我们常常接触的MySQL原理，你是否了解过呢？本文将对MySQL的一些原理进行描述，希望能帮助大家了解数据库。

注：本文中对于数据库的介绍以及研究都是在 MySQL 上进行的，如果涉及到了其他数据库的内容或者实现会在文中单独指出。

非完全原创，大部分内容引自他人博客。

---

---

一、数据库的概念

数据库的定义

很多开发者在最开始时其实都对数据库有一个比较模糊的认识，觉得数据库就是一堆数据的集合，但是实际却比这复杂的多，数据库领域中有两个词非常容易混淆，也就是数据库和实例：

• 数据库：物理操作文件系统或其他形式文件类型的集合；

• 实例：MySQL 数据库由后台线程以及一个共享内存区组成；

对于数据库和实例的定义都来自于 《MySQL 技术内幕：InnoDB 存储引擎》 一书，想要了解 InnoDB 存储引擎的读者可以阅读这本书籍。

数据库和实例

在 MySQL 中，实例和数据库往往都是一一对应的，而我们也无法直接操作数据库，而是要通过数据库实例来操作数据库文件，可以理解为数据库实例是数据库为上层提供的一个专门用于操作的接口。

在 Unix 上，启动一个 MySQL 实例往往会产生两个进程， mysqld 就是真正的数据库服务守护进程，而 mysqld_safe是一个用于检查和设置 mysqld 启动的控制程序，它负责监控 MySQL 进程的执行，当 mysqld 发生错误时， mysqld_safe 会对其状态进行检查并在合适的条件下重启。

---

二、MySQL

2.1 MySQL 的架构

MySQL 从第一个版本发布到现在已经有了 20 多年的历史，在这么多年的发展和演变中，整个应用的体系结构变得越来越复杂：

支持接口是第三方语言对数据库的操作接口，这里不再赘述。

• 1）连接层

最上层的连接池是一些连接服务，包含本地sock通信和大多数基于C/S工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全连接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

• 2）服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口、缓存的查询、SQL的分析和优化、内置函数等。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在频繁读操作的环境中能够很好的提升系统的性能。

• 3）引擎层

存储引擎真正的负责MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有的特性不同，我们可以根据实际需进行选取。下文将对相关存储引擎进行具体介绍。

• 4）存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

2.1.1 SQL的执行过程

数据库通常不会被单独使用，而是由其它编程语言通过SQL支持接口调用MySQL，由MySQL处理并返回执行结果。首先，其它编程语言通过SQL支持接口调用MySQL，MySQL收到请求后，会将该请求暂时放在连接池，并由管理服务与工具进行管理。当该请求从等待队列进入到处理队列时，管理器会将该请求传给SQL接口，SQL接口接收到请求后，它会将请求进行hash处理并与缓存中的数据进行对比，如果匹配则通过缓存直接返回处理结果；否则，去文件系统查询：由SQL接口传给后面的解析器，解析器会判断SQL语句是否正确，若正确则将其转化为数据结构。解析器处理完毕后，便将处理后的请求传给优化器控制器，它会产生多种执行计划，最终数据库会选择最优的方案去执行。确定最优执行计划后，SQL语句交由存储引擎处理，存储引擎将会到文件系统中取得相应的数据，并原路返回。

---

2.2 MySQL的存储引擎

MySQL在5.1版本之前默认存储引擎为MyISAM，在5.1版本版本之后为InnoDB。

• 1）MyISAM存储引擎

MyIsam 的存储文件有三个，后缀名分别是 .frm、.MYD、MYI，其中 .frm 是表的定义文件，.MYD 是数据文件，.MYI 是索引文件。MyIsam 只支持表锁，不支持事务。MyIsam 由于有单独的索引文件，在读取数据方面的性能很高。Myisam是以堆结构进行组织数据，其表容易损坏。

• 2）InnoDB

InnoDB 的存储文件有两个，后缀名分别是 .frm 和 .idb，其中 .frm 是表的定义文件，而 idb 是数据文件。InnoDB 中存在表锁和行锁，不过行锁是在命中索引的情况下才会起作用。InnoDB 支持事务，且支持四种隔离级别（读未提交、读已提交、可重复读、串行化），默认的为可重复读。

两种存储引擎的对比：从MyISAM和InnoDB的存储文件可看出，MyISAM注重的是对数据的快速读取，但由于MyISAM不支持事务，同时缺乏灵活性。而InnoDB支持事务和行级锁，因此在5.1之后MySQL的默认存储引擎为InnoDB。

---

2.2.1 InnoDB存储引擎架构

在MySQL 8.0 版本中，InnoDB存储引擎架构分为 内存区架构 与 磁盘区架构。

InnoDB 内存中组件

·
Buffer Pool

理解了缓存池 BP（Buffer Pool）的原理，对于 InnoDB 架构中数据存储原理也差不多理解了一半了。BP 主要结构是个 List，用的是LRU算法的变种（least recently used）。

• InnoDB存储引擎的核心缓冲区。在 BP 之中，加载表和索引数据。主要用来缓存数据块（page，也叫页），MySQL默认大小为16k，Oracle为8k。
• InnoDB缓存表数据和索引数据的主要区域。占据80%以上的物理内存，在专用数据库服务器中所有会话的共享缓冲区。BP 在整个内存中占绝大多数，MySQL官方一般建议设置为服务器物理内存的80%。建议MySQL整体内存控制在80%左右，不要超过80%，不然容易OOM。
• InnoDB使用LRU页面置换算法。如果内存达到了阈值，那么就会把最近最少使用到的page驱逐（evicted）出内存。然后把新页插入到中间位置，一般为整条list的热端5/8处。

查看下图可以看到，整个 BP 分为两个 Sublist，上端为热端，缓存热点数据（频繁被访问）；另外一端为冷端，随时被驱逐出内存。

BP 的主要作用是 提升访问效率，因为磁盘访问数据速度不如内存。所有用户访问的数据，都要先经过 BP ，但如果走全表扫描，按道理也会把数据缓存在 BP 中，那么势必会把大量数据驱逐出去，当然我们也可以通过调整参数来优化。优化的相关知识不在本文作详细介绍。

Change Buffer

可变缓冲区（Change Buffer），在内存中，可变缓冲区是InnoDB缓冲池的一部分，在磁盘上，它是系统表空间的一部分，因此即使在数据库重新启动之后，索引更改也会保持缓冲状态。

可变缓冲区是一种特殊的数据结构，当受影响的页不在缓冲池中时，缓存对辅助索引页的更改。


Log Buffer

日志缓冲区（Log Buffer ），主要保存写到redo log(重放日志)的数据。周期性的将缓冲区内的数据写入redo日志中。将内存中的数据写入磁盘的行为由innodb_log_at_trx_commit 和 innodb_log_at_timeout 调节。较大的redo日志缓冲区允许大型事务在事务提交前不进行写磁盘操作。

变量：innodb_log_buffer_size (默认 16M)


Adaptive Hash Index

自适应哈希索引（Adaptive Hash Index）功能可以让 InnoDB 在不牺牲事务功能或可靠性的情况下，在工作负载和缓冲池有足够内存的适当组合的系统上，更像是内存数据库。

哈希索引是根据对经常访问的索引页面的需求而建立的，在某些情况下哈希索引的查找速度更快。当查询的是热点数据时，可以根据hash值快速从 BP 中匹配到值。

根据观察到的搜索模式，使用索引关键字的前缀构建哈希索引。该前缀可以是任何长度，并且可能是哈希树索引中仅B树中的某些值出现。

自适应哈希索引功能由innodb_adaptive_hash_index 变量启用 ，或在服务器启动时由禁用 --skip-innodb-adaptive-hash-index。

---

InnoDB 磁盘上的组件

·
系统表空间（System Tablespace）

除了存储表数据之外，InnoDB也支持查找表元信息，存储和检索MVCC信息以兑现服从ACID和事务隔离性等原则。它包含几种类型的InnoDB对象信息。


通用表空间（General Tablespace）

共享的 Tablespace 存储多个表信息，在MySQL 5.7.6时引入。用户只能使用CREATE TABLESPACE创建一个这样的表空间。 TABLESPACE选项可以在使用CREATE TABLE命令创建一个表然后 ALTER TABLE 将表移入通用空间时发挥作用。


独立表空间（File-Per-Table Tablespace）

独立表空间是一种单表表空间，它创建在自有的数据文件中而不是在系统表空间中。当innodb_file_per_table选项启用时，表会创建在独立表空间中。否则，InnoDB表会创建在系统表空间中。每一个独立表空间都由一个.ibd数据文件表示，它在默认情况下会创建在数据库目录中，在MySQL 5.7时引入。

只有在innodb_file_per_table选项启用后，才会开启独立表空间，此后创建的表会在独立空间内，之前创建的表仍在System Tablespace。


双写缓冲区文件（Doublewrite Buffer Files）

系统 Tablespace 的存储区域，InnoDB在写入物理文件之前先将页从InnoDB Buffer pool写入此空间。mysqld进程突然崩溃会导致部分写问题。InnoDB可以从这个区域拿到一个备份。 变量: inndb_doublewrite (默认开启)。


Redo日志（Redo Log）

用于灾难恢复。mysqld启动的时候，InnoDB会尝试执行自动恢复，将不完整的事务更改矫正。还未完成更新数据文件的事务会在mysqld启动时会根据此日志记录中的信息被重放。它使用 LSN(Log Sequence Number)值来重放信息，因为mySQL会为每个事务赋予一个ID。因为大量数据更改不可能及时写道磁盘，所以得先记录到redo日志，然后再写入磁盘。

在Redo日志里，所有更改都会带有 row_id, 旧的列值，新的列值, session_id 和时间。


UNDO日志和UNDO表空间

UNDO Tablespaces 包含一个或多个Undo日志文件。UNDO通过为事务（MVCC）保存被更改还未提交的值保持读一致性。未提交值从这个存储区域读取。UNDO日志也被叫做回滚数据段。

默认地，UNDO日志是系统表空间的一部分。但MySQL允许UNDO日志置于一个单独的表空间中。这需要在初始化mysqld之前进行更改才起作用。

当我们配置单独UNDO表空间时，系统表空间的UNDO日志就被抑制了，但是一旦配置成单独的，我们只能删除UNDO日志的一部分，比如过期日志，而不能删除它。


临时表空间（Temporary Tablespace）

为临时表和相关对象提供存储功能，存储包括临时表未提交的数据。在MySQL 5.7.2引入，用于对临时表修改的回滚。 ibtmp1每次系统启动被重新创建，避免Redo日志对临时表的I/O操作。

---

2.2.2 InnoDB 数据的存储

在整个数据库体系结构中，我们可以使用不同的存储引擎来存储数据，而绝大多数存储引擎都以二进制的形式存储数据；这一节会介绍 InnoDB 中对数据是如何存储的。

注：这一节中关于数据库中数据存储、结构的内容全是基于InnoDB 引擎的。

在 InnoDB 存储引擎中，所有的数据都被逻辑地存放在表空间中，表空间（tablespace）是存储引擎中最高的存储逻辑单位，在表空间的下面又包括段（segment）、区（extent）、页（page）：