MySQL

本文以 MySQL 8.4.x LTS 为主，SQL 示例在 MySQL Community Server 8.4.6 上验证。存储引擎相关内容主要讨论 InnoDB，与旧版行为不同的地方会单独注明。命令行示例使用 mysql 客户端，配置文件片段放在 [mysqld] 配置组下。

mysql -h 127.0.0.1 -P 3306 -u root -p

1. SQL

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象（数据库、表、字段）
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

这是便于学习的分类，SELECT 在 MySQL 手册中归入数据操作语句。START TRANSACTION、COMMIT、ROLLBACK 属于事务控制语句。

1.1 DDL

1.1.1 数据库操作

先创建并选择 demo_sql，删除数据库的语句在完成练习后执行。

查询

sql

-- 查询所有数据库（方法一）
SHOW DATABASES;

-- 查询所有数据库（方法二）
SHOW SCHEMAS;

-- 查询当前数据库
SELECT DATABASE();

创建

sql

CREATE DATABASE IF NOT EXISTS demo_sql
    DEFAULT CHARSET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

删除

sql

DROP DATABASE IF EXISTS demo_sql;

使用

sql

USE demo_sql;

1.1.2 表操作

先创建 tb_user，其余语句以相应对象存在为前提。改表时需要保留的默认值、注释和约束应在完整列定义中一并指定。

查询当前数据库所有表

sql

SHOW TABLES;

查询表结构

sql

DESC tb_user;

查询指定表的建表语句

sql

SHOW CREATE TABLE tb_user;

创建

sql

-- CREATE TABLE [IF NOT EXISTS] 表名 (
--     字段名1 字段类型1 [COMMENT '字段注释'],
--     字段名2 字段类型2 [COMMENT '字段注释'],
--     字段名3 字段类型3 [COMMENT '字段注释'],
--     ...
--     字段名n 字段类型n [COMMENT '字段注释']
-- ) [COMMENT '表注释'];

CREATE TABLE IF NOT EXISTS tb_user (     
    `id`     INT              COMMENT '编号',
    `name`   VARCHAR(50)      COMMENT '姓名',
    `age`    TINYINT UNSIGNED COMMENT '年龄',
    `gender` CHAR(1)          COMMENT '性别'
) COMMENT '用户表';

添加字段

sql

-- ALTER TABLE 表名 ADD 字段名 类型 [COMMENT '注释'] [约束];
ALTER TABLE tb_user ADD `nickname` VARCHAR(20) COMMENT '昵称';

修改数据类型

sql

-- ALTER TABLE 表名 MODIFY 字段名 数据类型;
ALTER TABLE `tb_user` MODIFY `nickname` VARCHAR(32);

修改字段名和字段类型

sql

-- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型 [COMMENT '注释'] [约束];
ALTER TABLE `tb_user` CHANGE `nickname` `nick` VARCHAR(30) COMMENT '用户昵称';

删除字段

sql

-- ALTER TABLE 表名 DROP 字段名;
ALTER TABLE `tb_user` DROP `nick`;

修改表名

sql

-- ALTER TABLE 表名 RENAME TO 新表名;
ALTER TABLE `tb_user` RENAME TO `tbl_user`;

清空表并重置自增计数

sql

-- TRUNCATE TABLE 表名;
TRUNCATE TABLE `tbl_user`;

删除表

sql

-- DROP TABLE [IF EXISTS] 表名;
DROP TABLE IF EXISTS `tbl_user`;

TRUNCATE TABLE 保留表定义，不逐行执行 DELETE，也不触发 DELETE 触发器。它与多数建表、改表语句一样会隐式提交，不能用普通事务的 ROLLBACK 撤销。InnoDB 的原子 DDL 保证 DDL 在崩溃恢复后的完整性，不表示 DDL 可以随业务事务回滚。参见隐式提交。

1.1.3 数据类型

数值类型：

类型	大小
`TINYINT`	1 byte
`SMALLINT`	2 bytes
`MEDIUMINT`	3 bytes
`INT` 或 `INTEGER`	4 bytes
`BIGINT`	8 bytes
`FLOAT`	4 bytes
`DOUBLE`	8 bytes
`DECIMAL`	变长（按精度存储）

字符串类型：CHAR、VARCHAR、TINYBLOB、TINYTEXT、BLOB、TEXT、MEDIUMBLOB、MEDIUMTEXT、LONGBLOB、LONGTEXT

CHAR(M)、VARCHAR(M) 的 M 表示字符数，实际字节数取决于字符集。VARCHAR 还受 65,535 字节的行大小限制，可存储字符数需结合字符集和行内其他数据的空间占用计算。BLOB 存储二进制数据，TEXT 使用字符集和排序规则。金额通常用定点类型 DECIMAL，避免二进制浮点数带来的表示误差。

日期类型：

类型	大小	格式
`DATE`	3 bytes	`YYYY-MM-DD`
`TIME`	3 bytes	`HH:MM:SS`
`YEAR`	1 byte	`YYYY`
`DATETIME`	5 bytes	`YYYY-MM-DD HH:MM:SS`
`TIMESTAMP`	4 bytes	`YYYY-MM-DD HH:MM:SS`

表中未计小数秒。TIME、DATETIME、TIMESTAMP 支持 0 至 6 位小数秒，分别额外占用 0 至 3 字节。DATETIME 的非小数部分从 MySQL 5.6.4 起使用 5 字节。TIMESTAMP 在存取时进行会话时区与 UTC 的转换，8.4 的范围上限仍在 2038 年，DATETIME 不做这种时区转换。TIME 也可表示时长，其范围超过一天。参见存储空间和日期时间类型。

1.2 DML

先建立练习表：

sql

USE demo_sql;
CREATE TABLE tb_employee (
    id INT PRIMARY KEY,
    workno VARCHAR(20),
    name VARCHAR(50),
    gender CHAR(1),
    age TINYINT UNSIGNED,
    idcard CHAR(18),
    entrydate DATE,
    city VARCHAR(50) DEFAULT '广州'
) ENGINE=InnoDB;

给指定字段添加数据

sql

-- INSERT INTO 表名(字段1, 字段2, ...) VALUES (值1, 值2, ...);
INSERT INTO `tb_employee` (`id`, `workno`, `name`, `gender`, `age`, `idcard`, `entrydate`)
VALUES (1, '1', 'zhh', '男', 10, '12345678901234567X', '2001-03-16');

给全部字段添加数据

sql

-- INSERT INTO 表名 VALUES (值1, 值2, ...);
INSERT INTO `tb_employee`
VALUES (2, '2', 'zs', '男', 9, '123456789012345678', '2000-01-02', '广州');

批量添加数据

sql

-- INSERT INTO 表名(字段1, 字段2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...);
-- INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO `tb_employee`
VALUES (3, '3', 'ls', '女', 11, '123456789012345677', '2000-01-03', '广州'),
       (4, '4', 'ww', '男', 44, '123456789012345674', '2004-04-04', '上海');

修改数据

sql

-- UPDATE 表名 SET 字段名1=值1, 字段名2=值2, ... [WHERE 条件];
UPDATE `tb_employee`
SET `name` = 'Howard'
WHERE `id` = 1;

删除数据

sql

-- DELETE FROM 表名 [WHERE 条件];
DELETE
FROM `tb_employee`
WHERE `id` = 2;

1.3 DQL

1.3.1 基础查询

查询多个字段

sql

-- SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT `id`, `name`, `age` FROM `tb_employee`;

-- SELECT * FROM 表名;
SELECT * FROM `tb_employee`;

设置别名

sql

-- SELECT 字段1 [AS 别名1], 字段2 [AS 别名2], ... FROM 表名;
SELECT `id` AS `uid`, `name` AS `uname`, `age` FROM `tb_employee`;

-- AS 可省略
SELECT `id` `uid`, `name` `uname`, `age` FROM `tb_employee`;

去重

sql

-- SELECT DISTINCT 字段列表 FROM 表名;
SELECT DISTINCT `city` FROM `tb_employee`;

1.3.2 条件查询

比较运算符	功能
`>`	大于
`>=`	大于等于
`<`	小于
`<=`	小于等于
`=`	等于
`<>` 或 `!=`	不等于
`BETWEEN ... AND ...`	在某个范围内（含最大、最小值）
`IN (...)`	在 `IN` 之后的列表中的值
`LIKE 占位符`	模糊匹配（`_` 匹配单个字符，`%` 匹配任意个字符）
`IS NULL`	是 `NULL`

逻辑运算符	功能
`AND`	并且（多个条件同时成立）
`OR`	或者（任一条件成立）
`NOT`	非，不是

使用标准关键字可避免 SQL 模式差异。例如启用 PIPES_AS_CONCAT 后，|| 表示字符串拼接。字符串字面量使用单引号，反引号用于标识符。

与 NULL 的普通比较结果是未知值，判断空值应使用 IS NULL 或 IS NOT NULL。WHERE 只保留条件为真的行。尤其注意 NOT IN 列表或子查询含 NULL 时的结果。

sql

-- SELECT 字段列表 FROM 表名 WHERE 条件列表;
SELECT * FROM `tb_employee` WHERE `age` >= 20;
SELECT * FROM `tb_employee` WHERE `age` BETWEEN 40 AND 50;
SELECT * FROM `tb_employee` WHERE `idcard` IS NULL;
SELECT * FROM `tb_employee` WHERE `id` IN (1, 2, 3);
SELECT * FROM `tb_employee` WHERE `name` LIKE 'z_';

1.3.3 聚合函数

将一列数据作为一个整体，进行纵向计算。

常见函数	功能
`COUNT`	统计数量
`MAX`	最大值
`MIN`	最小值
`AVG`	平均值
`SUM`	求和

sql

-- SELECT 聚合函数(字段列表) FROM 表名;
SELECT COUNT(*) FROM `tb_employee`;
SELECT AVG(`age`) FROM `tb_employee`;
SELECT MAX(`age`) FROM `tb_employee`;
SELECT MIN(`age`) FROM `tb_employee`;
SELECT SUM(`age`) FROM `tb_employee`;

COUNT(*) 统计结果集的行数，COUNT(列) 只统计该列非 NULL 的行。这里的 SUM、AVG、MIN、MAX 忽略 NULL，没有非空输入时返回 NULL，而 COUNT 返回 0。

1.3.4 分组查询

sql

-- SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组过滤条件];
SELECT `gender`, COUNT(*) FROM `tb_employee` GROUP BY `gender`;
SELECT `gender`, AVG(`age`) FROM `tb_employee` GROUP BY `gender`;

-- 查询年龄小于 45 的员工，并且根据工作城市分组，获取员工数量不少于 3 的城市
SELECT `city`, COUNT(*) AS `city_count`
FROM `tb_employee`
WHERE `age` < 45
GROUP BY `city`
HAVING `city_count` >= 3;

8.4 默认启用 ONLY_FULL_GROUP_BY。选择列表中的非聚合列必须出现在 GROUP BY 中，或满足 MySQL 可识别的函数依赖等条件。WHERE 在分组前筛选行，HAVING 筛选分组后的结果。

1.3.5 排序查询

sql

-- SELECT 字段列表 FROM 表名 ORDER BY 字段1 [ASC | DESC], 字段2 排序方式2, ...;
SELECT * FROM `tb_employee` ORDER BY `age` ASC, `id` DESC;

1.3.6 分页查询

sql

-- SELECT 字段列表 FROM 表名 LIMIT 偏移量, 查询记录数;

-- 查询第 1 页员工数据，每页展示 20 条数据
SELECT * FROM `tb_employee` ORDER BY `id` LIMIT 0, 20;
-- 如果偏移量为 0 则可以省略，下面语句效果和上面一样
SELECT * FROM `tb_employee` ORDER BY `id` LIMIT 20;

-- 查询第 2 页员工数据，每页展示 20 条数据
SELECT * FROM `tb_employee` ORDER BY `id` LIMIT 20, 20;

分页需要明确的 ORDER BY，排序列有重复值时再用唯一列打破平局，否则不同执行计划可能返回不同的分页结果。

1.4 DCL

1.4.1 用户管理

MySQL 账号由 '用户名'@'主机' 共同确定，同名但主机不同的是不同账号。下面的本地练习账号需要由具备相应管理权限的账号创建，示例密码仅供练习。

查询用户

sql

SELECT User, Host, plugin FROM mysql.user;

创建用户

sql

-- CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
CREATE USER 'demo_user'@'localhost' IDENTIFIED BY 'DemoPass_846!';

修改用户密码

sql

-- ALTER USER '用户名'@'主机名' IDENTIFIED WITH caching_sha2_password BY '新密码';
ALTER USER 'demo_user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'DemoPass_846_new!';

8.4 默认使用 caching_sha2_password。mysql_native_password 从 8.0.34 起弃用，8.4 默认禁用，9.0 移除。修改密码时使用与当前认证插件对应的语法。参见认证插件。

删除用户，在权限练习结束后执行

sql

-- DROP USER '用户名'@'主机名';
DROP USER 'demo_user'@'localhost';

