MySQL 基础
一、MySQL 概述
1. 关系型数据库与非关系型数据库
RDBMS（Relational Database Management System）—— 关系型数据库管理系统。
简单来说，关系型数据库是指采用了二维表格来组织数据的数据库。
| 类型 | 特点 | 代表产品 |
|---|---|---|
| 关系型数据库 | 二维表格组织数据，支持事务 ACID | Oracle、MySQL、DB2、SQL Server |
| 非关系型数据库 | Key/Value 键值对维护数据，高扩展性 | Redis、MongoDB、Cassandra |
关系型数据库的最大特点是事务的一致性，但对事务一致性的维护会消耗较大的系统开销。
2. 什么是事务
事务（Transaction）由一条或多条 SQL 语句组成。在事务操作中，这些 SQL 语句要么全部成功执行，要么全部不执行。
3. 事务的 ACID 特性
| 特性 | 说明 | 通俗理解 |
|---|---|---|
| 原子性（Atomicity） | 事务中的操作不可分割，要么全部完成，要么均不执行 | 全有或全无 |
| 一致性（Consistency） | 事务执行前后数据库都处于一致状态，不破坏数据完整性和业务逻辑 | 数据不会乱 |
| 隔离性（Isolation） | 事务的执行不受其他事务干扰，中间结果对其他事务透明 | 互不干扰 |
| 持久性（Durability） | 事务提交后，数据修改永久生效，即使系统故障也不会丢失 | 落盘不丢 |
4. MySQL 体系结构
⚠️ 图片缺失：原文档引用的体系结构图为本地临时文件，未能保存到附件目录。建议补充 MySQL 体系结构图（可用 Draw.io 或 Excalidraw 绘制后放入 90-attachments/）。
MySQL 体系结构从上到下分为：
- 连接层：连接池组件，处理客户端连接请求
- 服务层：SQL 接口、查询解析器、查询优化器、缓存组件
- 引擎层：可插拔存储引擎（InnoDB、MyISAM 等）
- 存储层：文件系统与日志存储（数据文件、索引文件、日志文件）
5. 日志文件
| 日志类型 | 文件名/位置 | 作用 |
|---|---|---|
| 错误日志（Error Log） | /data/目录/主机名.err | 记录数据库启动、停止、运行时的错误信息（重点关注 ERROR 级别） |
| 二进制日志（Binlog） | 由 log_bin 参数指定 | 记录所有更改操作（DDL/DML/DCL），不包含 SELECT/SHOW 查询语句 |
二、MySQL 基本 SQL 语句
1. MySQL 客户端工具
# 常用连接参数
mysql -u 用户名 -p 密码 -h 主机地址 -P 端口号 -S socket文件
# 非交互模式执行 SQL
mysql -u root -p -e 'SHOW DATABASES;`
| 参数 | 说明 |
|---|---|
| -u / —user | 指定登录用户名 |
| -p / —password | 指定登录密码（注意小写 p，建议放到最后） |
| -h / —host | 指定数据库主机地址 |
| -P / —port | 指定数据库端口号（大写 P） |
| -S / —socket | 指定 socket 文件 |
| -e / —execute | 非交互模式执行 SQL |
2. 常用管理命令
RELOAD; -- 刷新授权表
SHUTDOWN; -- 停止 MySQL 服务
STATUS; -- 简查看数据库状态
START SLAVE; -- 启动从库复制
STOP SLAVE; -- 停止从库复制
SHOW VARIABLES; -- 查看可用变量
SELECT VERSION(); -- 查看 MySQL 版本信息3. 什么是 SQL
SQL（Structured Query Language）—— 结构化查询语言，是使用关系模型的数据库应用语言。
4. SQL 语句分类
| 分类 | 全称 | 作用 | 常见语句 |
|---|---|---|---|
| DDL | Data Definition Language | 数据定义语言，定义数据库、表、索引等 | CREATE、ALTER、DROP |
| DML | Data Manipulation Language | 数据操纵语言，增删改查记录 | INSERT、UPDATE、DELETE |
| DQL | Data Query Language | 数据查询语言，检索信息 | SELECT |
| DCL | Data Control Language | 数据控制语言，管理权限和安全级别 | GRANT、REVOKE |
5. 数据库操作
创建数据库：
CREATE DATABASE 数据库名称;查询数据库：
-- 显示所有数据库
SHOW DATABASES;
-- 显示某个数据库的创建语句
SHOW CREATE DATABASE db1;修改数据库信息：
ALTER DATABASE 数据库名称 DEFAULT CHARSET=编码格式;删除数据库（慎用）：
DROP DATABASE 数据库名称;6. 数据表操作
创建数据表：
CREATE TABLE 数据表名称 (
字段1 字段类型 [字段约束],
字段2 字段类型 [字段约束],
...
);查询数据表：
-- 选择数据库
USE 数据库名称;
-- 显示所有数据表
SHOW TABLES;
-- 显示表的创建语句
SHOW CREATE TABLE 数据表名称;
-- 显示表结构
DESC 数据表名称;修改数据表：
Ⅰ. 添加字段：
ALTER TABLE 数据表名称 ADD 新字段 字段类型 FIRST|AFTER 其他字段;
-- FIRST: 将新字段放在第一位
-- AFTER 字段名: 将新字段放在指定字段之后Ⅱ. 修改字段名称或类型：
ALTER TABLE 表名称 CHANGE 旧字段名 新字段名 字段类型;Ⅲ. 修改字段类型或约束：
ALTER TABLE 表名称 MODIFY 字段名称 字段类型;Ⅳ. 删除字段：
ALTER TABLE 表名称 DROP 字段名称;Ⅴ. 修改表名：
ALTER TABLE 旧表名 RENAME TO 新表名;Ⅵ. 修改表的字符集：
ALTER TABLE 表名称 DEFAULT CHARSET=编码格式;删除数据表（慎用）：
DROP TABLE 数据表名称;三、数据类型与运算符
1. 数据类型
整型：
| 类型 | 占用空间 | 取值范围（有符号） |
|---|---|---|
| TINYINT | 1 字节 | -128 ~ 127 |
| SMALLINT | 2 字节 | -32768 ~ 32767 |
| MEDIUMINT | 3 字节 | -8388608 ~ 8388607 |
| INT / INTEGER | 4 字节 | -2147483648 ~ 2147483647 |
| BIGINT | 8 字节 | ±9.22×10¹⁸ |
浮点与定点数：
| 类型 | 说明 |
|---|---|
| FLOAT(M,D) | 单精度浮点数，M 为总位数，D 为小数位 |
| DOUBLE(M,D) | 双精度浮点数 |
| DECIMAL(M,D) | 定点数（精确存储），适合金额等对精度敏感的场景 |
字符串类型：
| 类型 | 说明 | 最大长度 |
|---|---|---|
| CHAR(N) | 定长字符串 | 255 字符 |
| VARCHAR(N) | 变长字符串 | 65535 字符 |
| TINYTEXT | 微型文本 | 255 字节 |
| TEXT | 文本 | 65535 字节 |
| MEDIUMTEXT | 中型文本 | 16MB |
| LONGTEXT | 大型文本 | 4GB |
| ENUM | 枚举类型 | 65535 个值 |
| SET | 集合类型 | 64 个值 |
日期/时间类型：
| 类型 | 格式 | 说明 |
|---|---|---|
| DATE | YYYY-MM-DD | 日期 |
| TIME | HH:MM:SS | 时间 |
| DATETIME | YYYY-MM-DD HH:MM:SS | 日期时间 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 时间戳（受时区影响） |
| YEAR | YYYY | 年份 |
二进制类型：
| 类型 | 说明 |
|---|---|
| BINARY(N) | 定长二进制 |
| VARBINARY(N) | 变长二进制 |
| BLOB | 二进制大对象 |
其他类型：
| 类型 | 说明 |
|---|---|
| JSON | MySQL 5.7+ 原生 JSON 支持 |
| GEOMETRY | 空间数据类型 |
2. 运算符
算术运算符： +、-、*、/、%（取模）
比较运算符：
| 运算符 | 说明 |
|---|---|
| = | 等于 |
| <> / != | 不等于 |
| < / <= / > / >= | 大小比较 |
| BETWEEN … AND … | 在指定范围之间 |
| IN (值1, 值2, …) | 在指定集合中 |
| LIKE | 模糊匹配（% 匹配任意多个字符，_ 匹配单个字符） |
| IS NULL / IS NOT NULL | 判断是否为空 |
逻辑运算符： AND（与）、OR（或）、NOT（非）、XOR（异或）
位运算符： &（位与）、|（位或）、^（位异或）、~（位取反）、>>（右移）、<<（左移）
四、存储引擎
1. 存储引擎概述
MySQL 使用可插拔存储引擎架构，支持多种存储引擎以满足不同场景需求。
2. MyISAM 与 InnoDB 对比
| 特性 | MyISAM | InnoDB |
|---|---|---|
| 事务 | 不支持 | ✅ 支持 |
| 外键 | 不支持 | ✅ 支持 |
| 锁粒度 | 表级锁 | 行级锁 + 表级锁 |
| 并发性能 | 低（锁定整张表） | 高（只锁定涉及的行） |
| 全文索引 | ✅ 支持 | MySQL 5.6+ 支持 |
| 缓存 | 只缓存索引 | 缓存索引 + 数据 |
| 默认使用 | MySQL 5.5 之前 | MySQL 5.5+ 默认引擎 |
| 适用场景 | 以查询为主、不需要事务的表 | 需要事务支持、高并发读写 |
3. 查看存储引擎
-- 查看当前 MySQL 支持的存储引擎
SHOW ENGINES;
-- 查看表的存储引擎
SHOW CREATE TABLE 表名称;
-- 查看数据库默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine';五、索引
1. 索引概述
索引是帮助 MySQL 高效获取数据的数据结构。索引可以大大提高查询速度，但会降低写入性能并占用额外空间。
2. 索引分类
| 索引类型 | 说明 |
|---|---|
| 普通索引（INDEX） | 最基本的索引，没有唯一性限制 |
| 唯一索引（UNIQUE） | 索引列的值必须唯一，允许 NULL |
| 主键索引（PRIMARY KEY） | 特殊的唯一索引，不允许 NULL，一个表只能有一个 |
| 复合索引 | 多列组合索引，遵循最左前缀原则 |
| 全文索引（FULLTEXT） | 用于全文搜索 |
| 空间索引（SPATIAL） | 用于空间数据类型 |
3. 索引操作
-- 创建索引
CREATE INDEX 索引名 ON 表名(列名);
CREATE UNIQUE INDEX 索引名 ON 表名(列名);
-- 查看索引
SHOW INDEX FROM 表名;
-- 删除索引
DROP INDEX 索引名 ON 表名;4. 索引使用原则
- 最左前缀原则：复合索引从最左侧开始匹配，跳过中间列会导致后续列无法使用索引
- 高选择性列优先：区分度高的列放在复合索引左侧
- 避免过度索引：索引不是越多越好，每增加一个索引都会降低写入性能
- 覆盖索引：查询的列全部在索引中时，无需回表查询，性能极高
六、主从复制
1. MySQL 主从复制概述
MySQL 主从复制（Master-Slave Replication）是指将主数据库（Master）的 DDL 和 DML 操作通过二进制日志（Binlog）传输到从数据库（Slave）上，并在从库上重新执行，从而保持主从数据一致。
2. 主从复制架构
| 架构模式 | 说明 |
|---|---|
| 一主一从 | 一个 Master 同步到一个 Slave |
| 一主多从 | 一个 Master 同步到多个 Slave，分担读压力 |
| 主主复制（Master-Master） | 两个 Master 互相同步，同一时刻只有一台接收写请求 |
| 级联复制 | Slave 同时作为下一级 Slave 的 Master |
3. 主从复制核心思路
⚠️ 图片缺失：原文档引用的主从复制架构图为本地临时文件。
配置要点：
- Slave 必须安装相同版本的 MySQL 数据库软件
- Master 端必须开启二进制日志（Binlog）；Slave 端必须开启 Relay Log
- Master 端和 Slave 端的 server-id 不能一致
- Master 端必须创建复制用户
- 保证 Master 和 Slave 初始数据一致
配置步骤：
-- Master 端 my.cnf 配置
[mysqld]
log-bin=mysql-bin
server-id=1
-- Slave 端 my.cnf 配置
[mysqld]
server-id=2
relay-log=mysql-relay-bin-- Master 端创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY '密码';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-- Slave 端配置主从
CHANGE MASTER TO
MASTER_HOST='主库IP',
MASTER_USER='repl',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=位置号;
START SLAVE;
-- 查看从库状态
SHOW SLAVE STATUS\\G;七、基于 GTID 的复制
1. GTID 概述
GTID（Global Transaction Identifiers）—— 全局事务标识符，是 MySQL 5.6 引入的新特性。
特点：
- 每个事务都可以被唯一识别和跟踪
- 添加新 Slave 或 Master 故障切换时，无需指定 Binlog 文件名和 Position 值
- 完全基于事务，不支持 MyISAM 存储引擎
GTID 组成： source_id:transaction_id
- source_id：来自 server_uuid，可在 auto.cnf 中查看
- transaction_id：自动生成的序列数字
使用限制：
- ❌ 不支持非事务引擎（MyISAM），可能导致多个 GTID 分配给同一事务
- ❌ 不支持 CREATE TABLE … SELECT 语句（主库语法报错）
- ❌ 不支持 CREATE/DROP TEMPORARY TABLE 语句
- ⚠️ 必须设置 enforce-gtid-consistency 参数
- ⚠️ sql-slave-skip-counter 不适用（传统跳过错误方式失效）
- ⚠️ GTID 环境中所有节点必须统一开启或关闭 GTID
八、半同步复制
1. 概念
半同步复制（Semi-Synchronous Replication）保证 Master 在每次 COMMIT 事务时，必须等待至少一个 Slave 确认已收到并写入 Relay Log 后，才向客户端返回成功。
Master COMMIT → 发送 Binlog → Slave 接收并写入 Relay Log → Slave 回复 ACK → Master 返回客户端成功
优点： 主从数据一致性更高 缺点： 牺牲 Master 性能（需等待 Slave 确认）
等待超时时间可配置：
sql -- 设置半同步复制等待超时时间（默认 10 秒） SET GLOBAL rpl_semi_sync_master_timeout = 10000;
九、MHA 高可用架构
1. 什么是 MHA
MHA（Master High Availability）是一套成熟的 MySQL 高可用故障切换方案，由日本 DeNA 公司开发。
- 故障切换时间：0~30 秒内自动完成
- 数据一致性：在故障切换过程中最大程度保证数据不丢失
- 适用场景：生产环境高可用需求
2. 工作原理
⚠️ 图片缺失：原文档引用的 MHA 架构图为本地临时文件。
` Master 故障 → 对比 Slave Binlog 位置 → 选取最新 Slave → 生成差异中继日志 → 应用日志 → 提升为新 Master
- Master 出现故障
- 对比各 Slave 的 I/O 线程读取 Binlog 的位置，选取最接近 Master 的 Slave 作为 Latest Slave
- 其他 Slave 与 Latest Slave 对比生成差异中继日志并应用
- 在 Latest Slave 上应用从 Master 保存的 Binlog，提升为新的 Master
- 其他 Slave 应用差异中继日志并开始从新 Master 复制 `
3. MHA 组件
| 组件 | 部署位置 | 功能 |
|---|---|---|
| MHA Manager | 独立机器或 Slave 节点 | 监控 Master、自动故障切换、管理多个集群 |
| MHA Node | 所有运行 MySQL 的服务器 | 保存 Binlog、应用差异中继日志、清除中继日志 |
MHA Node 三大作用：
- 保存二进制日志：如果可访问故障 Master，拷贝其 Binlog
- 应用差异中继日志：从拥有最新数据的 Slave 生成并应用中继日志
- 清除中继日志：在不停止 SQL 线程的情况下安全删除中继日志
关联文档
- MySQL 复制原理与配置 — 主从复制原理详解
- MySQL binlog 保留时间与清理 — Binlog 管理与维护
- MySQL 高可用方案 — 高可用架构对比
- GTID 集合详解 — GTID 深入原理
- MySQL 基础知识面试题 — 配套面试题