MySQL全量实战手册：从CRUD到索引事务与SQL优化

很多新手学MySQL，最容易踩的坑就是“CRUD都会写，一上生产就懵”。SELECT、INSERT、UPDATE这些谁都能敲两行，但真到了建表、加索引、做统计、排故障的时候，才发现自己压根没吃透SQL的底层逻辑。这篇全量实战手册就是干这个用的——从连接数据库、设计表结构，到复杂的多表关联、窗口函数，再到底层索引原理和事务隔离级别，我会把SQL核心操作一条条过一遍，每一块都配上可直接复制的代码和避坑说明。面向的人群很明确：刚入门想系统打基础的同学，以及写了两三年SQL但总在性能优化、事务一致性上翻车的初级开发。保证你看完能直接用到项目里。

我这几年在项目里处理过不少MySQL的“疑难杂症”，比如一条看似正常的UPDATE把整个表数据改了、LEFT JOIN查出来一堆重复记录、加了索引查询反而变慢、字符集导致乱码……这些场景我都会在对应的章节里还原现场，告诉你根因和处理方式。这篇文章不会跟你扯虚的，所有技巧都来自真实操作，你照着抄就能少掉几根头发。

1. 开干前的基础框架：连接、库表与SQL分类

1.1 连接MySQL的那些细节

连接数据库看似简单，实际踩坑的也不少。最基础的命令是这样：

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

很多人在本地连接没问题，一到服务器上就连不上，大多不是密码错了，而是权限和网络问题。首先要确认MySQL服务有没有起来：

systemctl status mysqld # 或者 service mysql status

然后确认3306端口是否监听：

netstat -tlnp | grep 3306

如果服务正常、端口在听，但远程还是连不上，99%是账号权限没开或者防火墙拦着。MySQL默认的root账号只能本机登录，你要用某个IP远程连，需要单独授权：

CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPass123'; GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'192.168.1.%'; FLUSH PRIVILEGES;

这里有个特容易踩的坑：用GRANT ALL PRIVILEGES ON *.*给一个应用账号全库权限。生产环境千万别这么干，权限越大出事范围越大。给最小必要权限是基本原则。比如只让某个账号管某几个库：

GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'192.168.1.%';

还有一个小技巧：字符集的问题在连接时就要处理。连接后先看一眼当前会话的字符集：

SHOW VARIABLES LIKE 'character_set%';

我建议所有连接都明确指定UTF-8编码。命令行可以加参数：

mysql -h 127.0.0.1 -u root -p --default-character-set=utf8mb4

不然你写入中文后，再查出来是乱码，这锅真不一定该MySQL背。

1.2 库级操作与SQL分类框架

SQL一共分五大类，很多人学了半年都说不全：

DDL（数据定义语言）：CREATE、ALTER、DROP，操作的是库和表的结构。 DML（数据操作语言）：INSERT、UPDATE、DELETE，操作的是表里的数据。 DQL（数据查询语言）：SELECT，最核心最复杂。 DCL（数据控制语言）：GRANT、REVOKE，管权限。 TCL（事务控制语言）：COMMIT、ROLLBACK、SAVEPOINT，管事务。

先看库级DDL，这一块命令不多，但容易在命名和默认值上栽跟头。

-- 创建数据库 CREATE DATABASE IF NOT EXISTS company_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; -- 查看所有库 SHOW DATABASES; -- 切换库 USE company_db; -- 删除库（慎用！） DROP DATABASE IF EXISTS company_db;

IF NOT EXISTS和IF EXISTS这两个修饰词，写脚本的时候一定要带上。因为自动化部署脚本执行两次会报错，加上就是幂等的，可以重复执行不炸。

字符集我直接给结论：MySQL 8.0默认就是utf8mb4，你基本不用改；但5.7默认是utf8mb3，也就是常说的utf8，它存不了emoji和某些生僻字。所以建库时我习惯显式声明：

CREATE DATABASE IF NOT EXISTS company_db DEFAULT CHARACTER SET utf8mb4;

collate排序规则，utf8mb4_general_ci是大小写不敏感的常用选择，适合大部分业务。如果按二进制精确匹配（比如账号密码校验），可以用utf8mb4_bin。

1.3 手把手建两张演示表

后面所有CRUD和查询案例，我都基于一个模拟项目X的“员工管理”场景，建两张表。这是贯穿全文的实操数据。

USE company_db; -- 部门表 CREATE TABLE dept ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID', name VARCHAR(50) NOT NULL UNIQUE COMMENT '部门名称', location VARCHAR(100) DEFAULT NULL COMMENT '办公地点', created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表'; -- 员工表 CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID', name VARCHAR(50) NOT NULL COMMENT '姓名', gender ENUM('M','F') NOT NULL COMMENT '性别', dept_id INT NOT NULL COMMENT '部门ID', salary DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '月薪', hire_date DATE NOT NULL COMMENT '入职日期', status TINYINT NOT NULL DEFAULT 1 COMMENT '1在职，0离职', CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';

插入一些演示数据供全文使用：