1.4.2 权限控制

常用权限	说明
`ALL`，`ALL PRIVILEGES`	指定范围内的权限集合，不含 `GRANT OPTION` 和 `PROXY`
`SELECT`	查询数据
`INSERT`	插入数据
`UPDATE`	修改数据
`DELETE`	删除数据
`ALTER`	修改表
`DROP`	删除数据库/表/视图
`CREATE`	创建数据库/表

查询权限

sql

-- SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'demo_user'@'localhost';

授予权限

sql

-- GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
GRANT SELECT, INSERT, UPDATE, DELETE ON demo_sql.* TO 'demo_user'@'localhost';

撤销权限

sql

-- REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
REVOKE SELECT, INSERT, UPDATE, DELETE ON demo_sql.* FROM 'demo_user'@'localhost';

GRANT 的目标账号必须先存在。库名.* 表示该库的对象权限，*.* 表示全局范围。通过 CREATE USER、ALTER USER、GRANT、REVOKE 修改账号或权限后，无需额外执行 FLUSH PRIVILEGES。参见账号和授权。

2. 函数

2.1 字符串函数

常用函数	说明
`CONCAT(S1, S2, ..., Sn)`	字符串拼接
`LOWER(str)`	将字符串全部转为小写
`UPPER(str)`	将字符串全部转为大写
`LPAD(str, n, pad)`	在左侧填充至 `n` 个字符，原字符串过长时截断
`RPAD(str, n, pad)`	在右侧填充至 `n` 个字符，原字符串过长时截断
`TRIM(str)`	去掉字符串头部和尾部的空格
`SUBSTRING(str, start, len)`	返回字符串 `str` 从 `start` 位置起的 `len` 个长度的子字符串

sql

SELECT CONCAT('Hello ', 'World!'); -- Hello World!
SELECT LOWER('Hello World!'); -- hello world!
SELECT UPPER('Hello World!'); -- HELLO WORLD!
SELECT LPAD('0111', 8, '0'); -- 00000111
SELECT RPAD('0111', 8, '0'); -- 01110000
SELECT TRIM('  Hello World! '); -- Hello World!
SELECT SUBSTRING('Hello!', 2, 3); -- ell

SUBSTRING 的正起点从 1 开始，负起点从末尾计算，起点为 0 时返回空串。CONCAT 任一参数为 NULL 时返回 NULL。参见字符串函数。

2.2 数值函数

常用函数	说明
`CEIL(x)`	向上取整
`FLOOR(x)`	向下取整
`MOD(x, y)`	返回 `x % y`
`RAND()`	返回满足 `0 <= x < 1` 的随机数
`ROUND(x, y)`	返回 `x` 的四舍五入值，保留 `y` 位小数

sql

SELECT CEIL(1.2); -- 2
SELECT FLOOR(1.8); -- 1
SELECT MOD(6, 4); -- 2
SELECT RAND(); -- 随机数
SELECT ROUND(2.345, 2); -- 2.35

ROUND 对精确值采用中间值远离零的舍入规则，对近似浮点值则依赖底层库。

2.3 日期函数

常用函数	说明
`CURDATE()`	当前会话时区下的日期
`CURTIME()`	当前会话时区下的时间
`NOW()`	当前语句开始时的日期和时间
`YEAR(date)`	提取年份
`MONTH(date)`	提取月份
`DAY(date)`	提取日号，等价于 `DAYOFMONTH`
`DATE_ADD(date, INTERVAL expr type)`	加上指定时间间隔
`DATEDIFF(date1, date2)`	前一个日期减后一个日期的天数，忽略时间部分

sql

SELECT CURDATE(); -- 随执行日期变化
SELECT CURTIME(); -- 随执行时间变化
SELECT NOW(); -- 当前语句开始时间
SELECT YEAR('2022-10-23'); -- 2022
SELECT MONTH('2022-10-23'); -- 10
SELECT DAY('2022-10-23'); -- 23
SELECT DATE_ADD('2022-10-23 13:24:28', INTERVAL 5 DAY); -- 2022-10-28 13:24:28
SELECT DATEDIFF('2022-12-11', '2022-10-23'); -- 49

2.4 流程函数

常用函数	说明
`IF(value, t, f)`	如果 `value` 为 `true`，则返回 `t`，否则返回 `f`
`IFNULL(value1, value2)`	如果 `value1` 不为 `NULL`，返回 `value1`，否则返回 `value2`
`CASE WHEN [val1] THEN [res1] ... ELSE [default] END`	如果 `val1` 为 `true`，返回 `res1`，...，否则返回 `default` 默认值
`CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END`	如果 `expr` 的值为 `val1`，返回 `res1`，...，否则返回 `default` 默认值

sql

SELECT IF(1 + 1 > 1, 'Y', 'N'); -- Y
SELECT IFNULL(NULL, 'D'); -- D
SELECT CASE WHEN 0 THEN 'N' WHEN 1 THEN 'Y' ELSE 'Default' END; -- Y
SELECT CASE 0 WHEN 0 THEN 'N' WHEN 1 THEN 'Y' ELSE 'Default' END; -- N

3. 约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据。

约束	关键字	描述
非空约束	`NOT NULL`	限制该字段数据不能为 `NULL`
唯一约束	`UNIQUE`	保证该字段数据唯一、不重复
主键约束	`PRIMARY KEY`	主键是一行数据的唯一标识，要求非空且唯一
默认约束	`DEFAULT`	保存数据时，如果未指定该字段的值，则采用默认值
检查约束	`CHECK`	保证字段值满足一个条件
外键约束	`FOREIGN KEY`	用来让两张表的数据之间建立连接，保证数据的一致性和完整性

sql

DROP DATABASE IF EXISTS demo_constraint;
CREATE DATABASE demo_constraint CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE demo_constraint;

CREATE TABLE tb_user (
    `id`     INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    `name`   VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
    `age`    INT COMMENT '年龄',
    `status` CHAR(1) DEFAULT '1' COMMENT '状态',
    `gender` CHAR(1) COMMENT '性别',
    CONSTRAINT ck_user_age CHECK (age BETWEEN 0 AND 150)
) ENGINE=InnoDB COMMENT '用户表';

INSERT INTO `tb_user` (`name`, `age`, `status`, `gender`)
VALUES ('zhang1', 18, '1', '男'), -- ID: 1
       ('zhang2', 22, '0', '男'); -- ID: 2

-- 下面三条分别演示预期错误，逐条执行。
INSERT INTO `tb_user` (`name`, `age`, `status`, `gender`)
VALUES ('zhang1', 20, '1', '男');
-- Error: Duplicate entry 'zhang1' for key 'tb_user.name'

INSERT INTO `tb_user` (`name`, `age`, `status`, `gender`)
VALUES (NULL, 18, '1', '男');
-- Error: Column 'name' cannot be null

INSERT INTO `tb_user` (`name`, `age`, `status`, `gender`)
VALUES ('zhang3', 160, '1', '男');
-- Error: Check constraint 'ck_user_age' is violated.

INSERT INTO `tb_user` (`name`, `age`, `gender`)
VALUES ('zhang3', 24, '女'); -- status: '1'

UNIQUE 允许多个 NULL，NOT NULL 用于限制空值。CHECK 从 8.0.16 起真正执行检查，结果为假时拒绝写入，为未知值时通过。需要同时限制年龄范围和空值时，可组合 CHECK (age >= 0) 与 NOT NULL。示例中故意违反约束的语句已标明，逐条执行可观察相应错误。参见 CHECK 约束。

外键约束要求子表的非空外键值在父表中存在。InnoDB 的关联列需要兼容的数据类型，整数的有无符号属性也要一致。8.4 默认限制引用非唯一键等非标准外键，示例统一引用父表主键。

先建立父表并为练习表添加外键列：

sql

USE demo_constraint;
CREATE TABLE tb_dept (id INT PRIMARY KEY, name VARCHAR(50)) ENGINE=InnoDB;
INSERT INTO tb_dept VALUES (1, '研发部');
ALTER TABLE tb_user ADD COLUMN dept_id INT;

-- CREATE TABLE 的示例使用 tb_member，ALTER TABLE 的示例使用 tb_user。

添加外键

方式一：

sql

-- CREATE TABLE 表名 (
--     字段名 数据类型,
--     ...,
--     [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
-- );

CREATE TABLE tb_member (
    id INT PRIMARY KEY,
    dept_id INT,
    CONSTRAINT fk_member_dept_id FOREIGN KEY (dept_id) REFERENCES tb_dept(id)
) ENGINE=InnoDB;

方式二：

sql

-- ALTER TABLE 表名
--     ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段名) REFERENCES 主表名(主表字段名);
ALTER TABLE `tb_user`
    ADD CONSTRAINT fk_user_dept_id FOREIGN KEY (dept_id) REFERENCES tb_dept (id);

外键的删除/更新行为：

行为	说明
`NO ACTION`	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与 `RESTRICT` 一致）
`RESTRICT`	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与 `NO ACTION` 一致）
`CASCADE`	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录。
`SET NULL`	父表删除记录或修改被引用键时，将相关子表外键设为 `NULL`，外键列必须允许空值
`SET DEFAULT`	InnoDB 不支持，指定该动作的外键定义会被拒绝

上述 NO ACTION 与 RESTRICT 等价指的是 InnoDB，它不支持将外键检查延迟到事务提交。

sql

-- ALTER TABLE 表名
--     ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段名) REFERENCES 主表名(主表字段名)
--         ON UPDATE CASCADE ON DELETE CASCADE;
-- 先移除前一例中创建的约束，再演示级联动作。
ALTER TABLE `tb_user` DROP FOREIGN KEY fk_user_dept_id;
ALTER TABLE `tb_user`
    ADD CONSTRAINT fk_user_dept_id FOREIGN KEY (dept_id) REFERENCES tb_dept (id)
        ON UPDATE CASCADE ON DELETE CASCADE;

4. 多表查询

本节及后续查询示例使用以下练习表。初始化脚本清空并重建 demo_query，需要重做练习时再执行。

sql

DROP DATABASE IF EXISTS demo_query;
CREATE DATABASE demo_query CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE demo_query;

CREATE TABLE tb_dept (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;
INSERT INTO tb_dept VALUES (1, '研发部'), (2, '销售部'), (3, '市场部'), (4, '财务部');

CREATE TABLE tb_employee (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    gender CHAR(1),
    age TINYINT UNSIGNED,
    salary DECIMAL(10, 2),
    job VARCHAR(50),
    manager_id INT,
    dept_id INT,
    entrydate DATE,
    FOREIGN KEY (dept_id) REFERENCES tb_dept(id)
) ENGINE=InnoDB;
INSERT INTO tb_employee VALUES
    (1, 'Howard', '男', 28, 8000, '工程师', 5, 1, '2020-09-01'),
    (2, '张三', '男', 32, 6000, '销售', 5, 2, '2018-01-01'),
    (3, '李四', '女', 26, 5500, '运营', 5, 3, '2021-03-01'),
    (4, '王五', '男', 55, 5000, '销售', 5, 2, '2009-01-01'),
    (5, '赵六', '女', 40, 12000, '经理', NULL, 1, '2010-01-01'),
    (6, '孙七', '女', 29, 8000, '工程师', 5, 1, '2022-06-01'),
    (7, '周八', '男', 30, 6500, '顾问', NULL, NULL, '2023-01-01');

CREATE TABLE tb_user (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age TINYINT UNSIGNED,
    phone VARCHAR(20),
    profession VARCHAR(50),
    status CHAR(1),
    email VARCHAR(100)
) ENGINE=InnoDB;
INSERT INTO tb_user VALUES
    (1, 'zhang', 22, '13800000001', '电子信息', '0', 'zhang@example.com'),
    (2, 'li', 23, '13800000002', '电子信息', '1', 'li@example.com'),
    (3, 'wang', 22, '13800000003', '网络安全', '0', 'wang@example.com'),
    (4, 'zhao', 30, '13800000004', '网络安全', '1', 'zhao@example.com'),
    (5, 'sun', 25, '13800000005', '电子信息', '0', NULL);

CREATE TABLE tb_sku (id BIGINT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10, 2));
INSERT INTO tb_sku VALUES (1, '键盘', 199), (2, '鼠标', 99), (3, '显示器', 1299);

4.1 内连接

隐式内连接

sql

-- SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
SELECT `tb_employee`.`name`, `tb_dept`.`name`
FROM `tb_employee`, `tb_dept`
WHERE `tb_employee`.`dept_id` = `tb_dept`.`id`;

显式内连接

sql

-- SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;
SELECT `tb_employee`.`name`, `tb_dept`.`name`
FROM `tb_employee`
         INNER JOIN `tb_dept` ON `tb_employee`.`dept_id` = `tb_dept`.`id`;

4.2 外连接

左外连接

sql

-- SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;
SELECT `tb_employee`.`name`, `tb_dept`.`name`
FROM `tb_employee`
LEFT OUTER JOIN `tb_dept` ON `tb_employee`.`dept_id` = `tb_dept`.`id`;

