sxh / engineering-map

切换主题

第1章：数据库基础概念 ​

本章目标 ​

学完本章后，你将能够：

  1. 理解数据、数据库、DBMS 三个核心概念及其关系
  2. 区分关系型数据库与非关系型数据库，并了解各自的适用场景
  3. 掌握 SQL 的五大分类（DDL/DML/DQL/DCL/TCL）及各自职责
  4. 了解 MySQL 的发展历史、版本演进和两种发行版差异
  5. 理解 MySQL 的三层架构和常用存储引擎的区别
  6. 认识 MySQL 在真实世界中的应用场景

前置知识 ​

  • 本章是第1章，无需任何前置知识
  • 基本的计算机操作能力即可

1.1 数据、数据库与数据库管理系统 ​

什么是数据 ​

数据（Data） 是描述客观事物的符号记录。在计算机科学中，数据不仅仅指数字——文本、图片、音频、视频、地理位置坐标、用户行为日志，都是数据。

从数据库管理系统的角度看，数据被组织成一条条的记录（Record）：

学号姓名年龄成绩
001张三1892.5
002李四1985.0
003王五1878.5

上表中每一行就是一个学生的数据记录，每一列描述了一个属性。

什么是数据库 ​

数据库（Database，简称 DB） 是按照一定结构组织、存储和管理数据的"仓库"。它不是简单的文件存储——数据库提供了高效的数据检索、修改、删除能力，同时确保数据的安全性、一致性和完整性。

可以把数据库想象成一个超级电子表格：

  • 一个 Excel 文件能存几千行数据，查找靠 Ctrl+F
  • 一个数据库能存几亿行数据，查找靠索引，毫秒级返回结果
  • Excel 文件被一个人打开就锁定，数据库支持数千人同时读写

什么是数据库管理系统 ​

数据库管理系统（Database Management System，简称 DBMS） 是管理数据库的软件系统。它是用户与数据库之间的桥梁：

用户/应用程序  →  DBMS（如 MySQL）  →  数据库（存储在磁盘上的文件）

DBMS 提供以下核心功能：

  1. 数据定义：创建、修改、删除数据库和表结构
  2. 数据操作：增、删、改、查数据
  3. 数据安全：用户认证、权限控制
  4. 并发控制：多个用户同时操作时保证数据一致性
  5. 备份恢复：故障后恢复数据
  6. 数据完整性：通过约束保证数据正确

重要区分：MySQL 是 DBMS（数据库管理系统），不是数据库本身。就如同 Microsoft Word 是文字处理软件，不是文档本身。

1.2 关系型数据库 vs 非关系型数据库 ​

数据库管理系统分为两大阵营：关系型和非关系型。

关系型数据库（RDBMS） ​

关系型数据库 以二维表（行和列）的形式组织数据，表与表之间通过外键建立"关系"。核心理论基础是 E.F. Codd 于 1970 年提出的关系模型。

主要特点：

  • 数据以表（Table）的形式存储，每个表有固定的列（字段）
  • 表之间通过关系（外键）关联
  • 使用 SQL（结构化查询语言）操作数据
  • 严格遵守 ACID 事务特性（原子性、一致性、隔离性、持久性）
  • 数据模式（Schema）在使用前必须提前定义

常见的关系型数据库：MySQL、PostgreSQL、Oracle、SQL Server、SQLite。

非关系型数据库（NoSQL） ​

非关系型数据库 不采用固定的表结构，试图用更灵活的数据模型来解决关系型数据库难以处理的问题（如大规模数据、非结构化数据等）。"NoSQL" 不是"No SQL"，而是"Not Only SQL"。

NoSQL 按数据模型分为四类：

类型数据模型代表产品典型场景
键值存储Key → Value 简单映射Redis、Memcached缓存、会话存储
文档存储JSON/BSON/XML 文档MongoDB、CouchDB内容管理、用户配置
列族存储列族（按列存储）HBase、Cassandra日志分析、时序数据
图存储节点 + 边 + 属性Neo4j、JanusGraph社交网络、推荐系统

关系型 vs 非关系型全面对比 ​

对比维度关系型数据库 (RDBMS)非关系型数据库 (NoSQL)
数据模型二维表，结构固定键值/文档/列族/图，结构灵活
Schema严格预定义，修改困难动态 Schema，可随时添加字段
扩展方式垂直扩展为主（升级硬件）水平扩展为主（增加节点）
事务支持完整 ACID 事务多数只支持 BASE（最终一致性）
数据一致性强一致性最终一致性为主
查询语言SQL（标准化）各产品自有 API
JOIN 能力强大的多表连接多数不支持 JOIN，需数据冗余
适用场景复杂查询、事务性系统、数据一致性要求高海量数据、高并发读写、数据结构多变
代表产品MySQL、PostgreSQL、OracleRedis、MongoDB、HBase

