MySQL 数据库

MySQL 数据库管理软件本质上是一个 socket 套接字程序，核心作用是管理本地的文件数据。用户通过客户端连接 MySQL 服务端，发送 SQL 指令，MySQL 将这些指令转化为对本地文件的读写操作。本页是知识库数据库层的枢纽页，覆盖数据库基础、部署、管理、SQL、存储引擎、事务、索引、复制与高可用全景。

数据库本质：库/表/记录与文件系统的对应

数据库概念	文件系统对应	说明
库（Database）	文件夹	一个库对应一个目录
表（Table）	文件	一张表对应一个或多个文件（取决于存储引擎）
记录（Record）	文件内的一行	表中每一行数据就是一条记录
字段（Field）	文件内的一列	表中每一列定义数据的一种属性

关系型 vs 非关系型数据库

对比项	RDBMS（关系型）	NoSQL（非关系型）
数据结构	二维表，表间通过外键关联	键值对、文档、列族、图
Schema	固定表结构（Schema）	结构灵活，无需预先定义
事务	支持 ACID 事务	通常不支持或支持较弱
语言	SQL 标准语言	各自 API/查询语言
存储位置	数据存储在硬盘	部分产品存储在内存
适用场景	复杂查询、事务性业务	高并发、结构灵活场景
代表产品	MySQL、PostgreSQL、Oracle、SQL Server、MariaDB	Redis（键值对）、MongoDB（文档）、Cassandra（列族）、Neo4j（图）

MySQL 部署

部署方式对比

部署方式	适用场景	说明
部署 MariaDB	CentOS 7 默认源	MySQL 的分支，兼容性好
部署 MySQL（官方源）	生产环境推荐	从 MySQL 官方 YUM 源安装
Windows 部署 MySQL	开发环境	学习 SQL 开发时使用，操作简便
MySQL 多实例部署	单机多服务	同一台机器运行多个 MySQL 实例，端口不同

多实例部署

在一台物理机上用同一个 mysqld 命令启动多个 MySQL 进程，每个进程监听不同端口（如 3306/3307/3308），拥有独立的数据目录与配置文件，彼此隔离——相当于一台机器上运行多台”虚拟” MySQL 服务器。核心原则：端口、socket、数据目录三者必须独立。

# 1. 准备多个数据目录
mkdir -p /data/3306 /data/3307 /data/3308
chown -R mysql:mysql /data/3306 /data/3307 /data/3308
 
# 2. 每个实例独立配置文件（/etc/my3306.cnf）
[mysqld]
port=3306
datadir=/data/3306
socket=/tmp/mysql3306.sock
pid-file=/data/3306/mysql.pid
log-error=/data/3306/error.log
# 实例2/3 类似，端口改为 3307/3308
 
# 3. 分别初始化各实例数据目录
mysqld --initialize --user=mysql --datadir=/data/3306
 
# 4. 按配置文件启动各实例
mysqld_safe --defaults-file=/etc/my3306.cnf &
 
# 5. 验证：端口与 socket 连接
netstat -tlnp | grep 330
mysql -uroot -p -S /tmp/mysql3306.sock

适用场景：资源隔离（不同业务使用不同实例，避免相互影响）、测试环境（一台机器模拟多台 MySQL）、节省成本（硬件资源有限时充分利用单机性能）。

常见排错：端口冲突 → netstat -tlnp 查看端口占用；socket 路径冲突 → 每个实例使用不同 socket 路径；启动失败 → datadir 权限不足（chown -R mysql:mysql /data/3306）；初始化失败 → datadir 非空（删除目录下所有文件后重新初始化）。

源码编译安装 vs 二进制安装

对比项	源码安装	二进制安装
安装速度	慢（需编译）	快（解压即用）
定制化	灵活（可选编译参数）	有限
适用场景	有定制需求的生产环境	快速部署
依赖要求	需编译工具链	基本无

源码编译安装步骤：① 安装依赖 yum install gcc gcc-c++ cmake make ncurses-devel bison openssl-devel -y；② 下载源码并解压；③ CMake 配置（-DCMAKE_INSTALL_PREFIX=/usr/local/mysql、-DMYSQL_DATADIR=/data/mysql、-DSYSCONFDIR=/etc、-DWITH_INNOBASE_STORAGE_ENGINE=1、-DWITH_SSL=system）；④ make && make install；⑤ useradd -s /sbin/nologin mysql；⑥ mysqld --initialize 初始化数据目录；⑦ 复制 my-default.cnf 到 /etc/my.cnf；⑧ 配置 systemd/init.d 服务。