右外连接

sql

-- SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;
SELECT `tb_employee`.`name`, `tb_dept`.`name`
FROM `tb_employee`
RIGHT OUTER JOIN `tb_dept` ON `tb_employee`.`dept_id` = `tb_dept`.`id`;

4.3 自连接

自连接查询可以是内连接，也可以是外连接。

sql

-- SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;
SELECT `a`.`name` AS `员工`, `b`.`name` AS `领导`
FROM `tb_employee` `a`
LEFT OUTER JOIN `tb_employee` `b` ON `a`.`manager_id` = `b`.`id`;

4.4 联合查询

关键字：

UNION ALL：直接合并
UNION：去重合并

sql

-- SELECT 字段列表 FROM 表A ...
-- UNION [ALL]
-- SELECT 字段列表 FROM 表B ...;
SELECT * FROM `tb_employee` WHERE `salary` < 5500
UNION
SELECT * FROM `tb_employee` WHERE `age` > 50;

各查询的列数必须相同，对应列的数据类型要可兼容。需要指定联合查询的输出顺序时，在整个查询末尾添加 ORDER BY。

4.5 子查询

SQL 语句中嵌套 SELECT 语句，成为嵌套查询，又称子查询。

4.5.1 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），这种子查询称为标量子查询。

标量子查询没有结果行时取值为 NULL，返回超过一行时报错。

sql

-- 查询销售部的所有员工信息
SELECT * FROM `tb_employee`
WHERE `dept_id` = (SELECT `id` FROM `tb_dept` WHERE `name` = '销售部');

4.5.2 列子查询

子查询返回的结果是一列，这种子查询称为列子查询。

常配合 IN、ANY、ALL 使用，结果可以有多行。

sql

-- 查询销售部和市场部的所有员工信息
SELECT * FROM `tb_employee`
WHERE `dept_id` IN (SELECT `id` FROM `tb_dept`
                    WHERE `name` = '销售部' OR `name` = '市场部');

4.5.3 行子查询

行子查询用于将一行中的多个列值与行构造器比较，下面的等值比较要求子查询最多返回一行。

sql

-- 查询与 Howard 的薪资和直属领导相同的员工信息
SELECT * FROM `tb_employee`
WHERE (`salary`, `manager_id`) = (SELECT `salary`, `manager_id` FROM `tb_employee`
                                  WHERE `name` = 'Howard');

4.5.4 表子查询

子查询也可返回多行、多列，用于多列 IN 比较，或放在 FROM 中形成派生表。派生表需要指定别名。

sql

-- 查询与张三、李四的职位和薪资相同的员工信息
SELECT * FROM tb_employee
WHERE (`job`, `salary`) IN (SELECT `job`, `salary` FROM `tb_employee`
                            WHERE `name` = '张三' OR `name` = '李四');

-- 查询入职日期是 2010-01-01 之后的员工信息，及其部门信息
SELECT `e`.*, `d`.*
FROM (SELECT * FROM tb_employee WHERE `entrydate` > '2010-01-01') `e`
         LEFT JOIN `tb_dept` `d` ON `e`.`dept_id` = `d`.`id`;

5. 事务

事务将相关操作组织成一个提交或回滚的单元。例如转账时，扣款和入账应在同一事务中完成。本节讨论 InnoDB，非事务存储引擎上的写入不能靠 ROLLBACK 撤销。

默认 autocommit=1 且未显式开启事务时，每条语句独立提交。显式开启事务后，直到 COMMIT 或 ROLLBACK 才结束这组操作：

sql

CREATE TABLE account (id INT PRIMARY KEY, balance DECIMAL(12, 2) NOT NULL);
INSERT INTO account VALUES (1, 1000), (2, 1000);

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

应用还需检查执行结果并决定提交或回滚。语句报错不一定自动回滚整个事务，例如默认的锁等待超时只回滚当前语句，死锁则回滚被选中的整个事务。参见 InnoDB 错误处理。

查看/设置事务的提交方式

sql

SELECT @@autocommit;
SET SESSION autocommit = 0;
-- 本组练习结束后提交或回滚，再恢复默认值：
ROLLBACK;
SET SESSION autocommit = 1;

提交事务

sql

COMMIT;

回滚事务

sql

ROLLBACK;

5.1 事务操作

开启事务

sql

START TRANSACTION;

或：

sql

BEGIN;

提交事务

sql

COMMIT;

回滚事务

sql

ROLLBACK;

5.2 四大特性 ACID

原子性（Atomicity）：事务中的修改整体提交或回滚，InnoDB 用 undo log 支持回滚。
一致性（Consistency）：事务前后应满足定义的数据约束和业务不变量，由数据库约束和应用中的业务校验共同维护。
隔离性（Isolation）：隔离级别规定并发事务之间哪些变化可见，InnoDB 通过 MVCC 和锁实现相应行为。
持久性（Durability）：已提交的修改可在崩溃后恢复。保证程度依赖 redo、binlog 的刷盘配置及存储设备是否正确完成持久化。Doublewrite 用于防止数据页部分写入，不替代 redo log。

5.3 并发事务问题

问题	描述
脏读	一个事务读取了另一个事务尚未提交的修改数据
不可重复读	同一事务重复读取同一行时，因其他事务提交修改而读到不同值
幻读	重复执行同一条件查询，因其他事务的变更而出现或消失满足条件的行

5.4 事务隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted	可能	可能	可能
Read committed	不发生	可能	可能
Repeatable read	不发生	不发生	标准允许
Serializable	不发生	不发生	不发生

上表用于说明隔离级别允许的典型现象。InnoDB 默认是 REPEATABLE READ，一致性读复用快照，锁定读和写操作通常用 next-key lock 阻止范围内插入。快照读与锁定读看到的数据可能不同，同一事务也能看到自己的写入。

查看事务隔离级别

sql

SELECT @@TRANSACTION_ISOLATION; -- REPEATABLE-READ

设置事务隔离级别

sql