选择建议：大多数业务系统（电商、CRM、ERP、金融系统）的核心数据应该使用关系型数据库。NoSQL 通常作为补充——比如用 Redis 做缓存加速，用 MongoDB 存储非结构化日志。不要在核心交易数据上使用 NoSQL，除非你非常清楚其中的取舍。

1.3 SQL 语言与五大分类 ​

SQL（Structured Query Language，结构化查询语言） 是操作关系型数据库的标准语言。它由 IBM 在 1970 年代开发，1986 年被 ANSI 定为标准。所有主流关系型数据库都支持 SQL，但在标准之外各有扩展。

SQL 按功能分为五大类：

分类全称中文名称核心动词典型语句
DDLData Definition Language数据定义语言CREATE、ALTER、DROP建库、建表、修改表结构、删除表
DMLData Manipulation Language数据操作语言INSERT、UPDATE、DELETE插入数据、更新数据、删除数据
DQLData Query Language数据查询语言SELECT查询数据（最常用，也最复杂）
DCLData Control Language数据控制语言GRANT、REVOKE授予权限、撤销权限
TCLTransaction Control Language事务控制语言COMMIT、ROLLBACK、SAVEPOINT提交事务、回滚事务、设置保存点

各分类职责详解 ​

DDL —— 定义"骨架"：DDL 操作的是数据库对象（数据库、表、索引、视图等）的"结构"。DDL 操作通常是隐式提交的——执行后立即生效且无法回滚。

sql
-- DDL 示例
CREATE DATABASE school;                    -- 创建数据库
CREATE TABLE student (id INT, name VARCHAR(20));  -- 创建表
ALTER TABLE student ADD COLUMN age INT;    -- 修改表结构
DROP TABLE student;                        -- 删除表

DML —— 操作"血肉"：DML 操作的是表中存储的数据行。DML 操作通常放在事务中，可以提交（COMMIT）或回滚（ROLLBACK）。

sql
-- DML 示例
INSERT INTO student VALUES (1, '张三', 18);  -- 插入数据
UPDATE student SET age = 19 WHERE id = 1;   -- 更新数据
DELETE FROM student WHERE id = 1;           -- 删除数据

DQL —— "眼睛"：DQL 只有一个核心语句 SELECT，但它是最复杂、最常用的。从简单的全表查询到复杂的多表 JOIN + 子查询 + 聚合 + 排序 + 分页，都是 SELECT 的范畴。

sql
-- DQL 示例
SELECT * FROM student;                              -- 查所有
SELECT name, age FROM student WHERE age > 18;       -- 条件查询
SELECT COUNT(*) FROM student;                       -- 聚合计数

DCL —— "门禁"：控制谁能访问什么数据。

sql
-- DCL 示例
GRANT SELECT ON school.* TO 'user1'@'localhost';   -- 授予查询权限
REVOKE INSERT ON school.* FROM 'user1'@'localhost'; -- 撤销插入权限

TCL —— "安全网"：保证一组操作要么全部成功，要么全部失败。

sql
-- TCL 示例
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 确认转账 / ROLLBACK; 取消转账

1.4 MySQL 发展历史 ​

诞生（1995） ​

MySQL 由瑞典公司 MySQL AB 开发，创始人是 Michael "Monty" Widenius（芬兰人）和 David Axmark（瑞典人）。名字中的"My"来自 Monty 的女儿 My，而不是"我的 SQL"。

1995 年 5 月 23 日，MySQL 1.0 内部发布。1996 年，MySQL 3.11.1 首次公开发布。

最初的 MySQL 只支持基本的 SQL 功能，底层存储引擎使用的是 ISAM（后来发展为 MyISAM）。

主要版本演进 ​

版本发布时间重要特性
3.232001支持 InnoDB 引擎（事务、行级锁、外键）、全文索引
4.02003支持 UNION、多表 DELETE、查询缓存、InnoDB 成为可选引擎
4.12004支持子查询、预处理语句、字符集和排序规则的完整支持、GROUP BY 增加 ROLLUP
5.02005支持视图、存储过程、触发器、游标、INFORMATION_SCHEMA
5.12008支持分区、事件调度器、基于行的复制、插件式存储引擎 API
5.52010InnoDB 成为默认存储引擎、半同步复制、SIGNAL/RESIGNAL
5.62013全文索引支持 InnoDB、GTID 复制、在线 DDL、性能大幅提升
5.72015JSON 数据类型、虚拟列、原生支持 JSON 函数、改进查询优化器
8.02018重大版本跳跃（跳过 6、7）：原子 DDL、窗口函数、CTE、角色、降序索引、隐藏索引、数据字典统一为 InnoDB、移除查询缓存