INSERT INTO dept (name, location) VALUES ('技术部', 'A栋3楼'), ('产品部', 'B栋2楼'), ('运营部', 'B栋4楼'); INSERT INTO emp (name, gender, dept_id, salary, hire_date, status) VALUES ('张三', 'M', 1, 25000, '2019-03-11', 1), ('李四', 'F', 1, 18000, '2020-07-01', 1), ('王五', 'M', 1, 22000, '2018-01-15', 1), ('赵六', 'F', 2, 15000, '2021-05-20', 1), ('孙七', 'M', 2, 16000, '2022-02-14', 0), ('周八', 'M', 3, 12000, '2023-08-01', 1), ('吴九', 'F', 3, 13000, '2022-11-11', 1);

DECIMAL(10,2)表示总共10位数字、保留2位小数，最大能存99999999.99。这里千万别用FLOAT和DOUBLE存钱，二进制浮点会有精度误差，金额算错就是事故。这是我反复强调的。

2. DDL进阶：表结构设计与修改的学问

2.1 数据类型选择的核心经验

建表就是用数据类型给业务数据做“稳定的骨架”。选错类型的后果，轻则浪费存储，重则系统直接出故障。分享几个多年实战后沉淀下来的选型原则。

整数类型上，TINYINT（1字节，范围-128到127）、SMALLINT（2字节）、INT（4字节）、BIGINT（8字节）。状态值用TINYINT，比如status字段的0/1；主键自增用BIGINT还是INT？如果表数据量预期过亿，直接用BIGINT，别等ID溢出了再改表，那会儿锁表锁到你想哭。

字符串类型上，定长用CHAR，变长用VARCHAR。CHAR适合固定长度的编码如手机号、身份证号，实际上身份证号建议用CHAR(18)；VARCHAR要指定最大长度，且注意VARCHAR(255)和VARCHAR(256)在存储上有微妙差别，255以内长度前缀用1字节，超过用2字节——这个细节很多人不知道。

金额类型，再次强调：用DECIMAL。FLOAT和DOUBLE不精确，银行家算法也救不了你。

时间类型上，DATE存日期，DATETIME存日期和时间，TIMESTAMP有2038年问题且受时区影响，能选DATETIME就不要选TIMESTAMP。这个坑等你的系统活到2038年就会感谢我。

JSON类型是MySQL 5.7引入的，适合存不固定结构的配置、扩展字段。但别滥用，JSON字段不能走索引（生成列可以，后面讲），查询和更新性能都差，能用独立字段就不要用JSON。

2.2 约束机制：数据库为你兜底的“最后一根保险丝”

约束是数据库保证数据质量的核心机制，有主键约束、非空约束、唯一约束、默认值约束、外键约束。

主键约束（PRIMARY KEY）要求非空唯一，是每行数据的身份证。除了业务主键，我强烈建议每张表再加一个自增主键id，和业务解耦，ORM都好做。

唯一约束（UNIQUE）保证某个字段的值不重复，比如dept表的name字段加了UNIQUE，你再插入同名部门直接报错。要注意唯一约束对NULL不生效——NULL可以重复多次。如果业务上不允许重复，还得配合NOT NULL。

外键约束（FOREIGN KEY）是保证引用完整性的，例子中emp.dept_id引用dept.id。但生产环境的高并发写入场景，我反而见过不少团队主动放弃物理外键，只在应用层做逻辑约束。核心原因是外键会让每次插入、更新、删除都触发额外的完整性检查，在高频写入下成为性能瓶颈。单机和数据量小的时候，外键利大于弊；分布式或高频写入下，尽量不用外键。

默认值约束上，created_at之类的审计字段，直接DEFAULT CURRENT_TIMESTAMP。这种设计让插入方不用管时间，数据库自己搞定。

2.3 ALTER TABLE操作与Online DDL注意事项

改表结构是高风险操作，因为在5.7及之前的版本中，很多ALTER操作会锁表。业务高峰期你ALTER一张大表，可能直接卡死线上业务。8.0版本支持了INSTANT算法（只修改元数据）和INPLACE算法（允许并发DML），但也不是所有操作都能走这两种。

常用的修改语句：

-- 增加字段 ALTER TABLE emp ADD COLUMN phone VARCHAR(20) DEFAULT NULL COMMENT '手机号'; -- 修改字段类型或默认值 ALTER TABLE emp MODIFY COLUMN phone VARCHAR(30) DEFAULT '' COMMENT '手机号'; -- 重命名字段 ALTER TABLE emp CHANGE COLUMN phone mobile VARCHAR(30) DEFAULT '' COMMENT '手机号'; -- 删除字段（慎用，数据不可恢复） ALTER TABLE emp DROP COLUMN mobile; -- 添加索引 ALTER TABLE emp ADD INDEX idx_dept (dept_id); -- 删除索引 ALTER TABLE emp DROP INDEX idx_dept; -- 修改表名 ALTER TABLE dept RENAME TO department; -- 修改自增起点 ALTER TABLE emp AUTO_INCREMENT = 1000;

这里有几个重点经验：

第一，MODIFY和CHANGE很容易搞混。MODIFY只能改类型和默认值，不能改字段名；CHANGE既能改名也能改类型，但CHANGE必须把原字段名和新字段名都写出来，少写一个就报错。

第二，改表结构尽量放在业务低峰期。如果你用的是5.7，添加列可能会锁表，建议先查看是否支持ALGORITHM=INPLACE：

ALTER TABLE emp ADD COLUMN phone VARCHAR(20), ALGORITHM=INPLACE, LOCK=NONE;

如果执行报错，说明当前操作不支持在线DDL，那就得谨慎安排了。

