MySQL 体系结构及存储引擎

超、凢脫俗 2023-07-04 12:42 23阅读 0赞

**MySQL 原理篇**

[MySQL 索引机制][MySQL]

[MySQL 体系结构及存储引擎][MySQL 1]

[MySQL 语句执行过程详解][MySQL 2]

[MySQL 执行计划详解][MySQL 3]

[MySQL InnoDB 缓冲池][MySQL InnoDB]

[MySQL InnoDB 事务][MySQL InnoDB 1]

[MySQL InnoDB 锁][MySQL InnoDB 2]

[MySQL InnoDB MVCC][]

[MySQL InnoDB 实现高并发原理][MySQL InnoDB 3]

[MySQL InnoDB 快照读在RR和RC下有何差异][MySQL InnoDB _RR_RC]

## MySQL 体系结构 ##

MySQL 体系结构图：

![643613-20191110202048105-99389438.png][]

MySQL 从概念上分为四层，这四层自顶向下分别是网络连接层，服务层（核心层），存储引擎层，系统文件层。我们自顶向下开始讲解。

### 网络连接层（Client Connectors） ###

主要负责连接管理、授权认证、安全等等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到 MySQL 服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过 SSL 证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。这一层并不是 MySQL 所特有的技术。

### 服务层（MySQL SERVER） ###

第二层服务层是 MySQL 的核心，MySQL 的核心服务层都在这一层，查询解析，SQL 执行计划分析，SQL 执行计划优化，查询缓存。以及跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等。

1.  **连接池（****Connection Pool****）**：管理、缓冲用户的连接，线程处理等需要缓存的需求
2.  **管理服务和工具组件（****Services & utilities****）**：系统管理和控制工具，例如备份恢复、MySQL 复制、集群等
3.  **SQL 接口****（****SQL Interface****）**：接受用户的 SQL 命令，并且返回用户需要查询的结果
4.  **查询解析器****（****Parser****）**：SQL 命令传递到解析器的时候会被解析器验证和解析（权限、语法结构）
5.  **查询优化器****（****Optimizer****）**：SQL 语句在查询之前会使用查询优化器对查询进行优化
6.  **缓存****（****Caches****）**：如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据

### 存储引擎层（Pluggable Storage Engines） ###

负责 MySQL 中数据的存储与提取。 服务器中的查询执行引擎通过 API 与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。MySQL 采用插件式的存储引擎。MySQL 为我们提供了许多存储引擎，每种存储引擎有不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。如果对于存储引擎的性能不满意，可以通过修改源码来得到自己想要达到的性能。

各个存储引擎的描述参考下文。

**特点：**

1.  MySQL 采用插件式的存储引擎。
2.  存储引擎是针对于表的而不是针对库的（一个库中不同表可以使用不同的存储引擎），服务器通过 API 与存储引擎进行通信，用来屏蔽不同存储引擎之间的差异。
3.  不管表采用什么样的存储引擎，都会在数据区，产生对应的一个 的一个 frm 文件（表结构定义描述文件）

### 系统文件层（File System NTFS ext4 SAN NAS） ###

文件系统，数据、日志（redo，undo）、索引、错误日志、查询记录、慢查询等，详细参考下文的物理文件描述。

比如该层将数据库的数据存储在文件系统之上，并完成与存储引擎的交互。在 [MySQL 索引机制][MySQL] 中有讲到 Myisam 和 InnoDB 的文件结构内容。

![643613-20191110202802132-253848634.png][]

## MySQL 存储引擎 ##

下面简单介绍一下 MySQL 中常见的的存储引擎

### CSV 存储引擎 ###

数据存储以 CSV 文件。

**特点：**

1.  不能定义索引、列定义必须为 NOT NULL、不能设置自增列， 不适用大表或者数据的在线处理
2.  CSV 数据的存储用逗号隔开，可直接编辑 CSV 文件进行数据的编排，数据安全性低。编辑之后，要生效使用 `flush table XXX` 命令

**应用场景：**

1.  数据的快速导出导入
2.  表格直接转换成 表格直接转换成 CSV

### Archive 存储引擎 ###

压缩协议进行数据的存储，数据存储为 ARZ 文件格式。

**特点：**

1.  只支持 insert 和 select 两种操作
2.  只允许自增 ID 列建立索引
3.  行级锁
4.  不支持事务
5.  数据占用磁盘少

**应用场景：**

1.  日志系统
2.  大量的设备数据采集

### Memory 存储引擎 ###

数据都是存储在内存中，IO 效率要比其他引擎高很多，服务重启数据会丢失，内存数据表默认只有 16M，一般我们不会使用到 Memory 存储引擎。