值得注意：MySQL 从 5.7 直接跳到 8.0，跳过了 6 和 7。官方解释是因为 6.0 和 7.0 各有一些规划中的功能，最终认为这些功能应该整合到一个更大的版本中发布。

公司变迁 ​

年份事件
1995MySQL AB 创立
2008Sun Microsystems 以约 10 亿美元收购 MySQL AB
2009Oracle 以 74 亿美元收购 Sun Microsystems，MySQL 归入 Oracle 旗下
2009Monty Widenius 创建 MariaDB（MySQL 的一个分支），作为对 Oracle 收购的回应

MySQL 的两个发行版 ​

MySQL Community Edition（社区版）：

  • 完全免费，GPL 开源协议
  • 包含所有核心功能
  • 由社区维护，没有官方技术支持
  • 适合个人开发者、中小企业、学习用途

MySQL Enterprise Edition（企业版）：

  • 商业付费版本，需要购买 License
  • 包含额外的企业级工具：
    • MySQL Enterprise Backup（热备份工具）
    • MySQL Enterprise Monitor（监控和告警）
    • MySQL Enterprise Firewall（SQL 注入防护）
    • MySQL Enterprise Audit（审计日志）
    • MySQL Enterprise Transparent Data Encryption（透明数据加密）
  • 提供 24x7 官方技术支持
  • 适合对稳定性、安全性有高要求的企业

DBA 的得力工具：MySQL Enterprise Monitor 可以实时监控数据库性能、自动检测异常、生成优化建议，相当于给数据库配了一个"健康管家"。但价格不菲，通常年薪百万的 DBA 所在的公司才用得起。

1.5 MySQL 架构概览 ​

MySQL 采用三层架构，理解这三个层次有助于你之后学习查询优化、存储引擎选择等高级主题。

┌─────────────────────────────────────────────┐
│              连接层 (Connection Layer)        │
│  连接管理、身份认证、线程池                   │
├─────────────────────────────────────────────┤
│               SQL 层 (SQL Layer)              │
│  查询缓存（8.0已移除）→ 解析 → 预处理        │
│  → 优化 → 执行                                │
│  包括：权限检查、存储过程、触发器、视图等      │
├─────────────────────────────────────────────┤
│         存储引擎层 (Storage Engine Layer)      │
│  InnoDB / MyISAM / MEMORY / ARCHIVE / CSV    │
│  负责数据在磁盘上的物理存储                    │
└─────────────────────────────────────────────┘

连接层 ​

当客户端（命令行、Workbench、应用程序）连接 MySQL 时，首先接触的就是连接层。

主要职责：

  1. 连接管理：接收 TCP/IP 或 Unix Socket 连接
  2. 身份认证：验证用户名、密码、连接来源主机
  3. 线程分配：为每个连接分配一个线程（连接数过多会消耗大量内存）
sql
-- 查看当前连接数和最大连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

SQL 层 ​

SQL 层是 MySQL 的大脑，负责理解并执行 SQL 语句。一条 SELECT 语句进入 SQL 层后经历的流程：

SQL语句 → 词法分析 → 语法分析 → 预处理 → 查询优化 → 生成执行计划 → 调用存储引擎API → 返回结果

关键环节解释：

  • 分析器（Parser）：将 SQL 文本解析成"解析树"。如果你写了 SELECT * FORM user（FORM 拼写错误），这一层就会报语法错误
  • 预处理器（Preprocessor）：检查表和列是否存在，检查用户权限
  • 优化器（Optimizer）：这是 SQL 层最复杂的部分。同一个查询可能有多种执行方式，优化器负责选择"代价最小"的执行计划。例如，决定使用哪个索引、JOIN 的顺序等。后续章节中的 EXPLAIN 命令就是用来查看优化器生成的执行计划
  • 执行器（Executor）：根据优化器的执行计划，调用存储引擎的 API 来实际读写数据

关键认知：SQL 是一种"声明式"语言——你告诉数据库"我要什么"，而不是"怎么做"。优化器就是那个决定"怎么做"的幕后功臣，也是后续性能调优的重点关注对象。