第三，批量加字段建议合并成一条ALTER语句，而不是一行一行执行。一条ALTER加3个字段，和3条ALTER各加1个字段，后者触发3次全表重建，这时间差距可以到数倍。

第四，删除字段和删除表一样，是没有确认机制、没有回收站的。生产库删字段前一定要先备份或确认清楚，我见过有同事想在测试库删字段，结果库连错了，几百万行数据说没就没。

2.4 实战：给员工表增加用户画像能力

基于模拟项目X的某电商平台的真实需求，我演示一个完整的表结构演进过程。

运营组提了个需求：需要给员工表加“技能标签”、“紧急联系人”、“入职渠道”三个信息。技能标签可能有多个，通常设计单独的子表。但在这个简化场景里，我们就用JSON字段存：

ALTER TABLE emp ADD COLUMN tags JSON DEFAULT NULL COMMENT '技能标签,如["Java","MySQL"]', ADD COLUMN emergency_contact VARCHAR(50) DEFAULT NULL COMMENT '紧急联系人', ADD COLUMN hire_channel VARCHAR(20) DEFAULT '内推' COMMENT '入职渠道';

记住把三个ADD合并成一条语句，一次执行。执行完验证一下：

SHOW CREATE TABLE emp\G

这个\G结尾是MySQL命令行的一个好东西，让输出结果竖着展示，字段多的时候比横着清晰得多。我几乎每次排查表结构都用，比DESC给的信息全。

接着更新数据：

UPDATE emp SET tags = JSON_ARRAY('Java', 'MySQL', 'Redis'), emergency_contact = '张三妻子', hire_channel = '猎头推荐' WHERE name = '张三';

查询JSON字段里的内容：

SELECT name, JSON_EXTRACT(tags, '$[0]') AS first_tag FROM emp WHERE id = 1;

注意JSON_EXTRACT取出来是带引号的字符串，你用JSON_UNQUOTE()再包一层才能去掉引号。这个细节写报表的时候特别容易遇到。

3. DML数据操作：增删改的意外与自救

3.1 INSERT的多种写法与批量插入技巧

INSERT是最常用的写操作，但写法不同，性能差别巨大。

最基础的：

INSERT INTO dept (name, location) VALUES ('财务部', 'C栋1楼');

一次插入多行，用一条VALUES带多个括号：

INSERT INTO dept (name, location) VALUES ('财务部', 'C栋1楼'), ('人事部', 'C栋2楼'), ('行政部', 'C栋3楼');

批量插入不是简单的语法糖，它能减少客户端和MySQL的交互次数。实测下来，1000行数据分1000次插入和分10次批量插入，后者速度快10倍以上。所以我所有脚本里都要求批量INSERT，尽量一次不要超过500到1000行，太大也可能导致事务日志膨胀。

INSERT INTO ... SELECT是复制表的简便方式：

CREATE TABLE emp_backup LIKE emp; INSERT INTO emp_backup SELECT * FROM emp;

CREATE TABLE ... LIKE只复制表结构，不复制数据，之后再INSERT SELECT。如果要连数据带结构一起复制，用CREATE TABLE emp_backup AS SELECT * FROM emp，但这种方式不会带索引和约束，两个方法的取舍看场景。

还有一个保存冲突时的“更新或忽略”功能。比如批量导入员工数据，有些行已经存在，希望存在就跳过：

INSERT IGNORE INTO emp (id, name, gender, dept_id, salary, hire_date, status) VALUES (1, '张三', 'M', 1, 25000, '2019-03-11', 1);

存在就忽略，不存在就插入。但如果冲突时想更新某些字段，用ON DUPLICATE KEY UPDATE：

INSERT INTO emp (id, name, salary) VALUES (1, '张三', 26000) ON DUPLICATE KEY UPDATE salary = VALUES(salary);

这个语句在需要“有则更新，无则插入”的场景下极其好用，比如每日汇总数据的落库。8.0.20版本之后VALUES()函数被标记为废弃，推荐用别名写法：

INSERT INTO emp (id, name, salary) VALUES (1, '张三', 26000) AS new ON DUPLICATE KEY UPDATE salary = new.salary;

3.2 UPDATE的致命雷区：没写WHERE就是删库跑路

现在讲一个我在生产环境亲眼见过的惨痛教训。某同事要给某个员工调薪，SQL写成了：

UPDATE emp SET salary = 30000;

少了一个WHERE id = ...，结果全公司所有人工资都变成了3万块。这种事故在MySQL里几乎没有挽回余地。

预防手段有几条：

第一条，写UPDATE前先写SELECT确认范围：

-- 先查一下到底影响哪些行 SELECT id, name, salary FROM emp WHERE id = 5; -- 确认无误后再执行UPDATE，条件保持不变 UPDATE emp SET salary = 30000 WHERE id = 5;

第二条，在MySQL客户端开启自动提交关闭，配合显式事务：

SET AUTOCOMMIT = 0; START TRANSACTION; UPDATE emp SET salary = 30000 WHERE id = 5; -- 检查影响行数和数据，确认正确再提交 COMMIT; -- 如果不正确就回滚 ROLLBACK;

第三条，安全模式。MySQL客户端默认有一个--safe-updates模式（也写作--i-am-a-dummy），开启后不带WHERE条件的UPDATE和DELETE会直接拒绝执行，除非带LIMIT：

