一、什么是MySQL
MySQL是一个常用的数据库管理系统，它是关系型数据库，它使用结构化查询语言（SQL）来管理数据库中的数据。MySQL 使用 表（Table）来存储数据，数据以 行（Row） 和 列（Column） 的形式组织。关系型数据库的关键特性包括数据存储在表中，并通过 外键 等机制进行表与表之间的关联。SQL 语言用于执行查询、插入、更新和删除操作，并允许通过复杂的查询进行数据的检索和管理。
二、数据库的常用术语
1. 数据库（Database）
- 数据库是一个结构化的数据集合，用于存储和管理数据，通常以表格形式存储。
2. 数据库管理系统（DBMS）
3. 表（Table）
- 表是数据库中数据存储的基本单位。表由 行（记录） 和 列（字段） 组成。每个表有一个表名，用于唯一标识。
4. 行（Row）/记录（Record）
- 表中的一条数据记录，通常由多个字段（列）组成。每一行代表一个完整的实体或实例。
5. 列（Column）/字段（Field）
- 表的纵向数据，每列存储相同类型的数据。每个列有一个名称，并定义该列的数据类型（如整数、字符串、日期等）。
6. 主键（Primary Key）
- 主键是一个表中的列或一组列，其值唯一地标识每一行数据。主键的值不能为 NULL。
7. 外键（Foreign Key）
- 外键是指在一个表中的列，它用于建立与另一个表的联系。外键约束确保数据的完整性，确保外键值对应的记录在引用表中存在。
8. 索引（Index）
9. 查询（Query）
- 查询是用来从数据库中提取数据的请求，通常使用 SQL（结构化查询语言）进行操作。例如，
SELECT查询用于从表中选择数据。
三、MySQL体系结构
MySQL的体系结构可以分为多个层次，每个层次都有不同的功能组件。MySQL 的体系结构是客户端-服务器架构，主要包括以下几个核心部分：
1. 客户端层（Client Layer）
- 客户端应用程序：MySQL 的客户端可以是任何连接到数据库的应用程序。常见的客户端有：
- 客户端通过网络发送 SQL 请求到 MySQL 服务器，并从服务器接收响应。
2. 连接层（Connection Layer）
- 连接管理：MySQL 服务器接收到来自客户端的连接请求。连接层负责管理客户端与 MySQL 服务器之间的连接。它的主要任务是：
- 认证：验证客户端的身份（如用户名、密码）。
- 连接管理：处理并维护多个并发客户端的连接，包括连接的创建、维护和关闭。
- MySQL 支持多种连接方式（如 TCP/IP、Unix Socket、命名管道等）。
3. 查询解析层（Query Parsing Layer）
- 查询解析：客户端提交的 SQL 查询首先会被查询解析器处理。解析器将 SQL 查询语句转化为内部的结构，分为两个主要部分：
- 词法分析：将 SQL 查询中的字符流转化为标记（token）。
- 语法分析：分析标记的顺序和语法，构建抽象语法树（AST）。
- 该层的主要任务是理解 SQL 查询的意图，检查语法是否正确。
4. 优化层（Optimization Layer）
- 查询优化器：优化器的任务是选择最优的执行计划，以最有效率的方式执行查询。它会根据不同的查询条件和数据库表的结构来选择合适的执行方式。
- 关键的优化步骤包括：
- 选择执行计划：优化器可能会选择不同的查询计划，例如使用索引扫描或全表扫描。
- 重新排序操作：例如在多个表的联接时，优化器会选择合适的连接顺序。
- 选择最佳算法：如嵌套循环连接、哈希连接等。
- 查询优化是 MySQL 性能的重要组成部分。
5. 执行层（Execution Layer）
- 查询执行器：优化后的查询会传递给执行器，执行器负责实际执行查询并返回结果。执行器会根据执行计划调用相关的存储引擎来获取数据。
- 执行层包括：
- 执行查询操作（如插入、删除、更新）。
- 数据的检索操作（如 select 查询）。
6. 存储引擎层（Storage Engine Layer）
- 存储引擎：这是 MySQL 最底层的一个重要组件，负责实际的物理存储操作。不同的存储引擎有不同的存储格式和功能特性。常见的存储引擎包括：
- InnoDB：默认的事务性存储引擎，支持事务、行级锁、外键等特性。
- MyISAM：不支持事务，但提供较高的查询性能，适用于读取密集型应用。
- Memory：基于内存的存储引擎，数据存储在内存中，速度非常快，但数据会在 MySQL 重启时丢失。
- CSV：将数据存储为 CSV 格式文件。
- NDB：用于 MySQL Cluster 的分布式存储引擎。
- 存储引擎层负责数据的实际存取和管理操作，包括读取、写入、索引操作等。
7. 缓冲池层（Buffer Pool Layer）
- 缓存管理：MySQL 通过内存中的缓冲池来缓存数据页、索引页等常用的数据，减少磁盘I/O的次数，提高性能。
- InnoDB 引擎的缓冲池：InnoDB 存储引擎通过缓冲池来存储表数据、索引、Undo 日志等。频繁访问的数据会被缓存到内存中，从而提高查询效率。
8. 日志层（Logging Layer）
- 日志系统：MySQL 使用不同类型的日志来确保数据的持久性和恢复能力：
- 错误日志：记录服务器启动、停止或运行时的错误信息。
- 查询日志：记录所有执行的查询语句。
- 慢查询日志：记录执行时间超过阈值的查询语句，主要用于性能分析。
- 二进制日志（Binlog）：记录所有更改数据库数据的操作，用于复制和数据恢复。
- 事务日志（Redo Log）：InnoDB 存储引擎使用的日志，保证事务的持久性（即事务的 ACID 属性）。
9. 复制与高可用层（Replication and High Availability Layer）
- 复制：MySQL 支持主从复制，通过 二进制日志（Binlog） 和 复制线程 将主数据库的变更同步到从数据库。
- 高可用性：MySQL 支持多种高可用性方案，如主从复制、组复制、MySQL Cluster 和使用代理软件（如 ProxySQL）等来实现故障切换和负载均衡。
MySQL 的体系结构分为多个层次，从客户端到存储引擎，每个层次都有不同的组件和职责。整体架构中，MySQL 的查询请求从客户端层通过连接层传入，经过查询解析、优化和执行，最终由存储引擎进行数据的实际存取和操作。具体结构如下图：
四、数据库设计的三范式及反范式
在设计数据库中的数据表时，我们的设计原则是尽可能减少数据冗余，即减少数据存储大小节约磁盘空间，根据该原则产生了数据库设计额定三范式：
1、列的原子性：所有字段的值必须是原子的，不可再分；
2、满足第一范式的前提下，表中的每列都必须完全依赖于主键，不能只依赖主键的一部分（避免部分依赖）；
3、满足第二范式的前提下，表中的每列都直接依赖于主键，而不是通过其他非主键列间接依赖（避免传递依赖）；
什么是反范式？即我们有时需要违背数据库设计原则来换取查询效率，若我们完全遵守原则设计数据库，可能导致出现多张表，那我们查询时也会访问多张表，导致查询效率变低，因此在设计数据表时可以适时使用反范式。
五、MySQL的基本操作
MySQL的基础操作很多，这里就只列一些重要的，其余的读者可自行查阅资料。
1. 聚合函数
| 操作 | SQL 语句 |
|---|---|
| 计算总数 | SELECT COUNT(*) FROM users; |
| 计算平均值 | SELECT AVG(age) FROM users; |
| 计算最大值 | SELECT MAX(age) FROM users; |
| 计算最小值 | SELECT MIN(age) FROM users; |
| 计算总和 | SELECT SUM(age) FROM users; |
2. 分组查询（GROUP BY）
| 操作 | SQL 语句 |
|---|---|
| 按年龄分组，计算每个年龄段的人数 | SELECT age, COUNT(*) FROM users GROUP BY age; |
| 按部门统计平均薪资 | SELECT department, AVG(salary) FROM employees GROUP BY department; |
| 按订单状态统计订单总数 | SELECT status, COUNT(*) FROM orders GROUP BY status; |
| 统计每个城市的用户数量 | SELECT city, COUNT(*) FROM users GROUP BY city; |
3. 过滤分组结果（HAVING）
| 操作 | SQL 语句 |
|---|---|
| 统计用户数大于 2 的年龄段 | SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 2; |
| 统计薪资平均值超过 5000 的部门 | SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 5000; |
4. 结合 WHERE 和 GROUP BY
| 操作 | SQL 语句 |
|---|---|
| 统计 30 岁以上用户的数量（先筛选再分组） | SELECT age, COUNT(*) FROM users WHERE age > 30 GROUP BY age; |