-- SET [SESSION | GLOBAL]
-- TRANSACTION ISOLATION LEVEL
-- {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SESSION 设置当前会话后续事务的默认隔离级别，不改变正在执行的事务。GLOBAL 设置后续新连接的默认值，不改变已有连接。省略作用域的 SET TRANSACTION 只针对下一次事务。

6. 存储引擎

查询当前数据库支持的存储引擎

sql

SHOW ENGINES;

以下是常见输出，支持状态以当前实例的编译和安装配置为准。

Engine	Support	Comment	Transactions	XA	Savepoints
ARCHIVE	YES	Archive storage engine	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine	NULL	NULL	NULL
MyISAM	YES	MyISAM storage engine	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO

在创建表时指定存储引擎

sql

-- CREATE TABLE 表名 (
--     字段1, 字段1类型 [COMMENT 字段1注释],
--     ......
--     字段n, 字段n类型 [COMMENT 字段n注释]
-- ) ENGINE = 存储引擎 [COMMENT 表注释];

-- 创建表 my_myisam，并指定 MyISAM 存储引擎
CREATE TABLE `my_myisam` (
    `id`   int         NOT NULL,
    `name` varchar(10) NOT NULL
) ENGINE = MyISAM;

-- 创建表 my_memory，并指定 MEMORY 存储引擎
CREATE TABLE `my_memory` (
    `id`   int         NOT NULL,
    `name` varchar(10) NOT NULL
) ENGINE = MEMORY;

6.1 InnoDB

InnoDB 是支持事务、行级锁和崩溃恢复的通用存储引擎，从 MySQL 5.5 起成为默认存储引擎。

特点：

DML 操作遵循 ACID 模型，支持事务
行级锁，提高并发访问性能
支持外键 FOREIGN KEY 约束，保证数据的完整性和一致性

开启 innodb_file_per_table 后，新建的普通非分区表默认使用独立的 tb_name.ibd，存储数据、索引和序列化字典信息（SDI）。表也可以放在通用表空间等共享表空间中，分区表则有多个分区文件。

创建新表时是否默认使用独立表空间，可通过以下命令确认。该设置的变化不会自动搬迁已有表：

sql

SHOW VARIABLES LIKE 'innodb_file_per_table';

数据目录由 datadir 决定，可以执行 SELECT @@datadir 查询。部分 Linux 发行版的软件包使用 /var/lib/mysql。

MySQL 8.0 起不再使用 .frm 文件保存表定义，事务数据字典存储于 mysql.ibd。ibd2sdi 表名.ibd 用于提取表的 SDI 元数据。

逻辑存储结构：

Tablespace：表空间，一个表空间包含多个段
Segment：段，管理分配给该段的零散页和区
Extent：区，默认 1 MiB，包含 64 个 16 KiB 页
Page：页，默认 16 KiB，是缓存和磁盘读写的基本单位
Row：行，较长的变长字段可能使用页外存储

页大小在实例初始化时确定。4、8、16 KiB 页对应 1 MiB 的区，32、64 KiB 页对应 2、4 MiB 的区。

6.2 MyISAM

MyISAM 是 MySQL 早期的默认存储引擎。

特点：

不支持事务，不支持外键
支持表锁，不支持行锁
适用于不需要事务和外键的特定场景，性能需根据工作负载测量

文件：

tb_name_<id>.sdi：存储表结构的 SDI，文件名含内部表 ID
tb_name.MYD：存储数据
tb_name.MYI：存储索引

6.3 MEMORY

MEMORY 表将数据放在内存中，服务器重启后表定义仍在，数据会丢失。它适合可重新生成的数据。8.4 的内部临时表通常使用 TempTable 或 InnoDB。

特点：

内存存放
Hash 索引（默认）
可以显式建立 BTREE 索引，支持范围查找

文件：tb_name_<id>.sdi 存储表结构的 SDI，不保存内存中的业务数据。

6.4 对比

特点	InnoDB	MyISAM	MEMORY
事务	支持	-	-
外键	支持	-	-
主要数据锁	行级锁及表级意向锁	表锁	表锁
BTREE 索引	支持，内部为 B+ 树	支持	支持
Hash 索引	-	-	支持

这里的 Hash 索引指用户显式创建的索引，InnoDB 的自适应哈希索引是另一种内部机制。

7. 索引

7.1 结构

InnoDB 的普通索引采用 B+ 树。MySQL 还支持 MEMORY 的 Hash 索引、空间索引的 R-tree、全文索引等，索引结构取决于引擎与索引类型。

特性	B 树	B+ 树
存储内容	非叶子节点和叶子节点均存储键+值	仅叶子节点存储键+值，非叶子节点仅存键（索引）
叶子节点	一般不依靠叶子链表组织数据	叶子节点按键顺序相连，适合顺序访问
查找路径	可能在非叶子节点找到数据	查找数据需要到达叶子节点
范围查询	需沿树结构做有序遍历	定位首个叶子记录后，可沿叶子链继续遍历
节点存储密度	低（键+值占用空间大，单节点键数少）	高（非叶子节点仅存键，单节点键数更多）
树高	取决于节点大小、数据大小与填充率	非叶子节点不存行数据，通常能容纳更多分隔键
数据冗余	无冗余（键仅出现一次）	有冗余（非叶子节点的键是叶子节点的副本）

7.2 分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建, 只能有一个	`PRIMARY`
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	`UNIQUE`
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	`FULLTEXT`

在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚簇索引（Clustered Index）	将数据存储和索引放到了一块，索引结构的叶子节点保存了行数据	必须要，而且只有一个
二级索引（Secondary Index）	叶子记录保存索引列及聚簇键，需要其他列时再查聚簇索引	可以存在多个

聚簇索引选取规则：

如果存在主键，主键索引就是聚簇索引
如果不存在主键，选择第一个所有列均为 NOT NULL 的唯一索引
如果没有符合条件的索引，生成 6 字节的 DB_ROW_ID，建立隐藏聚簇索引 GEN_CLUST_INDEX

7.3 语法

本节及后续索引示例使用 demo_query.tb_user，同名索引只需创建一次。小型练习表可能更适合全表扫描。

创建索引

sql

-- CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名 (字段名, ...);
CREATE INDEX `idx_user_name` ON `tb_user` (`name`);
CREATE INDEX `idx_user_pro` ON `tb_user` (`profession`);
CREATE INDEX `idx_user_pro_age_sta` ON `tb_user` (`profession`, `age`, `status`);
CREATE UNIQUE INDEX `idx_user_phone` ON `tb_user` (`phone`);

查看索引

sql

-- SHOW INDEX FROM 表名;
SHOW INDEX FROM `tb_user`;

删除索引

sql

-- DROP INDEX 索引名 ON 表名;
DROP INDEX `idx_user_name` ON `tb_user`;

7.4 SQL 性能分析

7.4.1 SQL 执行频率

SHOW SESSION STATUS 查看当前会话状态，SHOW GLOBAL STATUS 查看实例累计状态。Com_* 统计相应作用域内的命令执行次数，比较一段时间内计数的差值，可了解执行频率。

sql

SHOW GLOBAL STATUS WHERE Variable_name IN
    ('Com_select', 'Com_insert', 'Com_update', 'Com_delete');

7.4.2 慢查询日志

慢查询日志通常记录执行时间超过 long_query_time 且检查行数至少为 min_examined_row_limit 的语句。long_query_time 默认 10 秒，管理语句等是否记录还受其他选项控制。

MySQL 的慢查询日志默认没有开启，需要在 MySQL 的配置文件（Ubuntu 默认在 /etc/mysql/mysql.conf.d/mysqld.cnf）配置如下信息：

cnf

[mysqld]
slow_query_log      = ON
slow_query_log_file = mysql-slow.log
long_query_time     = 2

文件路径需适合当前安装方式，并允许服务进程写入。修改全局 long_query_time 后，已有连接的会话值不会自动变化。参见慢查询日志。

7.4.3 语句耗时

8.4 优先通过 Performance Schema 和 sys 视图分析语句耗时。下面查看实例级的规范化语句统计，按总耗时排序：

sql

-- x$ 视图保留数值耗时，避免对带单位的字符串进行排序。
SELECT db, query, exec_count,
       ROUND(total_latency / 1000000000000, 6) AS total_seconds,
       ROUND(avg_latency / 1000000000000, 6) AS avg_seconds,
       rows_examined, rows_sent
FROM sys.x$statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

旧的 SHOW PROFILE、SHOW PROFILES 在 8.4 仍可用，但已弃用。阅读旧代码时，可先检查支持情况：

sql

SELECT @@have_profiling; -- YES

profiling 只支持会话作用域，默认关闭：

sql

SELECT @@profiling; -- 0
SET profiling = 1;
SELECT @@profiling; -- 1

执行一系列的业务 SQL 操作，然后通过如下指令查看执行耗时：

sql

SELECT * FROM tb_user;
SELECT * FROM tb_user WHERE id = 1;
SELECT * FROM tb_user WHERE name = 'zhang';

SHOW PROFILES;
-- 根据上一条命令实际输出的 Query_ID 选择查询，不固定写为 6。
-- SHOW PROFILE FOR QUERY query_id;
-- SHOW PROFILE CPU FOR QUERY query_id;

7.4.4 `EXPLAIN` 执行计划

EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

EXPLAIN FORMAT=TRADITIONAL 返回下述表格格式，FORMAT=TREE 或 FORMAT=JSON 提供不同的计划细节。EXPLAIN ANALYZE 从 8.0.18 起支持，会实际执行查询并报告测量结果。

sql

EXPLAIN FORMAT=TRADITIONAL SELECT * FROM tb_user WHERE id = 1;
EXPLAIN FORMAT=TREE SELECT * FROM tb_user WHERE id = 1;
EXPLAIN ANALYZE SELECT * FROM tb_user WHERE profession = '电子信息';

EXPLAIN 执行计划各字段含义：

id：查询块的标识
select_type：表示 SELECT 的类型，常见取值：
- SIMPLE：不含 UNION 或子查询的简单查询，可以包含表连接
- PRIMARY：主查询，即外层的查询
- UNION：UNION 中的第二个或者后面的查询语句
- SUBQUERY：SELECT / WHERE 之后包含了子查询
type：访问或连接类型，常见值如下。评估计划时应结合数据量、访问路径和总成本：
1. NULL：不涉及表（如 SELECT 1+1）
2. system：表中只有一行数据（const 的特例，如系统表）
3. const：通过主键/唯一索引匹配到单行
4. eq_ref：多表连接中，被连接表通过唯一索引匹配
5. ref：通过普通索引匹配多行
6. range：索引范围扫描（如 BETWEEN、IN、> 等）
7. index：完整索引扫描，是否还需要读取完整行取决于索引是否覆盖
8. ALL：全表扫描，小表或返回大部分行时可能是合理选择
possible_keys：显示可能应用在这张表上的索引，一个或多个
key：实际使用的索引，如果为 NULL，则没有使用索引
key_len：计划使用的索引键长度，受类型、字符集、可空属性等影响，可辅助判断使用了哪些键部分
rows：访问该表时预计扫描的行数，InnoDB 根据统计信息估计
filtered：估计的条件过滤后保留比例，rows × filtered / 100 可估算传给后续连接的行数
Extra：补充信息，例如覆盖索引、索引条件下推、临时表或额外排序

7.5 使用规则

7.5.1 最左前缀原则

联合索引 (profession, age, status) 可用连续的最左列构造常规查找范围。跳过 age 后，仍可按 profession 定位，再过滤 status。后续列还可能用于索引条件下推或覆盖查询。8.0.13 起提供的 Skip Scan 在满足条件时也可能使用缺少首列条件的索引。

sql

-- 使用 7.3 已创建的 (profession, age, status) 联合索引。

/* 验证最左前缀原则 */

-- 可用三列等值条件构造查找范围
EXPLAIN FORMAT=JSON SELECT * FROM `tb_user`
WHERE `profession` = '电子信息' AND `age` = 22 AND `status` = '0';

-- 可用前两列构造查找范围
EXPLAIN FORMAT=JSON SELECT * FROM `tb_user`
WHERE `profession` = '电子信息' AND `age` = 22;

-- 可用首列构造查找范围
EXPLAIN FORMAT=JSON SELECT * FROM `tb_user`
WHERE `profession` = '电子信息';

EXPLAIN FORMAT=JSON SELECT * FROM `tb_user` WHERE `age` = 22 AND `status` = '0'; -- 缺少首列，观察优化器选择
EXPLAIN FORMAT=JSON SELECT * FROM `tb_user` WHERE `status` = '0'; -- 缺少首列，观察优化器选择

-- 跳过 age，profession 仍可用于定位，status 可能参与过滤
EXPLAIN FORMAT=JSON SELECT * FROM `tb_user` WHERE `profession` = '电子信息' AND `status` = '0';

7.5.2 范围查询

构造多列 BTREE 范围时，非等值范围条件通常会限制后续列继续收窄扫描区间。后续列仍可能参与边界处理、索引条件下推或覆盖查询。比较 > 与 >= 时，需检查实际范围与过滤步骤。

sql

-- 使用 7.3 已创建的 (profession, age, status) 联合索引。

/* 验证范围查询的情况 */

-- 观察范围扫描以及 status 条件的过滤位置
EXPLAIN FORMAT=JSON SELECT * FROM `tb_user`
WHERE `profession` = '电子信息' AND `age` > 22 AND `status` = '0';

-- >= 不保证后续列都能收窄整个范围，比较实际计划
EXPLAIN FORMAT=JSON SELECT * FROM `tb_user`
WHERE `profession` = '电子信息' AND `age` >= 22 AND `status` = '0';

7.5.3 难以使用索引定位的情况

以下情况可能妨碍普通索引构造高效的查找范围，是否改用全表扫描还由优化器成本判断决定：

索引字段参与运算
如 WHERE id + 1 = 10 或 WHERE SUBSTR(name, 1, 3) = 'abc'。8.0.13 起支持函数索引，表达式与相应索引匹配时可以使用。
字符串不加引号
如 WHERE name = 123 会在比较字符串列时引入数值转换。按字段类型写为 WHERE name = '123'，有利于利用字符串索引定位。
LIKE 以通配符开头
如 WHERE name LIKE '%abc' 通常不能利用普通 BTREE 构造前缀范围，但仍可能扫描覆盖索引。LIKE 'abc%' 则可形成前缀范围。
使用 OR 连接非索引字段
如 WHERE idx_col = 1 OR no_idx_col = 2，未索引的分支通常妨碍 Index Merge 的联合访问。两个分支都有合适索引时可能使用 Index Merge，是否采用仍需看成本。
数据分布影响
如果 MySQL 评估使用索引比全表更慢，则不使用索引

7.5.4 SQL 提示

索引提示可以限制优化器的候选索引。结合统计信息和执行计划选择提示，并比较实际耗时。

USE INDEX：

sql

EXPLAIN SELECT * FROM `tb_user` USE INDEX (`idx_user_pro`)
WHERE `profession` = '电子信息';

IGNORE INDEX：

sql

EXPLAIN SELECT * FROM `tb_user` IGNORE INDEX (`idx_user_pro_age_sta`)
WHERE `profession` = '电子信息';

FORCE INDEX：

sql

EXPLAIN SELECT * FROM `tb_user` FORCE INDEX (`idx_user_pro`)
WHERE `profession` = '电子信息';

这些传统语法在 8.4 仍支持。8.4 还可使用 INDEX、JOIN_INDEX、GROUP_INDEX、ORDER_INDEX 等优化器提示，具体适用范围见索引提示。

7.5.5 覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列在该索引中已经全部能够找到），减少 SELECT *。

InnoDB 二级索引隐含聚簇键，覆盖查询也可利用这些键值。Extra 中的 Using index 表示覆盖访问，排序方式可结合 Using filesort 等信息判断。

7.5.6 前缀索引

字符串字段（VARCHAR、TEXT 等）的完整索引可能占用较多空间。前缀索引只保存字符串的前一部分，可减小索引大小。

sql

-- CREATE INDEX 索引名 ON 表名 (字段名(前缀长度));
CREATE INDEX `idx_user_email_5` ON `tb_user` (`email`(5));

去重比例反映前缀的区分能力。可比较不同前缀长度的去重比例，再结合查询模式、索引大小和写入成本选择长度。字符列的前缀长度按字符计算，二进制列按字节计算。

sql

SELECT COUNT(DISTINCT `email`) / NULLIF(COUNT(*), 0) FROM `tb_user`;
SELECT COUNT(DISTINCT SUBSTRING(`email`, 1, 5)) / NULLIF(COUNT(*), 0) FROM `tb_user`;

前缀索引不能覆盖查询所需的完整字段值。若设为唯一前缀索引，约束的也是前缀，两个完整字符串不同但前缀相同的值仍会冲突。

8. SQL 优化

8.1 `INSERT` 优化

8.1.1 小批量插入数据

下面比较同一张空表上的三种插入方式，每种方式分别执行。先建立练习表，重做时用 TRUNCATE TABLE tb_insert 清空：

sql

USE demo_query;
CREATE TABLE tb_insert (id INT PRIMARY KEY, name VARCHAR(50)) ENGINE=InnoDB;

sql

INSERT INTO `tb_insert` VALUES (1, 'zhang1');
INSERT INTO `tb_insert` VALUES (2, 'zhang2');
INSERT INTO `tb_insert` VALUES (3, 'zhang3');

优化 1：改为批量插入

sql

INSERT INTO `tb_insert` VALUES (1, 'zhang1'), (2, 'zhang2'), (3, 'zhang3');

优化 2：手动提交事务

自动提交且未显式开启事务时，多条插入语句分别提交。适度批量插入或将一批写入放入同一事务，可以减少通信和提交开销，但过大的批次也会增加锁持有时间、日志量与失败重试成本。

sql

START TRANSACTION;
INSERT INTO `tb_insert` VALUES (1, 'zhang1'), (2, 'zhang2'), (3, 'zhang3');
INSERT INTO `tb_insert` VALUES (4, 'zhang4'), (5, 'zhang5'), (6, 'zhang6');
INSERT INTO `tb_insert` VALUES (7, 'zhang7'), (8, 'zhang8'), (9, 'zhang9');
COMMIT;

优化 3：主键顺序插入

txt

主键乱序插入：8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入：1 2 3 4 5 7 8 9 15 21 88 89

8.1.2 大批量插入数据

导入大量文本数据时，可比较多行 INSERT 与 LOAD DATA 的实际表现。以下示例将客户端文件中的两列导入 tb_insert，不与前面的插入示例同时执行。先准备无表头的 /tmp/tb_insert.csv，每行格式为 id,name。

mysql --local-infile=1 -u root -p demo_query

sql

-- 服务器默认关闭 LOCAL，需要管理权限启用。
SET @old_local_infile = @@GLOBAL.local_infile;
SET GLOBAL local_infile = ON;

LOAD DATA LOCAL INFILE '/tmp/tb_insert.csv' INTO TABLE tb_insert
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(id, name);

-- 在同一会话中恢复练习前的设置。
SET GLOBAL local_infile = @old_local_infile;

LOCAL 从客户端读取文件，客户端和服务器都需允许本地导入。没有 LOCAL 时由服务器读取文件，需要 FILE 权限，并受 secure_file_priv 限制。参见 LOAD DATA。

8.2 主键优化

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（Index Organized Table，IOT）。

8.2.1 页分裂

插入目标页空间不足时，可能分配新页并重新分配记录。聚簇索引按聚簇键排序，随机键插入通常更容易分散到不同页。每页能容纳多少记录取决于记录大小、页头开销和填充情况。

8.2.2 页合并

InnoDB 通常先将记录标记为删除，在不再需要它支持回滚或一致性读后，由 purge 清理并回收记录占用的页内空间。

删除或缩短记录使页的填充比例低于 MERGE_THRESHOLD 时，InnoDB 尝试与相邻页合并。默认阈值为 50%，指剩余填充比例。参见页合并阈值。

MERGE_THRESHOLD

合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

8.2.3 主键设计原则

满足业务需求的情况下，尽量降低主键长度
插入数据时，尽量顺序插入，选择 AUTO_INCREMENT 自增主键
比较候选键的宽度、顺序性和业务稳定性，随机 UUID 与较长自然键可能增加索引空间和随机写入
业务操作时，避免对主键的修改

8.3 `ORDER BY` 优化

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
索引顺序满足 ORDER BY 时，可以省去额外排序

filesort 可以全部在内存中完成，也可能使用磁盘临时文件。以下注释说明可能的计划，需要结合当前数据和统计信息确认。

sql

-- 以下 Extra 为可能的表现，实际结果取决于数据与成本选择。
-- 没创建索引时，根据 age、phone 进行排序
EXPLAIN SELECT `id`, `age`, `phone` FROM `tb_user`
ORDER BY `age` ASC, `phone` ASC; -- Using filesort

-- 创建索引
CREATE INDEX `idx_user_age_phone_aa` ON `tb_user` (`age` ASC, `phone` ASC);

-- 创建索引后，根据 age、phone 进行升序排序
EXPLAIN SELECT `id`, `age`, `phone` FROM `tb_user`
ORDER BY `age` ASC, `phone` ASC; -- Using index

-- 创建索引后，根据 age、phone 进行降序排序
EXPLAIN SELECT `id`, `age`, `phone` FROM `tb_user`
ORDER BY `age` DESC, `phone` DESC; -- Backward index scan; Using index

-- 创建索引后，根据 phone、age 进行升序排序
EXPLAIN SELECT `id`, `age`, `phone` FROM `tb_user`
ORDER BY `phone` ASC, `age` ASC; -- Using index; Using filesort

-- 创建索引后，根据 age 进行升序排序，根据 phone 进行降序排序
EXPLAIN SELECT `id`, `age`, `phone` FROM `tb_user`
ORDER BY `age` ASC, `phone` DESC; -- Using index; Using filesort

-- 创建索引，age 从小到大排，phone 从大到小排
CREATE INDEX `idx_user_age_phone_ad` ON `tb_user` (`age` ASC, `phone` DESC);

-- 创建索引 idx_user_age_phone_ad 后，根据 age 进行升序排，根据 phone 进行降序排
EXPLAIN SELECT `id`, `age`, `phone` FROM `tb_user`
ORDER BY `age` ASC, `phone` DESC; -- Using index

根据排序字段建立合适的索引，多字段排序时，也遵守最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC / DESC）
多字段混合升降序可使用方向匹配的索引，InnoDB 从 8.0 起支持实际的降序索引
sort_buffer_size 默认 256 KiB，按会话分配。设置时结合排序规模和并发量评估内存需求
sql
```
SELECT @@sort_buffer_size; -- 262144
```

8.4 `GROUP BY` 优化

sql

-- 以下 Extra 为可能的表现，实际结果取决于数据与成本选择。
-- 未建索引的表可能需要临时表，比较时先移除相应索引
EXPLAIN SELECT `profession`, COUNT(*) FROM `tb_user`
GROUP BY `profession`; -- 可能使用临时表

-- 使用 7.3 已创建的联合索引，需要比较前后计划时单独移除、重建。

-- 创建索引后，根据 profession 字段分组
EXPLAIN SELECT `profession`, COUNT(*) FROM `tb_user`
GROUP BY `profession`;  -- Using index

-- 创建索引后，根据 age 字段分组。不满足最左前缀原则
EXPLAIN SELECT `age`, COUNT(*) FROM `tb_user`
GROUP BY `age`; -- Using index; Using temporary

-- 创建索引后，根据 profession、age 两个字段分组
EXPLAIN SELECT `profession`, `age`, COUNT(*) FROM `tb_user`
GROUP BY `profession`, `age`; -- Using index

-- 创建索引后，先查询 profession 再根据 age 字段分组
EXPLAIN SELECT `age`, COUNT(*) FROM `tb_user`
WHERE `profession` = '网络安全' GROUP BY `age`; -- Using index

在分组操作时，可以通过索引来提升效率
分组操作时，索引的使用也是满足最左前缀原则的

合适的索引可能减少临时表或启用松散索引扫描，但要满足具体查询条件。8.0 起 GROUP BY 不再隐式排序，需要有序输出时显式加 ORDER BY。

8.5 `LIMIT` 优化

对于按索引扫描的 ORDER BY id LIMIT 2000000, 10，仍需跳过前 2,000,000 个匹配项，偏移量越大，扫描成本通常越高。下列大偏移量仅用于说明问题，练习表数据不足时会返回空结果。

优化思路 1：先从覆盖索引取得这一页的主键，再读取完整行，减少回表次数，但仍需扫描并跳过偏移部分

sql

EXPLAIN SELECT *
FROM `tb_sku` `t`,
     (SELECT `id` FROM `tb_sku` ORDER BY `id` LIMIT 2000000, 10) `a`
WHERE `t`.`id` = `a`.`id`
ORDER BY `t`.`id`;

优化思路 2：记录上一页的最后一个排序键，用范围条件继续读取，避免深偏移。它适合沿排序键顺序翻页，任意页码跳转需另行设计

sql

-- 记录上一页最后一条的 `id`，直接从该 `id` 往后查
SET @last_id = 2000000; -- 上一页实际返回的最后一个 id，示例值
EXPLAIN SELECT * FROM `tb_sku`
WHERE `id` > @last_id ORDER BY `id` LIMIT 10;

8.6 `COUNT` 优化

MyISAM 保存总行数，单表、无 WHERE 等符合条件的 COUNT(*) 可以直接取得该值
InnoDB 需要统计当前事务可见的记录，通常扫描最小的二级索引，没有二级索引时扫描聚簇索引

频繁获取大表总数时，可考虑统计缓存或维护计数表。精确计数需要和业务修改保持事务一致性，缓存则要明确允许的延迟。

8.6.1 `COUNT` 的几种用法

COUNT() 是一个聚合函数，对于返回的结果集，一行行地判断，如果参数不为 NULL，累计值加一，最后返回累计值。

用法：

COUNT(主键)：主键不为空，语义上等于统计结果行数
COUNT(字段)：统计该字段不为 NULL 的行数
COUNT(1)：常量 1 不为空，统计结果行数
COUNT(*)：直接统计结果行数，通常最清楚地表达意图

InnoDB 对 COUNT(*) 与 COUNT(1) 的处理相同，其他形式的性能受可用索引、可空性及查询条件影响。

8.7 `UPDATE` 优化

InnoDB 的记录锁加在索引记录上。UPDATE 缺少可缩小扫描范围的索引时，可能锁住大量记录或区间，增加并发等待。实际锁范围还取决于隔离级别，RC 会释放不匹配行上的记录锁。

9. 视图 / 存储过程 / 触发器

9.1 视图

视图（View）是由查询定义的虚拟表，结果来自查询引用的基表或其他视图。

视图保存查询定义，查询结果在执行时生成。执行时可合并到外层查询，也可能使用临时表，具体取决于视图定义和执行计划。含聚合、分组等操作的视图通常不可更新。

创建

sql

-- CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS
-- SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION];
CREATE OR REPLACE VIEW `user_v_1` AS
SELECT `id`, `name` FROM `tb_user` WHERE `id` <= 10;