mysql -u root -p --safe-updates

我建议新手长期开启这个模式。虽然有时确实会误伤一些正常的全表操作，但和“误更新全部数据”的代价相比，这点不便完全可以接受。

UPDATE的另一个实用经验是“联表更新”。比如公司决定：技术部所有员工工资涨10%：

UPDATE emp e JOIN dept d ON e.dept_id = d.id SET e.salary = e.salary * 1.1 WHERE d.name = '技术部';

这种写法比子查询UPDATE更直观高效，但它依然是高危操作，先SELECT验证JOIN结果再执行是铁律：

SELECT e.id, e.name, e.salary, d.name AS dept_name FROM emp e JOIN dept d ON e.dept_id = d.id WHERE d.name = '技术部';

3.3 DELETE vs TRUNCATE vs DROP：三个“毁灭者”的区别

删数据有三种级别，搞错一次就是事故。

DELETE FROM删除数据但保留表结构，可以加WHERE选行，而且走事务可以回滚。DELETE后表的自增ID不会重置——如果你删掉id=7的行，再插入时新行是id=8，这常让新手困惑。

TRUNCATE TABLE清空整张表，不能加WHERE，DDL操作、不完全走事务（MySQL对TRUNCATE的隐式提交会导致无法回滚），自增ID会重置为1。它的速度比DELETE快很多，因为它直接释放数据页而不是逐行删除。日常测试环境要清空表时，用TRUNCATE。

DROP TABLE直接删除整张表包括结构，数据、索引、约束全部消失，空间也会释放。一旦执行，能救回来的唯一方式就是备份。

经验：生产库禁止直接DROP。我处理过不止一次“误删表后靠之前定时备份抢回数据”的案例，恢复时间少则2小时多则一个通宵。所以运维规范里我永远写着一句话：删表之前，必须有最近的物理备份和binlog，能恢复到删除前的时间点。

安全删除大批量的数据，比如要删2020年以前离职员工的数据，不要一次性DELETE百万行，那样会产生巨量binlog和锁，拖垮主库甚至导致主从延迟。要分批删除：

DELETE FROM emp WHERE hire_date < '2020-01-01' ORDER BY id LIMIT 1000;

配合循环执行，每次删除1000行并停止几秒钟，直到影响行数为0。这个方法我把一个千万级表的清理流程从数小时降到了十几分钟，而且全程不影响线上读写。

如果要删除一张超大表里的部分数据，删除比例很高（比如超过一半），TRUNCATE重建表可能反而更优。先把需要保留的数据INSERT到新表，改名切换，整个过程比DELETE快得多。

3.4 实战：员工调薪与离职处理的完整流程

完整的DML流程应该长这样。先开启事务，然后执行操作，验证后提交。

-- 场景：张三从技术部调往产品部，工资调整为20000 START TRANSACTION; UPDATE emp SET dept_id = 2, salary = 20000 WHERE name = '张三'; -- 验证 SELECT id, name, dept_id, salary FROM emp WHERE name = '张三'; COMMIT;

如果发现张三根本不在技术部，或者调整的数据不对，执行：

ROLLBACK;

RALLBACK之后整个事务里的所有修改全部撤销。这个“先开启事务，确认再提交”的习惯，是我今天最想让你记住的一个操作习惯。

4. DQL查询：SQL能力的试金石

4.1 SELECT基础与WHERE过滤的细节

查询是所有SQL操作里最核心的。基础语法：

SELECT column1, column2 FROM table WHERE condition;

先踩一个最常见的坑：字段名和关键字冲突。name不是关键字，但像order、group、desc这些就很容易撞车。比如一张订单表，字段名叫order，每次查询都要反引号：

SELECT `order`, amount FROM orders WHERE `order` = 'NO123';

这个反引号问题在Workbench、Navicat里可能被工具自动处理了，但命令行下手写SQL，漏了反引号就报语法错误。

WHERE条件中的逻辑运算，需要注意优先级：AND大于OR。比如要查技术部或产品部里工资大于2万的员工：

-- 错误写法 SELECT * FROM emp WHERE dept_id = 1 OR dept_id = 2 AND salary > 20000; -- 会被解析成 dept_id = 1 OR (dept_id = 2 AND salary > 20000) -- 正确写法 SELECT * FROM emp WHERE (dept_id = 1 OR dept_id = 2) AND salary > 20000;

这个问题我面试时问过很多候选人，能答出AND优先级高于OR的不到一半。SQL里这个坑特别隐蔽，因为结果看起来“好像也没什么不对”，但数据一多就出问题了。

IN和NOT IN的使用，要特别注意NULL。查不在1、2部门的员工：

SELECT * FROM emp WHERE dept_id NOT IN (1, 2);

如果dept_id这一列里有NULL值，NOT IN的结果会让你怀疑人生——因为NULL NOT IN (...)的结果是NULL而不是TRUE，所以那一行既不会被选出来，也不会报错。这也是为什么很多老手宁可写成：

SELECT * FROM emp WHERE (dept_id != 1 AND dept_id != 2) OR dept_id IS NULL;

LIKE模糊查询，前缀通配符会放弃索引。WHERE name LIKE '%张%'无法走索引，但WHERE name LIKE '张%'可以。业务必须支持模糊搜索时，考虑用全文索引或者引入搜索组件，而不是硬扛。

