MySQL进阶第二章

二.MySQL的I/O与存储

MySQL性能决定了一个系统的性能，执行一条sql的成本主要在于两个方面

        I/O成本：我们经常使用的数据库存储引擎为InnoDB和MyIsam（大部分公司使用的都是InnoDB，少数会使用MyIsam和InnoDB结合），这些引擎将数据存储到磁盘上，当查询时先把数据加载到内存上，再进行操作，这个过程为I/O成本。

        CPU成本：读取记录并检测是否满足查询要求，对记录进行排序等操作消耗称之为CPU成本。

1.数据储存

1.InnoDB数据存储

对于InnoDB来说数据是存储在表空间里的，表空间是一个抽象的概念，对应着硬盘中一个或多个文件。

【表空间】：表空间的存储单位是页，不同类型的页剋以存放不同的数据类型，每一页大概占16k的空间；页的结构通常有三部分：file header，file trailer和中间区域；

• file header：记录页面的一些通用信息，比如当前页的校验和、页号、上页号、下页号、所属表空间等。
• file trailer：主要的工作是检验页是否完整。
• 中间区域不同类型的页面这个区域的作用是不同的；

(1).系统表空间

1.系统表空间包含了很多公共数据，比如InnoDB的字典、回滚信息、系统事务信息，二次写缓冲等。

2.系统表空间是一个共享的表空间，因为他是被多个表共享的。

3.该空间的数据文件通过参数【innodb_data_file_path】控制，默认值是【ibdata1:12M:autoextend】（文件名为ibdata1、12MB、自动扩展）。

4.当然系统表空间也可以通过配置，修改文件的名称和个数。

(2).独立表空间(File-Per-Table Tablespaces)

• 独立表空间是默认开启的，InnoDB不在默认将各个表的数据存储在【系统表空间】当中，而是回味每一个表建立一个独立表空间，InnoDB存储引擎的独立表空间为【.ibd】文件。
• 如果启用了【innodb_file_per_table】参数，需要注意的是每张表的表空间内存放的只是【数据】、【索引】和【插入缓冲Bitmap页】，其他数据如：回滚信息、系统事物信息、二次写缓冲（Double write buffer）等还是放在原来的系统表空间内。
• 同时说明了一个问题：即使启用了【innodb_file_per_table】参数，系统表空间还是会不断的增加其大小的。

 (3).其他类型的表空间

除了以上两种表空间，，innodb还提供了很多其他类型的表空间，比如通用表空间，undolog表空间、临时表空间等，这里不在赘述。

Mylasm没有表空间的概念，他会在目录中产生2个文件【.MYD】（数据文件）、【 .MYI】（索引文件）三个文件。

**注意：**在5.7以前【数据文件】和【表信息文件】是分开的，相互独立的。会多一个【.frm】文件，8.0之后进行了合并。

2.组织结构 

• 区（extent）：：每一个表空间保存了大量的页，为了更好的管理这些页面，Innodb提出了【区】的概念，对于16k的页，连续64个页就是一个区，大概1M的空间，每一个表空间都是由若干个连续的区组成的，每256个区被划分为一组。
• 段（Segment）：分为索引段，数据段，回滚段等后边会将，段是为了区分不同的数据类型，相同的段存的数据类型是一致的。一个段包含256个区(256M大小)。

3.Row Format(行记录格式)

• 一个表的【行记录格式】决定表中行的【物理存储模式】，决定了【DQL】和【DML】的操作性能。越多的行被匹配进独立的磁盘页，sql的性能会更好一些，需要的缓存及io操作就越少。
• 一条完整的信息记录分为：【记录的额外信息】和【记录的真实数据】两大部分，就如同一箱苹果里的苹果分为包装和苹果一样。
• 我们可以通过命令SHOW TABLE STATUS LIKE 'table_name'来查看当前表使用的行格式，其中row_format就代表了当前使用的行记录结构类型。
• 

(1).COMPACT

【compact行记录】是在MySQL5.0时被引入的，其设计目的时高效存放数据。Compact行记录以下面方式进行存储：