**特点：**

1.  支持 hash 索引，B tree 索引，默认 hash（查找复杂度 0(1)）
2.  字段长度都是固定长度 varchar(32)=char(32)
3.  不支持大数据存储类型字段如 blog，text
4.  表级锁

**应用场景：**

1.  等值查找热度较高数据
2.  在排序、分组等操作中，当数据量小于16M（默认大小），由查询优化器建立的临时表就是 Memory 类型

### MyISAM 存储引擎 ###

MySQL5.5 版本之前的默认存储引擎，较多的系统表也还是使用这个存储引擎，系统临时表也会用到 MyISAM 存储引擎。

**特点：**

1.  select count(\*) from table 无需进行数据的扫描
2.  数据（MYD）和索引（MYI）分开存储
3.  表级锁
4.  不支持事务

**应用场景：**

1.  在排序、分组等操作中，当数量超过一定大小之后，由查询优化器建立的临时表就是 MyISAM 类型
2.  报表，数据仓库

### InnoDB 存储引擎 ###

MySQL 5.5 及以后版本的默认存储引擎。

**特点：**

1.  事务 ACID
2.  行级锁
3.  聚集索引（主键索引）方式进行数据存储
4.  支持外键关系保证数据完整性

### 对比 ###

<table> 
 <thead> 
 <tr> 
 <td>Feature</td> 
 <td>MyISAM</td> 
 <td>Memory</td> 
 <td>InnoDB</td> 
 <td>Archive</td> 
 <td>NDB</td> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td>B-tree indexes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>No</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td>Backup/point-in-time recovery(note 1)</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td>Cluster database support</td> 
 <td>No</td> 
 <td>No</td> 
 <td>No</td> 
 <td>No</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td>Clustered indexes</td> 
 <td>No</td> 
 <td>No</td> 
 <td>Yes</td> 
 <td>No</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td>Compressed data</td> 
 <td>Yes (note 2)</td> 
 <td>No</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td>Data caches</td> 
 <td>No</td> 
 <td>N/A</td> 
 <td>Yes</td> 
 <td>No</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td>Encrypted data</td> 
 <td>Yes (note 3)</td> 
 <td>Yes (note 3)</td> 
 <td>Yes (note 4)</td> 
 <td>Yes (note 3)</td> 
 <td>Yes (note 3)</td> 
 </tr> 
 <tr> 
 <td>Foreign key support</td> 
 <td>No</td> 
 <td>No</td> 
 <td>Yes</td> 
 <td>No</td> 
 <td>Yes (note 5)</td> 
 </tr> 
 <tr> 
 <td>Full-text search indexes</td> 
 <td>Yes</td> 
 <td>No</td> 
 <td>Yes (note 6)</td> 
 <td>No</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td>Geospatial data type support</td> 
 <td>Yes</td> 
 <td>No</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td>Geospatial indexing support</td> 
 <td>Yes</td> 
 <td>No</td> 
 <td>Yes (note 7)</td> 
 <td>No</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td>Hash indexes</td> 
 <td>No</td> 
 <td>Yes</td> 
 <td>No (note 8)</td> 
 <td>No</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td>Index caches</td> 
 <td>Yes</td> 
 <td>N/A</td> 
 <td>Yes</td> 
 <td>No</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td>Locking granularity</td> 
 <td>Table</td> 
 <td>Table</td> 
 <td>Row</td> 
 <td>Row</td> 
 <td>Row</td> 
 </tr> 
 <tr> 
 <td>MVCC</td> 
 <td>No</td> 
 <td>No</td> 
 <td>Yes</td> 
 <td>No</td> 
 <td>No</td> 
 </tr> 
 <tr> 
 <td>Replication support&nbsp;(note 1)</td> 
 <td>Yes</td> 
 <td>Limited (note 9)</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td>Storage limits</td> 
 <td>256TB</td> 
 <td>RAM</td> 
 <td>64TB</td> 
 <td>None</td> 
 <td>384EB</td> 
 </tr> 
 <tr> 
 <td>T-tree indexes</td> 
 <td>No</td> 
 <td>No</td> 
 <td>No</td> 
 <td>No</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td>Transactions</td> 
 <td>No</td> 
 <td>No</td> 
 <td>Yes</td> 
 <td>No</td> 
 <td>Yes</td> 
 </tr> 
 <tr> 
 <td>Update statistics for data dictionary</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 <td>Yes</td> 
 </tr> 
 </tbody> 
</table>

## 物理文件 ##

物理文件包括：日志文件，数据文件，配置文件，pid 文件，socket 文件等

