一般来说，数据库的性能瓶颈就是整套系统的性能瓶颈的上限。系统的性能遵循一个木桶效应，而木桶的短板通常在于数据库层面。以，MySQL为例子，我们来分析一下执行一条sql语句的成本在哪里：

  • I/O成本

    这是毋庸置疑的，数据的持久化是离不开硬盘（或磁盘）的。我们经常使用的MyIsam和InnoDB存储引擎都是将数据挂载在硬盘上的，当查询某个表中的记录时，需要先将数据载入内存中，然后再进行操作，从磁盘加载数据到内存的时间和空间成本就是所谓的I/O成本。

  • CPU成本

    读取记录以及检测记录是否满足查询条件、对结果集进行排序等操作所消耗的CPU时间称为CPU成本。

I/O成本

感兴趣的同学可以自行查阅相关的文章或者视频了解，当然，如果是计算机专业的同学，那么在《计算机组成原理》这门课上，肯定已经深入的了解过了。（好吧，假设你老师很会且你也没有打瞌睡）

交换单位

块是操作系统中最小的逻辑存储单位，它是虚拟出来的一个单位，即是一个概念模型。操作系统与磁盘打交道的最小单位是磁盘块。每个块可以包含2的N次方个扇区。

扇区、块/簇、page的关系：
  • 扇区：硬盘最小的读写单元
  • 块/簇：操作系统针对硬盘读写的最小单元
  • page：是内存与操作系统之间操作的最小单元

扇区 <= 块/簇 <= page

（这一块如果不明白的话，可以去看一下 操作系统 章节的知识）

局部性原理与磁盘预读取

局部性原理：当一个数据被用到时，其附近的数据通常会被使用，程序运行期间所需要的数据比较集中。

由于硬盘的顺序读取效率很高，预读取机制可以有效提高I/O效率。

预读取的长度一般为page的整数倍（许多操作系统中，单页的大小一般为4k）。当程序读取的数据不再主存中时，会触发一个缺页异常，操作系统向硬盘发送读取信号，硬盘会找到数据的起始位置并向后连续读取一页或几页的数据载入内存中，然后异常返回，程序继续执行。

（具体请看 操作系统 章节）

数据的存储

对于MySQL而言，数据存储在文件系统中，不同的存储引擎有不同的文件格式以及组织形式，下文还是以innodb为例子讲解。

innodb数据存储

对于innodb存储引擎而言，数据是存储在表空间中的，所谓的表空间只是以抽象的概念，它对应着硬盘上的一个或多个文件：

image-20241106110127842

表空间的存储单位是页（不要和操作系统中的page的概念搞混淆了，两个不是一个概念），innodb是以页为单位存储的，常用的页面类型有很多，不同类型的页面可以存放不同类型的数据，这里统称为数据页。通用模型如下，每一页大概占16kb：

image-20241106110416748

  • File Header：记录页面的一些通用信息，如当前页的校验和、页号、上页号、下页号所属表空间等。
  • File Trailer：主要的工作是校验页是否完整。

表空间中的每个页都有页号（File_PAGE_OFFSET），我们可以通过这个页号在表空间快速定位到指定的页面。

这个页号由4个字节组成，也就是32位，也就是32位，所以最多存在2的32次方页。如果按照一页16kb计算的话，一个表空间最大支持【64TB】的数据。整体的排列中，页是连续的，但是页有上下指针，不连续的页也能组成一个链表。

表空间示意图：

image-20241106111446897

表空间可以分为系统表空间和独立表空间：

系统表空间

  • 系统表空间包含了很多【公共数据】，比如InnoDB的数据字典，回滚信息、系统事物信息、二次写缓冲等，老版本的mysql表中的数据也会存储在系统表空间。

  • 系统表空间是一个共享的表空间因为它是被多个表共享的。

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

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

image-20241106111945385