存储引擎层 ​

存储引擎是 MySQL 区别于其他数据库的重要特性。它是可插拔的（Pluggable）——你可以在不重启数据库的情况下切换表的存储引擎。

存储引擎负责数据的实际存储和读取。不同的存储引擎有不同的特性：

sql
-- 查看当前 MySQL 支持哪些存储引擎
SHOW ENGINES;

-- 查看某张表使用的存储引擎
SHOW TABLE STATUS LIKE 'table_name';

1.6 常用存储引擎对比 ​

InnoDB vs MyISAM ​

特性InnoDB（✅ 推荐）MyISAM（❌ 逐渐淘汰）
事务支持✅ 完整 ACID 事务❌ 不支持事务
行级锁✅ 行级锁（高并发）❌ 表级锁（并发差）
外键✅ 支持❌ 不支持
崩溃恢复✅ 自动崩溃恢复❌ 崩溃后可能损坏，需手动修复
全文索引✅ MySQL 5.6+ 支持✅ 原生支持
存储限制64TB / 表256TB / 表（取决于 OS）
数据存储表空间（共享或独立）每个表 3 个文件（.frm/.MYD/.MYI）
计数性能SELECT COUNT(*) 需扫描索引SELECT COUNT(*) 直接从元数据读取，很快（但没有 WHERE 条件时）
压缩支持页压缩支持静态表压缩
默认引擎MySQL 5.5+ 默认MySQL 5.5 之前默认
适用场景绝大多数 OLTP 应用只读或极少更新的归档数据

InnoDB 为什么是默认选择 ​

  1. 事务支持：转账、下单等操作需要事务保证数据一致性
  2. 行级锁：高并发场景下，不同事务可以同时修改不同行，不会互相阻塞
  3. 外键约束：在数据库层面保证引用完整性（尽管很多架构倾向于在应用层控制）
  4. 崩溃恢复：InnoDB 有 redo log，即使在写数据时断电，重启后也能自动恢复
  5. MVCC：多版本并发控制，读不阻塞写，写不阻塞读

其他存储引擎简介 ​

引擎特点使用场景
MEMORY数据全在内存中，重启数据丢失临时表、缓存表
ARCHIVE高压缩比，只支持 INSERT 和 SELECT归档日志数据
CSV以 CSV 文件格式存储与外部工具的简单数据交换
BLACKHOLE吞掉所有写入，不存储数据主从复制的中间节点
FEDERATED访问远程 MySQL 表如同本地表分布式数据库架构（不常用）

1.7 MySQL 在真实世界中的应用 ​

MySQL 是全球最流行的开源数据库，被广泛应用于各类系统中。以下是一些典型的使用场景：

Web 应用后端 ​

MySQL 是 LAMP（Linux + Apache + MySQL + PHP）技术栈的核心组成部分。全球超过 80% 的网站使用这个组合或其变体。

  • Facebook：历史上使用 MySQL 作为主要数据存储，拥有数千台 MySQL 服务器，并为此贡献了大量优化和工具
  • YouTube：视频元数据、用户信息全部在 MySQL 中
  • Wikipedia：全球第五大网站的百科全书内容存储在 MySQL 中
  • Twitter：早期大量使用 MySQL，后逐渐加入其他数据存储

电商系统 ​

中国的电商巨头普遍使用 MySQL（淘宝/天猫已经迁移到自研的 AliSQL 和 OceanBase，但它们最初都是从 MySQL 架构演化而来）。

典型用法：用户信息（MySQL） + 商品数据（MySQL） + 缓存（Redis） + 搜索（Elasticsearch）

金融系统 ​

在金融领域，虽然 Oracle 更为主流（核心交易系统），但 MySQL 在以下场景广泛使用：

  • 对账系统（批量数据处理，InnoDB 事务保证准确性）
  • 风控系统（实时查询、高频更新）
  • 报表系统（MySQL + BI 工具）

游戏行业 ​

  • 玩家信息存储（账号、角色、装备、货币）
  • 排行榜（MySQL ORDER BY + LIMIT 或结合 Redis Sorted Set）
  • 日志分析

日志和监控 ​

配合 ELK 或自有监控系统，MySQL 用于存储监控指标、告警历史等结构化日志数据。

云数据库 ​

几乎所有云服务商都提供了基于 MySQL 的托管服务：

  • AWS：Amazon RDS for MySQL、Amazon Aurora（MySQL 兼容）
  • 阿里云：云数据库 RDS MySQL 版、PolarDB for MySQL
  • 腾讯云：云数据库 MySQL
  • Google Cloud：Cloud SQL for MySQL

