数据库基础（二）【MySQL】

阅读前导：理论上数据库可以在操作系统和网络之前学习，但是这样会让学习层次割裂为两个阶段：第一，会用 SQL 对数据进行 CRUD（增删查改）；第二，理解数据库实现的原理，即知道数据库是如何保证在并发时数据的安全性的。其中第二点在系统学习过操作系统（尤其）和网络后才能有较好的体会。

因此本系列会经常以操作系统的角度来讨论数据库在计算机中的作用。

什么是数据库

数据库是一种用于存储和管理数据的电子化系统，它可以让用户对数据进行各种操作，如查询、修改、删除、分析等。数据库的出现是为了解决数据管理的问题，提高数据的安全性、可靠性、一致性和效率。

数据库是运行在操作系统中的软件

从冯诺依曼体系的角度，数据库是一种软件系统，它运行在计算机硬件上，通过操作系统和驱动程序来访问存储设备上的数据文件。

数据库是一种多进程系统，它由一个或多个进程组成，每个进程负责完成特定的功能。进程是程序执行时的一个实例，它具有自己的地址空间和资源。数据库中常见的进程有以下几种：

• 服务器进程：负责接收客户端进程的请求，并调用相应的模块来处理请求，并将结果返回给客户端进程。服务器进程通常采用多线程模式来提高并发性能。
• 客户端进程：负责向服务器进程发送请求，并接收服务器进程返回的结果。客户端进程通常是用户通过数据库应用程序或工具来发起的，如 SQL Developer、MySQL Workbench、PHPMyAdmin 等。
• 后台进程：负责执行数据库的内部功能，如数据缓存、日志记录、恢复、备份、调度等。后台进程通常是数据库系统自动启动和管理的，如 Oracle 的 PMON、SMON、LGWR 等。

例如，执行 mysql -uroot -p 语句，就是用 MySQL 的客户端连接到 MySQL 的服务端。MySQL 的客户端和服务端在 Linux 中的进程的具体名称分别是：

• MySQL 客户端进程：mysql。这是一个命令行程序，用于与 MySQL 服务器进行交互，可以输入 SQL 语句或者执行 SQL 脚本文件。mysql 进程的参数可以指定连接的服务器地址、端口、用户名、密码等信息。
• MySQL 服务端进程：mysqld。这是一个守护进程，用于接收和处理客户端的请求，以及管理数据库的文件、内存、网络等资源。mysqld 进程的参数可以指定服务器的配置、日志、插件等选项。

可以使用 ps 命令来查看 MySQL 的客户端和服务端进程的信息，例如：

ps -ef | grep mysql

这个命令会显示所有包含 mysql 字符串的进程的详细信息，如进程号、用户、启动时间、命令行等。

第一行的 mysqld 就是 MySQL 服务器，它是一个守护进程运行在后台。第二行的 mysql 就是 MySQL 客户端，它是由名为 xy 的用户执行的。

上面这种方式是用本地的客户端连接到本地的服务端，实际上 MySQL 服务器是一款网络服务器，它可以连接到指定主机中正在运行的 mysqld 服务器。

为什么需要数据库

在 Linux（操作系统）一切皆文件的语义下，运行在操作系统中的所有软件本质上都是文件，那么数据在数据库眼中也是一堆文件。创建一个数据库，一张表，都会在特定目录下创建对应的文件。

理论上，我们可以单纯地用文件来存储和管理数据，但是在面对工业级的场景下，手动维护文件无法保证数据的安全性，也无法保证效率。也就是说，数据库代替程序员做管理数据这件事，是一种用于存储和管理数据的电子化系统，它有许多优点，比如：

• 结构化地存储大量的数据信息，方便用户进行有效的检索和访问。
• 有效地保持数据信息的一致性、完整性、降低数据冗余。
• 可以满足应用的共享和安全方面的要求，例如需要撤销某些错误的操作。
• 能够方便智能化地分析，产生新的有用信息。

结合数据库是一个服务器和客户端分离的管理数据的软件，它是用户和文件之间的软件层，用户使用 SQL 让 MySQL 执行对应的操作，以间接地管理数据。数据以何种方式组织，对上层用户是透明的，用户只需要对数据进行增删查改即可。

这里的“管理”区别于操作系统中对文件的管理，数据库的管理主要是面向业务的，而操作系统需要用一定的数据结构和方式来描述和管理文件，以管理文件的属性，而不关心文件本身保存了什么数据。

有哪些数据库

从数据管理的角度，数据库可以分为以下几种类型：