4.2 ORDER BY和LIMIT分页的陷阱

排序和分页看起来没技术含量，实际坑也不少。

SELECT * FROM emp ORDER BY salary DESC LIMIT 10;

第一个坑是排序的稳定性。MySQL 5.7及以下，如果不加二级排序，salary相同行的顺序是不确定的。比如一个榜单按工资取前10，如果第10和第11工资一样，下次查询可能换人。解决方式：

SELECT * FROM emp ORDER BY salary DESC, id ASC LIMIT 10;

第二个坑是分页越深越慢。经典的分页语句：

SELECT * FROM emp ORDER BY id LIMIT 100000, 20;

MySQL要先把前100020行查出来，再丢掉前100000行。这个OFFSET越大越慢，是性能杀手。优化方式是把“翻页”改成“基于游标”的查询，页面记住上一页最后一条的ID：

SELECT * FROM emp WHERE id > 100000 ORDER BY id LIMIT 20;

如果有WHERE条件，可以改成：

SELECT * FROM emp WHERE dept_id = 1 AND id > 100000 ORDER BY id LIMIT 20;

这种写法能用上索引，翻到百万页也不卡。

4.3 聚合函数与GROUP BY的分组统计

聚合函数COUNT、SUM、AVG、MAX、MIN配合GROUP BY，是统计报表的基础。

统计每个部门的人数和平均工资：

SELECT d.name AS 部门, COUNT(e.id) AS 人数, ROUND(AVG(e.salary), 2) AS 平均工资, MAX(e.salary) AS 最高工资 FROM emp e JOIN dept d ON e.dept_id = d.id WHERE e.status = 1 GROUP BY d.id, d.name;

COUNT()和COUNT(某个字段)的巨大差异，新手经常踩。COUNT()统计所有行，包括NULL；COUNT(字段)只统计该字段非NULL的行数。例如统计有手机号的员工数量，应该是COUNT(phone)而不是COUNT(*)，二者的数字完全不一样。

AVG忽略NULL值。如果一个部门有5个人工资是NULL和5个人是20000，AVG返回的是20000而不是10000。

GROUP BY的分组字段和SELECT字段，这里有个“隐性问题”。MySQL 5.7默认开着的ONLY_FULL_GROUP_BY模式会强制SELECT后的非聚合字段都要出现在GROUP BY中，否则报错。这是个保护机制，别关。比如：

-- 报错：dept_id未出现在GROUP BY中 SELECT dept_id, name, COUNT(*) FROM emp GROUP BY dept_id;

如果非要取出“每个部门工资最高的人的名字”，就不能用简单的GROUP BY，要用子查询或窗口函数。用窗口函数最优雅，后面讲。

HAVING和WHERE的区别是另一个高频问点。WHERE先过滤行再分组，HAVING先分组再过滤组。统计平均工资大于15000的部门：

SELECT dept_id, ROUND(AVG(salary), 2) AS avg_salary FROM emp WHERE status = 1 GROUP BY dept_id HAVING AVG(salary) > 15000;

这里WHERE status = 1先过滤掉离职员工，再统计平均工资，逻辑完全正确。如果把status条件放在HAVING里，虽然结果可能一样，但语义就错了，而且性能也可能更差。

4.4 JOIN连接查询：内连接、左连接与重复数据

多表查询是SQL能力的分水岭。基于部门表和员工表，最常用的连接方式有几种。

内连接INNER JOIN，只返回两边都匹配的行。查询所有在职员工及其部门信息：

SELECT e.name, e.salary, d.name AS dept_name FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.status = 1;

左连接LEFT JOIN，返回左表全部行，右表匹配不到就填NULL。查询所有部门及其员工，没人的部门也要显示：

SELECT d.name AS dept_name, e.name AS emp_name FROM dept d LEFT JOIN emp e ON d.id = e.dept_id;

这个查询会显示所有部门，技术部、产品部、运营部都在，即使某个部门没有员工，右侧也显示NULL。

左连接最常见的坑是“一对多导致结果翻倍”。如果你join的右表有两条匹配记录，左表那一行就会重复出现两次。比如部门表是1，员工表在该部门有7个人，LEFT JOIN之后这行部门信息会出现7次。排查方法：连表查询后检查COUNT(*)是否和左表原行数一致，不一致就说明出现了重复。

连接条件的写法上，ON和WHERE的位置很关键。在LEFT JOIN中，右表的过滤条件如果写在WHERE里，会破坏左连接语义。举个例子：

-- 错误：因为WHERE过滤在JOIN之后执行，左连接变成内连接 SELECT d.name AS dept_name, e.name AS emp_name FROM dept d LEFT JOIN emp e ON d.id = e.dept_id WHERE e.salary > 15000; -- 正确：把过滤条件放在ON里 SELECT d.name AS dept_name, e.name AS emp_name FROM dept d LEFT JOIN emp e ON d.id = e.dept_id AND e.salary > 15000;

第一条查询会把没员工的部门也过滤掉，因为它要求右表e.salary>15000，NULL>15000结果是NULL，为假。查出来的结果和INNER JOIN没区别，很多人刚开始都踩过这个坑。这个点理解了，JOIN就掌握了一半。

