我们介绍了 MySQL InnoDB 存储引擎的具体架构,下面重点讲解InnoDB 存储引擎的内存和物理结构,如下图所示。
用户读取或者写入的最新数据都存储在 Buffer Pool 中,如果 Buffer Pool 中没有找到则会读取物理文件进行查找,之后存储到 Buffer Pool 中并返回给 MySQL Server。Buffer Pool 采用LRU 机制,具体的内存队列和刷新机制建议你课后学习了解下,这里不详细讲述。
Buffer Pool 决定了一个 SQL 执行的速度快慢,如果查询结果页都在内存中则返回结果速度很快,否则会产生物理读(磁盘读),返回结果时间变长,性能远不如存储在内存中。但我们又不能将所有数据页都存储到 Buffer Pool 中,比如物理 ibd 文件有 500GB,我们的机器不可能配置能容得下 500GB 数据页的内存,因为这样做成本很高而且也没必要。在单机单实例情况下,我们可以配置 Buffer Pool 为物理内存的 60%~80%,剩余内存用于 session 产生的 sort 和 join 等,以及运维管理使用。如果是单机多实例,所有实例的buffer pool总量也不要超过物理内存的80%。开始时我们可以根据经验设置一个 Buffer Pool 的经验值,比如 16GB,之后业务在 MySQL 运行一段时间后可以根据 show global status like '%buffer_pool_wait%' 的值来看是否需要调整 Buffer Pool 的大小。
Redo log 是一个循环复用的文件集,负责记录 InnoDB 中所有对 Buffer Pool的物理修改日志,当 Redo log文件空间中,检查点位置的 LSN 和最新写入的 LSN 差值(checkpoint_age)达到 Redo log 文件总空间的 75% 后,InnoDB 会进行异步刷新操作,直到降至 75% 以下,并释放 Redo log 的空间;当 checkpoint_age 达到文件总量大小的 90% 后,会触发同步刷新,此时 InnoDB 处于挂起状态无法操作。
这样我们就看到 Redo log 的大小直接影响了数据库的处理能力,如果设置太小会导致强行 checkpoint 操作频繁刷新脏页,那我们就需要将 Redo log 设置的大一些,5.6 版本之前 Redo log 总大小不能超过 3.8GB,5.7 版本之后放开了这个限制。那既然太小影响性能,是不是设置得越大越好呢,这个问题留给你课后自己思考。
事务提交时 log buffer 会刷新到 Redo log 文件中,具体刷新机制由参数控制,你可以课后学习并根据自身业务特点进行配置。
若参数 innodb_file_per_table=ON,则表示用户建表时采用用户独立表空间,即一个表对应一组物理文件,.frm 表定义文件和 .ibd 表数据文件。
当然若这个参数设置为 OFF,则表示用户建表存储在 ibdata 文件中,不建议采用共享表空间,这样会导致 ibdata 文件过大,而且当表删除后空间无法回收。独立表空间可以在用户删除大量数据后回收物理空间,执行一个 DDL 就可以将表空间的高水位降下来了。