• 关系型数据库：使用表格的形式来存储和组织数据，每个表格有行和列，每行表示一条记录，每列表示一个属性。关系型数据库使用结构化查询语言（SQL）来操作数据，如 MySQL1、Oracle2、SQL Server 等。
• 非关系型数据库：不使用表格的形式来存储和组织数据，而是使用其他的数据模型，如文档、键值对、图形、列族等。非关系型数据库通常用于处理非结构化或半结构化的数据，如 NoSQL2、MongoDB、Neo4j 等。
• 分布式数据库：将数据分散存储在不同的物理位置或网络上，以提高数据的可用性、容错性和并发性。分布式数据库可以是关系型或非关系型的，如 Hadoop、Cassandra、Redis 等。
• 云数据库：将数据存储在云计算平台上，以利用云服务提供的弹性、可扩展性和成本效益。云数据库可以是传统的数据库软件或者专门为云设计的数据库服务，如 Oracle Cloud Database、Amazon RDS、Google Cloud SQL 等。

根据数据库的存储介质，可以分为以下几种：

• 磁盘数据库：使用磁盘作为主要的数据存储设备，如机械硬盘、固态硬盘等。磁盘数据库的优点是数据持久性高，容量大，成本低。缺点是访问速度慢，需要缓存和索引来提高性能。常见的磁盘数据库有 Oracle, MySQL, SQL Server 等。
• 内存数据库：使用内存作为主要的数据存储设备（因此又称主存数据库，Main Memory Database），如随机存取存储器（RAM）。内存数据库的优点是访问速度快，无需缓存和索引。缺点是数据持久性低，容量小，成本高。常见的内存数据库有 Redis, Memcached, VoltDB 等。
• 光学数据库：使用光学介质作为数据存储设备，如 CD, DVD 等。光学数据库的优点是数据稳定性高，不易受外界干扰。缺点是访问速度慢，容量小，不易修改。光学数据库主要用于数据归档和备份。

光学数据库暂不讨论。

值得注意的是：

1. 磁盘数据库一般用于数据的持久化，但并非用户的所有 SQL 操作都会使数据刷新到磁盘中，而是存放在缓冲区中，在特定时刻刷新到磁盘中。这么做是减少内存和磁盘的 I/O 次数，以提高存储效率。
2. 内存数据库虽然读写速度很快，但并非不使用磁盘，内存数据库的启动信息、初始数据等重要信息都需要存储在磁盘中；当可用内存过少时，会将部分数据写入到磁盘中，以减轻内存压力。

MySQL 的体系架构

MySQL 的架构主要分为网络连接层、数据库服务层、存储引擎层和系统文件层四大部分。

图片来源：https://acronymor.com/posts/mysql/ch01/

MySQL 主要是用 C++ 实现的：

• 服务层包括连接器（Connector）、查询缓存（Cache）、分析器（Parser）、优化器（Optimizer）和执行器（Executor）等。这一层包含了 MySQL 的大部分核心功能以及所有的内置函数（如日期、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器等。

• 存储引擎层负责数据的存储和提取。例如 InnoDB、MyISAM、Memory 等都是存储引擎。

这种架构设计使得服务层成为公用层，而存储引擎层则是多态层，可以按需选择具体的存储引擎。

在 MySQL 中，所有的存储引擎都继承自一个公共的基类。这个基类定义了一些接口和默认行为。每个具体的存储引擎（如 InnoDB、MyISAM 等）都是这个基类的派生类，它们通过重写（覆盖）基类中的方法来实现自己特有的行为。

这种设计使得 MySQL 服务器（作为基类操作的执行者）不需要知道具体正在使用哪个存储引擎，它只需要调用基类定义的接口即可。至于这些接口如何具体执行，则取决于运行时所使用的具体存储引擎实例，这就实现了多态。

网络连接层/API 层

由于 MySQL 是一款 C/S 软件，直接管理数据的主体是 mysqld（服务器），在真实的业务场景中，应用程序和实际的数据库一般是部署在不同的服务器中的，MySQL 客户端和服务器之间的连接通常是通过 TCP/IP 协议进行的。

所以网络连接层也叫 API 层。它负责提供给外部应用程序访问 MySQL 数据库的接口。API 层由 MySQL 提供的各种客户端库组成，包括 C/C++、Java、Python、PHP 等语言的库。

MySQL 客户端是一个命令行程序，也就是说它是一个可执行程序，准确地说，它是采用动态链接生成的可执行程序。

通过 file 命令可以知道 mysql 客户端可执行程序是多态链接的，lld 命令可以查看它依赖的库。

数据库服务层

条目	说明
系统管理和控制工具	提供数据库系统的管理和控制功能，例如对数据库中的数据进行备份和恢复，保证整个数据库的安全性，提供安全管理，对整个数据库的集群进行协调和管理等。
连接池	主要负责存储和管理客户端与数据库的连接信息，连接池里的一个线程负责管理一个客户端到数据库的连接信息。
SQL 接口	主要负责接收客户端发送过来的各种 SQL 命令，并将 SQL 命令发送到其他部分，并接收其他部分返回的结果数据，将结果数据返回给客户端。
解析器	主要负责对请求的 SQL 解析成一棵“语法树”，然后根据 MySQL 中的一些规则对“语法树”做进一步的语法验证，确认其是否合法。
查询优化器	在 MySQL 中，如果“语法树”通过了解析器的语法检查，此时就会由优化器将其转化为执行计划，然后与存储引擎进行交互，通过存储引擎与底层的数据文件进行交互。
缓存	MySQL 的缓存是由一系列的小缓存组成的。例如：MySQL 的表缓存，记录缓存，MySQL 中的权限缓存，引擎缓存等。MySQL 中的缓存能够提高数据的查询性能，如果查询的结果能够命中缓存，则 MySQL 会直接返回缓存中的结果信息。