JOIN的底层执行逻辑，MySQL通常会选择驱动表和小表作为外表，按索引去内表查找。尽量让WHERE条件走索引、连接字段有索引，是优化JOIN的核心思路。另外能用INNER JOIN查出来的数据，不要为了“看起来全”而用LEFT JOIN，后者执行代价更高。

4.5 子查询与EXISTS的取舍

子查询就是嵌套在另一个查询里的SELECT。查工资高于全公司平均值员工：

SELECT name, salary FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);

查有员工的部门：

SELECT name FROM dept d WHERE EXISTS (SELECT 1 FROM emp e WHERE e.dept_id = d.id);

EXISTS和IN的选择讲究。子查询的结果集很大时，用EXISTS，因为它不需要把子查询结果全部查出来物化，只需要判断是否存在。

查没有员工的部门，用NOT EXISTS：

SELECT name FROM dept d WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.dept_id = d.id);

比起NOT IN，NOT EXISTS不会踩NULL的坑，更安全。

子查询还有一个性能坑：MySQL 8.0之前的版本对“相关性子查询”优化得不好。比如给每个部门查最高薪员工，早期的写法：

SELECT e1.* FROM emp e1 WHERE e1.salary = (SELECT MAX(e2.salary) FROM emp e2 WHERE e2.dept_id = e1.dept_id);

这个在数据量大时极慢，因为每行都要执行一次子查询。8.0里的窗口函数或MySQL 5.7的派生表联合JOIN都可以优化。优化的思路是尽量把子查询改成JOIN。下面这个写法如果用窗口函数会更干净，见后续。

4.6 实战：经典Top N问题与部门统计报表

Top N问题（每个部门工资最高的前2名）是SQL面试必考题，也是实际开发高频场景。MySQL 8.0最优雅的解法是窗口函数：

SELECT name, dept_id, salary, rn FROM ( SELECT name, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM emp WHERE status = 1 ) t WHERE rn <= 2;

ROW_NUMBER会按部门分组（PARTITION BY dept_id），每个组内按工资降序编号，然后外层过滤前2名。

MySQL 5.7没有窗口函数，只能用用户变量或JOIN自连接来实现。比如每部门工资最高的人：

SELECT e.name, e.dept_id, e.salary FROM emp e LEFT JOIN emp e2 ON e.dept_id = e2.dept_id AND e.salary < e2.salary WHERE e.status = 1 AND e2.id IS NULL;

思路是找出“没有别人工资比我高”的员工。这个写法能用索引的时候性能也不错，但可读性差。能用8.0就用8.0的窗口函数，简单明了，这是版本升级带来的最大红利之一。

再做一个综合报表：每个部门的员工人数、平均工资、最高工资、工资最高的员工姓名。

SELECT d.name AS dept_name, COUNT(e.id) AS emp_count, ROUND(AVG(e.salary), 2) AS avg_salary, MAX(e.salary) AS max_salary, SUBSTRING_INDEX(GROUP_CONCAT(e.name ORDER BY e.salary DESC), ',', 1) AS top_name FROM dept d LEFT JOIN emp e ON d.id = e.dept_id GROUP BY d.id, d.name;

这里用了GROUP_CONCAT，把部门内员工按工资降序拼成逗号分隔的字符串，再用SUBSTRING_INDEX取第一个，就是最高工资的员工名。这是没有窗口函数时常用的取巧方案，但GROUP_CONCAT默认长度限制是1024字节，人数多了会截断，需要先SET SESSION group_concat_max_len = 1000000;。能上窗口函数就优先窗口函数，这个方式只作为5.7的备选。

5. 进阶能力：索引、事务、视图与窗口函数

5.1 索引原理与最左前缀法则

索引是MySQL高性能的基石。一句话解释索引：就像书的目录，没有目录你要翻整本书，有目录直接翻到对应页码。

MySQL默认的InnoDB引擎使用B+树索引。为什么用B+树？因为树矮、扇出高、范围查询友好，一次磁盘IO能读出很多索引项。一个3层B+树能存上千万条记录，所以大部分查询只需要三次磁盘IO就能定位到叶子节点。

索引的分类，按功能分有普通索引、唯一索引、主键索引、全文索引；按结构分有聚簇索引和二级索引。

主键索引是聚簇索引，表数据物理地按主键顺序排列，所以InnoDB表必须要有主键。二级索引（也叫普通索引）的叶子节点存的是主键值，而不是数据行指针，所以通过二级索引查数据，需要先找到主键，再回聚簇索引二次查找——这叫“回表”。

如何建索引：

ALTER TABLE emp ADD INDEX idx_dept_id (dept_id); ALTER TABLE emp ADD UNIQUE INDEX uk_name (name); ALTER TABLE emp ADD INDEX idx_dept_salary (dept_id, salary);

复合索引最左前缀法则是理解索引的重中之重。复合索引(a, b, c)，查询条件只有在包含a时才能用到这个索引；跳过a直接用b或c，索引用不上。CREATE INDEX idx_dept_salary (dept_id, salary)后，WHERE dept_id = 1 ORDER BY salary DESC能走索引；如果只写WHERE salary > 10000，则走不了。

几个真实的索引设计经验：

第一，区分度高、查询频繁的字段才适合加索引。性别这种字段区分度低，加索引几乎没用，甚至会因为回表让查询更慢。

第二，避免在索引列上做函数运算，否则索引会失效。这可能是索引失效最常见的原因：