常见错误 ​

错误 1：把 MySQL 当成"数据库"而不是 "DBMS" ​

现象：初学者常说"我安装了一个数据库"，实际上安装的是 MySQL 这个数据库管理系统。在 MySQL 里可以创建很多个数据库。

原因：中文语境下，"数据库"这个词使用宽松，导致了概念混淆。

正确理解：MySQL = DBMS（管理工具），你在 MySQL 里 CREATE DATABASE mydb; 创建的 mydb 才是数据库。

错误 2：认为 NoSQL 会取代关系型数据库 ​

现象：在 NoSQL 热潮时期（2010 年前后），很多文章宣称"关系型数据库已死"。

原因：对 NoSQL 优势的过度夸大。NoSQL 在特定场景下很有优势（如海量日志、社交关系图），但它缺乏 ACID 事务、JOIN 等关键能力。

正确理解：关系型数据库和 NoSQL 不是替代关系，而是互补关系。大多数公司的技术栈是"关系型数据库 + Redis + 搜索引擎"的组合。核心交易数据永远应该在关系型数据库中。

错误 3：SQL 分类不清 ​

现象：把 SELECT 和 INSERT 当成同一类，或者不知道 GRANT 属于什么。

原因：刚接触数据库时，所有语句都混在一起学，没有有意识地分类。

正确理解：

  • 有没有动数据？没有 → DQL（SELECT）
  • 有没有改数据行？有 → DML（INSERT/UPDATE/DELETE）
  • 有没有改表结构？有 → DDL（CREATE/ALTER/DROP）
  • 是不是权限相关？→ DCL（GRANT/REVOKE）
  • 是不是事务相关？→ TCL（COMMIT/ROLLBACK）

错误 4：没搞清楚 utf8 和 utf8mb4 的区别就开始用 ​

现象：很多人设置 CHARACTER SET utf8，然后发现插入 emoji（如 😀）时报错或变成乱码。

原因：MySQL 的 utf8 是一个"残缺的" UTF-8，只支持最多 3 个字节的字符（即 BMP 基本多文种平面），而 emoji 和部分罕见汉字需要 4 个字节。真正的完整 UTF-8 在 MySQL 中叫 utf8mb4。

正确做法：永远使用 utf8mb4，除非有非常特殊的理由必须用 utf8。后面第4章会详细讲解。

错误 5：认为 MySQL 只有 InnoDB 一种引擎 ​

现象：学了一段时间 MySQL 后仍然不知道还有其他存储引擎，也不知道自己用的到底是什么引擎。

原因：MySQL 5.5 后 InnoDB 是默认引擎，大多数教程也默认使用 InnoDB。但了解其他引擎能帮你应对特殊场景。

正确做法：在做技术选型时，根据业务需求（是否需要事务、是否需要全文索引、是否只读归档等）选择合适的存储引擎。绝大部分情况选 InnoDB 是对的，但知道为什么选它更对。

本章练习 ​

  1. 概念辨析：请你用自己的话，用 1-2 句话分别解释以下三个概念：数据、数据库、DBMS。然后举一个生活中的例子说明三者的关系（例如：图书、书架、图书馆管理系统）。

  2. SQL 分类练习：将以下 10 个 SQL 关键词分别归入 DDL、DML、DQL、DCL、TCL 五个类别中： SELECT、CREATE TABLE、INSERT、GRANT、ROLLBACK、ALTER TABLE、DELETE、REVOKE、DROP DATABASE、COMMIT

  3. MySQL 版本调研：到 MySQL 官网查看当前最新版本是多少（8.0.x 还是 8.4.x 还是 9.x？）。查阅资料对比 MySQL 5.7 和 MySQL 8.0 在 JSON 支持方面的差异。

  4. 存储引擎对比：用表格形式对比 InnoDB 和 MyISAM 的 5 个关键差异（事务、锁、外键、崩溃恢复、全文索引支持情况）。简述如果让你为一个博客系统选存储引擎，你会选哪个？为什么？

  5. 真实场景分析：假设你要设计一个"校园二手交易平台"的数据库，列出可能需要存储的 8-10 种数据实体（如用户、商品等）以及它们之间的大致关系。

  6. 思考题：为什么在 MySQL 5.5 版本中，Oracle 决定将默认存储引擎从 MyISAM 改为 InnoDB？这一改变反映了数据库行业怎样的发展趋势？

把零散知识，整理成可以反复走的路。

© sxh313 · Engineering Map