查询

sql

-- 查看创建视图语句：SHOW CREATE VIEW 视图名称;
SHOW CREATE VIEW `user_v_1`;

-- 查看视图数据：SELECT * FROM 视图名称 ...;
SELECT * FROM `user_v_1`;
SELECT * FROM `user_v_1` WHERE `id` < 3;

修改

sql

-- 方式一：通过 OR REPLACE 替换已有视图
--   CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS
--   SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION];
CREATE OR REPLACE VIEW `user_v_1` AS
SELECT `id`, `name` FROM `tb_user` WHERE `id` <= 3;

-- 方式二：
--   ALTER VIEW 视图名称[(列名列表)] AS
--   SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION];
ALTER VIEW `user_v_1` AS
SELECT `id`, `name` FROM `tb_user` WHERE `id` <= 5;

删除

sql

-- DROP VIEW IF EXISTS 视图名称;
DROP VIEW IF EXISTS `user_v_1`;

9.2 存储过程

存储过程是保存在服务器端的程序，通过 CALL 调用。它可以集中处理相关 SQL，减少应用与数据库之间的通信次数。

特点：

支持 IN、OUT、INOUT 参数，可以返回结果集
服务器按会话缓存解析后的程序，相关元数据变化时可能重新解析
是否放入数据库应根据事务边界、部署与调试方式、迁移需求决定

9.2.1 基本语法

创建

sql

-- CREATE PROCEDURE 存储过程([参数列表])
-- BEGIN
--     SQL 语句
-- END;
DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
    SELECT COUNT(*) FROM `tb_user`;
END$$
DELIMITER ;

DELIMITER 是 mysql 客户端命令，用于设置语句分隔符，让客户端将完整过程定义发送给服务器。通过数据库驱动创建存储过程时，直接发送完整的 CREATE PROCEDURE 语句。下面的完整过程示例均在定义结束后恢复 ;。

调用

sql

-- CALL 存储过程(参数);
CALL p1();

查看

sql

-- 查询指定数据库的存储过程及状态信息：
--   SELECT * FROM information_schema.ROUTINES
--   WHERE ROUTINE_SCHEMA = '数据库名';
SELECT * FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'demo_query';

-- 查询某个存储过程的定义：
--   SHOW CREATE PROCEDURE 存储过程名称;
SHOW CREATE PROCEDURE p1;

删除

sql

-- DROP PROCEDURE [IF EXISTS] 存储过程名称;
DROP PROCEDURE IF EXISTS p1;

9.2.2 系统变量

系统变量由 MySQL 服务器提供，按作用域分为全局变量（GLOBAL）和会话变量（SESSION，默认）。

不同变量支持的作用域和动态修改能力各不相同。多数会话变量在建立连接时继承全局值，修改全局值后，已有会话仍使用各自的会话值。

查看系统变量

sql

-- 查看所有系统变量：
--   SHOW [SESSION | GLOBAL] VARIABLES;
SHOW GLOBAL VARIABLES;

-- 可以通过 LIKE 模糊匹配的方式查找变量：
--   SHOW [SESSION | GLOBAL] VARIABLES LIKE '...';
SHOW SESSION VARIABLES LIKE 'auto%';
SHOW GLOBAL VARIABLES LIKE 'auto%';

-- 查看指定变量的值：
--   SELECT @@[SESSION. | GLOBAL.]系统变量名;
SELECT @@GLOBAL.autocommit;

设置系统变量

sql

-- SET [SESSION | GLOBAL] 系统变量名 = 值;
SET SESSION autocommit = 0;

-- SET @@[SESSION. | GLOBAL.]系统变量名 = 值;
SET @@SESSION.autocommit = 0;

9.2.3 用户定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 @变量名 使用就可以。其作用域为当前连接。

未初始化时值为 NULL。建议通过 SET @变量名 = 值 赋值，避免依赖同一表达式中读写变量的未定义求值顺序。

9.2.4 局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要 DECLARE 声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其声明的 BEGIN ... END 块。

声明

sql

-- DECLARE 变量名 变量类型 [DEFAULT ...];
DELIMITER $$
CREATE PROCEDURE p2()
BEGIN
    DECLARE stu_count INT DEFAULT 0;
    SELECT stu_count;
END$$
DELIMITER ;

赋值

sql

-- SET 变量名 = 值;
-- SET 变量名 := 值;
-- SELECT 字段名 INTO 变量名 FROM 表名 ...;
DELIMITER $$
CREATE PROCEDURE p2_count()
BEGIN
    DECLARE stu_count INT DEFAULT 0;
    SELECT COUNT(*) INTO stu_count FROM `tb_user`;
    SELECT stu_count;
END$$
DELIMITER ;

9.2.5 `IF`

sql

DELIMITER $$
CREATE PROCEDURE p3()
BEGIN
    DECLARE score INT DEFAULT 58;
    DECLARE result VARCHAR(10);

    IF score >= 85 THEN
        SET result := '优秀';
    ELSEIF score >= 60 THEN
        SET result := '及格';
    ELSE
        SET result := '不及格';
    END IF;

    SELECT result;
END$$
DELIMITER ;

CALL p3(); -- 不及格

9.2.6 `WHILE`

sql

DELIMITER $$
CREATE PROCEDURE p4(IN p_n INT)
BEGIN
    DECLARE v_total BIGINT DEFAULT 0;
    IF p_n IS NULL OR p_n < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'n must be nonnegative';
    END IF;
    WHILE p_n > 0
        DO
            SET v_total := v_total + p_n;
            SET p_n := p_n - 1;
        END WHILE;
    SELECT v_total AS sum_result;
END$$
DELIMITER ;

CALL p4(100); -- 5050

9.2.7 `REPEAT`

REPEAT 是有条件的循环控制语句，当满足条件的时候退出循环。