独立表空间

  • 独立表空间是默认开启的，在5.6.6以后，Innodb不在默认将各个表的数据存储在【系统表空间】当中，而是会为每一个表建立一个独立表空间，innodb存储引擎的独立表空间为【.ibd】文件。

  • 如果启用了【innodb_file_per_table】参数，需要注意的是每张表的表空间内存放的只是【数据】、【索引】和【插入缓冲Bitmap页】，其他数据如：回滚信息、系统事物信息、二次写缓冲（Double writebuffer）等还是放在原来的系统表空间内。

  • 同时说明了一个问题：即使启用了【innodb_file_per_table】参数，系统表空间还是会不断的增加其大小的。

其他类型的表空间

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

组织结构

  • 区（extent）：每一个表空间保存了大量的页，为了更好的管理这些页面，Innodb提出了【区】的概念，对于16k的页，连续64个页就是一个区，大概1M的空间，每一个表空间都是由若干个连续的区组成的，每256个区被划分为一组。

  • 段（Segment）：分为索引段，数据段，回滚段等后边会将，段是为了区分不同的数据类型，相同的段存的数据类型是一致的。一个段包含256个区(256M大小)。

inndb表空间结构如下图所示：

image-20241106112827814

Row Format（行记录格式）

  • 一个表的【行记录格式】决定表中行的【物理存储模式】，决定了【DQL】和【DML】的操作性能。越多的行被匹配进独立的磁盘页，sql的性能会更好一些，需要的缓存及io操作就越少。

  • 一条完整的信息记录分为：【记录的额外信息】和【记录的真实数据】两大部分。

  • 我们可以通过命令 SHOW TABLE STATUS LIKE 'table_name' 来查看当前表使用的行格式，其中

    row_format就代表了当前使用的行记录结构类型。

指定行格式的语法如下：

-- 创建数据表时,显示指定行格式
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;
-- 创建数据表时,修改行格式
ALTER TABLE 表名 ROW_FORMAT=行格式名称;
-- 具体如下：
CREATE TABLE `ydl_user` (
`user_id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL
COMMENT '用户账号',
....
PRIMARY KEY (`user_id`) USING BTREE
) ROW_FORMAT = DYNAMIC;
（1）COMPACT

【compact行记录】是在MySQL 5.0时被引入的，其设计目标是能高效存放数据。Compact行记录以如下方式进行存储：

image-20241106162836706

  • 第一个部分是一个非NULL【变长字段长度列表】。这个其实很好理解，我们的数据类型除了定长的char、int还有不定长的如varchar、text等，变长列的真实长度就保存在这个部分，他是按照列的顺序【逆序放置】的。当列的长度小于255字节，如（varchar(50)），用1字节表示；若大于255个字节（varchar(600)），用2个字节表示，这其实也就说明了为什么varchar的最大长度是65536。

  • 第二个部分是【NULL标志位】，他指示了当前行数据中哪些为null值，用一个bitmap表示。举一个例子：假

    如该标志位为06(二进制：00000110)则表示第二三列（可以为空的列）的数据为NULL。需要注意的是，

    NULL值标志位仅仅针对可以为【NULL的字段】，如果某个字段被定义为not null，那么这个字段就不会进入

    NULL值标志位的BitMap中，这样可以节省很多空间，NULL值标志位也是逆序排列，占用空间按照字节数高

    位补零，如有九个字段可以为空（00000001 01010101）。

  • 第三部分为记录头信息（record header），固定占用5个字节（40位），每位的含义见下表：

​ image-20241106163044009

  • 第四部分就是实际存储的每个列的数据了，需要特别注意的是，NULL不占该部分任何数据，即NULL除了占

    有NULL标志位，实际存储不占有任何空间。Innodb存储变长列（VARCHAR, VARBINARY, BLOB, TEXT）的前

    768字节，剩下的部分存储在溢出页中。固定长度列，超过768字节的视为变长列。内部存储前768字节，20

    字节指针存储列的溢出页的地址，所以长度为768+20字节。

    image-20241106163216037

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

（2）DYNAMIC

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

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

image-20241106163524013

（3）Compressed

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

2024.11.06

writeBy kaiven