DBA | MySQL 数据库前置知识与概述学习笔记

大家好，我是?WeiyiGeek，一名深耕安全运维开发（SecOpsDev）领域的技术从业者，致力于探索DevOps与安全的融合（DevSecOps），自动化运维工具开发与实践，企业网络安全防护，欢迎各位道友一起学习交流、一起进步 ，若此文对你有帮助，一定记得倒点个关注?与小红星??，收藏学习不迷路??。

前言简述

描述：无论你是前端还是后端亦或是运维，只要是一个合格的开发者/数据库管理者，对于MySQL这个名词相信都不陌生，MySQL 目前是主流的关系型数据库，无论你是大前端，亦或是Java、Go、Python、C/C++、PHP....等这些语言的后端程序员，乃至运维人员，对于MySQL是必然要掌握的核心技术之一，程序员不能没有MySQL，用一句话形容很贴切：“天不生我MySQL，编程万古如长夜”。

原文来源于公众号《全栈工程师修炼指南》，学习更多知识请关注作者哟！

前面已经和小伙伴们一起简单的学习数据库基础知识，以及 SQL 的相关知识，相信大家都有所收获吧，正好趁着这次的机会，作者《WeiyiGeek》整理了相关笔记，现在我们继续来学习使用最广泛的MySQL数据库管理系统的相关知识，作者将以初学者的视角为切入，带领各位看友从初学、到进阶再到企业运维实践，希望大家能够有所收获，最后，若文章还不错的化，请您动动手指关注《公众号：全栈工程师修炼指南》以及点点红心与转发。

废话不多说，直接进入主题，在学习 MySQL 数据库之前，我们先来回顾一下数据库的基础知识。

原文链接:?https://articles.zsxq.com/id_asji0hmtnm1a.html

原文来源于公众号《全栈工程师修炼指南》，学习更多知识请关注作者哟！

前置知识

基本概念

数据（Data）：指对客观事物进行描述并可以鉴别的符号，这些符号是可识别的、抽象的。它不仅指狭义上的数字，而是有多种表现形式：字母、文字、文本、图形、音频、视频等，例如：学生信息数据。

数据库（Database）：是数据管理的有效技术，是由一批数据构成的有序集合，这些数据被存放在结构化的数据表里。数据表之间相互关联，反映客观事物间的本质联系，例如：存储学生信息表，学生成绩表。

数据库管理系统（Database Management System，DBMS）：是用来定义和管理数据的软件，例如：MySQL、Oracle、MongoDB。

数据库应用程序（Database Application System，DBAS）：是在数据库管理系统基础上，使用数据库管理系统的语法，开发的直接面对最终用户的应用程序，例如：Oracle 的 PLSQL。

数据库管理员（Database Administrator，DBA）：是指对数据库管理系统进行操作的人员，其主要负责数据库的运营和维护，例如：运维人员。

总结：数据库就是一个文件系统，它是数据的结构化集合，按照数据结构来组织、存储和管理数据库的建立在计算机存储设备上的仓库; 数据库管理系统是建立在操作系统之上，专门管理数据库的软件系统;数据库管理员通过数据库管理系统对数据进行操作和管理;数据库应用程序通过数据库管理系统实现对数据的管理和访问。

原文来源于公众号《全栈工程师修炼指南》，学习更多知识请关注作者哟！

数据库分类

关系型数据库（Relational Database）：是基于关系模型的数据库，通过二维表及其之间的联系所组成数据组来存储数据，可采用结构化查询语言 SQL 对数据库进行 CRUD (?增删改查 )，例如：MySQL、Oracle、SQL Server 等传统关系型数据库。

优点：使用方便（兼容 SQL），易于维护（二维表结构），支持SQL标准，满足多个表间的复杂查询、操作等。

缺点：海量数据读写性能较差（相对，集中式），表结构字段类型在创建时已然固定，高并发场景下传统关系型数据库磁盘I/O瓶颈较为突出。

非关系型数据库（Non-Relational Database）：也称为 NoSQL 数据库，是一种数据结构化存储方 法的集合，可以是文档(DOC)或者键值对(KV)等，例如：MongoDB、Redis 。

优点：格式灵活、读写性能好（数据在内存或随机存储器中），易于扩展，运行成本低。

缺点：不提供事务支持，不支持SQL传统语法（额外的学习成本），数据结构冗杂复制查询方面稍欠。

总结：在实际开发应用中，两者各有优缺点，通常会将二者结合使用，例如：Redis 存储临时高频访问的KV数据，而 MySQL 存储关键核心的表数据。

面试题：关系型数据库模型是将复杂的数据结构用(二维表结构)来表示。

面试题：数据库管理系统是用来定义和(管理数据)的软件。

原文来源于公众号《全栈工程师修炼指南》，学习更多知识请关注作者哟！

什么是 SQL ?

SQL, 即结构化查询语言?Structured Query Language, 是一种用于访问和处理关系型数据库的标准语言,用于存取数据以及查询、更新和管理关系数据库系统。