1. 第一部分是一个非空【变长字段长度列表】。理解为数据类型除了定长的char、int还有不定长的varchar、text等，变长列的真实长度就保存在这个部分，按照逆序放置。当列的长度小于255个字节，用一个字节表示；若大于255个字节，用2个字节表示，这也就说明了为什么varchar的最大长度是65536。
2. 第二个部分是【NULL标志位】，他指示了当前行数据中哪些为null值，用一个bitmap表示。如：加入该标志为06(二进制：00000110)则表示地二三列的数据为NULL，前提是可以为NULL。因此NULL标志位仅仅针对可以为NULL的字段，如果某个字段被定义为not null 他就不会进入NULL标志位的bitmap中，NULL标志位也是逆序排列，占用空间按照字节数高位补0，如果有9个字段可以为空（00000001 01010101）。
3. 第三部分为记录头信息（record header），固定占用五个字节（40位）。
4. 第四部分就是实际存储的每个列的数据量，要注意的是，NULL不占该部分任何数据，就是NULL除了占有NULL标志位，实际不占有任何空间。innodb存储变长列（VARCHAR, VARBINARY, BLOB, TEXT）的前768字节，剩下的部分存储在溢出页中。固定长度列，超过768字节的视为变长列。内部存储前768字节，20字节指针存储列的溢出页的地址，所以长度为768+20字节。

**注意：**另外有一点需要注意的是，每行数据除了用户定义的列外，还有两个隐藏列，事务ID列和回滚指针列，分别为6个字节和7个字节的大小。若InnoDB表没有定义Primary Key，每行还会增加一个【6字节的RowID列】。

(2).REDUNDANT

【Redundant】是MySQL 5.0版本之前InnoDB的行记录存储方式。Redundant行记录以如下方式存储：

1. 第一个部分保存了【字段长度偏移列表】，这个部分保存了该行数据所有列，包括隐藏列的长度偏移量。举一个例子说明一下偏移，假如第一个字段长度为x，第二个字段长度为y，那么列表中第一个字段就是x，第二个字段就是x+y。这个偏移列表是按照列的顺序【逆序排列】。

2. 第二个部分为记录头信息【record header】，Redundant行格式固定占用6个字节（48位）。

3. 第三个部分就是实际存储的每个列的数据了。

null值的存储，在【字段长度列表】的每个字段长度最高位标记 1 表示这个字段为 NULL。

• 对于一字节存储，通过【最高位标记字段】判断是否为 NULL，如果为 NULL，则最高位为1，否则为0。剩下的 7 位用来存储数据，所以最多是127。
• 对于两字节存储，通过【最高位标记字段】判断是否为 NULL，第二位标记这条记录是否在同一页，如果在则为0，如果不在则为1，这其实就涉及到了后面要说的溢出页。剩下的 14 位表示长度，所以最多是16383。
• 在这种类型的行格式中，无论字段是否为 NULL，或者长度是多少，char(M) 都会占用 M * 字节编码最大长度那么多字节。为 NULL 的话，填充的是 0x00，不为 NULL，长度不够的情况下，末尾补充 0x20。 对于varchar来说，NULL 还是不占用空间的。

(3).DYNAMIC

InnoDB Plugin引入了两种新的文件格式（file format，可以理解为新的页格式），对于以前支持的Compact和Redundant格式将其称为Antelope文件格式，新的文件格式称为Barracuda。Barracuda文件格式下拥有两种新的行记录格式Compressed和Dynamic两种。

新的两种格式对于存放BLOB的数据采用了完全的行溢出的方式，在数据页中只存放20个字节的指针，实际的数据都存放在BLOB Page中，而之前的Compact和Redundant两种格式会存放768个前缀字节。mysql8.0默认此格式。

(4).COMPRESSED 

COMPRESSED 基于dynamic格式，支持表和索引数据压缩。compressed行格式采用dynamic相同的页外存储细节，同时，存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能进行非常有效的存储。