-- 无法走索引 WHERE DATE(hire_date) = '2019-03-11'; -- 可以走索引（范围扫） WHERE hire_date >= '2019-03-11 00:00:00' AND hire_date < '2019-03-12 00:00:00';

第三，隐式类型转换也会导致索引失效。字段是VARCHAR，你用数字比较，MySQL会对字段做类型转换，索引就失效了：

-- phone字段是varchar类型，下面写法索引失效 WHERE phone = 13800138000; -- 正确 WHERE phone = '13800138000';

第四，EXPLAIN是排查索引是否生效的唯一标准。执行EXPLAIN SELECT ...，看key列有没有用上索引、type列是不是range或ref而不是ALL。type列出现ALL就是全表扫描，这是优化的头号目标。

第五，索引不是越多越好。每次写入都要维护索引，索引多了写入变慢、磁盘占用变大。一张表建议不超过5个索引，而且要结合业务真正高频的查询来设计。宁缺毋滥，为不存在的查询加索引是浪费。

5.2 事务ACID与隔离级别的取舍

事务是数据库保证并发安全的核心。一个事务要么全部成功提交，要么全部失败回滚，不存在中间状态。转账场景是经典例子：A扣钱、B加钱，两个操作必须原子完成。

事务有四大特性ACID： 原子性（Atomicity）：操作要么全做，要么全不做。由undo log保证。 一致性（Consistency）：事务前后数据符合业务规则。由其他三个特性共同保证。 隔离性（Isolation）：多个事务互不干扰。由锁和MVCC保证。 持久性（Durability）：事务提交后数据不丢失。由redo log保证。

事务的语法：

START TRANSACTION; UPDATE account SET balance = balance - 1000 WHERE id = 1; UPDATE account SET balance = balance + 1000 WHERE id = 2; COMMIT; -- 任何一步出错，就ROLLBACK

隔离级别有四种，从宽松到严格： 读未提交（READ UNCOMMITTED）：能读到别人没提交的数据，存在脏读。基本不用。 读已提交（READ COMMITTED）：只能读到已提交的数据。解决了脏读，但存在不可重复读。Oracle默认是这个。 可重复读（REPEATABLE READ）：同一事务内多次读结果一致。解决了不可重复读。MySQL默认是这个。 串行化（SERIALIZABLE）：所有事务串行执行，最安全但性能最差。

MySQL默认的REPEATABLE READ配合MVCC，在实际业务中解决了绝大多数并发问题。不过也有例外，比如需要监控统计类场景，希望读到最新已提交数据，可以临时把隔离级别调为READ COMMITTED：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

注意隔离级别设置对当前会话生效，不影响其他连接。

事务使用经验：事务要短、要快，不要在事务里做耗时操作比如外部API调用、复杂计算、大批量循环。事务持锁的时间越短，死锁和阻塞概率越低。另一个经验是明确事务边界，不要在一个事务里混入无关的查询操作。很多新手把整个接口的所有SQL都包在一个事务里，表面看“更安全”，实际把数据库拖得很惨。

死锁问题，两个事务互相持有对方需要的锁。MySQL会自动检测死锁并回滚其中一个。遇到死锁错误不用慌，写代码时捕获异常并重试即可。降低死锁的办法：让所有事务按相同顺序访问表，比如先更新A再更新B，不要有的先A后B、有的先B后A。

5.3 视图与存储过程简谈

视图是一种虚拟表，本质是保存了一条SELECT语句，不存数据。每次查询视图，MySQL都会执行底层的SELECT。

创建视图：

CREATE VIEW v_emp_dept AS SELECT e.id, e.name, e.gender, e.salary, d.name AS dept_name FROM emp e JOIN dept d ON e.dept_id = d.id WHERE e.status = 1;

之后就能像查表一样查视图：

SELECT * FROM v_emp_dept WHERE dept_name = '技术部';

视图的价值是把复杂查询封装起来，让上层业务代码简洁统一。缺点也很明显：MySQL的视图性能并不总对底层SQL做很好的改写优化，多层视图嵌套可能导致非常差的执行计划。经验是：简单场景用视图没问题，复杂报表不要依赖视图，直接在业务层写完整SQL更可控。

存储过程，把一段SQL逻辑存到数据库里，可以传参、有条件判断、循环。但我个人对存储过程持保守态度：逻辑放在数据库层，不好版本控制、不好调试、不好测试。除非是那种对性能要求极高、需要减少网络往返的固定逻辑，否则我更倾向在应用层实现业务逻辑。存储过程的编写也容易踩坑，比如MySQL的存储过程没有原生的“日志”机制，排查问题靠SELECT调试，体验很差。

5.4 窗口函数：现代SQL的宝藏

MySQL 8.0支持的窗口函数，是处理“分组内排名”、“分组内累计”等问题的利器，语法层面能大大简化SQL。

窗口函数的通用结构：

SELECT 列, 聚合函数或排名函数 OVER (PARTITION BY 分组列 ORDER BY 排序列 [ROWS 窗口范围]) FROM 表;

常用的窗口函数有： ROW_NUMBER()：分组内连续编号，1,2,3，无并列。 RANK()：有并列但会跳号，1,1,3。 DENSE_RANK()：有并列但不跳号，1,1,2。 SUM/AVG/COUNT + OVER：分组累计值。

对比排名函数的差异：

SELECT name, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr FROM emp;