存储引擎层

存储引擎是数据库底层的组件，是数据库的核心，主要负责数据的写入和读取，与底层的文件进行交互。它规定了数据存储时的存储结构。使用存储引擎可以创建、查询、更新、删除数据库。不同的存储引擎提供的存储方式、索引机制等也不相同。

MySQL 中的存储引擎是插件式的，服务器中的查询执行引擎通过相关的接口与存储引擎进行通信。什么意思呢？就是我们可以指定不同的存储引擎，但是它们的使用方法都是通过同一套上层接口实现的。同时，接口屏蔽了不同存储引擎之间的差异（因为它们使用了 C++的继承和多态）。MySQL 中，最常用的存储引擎就是 InnoDB 和 MyISAM。

条目	InnoDB	MyISAM
事务支持	支持	不支持
存储结构	所有的表都保存在系统表空间，或者每张表各自的表空间	每张表在磁盘上存储成三个文件
存储空间	需要更多的内存和存储，在主内存中建立其专用的缓冲池用于高速缓冲数据和索引	可被压缩，存储空间较小
表锁差异	支持事务和行级锁	只支持表级锁
全文索引	不支持 (FULLTEXT 类型的） 全文索引，但是 innodb 可以使用 sphinx 插件支持全文索引，并且效果更好	支持 (FULLTEXT 类型的） 全文索引
主键	如果没有设定主键或者非空唯一索引，就会自动生成一个 6 字节的主键 （用户不可见），数据是主索引的一部分，附加索引保存的是主索引的值	允许没有任何索引和主键的表存在，索引都是保存行的地址
外键	支持	不支持

MySQL 支持多种不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，你可以根据对数据处理的不同需求选择合适的存储引擎，这样不仅可以提高数据存储和检索的效率，还可以降低高并发情况下的数据压力。

系统文件层

系统文件层主要包括 MySQL 中存储数据的底层文件，与上层的存储引擎进行交互，是文件的物理存储层，是整个系统的核心，负责存储数据库中的数据。存储层由以下几个主要组件组成：

• 表空间：存储数据库中的表、索引、日志等数据。
• 引擎：负责处理数据库的读写操作。
• 缓冲池：存储最近访问过的数据，提高数据访问效率。
• 日志：记录数据库的变更信息，用于数据恢复。

条目	说明
日志文件	包括错误日志、通用查询日志、二进制日志、慢查询日志等
数据文件	db.opt 文件、frm 文件 (MySQL 8.0 无此文件）、MYD 文件、MYI 文件、ibd 文件、ibdata 文件、ibdata1 文件、ib_logfile0 和 ib_logfile1 文件等。
配置文件	在 Unix/Linux 环境中是 my.cnf 文件，在 Windows 环境中是 my.ini 文件。
pid 文件	pid 文件是存放 MySQL 进程运行时的进程号的文件
socket 文件	socket 文件和 pid 文件一样，都是 MySQL 在 Unix/Linux 环境中运行才会有的文件。

什么是 SQL

SQL 是 Structured Query Language 的缩写，即结构化查询语言。它是一种用于数据库管理系统（DBMS）的计算机语言，用于存储、检索和管理数据库中的数据。SQL 是关系数据库管理系统 (RDBMS) 的标准语，由 ISO（国际标准组织）定义。

SQL 通常可以分为以下几类：

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。例如，CREATE DATABASE 用于创建新数据库，CREATE TABLE 用于创建新表，ALTER TABLE 用于修改表结构，DROP TABLE 用于删除表。
• DML（Data Manipulation Language）：数据操作语言，用来对数据库记录（数据）进行操作。例如，INSERT INTO 用于插入新数据，UPDATE 用于更新已有数据，DELETE FROM 用于删除数据。
  • DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。如 SELECT 用于查询数据。
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。例如，GRANT 用于授予用户权限，REVOKE 用于撤销用户权限，COMMIT 用于提交事务。

这些都是 SQL 的主要组成部分，每一种都有其特定的用途和语法。

其中，DQL 在一定程度上可以被视为 DML 的一部分。在查询语句还没有太过复杂时，查询语句是属于 DML 的。但随着查询语句逐渐细化增多，查询语句被单独提出来作为 DQL 进行学习。

参考资料

• https://acronymor.com/posts/mysql/ch01/
• https://blog.csdn.net/chenlong_cxy/article/details/128055520