自其问世以来得到了广泛的应用，不仅著名的大型商用数据库产品，如 Oracle、DB2、Sybase、SQL Server 等兼容支持它，很多开源的数据库产品如 PostgreSQL、MySQL、MariaDB 等也兼容支持它，甚至一些小型的产品如 Access 也支持 SQL 语言。另外，近些年蓬勃发展的 NoSQL 数据库系统最初是宣称不再需要 SQL 的，后来也不得不修正为 Not Only SQL，来拥抱 SQL，所以从这里凸显出学SQL语法就是学习数据库管理操作之基，重要性不言而喻。此外为了加强SQL的语言能力，各数据库厂商增强了过程性语言的特征，例如 Oracle 的?PL/SQL?过程性处理能力, 以及 SQL Server、Sybas 的T-SQL?能力等;

SQL 是 1986 年 10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了 SQL 正式国际标准。1989 年 4 月，ISO 提出了具有完整性特征的 SQL89 标准，1992 年 11 月又公布了 SQL92 标准，在此标准中，把数据库分为三个级别：基本集、标准集和完全集。在 1999 年推出 99 版标准，目前最新版本为 SQL2023 版。比较有代表性的几个版本：SQL86、SQL92、SQL99。

原文来源于公众号《全栈工程师修炼指南》，学习更多知识请关注作者哟！

SQL 语言主要分为如下五类。

数据查询语言（DQL：Data Query Language）其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。关键字 SELECT 是 DQL（也是所有 SQL）用得最多的动词。

数据操作语言（DML：Data Manipulation Language）其语句包括动词 INSERT，UPDATE 和 DELETE。它们分别用于添加，修改和删除表中的行。

数据定义语言（DDL：Data Definition Language）定义数据库对象语言，其语句包括动词 CREATE 和 DROP 等。

数据控制语言（DCL：Data Control Language）它的语句通过 GRANT 或 REVOKE 获得许可，确定用户对数据库对象的访问。

事务控制语言（TCL：Transaction Control Language）它的语句能确保被 DML 语句影响的表的所有行及时得以更新，其语句包括 COMMIT 和 ROLLBACK、SETPOINT。

weiyigeek.top-SQL语言分类图

特别注意：DML 包含 INSERT、UPDATE 和 DELETE 主要针对表中的数据，而 DDL 包含 CREATE、ALTER、DROP 主要针对数据库对象，例如：数据库（Database）、表（Table）、索引（Index）、视图（View）、存储过程（Procedure）、触发器（Trigger）等。

温馨提示：由于作者在前面的文章中已经对 SQL 相关知识进行了讲解，所以这里就不再赘述了，大家可访问之前的文章进行复习回顾，链接如下：

DBA | SQL 结构化查询语言介绍： https://articles.zsxq.com/id_jcwpnaa75tc5.htmlDBA | SQL 结构化查询语言基础概论与分类： https://articles.zsxq.com/id_j57gx0mtvrjd.html

原文来源于公众号《全栈工程师修炼指南》，学习更多知识请关注作者哟！

基础知识

MySQL 简介