二进制安装步骤：① 下载 mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz 解压 mv 到 /usr/local/mysql；② 创建 mysql 用户；③ 创建数据目录授权 chown -R mysql:mysql /usr/local/mysql /data/mysql；④ 配置 PATH 环境变量 /etc/profile.d/mysql.sh；⑤ mysqld --initialize --user=mysql --basedir=... --datadir=...（输出生成临时密码）；⑥ 编写 /etc/my.cnf；⑦ 配置 systemd（mysqld_safe + mysqladmin shutdown）；⑧ grep 'temporary password' /data/mysql/*.err 获取临时密码登录后 ALTER USER 修改。

基本管理

管理项	说明
启动与关闭	systemctl start/stop mysqld，或使用 mysqld_safe
设置密码	首次安装后需设置 root 密码，生产环境建议定期更换
客户端连接	mysql -uroot -p -h 127.0.0.1 -P 3306
字符编码	建议统一设置为 utf8mb4（支持 emoji 和完整 Unicode）

密码管理

• 初始密码（源码/二进制安装）：grep 'temporary password' /var/log/mysqld.log
• 修改密码（MySQL 5.7+）：ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码'; FLUSH PRIVILEGES;
• 修改密码（MySQL 5.6-）：SET PASSWORD FOR 'root'@'localhost' = PASSWORD('新密码');
• 忘记密码破解：核心是 skip-grant-tables 跳过授权表

# 忘记密码破解流程
systemctl stop mysqld
# /etc/my.cnf 的 [mysqld] 段添加：skip-grant-tables
systemctl start mysqld
mysql -uroot          # 无密码登录
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';
# 退出后移除 skip-grant-tables 配置，重启 MySQL
vim /etc/my.cnf       # 删除 skip-grant-tables 行
systemctl restart mysqld

⚠️ skip-grant-tables 仅限临时故障恢复使用，用完立即移除，否则任何人可无密码登录。

客户端快捷命令

SHOW DATABASES;              -- 查看所有库
USE 库名;                    -- 切换库
SHOW TABLES;                 -- 查看当前库的所有表
DESC 表名;                   -- 查看表结构
SHOW CREATE TABLE 表名;      -- 查看建表语句
STATUS;                      -- 查看当前状态
\q                           -- 退出

配置文件 /etc/my.cnf

MySQL 配置文件（/etc/my.cnf 或 /etc/mysql/my.cnf）用于设置服务端和客户端行为参数。加载顺序：/etc/my.cnf → /etc/mysql/my.cnf → /usr/local/mysql/etc/my.cnf → ~/.my.cnf，后面的配置覆盖前面同名项。

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
 
[mysqld]
user = mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysqld.log
 
# 字符编码
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
 
# 连接与性能
max_connections = 1000
innodb_buffer_pool_size = 1G

配置项	说明	建议值
datadir	数据文件存放目录	默认 /var/lib/mysql
port	监听端口	默认 3306
max_connections	最大连接数	根据业务调整，建议 500-2000
character-set-server	字符集	utf8mb4（支持 emoji）
innodb_buffer_pool_size	InnoDB 缓存池大小	物理内存的 60%-70%
skip-grant-tables	跳过授权表（故障恢复用）	仅临时使用，用完立即移除

SQL 语句

SQL（Structured Query Language）是操作关系型数据库的标准语言，分四大类：

分类	说明	关键词
DDL（数据定义语言）	定义数据库结构	CREATE、ALTER、DROP
DML（数据操作语言）	操作数据	INSERT、UPDATE、DELETE
DQL（数据查询语言）	查询数据	SELECT
DCL（数据控制语言）	权限管理	GRANT、REVOKE

库/表/记录 CRUD

-- 库操作（库 = 文件系统的目录）
CREATE DATABASE db1;
CREATE DATABASE db1 CHARACTER SET utf8mb4;
CREATE DATABASE db1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  -- 指定排序规则
SHOW DATABASES;               -- 查看所有库
SHOW CREATE DATABASE db1;     -- 查看建库语句
USE db1;                      -- 切换库
SELECT DATABASE();            -- 查看当前所在库
ALTER DATABASE db1 CHARACTER SET utf8mb4;
DROP DATABASE db1;            -- ⚠️ 危险操作，删除所有数据
 
-- 表操作
CREATE TABLE t1 (id INT, name VARCHAR(20));
DROP TABLE t1;
ALTER TABLE t1 ADD age INT;
ALTER TABLE t1 MODIFY name VARCHAR(50);
SHOW TABLES;
DESC t1;
 
-- 记录操作（CRUD）
INSERT INTO t1 VALUES (1, 'egon', 18);
INSERT INTO t1 (name, age) VALUES ('tom', 20);   -- 指定列插入
SELECT * FROM t1;
SELECT name, age FROM t1 WHERE age > 18;
SELECT * FROM t1 ORDER BY age DESC;              -- 排序
SELECT * FROM t1 LIMIT 10;                       -- 分页
UPDATE t1 SET age = 20 WHERE name = 'egon';
DELETE FROM t1 WHERE id = 1;
-- ⚠️ 不带 WHERE 会删除全部数据：DELETE FROM t1; 危险操作

高级机制（SQL 开发辅助）

机制	说明	使用场景
视图（View）	虚拟表，存储查询结果	简化复杂查询，数据安全
触发器（Trigger）	表操作前后自动执行	审计日志、数据同步
存储过程（Procedure）	预编译的 SQL 代码块	复杂业务逻辑封装
函数（Function）	返回值的 SQL 代码块	计算、格式化等
流程控制	IF、CASE、LOOP、WHILE	存储过程内的逻辑控制

数据类型

数据类型定义字段可存储的数据种类，不同类型占用不同存储空间，影响查询效率。

整数类型

类型	字节	范围（有符号）
TINYINT	1	-128 ~ 127
SMALLINT	2	-32768 ~ 32767
MEDIUMINT	3	-8388608 ~ 8388607
INT	4	-2147483648 ~ 2147483647
BIGINT	8	-9223372036854775808 ~ 9223372036854775807

CREATE TABLE t_int (id INT, age TINYINT, big_num BIGINT);

浮点数与定点数

类型	精度	适用场景
FLOAT	低	科学计算，不要求高精度
DOUBLE	中	一般数值计算
DECIMAL	高	金额、财务数据（推荐）

CREATE TABLE t10 (x FLOAT(255,30));
CREATE TABLE t11 (x DOUBLE(255,30));
CREATE TABLE t12 (x DECIMAL(65,30));
-- 插入同一高精度小数，FLOAT/DOUBLE 有精度损失，DECIMAL 精确

日期时间类型

类型	格式	说明
DATE	YYYY-MM-DD	仅日期
TIME	HH:MM:SS	仅时间，范围 -838:59:59 ~ 838:59:59
DATETIME	YYYY-MM-DD HH:MM:SS	日期+时间，最常用
TIMESTAMP	YYYY-MM-DD HH:MM:SS	带时区，范围 1970-01-01 ~ 2038-01-19（2038 年问题）

CREATE TABLE t13 (id INT, reg_time DATETIME, class_time TIME, born_year DATE);
INSERT INTO t13 VALUES (1, '2020-11-11 11:11:11', '08:30:00', '1999-01-01');

字符串：CHAR vs VARCHAR

对比项	CHAR	VARCHAR
含义	定长	变长
存储方式	固定长度，不足补空格	长度前缀 + 实际数据
存储空间	固定（CHAR(4) 占 4 字节）	1~2 字节前缀 + 实际内容
查询效率	高	略低（需计算长度）
适用场景	长度固定（手机号、身份证）	长度不固定（姓名、地址）

枚举（ENUM）与集合（SET）

类型	说明	取值方式
ENUM	枚举单选	从预定义列表选一个值
SET	集合多选	从预定义列表选多个值（逗号分隔）

CREATE TABLE t17 (
    id INT,
    hobbies SET('music', 'read', 'movie'),  -- 多选
    gender ENUM('male', 'female')           -- 单选
);
INSERT INTO t17 VALUES (2, 'music,read', 'male');  -- SET 用逗号分隔，值必须在列表中

约束条件

约束条件限制字段取值，保证数据完整性和一致性。

约束	关键字	作用
无符号	UNSIGNED	只存储非负数
非空	NOT NULL	字段不能为空
默认值	DEFAULT	未指定值时使用默认值
唯一	UNIQUE	字段值不能重复
主键	PRIMARY KEY	唯一 + 非空 + 加速查询
自增	AUTO_INCREMENT	自动递增，通常与主键配合

-- UNSIGNED + NOT NULL + DEFAULT
CREATE TABLE t19 (id INT UNSIGNED NOT NULL DEFAULT 10);
INSERT INTO t19 VALUES ();    -- 使用默认值 10
INSERT INTO t19 VALUES (-1);  -- 报错（UNSIGNED 不允许负数）
 
-- UNIQUE 唯一约束
CREATE TABLE t20 (id INT UNIQUE);
 
-- PRIMARY KEY 主键（唯一 + 非空 + 加速查询）
CREATE TABLE t21 (id INT PRIMARY KEY, name VARCHAR(10), age INT);
SELECT * FROM t21 WHERE id = 2;       -- 走主键 B+树索引，速度最快
SELECT * FROM t21 WHERE name = 'tom'; -- 无索引，速度慢
 
-- 复合主键（多个字段共同组成主键）
CREATE TABLE t22 (id INT, name VARCHAR(10), PRIMARY KEY (id, name));
 
-- AUTO_INCREMENT 自增（配合主键）
CREATE TABLE t23 (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10));
INSERT INTO t23 (name) VALUES ('egon1'), ('egon2'), ('egon3');  -- id 自动 1, 2, 3

主键作用：约束效果 = NOT NULL + UNIQUE；查询加速（主键字段走 B+树索引最快）；作为其他表外键的引用目标。

表关系与外键

关系型数据库中表与表之间存在三种基本关系，均通过**外键（Foreign Key）**建立：

关系	实现方式	示例
多对一（Many-to-One）	单向外键	多个学生通过 class_id 指向一个班级
多对多（Many-to-Many）	中间表 + 两个外键	book2author 连接 book 与 author
一对一（One-to-One）	外键 + UNIQUE 约束	customer_id 唯一，一个客户最多对应一条记录

多对一：student.class_id 外键引用 class(id)。外键保证引用完整性——插入不存在的父表 id 会报错。

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(16),
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES class(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

级联（CASCADE）：ON UPDATE CASCADE — 父表主键修改时子表外键自动跟随；ON DELETE CASCADE — 父表记录删除时子表关联记录一并删除。

多对多：需要中间表保存两个外键，把多对多拆成两个多对一。

CREATE TABLE book2author (
    id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT,
    author_id INT,
    FOREIGN KEY (book_id) REFERENCES book(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (author_id) REFERENCES author(id) ON DELETE CASCADE ON UPDATE CASCADE
);

一对一：外键上再加 UNIQUE，使关联值只能出现一次。

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT UNIQUE,
    FOREIGN KEY (customer_id) REFERENCES customer(id)
        ON UPDATE CASCADE ON DELETE CASCADE
);

表复制：INSERT ... SELECT 将查询结果插入另一张表（可跨库）：

INSERT db3.test2(id, name, email, reg_time)
SELECT id, name, email, reg_time FROM db2.test1;

单表查询与 WHERE 过滤

单表查询完整结构：

SELECT DISTINCT 字段1, 字段2, ...
FROM 库.表
WHERE 过滤条件
GROUP BY 分组字段
HAVING 过滤条件
ORDER BY 排序字段
LIMIT 条数;

CASE 条件分支：按字段值生成新列。

SELECT id,
    (CASE
        WHEN name = 'egon' THEN CONCAT(name, '_vip')
        WHEN name = 'alex' THEN CONCAT(name, '_BIGSB')
        ELSE name
    END) AS new_name,
    age
FROM employee;

WHERE 过滤条件汇总：

语法	作用	示例
比较运算	等值/大小/不等	id = 3、id > 3、id >= 3、id != 3
AND	多个条件同时成立	id > 3 AND id < 5
OR	满足一个即可	id < 3 OR id > 10
BETWEEN	范围闭区间	id BETWEEN 3 AND 5
IN	多个指定值	id IN (3, 5, 7)（等价多个 OR）
LIKE	模糊匹配	name LIKE 'ji%'（% 任意长度）、'ji_'（_ 单字符）
REGEXP	正则匹配	name REGEXP '^jin'（以 jin 开头）、'n$'（以 n 结尾）

分组查询 GROUP BY

按一个或多个字段分组，配合聚合函数统计每组数据：COUNT() 数量、SUM() 求和、AVG() 平均值、MAX() 最大值、MIN() 最小值。

-- 按班级统计人数与平均年龄
SELECT class_id, COUNT(*) AS student_count, AVG(age) AS average_age
FROM student
GROUP BY class_id;
 
-- 按多个字段分组
SELECT class_id, gender, COUNT(*) AS total
FROM student
GROUP BY class_id, gender;

要点：

• 查询中同时出现普通字段和聚合函数时，普通字段必须出现在 GROUP BY 中
• COUNT(字段名) 不统计 NULL，统计全部记录用 COUNT(*)
• GROUP BY 必须写在 WHERE 之后

HAVING vs WHERE

对比项	WHERE	HAVING
过滤对象	原始记录	分组结果
执行阶段	分组前	分组后
聚合函数	通常不适合	适合

查询执行顺序：FROM → WHERE → GROUP BY → 聚合 → HAVING → ORDER BY → LIMIT。

-- 查询学生人数大于 3 的班级
SELECT class_id, COUNT(*) AS student_count
FROM student
GROUP BY class_id
HAVING COUNT(*) > 3;
 
-- 先过滤年龄再分组最后过滤人数
SELECT class_id, COUNT(*) AS student_count
FROM student
WHERE age >= 18
GROUP BY class_id
HAVING COUNT(*) >= 2;

⚠️ 不能写 WHERE COUNT(*) > 3（聚合结果只能由 HAVING 过滤）；普通记录过滤应放 WHERE 而非 HAVING。

排序与分页

ORDER BY：ASC（默认）升序、DESC 降序；多字段排序按书写顺序依次比较：ORDER BY class_id ASC, age DESC。

LIMIT：限制返回数量，语法 LIMIT 起始位置, 返回数量，起始位置从 0 开始。分页偏移量 = (页码 - 1) × 每页记录数；分页必须配合 ORDER BY 保证结果稳定。

-- 年龄最大的 3 名学生
SELECT id, name, age FROM student ORDER BY age DESC LIMIT 3;
 
-- 每页 10 条，第 4 页
SELECT * FROM student ORDER BY id LIMIT 30, 10;
 
-- 与分组结合：学生人数最多的前 3 个班级
SELECT class_id, COUNT(*) AS student_count
FROM student
GROUP BY class_id
ORDER BY student_count DESC
LIMIT 3;

多表连接（链表查询）

多表查询通过连接条件组合数据，核心是 表1.字段 = 表2.字段，推荐使用表别名避免同名字段冲突。

-- 隐式连接（FROM 多表 + WHERE 连接条件）
SELECT student.name, class.name
FROM student, class
WHERE student.class_id = class.id;
 
-- 显式连接（推荐）
SELECT s.name AS student_name, c.name AS class_name
FROM student AS s
INNER JOIN class AS c ON s.class_id = c.id;
 
-- 三张表连接（至少两个连接条件）
SELECT s.name, c.name, t.name
FROM student AS s
INNER JOIN class AS c ON s.class_id = c.id
INNER JOIN teacher AS t ON c.teacher_id = t.id;
 
-- LEFT JOIN 保留左表全部记录（COALESCE 处理 NULL）
SELECT c.name, COALESCE(result.student_count, 0)
FROM class AS c
LEFT JOIN (...) AS result ON c.id = result.class_id;

常见错误：忘记连接条件产生笛卡尔积（结果数量异常膨胀）；同名字段需用 表别名.字段；INNER JOIN 会丢弃无匹配的左表记录，需保留全部时用 LEFT JOIN。

物理表与虚拟表连接

表类型	说明	来源
物理表	真实存储数据	student、class、teacher
虚拟表	查询结果临时结果集	子查询、派生表、视图

-- 先分组统计生成虚拟表，再与物理表连接
SELECT c.name AS class_name, result.student_count
FROM class AS c
INNER JOIN (
    SELECT class_id, COUNT(*) AS student_count
    FROM student
    GROUP BY class_id
) AS result ON c.id = result.class_id;

⚠️ FROM/JOIN 后的子查询必须设置别名（如 AS result）。

视图（View）：把查询结果固化为可复用的虚拟表：

CREATE VIEW class_student_count AS
SELECT class_id, COUNT(*) AS student_count FROM student GROUP BY class_id;
 
SELECT c.name, v.student_count
FROM class AS c INNER JOIN class_student_count AS v ON c.id = v.class_id;
 
DROP VIEW class_student_count;

子查询（Subquery）

子查询是嵌套在另一条 SQL 中的查询，外层称主查询。按返回结果分类：

类型	返回结果	常用运算符/位置
标量子查询	一个值	=、>、<
列子查询	一列数据	IN、NOT IN
行子查询	一行数据	比较运算
表子查询	多行多列	FROM 后作派生表（必须别名）

-- 标量子查询：年龄大于平均年龄
SELECT name, age FROM student
WHERE age > (SELECT AVG(age) FROM student);
 
-- 列子查询 + IN
SELECT name, class_id FROM student
WHERE class_id IN (SELECT id FROM class WHERE name IN ('一班', '二班'));
 
-- 关联子查询 EXISTS：有学生的班级
SELECT c.id, c.name FROM class AS c
WHERE EXISTS (SELECT 1 FROM student AS s WHERE s.class_id = c.id);
 
-- NOT EXISTS：没有学生的班级
SELECT c.id, c.name FROM class AS c
WHERE NOT EXISTS (SELECT 1 FROM student AS s WHERE s.class_id = c.id);
 
-- 表子查询作为派生表
SELECT result.class_id, result.student_count
FROM (SELECT class_id, COUNT(*) AS student_count FROM student GROUP BY class_id) AS result
WHERE result.student_count >= 2;
 
-- SELECT 中嵌套标量子查询
SELECT s.name, s.age, (SELECT AVG(age) FROM student) AS average_age FROM student AS s;

排错要点：子查询返回多行时用 = 会报错（改用 IN）；子查询返回 NULL 会使 NOT IN 结果为空（改用 NOT EXISTS）；FROM 后子查询必须设置别名。

子查询进阶：ANY / ALL / EXISTS

在基础 IN/EXISTS 之上，Day53 补充集合比较运算符：

运算符	含义	典型用法
= ANY	等价于 IN	满足任意一个即可
> ANY	大于结果中的至少一个值（即大于最小值）	薪资不垫底
< ANY	小于结果中的至少一个值（即小于最大值）	薪资不冒尖
> ALL	大于全部结果（即大于最大值）	高于所有岗位平均薪资
< ALL	小于全部结果（即小于最小值）	低于所有岗位平均薪资
EXISTS / NOT EXISTS	只判断是否存在记录，真/假	配合关联子查询做存在性判断

-- 薪资高于所有岗位平均薪资
SELECT * FROM employee
WHERE salary > ALL (SELECT AVG(salary) FROM employee GROUP BY post);
 
-- 薪资不垫底（高于至少一个岗位平均）
SELECT * FROM employee
WHERE salary > ANY (SELECT AVG(salary) FROM employee GROUP BY post);

经典题：每个部门最新入职的员工（三种解法）：

-- 解法1：关联子查询（同日期会返回多条）
SELECT e.id, e.name, e.hire_date FROM employee AS e
WHERE e.hire_date = (
    SELECT MAX(e2.hire_date) FROM employee AS e2 WHERE e2.depart_id = e.depart_id);
 
-- 解法2：连接派生表（GROUP BY 部门取 MAX 后 JOIN 回原表）
SELECT e.id, e.name, e.hire_date FROM employee AS e
INNER JOIN (SELECT depart_id, MAX(hire_date) AS max_hire_date
            FROM employee GROUP BY depart_id) AS latest
    ON latest.depart_id = e.depart_id AND latest.max_hire_date = e.hire_date;
 
-- 解法3：窗口函数（MySQL 8.0+，每部门只取一名）
WITH ranked AS (
    SELECT e.*, ROW_NUMBER() OVER (
        PARTITION BY e.depart_id ORDER BY e.hire_date DESC, e.id DESC) AS row_num
    FROM employee AS e)
SELECT * FROM ranked WHERE row_num = 1;

关联子查询（Correlated Subquery）：子查询引用外层字段，对外层每一行各执行一次；NOT IN 子查询含 NULL 时结果为空，优先改用 NOT EXISTS。

视图（View）

视图是虚拟表，本身主要保存查询语句而非数据副本。作用：简化复杂查询、隐藏多表连接细节、限制用户可见字段、统一查询逻辑。

CREATE VIEW department_salary_view AS
SELECT depart_id, COUNT(*) AS employee_count, AVG(salary) AS average_salary
FROM employee GROUP BY depart_id;
 
SHOW CREATE VIEW department_salary_view;   -- 查看定义
DROP VIEW IF EXISTS department_salary_view;

要点：数据仍来自基础表（基础表删字段后视图失效，用 SHOW CREATE VIEW 排查依赖）；含聚合/分组/复杂连接的复杂视图通常只适合查询、不适合直接 UPDATE；命名建议统一 _view 或 v_ 前缀避免与表重名。

触发器（Trigger）

触发器与表关联，在 INSERT/UPDATE/DELETE 发生时自动执行。时机 BEFORE/AFTER；行级对象 NEW（新数据）/OLD（旧数据）：INSERT 只有 NEW，DELETE 只有 OLD，UPDATE 两者都有。适用场景：数据校验、操作日志、自动维护关联数据——复杂业务应放应用层或存储过程。

-- 万能模板（多条语句必须临时修改 DELIMITER）
DELIMITER //
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
BEGIN
    -- 自动执行的 SQL
END//
DELIMITER ;
 
-- 实例1：薪资变更日志（AFTER UPDATE，<=> 安全比较 NULL）
IF NOT (OLD.salary <=> NEW.salary) THEN
    INSERT INTO employee_salary_log(employee_id, old_salary, new_salary)
    VALUES (NEW.id, OLD.salary, NEW.salary);
END IF;
 
-- 实例2：插入前校验（BEFORE INSERT + SIGNAL 抛错）
IF NEW.salary IS NOT NULL AND NEW.salary < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '薪资不能小于0';
END IF;

存储过程（Procedure）与函数（Function）

存储过程是保存在服务端的 SQL 语句集合，用 CALL 过程名() 调用（不是 SELECT）；开发人员调用，DBA 管理。支持 IN（传入）/OUT（返回，通过用户变量 @var 取回）参数。

函数接收参数并 RETURN 一个结果，只能作为 SQL 表达式使用（SELECT/WHERE），不能 CALL。

对比项	存储过程	函数
调用方式	CALL 过程名()	嵌在 SQL 表达式中
返回	可返回结果集/多语句操作	必须 RETURN 一个值
适用	批量插入、复杂流程	计算、转换、判断

-- 函数示例：薪资等级（注意 DETERMINISTIC 与每条分支都要有 RETURN）
CREATE FUNCTION get_salary_level(p_salary DECIMAL(15,2))
RETURNS VARCHAR(20) DETERMINISTIC
BEGIN
    IF p_salary IS NULL THEN RETURN '未知';
    ELSEIF p_salary >= 100000 THEN RETURN '高级';
    ELSEIF p_salary >= 10000 THEN RETURN '中级';
    ELSE RETURN '初级'; END IF;
END

实战：批量插入 300 万条数据。 思路：CROSS JOIN 四个 0-9 数字序列生成 10^4 连续序号，每次插入 10000 条 ×300 批，整体包在一个事务中：

DELIMITER $$
CREATE PROCEDURE insert_3m_data()
BEGIN
    DECLARE v_batch INT DEFAULT 1;
    START TRANSACTION;
    WHILE v_batch <= 300 DO
        INSERT INTO big_data(code, content)
        SELECT MD5(CONCAT(v_batch,'_',nums.seq)), CONCAT('test_data_',v_batch,'_',nums.seq)
        FROM (SELECT a.n + b.n*10 + c.n*100 + d.n*1000 + 1 AS seq
              FROM nums10 a CROSS JOIN nums10 b CROSS JOIN nums10 c CROSS JOIN nums10 d) AS nums;
        SET v_batch = v_batch + 1;
    END WHILE;
    COMMIT;
END$$
DELIMITER ;

计时与体积统计：time mysql -uroot -p -D db13 -e "CALL insert_3m_data();"（real 总耗时）；information_schema.tables 的 data_length/index_length 或 du -sh /var/lib/mysql/db13/big_data* 查磁盘占用。重复测试前先 TRUNCATE TABLE。

流程控制

用于存储过程、函数和触发器内：IF...ELSEIF...ELSE（MySQL 用 ELSEIF 不是 ELSE IF）、CASE 多分支、WHILE（条件为真循环）、REPEAT...UNTIL（先执行一次）、LOOP + LEAVE 退出。

局部变量：DECLARE 变量名 类型 DEFAULT 默认值; 必须声明在 BEGIN 之后、其他执行语句之前。排错：变量未声明先用、WHILE 循环体忘写递增、IF 缺 END IF。

SQL 注入防范

SQL 注入（SQL Injection）：应用将用户输入直接拼接到 SQL 语句，攻击者借特殊输入改变原始执行逻辑。

防范原则：

• 参数化查询（预编译语句）：Java PreparedStatement 的 ? 占位符、Python pymysql 的 %s 占位符；不要自行替换单引号处理特殊字符
• 动态表名/列名/排序字段用白名单映射成固定 SQL 片段
• 输入做长度、类型、格式校验
• 数据库账户遵循最小权限原则，应用不用 root 连接
• 错误详情不回显给用户（只写服务端日志），密码使用安全哈希存储

sql = "SELECT id, name FROM employee WHERE name = %s AND post = %s"
cursor.execute(sql, (username, password))   # 参数化，永不拼接

权限管理

MySQL 将权限信息存储在系统库 mysql 的授权表中。账号 = 用户名 + 主机来源：'egon'@'localhost' 与 'egon'@'%' 是两个不同账号；权限由「用户是谁 + 从哪里连 + 访问对象范围」共同决定。

五级授权范围与对应授权表：

授权范围	对象写法	对应授权表
全局级	*.*	mysql.user
数据库级	db1.*	mysql.db
数据表级	db1.blog	mysql.tables_priv
数据列级	db1.blog(列)	mysql.columns_priv
存储过程级	PROCEDURE db1.p1	mysql.procs_priv

权限范围越具体控制越精细；优先用 GRANT/REVOKE 管理，不建议直接改系统表。

-- 创建用户（高版本先 CREATE USER 再 GRANT；旧版才支持 GRANT ... IDENTIFIED BY）
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
 
-- 各级授予（粒度可到库/表/列）
GRANT ALL PRIVILEGES ON db1.* TO 'username'@'host';
GRANT SELECT, INSERT ON db1.t1 TO 'username'@'host';
GRANT SELECT (id, sub_time), UPDATE (id) ON db1.blog TO 'xxx'@'%';   -- 列级
 
-- 查看权限
SHOW GRANTS FOR 'username'@'host';
 
-- 回收与删除
REVOKE ALL PRIVILEGES ON db1.* FROM 'username'@'host';
DROP USER 'username'@'host';
 
FLUSH PRIVILEGES;   -- 直接改授权表后才需要；8.0 中 CREATE USER/GRANT 即时生效

扩展权限与资源限制： WITH GRANT OPTION 允许用户把自身权限转授他人（只给授权管理员，不给业务账号）；WITH MAX_USER_CONNECTIONS n 限制同时连接数；MAX_QUERIES_PER_HOUR / MAX_UPDATES_PER_HOUR / MAX_CONNECTIONS_PER_HOUR 做每小时配额。收回转授能力：REVOKE GRANT OPTION ON *.* FROM 'yyy'@'%';

排错要点：REVOKE 后仍可访问 → 更高范围的同类授权仍覆盖该表，需重新设计授权层级；列级 UPDATE 需配套 SELECT（更新时要读原值）。核心原则是最小权限原则（Principle of Least Privilege）：业务账号只授必需的库/表/操作，绝不给 ALL ON *.*，应用不用 root 连库。

存储引擎

存储引擎是 MySQL 管理软件与本地文件之间的”翻译层”，决定数据如何存储、索引如何建立、是否支持事务等（类比 Word 选择不同排版引擎）。服务端负责 SQL 解析/权限验证/执行计划，存储引擎负责具体的数据访问与加锁。查看与管理：SHOW ENGINES;（所有引擎及状态）、SHOW VARIABLES LIKE 'default_storage_engine';（当前默认 InnoDB）、SHOW TABLE STATUS FROM db1 LIKE 'blog'; 或查 information_schema.TABLES 看单表引擎、建表指定 CREATE TABLE t1 (id INT) ENGINE=InnoDB;、转换 ALTER TABLE t1 ENGINE=InnoDB;。

⚠️ 引擎转换会重建整张表，大表转换占用磁盘/CPU/I/O；修改默认引擎只影响新建表，已有表需逐个 ALTER。

主要存储引擎对比

对比项	InnoDB（默认）	MyISAM	Memory	Blackhole
事务	支持	不支持	不支持	不支持
外键	支持	不支持	不支持	不支持
行级锁	支持	不支持（表锁）	不支持（表锁）	不支持
崩溃恢复	支持	不支持	不支持	不支持
数据存储	磁盘	磁盘	内存	不存储
适用场景	生产环境首选	读多写少	临时表/缓存	日志/丢弃数据

各存储引擎文件对比

引擎	文件	说明
MyISAM	.frm + .MYD + .MYI	表结构 + 数据 + 索引三者分离
InnoDB	.frm + .ibd	表结构 + 数据+索引合并存储
Memory	.frm	仅表结构，数据存储在内存
Blackhole	.frm	仅表结构，数据不存储

Blackhole 引擎：数据写入后立即丢弃（INSERT 成功但数据不落盘），可用于日志丢弃或作为复制架构中的”黑洞”主库。

InnoDB 核心组件

组件	说明
Buffer Pool	缓存池，存储索引和数据页，性能优化核心。生产环境可分配 60%~70% 物理内存
Redo Log	重做日志，用于崩溃恢复，确保持久性
Undo Log	回滚日志，用于事务回滚，提供 MVCC 机制
Binlog	二进制日志，用于主从复制和数据恢复

存储单位层级

单位	说明
Row（行）	数据存储的最小单位
Page（页）	InnoDB 默认页大小 16KB，数据读写以页为单位
Extent（区）	由 64 个连续页组成，约 1MB
Segment（段）	由多个区组成，每个索引或表占用一个段
Tablespace（表空间）	由多个段组成，InnoDB 存储的逻辑最高层

补充：一页能存多少行不固定——由行记录大小、变长字段和页内管理信息共同决定；区是空间分配单位，不专属某张表；禁止直接删除 .ibd 文件删表（会造成数据字典与物理文件不一致），必须 DROP TABLE / TRUNCATE TABLE。

表空间：共享 vs 独立

对比项目	共享表空间	独立表空间
存储方式	多张表共用系统表空间（如 ibdata1）	每表一个 .ibd 文件
文件管理	集中管理	可按表管理
删表后空间	通常不归还操作系统	删表/重建可释放文件空间
表迁移	不便单独迁移	支持按表导出导入
配置	innodb_file_per_table=OFF	innodb_file_per_table=ON（现代默认）

SHOW VARIABLES LIKE 'innodb_file_per_table';
 
-- 查看表的数据/索引空间与空闲碎片
SELECT table_schema, table_name, engine, table_rows,
       data_length, index_length, data_free
FROM information_schema.tables
WHERE table_schema = 'db1';

⚠️ 修改 innodb_file_per_table 只影响新建表，已有表需 ALTER TABLE ... ENGINE=InnoDB 重建迁移；DELETE 只删逻辑记录不会缩小 .ibd 文件，回收空间靠重建表。

在线迁移表：传输表空间

不停服将 InnoDB 表迁到另一实例/服务器。前提：两端表结构完全一致（字段顺序/类型/索引/字符集，用 SHOW CREATE TABLE 比对）且都是 InnoDB。

-- ① 源端：刷新表并锁定写入（生成 .cfg 元数据文件）
FLUSH TABLES user_account FOR EXPORT;
-- ② shell：复制 .ibd + .cfg 到目标库目录，chown mysql:mysql
-- ③ 源端：复制完成后解锁
UNLOCK TABLES;
-- ④ 目标端：建好结构一致的空表后丢弃本地表空间
ALTER TABLE user_account DISCARD TABLESPACE;
-- ⑤ 文件就位后导入并验证
ALTER TABLE user_account IMPORT TABLESPACE;

排错：IMPORT 报错 → 确认先执行了 DISCARD、检查文件路径/属主/权限；只拷 .ibd 缺 .cfg → 传统传输表空间两者都要；FOR EXPORT 之前拷贝 → 数据不完整。

Undo Log 与在线自动收缩

Undo 记录修改前的数据版本：①事务回滚恢复原值；②MVCC 支持其他事务读旧版本。事务提交后 Undo 不一定立即删除——只要还有活动事务需要读旧版本就必须保留，无读者后才可回收；空间超阈值由后台自动截断收缩。

SHOW VARIABLES LIKE 'innodb_undo%';           -- Undo 配置
SELECT * FROM information_schema.innodb_trx;   -- 当前事务（重点看运行时长）

[mysqld]
innodb_undo_tablespaces=2            # 独立 Undo 表空间个数
innodb_undo_log_truncate=ON          # 开启在线自动收缩
innodb_max_undo_log_size=1073741824  # 超过阈值触发截断

⚠️ 长事务是 Undo 膨胀头号原因（旧版本无法回收）；禁止直接删除 Undo 文件；开启 truncate 后也不会即时收缩，要满足大小阈值与版本清理条件。

InnoDB 逻辑架构

SQL 请求流程：客户端发 SQL → 服务端完成连接管理、权限验证、SQL 解析与执行计划 → 执行器调用 InnoDB 接口 → 所需数据页在 Buffer Pool 中则直接访问，不在则从磁盘读入缓冲池 → 修改先发生在内存 → 按日志与刷盘机制落盘。

内存结构： Buffer Pool（缓存数据页/索引页，性能核心）、Change Buffer（缓存非唯一二级索引页修改）、Adaptive Hash Index（自适应哈希索引）、Log Buffer（暂存重做日志内容）、Dictionary Cache（元数据缓存）。磁盘结构：表空间文件、重做日志文件、双写缓冲区域、撤销日志区域、数据与索引。

写入与崩溃恢复路径：

事务修改 → Buffer Pool 中数据页变为脏页（Dirty Page）→ 修改记录到 Redo Log（WAL）
→ 提交时按配置保证日志持久化 → 后台线程逐步把脏页刷回数据文件
→ 异常重启时用 Redo Log 恢复已提交但未落盘的数据

注意：COMMIT 后数据页不会立即同步写数据文件——事务提交、日志持久化、脏页刷盘是三个不同过程。Doublewrite Buffer 降低数据页部分写（partial write）导致页面损坏的风险。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';    -- 缓冲池大小
SHOW VARIABLES LIKE 'innodb_log_buffer_size';     -- 日志缓冲区
SHOW ENGINE INNODB STATUS\G                       -- 综合状态（含最近死锁）
SELECT * FROM information_schema.INNODB_TRX;      -- 当前事务
SELECT @@transaction_isolation;                   -- 隔离级别

诊断提示：磁盘 I/O 高 → 检查缓冲池大小与命中率、是否存在大量随机访问；死锁 → SHOW ENGINE INNODB STATUS 查看最近死锁并统一事务访问顺序；二级索引查询慢 → EXPLAIN 检查回表与扫描行数。

聚簇索引数据组织

InnoDB 聚簇索引以 B+树组织：叶子节点保存完整行数据，非叶子节点保存索引键与指针；主键查询直达聚簇索引叶子节点；二级索引若不能覆盖所需字段，需按主键回聚簇索引再取一次——即回表。

索引与慢查询优化

索引（Index，也称 Key）是加速查询的数据结构。无索引时从第一行逐行比对（全表扫描）；有索引时先在索引结构中定位再取数据。常见分类：

索引类型	特点
主键索引	主键约束自动创建，唯一 + 非空；InnoDB 中即聚簇索引
唯一索引	值不重复，允许 NULL
普通索引	无唯一性要求，纯加速
联合索引	多字段组成，如 (name, age)，遵循最左匹配
全文索引	文本搜索场景
空间索引	空间数据类型

CREATE INDEX idx_name ON user_info (name);             -- 普通
CREATE UNIQUE INDEX uk_name ON user_info (name);       -- 唯一
CREATE INDEX idx_city_age ON user_info (city, age);    -- 联合
DROP INDEX idx_city_age ON user_info;                  -- 删除
SHOW INDEX FROM user_info;

索引失效典型场景：对索引字段做函数运算、前置模糊 LIKE '%xx'（LIKE 'xx%' 可以走）、字段类型不一致引发隐式转换、联合索引缺最左列。索引不是越多越好——占空间且增加写操作维护成本。InnoDB 使用 B+树 作为索引数据结构：所有数据存储在叶子节点，叶子节点间有链表连接，范围查询效率高。

数据结构演进：为什么是 B+树

结构	问题 / 特点
二叉搜索树	有序插入时退化为链表，O(log n) → O(n)
平衡二叉树（AVL/红黑）	避免倾斜，但每节点只存少量数据、树高高，磁盘随机读次数多
B 树	多路平衡、节点存多键值、高度低，但节点内同时存键值和数据记录
B+树	非叶子节点只存索引键不存数据 → 单节点容纳更多键、树更矮；完整数据全在叶子节点；叶子节点双向链表相连 → 等值与范围查询都强

数据库索引设计不只看比较次数，还要考虑磁盘页大小、磁盘读取次数、节点容量和范围扫描能力。InnoDB 中聚簇索引叶子节点存整行数据，二级索引叶子节点存主键值。

索引价值：选择性（Selectivity）与基数（Cardinality）

• 选择性：字段区分不同记录的能力，值越分散越高——身份证号高、性别低；
• 基数：索引列中不同值的大致数量（SHOW INDEX 的 Cardinality 列为估算值），基数越高索引通常越有价值。

索引效果还取决于：数据量、条件字段区分度、返回数据量、字段是否参与函数计算、隐式类型转换、联合索引字段顺序、优化器成本判断。

SELECT COUNT(DISTINCT gender) FROM user;   -- 对比不同字段的选择性
SHOW INDEX FROM user;   -- Key_name/Cardinality/Index_type 等

聚集索引 vs 辅助索引

索引类型	说明
聚集索引（Clustered Index）	叶子节点存储完整行数据。InnoDB 中主键就是聚集索引
辅助索引（Secondary Index）	叶子节点存储主键值。查询时需要回表获取完整数据

概念	说明
覆盖索引	查询所需的数据在辅助索引中已经包含，无需回表查询完整行
回表	辅助索引中只有主键值，需要根据主键再到聚集索引中查询完整行

联合索引与最左前缀匹配

联合索引（如 (a, b, c)）遵循最左前缀匹配原则：查询条件必须从索引的最左列开始才能使用该索引。

-- 联合索引 idx(a, b, c)
-- 以下查询可以使用索引
SELECT * FROM t WHERE a = 1;
SELECT * FROM t WHERE a = 1 AND b = 2;
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;
 
-- 以下查询不能使用索引
SELECT * FROM t WHERE b = 2;           -- 没有从最左列开始
SELECT * FROM t WHERE a = 1 AND c = 3; -- 跳过了中间列

范围条件截断：等值匹配可以继续向右匹配后续字段，遇到范围条件后，其右侧字段的索引利用通常被截断：

-- 联合索引 idx(a, b, c)
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3;
-- a 等值 + b 范围可定位，但 b 之后的 c 通常无法继续缩小索引扫描范围

注意：查询条件的书写顺序不影响匹配（WHERE b = 2 AND a = 1 优化器会重排），能否充分使用仍以 EXPLAIN 为准。字段顺序设计原则：高频条件、区分度高、等值字段在前，范围字段在后。

EXPLAIN 分析执行计划

EXPLAIN SELECT * FROM users WHERE age > 18;

字段	说明
type	访问类型（ALL > index > range > ref > eq_ref > const，性能依次提升）
possible_keys	可能使用的索引
key	实际使用的索引
rows	扫描的行数估算
Extra	Using index = 覆盖索引；Using where = 需要回表

EXPLAIN ANALYZE 会实际执行语句并显示真实访问行数、耗时与执行步骤，比估算的 rows 更可靠：

EXPLAIN ANALYZE SELECT * FROM employee WHERE department_id = 10;

验证索引效果的实验方法：保持环境一致，建索引前后用同一 SQL 对比执行计划；小数据量时优化器可能认为全表扫描更快；走了索引仍慢时检查区分度、回表次数与排序/临时表。

慢查询日志

-- 查看慢查询日志状态
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
 
-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 超过2秒的查询记录到日志

索引使用原则与查询条件

索引设计必须结合查询模式、数据分布和返回结果判断，而不是只要字段出现在 WHERE 中就创建索引。通常优先考虑：查询和 JOIN 中高频使用的列、选择性和基数较高的列、非空且长度较短的列；同时评估索引占用空间以及对 INSERT/UPDATE/DELETE 的维护成本。

常见查询条件与索引效果：

查询方式	常见表现
高区分度字段等值查询	通常能显著缩小扫描范围
命中大量记录的等值查询	回表成本高，优化器可能选择全表扫描
小范围查询	通常适合使用索引
过大范围查询	扫描大量索引记录，收益可能下降
索引字段参与运算或函数	通常无法按原索引值直接定位
查询参数类型与列类型不一致	可能发生隐式转换，影响索引使用
LIKE 'prefix%'	通常可以利用索引的有序性
LIKE '%keyword' / LIKE '%keyword%'	普通 B+树索引通常无法有效定位

-- 把运算放到常量侧，避免改写索引列
SELECT COUNT(id) FROM s1 WHERE id = 10000 / 12;
 
-- 日期函数改写为范围条件
SELECT COUNT(id)
FROM s1
WHERE created_at >= '2025-01-01'
  AND created_at < '2026-01-01';
 
-- 大数据量下使用基于主键的范围分页
SELECT id, name, price
FROM product
WHERE id > 1000
ORDER BY id
LIMIT 20;

全表统计、返回绝大部分记录或列表使用 SELECT * 时，索引未必带来收益。应结合 EXPLAIN 的 type、key、rows、filtered、Extra，以及 EXPLAIN ANALYZE 的真实行数和耗时判断。查询参数类型应与字段定义保持一致，联合索引仍需遵循最左前缀和范围条件截断原则。

索引条件下推（ICP）

索引条件下推（Index Condition Pushdown，ICP） 会把能够在索引层判断的条件提前执行，减少不必要的回表。假设存在联合索引 (name, age)：

CREATE INDEX idx_user_name_age ON user(name, age);
 
SELECT *
FROM user
WHERE name LIKE 'egon%'
  AND age = 18;

没有 ICP 时，数据库可能先扫描满足 name 的辅助索引记录、回表后再判断 age；使用 ICP 时，可以在索引扫描阶段先判断 age = 18，只有更可能符合条件的记录才回表。

对比项	覆盖索引	索引条件下推
是否需要回表	查询字段被覆盖时不需要	仍然可能需要回表
核心作用	直接从索引返回所需字段	索引层提前过滤，减少回表次数
常见执行信息	Using index	Using index condition

Using index condition 不代表完全不回表。实际收益取决于扫描范围、过滤比例、返回字段、数据分布和执行计划，可通过以下命令检查：

SHOW VARIABLES LIKE 'optimizer_switch';
SET SESSION optimizer_switch = 'index_condition_pushdown=off';
SET SESSION optimizer_switch = 'index_condition_pushdown=on';
EXPLAIN ANALYZE SELECT * FROM user WHERE name LIKE 'egon%' AND age = 18;

事务

事务（Transaction）是一组 SQL 语句的逻辑单元，要么全部成功提交，要么在异常时回滚，是保证数据一致性的核心机制。事务常用于转账、支付和库存扣减等多语句业务。

ACID 四大特性

特性	说明
原子性（Atomicity）	所有操作要么成功，要么全部撤销
一致性（Consistency）	执行前后都满足约束和业务规则
隔离性（Isolation）	并发事务按隔离规则互不产生不应有的影响
持久性（Durability）	提交结果在故障恢复后仍应保留

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 异常处理路径执行 ROLLBACK;

事务运行模式与保存点

MySQL 常见运行方式包括：自动提交（每条修改语句完成后自动提交）、显式事务（BEGIN/START TRANSACTION 后手动提交或回滚）和关闭自动提交后形成的隐式连续事务。

SELECT @@autocommit;
SET autocommit = 0;
UPDATE employee SET name = 'WXX' WHERE id = 3;
COMMIT;
SET autocommit = 1;

保存点（Savepoint） 是事务内的临时回滚位置。ROLLBACK TO SAVEPOINT 只撤销保存点之后的修改，事务仍可继续；提交或完整回滚后保存点失效。

START TRANSACTION;
UPDATE employee SET name = 'EGON_NB' WHERE id = 1;
SAVEPOINT one;
UPDATE employee SET name = 'ALEX_SB' WHERE id = 2;
SAVEPOINT two;
INSERT INTO employee VALUES (19, 'egonxxx', 19);
ROLLBACK TO SAVEPOINT two;
RELEASE SAVEPOINT one;
COMMIT;

注意：应使用支持事务的存储引擎；DDL 等语句可能产生隐式提交；关闭自动提交后要及时 COMMIT 或 ROLLBACK，避免长事务占用连接和锁。

并发读现象与事务隔离级别

三种并发读现象

现象	说明
脏读（Dirty Read）	读取其他事务尚未提交、随后可能回滚的数据
不可重复读（Non-Repeatable Read）	同一事务两次读取同一行，期间其他事务修改并提交
幻读（Phantom Read）	同一事务按相同条件重复查询，符合条件的记录集合发生变化
丢失更新（Lost Update）	并发修改相互覆盖，后提交结果覆盖先提交结果

四种事务隔离级别

隔离级别	脏读	不可重复读	幻读控制
READ UNCOMMITTED	可能	可能	控制最弱
READ COMMITTED	避免	可能	需结合当前读和锁分析
REPEATABLE READ（InnoDB 默认）	避免	避免快照读下的重复读取	当前读结合 Next-Key Lock，快照读依赖 MVCC
SERIALIZABLE	避免	避免	使用更强的锁控制

SELECT @@transaction_isolation;
SELECT @@global.transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

普通 SELECT 与 SELECT ... FOR UPDATE 的行为不同。事务隔离实验至少需要两个连接，并记录事务启动、读取、修改和提交的先后顺序。

锁机制与 MVCC

锁的分类与加锁读

锁用于协调并发访问，锁范围和持有时间越大，对并发性能的影响通常越明显。

分类维度	锁类型	说明
锁粒度	行锁、表锁、页锁	行锁范围小；表锁范围大但管理简单
锁模式	共享锁（S）、排他锁（X）	共享锁通常允许并发读；排他锁用于独占修改
使用方式	悲观锁、乐观锁	悲观锁操作前加锁；乐观锁提交时检查版本或条件
InnoDB 算法	Record、Gap、Next-Key	作用于索引记录和索引间隙

-- 共享锁（旧写法：LOCK IN SHARE MODE）
SELECT * FROM employee WHERE id = 3 FOR SHARE;
 
-- 当前读并申请排他锁
SELECT * FROM employee WHERE id = 3 FOR UPDATE;

InnoDB 锁索引原理与锁算法

InnoDB 实际依赖索引访问路径控制并发，锁定的是索引记录和索引间隙，而不是脱离索引树存在的抽象数据行。命中主键通常锁聚集索引；命中辅助索引时会沿辅助索引定位，并根据主键访问聚集索引。缺少合适索引时可能扫描和锁定大量记录，锁范围扩大，不能简单把所有情况概括成固定的传统表锁。

算法	含义	作用
Record Lock	记录锁，只锁索引记录本身	精确保护匹配行
Gap Lock	间隙锁，只锁索引记录之间的间隙	阻止其他事务在间隙插入
Next-Key Lock	临键锁，Record + Gap	锁记录及间隙，抑制幻读

在常见 REPEATABLE READ 加锁读语境下：唯一索引等值命中通常只需 Record Lock；非唯一索引等值和范围查询通常涉及 Next-Key Lock；范围锁的边界仍要结合 MySQL 版本、隔离级别、索引和执行计划确认。

表级锁与写操作

表级锁以整张表为单位。LOCK TABLES ... READ 通常允许读取但限制写入，LOCK TABLES ... WRITE 通常只允许当前连接读写；使用后必须 UNLOCK TABLES。支持事务的存储引擎中，INSERT、UPDATE、DELETE 通常自动申请排他锁，并持续到事务提交或回滚。

LOCK TABLES employee READ;
UNLOCK TABLES;
 
SET SESSION innodb_lock_wait_timeout = 10;
EXPLAIN UPDATE employee SET name = 'EGON_LOCK' WHERE id = 3;

锁等待、死锁与意向锁

锁等待发生在事务申请与已有锁冲突时。死锁则是多个事务互相持有对方需要的锁，形成循环等待，例如事务一先锁 id=1 再请求 id=5，事务二先锁 id=5 再请求 id=1。辅助索引定位后还需访问聚集索引，在访问顺序不一致时也可能增加死锁风险。

InnoDB 通常会检测死锁并回滚其中一个事务；应用应捕获死锁错误并重试完整事务。降低风险的方法包括统一访问顺序、缩短事务范围、为条件建立合适索引、减少事务中的无关操作和及时结束异常事务。

意向锁（Intention Lock） 是表级标记，表示事务准备在表中的某些行上加共享锁或排他锁，用于快速判断表级锁与行级锁的潜在冲突，常见类型为 IS、IX。

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G;
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM sys.innodb_lock_waits;

MVCC（多版本并发控制）

MVCC 通过 Undo Log 保存数据历史版本，使一致性读在很多场景下不必等待写锁：

读类型	读取内容	是否通常加锁
快照读（Snapshot Read）	按事务可见性规则读取历史版本	不加锁的一致性读
当前读（Current Read）	读取最新版本	通常加锁，如 FOR UPDATE、UPDATE、DELETE

REPEATABLE READ 下，快照读依赖事务视图保持一致性；当前读要读取最新版本，通常结合 Record/Gap/Next-Key Lock 控制并发和幻读。隔离级别、事务启动时机、SQL 类型、索引和 MySQL 版本都会影响最终行为，应用 EXPLAIN、锁状态和双连接实验验证。

日志管理

日志类型	作用	典型应用
错误日志（Error Log）	记录启动、运行、停止时的错误信息	故障排查首要依据
慢查询日志（Slow Query Log）	记录执行时间超过阈值的 SQL	性能优化数据来源
Binlog（二进制日志）	记录数据变更操作（INSERT/UPDATE/DELETE）	主从复制、数据恢复
Redo Log（事务日志）	记录数据页的物理修改	崩溃恢复，保证持久性

Binlog vs Redo Log：Binlog 记录逻辑变更（SQL 级别），用于主从复制与数据恢复；Redo Log 记录物理修改（数据页级别），用于崩溃恢复。两者职责不同、缺一不可。

备份与恢复

备份方式	工具	备份内容	恢复速度	适用场景
逻辑备份	mysqldump	SQL 语句	慢	小数据量、迁移
物理备份	xtrabackup	数据文件	快	大数据量、生产环境

# mysqldump 逻辑备份
mysqldump -uroot -p --all-databases > all.sql
mysqldump -uroot -p db1 > db1.sql
mysql -uroot -p < all.sql          # 恢复
 
# xtrabackup 物理备份
xtrabackup --backup --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full

主从复制

主从复制（Replication）是将主库的数据变更同步到从库的机制，是 MySQL 高可用架构的基础。

原理流程

主库写 Binlog → 从库 IO 线程连主库请求 → 主库 dump 线程发送 Binlog
→ 从库 IO 线程写入中继日志（Relay Log）→ 从库 SQL 线程读取并执行 → 数据同步

Binlog 三种格式

格式	说明	优缺点
STATEMENT	记录执行的 SQL 语句	日志量小，但非确定性函数可能导致主从不一致
ROW	记录行级别的变更	日志量大，但最安全，主从一致性好
MIXED	混合模式	自动选择 STATEMENT 或 ROW

两种复制方式

方式	说明	特点
异步复制	主库不等待从库确认	性能高，可能丢失数据
半同步复制	主库等待至少一个从库确认	数据一致性更高，性能略低

架构模式

架构	说明	适用场景
一主一从	一个主库一个从库	读写分离入门
一主多从	一个主库多个从库	读多写少场景
双主（主主）	两个互为主从	高可用，需配合 Keepalived
双主+多从+Keepalived	双主高可用 + 多个从库	生产环境高可用方案

高可用与读写分离

中间件	定位	核心功能
MHA	高可用方案	故障检测、自动选举新 Master、数据补偿、VIP 漂移（应用无感知）
Atlas	读写分离中间件	对外统一地址，写请求→主库，读请求→从库
MyCat	分布式数据库中间件	读写分离 + 分库分表（水平拆分突破单库瓶颈）+ 多租户 + 全局序列（分布式唯一 ID）

数据库优化

优化层面	内容
硬件优化	CPU、内存、SSD 磁盘选型
系统优化	内核参数、文件系统
配置优化	InnoDB Buffer Pool、日志刷盘策略
SQL 优化	索引、查询语句、执行计划分析

配置项	作用
innodb_buffer_pool_size	缓存池大小，建议 60%~70% 物理内存
innodb_log_file_size	Redo Log 文件大小，影响恢复速度
innodb_flush_log_at_trx_commit	Redo Log 刷盘策略（0/1/2）
sync_binlog	Binlog 刷盘策略（0/1/N）

相关页面

• 数据库导学（Day48） — MySQL 学习周期路线图
• MySQL 数据库 1（Day49） — 密码管理/配置文件/SQL/安装实操
• MySQL 数据库 2（Day50） — 多实例部署/库操作/存储引擎/数据类型/约束
• MySQL 数据库 3（Day51） — 表关系/外键/单表查询/WHERE 过滤
• MySQL 数据库 4（Day52） — 分组查询/HAVING/排序分页/多表连接/子查询
• MySQL 数据库 5（Day53） — 子查询进阶/视图/触发器/存储过程/SQL 注入
• MySQL 数据库 6（Day54） — 授权体系/存储引擎管理/InnoDB 架构
• MySQL 数据库 7（Day55） — 表空间与 .ibd/共享 vs 独立表空间/在线迁移表/Undo 在线收缩/索引入门
• MySQL 数据库 8（Day56） — 索引专题：选择性/基数/B+树演进/覆盖索引与回表/EXPLAIN ANALYZE/最左前缀
• MySQL 数据库 9（Day57） — 索引查询进阶/ICP/覆盖索引与回表/事务 ACID/并发读现象
• MySQL 数据库 10（Day58） — 事务运行模式/保存点/锁分类/表级锁/锁等待与死锁
• MySQL 数据库 11（Day59） — InnoDB 锁索引原理/Record-Gap-Next-Key Lock/死锁/MVCC/快照读与当前读
• PHP 体系架构 — LNMP 架构中的 MySQL 角色（Day38 部署）
• Nginx 概念页 — Web 层反向代理，LNMP 上游 MySQL
• HTTP 协议 — Web 应用层与会话保持（Redis 会话共享）
• Keepalived — 高可用 VIP 漂移（双主架构配合）
• LVS — 集群架构中的负载均衡层