MySQL进阶二（InnoDB存储引擎）
继续上一篇，我们在得到执行计划之后，sql是不是就可以执行了？这里有两个问题：1.数据存放在哪里？或者说放在一个什么结构里面2.执行计划在哪里执行，怎么执行？
1.存储引擎的基本介绍我们先来看第一个问题，在关系型数据库中，数据是存放在表中，我们可以把这个表理解成Excel里面的表格，所以我们在存储数据时还要组织数据的存储结构，这个存储结构就是由我们的存储引擎决定的，在MySQL里面，支持多种存储引擎，存储引擎是以插件的形式存在，那么这些存储引擎的差别在哪里呢？
1.1存储引擎比较常见的存储引擎??innoDB和MyISAM是我们用的最多的两个存储引擎，在MySQL5.5之前，默认的存储引擎是MyISAM，它是MySQL自带的。??MyISAM前身是ISAM（Indexed Sequential Access Method：利用索引，顺序存取数据的方法）。??MySQL5.5之后默认的存储引擎改成了innoDB，主要是因为InnoDB支持事务，支持行级锁，对于业务一致性的要求高的场景来说更合适。
如果需要一个用于查询的临时表，可以用memory，顾名思义就是存在于内存中的表，访问速度会很快。具体各个存储引擎的特性：https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html1.2.执行引擎第二个问题，执行计划就是由执行引擎去操作存储引擎的，不同的存储引擎实现了相同的API，因此对于执行引擎来说，不同的存储引擎的操作是一样的。
2.MySQL体系架构总结2.1模块详解Connectors：用来支持各种语言和SQL的交互，比如PHP，Java等。management Service&utililties：系统管理和控制工具，包括备份恢复，MySQL复制，集群等。connection pool：连接池，管理需要缓冲的资源，包括用户密码权限线程等。sql interface：用来接收客户端的sql命令，并返回结果。parser：用来解析sql语句。optimizer：查询优化器。cache & buffers：查询缓存，还有表缓存，key缓存，权限缓存等。pluggable storage engines：插件式存储引擎，提供api给服务层使用。
2.2架构分层3.一条更新SQL是如何执行的？基本流程和查询SQL是一致的，解析器-》优化器-》执行引擎，主要的区别在于拿到符合条件的数据之后的操作。
3.1缓冲池 Buffer Pool??首先，innoDB的数据都是存储在磁盘中的，innoDB操作数据有一个最小单元，叫做页（索引页和数据页）。我们对数据的操作，不是每次都直接操作磁盘，这样太慢了。innnoDB使用了一种缓冲池的技术，也即是把磁盘读到的每一页放到一块内存里面，这个区域就叫做Buffer Pool。??每次读取页的时候，先从缓存池中读取，如果缓存池中存在就直接读取，不再访问磁盘。??修改数据的时候，先修改缓存池里的页。当磁盘和缓存池里的页数据不一致的时候，我们把它叫做脏页。InnoDB会有专门的线程，把buffer pool的数据写入到磁盘，每隔一段时间就把多个修改写入磁盘，这个动作就叫刷脏。
3.2.InnoDB的内存和磁盘结构：https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.htmlInnoDB Architecture3.2.1.内存结构1. Buffer Pool?Buffer Pool缓存的是页信息，包括索引页和数据页，默认大小是128M（134217728字节），可以调整。当缓存池满了的时候，使用LRU算法来管理缓存池（链表实现，不是传统的LRU，分成了young和old），经过淘汰的数据之后剩下的就是热点数据。2. change Buffer 写缓存??在更新缓存的时候，如果在缓存中没有数据，就要从磁盘读取一次数据到缓存再更新，至少会发生一次io，所以为了避免这种情况，增加了一块change Buffer，如果这个数据页不是唯一索引，不需要考虑数据唯一性（否则就还是要跟磁盘的数据作比较，避免不了io操作），这种情况下可以先把修改记录在缓冲池中，从而提升语句（insert update delete）的执行速度。最后把Change Buffer 记录到数据页的操作叫merge，什么时候发生merge：在访问这个数据页的时候、或者通过后台线程、或者数据库shut down、redo log写满时触发。??如果数据大部分索引时非唯一索引，并且业务时写多读少，不会在数据写后立即读取，就可以调大一点Change Buffer，默认是占Buffer Pool的25%。3.Log Buffer（redo Log）??如果buffer pool的脏页还没有刷新到磁盘时，数据库宕机或者重启，这些数据丢失。如果写到一半，甚至会破坏数据文件导致数据不可用。为了避免这个问题，innoDB把所有的写操作专门写入到一个日志文件，并且在数据库启动时从这个文件进行恢复操作（实现crash-safe）-用来实现事务的持久性。??这个文件就是磁盘的redo Log，对应于/var/lib/mysql/目录下的ib_logfile0和ib_logfile1，每个大小48M??这种日志和磁盘的配合过程，其实就是MySQL里面的WAL（Write Ahead Log），先写日志，再写磁盘。??这里还有个知识点，写redo Log到磁盘是顺序IO，写数据到磁盘是随机io，所以写日志的速度更快。??redo log buffer写入到磁盘的时机有3种选择，首先我们知道，内存往磁盘写数据中间时存在操作系统缓存的，flush就是把os cache刷到磁盘中。show variables like 'innodb_flush_log_trx_commit'默认是1，含义：https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
redo Log的大小是固定的，前面写的内容会被覆盖。
图片.pngcheckpoint是当前要覆盖的位置。如果writepos跟checkpoint重叠，说明redolog已经写满，这时候需要同步redo log到磁盘中。
3.2.2.磁盘结构??表空间可以看作是innoDB存储引擎逻辑层最高层，所有的数据都存放在表空间中。InnoDB的表空间分为5大类。系统表空间 system table space??主要包含双写缓冲，change buffer，undo log，如果没有指定file-per-table，也会包含用户创建的表。
undo log（撤销日志或回滚日志）记录了事务发生之前的数据状态。如果修改数据时出现异常，可以用undo log来实现回滚操作（保持原子性）。在执行undo 的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，属于逻辑格式的日志。redo Log和undo Log与事务密切相关，统称为事务日志。数据字典：由内部系统表组成，存储表和索引的元数据（定义信息）。双写缓冲（InnoDB的一大特性）：InnoDB的页和操作系统的页大小不一致，InnoDB页大小一般为16K，操作系统页大小为4K，InnoDB的页写入到磁盘时，一个页需要分4次写。如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了4K，就宕机了，这种情况叫做部分写失效（partialpagewrite），可能会导致数据丢失。尽管我们已经有了redo log但是如果这个页已经损坏，那再恢复也是没意义的，因此我们应用redo log之前需要一个页的副本，如果出现了页的写入失效，则先还原这个页再应用redo log。这个页的副本就是double write，双写技术，通过它保证数据页的可靠性。有了这些日志之后，我们来总结一下一个更新操作的流程，这是一个简化的过程。name原值是qingshan。updateusersetname='penyuyan'whereid=1;1、事务开始，从内存或磁盘取到这条数据，返回给Server 的执行器；2、执行器修改这一行数据的值为penyuyan；3、记录name=qingshan到undo log；4、记录name=penyuyan到redo log；5、调用存储引擎接口，在内存（Buffer Pool）中修改 name=penyuyan；6、事务提交。3.3.Binlog日志??binlog以事件的形式记录了所有的DDL和DML语句（因为它记录的是操作而不是数据值，属于逻辑日志），可以用来做主从复制和数据恢复。??跟redo log不一样，它的文件内容是可以追加的，没有固定大小限制。??在开启了binlog功能的情况下，我们可以把binlog导出成SQL语句，把所有的操作重放一遍，来实现数据的恢复。??binlog的另一个功能就是用来实现主从复制，它的原理就是从服务器读取主服务器的binlog，然后执行一遍。有了这两个日志之后，我们来看一下一条更新语句是怎么执行的：
整体流程1、先查询到这条数据，如果有缓存，也会用到缓存。2、把name改成盆鱼宴，然后调用引擎的API接口，写入这一行数据到内存，同时记录redo log。这时 redo log 进入prepare 状态，然后告诉执行器，执行完成了，可以随时提交。3、 执行器收到通知后记录binlog，然后调用存储引擎接口， 设置redolog为commit状态。4、更新完成。
在InnoDB存储引擎的索引里，存在一些数据存储结构的概念，这一节先了解一下InnodDB的逻辑存储结构，为索引的学习打好基础。
从InnoDB存储引擎的存储结构看，所有数据都被逻辑地放在一个空间中，称之为表空间（tablespace）、区（extent）、页（page）组成，页在一些文档中也被称之为块（block）。
1、InnoDB逻辑存储结构
InnoDB存储引擎的逻辑存储结构大致如图：
这张图更清晰地展示了这些空间的包含关系：
1.1、表空间（Tablespace）
表空间可以看做InnoDB逻辑结构的最高层，所有的数据都放在表空间中。
在默认情况下，InnoDB存储引擎都有一个共享表空间ibdata1，即所有数据都存放在这个表空间内。如果用户启用了参数innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内。
如果启用了innodb_file_per_table参数，也需要注意，每张表的表空间存放的只是数据、索引和插入缓冲Bitmap页，其它类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲等还是存放在原来的共享表空间内。
1.2、段（Segment）
表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。
InnoDB存储引擎表是索引组织（index organized）的，因此数据即索引，索引即数据。那么数据段即为B+树的叶子节点（Leaf node segment），索引段即为B+树的非索引节点（Non-leaf node segment），这些内容在后面的索引学习里会详细介绍。
1.3、区（extend）
区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InonoDB存储引擎一次从磁盘申请4-5个区。在默认情况下，InnoDB存储引擎的页的大小为16KB，即一个区中应有64个连续的页。
InnoDB1.0.x版本开始引入压缩页，每个页的大小可以通过参数KEY_BLOCK_SIZE设置为2K、4K、8K，因此每个区对应的页尾512、256、128.
InnoDB1.2.x版本新增了参数innodb_page_size，通过该参数可以将默认页的大小设置为4K、8K，但是页中的数据不是压缩的。
但是有时候为了节约磁盘容量的开销，创建表默认大小是96KB，区中是64个连续的页。（对于一些小表）
1.4、页（page）
页是InnoDB存储引擎磁盘管理的最小单位，每个页默认16KB；InnoDB存储引擎从1.2.x版本开始，可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。
若设置完成，则所有表中页的大小都为innodb_page_size，不可以再次对其进行修改，除非通过mysqldump导入和导出操作来产生新的库。
innoDB存储引擎中，常见的页类型有：
数据页（B-tree Node)undo页（undo Log Page）系统页 （System Page）事务数据页 （Transaction System Page）插入缓冲位图页（Insert Buffer Bitmap）插入缓冲空闲列表页（Insert Buffer Free List）未压缩的二进制大对象页（Uncompressed BLOB Page）压缩的二进制大对象页 （compressed BLOB Page）1.5、行（row）
InnoDB存储引擎是面向行的（row-oriented)，也就是说数据是按行进行存放的，每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200，即7992行记录。
2、InnoDB 行记录格式
InnoDB 存储引擎和大多数数据库一样（如 Oracle 和 Microsoft SQL Server 数据库），记录是以行的形式存储的。这意味着页中保存着表中一行行的数据。在 InnoDB 1.0x 版本之前，InnoDB 存储引擎提供了 Compact 和 Redundant 两种格式来存放行记录数据，这也是目前使用最多的一种格式。
2.1、Compact 行记录格式
Compact 行记录是在 MySQL 5.0 中引人的，其设计目标是髙效地存储数据。简单来说,一个页中存放的行数据越多，其性能就越髙。
下图显示了 Compact 行记录的存储方式：
Compact 行记录格式的首部是一个非 NULL 变长字段长度列表，并且其是按照列的顺序逆序放置的，其长度为：
若列的长度小于 255 字节，用 1 字节表示；若大于 255 个字节，用2 字节表示。变长字段的长度最大不可以超过 2 字节，这是因在 MySQL 数据库中 VARCHAR 类型的最大长度限制为 65535。变长字段之后的第二个部分是 NULL 标志位，该位指示了该行数据中是否有 NULL 值，有则用 1 表示。
接下来的部分是记录头信息（record header)，固定占用5 字节（40 位)。每位含义见表：
最后的部分就是实际存储每个列的数据。
需要特别注意的是，NULL 不占该部分任何空间，即 NULL 除了占有 NULL 标志位，实际存储不占有任何空间。另外有一点需要注意的是，每行数据除了用户定义的列外，还有两个隐藏列，事务 1D 列和回滚指针列,分别为 6 字节和 7 字节的大小。若 InnoDB 表没有定义主键，每行还会增加一个 6 字节的 rowid 列。
Redundant 是 MySQL 5 . 0 版本之前 InnoDB 的 行 记 录 存 储 方 式，这里就不展开。
2.2、行溢出数据
InnoDB 存储引擎可以将一条记录中的某些数据存储在真正的数据页之外。因为一般数据页默认大小为16KB，假如一个数据页存储不了插入的数据，这时肯定就会发生行溢出。
一般认为 BLOB、LOB 这类的大对象列类型的存储会把数据存放在数据页之外。但是，BLOB 也可以不将数据放在溢出页面，而且即便是 VARCHAR 列数据类型，依然有可能被存放为行溢出数据。
3、InnoDB 数据页结构
页是 InnoDB 存储引擎管理数据库最小磁盘单位。页类型为 B-tree Node 的页存放的即是表中行的实际数据了。
InnoDB 数据页由以下 7 个部分组成:
File Header (文件头）Page Header (页头）Infimun 和 Supremum RecordsUser Records (用户记录，即行记录）Free Space (空闲空间）Page Directory (页目录）File Trailer (文件结尾信息）其中 File Header、Page Header、File Trailer的大小是固定的, 分别为 38、56、8 字节，这些空间用来标记该页的一些信息，如 Checksum, 数据页所在 B+ 树索引的层数等。User Records、Free Space、Page Directory 这些部分为实际的行记录存储空间，因此大小是动态的。
发表评论