描述：MySQL 是一个开源的关系型数据库管理系统(Relational Database Management System，最开始由瑞典 MySQL AB 公司开发，MySQL 以联合创始人?Monty Widenius(MySQL之父)?长期以女儿名“my”为前缀的众多指南和库工具，共同构成了MySQL这一名字的由来; 后被?Sun Microsystems?公司收购，因 Sun 公司并于 2010 年被 Oracle 公司以 74 亿美元收购，所以目前属于?Oracle?旗下产品，由?Oracle Corporation?主持开发，分发和支持。

MySQL 软件提供了一个非常快速，多线程，多用户和强大的SQL（结构化查询语言）关系型数据库管理系统其适用于任务关键型、重负载的生产系统，以及嵌入到大规模部署的软件中。其关系型数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

知识扩展：MySQL 官方发音为"My Ess Que Ell [??s kju???l]", 但通常大家为了偷懒，会少读一个音节，读 My sequel [?si?kw?l] 发音，其中 sequel 表示?Structured English Query Language?意为结构性英语查询语言，但由于 sequel 是一家航空公司的商标，又更改为 squel 表示?Structured Query Language?意为结构性查询语言简称 SQL，所以在国内就往往读为 My SQL;

知识扩展：MySQL 采用海豚标志，其名为“sakila”，此名字是由?Ambrose Twebaze，一位来自非洲斯威士兰的开源软件开发者，在用户参与的“海豚命名”竞赛中脱颖而出而获得。据Ambrose介绍，Sakila 源自斯威士兰的方言SiSwati，并寓意着坦桑尼亚Arusha小镇的美好回忆。

官方网站：https://www.mysql.com/

官方文档：https://dev.mysql.com/doc/refman/8.4/en/what-is-mysql.html

MySQL 特点

MySQL 采用 GPL 协议开源，可自行修改源码来开发实现满足自身需求的 MySQL 定制版本。

MySQL 支持标准的 SQL 通用语言，以及多种数据类型、高度优化的类库实现 SQL 函数。

MySQL 支持多种存储引擎，例如：InnoDB、MyISAM 等。

MySQL 支持事务处理、存储过程和触发器等高级数据库功能。

MySQL 支持大型系统的数据库，32 位系统表文件最大可支持 4GB ，64 位系统支持最大的 表文件为 8TB。

MySQL 使用 C 和 C++ 编写，支持多种操作系统多环境移植、部署，如：桌面系统、服务器核嵌入式系统，和多语言客户端程序，如：C、C++、Go、Python、Java、Perl、PHP 等开发语言。

MySQL 非常快速、可靠、可扩展且易于使用，支持主从、高可用、集群化架构部署，满足企业场景使用。

MySQL 支持多线程，充分利用 CPU 资源，支持高并发访问。

MySQL 拥有大量实用的数据库管理以及客户端工具，例如：MySQL Workbench、MySQL Query Browser、MySQL HeatWave 等。

原文来源于公众号《全栈工程师修炼指南》，学习更多知识请关注作者哟！

MySQL 分类

当前 MySQL 采用双许可证模式（即: GPLv2 开源许可证和商业许可证）发布，用户可以根据 GNU 通用公共许可证 的条款选择将MySQL软件作为开源产品使用，也可以从Oracle购买标准商业许可证。

所以当下 MySQL 主要有两大版本：社区版（开源）和企业版（商业），二者在核心功能上并无太大差异，主要体现在企业版提供了额外的扩展、技术支持与服务。

社区版（开源）

MySQL Community Server（社区 Community 版本）：遵循GPL协议完全开源免费，社区版也支持多种数据类型和标准的SQL查询语言，能够对数据进行各种查询、增加、删除、修改等操作，所以一般情况下社区版就可以满足开发需求了。

企业版（商业）

MySQL Enterprise Server（企业 Enterprise 版本）：主要用于商业用途，即使在开发中需要用到一些付费的附加功能，其价格相对于昂贵的 Oracle、DB2 等也是有很大优势的，对数据库可靠性要求比较高的企业可以选择企业版。

原文来源于公众号《全栈工程师修炼指南》，学习更多知识请关注作者哟！

特别注意：MySQL 在引用?Memcached.pm?的源代码时有如下声明（Artistic license 就是更为宽松且没有传染性的开源许可证，允许修改或引用其源代码后不开源），基于以上事实，再加上当第三方向 Oracle MySQL 贡献源代码时，都必须签署 Oracle 公司 的 OCA 协议（Oracle Contributor Agreement），声明放弃所贡献的源代码的知识产权， 这部分源代码的知识产权归 Oracle 公司所有，于是 Oracle 公司对 MySQL 源代码拥有了绝对的控制权与知识产权，Oracle 公司有权决定 MySQL 基于何种许可证分发，存在闭源风险。

为了应对这一风险，其 MySQL 之父 Monty Widenius 主导开发了 MySQL 的分支 (兄弟) 开源版本 MariaDB，并由 MariaDB Foundation 管理维护，MariaDB 不仅继承了 MySQL 的优点，还通过引入新功能和优化来提高性能，由于 MariaDB 社区版也完全开源免费，当下的一些操作系统发行版中已将原有的 MySQL 使用 MariaDB 进行替换 ，另外，许多企业和服务提供商，如阿里云，都采用了 MariaDB 作为其数据库解决方案的一部分。

不过，这并不妨碍我们当下学习 MySQL 数据库相关，其毕竟是目前排名第二的关系型数据库，市场占用率广，其次对于数据库基础知识以及SQL使用操作都是一致的，可谓是一通百通，此外 MariaDB 其高度兼容MySQL的大部分功能，所以二者可以互为替代。

知识扩展：常见的商业化数据库有那些？

Oracle : http://www.oracle.com/index.html微软的SQLServer: http://www.microsoft.com/SQL/default.mspxIBM 的DB2 : http://www-306.ibm.com/software/data/db2/9/download.htmlSybase : http://www.sybase.com/达梦(国产): http://www.dameng.com/

知识扩展：常见的开源的数据库有那些？

MySQL:http://www.mysql.com/SQLite:http://www.sqlite.org/HSQLDB 纯Java小型数据库:http://www.hsqldb.org/PostgreSQL:http://www.postgresql.org/Redis: https://redis.ioOpenGuass(国产): https://opengauss.org/zh/

更多流行的数据库产品实时排名，请参考：https://www.db-engines.com/en/ranking

总结：首先我们了解到最终用户完全可以使用及分发MySQL开源社区版本的；其次最终用户和软件开发商应大力支持基于 MySQL 的商业发行版，但需要通晓相关协议和规则，MySQL内核部分必须遵循开源协议；最后在发展国内 MySQL 开源分支时，只要遵循 GPLv2 的所有规定和约束，就不会触发知识产权层面的争议，我们可以合法的自由使用 Oracle 发布的 MySQL 源代码。

参考来源：

七问七答理清MySQL开源许可 - https://zhuanlan.zhihu.com/p/523166193

MySQL 版本

描述：与大多数开源软件一样，MySQL 也经历了多个版本迭代更新，从最初的 MySQL 1.0 到最新的 MySQL 8.0 系列，MySQL 数据库的架构经历了多次演变，以社区版本为例其版本命名规则为主版本.子版本.修订版，例如：5.7.44、8.0.43?等。

当前MySQL的发布模式分为两个主要部分：LTS（长期支持）和创新，其中LTS和Innovation版本都包含错误和安全修复，并被视为生产级质量，当前 MySQL 版本发布计划如下所示：

截止本文章发布前 MySQL Community 版本：

5.7.x 系列 (当前版本 5.7.44) - 长期支持版本（5.7 版本升级推荐）8.x 系列 (当前版本 8.0.43) - 长期支持版本（生产环境推荐）9.x 系列 (当前版本 9.4.0) - 创新版本（新特性，开发测试环境推荐）

温馨提示：MySQL 不建议跨大版本升级，例如：从 5.7 直接升级到 8.0，中间需要经过多次小版本的迭代更新，否则可能会遇到兼容性问题；若是在要跨大版本升级，实际上更好的方式是直接在新的环境中安装新版本的 MySQL，然后导入旧版本的数据，验证是否兼容性等问题。

原文来源于公众号《全栈工程师修炼指南》，学习更多知识请关注作者哟！

MySQL 8.x 与 5.7.x 版本到底有什么区别?

1.速率快：MySQL 8.x 的速度要比 MySQL 5.7 快 2 倍 (该理由足以让你在新环境中使用 MySQL 8.x)

2.性能优: 读/写工作负载、IO 密集型工作负载、以及高竞争（"hot spot"热点竞争问题）工作负载都进行了相应优化。

3.NoSQL 支持：从 5.7 版本开始提供 NoSQL 存储功能，在8.x系列版本得到了更大的改进该项功能消除了对独立的 NoSQL 文档数据库的需求，而 MySQL 文档存储也为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性。

4.窗口函数: 自 8.0 版本引入，它可以用来实现若干新的查询方式；它与 SUM()、COUNT() 这种集合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多行当中，通常与 PARTITION BY 关键字联用。

5.隐藏索引: 从 8.0 起支持索引可以被“隐藏”和“显示”，当对索引进行隐藏时，它不会被查询优化器所使用，可用此特性进行性能调试； 如: 我们先隐藏一个索引，然后观察其对数据库的影响。如果数据库性能有所下降，说明这个索引是有用的，然后将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的可以考虑删掉。

6.降序索引: 从 8.0 起为索引提供按降序方式进行排序的支持，此中索引中的值也会按降序的方式进行排序;

7.utf8mb4 缺省编码: 从 8.0 开始使用?utf8mb4?作为 MySQL 的默认字符集，占用四个字节。

8.JSON 支持: 从 8.0 大幅改进了对 JSON 的支持，添加了基于路径查询参数从 JSON 字段中抽取数据的?JSON_EXTRACT()?函数，以及用于将数据分别组合到 JSON 数组和对象中的JSON_ARRAYAGG() 和 JSON_OBJECTAGG()聚合函数(重点关注)

9.可靠性高 :InnoDB 现在支持表 DDL 的原子性(InnoDB 表上的 DDL 也可以实现事务完整性了)，要么失败回滚要么成功提交，不至于出现 DDL 时部分成功的问题，此外还支持 crash-safe 特性，元数据存储在单个事务数据字典中(重点关注)

10.安全性高: 对 OpenSSL 的改进、新的默认身份验证、SQL角色、密码强度、授权。

11.高可用性(High Availability): 从 8.x 起支持InnoDB 集群为您的数据库提供集成的原生 HA 解决方案。

12.通用表表达式(Common Table Expressions CTE)?支持: 是一个命名的临时结果集，它存在于单个语句的范围内，在复杂的查询中使用嵌入式表时，使用 CTE 使得查询语句更清晰。

13.数据目录：从 MySQL 8.0 起不支持通过在数据目录（/var/lib/mysql）下手动创建目录（例如，使用 mkdir test）来创建 test 数据库目录 。

原文来源于,公众号《全栈工程师修炼指南》，学习更多知识请关注作者哟！

参考资料:

MySQL 创新和 LTS 概述：https://dev.mysql.com/doc/refman/8.4/en/mysql-releases.html

MySQL 8.0 新特性概览: https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

MySQL 5.7 新特性概览: https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

---

MySQL 架构

描述：为了给后续深入学习与优化 MySQL 相关知识打下基础，我们先来了解下 MySQL 的架构，毕竟只有先对MySQL的整体架构有了一个宏观的认知，才能更好的理解每个细节点的知识。

例如，下图从上往下看，依次会分为网络连接层、系统服务层、存储引擎层、以及文件系统层，往往编写SQL后，都会遵守着MySQL的这个架构往下走。

连接层：主要是指数据库连接池，会负责处理所有客户端接入的工作。服务层：主要包含SQL接口、解析器、优化器以及缓存缓冲区四块区域。存储引擎层：这里是指MySQL支持的各大存储引擎，如InnoDB、MyISAM等。文件系统层：涵盖了所有的日志，以及数据、索引文件，位于系统物理硬盘上。

weiyigeek.top-MySQL 架构图

另外，除了上述的四层外，还有客户端它是一种复合概念包含了不同的编程语言编写的后端查询存储应用程序以及所调用的API接口；它可以是各类编程语言，如?Java（JDBC）、Go、Python、C/C++、PHP（PDO）、Node、.Net....，也可以是一些数据库的可视化软件，例如?Navicat、SQLyog等，也可以是?mysql-cli?命令行工具。总之，只要能与MySQL建立网络连接，都可以被称为是MySQL的客户端。

此外，MySQL-Server 就是上述图中的那玩意儿，一般来说，客户端负责编写SQL，而服务端则负责SQL的执行与数据的存储。

---

网络连接层

MySQL 的连接通常基于TCP/IP协议建立网络连接，因此凡是可以支持TCP/IP的语言，几乎都能与MySQL建立连接，此外MySQL还支持另一种连接方式，就是Unix系统下的Socket直连，但这种方式一般使用的较少。

当一个客户端尝试与 MySQL 建立连接时，MySQL 内部都会派发一条线程负责处理该客户端接下来的所有工作，而数据库的连接层负责的就是所有客户端的接入工作。

例如，我们在终端中使用mysql-cli命令行工具连接MySQL数据库，指令如下：

# 命令
mysql -h 127.0.0.1 -uroot -p123456
mysql -h 127.0.0.1 -uroot -p ?# 交互式输入密码（推荐更安全）

# 参数
-h 表示MySQL所在的服务器IP地址
-u 表示本次连接所使用的用户名
-p 则代表着当前用户的账号密码

步骤 01.当执行这条指令后，将开始与 MySQL-Server 建立网络连接，也就是会经历TCP的三次握手过程，此外，MySQL也支持SSL加密连接，如果采用这种方式建立连接，那还会经过SSL多次握手过程，当握手结束，网络建立成功后，则会开始正式的数据库连接建立工作。

步骤 02.当TCP网络连接建立成功后，MySQL服务端与客户端之间会建立一个session会话，紧接着会对登录的用户名和密码进行效验，MySQL首先会查询自身的用户表信息，判断输入的用户名是否存在，如果存在则会判断输入的密码是否正确，如若密码错误或用户名不存在就会返回1045的错误码，如下信息：ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

步骤 03.当认证通过数据库连接建立成功后，MySQL 会为该客户端分配一个线程，MySQL与客户端之间会采用半双工的通讯机制工作，与之对应的还有“全双工、单工”（与网工概念一致）的工作模式，此外通过show processlist;?命令查询所有正在运行的线程，以及?show full processlist;?命令查看到进程执行的SQL语句，这条指令对于以后做线上排查时有很大的作用，目前先简单了解，后续作者将会运维实践中讲解。

全双工：代表通讯的双方在同一时间内，即可以发送数据，也可以接收数据。半双工：代表同一时刻内，单方要么只能发送数据，要么只能接受数据。单工：当前连接只能发送数据或只能接收数据，也就是“单向类型的通道”。

原文来源.于公众号《全栈工程师修炼指南》，学习更多知识请关注作者哟！

数据库连接池(Connection Pool)

MySQL 数据库是一个单进程多线程的应用程序，所有的客户端连接都需要一条线程去维护，而线程资源无论在哪里都属于宝贵资源，因此不可能无限量创建，所以这里的连接池就相当于Tomcat中的线程池，主要是为了复用线程、管理线程以及限制最大连接数的。

weiyigeek.top-数据库连接缓存池层

在 MySQL 中通过?max-connections?参数来控制连接池的最大线程数，当客户端连接数超过这个值，则会拒绝新的连接请求；对于不同的机器配置，可以适当的调整连接池的最大连接数大小，以此可以在一定程度上提升数据库的性能, 命令如下：

# 查询目前数据库的最大连接数
show variables like?'%max_connections%';

# 修改数据库的最大连接数为指定值
set?GLOBAL max_connections = 1024;

除了可以查询最大连接数外，MySQL本身还会对客户端的连接数进行统计，对于这点可以通过命令show status like "Threads%";查询：

总结：连接池的优化思想与Java线程池相同，会将数据库创建出的连接对象放入到一个池中，一旦出现新的访问请求会复用这些连接，一方面提升了性能，第二方面还节省了一定程度上的资源开销。

---

系统服务层

MySQL 大多数核心功能都位于这一层，包括客户端SQL请求解析、语义分析、查询优化、缓存以及所有的内置函数（例如：日期、时间、统计、加密函数...），所有跨引擎的功能都在这一层实现，譬如存储过程、触发器和视图等一系列服务。

SQL 接口

SQL 接口组件：主要作用就是负责处理客户端的SQL语句，当客户端连接建立成功之后，会接收客户端的SQL命令，比如 DML（数据库操作语句）、DDL (数据库定义语句) 语句以及存储过程、触发器等; 当收到要执行的SQL语句时，SQL接口会将其分发给其他组件，然后等待接收执行结果的返回，最后会将其返回给客户端。

简单来说，SQL接口会作为客户端连接传递SQL语句时的入口，并且作为数据库返回数据时的出口。

存储过程（Procedure）：是指提前编写好的一段较为常用或复杂SQL语句，然后指定一个名称存储起来，然后先经过编译、优化，完成后，这个“过程”会被嵌入到MySQL中，所以其本质就是一段预先写好并编译完成的SQL。

触发器（Trigger）：是一种特殊的存储过程，它可由某个事件主动触发执行，而非手动调用后才执行，在MySQL中支持INSERT、UPDATE、DELETE三种事件触发，同时也可以通过AFTER、BEFORE语句声明触发的时机，是在操作执行之前还是执行之后。

说简单一点，MySQL触发器类似于Spring框架中的AOP切面。

同样这里，点到为止，后续会专门介绍MySQL的存储过程、触发器、视图等这些特殊的操作。

原文来源于v公众号《全栈工程师修炼指南》，学习更多知识请关注作者哟！

解析器

客户端连接发送的SQL语句，经过SQL接口后会被分发到解析器，解析器这东西其实在所有语言中都存在，Java、C、Go...等其他语言都有，解析器的作用主要是做词法分析、语义分析、语法树生成...这类工作的。

解析器的主要功能是为了验证SQL语句是否正确，以及将SQL语句解析成MySQL能看懂的机器码指令，简单的说：

# 例如，我们编写如下一条SQL语句
select * form user;

# 运行会得到如下错误信息：
ERROR 1064 (42000): You have an error?in?your SQL syntax; check....

在上述SQL中，我们将from写成了form，结果运行时MySQL提示语法错误了，MySQL是如何发现的呢？就是在词法分析阶段，检测到了存在语法错误，因此抛出了对应的错误码及信息。当然，如果SQL正确，则会进行下一步工作，生成MySQL能看懂的执行指令。

优化器

优化器的主要职责在于生成执行计划，比如选择最合适的索引，选择最合适的join方式等，最终会选择出一套最优的执行计划。

当然，在这里其实有很多资料也会聊到，存在一个执行器的抽象概念，实际上执行器是不存在的，因此前面聊到过，每个客户端连接在MySQL中都用一条线程维护，而线程是操作系统的最小执行单位，因此所谓的执行器，本质上就是线程本身。

优化器生成了执行计划后，维护当前连接的线程会负责根据计划去执行SQL，这个执行的过程实际上是在调用存储引擎所提供的API。

缓存&缓冲

主要分为了读取缓存与写入缓冲，读取缓存主要是指select语句的数据缓存，当然也会包含一些权限缓存、引擎缓存等信息，但主要还是select语句的数据缓存，MySQL会对于一些经常执行的查询SQL语句，将其结果保存在Cache中，因为这些SQL经常执行，因此如果下次再出现相同的SQL时，能从内存缓存中直接命中数据，自然会比走磁盘效率更高，对于Cache是否开启可通过命令查询。

-- 查询缓存是否开启
showglobalvariableslike"%query_cache_type%";
-- 查询缓存的空间大小
showglobalvariableslike"%query_cache_size%";
-- 查询缓存相关的统计信息
showstatuslike'%Qcache%';

weiyigeek.top-缓存&缓冲层

特别注意，在 MySQL 5.7 与 MariaDB 版本对于缓存信息依旧可以查询到，但是在高版本的 MySQL 8.x 中，移除了查询缓存区，毕竟命中率不高，而且查询缓存这一步还要带来额外开销，同时一般程序都会使用Redis做一次缓存，因此结合多方面的原因就移除了查询缓存的设计。

weiyigeek.top-MySQL 5.7与8.x的查询缓存差异图

了解了查询缓存后，再来看看写入缓冲，这也是我说的比较有趣的点，缓冲区的设计主要是：为了通过内存的速度来弥补磁盘速度较慢对数据库造成的性能影响。在数据库中读取某页数据操作时，会先将从磁盘读到的页存放在缓冲区中，后续操作相同页的时候，可以基于内存操作。即：通过缓冲区能减少大量的磁盘IO，从而进一步提高数据库整体性能。毕竟每次操作都走磁盘，性能自然上不去的。

一般来说，当你对数据库进行写操作时，都会先从缓冲区中查询是否有你要操作的页，如果有，则直接对内存中的数据页进行操作（例如修改、删除等），对缓冲区中的数据操作完成后，会直接给客户端返回成功的信息，然后MySQL会在后台利用一种名为Checkpoint的机制，将内存中更新的数据刷写到磁盘。

特别注意：在 MySQL 8.x 中，虽然移除了查询缓存的设计，但是依旧保留了写入缓冲的设计，只不过写入缓冲的默认大小是0，也就是说默认不开启。此外，缓冲区是与存储引擎有关的，不同的存储引擎实现也不同，比如?InnoDB?的缓冲区叫做?innodb_buffer_pool，而MyISAM则叫做key_buffer。

---

存储引擎层

存储引擎是 MySQL 中最重要的一层，在前面的服务层中，聚集了MySQL所有的核心逻辑操作，而引擎层则负责具体的数据操作以及执行工作。存储引擎是MySQL数据库中与磁盘文件打交道的子系统，不同的引擎底层访问文件的机制也存在些许细微差异，引擎也不仅仅只负责数据的管理，也会负责库表管理、索引管理等，MySQL中所有与磁盘打交道的工作，最终都会交给存储引擎来完成。

若有小伙伴了解过 Oracle、SQLServer 等数据库的实现，应该会发现这些数据库只有一个存储引擎，因为它们是闭源的，所以仅有官方自己提供的一种引擎。而MySQL则因为其开源特性，所以存在很多很多款不同的存储引擎实现，MySQL为了能够正常搭载不同的存储引擎运行，因此引擎层是被设计成可拔插式的，也就是可以根据业务特性，对算法和IO执行效率根据不同场景进行选择，为自己的数据库选择不同的存储引擎。

weiyigeek.top-MySQL存储引擎层

MySQL目前有非常多的存储引擎可选择，其中最为常用的则是?InnoDB?（缺省）与?MyISAM?（老版本）引擎，可以通过?show variables like '%storage_engine%';命令来查看当前所使用的引擎。

# MySQL 5.7.44 与 MySQL 8.x 的存储引擎差异
mysql5> show variables like?'%storage_engine%';
+----------------------------------+--------+
| Variable_name ? ? ? ? ? ? ? ? ? ?| Value ?|
+----------------------------------+--------+
| default_storage_engine ? ? ? ? ? | InnoDB |
| default_tmp_storage_engine ? ? ? | InnoDB |
| disabled_storage_engines ? ? ? ? | ? ? ? ?|
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows?inset, 1 warning (0.00 sec)

mysql8> show variables like?'%storage_engine%';
+---------------------------------+-----------+
| Variable_name ? ? ? ? ? ? ? ? ? | Value ? ? |
+---------------------------------+-----------+
| default_storage_engine ? ? ? ? ?| InnoDB ? ?|
| default_tmp_storage_engine ? ? ?| InnoDB ? ?|
| disabled_storage_engines ? ? ? ?| ? ? ? ? ? |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows?inset?(0.00 sec)

知识扩展：MySQL的存储引擎主要分为官方版和民间版，前者是MySQL官方开发的，后者则是第三方开发的。存储引擎在MySQL中，相关的规范标准被定义成了一系列的接口，如果你也想要使用自己开发的存储引擎，那么只需要根据MySQL AB公司定义的准则，编写对应的引擎实现即可。

---

文件系统层

数据库中的数据最终都是存储在磁盘上的，而文件系统本质上就是基于机器物理磁盘的其中包含了配置文件、库表结构文件、数据文件、索引文件、日志文件等各类MySQL运行时所需的文件，这一层的功能比较简单，也就是与上层的存储引擎做交互，负责数据的最终存储与持久化工作。

原文来源于公众号《全栈工程师修炼指南》，学习更多知识请关注作者哟！

日志模块

下面列出了MySQL中较为常见的七种日志，但实际上还存在很多其他类型的日志，不过一般对调优、排查问题、数据恢复/迁移没太大帮助，用的较少，因此不再列出。

binlog 二进制日志：主要记录MySQL数据库的所有写操作（增删改）。

redo-log 重做/重写日志：MySQL崩溃时，对于未落盘的操作会记录在这里面，用于重启时重新落盘（InnoDB专有的）。

undo-logs 撤销/回滚日志：记录事务开始前[修改数据]的备份，用于回滚事务。

error-log 错误日志：记录MySQL启动、运行、停止时的错误信息。

general-log 常规日志：主要记录MySQL收到的每一个查询或SQL命令。

slow-log 慢查询日志：主要记录执行时间较长的SQL。

relay-log 中继日志：主要用于主从复制做数据拷贝。

数据模块

MySQL 的所有数据最终都会落盘（写入到磁盘），而不同的数据在磁盘空间中，存储的格式也并不相同，MySQL 中常见的数据文件类型如下：

db.opt 文件：主要记录当前数据库使用的字符集和验证规则等信息。

.frm 文件：存储表结构的元数据信息文件，每张表都会有一个这样的文件。

.MYD 文件：用于存储表中所有数据的文件（MyISAM引擎独有的）。

.MYI 文件：用于存储表中索引信息的文件（MyISAM引擎独有的）。

.ibd 文件：用于存储表数据和索引信息的文件（InnoDB引擎独有的）。

.ibdata 文件：用于存储共享表空间的数据和索引的文件（InnoDB引擎独有）。

.ibdata1 文件：这个主要是用于存储MySQL系统（自带）表数据及结构的文件。

.ib_logfile0/.ib_logfile1 文件：用于故障数据恢复时的日志文件。

.cnf/.ini MySQL的配置文件:，Windows下是.ini，其他系统大多为.cnf。

总结：通过参考[竹子爱熊猫]作者的《（一）全解MySQL之架构篇：自顶向下深入剖析MySQL整体架构》文章，结合自身理解，对MySQL的整体架构有了更深的理解，这也为后续文章的学习打下坚实的基础。

 

这里也是学习底层、源码、原理、调优等知识的一个小技巧，如果只关注于某一个点，很容易出现“不识庐山真面目，只缘身在此山中”的情况，好比你想要研究“庐山”，但是一上来就抓着里面的某颗松树往死里钻，这定然是不妥的，更应该的是先从整体出发，先将整个庐山的面貌看清楚，最后再依次根据所观察到的全貌，逐步研究每个节点上的细节。

借用其作者的一句话结尾：学习底层原理、源码实现，亦或是做性能调优、线上排查，一定要遵循“先理主干，再扣细节”的方式。

 

MySQL 数据模型

描述：前面讲解了 MySQL的整体架构，接下来我们来聊一聊MySQL的数据模型，不过在此之前，我们先来聊一聊关系模型。

关系模型

在1970年的开创性论文“大型共享数据库的数据关系模型”中，E. F. Codd 定义了一个基于数学集合论的关系模型。 关系模型是在概念模型的基础上所建立的适用于具体数据库实现的一种数据模型，其数学理论基础是建立在集合代数上的，与层次模型、网状模型相比较，是目前应用最为广外的一种重要的数据模型。

关系型数据库（RDBMS）

建立在关系模型基础上,使用表格（也称为关系）来存储实体数据,由多张相互连接的二维表共同组成的数据库。而所谓二维表，指的是由行和列组成的表，表中的每一行被称为一条记录，类似于Excel表格数据，有表头、有列、有行，还可以通过一列关联另外一个表格中的某一列数据。

关系型数据库的核心思想是将数据组织成关系，从而使得数据的管理和查询更加高效和灵活。

MySQL 就是一个典型的关系型数据库，其数据模型是基于二维表进行数据存储的，其特点是格式统一，便于维护。而使用SQL语言操作查询数据，标准统一，使用方便。

客户端、MySQL 数据库管理系统、数据库和表的关系，如下所示：

weiyigeek.top-MySQL 数据模型图

通过 MySQL 客户端连接数据库管理系统 DBMS，然后通过 DBMS 操作数据库。利用 SQL 语句通过数据库管理系统操作数据库，以及操作数据库中的表结构及数据，一个数据库服务器中可以创建多个数据库，一个数据库中也可以包含多张表，而一张表中又可以包含多行记录。

OK，这里简单了解一些，前面作者也在《DBA | 炼气期，关系型数据库知识概述! 》文章中详细讲解过，这里就不再赘述了，有兴趣的看友可以自行查阅。

数据模型的表示

描述：对于从事数据库结构设计相关人员（DBA、开发）而言，我们通常会在设计的不同阶段用到ER模型图和数据库模型图，通过图形化的方式描述了数据之间的关系，帮助开发者设计数据库结构。

weiyigeek.top-数据库结构设计过程图

ER模型图（Entity-Relationship Diagram）?全称为实体联系模型、实体关系模型或实体联系模式图

用在概念结构设计阶段，侧重于概念设计，用于分析数据间的关系，满足第几范式要求，参考《 DBA | 炼气期，关系数据库的范式理论!》文章。 提供了表示实体型、属性和联系的方法，用来描述现实世界的概念模型(数据需求)，例如：存储在数据库中的数据范围、数据类型、数据间的关系等等

数据库模型图?一般在数据库建模时使用，也可以从数据库逆向生成数据库模型图

在概念设计阶段之后，用在数据库建模阶段,侧重点是生成具体的数据库结构，表、字段、索引、主键、外键等，跟具体的数据库实现有一定关系。

下面我们主要讲解 ER 模型图，用它来类比关系模型，以及表示实体与实体之间的关系，ER 模型图的组成元素如下：

实体（Entity）：实体是数据库中的基本对象，通常用矩形表示。例如，在学生管理系统中，“学生”和“班级”都是实体。

属性（Attribute）：属性是实体的特征，通常用椭圆表示。例如，“学生”实体的属性可以是“ID”、“年龄”、“性别”和“成绩”。

关系（Relationship）：关系表示实体之间的关联，通常用菱形表示。例如，“学生”和“班级”之间的关系可以是“属于”。

例如，使用ER模型图的结构化表示一个学生和班级的例子：

# 以下是用伪代码表示的ER模型图结构：
Entity: 学生
Attributes: ID, 年龄, 性别, 成绩

Entity: 班级
Attributes: ID, 名称

Relationship: 学生 属于 班级

weiyigeek.top-学生和班级E-R模型图

另外，两个实体之间的联系有多种类型，即：

一对一联系（1：1）: 实体A中的每个实例在实体B中至多有有一个（或没有）实例与其关联，反之亦然，则称实体A和实体B为一对一关系。

一对多联系（1：n）?: 实体A中的每个实例在实体B中有n个实例（n>1）与之相关联，而实体B中的每个实例在实体A中最多只有一个实例与之关联，则称实体A和实体B为一对多关系

多对一联系（n：1）?: 实体B中的每个实例在实体A中有n个实例（n>1）与之相关联，而实体A中的每个实例在实体b中最多只有一个实例与之关联，则称实体A和实体B为多对一关系

多为多联系（m:n）: ?实体A中的每个实例在实体B中有n(m>1)个实例与之关联，实体b中的每个实例在实体A中有m（m>1）个实例与之关联，则成为实体A与实体B为多对多关系。

weiyigeek.top-二元实体之间的联系有多种类型图

参考资料

ER模型图与常见关系型数据库介绍

第七章:使用E-R模型的数据库设计

加入：作者【全栈工程师修炼指南】知识星球