### 日志文件 ###

MySQL 5.7 日志文件介绍：[https://dev.mysql.com/doc/refman/5.7/en/server-logs.html][https_dev.mysql.com_doc_refman_5.7_en_server-logs.html]

<table> 
 <thead> 
 <tr> 
 <td>Log Type</td> 
 <td>Information Written to Log</td> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td>Error log</td> 
 <td>Problems encountered starting, running, or stopping&nbsp;<a href="https://dev.mysql.com/doc/refman/5.7/en/mysqld.html" rel="nofollow">mysqld</a></td> 
 </tr> 
 <tr> 
 <td>General query log</td> 
 <td>Established client connections and statements received from clients</td> 
 </tr> 
 <tr> 
 <td>Binary log</td> 
 <td>Statements that change data (also used for replication)</td> 
 </tr> 
 <tr> 
 <td>Relay log</td> 
 <td>Data changes received from a replication master server</td> 
 </tr> 
 <tr> 
 <td>Slow query log</td> 
 <td>Queries that took more than&nbsp;<a href="https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_long_query_time" rel="nofollow"><code>long_query_time</code></a>&nbsp;seconds to execute</td> 
 </tr> 
 <tr> 
 <td>DDL log (metadata log)</td> 
 <td>Metadata operations performed by DDL statements</td> 
 </tr> 
 </tbody> 
</table>

#### 错误日志（Error log） ####

MySQL 错误日志记录 MySQL 运行过程中较为严重的警告和错误信息，以及 MySQL 每次启动和关闭的详细信息。MySQL 错误日志默认是开启的。

可以通过 MySQL 配置文件中的 log-error=/var/log/mysqld.log 配置，修改错误日志的配置信息。

可以通过如下 SQL 查看错误日志的详细信息：

show variables like '%log_err%';

![643613-20191110203057929-2070066619.png][]

#### 通用查询日志（General query log） ####

记录建立的客户端连接和执行的语句。

可以通过如下 SQL 查看当前的通用日志是否开启：

SHOW VARIABLES LIKE '%general%';

![643613-20191110203129719-304171750.png][]

/*开启通用日志查询：*/
    set global general_log = on;
    
    /*关闭通用日志查询：*/
    set global general_log = off;

#### 二进制日志（Binary log） ####

MySQL 的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的 MySQL 语句。二进制日志（binary log）中记录了对 MySQL 数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录 SELECT、SHOW 等那些不修改数据的 SQL 语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。

/*删除所有二进制文件：*/
    reset master
    
    /*删除部分二进制文件：*/
    purge master logs
    
    /*查看是否启用二进制日志：*/
    show variables like '%log_bin%';
    
    /*查看所有的二进制参数*/
    show variables like '%binlog%';
    
    /*查看文件的位置*/
    show variables like '%datadir%';
    
    /*查看当前服务器所有的二进制日志文件*/
    show binary logs;
    show master logs;

#### 慢查询日志（Slow query log） ####

记录所有执行时间超过 long\_query\_time 秒的查询 SQL 或者没有使用索引的查询 SQL，默认情况下，MySQL 不开启慢查询日志，long\_query\_time 的默认值为10，即运行时间超过 10s 的语句是慢查询语句。

/*查看当前慢查询日志的开启情况：*/
    show variables like '%query%';

![643613-20191110203240154-1853830368.png][]

*  slow\_query\_log：ON 表示开启慢查询日志，OFF 表示关闭慢查询日志
 *  slow\_query\_log\_file：记录慢查询日志的文件地址（默认为主机名.log）
 *  long\_query\_time：指定了慢查询的阈值，单位是秒，即执行语句的时间若超过这个值则为慢查询语句
 *  log\_queries\_not\_using\_indexes：ON 表示会记录所有没有利用索引来进行查询的语句，前提是 slow\_query\_log 的值也是 ON，否则，不会奏效，OFF 表示不会记录所有没有利用索引来进行查询的语句。

### 配置文件 ###

Linux下 MySQL 的配置文件是 my.cnf，一般会存放在 /etc/my.cnf，/etc/mysql/my.cnf，MySQL 所有的配置信息都存放在该文件中，后面会单独整理一篇 MySQL 的配置内容介绍。

### 数据文件 ###

获取硬盘中数据存储的地址：

SHOW VARIABLES LIKE 'datadir';

![643613-20191110203332214-406719280.png][]

根目录文件内容：

![643613-20191110203352181-1841024076.png][]

engine 数据库文件内容：

![643613-20191110203417728-194285090.png][]

#### db.opt 文件 ####

该文件记录这个库的默认使用的字符集和校验规，文件存放在所属数据库的目录下。

#### FRM 文件 ####

不论使用什么存储引擎，每一张表都会有一个以表名命名的 .frm 文件，与表相关的元数据（meta）信息都存放在此文件中，包括表结构的定义信息等，文件存放在所属数据库的目录下。

#### MYD 文件 ####

MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)。每一张 MyISAM 表都会有一个 .MYD 文件，文件存放在所属数据库的目录下。