它先执行循环体，再检查退出条件，至少执行一次。下列求和过程将 NULL、负数作为非法输入，0 返回 0。

sql

DELIMITER $$
CREATE PROCEDURE p5(IN p_n INT)
BEGIN
    DECLARE v_total BIGINT DEFAULT 0;
    IF p_n IS NULL OR p_n < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'n must be nonnegative';
    END IF;
    IF p_n > 0 THEN
        REPEAT
            SET v_total := v_total + p_n;
            SET p_n := p_n - 1;
        UNTIL p_n = 0 END REPEAT;
    END IF;
    SELECT v_total AS sum_result;
END$$
DELIMITER ;

CALL p5(100); -- 5050

9.2.8 `LOOP`

LOOP 实现简单的循环，如果不在 SQL 逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP 可以配合以下两个语句使用：

LEAVE：配合循环使用，退出循环
ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环

sql

-- 计算从 1 到 n 的和
DELIMITER $$
CREATE PROCEDURE p6(IN p_n INT)
BEGIN
    DECLARE v_total BIGINT DEFAULT 0;
    IF p_n IS NULL OR p_n < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'n must be nonnegative';
    END IF;
    sum:
    LOOP
        IF p_n <= 0 THEN
            LEAVE sum;
        END IF;
        SET v_total := v_total + p_n;
        SET p_n := p_n - 1;
    END LOOP sum;
    SELECT v_total AS sum_result;
END$$

-- 计算从 1 到 n 中间偶数的和
CREATE PROCEDURE p7(IN p_n INT)
BEGIN
    DECLARE v_total BIGINT DEFAULT 0;
    IF p_n IS NULL OR p_n < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'n must be nonnegative';
    END IF;
    sum:
    LOOP
        IF p_n <= 0 THEN
            LEAVE sum;
        END IF;
        IF p_n % 2 <> 0 THEN
            SET p_n := p_n - 1;
            ITERATE sum;
        END IF;
        SET v_total := v_total + p_n;
        SET p_n := p_n - 1;
    END LOOP sum;
    SELECT v_total AS sum_result;
END$$
DELIMITER ;

CALL p6(100); -- 5050
CALL p7(100); -- 2550

9.3 触发器

触发器是与数据库表有关的数据对象，指在 INSERT / UPDATE / DELETE 之前或之后，触发并执行触发器中定义的 SQL 语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	`OLD` 和 `NEW`
`INSERT` 型触发器	`NEW` 表示将要或已经新增的数据
`UPDATE` 型触发器	`OLD` 表示修改之前的数据，`NEW` 表示将要或已经修改的数据
`DELETE` 型触发器	`OLD` 表示将要或已经删除的数据

只有 BEFORE 触发器可通过 SET NEW.列 = 值 改写即将写入的值，OLD 始终只读。

10. 锁

按锁的粒度可分为全局锁、表级锁、行级锁。

10.1 全局锁

FLUSH TABLES WITH READ LOCK 获取全局读锁，在持锁期间阻止表的数据更新及相关提交、DDL 等操作，普通查询仍可执行。获取锁本身也可能等待正在执行的语句。

典型使用场景是做全库的逻辑备份，对所有表进行锁定，从而获取一致性视图，保证数据完整性。

在交互式会话 A 中加锁，并保持连接

sql

FLUSH TABLES WITH READ LOCK;

在另一个终端 B 中备份

mysqldump -u root -p demo_query > demo_query.sql

回到会话 A 解锁

sql

UNLOCK TABLES;

数据库中加全局锁，是一个比较重的操作，存在以下问题：

在主库持有全局读锁期间，业务写入会等待锁释放。
在副本持有全局读锁期间，复制事件的应用可能等待，增加复制延迟。

mysql -e 'FLUSH TABLES WITH READ LOCK' 执行后连接即结束，锁也随之释放。备份期间应保持会话 A 的连接。

对 InnoDB 数据，可以使用 --single-transaction 取得一致性快照，避免在整个备份期间持有全局读锁。该快照适用于 InnoDB 表，MyISAM、MEMORY 表的一致性需另行保证。备份期间需避免对被备份表执行可能破坏快照读取的 DDL。某些 GTID 或坐标相关选项仍可能需要短暂锁定。

mysqldump --single-transaction -u root -p demo_query > demo_query.sql

10.2 表级锁

表级锁覆盖整张表，具体用途与兼容关系取决于锁类型。MyISAM 和 MEMORY 主要通过表锁控制数据访问，InnoDB 也存在显式表锁、MDL 和意向锁。

对于表级锁，主要分为以下三类：

表锁
元数据锁（Meta Data Lock，MDL）
意向锁

10.2.1 表锁

对于表锁，分为两类：

表共享读锁（read lock）。我加了读锁以后，我能读不能写，别人也是能读不能写。
表独占写锁（write lock）。我加了写锁以后，我能读能写，别人既不能读也不能写。

语法：

加锁：LOCK TABLES 表名 ... READ/WRITE。
释放锁：UNLOCK TABLES / 客户端断开连接。

LOCK TABLES 会隐式提交当前事务，持锁连接访问的表也必须在锁定列表中列出，不宜直接夹在普通 START TRANSACTION 流程中使用。参见 LOCK TABLES。

10.2.2 元数据锁

访问表时系统自动获取相应的 MDL，用于协调表结构变更与数据访问，避免执行期间依赖的表定义被不兼容地修改。

MDL 从 MySQL 5.5 起提供。读写通常获得兼容的元数据锁，DDL（包括在线 DDL）在某些阶段需要更强的锁。事务访问过的表通常要到事务结束后才释放相应元数据锁，长事务因此可能阻塞 DDL。

查看元数据锁：

sql

SELECT `OBJECT_TYPE`, `OBJECT_SCHEMA`, `OBJECT_NAME`, `LOCK_TYPE`, `LOCK_DURATION`
FROM `performance_schema`.`metadata_locks`;

10.2.3 意向锁

为了避免 DML 在执行时加的行锁与表锁冲突，在 InnoDB 中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

意向共享锁（IS）：表明事务准备在表内获得共享记录锁，例如 SELECT ... FOR SHARE
意向排他锁（IX）：表明事务准备在表内获得排他记录锁，例如 SELECT ... FOR UPDATE

IS 与 IX 彼此兼容，记录锁是否冲突取决于双方访问的索引记录和锁模式。下面是 InnoDB 表级锁模式的兼容关系，S/X 指整表共享锁/排他锁：

已有锁 / 请求锁	IS	IX	S	X
IS	兼容	兼容	兼容	冲突
IX	兼容	兼容	冲突	冲突
S	兼容	冲突	兼容	冲突
X	冲突	冲突	冲突	冲突

查看意向锁：

sql

SELECT `OBJECT_SCHEMA`, `OBJECT_NAME`, `INDEX_NAME`,
       `LOCK_TYPE`, `LOCK_MODE`, `LOCK_DATA`
FROM `performance_schema`.`data_locks`;

10.3 行级锁

行级锁在较小范围内控制并发访问，可减少无关记录之间的冲突。热点记录上的竞争仍可能限制并发。

InnoDB 的记录锁实际作用在索引记录上，没有显式索引时也会使用隐藏的聚簇索引。常见的数据锁包括以下三类：

记录锁（Record Lock）：锁定索引记录，分为共享和排他模式，RC、RR 等隔离级别均使用。
间隙锁（Gap Lock）：阻止其他事务向指定间隙插入，不锁定间隙边界上的已有记录，RR 下常用于保护查询范围。
临键锁（Next-Key Lock）：记录锁与该记录前方间隙锁的组合，RR 的锁定扫描通常使用它。

10.3.1 行锁

InnoDB 实现了以下两种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

SQL	行锁类型	说明
`INSERT ...`	排他锁	自动加锁
`UPDATE ...`	排他锁	自动加锁
`DELETE ...`	排他锁	自动加锁
普通 `SELECT`，RC/RR 一致性读	不加记录锁	仍获取 MDL，并受视图可见性规则约束
`SELECT ... FOR SHARE`	共享锁	锁定读，旧语法为 `LOCK IN SHARE MODE`
`SELECT ... FOR UPDATE`	排他锁	需要手动在 `SELECT` 之后加 `FOR UPDATE`

RR 下的锁定读和写操作通常通过记录锁及临键锁保护访问范围。

针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
上述唯一匹配需要覆盖唯一索引的全部列。没有合适索引时，锁定扫描可能涉及大量记录和间隙，但 InnoDB 不会自动做行锁到表锁的升级。

需要在多条语句间保持锁时，应在显式事务中执行锁定读，并在完成后提交或回滚。自动提交下语句结束就释放事务锁。

查看行锁：

sql

SELECT `OBJECT_SCHEMA`, `OBJECT_NAME`, `INDEX_NAME`,
       `LOCK_TYPE`, `LOCK_MODE`, `LOCK_DATA`
FROM `performance_schema`.`data_locks`;

10.3.2 间隙锁/临键锁

锁范围需要结合隔离级别与访问路径判断，常见情况如下：

RR 下完整唯一键的等值锁定读命中已有记录时，通常只锁记录。若记录不存在，则可锁住其应插入的间隙。
非唯一键等值查询或范围查询，通常需要 next-key lock 覆盖搜索范围。具体边界取决于谓词、索引和执行计划。
RC 下通常不为普通搜索保留间隙锁，但外键检查和重复键检查等仍可能使用间隙锁。

间隙锁用于阻止插入，多个事务的间隙锁可以共存，获得同一间隙的锁本身不会互相阻塞。

11. InnoDB 引擎

本节进一步讨论 InnoDB 的存储结构、内存组件与事务实现。

11.1 逻辑存储结构

InnoDB 把数据按下面 5 个层级组织，自顶向下：

层级	大小	关系
Tablespace（表空间）	可增长	可独立于单表，也可供多表共享，部分表空间包含多个文件
Segment（段）	可增长	每个索引分别管理叶子与非叶子节点的段
Extent（区）	默认 1 MiB	默认页大小下包含 64 个连续页
Page（页）	默认 16 KiB	页大小在初始化时配置，区大小随页大小变化
Row（行）	变长	索引页保存记录，较长字段可有页外部分

每个索引的叶子与非叶子部分使用各自的段。小段可以先分配零散页，增长后再分配完整区。回滚段用于管理 undo log。

行的隐藏列

DB_TRX_ID：聚簇记录上最近一次插入或修改的事务 ID，占 6 字节
DB_ROLL_PTR：回滚指针，用于通过 undo 重建历史版本，占 7 字节
DB_ROW_ID：表没主键也没非空唯一索引时由 InnoDB 自动生成的 6 字节隐藏主键，作为聚簇索引使用。

11.2 架构总览

InnoDB 在内存中缓存数据页和日志，在磁盘中管理表空间与日志文件，通过后台线程完成刷页、日志写入和历史版本清理。

11.2.1 内存

Buffer Pool：缓存数据页和索引页，页大小与实例配置一致。使用带有老区和新区的 LRU 管理方法减少顺序扫描对热点缓存的冲击。可以按状态区分页：

free page：可供分配的缓冲页
clean page：与磁盘内容一致的缓存页
dirty page：内容已修改且尚未写回磁盘的缓存页

空闲页、LRU 和脏页刷盘分别有对应的管理链表。

Change Buffer：启用后，可缓冲不在 Buffer Pool 中的部分非唯一二级索引页变更，在读取页面或后台合并时应用，以减少随机读取。包含降序列的二级索引不支持 Change Buffer。8.4 的 innodb_change_buffering 默认是 none，8.0 默认是 all。

Adaptive Hash Index：启用后，InnoDB 根据访问模式为部分索引页建立内存哈希结构，尝试加速适用的查找。它也可能带来锁竞争，需要测量是否受益。8.4 默认关闭，8.0 默认开启，开关是 innodb_adaptive_hash_index。参见自适应哈希索引。

Log Buffer：redo log 的内存缓冲，由 innodb_log_buffer_size 控制，8.4 默认 64 MiB，8.0 默认 16 MiB。长事务也可能在提交前写出部分日志。undo log 不使用这个缓冲区。

11.2.2 磁盘

文件 / 区域	用途
System Tablespace（通常为 `ibdata1`）	包含 Change Buffer 等内部结构，也可配置存放表数据
Data Dictionary Tablespace（`mysql.ibd`）	8.0 起的事务数据字典，以及 `mysql` 系统库中的相关表
File-Per-Table（`tb_name.ibd`）	每张表一个数据文件，包含本表的聚簇索引、二级索引、数据
General Tablespaces	用户用 `CREATE TABLESPACE` 显式建出来共享给多张表
Undo Tablespaces	存放持久表事务的 undo log，初始化时创建两个默认表空间
Temporary Tablespaces	会话临时表、全局临时表的数据
Doublewrite Buffer	默认启用，先保存待刷数据页的完整副本，用于恢复部分写入的数据页。8.0.20 起使用独立 doublewrite 文件，旧版存于系统表空间
Redo Log（`#innodb_redo/` 目录）	8.0.30 起使用该目录，记录用于崩溃恢复的页修改，容量由 `innodb_redo_log_capacity` 控制

