page（逻辑）
- page应用于InnoDBb表空间包括：系统表空间、单表文件表空间、常规表空间；
- page_size默认16KB；足够大以容纳大多数行的数据，但足够小以最小化将不需要的数据传输到内存的性能开销;
表
表的物理结构
- .frm文件即包含MySQL表的元数据（例如表定义）的文件；
- 尽管每个InnoDB表都有一个 .frm文件，但是InnoDB 在系统表空间中维护其自己的表元数据；
表移动
- 可移动表空间
- 一个服务器实例复制到另一个服务器实例
- FLUSH TABLES ... FOR EXPORT
- innodb_file_per_table须设置为ON
- 复制数据文件（冷备份方法）
- 导出和导入（mysqldump）
索引
索引的物理结构
- InnoDB 中除空间GIS索引（是R树数据结构）外，其他索引均为B树结构；
聚簇索引和二级索引（辅助索引）
- 每个InnoDB 表都有一个特殊的索引，称为聚簇索引 ，通常称之为主键索引；除聚簇索引之外的所有索引都称为二级索引（辅助索引）；
- 如果您没有为表定义PRIMARY KEY，MySQL会在所有NOT NULL键列所在的位置找到第一个UNIQUE索引，并将其用作聚集索引；
- 如果表没有索引或没有合适的UNIQUE索引，则在InnoDB 内部生成一个隐藏的聚集索引
- 二级索引中的每个记录都包含该行的主键列以及为二级索引指定的列；如果主键较长，则辅助索引将使用更多空间，因此具有短的主键是有利的；
排序索引创建
- 第一阶段：将扫描聚簇索引，并生成索引条目并将其添加到排序缓冲区。当排序缓冲区已满时，将对条目进行排序并将其写到临时中间文件中。此过程也称为“运行”；
- 第二阶段，将一个或多个”运行”写入临时中间文件，对文件中的所有条目执行合并排序。
- 第三阶段，将已排序的条目插入 B-tree中。
表空间
系统表空间
- 系统表空间是InnoDB数据字典，双写缓冲区，更改缓冲区和撤消日志的存储区（ibdata1文件）；
单表文件表空间
- 该文件为每个表的表空间提供了一种更灵活的选择，其中，每个InnoDB表被存储在其自己的表空间的数据文件（.ibd文件），默认启用；
常规表空间
- 常规表空间是InnoDB 使用CREATE TABLESPACE语法创建的共享表空间；
撤销表空间
- 撤消表空间包含撤消日志，其中包含如何通过事务撤消对表数据更改的信息
- 撤消日志可以存储在一个或多个撤消表空间中。在默认配置中，撤消日志位于系统表空间；
临时表空间
- 在正常关闭或初始化中止时，将删除临时表空间，并在每次启动服务器时重新创建
双写缓冲区
[图片上传失败...(image-ceb221-1574387700883)]
- 脏页数据从缓冲池向磁盘写数据时，在写到指定磁盘位置前，会优先写到双写缓冲区；当写到双写缓冲区成功后，才会向磁盘指定位置写。
- 如果在页面写入过程中发生操作系统，存储子系统或mysqld进程崩溃，InnoDB以后可以在崩溃恢复期间从doublewrite缓冲区中找到该页面的副本
- 尽管数据总是被写入两次，但双写缓冲区并不需要两倍的I / O开销或两倍的I / O操作。只需对操作系统进行一次调用，就可以将数据作为一个较大的顺序块写入双写缓冲区本身。（解决partial page write问题）
- partial page write问题
[图片上传失败...(image-58f001-1574387700883)]
重做日志
- 重做日志是基于磁盘的数据结构，在崩溃恢复期间用于纠正不完整事务写入的数据。
- 以循环方式写入重做日志文件ib_logfile0和ib_logfile1，重做日志中的数据按照受影响的记录进行编码；此数据统称为重做。通过重做日志的数据传递以不断增加的LSN值表示。
撤销日志
- 撤消日志是单个读写事务的撤消记录的集合
- 撤消日志记录包含有关如何撤消事务对聚簇索引记录的最新更改的信息
- 如果另一个事务需要将原始数据视为一致读取操作的一部分，则将从撤消日志记录中检索未修改的数据
其他概念
-
脏页
- InnoDB缓冲池中已在内存中更新的页面，其中的更改尚未写入(刷新)数据文件。
-
净页
- InnoDB缓冲池中的一个页面，所有内存中的更改都被写入(刷新)数据文件。
-
二级制日志binLog
- 描述数据库更改（例如表创建操作或表数据更改）的“ 事件 ”
- 包含有关每个语句花费该更新数据多长时间的信息
- 二进制日志的执行顺序是在语句执行之后但释放任何锁之前
- 当使用基于行的二进制日志记录时，更新是作为行更改而不是SQL语句发送的
- 提供了要发送到从属服务器的数据更改的记录
- 某些数据恢复操作需要使用二进制日志
-
通用日志
- 当使用基于行的二进制日志记录时，更新是作为行更改而不是SQL语句发送的
- 常规查询日志是mysqld在做什么的常规记录
总结
让我们用sql的执行过程，把这些机制和流程串起来：
- 执行DML查询语句sql1,更改数据sql2,提交事务后再查询sql3；
- sql1将page存入缓冲池；
- sql2提交事务之前：
- 由于数据在缓冲池，不论数据是否更改二级索引，均不在缓冲更改区；
- 更新缓冲池中数据页（脏页），是否落磁盘取决于WAL规则（Write ahead redo log）；
- 更改page记录的日志在日志缓冲区（包含redo log和undo log），是否落磁盘取决于日志缓冲区大小和刷新策略（默认提交事务立即落磁盘）；
- 事务提交之后
- 根据WAL规则优先写Redo Log
- 成功之后；再写双写缓冲区；
- 最后写离散位置的指定page；
- 如果数据库再事务提交之后崩溃
- 此时从redo log中恢复未完成的事务（如果Redo Log未写成功，及事务未提交成功）；
- 恢复日志大致如下：
- InnoDB自动回滚崩溃时存在的未提交的事务
InnoDB: Log scan progressed past the checkpoint lsn 369163704
InnoDB: Doing recovery: scanned up to log sequence number 374340608
InnoDB: Doing recovery: scanned up to log sequence number 379583488
InnoDB: Doing recovery: scanned up to log sequence number 384826368
InnoDB: Doing recovery: scanned up to log sequence number 390069248
InnoDB: Doing recovery: scanned up to log sequence number 395312128
InnoDB: Doing recovery: scanned up to log sequence number 400555008
InnoDB: Doing recovery: scanned up to log sequence number 405797888
InnoDB: Doing recovery: scanned up to log sequence number 411040768
InnoDB: Doing recovery: scanned up to log sequence number 414724794
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: 1 transaction(s) which must be rolled back or cleaned up in
total 518425 row operations to undo
InnoDB: Trx id counter is 1792
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
...
InnoDB: Starting in background the rollback of uncommitted transactions
InnoDB: Rolling back trx with id 1511, 518425 rows to undo
...
InnoDB: Waiting for purge to start
InnoDB: 5.7.18 started; log sequence number 414724794
...
./mysqld: ready for connections.
网友评论