#### MYI 文件 ####

也是 MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息。每一张 MyISAM 表对应一个 .MYI 文件，文件存放在所属数据库的目录下。

#### IBD 文件和 IBDATA 文件 ####

存放 InnoDB 的数据文件（包括索引）。InnoDB 存储引擎有两种表空间方式：独享表空间和共享表空间。

独享表空间：使用 .ibd 文件来存放数据，且每一张 InnoDB 表对应一个 .ibd 文件，文件存放在所属数据库的目录下。

共享表空间：使用 .ibdata 文件，所有表共同使用一个（或多个，自行配置）.ibdata 文件。

#### ibdata1 文件 ####

系统表空间（数据文件）undo 段，文件存放在 datadir 目录下。

#### ib\_logfile0、ib\_logfile1 文件 ####

redlog 文件，文件存放在 datadir 目录下。

### pid 文件 ###

pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件，和许多其他 Unix/Linux 服务端程序一样，它存放着自己的进程 id。

### socket 文件 ###

socket 文件也是在 Unix/Linux 环境下才有的，用户在 Unix/Linux 环境下客户端连接可以不通过 TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL。

### 参考 ###

[MySQL体系结构][MySQL 4]

[【MySQL】漫谈MySQL体系结构][MySQL_MySQL]

[Chapter 15 Alternative Storage Engines][]

[MySQL5.7 四种日志文件][MySQL5.7]

[MySQL]: https://www.yuque.com/yinjianwei/vyrvkf/ei2evb
[MySQL 1]: https://www.yuque.com/yinjianwei/vyrvkf/vu1ggy
[MySQL 2]: https://www.yuque.com/yinjianwei/vyrvkf/ri4ks7
[MySQL 3]: https://www.yuque.com/yinjianwei/vyrvkf/qeog7k
[MySQL InnoDB]: https://www.yuque.com/yinjianwei/vyrvkf/karbt2
[MySQL InnoDB 1]: https://www.yuque.com/yinjianwei/vyrvkf/cl4weh
[MySQL InnoDB 2]: https://www.yuque.com/yinjianwei/vyrvkf/ei0mep
[MySQL InnoDB MVCC]: https://www.yuque.com/yinjianwei/vyrvkf/skszul
[MySQL InnoDB 3]: https://www.yuque.com/yinjianwei/vyrvkf/cky0ic
[MySQL InnoDB _RR_RC]: https://www.yuque.com/yinjianwei/vyrvkf/wig7wg
[643613-20191110202048105-99389438.png]: https://img2018.cnblogs.com/i-beta/643613/201911/643613-20191110202048105-99389438.png
[643613-20191110202802132-253848634.png]: https://img2018.cnblogs.com/i-beta/643613/201911/643613-20191110202802132-253848634.png
[https_dev.mysql.com_doc_refman_5.7_en_server-logs.html]: https://dev.mysql.com/doc/refman/5.7/en/server-logs.html
[643613-20191110203057929-2070066619.png]: https://img2018.cnblogs.com/i-beta/643613/201911/643613-20191110203057929-2070066619.png
[643613-20191110203129719-304171750.png]: https://img2018.cnblogs.com/i-beta/643613/201911/643613-20191110203129719-304171750.png
[643613-20191110203240154-1853830368.png]: https://img2018.cnblogs.com/i-beta/643613/201911/643613-20191110203240154-1853830368.png
[643613-20191110203332214-406719280.png]: https://img2018.cnblogs.com/i-beta/643613/201911/643613-20191110203332214-406719280.png
[643613-20191110203352181-1841024076.png]: https://img2018.cnblogs.com/i-beta/643613/201911/643613-20191110203352181-1841024076.png
[643613-20191110203417728-194285090.png]: https://img2018.cnblogs.com/i-beta/643613/201911/643613-20191110203417728-194285090.png
[MySQL 4]: https://www.cnblogs.com/yanjieli/p/9780100.html
[MySQL_MySQL]: https://blog.csdn.net/da_guo_li/article/details/80280289
[Chapter 15 Alternative Storage Engines]: https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
[MySQL5.7]: https://www.cnblogs.com/jpfss/p/9449104.html