如果两个人工资一样高，ROW_NUMBER会随机分1和2；RANK会让两人都排1，下一个是3；DENSE_RANK让两人都排1，下一个是2。榜单场景要并列排名用RANK或DENSE_RANK，不要用ROW_NUMBER。

窗口函数配合聚合可以处理“计算累计占比”：

SELECT dept_id, salary, SUM(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) AS dept_salary_cum FROM emp;

这个按入职日期累计部门工资总额的查询，在5.7里要用自连接加GROUP BY，写法又长又慢。窗口函数让SQL的表达能力上升了一个档次，带来体验提升非常明显。

6. 避坑指南与问题排查实录

6.1 常见问题速查表

把我在项目里遇到的高频问题整理成一张速查表，每个都踩过或帮别人排查过，希望你能绕开：

6.2 字符集与乱码的彻底排查

乱码问题几乎每个用MySQL写过中文的人都会遇到。先理清一套排查思路：

第一步，看库表字段的字符集：

SHOW CREATE TABLE emp\G SHOW FULL COLUMNS FROM emp;

第二步，看当前会话的字符集：

SHOW VARIABLES LIKE '%character%';

第三步，看客户端工具的连接设置。连接字符集、库字符集、表字符集、字段字符集，必须全部是utf8mb4。命令行连MySQL时，如果忘记指定，客户端默认可能用本地系统的latin1，写入中文就直接变问号，插入前先跑一句：

SET NAMES utf8mb4;

这句话同时设置客户端、连接层和返回结果的字符集，是命令行操作时最常用的“保命符”。

另外要说明的是，如果一张表已经建好了，里面的中文显示乱码，不能只靠改字段字符集解决。先确认数据本身是否已经被错误编码写坏，如果是，改字符集可能越改越乱。正确做法是导数据时指定正确的字符集重导，或者用CONVERT函数处理。

6.3 慢查询排查与EXPLAIN详解

慢查询是生产环境最常见的性能问题。排查流程是先开启慢查询日志：

-- 查看是否开启 SHOW VARIABLES LIKE 'slow_query_log'; -- 开启 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1;

超过1秒的SQL会记录到日志文件里。找到慢SQL后，第一步先EXPLAIN：

EXPLAIN SELECT * FROM emp WHERE salary > 20000\G

重点看这几列：

type：const > eq_ref > ref > range > index > ALL，const最快，ALL最慢。 key：实际用到的索引。 rows：预估扫描行数，越小越好。 Extra：出现Using filesort、Using temporary说明需要额外优化。

如果SQL已经走了索引但还是很慢，优先看是不是查询次数太多，或者是回表太多。必要的时候可以调整show variables like 'optimizer_switch'，但一般不建议乱调全局参数。更靠谱的是业务层控制查询次数，减少重复请求。

如果一条SQL执行计划中出现了Using filesort，意味着MySQL需要额外的排序步骤，无法直接利用索引有序性。解决办法一般是让ORDER BY的字段和索引顺序匹配。比如索引是idx_dept_salary(dept_id, salary)，查询WHERE dept_id = 1 ORDER BY salary DESC就能利用索引直接有序输出，避免filesort。而WHERE salary > 10000 ORDER BY hire_date则大概率要filesort，因为WHERE没走索引前缀，又排了别的字段。

6.4 主从复制延迟与数据恢复意识

很多团队用主从复制做读写分离，但主从延迟是常见故障。大事务、DDL操作、无主键表、从库配置太低，都会引起延迟。

排查主从状态：

SHOW SLAVE STATUS\G

关注Seconds_Behind_Master这个值，持续增大说明延迟严重。处理方式：先找到延迟的大SQL或大事务，拆分或限速；对影响大的DDL可以放到低峰期；确保所有表都有主键，否则binlog同步到从库时每个行操作都要全表扫描定位，延迟极其夸张。

最后说说备份意识。虽然它不是SQL操作本身，但它比任何SQL技巧都重要。一定要定期做全量备份加binlog增量备份。mysqldump虽然重要，但要注意一致性问题。InnoDB表用：

mysqldump -u root -p --single-transaction --routines --triggers company_db > backup.sql

--single-transaction利用InnoDB的MVCC在备份期间拿到一致快照，不会锁业务表。恢复时用：

mysql -u root -p company_db < backup.sql

恢复数据的速度和备份数据量正相关，所以几千G的大库的备份策略需要用到其他方案比如xtrabackup，但基础思路一样：永远保证“出事时能恢复”。

我个人在实际工作中最深的体会是：SQL写得好不好，七分在表结构设计，三分在写查询。很多后续的问题，比如莫名其妙的慢查询、联表查出重复数据、无法加索引的字段，追根溯源都是在建表阶段埋下的。所以别急着写各种花哨的SQL，先花时间把字段类型、字符集、约束、索引结构设计对，后面能省出十倍的事故排查时间。

最后再分享一个学习路径的私货建议：把今天这篇手册里的建表和CRUD语句，自己亲手敲一遍，每个案例都跑通，然后试着把窗口函数、EXPLAIN、事务回滚这三块再做一次刻意练习。这三块一旦掌握，你的SQL水平已经超过多数只写业务查询的开发者了。MySQL的官方文档其实写得很好，遇到疑惑直接去查对应章节，比到处搜各种残缺博客靠谱得多。