通用表空间的语法可看下面这条：

sql

CREATE TABLESPACE my_tablespace 
ADD DATAFILE 'my_tablespace.ibd' ENGINE = InnoDB;

CREATE TABLE a (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(10)
) ENGINE=InnoDB TABLESPACE my_tablespace;

11.2.3 后台线程

InnoDB 是多线程引擎，关键的几个角色：

Master Thread：协调后台活动，包括适用的 Change Buffer 合并等任务
IO Thread：处理后台读写请求及完成通知。8.4 的读 IO 线程默认数量按可用逻辑处理器数计算

配置项	8.4 默认值	用途
`innodb_read_io_threads`	可用逻辑处理器数的一半，至少 4，最多 64	后台读 IO 线程
`innodb_write_io_threads`	4	后台写 IO 线程
`innodb_page_cleaners`	随 Buffer Pool 实例数确定	脏页刷盘线程

日志写入和持久化由专门的日志线程处理。实际配置可查 SHOW VARIABLES，运行状态可通过以下命令观察，输出随版本与负载变化：

sql

SHOW ENGINE InnoDB STATUS\G

-- 可同时查询当前配置，状态输出不是线程数量的唯一依据。
SHOW VARIABLES WHERE Variable_name IN
    ('innodb_read_io_threads', 'innodb_write_io_threads',
     'innodb_page_cleaners', 'innodb_purge_threads');

Purge Thread：清理不再需要的历史版本与删除标记，线程数由 innodb_purge_threads 控制
Page Cleaner Thread：负责 Buffer Pool 的脏页刷盘

11.3 事务原理

InnoDB 的事务靠两套日志撑起来：redo log 负责崩溃恢复（D），undo log 负责回滚和 MVCC（A 与 I）。

11.3.1 redo log

redo 描述页及记录的修改，属于物理层面的恢复日志。WAL 要求相关 redo 先持久化，修改后的数据页才能持久化。简化流程如下：

事务里的修改先改 Buffer Pool 中的页（这页变脏）。
修改的物理动作写进 Log Buffer。
事务 commit 时按 innodb_flush_log_at_trx_commit 的策略把 Log Buffer 刷到磁盘 redo log。
脏页可以晚一点由 Page Cleaner 刷回 .ibd。

崩溃恢复会根据 redo 恢复尚未落入数据文件的修改，其中可能包括未提交事务的修改，再用 undo 回滚未完成事务。

innodb_flush_log_at_trx_commit 三档：

1（默认）：每次提交要求 redo 写入并完成持久化，可通过组提交合并实际刷盘
2：每次提交写入文件系统缓存，由后台周期性持久化，操作系统崩溃或断电可丢失近期提交
0：提交时不要求写出，由后台周期性写入并持久化，服务进程崩溃也可能丢失近期提交

后台间隔受 innodb_flush_log_at_timeout 控制，调度延迟等使“一秒”不是严格的最大丢失窗口。需要 redo 与 binlog 都持久化时，通常同时使用 innodb_flush_log_at_trx_commit=1 和 sync_binlog=1，前提是操作系统与设备正确兑现刷盘要求。

11.3.2 undo log

undo 保存撤销聚簇记录修改和重建旧版本所需的信息，通常归为逻辑日志。回滚时根据这些信息恢复记录的先前状态。

它有两个用途：

事务回滚时按 undo log 逆推回去。
MVCC 读历史版本时沿着回滚指针往老版本走。

undo log 由回滚段中的 undo slot 管理，槽数与页大小有关。INSERT undo 在提交后不再需要，UPDATE undo 还用于一致性读，必须等历史版本不再被需要时才能 purge。

11.4 MVCC

MVCC 让一致性读通过历史版本获得可见数据，通常无需与写操作争用记录锁。锁定读、写操作和元数据访问仍受相应锁约束。

11.4.1 当前读 vs 快照读

类型	看的是	加锁	触发场景
锁定读 / 写操作，常称当前读	访问当前记录并遵守冲突锁，必要时等待其他事务	加数据锁	DML、`SELECT ... FOR UPDATE` / `FOR SHARE`
一致性读，常称快照读	ReadView 可见的数据及本事务的修改，可能需要历史版本	不加记录锁，仍有 MDL	RC/RR 下的普通 `SELECT`

不同隔离级别下，快照读的"快照"是什么时候定的：

Read committed：每次一致性读建立新的快照，同一事务可能看到其他事务新提交的修改
Repeatable read：第一次一致性读建立快照，后续一致性读复用。START TRANSACTION WITH CONSISTENT SNAPSHOT 可在事务开始时建立快照，它在 RR 下生效
Serializable：关闭自动提交时，普通 SELECT 隐式转为 FOR SHARE。自动提交下独立的只读查询可以使用非锁定一致性读

锁定读不会按上述 RR 规则建立用于后续一致性读的快照。参见一致性读。

11.4.2 隐藏字段（再回顾一次）

字段	作用
`DB_TRX_ID`	写入这条记录的事务 ID
`DB_ROLL_PTR`	指向 undo log 中的上一个版本，串起版本链
`DB_ROW_ID`	没主键且没 NOT NULL 唯一索引时，InnoDB 兜底生成

11.4.3 undo log 与版本链

聚簇索引保存当前记录，DB_ROLL_PTR 指向可用于重建更早状态的 undo 记录。读取时先判断当前版本，不可见才通过 undo 逐步重建历史版本。

11.4.4 ReadView 与可见性判断

ReadView 保存快照建立时的活跃读写事务信息和可见性边界。以下使用 MySQL 8.4 源码中的字段名，纯只读事务不一定获得普通读写事务 ID：

字段	含义
`m_ids`	快照建立时活跃的读写事务 ID 集合，不含创建者
`m_up_limit_id`	最小活跃事务 ID，没有其他活跃事务时等于下方边界
`m_low_limit_id`	建立快照时下一个将分配的读写事务 ID
`m_creator_trx_id`	创建该 ReadView 的事务 ID

判断一个数据版本上的 DB_TRX_ID 是否对当前事务可见，按下面顺序逐条判：

情况	结论
`trx_id == m_creator_trx_id`	自己的修改，可见
`trx_id < m_up_limit_id`	在快照建立前已经提交，可见
`trx_id >= m_low_limit_id`	在快照建立时尚未分配的事务，不可见
位于两个边界之间，且属于 `m_ids`	建立快照时仍活跃，不可见
位于两个边界之间，且不属于 `m_ids`	建立快照前已提交，可见

不可见时继续重建前一版本。如果不存在可见的记录版本，这一行不进入结果集。删除标记也按相应版本的可见性处理。源码可参考 MySQL 8.4.6 的 ReadView。

12. MySQL 管理

12.1 系统数据库

MySQL 数据库安装完成后，自带了以下四个数据库，具体作用如下：

数据库	含义
`mysql`	存储 MySQL 服务器正常运行所需要的各种信息（时区、主从、用户、权限等）
`information_schema`	提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等
`performance_schema`	为 MySQL 服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
`sys`	包含了一系列方便 DBA 和开发人员利用 `performance_schema` 性能数据库进行性能调优和诊断的视图

12.2 常用工具

12.2.1 `mysql`

mysql 是连接服务器并执行 SQL 的客户端工具。

语法：mysql [options] [database]
选项：
- -u, --user=name：指定用户名
- -p, --password[=password]：省略值时交互式输入密码，短选项携带值时写为 -p密码
- -h, --host=name：指定服务器 IP 或域名
- -P, --port=port：指定连接端口
- -e, --execute=name：执行 SQL 语句并退出

-e 选项可以在 MySQL 客户端连接数据库后执行 SQL 语句，执行完成后自动退出，对于一些批处理脚本，这种方式尤其方便。

示例：

mysql -h 127.0.0.1 -P 3306 -u root -p demo_query -e "SELECT id, name FROM tb_user ORDER BY id"

12.2.2 `mysqladmin`

mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。

通过帮助文档查看选项：

mysqladmin --help

示例：

# 创建一个专门用于演示删除操作的空库。
mysqladmin -u root -p create demo_drop
mysqladmin -u root -p drop demo_drop

mysqladmin -u root -p version
# Server version 字段表示服务端版本，其他状态值随运行环境变化。

12.2.3 `mysqlbinlog`

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些日志的文本格式，就会使用到 mysqlbinlog 日志管理工具。

语法：mysqlbinlog [options] log-files1 log-files2 ...
选项：
- -d, --database=name：按数据库筛选，语句日志与行日志的筛选语义不同。
- -o, --offset=#：跳过前 n 个日志事件。
- -r, --result-file=name：将输出的文本格式日志输出到指定文件。
- -s, --short-form：显示简单格式，省略掉一些信息。
- --start-datetime=date1 --stop-datetime=date2：按运行该工具的本地时区解释日期时间边界。
- --start-position=pos1 --stop-position=pos2：指定位置间隔内的所有日志。

# 先在 SQL 客户端查询位置和文件列表：
# SELECT @@log_bin_basename;
# SHOW BINARY LOGS;

# 以下路径替换为当前实例的实际日志文件。
mysqlbinlog /path/to/binlog.000001

# 仅用于阅读 ROW 事件，输出不是直接用于恢复的 SQL。
mysqlbinlog --base64-output=DECODE-ROWS -vv /path/to/binlog.000001

检查 ROW 事件时使用 --base64-output=DECODE-ROWS -vv 可显示带注释的伪 SQL，便于阅读。实际回放需要工具默认生成的可执行输出，并保留完整事务及所需日志文件。

12.2.4 `mysqlshow`

mysqlshow 客户端对象查找工具，用来快速查找存在哪些数据库、数据库中的表、表中的列或者索引。

语法：mysqlshow [options] [db_name [table_name [col_name]]]
选项：
- --count：显示数据库及表的统计信息（数据库，表均可以不指定）
- -i：显示指定数据库或者指定表的状态信息
- -k, --keys：显示表索引

示例：

mysqlshow -u root -p --count

mysqlshow -u root -p demo_query --count

mysqlshow -u root -p --count demo_query 'tb\_user'
# 显示指定表的索引：
mysqlshow -u root -p --keys demo_query 'tb\_user'

--count 会执行计数，较大的表可能耗时，输出随当前数据变化。末尾参数中的 _ 等字符会被当作通配符，按表名查看时应像示例一样转义，或添加列模式参数。参见 mysqlshow。

12.2.5 `mysqldump`

mysqldump 是 MySQL 的客户端备份工具，主要用于：

数据库备份：生成包含建表语句、数据插入语句的 SQL 文件
数据迁移：在不同 MySQL 实例（或数据库）间转移数据。

12.2.6 `mysqlimport`/`source`

mysqlimport 用 LOAD DATA 导入分隔文本文件，并根据文件名确定目标表名，不限于 mysqldump -T 生成的文件。例如 tb_insert.csv 对应已存在的 tb_insert 表，列分隔符需与文件一致。

source /path/to/backup.sql 是 mysql 客户端执行 SQL 文件的命令，也可在终端使用 mysql -u root -p demo_query < backup.sql。它与导入 CSV 的 mysqlimport 用途不同。

13. 日志

13.1 错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障无法正常使用时，建议首先查看此日志。

错误输出的目标取决于启动方式和 log_error 配置，可以是文件或标准错误。查看配置：

sql

SHOW VARIABLES LIKE '%log_error%';
-- 路径与状态以当前实例输出为准。

13.2 二进制日志

二进制日志（binlog）记录需要用于复制和恢复的数据变更及相关事件，通常不记录不改变数据的普通 SELECT、SHOW。记录范围还受 sql_log_bin、过滤配置及语句类型等影响。

作用：

配合完整备份进行时间点恢复
MySQL 的主从复制。在 MySQL 8 版本中，默认二进制日志是开启的，涉及到的参数如下：

sql

SHOW VARIABLES LIKE '%log_bin%';
-- 路径与状态以当前实例输出为准。

MySQL 服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：

日志格式	含义
STATEMENT	基于 SQL 语句的日志记录，记录的是 SQL 语句，对数据进行修改的 SQL 都会记录在日志文件中。
ROW	基于行的日志记录，记录的是每一行的数据变更。（默认）
MIXED	混合了 STATEMENT 和 ROW 两种格式，优先采用 STATEMENT，在某些特殊情况下会自动切换为 ROW 进行记录。

查看当前二进制日志格式的语句：

sql

SHOW VARIABLES LIKE '%binlog_format%';
-- 路径与状态以当前实例输出为准。

8.4 默认开启 binlog，并使用 ROW。binlog_format 从 8.0.34 起已弃用，虽然 8.4 仍支持上述三种格式，新的配置应以行日志为主。DDL 即使在 ROW 模式下仍主要记录为语句事件。

13.2.1 日志查看

使用 mysqlbinlog 将二进制日志转换为可读形式：

