深入理解 MySQL InnoDB从表空间、页到 Redo Log、Binlog 和慢查询日志深入理解 MySQL InnoDB从表空间、页到 Redo Log、Binlog 和慢查询日志前言一、InnoDB 的整体存储结构二、InnoDB 逻辑存储结构1. Tablespace表空间三、Segment段四、Extent区五、PageInnoDB 最重要的存储单位1. 为什么 Page 很重要2. 常见 Page 类型六、Row最终的数据记录七、InnoDB 的物理文件八、数据文件ibdata 与 .ibd九、Redo Log保证事务持久性的关键日志1. 为什么需要 Redo Log十、Redo Log 和 Binlog 到底有什么区别十一、Undo保存数据修改前的信息1. 事务回滚2. MVCC十二、MySQL 配置文件十三、常见 MySQL 配置参数动态参数与静态参数十四、Error Log排查 MySQL 故障的第一现场十五、BinlogMySQL 数据恢复和复制的核心日志十六、Binlog 有什么作用1. 主从复制2. 数据恢复十七、Binlog 的三种格式1. STATEMENT2. ROW3. MIXED十八、如何查看 Binlog 是否开启十九、Slow Query Log定位慢 SQL 的重要工具1. 临时开启慢查询日志2. 如何验证慢查询日志二十、mysqldumpslow快速分析慢日志二十一、General Log记录几乎所有数据库操作二十二、Relay Log主从复制中的中继日志二十三、PID 文件二十四、Socket 文件二十五、表结构元数据发生了什么变化二十六、MySQL 8.0 学习时一定要注意“版本问题”二十七、把所有核心日志放在一起理解二十八、InnoDB 存储体系全景图总结深入理解 MySQL InnoDB从表空间、页到 Redo Log、Binlog 和慢查询日志前言InnoDB 是 MySQL 最常用、也是默认的事务型存储引擎。平时写 SQL 时我们看到的通常只是数据库、表、字段和索引例如CREATETABLEuser(idBIGINTPRIMARYKEY,nameVARCHAR(50));但一条数据真正落到磁盘后并不是简单地“保存到一个表文件里”。在 InnoDB 内部数据会经过一套完整的存储体系表空间 ↓ 段 ↓ 区 ↓ 页 ↓ 行与此同时MySQL 还会维护多种文件与日志.ibd 数据文件 redo log undo log binlog error log slow query log general log relay log PID 文件 Socket 文件这些组件共同完成数据存储、事务恢复、主从复制、故障排查以及 SQL 性能分析。本文就从底层存储结构开始系统梳理 InnoDB 的核心组成。一、InnoDB 的整体存储结构InnoDB 的存储结构可以分成两个角度理解InnoDB ├── 逻辑存储结构 │ ├── Tablespace │ ├── Segment │ ├── Extent │ ├── Page │ └── Row │ └── 物理存储结构 ├── 数据文件 ├── Redo Log ├── Undo ├── 配置文件 ├── 各类运行日志 └── 其他辅助文件逻辑存储结构描述的是InnoDB 如何组织和管理数据。物理存储结构描述的则是这些数据最终以什么文件形式存在磁盘上。理解 InnoDB最好先从逻辑层开始。二、InnoDB 逻辑存储结构1. Tablespace表空间表空间可以理解为 InnoDB 逻辑存储结构中的最高层。InnoDB 的各种数据最终都存储在不同类型的表空间中。早期 InnoDB 经常使用共享系统表空间例如ibdata1可以在 MySQL 数据目录中看到类似文件ls-lh/usr/local/mysql/data/如果使用独立表空间则每张 InnoDB 表通常拥有自己的.ibd文件。与之相关的重要参数是SHOWVARIABLESLIKEinnodb_file_per_table;常见结果innodb_file_per_table ON开启独立表空间之后一张表的数据和索引可以保存在自己的.ibd文件中。例如demo/ ├── user.ibd ├── orders.ibd └── product.ibd不过要注意独立表空间并不意味着 InnoDB 的所有数据都会进入.ibd文件。例如系统级信息、Undo、部分内部结构等可能存储在其他专用表空间或系统区域中。三、Segment段表空间内部继续划分为 Segment也就是“段”。典型的 Segment 包括数据段 索引段 回滚段对于 InnoDB 来说尤其值得注意的一点是InnoDB 是索引组织表Index Organized Table。也就是说InnoDB 表中的数据本身就是按照索引结构组织的。对于聚簇索引而言索引 ≈ 数据组织结构因此不能完全把“索引文件”和“数据文件”理解成两套互不相关的东西。InnoDB 的主键索引叶子节点本身就保存了完整的行数据。四、Extent区Segment 继续向下划分就是 Extent也就是“区”。Extent 是由一组连续的 Page 组成的空间分配单位。在默认 16KB Page 的情况下一个 Extent 通常包含64 个 Page因此64 × 16KB 1024KB 1MB也就是说在典型配置下1 Extent 1MB可以理解为Tablespace ↓ Segment ↓ Extent约 1MB ↓ Page为什么不直接一页一页申请空间因为如果数据库频繁向操作系统申请极小的存储空间会增加管理成本。采用 Extent可以一次申请一组连续页面有利于提高空间管理和顺序访问效率。五、PageInnoDB 最重要的存储单位如果只记住一个概念那么一定要记住Page 是 InnoDB 最基本、最核心的磁盘存储单位。默认情况下InnoDB Page 大小通常为16KB可以查看SHOWVARIABLESLIKEinnodb_page_size;典型结果innodb_page_size 1638416384 Byte 正好是16KB1. 为什么 Page 很重要当 MySQL 查询一条记录时并不是只从磁盘读取那几十个字节的数据。磁盘和内存之间的数据交换通常是以 Page 为基本单位进行的。简单理解磁盘 ↓ 16KB Page ↓ Buffer Pool ↓ SQL 使用数据所以在分析 MySQL索引Buffer Pool随机 IO顺序 IO页分裂页命中率这些问题时Page 都是基础概念。2. 常见 Page 类型InnoDB 中并不是所有 Page 都用来存放普通数据。常见页面包括数据页 Undo 页 系统页 事务系统页 插入缓冲相关页面 大对象页面 压缩大对象页面其中实际开发中最常接触的是BTree 数据页InnoDB 索引树中的节点就是由一个个 Page 构成的。六、Row最终的数据记录Page 再往下就是 Row也就是行。InnoDB 是一个Row-Oriented Storage Engine即面向行的存储引擎。例如INSERTINTOuserVALUES(1,Tom,18);最终数据会以行记录的形式存放在数据页中。从宏观到微观可以形成完整关系Tablespace ↓ Segment ↓ Extent ↓ Page ↓ Row这条关系是理解 InnoDB 存储结构的核心。七、InnoDB 的物理文件理解完逻辑结构再来看数据真正落到操作系统后会出现哪些文件。八、数据文件ibdata 与 .ibdInnoDB 最直接的数据文件主要可以分为系统表空间文件 独立表空间文件传统的系统表空间文件常见ibdata1独立表空间文件则通常是表名.ibd例如demo/ ├── user.ibd ├── orders.ibd └── goods.ibd在开启innodb_file_per_table之后每张 InnoDB 表通常会建立自己的独立表空间文件。这使得单表空间管理更加灵活。九、Redo Log保证事务持久性的关键日志Redo Log 是理解 InnoDB 必须掌握的日志。它属于InnoDB 存储引擎层核心目标是保证数据库发生异常宕机之后已经提交或需要恢复的修改能够重新恢复出来。1. 为什么需要 Redo Log假设执行UPDATEaccountSETmoneymoney-100WHEREid1;如果每次事务提交都必须立刻把所有修改过的数据页随机写入磁盘那么性能会非常差。因为数据页可能分布在磁盘不同位置。InnoDB 会利用 Redo Log将随机的数据页修改转变为更适合持久化的日志写入。可以简单理解成修改数据 ↓ Buffer Pool 中的 Page 被修改 ↓ 产生 Redo ↓ Redo 持久化 ↓ 脏页之后再刷入数据文件如果数据库突然宕机数据文件可能还没完全写入但只要 Redo 中记录了必要的修改信息就可以在数据库重新启动时进行恢复。十、Redo Log 和 Binlog 到底有什么区别这是 MySQL 面试中非常高频的问题。虽然两者都叫“日志”但完全不是一回事。可以从几个维度理解。对比项Redo LogBinlog所属层次InnoDB 存储引擎层MySQL Server 层主要用途崩溃恢复复制、数据恢复内容特点偏物理变化逻辑事件/行变化写入方式持续写入按事务记录使用方式循环使用的日志空间机制持续生成新的日志文件典型场景Crash Recovery主从复制、时间点恢复可以用一句话记忆Redo Log保证数据库自己“摔倒还能爬起来” Binlog记录数据库“做过什么”十一、Undo保存数据修改前的信息与 Redo 对应的另一个重要概念就是 Undo。Redo 更关注如何把修改重新做一遍Undo 则更关注如何获得修改前的数据版本当一条记录发生修改时InnoDB 会产生相应的 Undo 信息。Undo 在两个场景中非常重要事务回滚 MVCC1. 事务回滚例如BEGIN;UPDATEaccountSETmoney0WHEREid1;ROLLBACK;执行ROLLBACK后需要将数据恢复到修改之前的状态。这时就需要借助 Undo 信息。2. MVCC当一个事务修改某条记录时另一个事务可能仍然需要读取它之前的版本。这就是Multi-Version Concurrency Control MVCCInnoDB 可以借助 Undo 中保存的历史版本构造一致性读需要的数据。因此Redo → 重做 Undo → 撤销 / 历史版本两者的作用完全不同。十二、MySQL 配置文件MySQL 启动时需要读取配置文件。在 Linux 环境下常见配置文件位置可能包括/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf可以使用mysql--help|grepmy.cnf查看配置文件搜索路径。如果需要明确指定某个配置文件可以在启动时使用相应参数例如mysqld --defaults-file/etc/my3306.cnf十三、常见 MySQL 配置参数MySQL 配置通常分为服务端和客户端。例如服务端[mysqld] port3306 basedir/usr/local/mysql datadir/usr/local/mysql/data客户端[client] port3306 default-character-setutf8mb4常见参数包括port basedir datadir socket pid-file character-set-server lower_case_table_names default-storage-engine log-error动态参数与静态参数MySQL 参数还可以从是否支持在线修改的角度分类。一部分参数可以动态修改例如SETGLOBAL参数名值;或者SETSESSION参数名值;两者区别是GLOBAL ↓ 影响之后建立的连接或全局环境 SESSION ↓ 只影响当前连接而部分静态参数通常需要修改配置文件 重启 MySQL才能生效。十四、Error Log排查 MySQL 故障的第一现场Error Log即错误日志。它会记录 MySQL启动 运行 异常 关闭 故障等过程中产生的重要信息。可以查看相关配置SHOWVARIABLESLIKElog_error;当出现MySQL 启动失败 表空间文件丢失 权限错误 配置错误 InnoDB 恢复异常这类问题时第一个应该检查的通常就是 Error Log。因此实际运维时可以形成一个习惯MySQL 出现异常先查错误日志。十五、BinlogMySQL 数据恢复和复制的核心日志Binlog 全称Binary Log也就是二进制日志。它由 MySQL Server 层产生。Binlog 主要记录对数据库数据造成修改的事件。例如INSERTUPDATEDELETECREATETABLEALTERTABLE而类似SELECTSHOW通常不会作为普通数据修改事件记录进去。十六、Binlog 有什么作用Binlog 最重要的两个作用是1. 主从复制 2. 数据恢复1. 主从复制主库执行UPDATEuserSETnameTomWHEREid1;之后变化被记录到 Binlog。从库获取主库 Binlog再重放其中的事件Master ↓ Binlog ↓ Replica ↓ Relay Log ↓ 重放最终实现数据同步。2. 数据恢复如果误删了数据DELETEFROMuser;只要备份和 Binlog 策略合理就可以通过全量备份 Binlog实现时间点恢复。这也是生产数据库必须认真规划 Binlog 的原因之一。十七、Binlog 的三种格式Binlog 经典的三种日志格式分别为STATEMENT ROW MIXED1. STATEMENTSTATEMENT 记录执行过的 SQL。例如UPDATEuserSETmoneymoney100WHEREid1;Binlog 中主要记录这条 SQL。优点日志量相对较小缺点是某些依赖上下文、随机函数或者环境差异的 SQL 可能产生复制一致性问题。2. ROWROW 模式重点记录哪些行发生了怎样的变化。它不依赖从库重新“理解”原 SQL 的业务语义。优点复制更加可靠 数据一致性更好缺点大量数据更新时 Binlog 可能明显增大例如UPDATEuserSETstatus1;如果修改 100 万行ROW 模式需要记录大量行变化。3. MIXEDMIXED 可以理解为STATEMENT ROWMySQL 根据具体 SQL 情况选择适合的日志形式。十八、如何查看 Binlog 是否开启可以使用SHOWVARIABLESLIKE%log_bin%;重点关注log_bin log_bin_basename log_bin_index含义分别可以理解为log_bin 是否启用 Binlog log_bin_basename Binlog 文件基础路径 log_bin_index Binlog 索引文件查看当前 Binlog 状态时也可以使用对应版本支持的状态命令。查看具体日志事件例如SHOWBINLOG EVENTSINbinlog.000010;在服务器命令行还可以利用mysqlbinlog binlog.000010解析 Binlog。十九、Slow Query Log定位慢 SQL 的重要工具对于数据库性能优化来说慢查询日志非常重要。它会记录执行时间超过指定阈值的 SQL。首先查看SHOWVARIABLESLIKE%slow_query%;常见变量包括slow_query_log slow_query_log_file查看慢查询阈值SHOWVARIABLESLIKElong_query_time;例如long_query_time 2意味着执行时间达到相应条件的 SQL 可以被纳入慢查询分析范围。1. 临时开启慢查询日志例如SETGLOBALslow_query_logON;调整慢查询阈值SETGLOBALlong_query_time2;如果希望长期生效通常应该写入 MySQL 配置文件。例如[mysqld] slow_query_logON slow_query_log_file/usr/local/mysql/data/mysql-slow.log long_query_time2修改后按照实际环境使配置生效。2. 如何验证慢查询日志可以人为执行一条耗时 SQL例如SELECTSLEEP(3);然后检查慢日志。日志中通常能够看到执行时间 用户 主机 Query_time Lock_time Rows_sent Rows_examined SQL这些数据对 SQL 性能诊断非常有帮助。例如Query_time 很大 Rows_examined 非常大 Rows_sent 很小往往意味着数据库扫描了大量数据但真正返回的数据非常少。这种 SQL 就值得重点检查索引设计和执行计划。二十、mysqldumpslow快速分析慢日志当慢查询日志非常大时人工查看效率很低。可以使用mysqldumpslow mysql-slow.log进行初步聚合分析。在生产环境中慢查询日志还经常会结合pt-query-digest Performance Schema EXPLAIN EXPLAIN ANALYZE进行进一步分析。完整的 SQL 优化链路通常是二十一、General Log记录几乎所有数据库操作General Log 又叫全量日志 / 通用查询日志它可以记录连接到 MySQL 后执行的大量操作包括SELECTSHOWINSERTUPDATEDELETE查看配置SHOWVARIABLESLIKE%general_log%;开启SETGLOBALgeneral_logON;General Log 在问题诊断时很有价值但它的日志量可能非常大。因此生产环境通常不建议长时间无目的开启 General Log。否则容易产生大量磁盘 IO 日志快速膨胀 额外性能开销更适合临时排查问题。二十二、Relay Log主从复制中的中继日志Relay Log 主要出现在 MySQL 复制体系中的从库一侧。传统复制流程可以抽象成因此Binlog主库产生 Relay Log从库复制过程中使用两者不能混为一谈。可以查看与 Relay Log 相关的参数例如SHOWVARIABLESLIKE%relay%;其中可能包含relay_log relay_log_index relay_log_purge relay_log_recovery等配置。二十三、PID 文件MySQL Server 启动之后本质上也是操作系统中的一个进程。系统需要记录mysqld 的进程 ID这通常通过 PID 文件实现。可以查看SHOWVARIABLESLIKE%pid%;例如pid_file对应文件中通常保存一个数字44764这个数字就是 MySQL Server 对应进程的 PID。PID 文件对于服务管理 进程控制 启动停止 状态检查都有一定作用。二十四、Socket 文件Linux/Unix 系统下客户端和本机 MySQL Server 之间除了 TCP/IP还可以通过 Unix Socket 通信。查看 Socket 文件位置SHOWVARIABLESLIKEsocket;常见值类似/tmp/mysql.sock例如本地执行mysql-uroot-p某些情况下客户端默认就是通过Unix Socket连接 MySQL。因此当遇到Cant connect to local MySQL server through socket这一类错误时就应该检查MySQL 是否启动 socket 文件是否存在 客户端和服务端 socket 路径是否一致 文件权限是否正常二十五、表结构元数据发生了什么变化在较早版本的 MySQL 中表结构信息会和.frm文件联系在一起。例如过去一张表可能对应table.frm table.ibd其中.frm用于保存表结构定义。但进入 MySQL 8.0 后元数据管理方式发生了重大变化。MySQL 8 使用事务型数据字典将大量数据库对象的元数据信息统一管理起来不再继续依赖传统.frm文件作为普通表定义的核心存储方式。因此学习 MySQL 文件结构时必须注意版本差异。二十六、MySQL 8.0 学习时一定要注意“版本问题”MySQL 的底层实现一直在演进。很多早期资料中的文件名 默认值 系统变量 日志管理方式 数据字典结构 复制术语在新的 MySQL 版本中都可能发生变化。尤其涉及Redo Log 参数 .frm 文件 复制相关命令 默认认证插件 默认字符集时更应该确认自己当前使用的版本。可以先执行SELECTVERSION();再针对当前版本查看SHOWVARIABLES;避免直接照搬其他版本的配置。对于生产环境更应该以实际版本的官方文档和SHOW VARIABLES结果为准。二十七、把所有核心日志放在一起理解最后将 MySQL 中几个最容易混淆的日志统一梳理一下。日志主要作用典型使用场景Redo Log保证事务持久性、崩溃恢复MySQL 异常宕机恢复Undo回滚、历史版本MVCC、事务回滚Binlog记录数据变更事件主从复制、数据恢复Relay Log保存从主库获取的复制事件从库复制Error Log记录启动运行错误故障排查Slow Query Log记录慢 SQLSQL 性能优化General Log记录数据库操作临时问题诊断如果觉得日志很多可以这样记Redo → 数据库崩了怎么恢复 Undo → 数据改了怎么撤回、怎么看旧版本 Binlog → 数据库曾经做过哪些修改 Relay Log → 从库准备执行哪些复制事件 Error Log → MySQL 到底哪里报错了 Slow Log → 到底是哪条 SQL 慢 General Log → MySQL 最近执行了什么二十八、InnoDB 存储体系全景图将本文内容串起来可以形成这样一个整体认识同时外围还存在Error Log Slow Query Log General Log Relay Log PID Socket 配置文件共同构成一个完整的 MySQL 运行环境。总结理解 InnoDB不能只停留在SELECTINSERTUPDATEDELETE更重要的是理解 SQL 背后发生了什么。从存储层面来看Tablespace → Segment → Extent → Page → Row其中Page 是 InnoDB 最核心的存储管理单位之一。从事务机制来看Redo Log → 保证崩溃恢复和持久性 Undo → 支持事务回滚和 MVCC从 MySQL Server 层来看Binlog → 支持复制和数据恢复从运维角度来看Error Log → 排查异常 Slow Query Log → 定位慢 SQL General Log → 临时追踪数据库操作 Relay Log → 支撑复制真正理解这些组件之后再学习Buffer Pool BTree 索引 MVCC 事务隔离 脏页刷新 Checkpoint 主从复制 SQL 优化就会容易很多。因为它们本质上都建立在 InnoDB 的存储结构和日志体系之上。若有转载请标明出处https://blog.csdn.net/CharlesYuangc/article/details/164188642
《深入理解 MySQL InnoDB：表空间、Page、Redo Log、Undo、Binlog 与慢查询日志一次讲清楚》
深入理解 MySQL InnoDB从表空间、页到 Redo Log、Binlog 和慢查询日志深入理解 MySQL InnoDB从表空间、页到 Redo Log、Binlog 和慢查询日志前言一、InnoDB 的整体存储结构二、InnoDB 逻辑存储结构1. Tablespace表空间三、Segment段四、Extent区五、PageInnoDB 最重要的存储单位1. 为什么 Page 很重要2. 常见 Page 类型六、Row最终的数据记录七、InnoDB 的物理文件八、数据文件ibdata 与 .ibd九、Redo Log保证事务持久性的关键日志1. 为什么需要 Redo Log十、Redo Log 和 Binlog 到底有什么区别十一、Undo保存数据修改前的信息1. 事务回滚2. MVCC十二、MySQL 配置文件十三、常见 MySQL 配置参数动态参数与静态参数十四、Error Log排查 MySQL 故障的第一现场十五、BinlogMySQL 数据恢复和复制的核心日志十六、Binlog 有什么作用1. 主从复制2. 数据恢复十七、Binlog 的三种格式1. STATEMENT2. ROW3. MIXED十八、如何查看 Binlog 是否开启十九、Slow Query Log定位慢 SQL 的重要工具1. 临时开启慢查询日志2. 如何验证慢查询日志二十、mysqldumpslow快速分析慢日志二十一、General Log记录几乎所有数据库操作二十二、Relay Log主从复制中的中继日志二十三、PID 文件二十四、Socket 文件二十五、表结构元数据发生了什么变化二十六、MySQL 8.0 学习时一定要注意“版本问题”二十七、把所有核心日志放在一起理解二十八、InnoDB 存储体系全景图总结深入理解 MySQL InnoDB从表空间、页到 Redo Log、Binlog 和慢查询日志前言InnoDB 是 MySQL 最常用、也是默认的事务型存储引擎。平时写 SQL 时我们看到的通常只是数据库、表、字段和索引例如CREATETABLEuser(idBIGINTPRIMARYKEY,nameVARCHAR(50));但一条数据真正落到磁盘后并不是简单地“保存到一个表文件里”。在 InnoDB 内部数据会经过一套完整的存储体系表空间 ↓ 段 ↓ 区 ↓ 页 ↓ 行与此同时MySQL 还会维护多种文件与日志.ibd 数据文件 redo log undo log binlog error log slow query log general log relay log PID 文件 Socket 文件这些组件共同完成数据存储、事务恢复、主从复制、故障排查以及 SQL 性能分析。本文就从底层存储结构开始系统梳理 InnoDB 的核心组成。一、InnoDB 的整体存储结构InnoDB 的存储结构可以分成两个角度理解InnoDB ├── 逻辑存储结构 │ ├── Tablespace │ ├── Segment │ ├── Extent │ ├── Page │ └── Row │ └── 物理存储结构 ├── 数据文件 ├── Redo Log ├── Undo ├── 配置文件 ├── 各类运行日志 └── 其他辅助文件逻辑存储结构描述的是InnoDB 如何组织和管理数据。物理存储结构描述的则是这些数据最终以什么文件形式存在磁盘上。理解 InnoDB最好先从逻辑层开始。二、InnoDB 逻辑存储结构1. Tablespace表空间表空间可以理解为 InnoDB 逻辑存储结构中的最高层。InnoDB 的各种数据最终都存储在不同类型的表空间中。早期 InnoDB 经常使用共享系统表空间例如ibdata1可以在 MySQL 数据目录中看到类似文件ls-lh/usr/local/mysql/data/如果使用独立表空间则每张 InnoDB 表通常拥有自己的.ibd文件。与之相关的重要参数是SHOWVARIABLESLIKEinnodb_file_per_table;常见结果innodb_file_per_table ON开启独立表空间之后一张表的数据和索引可以保存在自己的.ibd文件中。例如demo/ ├── user.ibd ├── orders.ibd └── product.ibd不过要注意独立表空间并不意味着 InnoDB 的所有数据都会进入.ibd文件。例如系统级信息、Undo、部分内部结构等可能存储在其他专用表空间或系统区域中。三、Segment段表空间内部继续划分为 Segment也就是“段”。典型的 Segment 包括数据段 索引段 回滚段对于 InnoDB 来说尤其值得注意的一点是InnoDB 是索引组织表Index Organized Table。也就是说InnoDB 表中的数据本身就是按照索引结构组织的。对于聚簇索引而言索引 ≈ 数据组织结构因此不能完全把“索引文件”和“数据文件”理解成两套互不相关的东西。InnoDB 的主键索引叶子节点本身就保存了完整的行数据。四、Extent区Segment 继续向下划分就是 Extent也就是“区”。Extent 是由一组连续的 Page 组成的空间分配单位。在默认 16KB Page 的情况下一个 Extent 通常包含64 个 Page因此64 × 16KB 1024KB 1MB也就是说在典型配置下1 Extent 1MB可以理解为Tablespace ↓ Segment ↓ Extent约 1MB ↓ Page为什么不直接一页一页申请空间因为如果数据库频繁向操作系统申请极小的存储空间会增加管理成本。采用 Extent可以一次申请一组连续页面有利于提高空间管理和顺序访问效率。五、PageInnoDB 最重要的存储单位如果只记住一个概念那么一定要记住Page 是 InnoDB 最基本、最核心的磁盘存储单位。默认情况下InnoDB Page 大小通常为16KB可以查看SHOWVARIABLESLIKEinnodb_page_size;典型结果innodb_page_size 1638416384 Byte 正好是16KB1. 为什么 Page 很重要当 MySQL 查询一条记录时并不是只从磁盘读取那几十个字节的数据。磁盘和内存之间的数据交换通常是以 Page 为基本单位进行的。简单理解磁盘 ↓ 16KB Page ↓ Buffer Pool ↓ SQL 使用数据所以在分析 MySQL索引Buffer Pool随机 IO顺序 IO页分裂页命中率这些问题时Page 都是基础概念。2. 常见 Page 类型InnoDB 中并不是所有 Page 都用来存放普通数据。常见页面包括数据页 Undo 页 系统页 事务系统页 插入缓冲相关页面 大对象页面 压缩大对象页面其中实际开发中最常接触的是BTree 数据页InnoDB 索引树中的节点就是由一个个 Page 构成的。六、Row最终的数据记录Page 再往下就是 Row也就是行。InnoDB 是一个Row-Oriented Storage Engine即面向行的存储引擎。例如INSERTINTOuserVALUES(1,Tom,18);最终数据会以行记录的形式存放在数据页中。从宏观到微观可以形成完整关系Tablespace ↓ Segment ↓ Extent ↓ Page ↓ Row这条关系是理解 InnoDB 存储结构的核心。七、InnoDB 的物理文件理解完逻辑结构再来看数据真正落到操作系统后会出现哪些文件。八、数据文件ibdata 与 .ibdInnoDB 最直接的数据文件主要可以分为系统表空间文件 独立表空间文件传统的系统表空间文件常见ibdata1独立表空间文件则通常是表名.ibd例如demo/ ├── user.ibd ├── orders.ibd └── goods.ibd在开启innodb_file_per_table之后每张 InnoDB 表通常会建立自己的独立表空间文件。这使得单表空间管理更加灵活。九、Redo Log保证事务持久性的关键日志Redo Log 是理解 InnoDB 必须掌握的日志。它属于InnoDB 存储引擎层核心目标是保证数据库发生异常宕机之后已经提交或需要恢复的修改能够重新恢复出来。1. 为什么需要 Redo Log假设执行UPDATEaccountSETmoneymoney-100WHEREid1;如果每次事务提交都必须立刻把所有修改过的数据页随机写入磁盘那么性能会非常差。因为数据页可能分布在磁盘不同位置。InnoDB 会利用 Redo Log将随机的数据页修改转变为更适合持久化的日志写入。可以简单理解成修改数据 ↓ Buffer Pool 中的 Page 被修改 ↓ 产生 Redo ↓ Redo 持久化 ↓ 脏页之后再刷入数据文件如果数据库突然宕机数据文件可能还没完全写入但只要 Redo 中记录了必要的修改信息就可以在数据库重新启动时进行恢复。十、Redo Log 和 Binlog 到底有什么区别这是 MySQL 面试中非常高频的问题。虽然两者都叫“日志”但完全不是一回事。可以从几个维度理解。对比项Redo LogBinlog所属层次InnoDB 存储引擎层MySQL Server 层主要用途崩溃恢复复制、数据恢复内容特点偏物理变化逻辑事件/行变化写入方式持续写入按事务记录使用方式循环使用的日志空间机制持续生成新的日志文件典型场景Crash Recovery主从复制、时间点恢复可以用一句话记忆Redo Log保证数据库自己“摔倒还能爬起来” Binlog记录数据库“做过什么”十一、Undo保存数据修改前的信息与 Redo 对应的另一个重要概念就是 Undo。Redo 更关注如何把修改重新做一遍Undo 则更关注如何获得修改前的数据版本当一条记录发生修改时InnoDB 会产生相应的 Undo 信息。Undo 在两个场景中非常重要事务回滚 MVCC1. 事务回滚例如BEGIN;UPDATEaccountSETmoney0WHEREid1;ROLLBACK;执行ROLLBACK后需要将数据恢复到修改之前的状态。这时就需要借助 Undo 信息。2. MVCC当一个事务修改某条记录时另一个事务可能仍然需要读取它之前的版本。这就是Multi-Version Concurrency Control MVCCInnoDB 可以借助 Undo 中保存的历史版本构造一致性读需要的数据。因此Redo → 重做 Undo → 撤销 / 历史版本两者的作用完全不同。十二、MySQL 配置文件MySQL 启动时需要读取配置文件。在 Linux 环境下常见配置文件位置可能包括/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf可以使用mysql--help|grepmy.cnf查看配置文件搜索路径。如果需要明确指定某个配置文件可以在启动时使用相应参数例如mysqld --defaults-file/etc/my3306.cnf十三、常见 MySQL 配置参数MySQL 配置通常分为服务端和客户端。例如服务端[mysqld] port3306 basedir/usr/local/mysql datadir/usr/local/mysql/data客户端[client] port3306 default-character-setutf8mb4常见参数包括port basedir datadir socket pid-file character-set-server lower_case_table_names default-storage-engine log-error动态参数与静态参数MySQL 参数还可以从是否支持在线修改的角度分类。一部分参数可以动态修改例如SETGLOBAL参数名值;或者SETSESSION参数名值;两者区别是GLOBAL ↓ 影响之后建立的连接或全局环境 SESSION ↓ 只影响当前连接而部分静态参数通常需要修改配置文件 重启 MySQL才能生效。十四、Error Log排查 MySQL 故障的第一现场Error Log即错误日志。它会记录 MySQL启动 运行 异常 关闭 故障等过程中产生的重要信息。可以查看相关配置SHOWVARIABLESLIKElog_error;当出现MySQL 启动失败 表空间文件丢失 权限错误 配置错误 InnoDB 恢复异常这类问题时第一个应该检查的通常就是 Error Log。因此实际运维时可以形成一个习惯MySQL 出现异常先查错误日志。十五、BinlogMySQL 数据恢复和复制的核心日志Binlog 全称Binary Log也就是二进制日志。它由 MySQL Server 层产生。Binlog 主要记录对数据库数据造成修改的事件。例如INSERTUPDATEDELETECREATETABLEALTERTABLE而类似SELECTSHOW通常不会作为普通数据修改事件记录进去。十六、Binlog 有什么作用Binlog 最重要的两个作用是1. 主从复制 2. 数据恢复1. 主从复制主库执行UPDATEuserSETnameTomWHEREid1;之后变化被记录到 Binlog。从库获取主库 Binlog再重放其中的事件Master ↓ Binlog ↓ Replica ↓ Relay Log ↓ 重放最终实现数据同步。2. 数据恢复如果误删了数据DELETEFROMuser;只要备份和 Binlog 策略合理就可以通过全量备份 Binlog实现时间点恢复。这也是生产数据库必须认真规划 Binlog 的原因之一。十七、Binlog 的三种格式Binlog 经典的三种日志格式分别为STATEMENT ROW MIXED1. STATEMENTSTATEMENT 记录执行过的 SQL。例如UPDATEuserSETmoneymoney100WHEREid1;Binlog 中主要记录这条 SQL。优点日志量相对较小缺点是某些依赖上下文、随机函数或者环境差异的 SQL 可能产生复制一致性问题。2. ROWROW 模式重点记录哪些行发生了怎样的变化。它不依赖从库重新“理解”原 SQL 的业务语义。优点复制更加可靠 数据一致性更好缺点大量数据更新时 Binlog 可能明显增大例如UPDATEuserSETstatus1;如果修改 100 万行ROW 模式需要记录大量行变化。3. MIXEDMIXED 可以理解为STATEMENT ROWMySQL 根据具体 SQL 情况选择适合的日志形式。十八、如何查看 Binlog 是否开启可以使用SHOWVARIABLESLIKE%log_bin%;重点关注log_bin log_bin_basename log_bin_index含义分别可以理解为log_bin 是否启用 Binlog log_bin_basename Binlog 文件基础路径 log_bin_index Binlog 索引文件查看当前 Binlog 状态时也可以使用对应版本支持的状态命令。查看具体日志事件例如SHOWBINLOG EVENTSINbinlog.000010;在服务器命令行还可以利用mysqlbinlog binlog.000010解析 Binlog。十九、Slow Query Log定位慢 SQL 的重要工具对于数据库性能优化来说慢查询日志非常重要。它会记录执行时间超过指定阈值的 SQL。首先查看SHOWVARIABLESLIKE%slow_query%;常见变量包括slow_query_log slow_query_log_file查看慢查询阈值SHOWVARIABLESLIKElong_query_time;例如long_query_time 2意味着执行时间达到相应条件的 SQL 可以被纳入慢查询分析范围。1. 临时开启慢查询日志例如SETGLOBALslow_query_logON;调整慢查询阈值SETGLOBALlong_query_time2;如果希望长期生效通常应该写入 MySQL 配置文件。例如[mysqld] slow_query_logON slow_query_log_file/usr/local/mysql/data/mysql-slow.log long_query_time2修改后按照实际环境使配置生效。2. 如何验证慢查询日志可以人为执行一条耗时 SQL例如SELECTSLEEP(3);然后检查慢日志。日志中通常能够看到执行时间 用户 主机 Query_time Lock_time Rows_sent Rows_examined SQL这些数据对 SQL 性能诊断非常有帮助。例如Query_time 很大 Rows_examined 非常大 Rows_sent 很小往往意味着数据库扫描了大量数据但真正返回的数据非常少。这种 SQL 就值得重点检查索引设计和执行计划。二十、mysqldumpslow快速分析慢日志当慢查询日志非常大时人工查看效率很低。可以使用mysqldumpslow mysql-slow.log进行初步聚合分析。在生产环境中慢查询日志还经常会结合pt-query-digest Performance Schema EXPLAIN EXPLAIN ANALYZE进行进一步分析。完整的 SQL 优化链路通常是二十一、General Log记录几乎所有数据库操作General Log 又叫全量日志 / 通用查询日志它可以记录连接到 MySQL 后执行的大量操作包括SELECTSHOWINSERTUPDATEDELETE查看配置SHOWVARIABLESLIKE%general_log%;开启SETGLOBALgeneral_logON;General Log 在问题诊断时很有价值但它的日志量可能非常大。因此生产环境通常不建议长时间无目的开启 General Log。否则容易产生大量磁盘 IO 日志快速膨胀 额外性能开销更适合临时排查问题。二十二、Relay Log主从复制中的中继日志Relay Log 主要出现在 MySQL 复制体系中的从库一侧。传统复制流程可以抽象成因此Binlog主库产生 Relay Log从库复制过程中使用两者不能混为一谈。可以查看与 Relay Log 相关的参数例如SHOWVARIABLESLIKE%relay%;其中可能包含relay_log relay_log_index relay_log_purge relay_log_recovery等配置。二十三、PID 文件MySQL Server 启动之后本质上也是操作系统中的一个进程。系统需要记录mysqld 的进程 ID这通常通过 PID 文件实现。可以查看SHOWVARIABLESLIKE%pid%;例如pid_file对应文件中通常保存一个数字44764这个数字就是 MySQL Server 对应进程的 PID。PID 文件对于服务管理 进程控制 启动停止 状态检查都有一定作用。二十四、Socket 文件Linux/Unix 系统下客户端和本机 MySQL Server 之间除了 TCP/IP还可以通过 Unix Socket 通信。查看 Socket 文件位置SHOWVARIABLESLIKEsocket;常见值类似/tmp/mysql.sock例如本地执行mysql-uroot-p某些情况下客户端默认就是通过Unix Socket连接 MySQL。因此当遇到Cant connect to local MySQL server through socket这一类错误时就应该检查MySQL 是否启动 socket 文件是否存在 客户端和服务端 socket 路径是否一致 文件权限是否正常二十五、表结构元数据发生了什么变化在较早版本的 MySQL 中表结构信息会和.frm文件联系在一起。例如过去一张表可能对应table.frm table.ibd其中.frm用于保存表结构定义。但进入 MySQL 8.0 后元数据管理方式发生了重大变化。MySQL 8 使用事务型数据字典将大量数据库对象的元数据信息统一管理起来不再继续依赖传统.frm文件作为普通表定义的核心存储方式。因此学习 MySQL 文件结构时必须注意版本差异。二十六、MySQL 8.0 学习时一定要注意“版本问题”MySQL 的底层实现一直在演进。很多早期资料中的文件名 默认值 系统变量 日志管理方式 数据字典结构 复制术语在新的 MySQL 版本中都可能发生变化。尤其涉及Redo Log 参数 .frm 文件 复制相关命令 默认认证插件 默认字符集时更应该确认自己当前使用的版本。可以先执行SELECTVERSION();再针对当前版本查看SHOWVARIABLES;避免直接照搬其他版本的配置。对于生产环境更应该以实际版本的官方文档和SHOW VARIABLES结果为准。二十七、把所有核心日志放在一起理解最后将 MySQL 中几个最容易混淆的日志统一梳理一下。日志主要作用典型使用场景Redo Log保证事务持久性、崩溃恢复MySQL 异常宕机恢复Undo回滚、历史版本MVCC、事务回滚Binlog记录数据变更事件主从复制、数据恢复Relay Log保存从主库获取的复制事件从库复制Error Log记录启动运行错误故障排查Slow Query Log记录慢 SQLSQL 性能优化General Log记录数据库操作临时问题诊断如果觉得日志很多可以这样记Redo → 数据库崩了怎么恢复 Undo → 数据改了怎么撤回、怎么看旧版本 Binlog → 数据库曾经做过哪些修改 Relay Log → 从库准备执行哪些复制事件 Error Log → MySQL 到底哪里报错了 Slow Log → 到底是哪条 SQL 慢 General Log → MySQL 最近执行了什么二十八、InnoDB 存储体系全景图将本文内容串起来可以形成这样一个整体认识同时外围还存在Error Log Slow Query Log General Log Relay Log PID Socket 配置文件共同构成一个完整的 MySQL 运行环境。总结理解 InnoDB不能只停留在SELECTINSERTUPDATEDELETE更重要的是理解 SQL 背后发生了什么。从存储层面来看Tablespace → Segment → Extent → Page → Row其中Page 是 InnoDB 最核心的存储管理单位之一。从事务机制来看Redo Log → 保证崩溃恢复和持久性 Undo → 支持事务回滚和 MVCC从 MySQL Server 层来看Binlog → 支持复制和数据恢复从运维角度来看Error Log → 排查异常 Slow Query Log → 定位慢 SQL General Log → 临时追踪数据库操作 Relay Log → 支撑复制真正理解这些组件之后再学习Buffer Pool BTree 索引 MVCC 事务隔离 脏页刷新 Checkpoint 主从复制 SQL 优化就会容易很多。因为它们本质上都建立在 InnoDB 的存储结构和日志体系之上。若有转载请标明出处https://blog.csdn.net/CharlesYuangc/article/details/164188642