语法：mysqlbinlog [参数选项] logfilename
参数选项：
- -d：按数据库过滤，具体语义见 12.2.3。
- -o：跳过指定数量的事件。
- -v：把行事件显示为注释形式的伪 SQL。
- -vv：在上述显示中增加类型等元信息。

13.2.2 日志删除

对于比较繁忙的业务系统，每天生成的 binlog 数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：

指令	含义
`PURGE BINARY LOGS TO 'binlog.000123'`	删除指定文件之前的日志，保留该文件
`PURGE BINARY LOGS BEFORE '2026-01-01 00:00:00'`	删除指定时间之前的日志文件

清理前应确认备份恢复和所有复制节点均不再需要这些日志。8.4 已移除 RESET MASTER 和 PURGE MASTER LOGS。RESET BINARY LOGS AND GTIDS 会删除全部 binlog 并清空 GTID 执行历史，不应作为日常日志清理命令。

也可以在 MySQL 的配置文件中配置二进制日志的过期时间，设置了以后，二进制日志过期会自动删除。

sql

SHOW VARIABLES LIKE '%binlog_expire%';
-- 路径与状态以当前实例输出为准。

8.4 默认 binlog_expire_logs_seconds=2592000，即 30 天，binlog_expire_logs_auto_purge=ON。自动清理通常在启动、日志轮转等时机检查并删除到期日志。

13.3 查询日志

通用查询日志记录客户端连接、断开及服务器接收到的语句。8.4 默认关闭，可用于临时观察请求，与记录数据变更的 binlog 用途不同。

sql

SHOW VARIABLES LIKE '%general%';
-- 路径与状态以当前实例输出为准。

修改 MySQL 的配置文件，添加以下内容：

cnf

[mysqld]
log_output       = FILE
general_log_file = query.log
general_log      = 1

日志目标还由 log_output 决定，可能为文件或表。持续记录会增加开销，应在诊断完成后恢复所需配置。参见通用查询日志。

13.4 慢查询日志

见 7.4.2 慢查询日志

14. 主从复制

传统复制由源服务器（source，主库）提供 binlog，副本（replica，从库）接收并应用事件。普通复制默认异步，主库可以在副本尚未应用事务时完成提交，因此读副本可能读到旧数据。一主多副本及链式复制均可配置。

常见用途：

故障切换：选择合适的副本提升为主库，普通复制本身不提供完整的自动选主和客户端切换
读写分离：将适合容忍复制延迟的读取分配给副本
备份：在副本执行备份可以减少主库负担，但仍会消耗副本资源，加锁时可能阻塞复制应用

简化工作流程：

源服务器记录 binlog，通过连接对应的 binlog dump 线程向副本发送事件
副本接收线程（receiver，传统称 IO Thread）取得事件并写入 relay log
副本应用线程（applier，传统称 SQL Thread）读取 relay log，或由协调线程分派给多个 worker 并行应用

8.4 的 binlog 默认是 ROW，gtid_mode 默认是 OFF。GTID 复制需要另行配置 gtid_mode=ON、enforce_gtid_consistency=ON 等前提，启用后可通过 SOURCE_AUTO_POSITION=1 使用事务标识定位。

8.4 使用 CHANGE REPLICATION SOURCE TO、START REPLICA、STOP REPLICA、SHOW REPLICA STATUS。旧的 CHANGE MASTER TO、START SLAVE、SHOW SLAVE STATUS 等语法已经移除。

在副本查看 SHOW REPLICA STATUS\G，重点检查 Replica_IO_Running、Replica_SQL_Running、Last_IO_Error、Last_SQL_Error。Seconds_Behind_Source 只反映特定条件下的延迟估计，网络延迟、接收线程落后或断开等可能使它低估延迟或为 NULL。评估复制进度还需结合线程状态和事务执行位置。

15. 分库分表

垂直扩容是提升单节点的 CPU、内存、存储等资源，水平扩容是增加节点分担数据或负载。分库分表是拆分数据的手段，可用于缓解单实例或单表的容量及访问压力。常见瓶颈包括：

IO 瓶颈：工作集不能有效缓存，随机读写或网络传输成为限制
CPU 瓶颈：查询处理、排序、分组等持续消耗处理能力

路由键的选择影响数据与负载分布，热点可能集中在部分分片。设计时需处理跨片查询、事务、全局约束、ID 生成和扩容迁移，并结合现有索引与查询评估拆分需求。

15.1 拆分策略

15.1.1 垂直分库

按业务域将不同的表放在不同库中，便于独立部署。跨库关联与事务需要另外设计。

15.1.2 垂直分表

按列拆出多个表，通常保留共同的主键。例如将低频的大字段从主表拆出，需要完整记录时通过键进行 JOIN。

15.1.3 水平分库

按路由键把同类记录分配到不同库，相关分片表通常保持相同结构。跨库查询可能需要应用或中间件合并结果。

15.1.4 水平分表

按路由键把同一逻辑表的记录分配到多个同结构的物理表。各片行集合共同组成逻辑表，唯一约束和外键在物理表或实例范围内生效，跨片约束需要额外协调。

15.2 实现技术

下面按接入位置和路由方式比较几种方案：

Apache ShardingSphere-JDBC：在 Java 应用的 JDBC 接口层处理 SQL 解析、路由、改写和结果归并。参见 5.5.2 文档。
MyCat：独立数据库代理，客户端通过代理访问后端分片。业务接入方式由路由规则和 SQL 支持范围决定。
GORM Sharding：运行在 Go 应用内的 GORM 分表插件，通过修改访问目标表实现路由，本身不提供跨多个数据库节点的完整分库方案。

15.3 GORM Sharding

以下以 gorm.io/sharding v0.6.2 为例。插件在连接访问层解析 SQL 并改写表名，支持 PostgreSQL、MySQL 和主键生成器。参见 v0.6.2 文档。

特性：

需要先准备物理分片表，再注册逻辑表和路由规则
常规路由需要分片键的等值条件，缺少可用路由键时会报错。跨片范围查询需另行设计
默认 Snowflake 可从生成的主键解析分片，其他主键方案如需按主键查询要配置相应路由算法
v0.6.2 不支持 GORM 的 PrepareStmt: true

15.3.1 安装

go mod init example.com/sharding-demo
go get gorm.io/sharding@v0.6.2 gorm.io/driver/mysql@v1.5.7 gorm.io/gorm@v1.25.12

15.3.2 用法

先用管理账号创建空练习库和专用账号：

sql

CREATE DATABASE demo_sharding CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE USER 'demo_sharding'@'localhost' IDENTIFIED BY 'DemoPass_846!';
GRANT CREATE, ALTER, INDEX, SELECT, INSERT, UPDATE, DELETE
ON demo_sharding.* TO 'demo_sharding'@'localhost';

在独立的 Go module 中安装上述依赖，将代码保存为 main.go。设置 MYSQL_DSN 后执行 go run .，例如：

MYSQL_DSN='demo_sharding:DemoPass_846!@tcp(127.0.0.1:3306)/demo_sharding?charset=utf8mb4&parseTime=true' go run .

这是单进程练习，物理表通过迁移 API 创建，示例账号具有该库建表和读写权限。

package main

import (
	"errors"
	"fmt"
	"log"
	"os"

	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/sharding"
)

type Order struct {
	ID        int64 `gorm:"primaryKey"`
	UserID    int64
	ProductID int64
}

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func expectMissingKey(err error) {
	if !errors.Is(err, sharding.ErrMissingShardingKey) {
		log.Fatalf("expected ErrMissingShardingKey, got %v", err)
	}
	fmt.Println("ErrMissingShardingKey")
}

func main() {
	dsn := os.Getenv("MYSQL_DSN")
	if dsn == "" {
		log.Fatal("set MYSQL_DSN to the demo_sharding database")
	}
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	must(err)
	sqlDB, err := db.DB()
	must(err)
	defer sqlDB.Close()

	// 先建立物理表，再注册插件。示例使用 64 张表。
	for i := 0; i < 64; i++ {
		must(db.Table(fmt.Sprintf("orders_%02d", i)).AutoMigrate(&Order{}))
	}
	must(db.Use(sharding.Register(sharding.Config{
		ShardingKey:         "user_id",
		NumberOfShards:      64,
		PrimaryKeyGenerator: sharding.PKSnowflake,
	}, "orders")))

	// 分别路由到 orders_02 和 orders_03。
	must(db.Create(&Order{UserID: 2, ProductID: 100}).Error)
	must(db.Exec("INSERT INTO orders(user_id, product_id) VALUES(?, ?)", 3, 101).Error)

	var orders []Order
	must(db.Where("user_id = ?", 2).Find(&orders).Error)
	fmt.Printf("user 2: %#v\n", orders)
	must(db.Raw("SELECT * FROM orders WHERE user_id = ?", 3).Scan(&orders).Error)
	fmt.Printf("user 3: %#v\n", orders)

	// 缺少路由所需的键，不会自动遍历所有分片。
	expectMissingKey(db.Exec("INSERT INTO orders(product_id) VALUES(?)", 102).Error)
	expectMissingKey(db.Where("product_id = ?", 100).Find(&orders).Error)
	must(db.Exec("UPDATE orders SET product_id = ? WHERE user_id = ?", 103, 3).Error)
	expectMissingKey(db.Exec("DELETE FROM orders WHERE product_id = ?", 103).Error)
}

WARNING

内置 Snowflake 的节点号由分片号决定，多个应用进程使用相同规则时可能发生 ID 冲突。生产环境应采用保证跨进程唯一性的主键方案，更换生成规则时，按主键路由的算法也需与之匹配。分片数量变化需要数据迁移，修改配置不会自动移动已有记录。

15.4 分片方式

范围分片：根据指定的字段所在的范围与节点的对应关系，来决定该数据属于哪一个分片。
取模分片：根据指定的字段值与节点数量进行求模运算，根据运算结果，来决定该数据属于哪一个分片。
一致性哈希：节点集合不变时，同一键映射保持稳定。增加或移除节点时仍有部分键重新映射，优势是通常减少迁移范围，已有数据也需要迁移。参见 Karger et al., 1997。
枚举分片：将有限取值映射到指定节点，需评估各取值的数据量和访问量，避免热点集中。
按日期分片：按时间范围拆分，便于归档与过期数据管理，但最新分片可能承受主要写入压力。

分片策略的支持范围由所用工具决定。GORM Sharding 的自定义算法需同时维护路由规则、表后缀和迁移方案。

MySQL ​

1. SQL ​

1.1 DDL ​

1.1.1 数据库操作 ​

1.1.2 表操作 ​

1.1.3 数据类型 ​

1.2 DML ​

1.3 DQL ​

1.3.1 基础查询 ​

1.3.2 条件查询 ​

1.3.3 聚合函数 ​

1.3.4 分组查询 ​

1.3.5 排序查询 ​

1.3.6 分页查询 ​

1.4 DCL ​

1.4.1 用户管理 ​

1.4.2 权限控制 ​

2. 函数 ​

2.1 字符串函数 ​

2.2 数值函数 ​

2.3 日期函数 ​

2.4 流程函数 ​

3. 约束 ​

4. 多表查询 ​

4.1 内连接 ​

4.2 外连接 ​

4.3 自连接 ​

4.4 联合查询 ​

4.5 子查询 ​

4.5.1 标量子查询 ​

4.5.2 列子查询 ​

4.5.3 行子查询 ​

4.5.4 表子查询 ​

5. 事务 ​

5.1 事务操作 ​

5.2 四大特性 ACID ​

5.3 并发事务问题 ​

5.4 事务隔离级别 ​

6. 存储引擎 ​

6.1 InnoDB ​

6.2 MyISAM ​

6.3 MEMORY ​

6.4 对比 ​

7. 索引 ​

7.1 结构 ​

7.2 分类 ​

7.3 语法 ​

7.4 SQL 性能分析 ​

7.4.1 SQL 执行频率 ​

7.4.2 慢查询日志 ​

7.4.3 语句耗时 ​

7.4.4 EXPLAIN 执行计划 ​

7.5 使用规则 ​

7.5.1 最左前缀原则 ​

7.5.2 范围查询 ​

7.5.3 难以使用索引定位的情况 ​

7.5.4 SQL 提示 ​

7.5.5 覆盖索引 ​

7.5.6 前缀索引 ​

8. SQL 优化 ​

8.1 INSERT 优化 ​

8.1.1 小批量插入数据 ​

8.1.2 大批量插入数据 ​

8.2 主键优化 ​

8.2.1 页分裂 ​

8.2.2 页合并 ​

8.2.3 主键设计原则 ​

8.3 ORDER BY 优化 ​

8.4 GROUP BY 优化 ​

8.5 LIMIT 优化 ​

8.6 COUNT 优化 ​

8.6.1 COUNT 的几种用法 ​

8.7 UPDATE 优化 ​

9. 视图 / 存储过程 / 触发器 ​

9.1 视图 ​

9.2 存储过程 ​

9.2.1 基本语法 ​

9.2.2 系统变量 ​

9.2.3 用户定义变量 ​

9.2.4 局部变量 ​