写了好几年SQLMySQL里的关键字来来去去也就那么几十个但真要系统梳理一遍很多人会发现自己一直在“会用”和“用对”之间打转。比如明明跑了 EXPLAIN 还是慢明明加了下划线还是索引失效问题往往就出在最基本的 WHERE、JOIN、LIMIT 这些关键字上。这篇文章不打算给你抄文档而是把我实际排查过的坑、验证过的写法整理出来从分类到实操、从语法到翻车案例一次性说透。如果你正在学 MySQL、写毕业设计或者工作里天天和数据打交道但总被慢查询困扰这篇文章应该能让你少走几个月的弯路。内容偏实战前置知识只需要一点 SQL 基础没有基础也能跟着代码示例跑一遍。1. 关键字分类先建立整体坐标系1.1 按功能划分的四大家族MySQL 官方把 SQL 关键字按用途分了几类我们平时写 SQL 其实也一直在用这几类。最核心的是这样四大块类别关键字代表解决什么问题DDL 数据定义CREATE、ALTER、DROP、TRUNCATE建表、改表结构、删表DML 数据操作SELECT、INSERT、UPDATE、DELETE查数、加数、改数、删数DCL 数据控制GRANT、REVOKE分配权限、回收权限TCL 事务控制BEGIN、COMMIT、ROLLBACK、SAVEPOINT保证一批操作要么全成功要么全失败这里有一个新手容易忽略的点MySQL 里还有一类“非 SQL 标准”但很常用的关键字比如 SHOW、DESC、USE、EXPLAIN它们更像运维和诊断工具。很多人会忽略 EXPLAIN 其实也是一个关键字它不是 SELECT 的一部分而是放在 SELECT 前面的执行计划查看器。后面我会专门讲。给这些关键字分类的意义不只是为了背概念而是帮你在脑子里面建立一个排错的顺序。比如收到一条 “Syntax error near order” 的报错第一反应应该是是不是字段名和关键字撞车了其次再想是不是语法顺序写错了。有了分类坐标系排查问题的时候就能少走弯路。1.2 关键字与保留字的区别MySQL 把关键字分成“保留字”和“非保留字”两种。保留字比如 SELECT、WHERE、ORDER这些词在绝大多数情况下不能直接作为表名、字段名必须用反引号包起来才能用。非保留字比如 INTEGER、SCHEMA它们虽然也是关键字但通常可以直接当标识符用不过为了可读性我建议一律别用。我踩过最典型的一个坑是业务表里有个字段叫 order没加反引号INSERT 语句直接报语法错误。当时第一反应是编码问题查了半小时最后才发现 order 是 ORDER BY 的 ORDER属于保留字。解决办法有两个一是改字段名比如 order_no二是写 SQL 时所有关键字大写字段统一小写ORder 这种混用最容易迷惑自己。如果历史表已经建了保留字字段SQL 里必须写成SELECT order FROM user_order WHERE order 0;反引号不是让你逃避问题的而是最后一道保险。新建表的时候字段名、表名一律避开这些词才是正经做法。否则每个查询都要记着加反引号迟早会漏一个。2. 查询类关键字每天都在写却最容易出错2.1 SELECT 与 FROM 的基础组合SELECT 可能是第一个接触的关键字但在逻辑执行顺序上它其实不是最先执行的。SQL 语句不是按照写的顺序执行的这一点一定要刻在脑子里。一条简单查询SELECT name, age FROM users WHERE age 18;逻辑顺序是FROM users 先把表取出来然后 WHERE age 18 过滤行最后 SELECT name, age 投影出要的列。这个过程可以类比成去食堂打饭先到窗口FROM 选桌台看看哪个菜不要WHERE 过滤再把要的菜装进盘SELECT 投影。这个执行顺序为什么重要因为很多人写子查询的时候会在 SELECT 后面直接放一个别名然后在 WHERE 里引用结果报错“Unknown column”。比如SELECT name, age * 2 AS double_age FROM users WHERE double_age 30;这样写会报错因为 WHERE 执行时 SELECT 的别名还没生成。正确写法是把 WHERE 改成对 age 的原始列过滤SELECT name, age * 2 AS double_age FROM users WHERE age 15;如果你真的很想用别名过滤那就再套一层子查询把上一步结果当成新表。这个逻辑理解透了后面 GROUP BY、HAVING 的坑你就能避开一大半。2.2 WHERE 与 HAVING 的过滤边界WHERE 和 HAVING 这两个关键字经常被搞混尤其是刚接触 GROUP BY 的时候。简记一句话WHERE 过滤的是“行”发生在分组之前HAVING 过滤的是“组”发生在分组之后。举个实际例子。订单表 orders 有字段 city 城市、amount 金额我想找出订单总金额超过 1000 的城市SELECT city, SUM(amount) AS total_amount FROM orders GROUP BY city HAVING SUM(amount) 1000;这里不能把 HAVING 换成 WHERE因为 WHERE 在 GROUP BY 之前执行那时候 SUM(amount) 这个概念还不存在。新手常见的做法是写 WHERE SUM(amount) 1000数据库直接报 “Invalid use of group function”。那 WHERE 到底怎么用如果只要统计 2024 年的数据就可以先用 WHERE 过滤年份再做分组统计这样可以减少参与分组的数据量性能更好SELECT city, SUM(amount) AS total_amount FROM orders WHERE order_year 2024 GROUP BY city HAVING SUM(amount) 1000;我给出的经验是能用 WHERE 过滤掉的尽量用 WHEREHAVING 只用来过滤聚合结果。两者职责一分开SQL 执行效率和学习难度都能大大下降。2.3 JOIN 家族的连接逻辑JOIN 应该是实际开发里最常用的关键字之一也是最容易写“表面正确、逻辑错误”的地方。MySQL 常用的是 INNER JOIN、LEFT JOIN、RIGHT JOIN以及用 UNION 模拟的 FULL JOIN。直接看例子。用户表 users 里有 id、name订单表 orders 里有 user_id、order_no。SELECT u.name, o.order_no FROM users u LEFT JOIN orders o ON u.id o.user_id;LEFT JOIN 的含义是左表 users 全保留右表 orders 匹配不上的用 NULL 填充。这个大家都会说但很多人不知道加上 WHERE 过滤右表字段后结果可能悄悄变成了 INNER JOIN。比如SELECT u.name, o.order_no FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.user_id IS NOT NULL;WHERE 把右表里为 NULL 的行过滤掉了这种情况下 NULL 填充的用户全部消失LEFT JOIN 就名存实亡了。想保留左表全部数据同时又筛选右表条件正确的做法是把条件放进 ON 里面SELECT u.name, o.order_no FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.status 1;这一点的认知差异我在代码 review 里见过很多次。记住JOIN 后的 ON 控制连接和保留哪些匹配行WHERE 控制最终输出哪些行。先连接再过滤还是连接时直接决定保留结果完全不同。RIGHT JOIN 用的相对较少因为大多数场景下可以把左右表调换一下用 LEFT JOIN 替代可读性会更好。至于 FULL JOINMySQL 不支持需要用 LEFT JOIN UNION RIGHT JOIN 去模拟实际业务中遇到不多但面试会问。2.4 GROUP BY 与聚合函数搭配注意GROUP BY 把相同的值合并成一组通常和 COUNT、SUM、AVG、MIN、MAX 这些聚合函数搭配。最典型的问题是“SELECT 里出现非聚合列但没写进 GROUP BY”。在 MySQL 5.7 以及更早版本默认设置了 ONLY_FULL_GROUP_BY 的可能不多所以很多人写下面这种 SQL 也能跑通SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id;如果 MySQL 开启了 ONLY_FULL_GROUP_BYMySQL 8.0 默认开启这个查询直接报错name 列不在 GROUP BY 子句中且不是聚合函数的一部分。为什么因为同一个 user_id 可能对应多个 name数据库不知道该取哪一条逻辑上是未定义的。MySQL 5.7 里它可能随机取一个看起来能出结果但结果是不可靠的。正确写法是两种情况选一种一是把 name 也加进 GROUP BYSELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id, name;二是用 ANY_VALUE() 告诉 MySQL 你无所谓取哪个或者干脆去掉 name。实际业务中要的是“每个用户的订单数”根本不需要 name直接去掉即可SELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id;这里还要提醒一个聚合函数细节COUNT() 和 COUNT(column) 不是一个东西。COUNT() 统计的是行数COUNT(user_id) 统计的是 user_id 非 NULL 的行数如果 user_id 有 NULL 就会被排除。统计总数用 COUNT(*)统计某一字段是否普遍填了就 COUNT(field)别混。2.5 ORDER BY 与 LIMIT 分页的坑ORDER BY 本质是排序关键字和 LIMIT 组合使用就构成了分页。简单分页写起来很容易SELECT id, name FROM users ORDER BY create_time DESC LIMIT 0, 10;LIMIT 的两种写法要说清楚LIMIT 0, 10 表示偏移量 0取 10 条LIMIT 100, 10 表示跳过 100 条取第 101 到 110 条。注意单独一个数字 LIMIT 10 等同于 LIMIT 0, 10。分页越深越慢这一点很多人都栽过尤其是大数据量场景。SQL 写出来没问题SELECT id, name FROM users ORDER BY id LIMIT 100000, 10;这条 SQL 看起来人畜无害实际上 MySQL 要多读 100010 行数据然后把前 100000 行扔掉。数据量一大这个“扔掉”的动作就可能把 CPU 和 IO 拖垮。我的优化习惯是“基于索引覆盖 延迟关联”。具体做法是先通过主键定位起始 ID再回表取数据。比如我们知道上次翻页的最后一个 ID 是 100000那下一页就可以写成SELECT id, name FROM users WHERE id 100000 ORDER BY id LIMIT 10;这样可以利用主键索引直接跳到目标位置不用扫前面 10 万行。不过这套方案只适用于按主键或唯一键排序的场景。如果你必须按 create_time 这种普通字段排序至少要给排序字段加索引并且保证排序字段有唯一性不然分页结果可能会重也可能漏数据。3. 过滤关键字细节IN、BETWEEN、LIKE、EXISTS 该怎么选3.1 IN 与 EXISTS 的选择逻辑IN 和 EXISTS 都用于子查询过滤很多人纠结区别。直接说结论在小数据量的业务系统里区分度没那么大因为优化器会做等价改写但理解原理有助于写出对复杂查询更稳的 SQL。先看 IN 的典型场景查有订单的用户。SELECT id, name FROM users WHERE id IN (SELECT user_id FROM orders WHERE status 1);EXISTS 的写法SELECT id, name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id u.id AND o.status 1);这两个写法语义上是同一个意思。区别在于执行策略上经典的教科书描述是IN 适合子查询结果集小、外层表大的场景EXISTS 适合外层表小、子查询大的场景。因为 IN 会先把子查询结果集加载到内存EXISTS 则是逐行判断存在性。但 MySQL 优化器会做半连接优化不一定按你想象的方式执行。我的建议是优先保证 SQL 可读性遇到性能瓶颈后用 EXPLAIN 看执行计划再决定要不要改写成 EXISTS。另外子查询返回的列如果可能包含 NULLIN 不会报错但结果可能出乎意料而 EXISTS 相对更安全。还有一个小技巧是IN 后面元组不要过多超过几千个值的时候性能会明显波动需要重新考虑拆批或者 JOIN。3.2 LIKE 模糊查询的索引失效问题LIKE 关键字用于模糊匹配最常见的坑是首部通配符导致索引失效。比如SELECT * FROM users WHERE name LIKE %小明%;这条 SQL 无法使用 name 字段的普通 B 树索引因为“%”在前面数据库不知道从哪个前缀开始找只能全表扫描。如果写成SELECT * FROM users WHERE name LIKE 小明%;那就有机会使用索引因为前缀是确定的“小明”可以走索引范围扫描。那实际业务逼着你做“包含”搜索怎么办数据量小无所谓直接全表扫但数据量大就别硬扛了。方案有这么几个方向一是用全文索引 MATCH ... AGAINST适合文本检索二是引入外部搜索服务但为了一个 Like 没必要三是把冗余字段拆出来比如预先拆分名字生成前缀片段。个人经验是能走前缀匹配就写前缀不行就做好慢查询预案。还有一点LIKE 的转义。默认情况下LIKE 中 “%” 表示任意多个字符“_” 表示任意一个字符。如果你要查真的含有“%”的字符需要 ESCAPESELECT * FROM logs WHERE message LIKE 100%% ESCAPE 100%;这个语法我一直建议写 SQL 的人至少知道因为日志表里经常要过滤“%”这种特殊字符。3.3 BETWEEN 的边界与日期陷阱BETWEEN 包含左右两个边界这个和很多人直觉一样。但日期范围检索时最容易出错。比如SELECT * FROM orders WHERE order_date BETWEEN 2025-01-01 AND 2025-01-31;如果 order_date 是日期时间类型datetime这条查询会漏掉 1 月 31 日 00:00:00 之后的所有数据比如 2025-01-31 12:30:00 就不会被查到。原因是 BETWEEN 的范围实际是 [2025-01-01 00:00:00, 2025-01-31 00:00:00]。正确写法是使用左闭右开区间SELECT * FROM orders WHERE order_date 2025-01-01 AND order_date 2025-02-01;这算是我最常写在 code review 注释里的一个建议。原因在于日期数据只要一旦包含时间部分边界判断就很容易出问题。统一的规范是按“天”查就用左闭右开“月”同理月初到下月初。3.4 IS NULL 与空字符串的纠葛NULL 在 SQL 里代表“未知”它不等于空字符串也不等于 0。判断 NULL 必须用 IS NULL 或 IS NOT NULL不能用 NULL因为 NULL 之间比较结果也是 NULLWHERE 条件视为假。最常见的错误是SELECT * FROM users WHERE phone NULL;结果永远查不到数据。正确写SELECT * FROM users WHERE phone IS NULL;查询“手机号没填或者为空字符串”的用户SELECT * FROM users WHERE phone IS NULL OR phone ;这里还要注意聚合函数对 NULL 的态度。COUNT(phone) 不会统计 phone 为 NULL 的行SUM(amount) 会忽略 NULL 值而不是当作 0AVG 也一样。如果你希望把 NULL 当 0 计算要配合 IFNULL 或 COALESCESELECT SUM(IFNULL(amount, 0)) FROM payments;很多业务统计不准查到最后都是 NULL 处理不当。这个看着基础却是最容易让数据对不上账的点。4. 数据操纵与事务控制关键字改数据前请三思4.1 INSERT 的三种姿势与批量插入INSERT 是写入数据的核心关键字。最简单的单行写入INSERT INTO users (name, age) VALUES (小明, 18);稍微进阶一点是多行批量插入INSERT INTO users (name, age) VALUES (小明, 18), (小红, 19), (小刚, 20);这种一条语句多 VALUES 的方式比循环执行单条 INSERT 要快很多因为减少了客户端与数据库的交互次数。在数据量几万以下时这个优化效果非常明显。还有一种 INSERT ... SELECT可以从一张表查出来直接写入另一张INSERT INTO user_backup (id, name, age) SELECT id, name, age FROM users WHERE age 18;这种用法适合做数据归档、备份、临时表填充。需要注意INSERT ... SELECT 默认对 SELECT 表加的锁在某些隔离级别下可能会扩大批量操作时尽量在业务低峰期执行。关于批量插入不要一次性往语句里塞 10 万条 VALUES很容易触达 max_allowed_packet 限制。我习惯分批每批 500 到 1000 条具体看单行宽度。如果非要在代码里大批量写入建议用事务包起来失败可以整体回滚避免写一半的数据残留。4.2 UPDATE 与 DELETE 的高危操作UPDATE 和 DELETE 是生产环境里最需要谨慎的关键字因为只要 WHERE 写错后果就是不可逆的批量损坏。最典型的就是UPDATE users SET age 30;没有 WHERE全表所有人的 age 都变成 30。DELETE FROM users 同样危险会清空全表。我给自己定的规矩是UPDATE 和 DELETE 的 WHERE 条件必须先以 SELECT 确认。也就是说先执行SELECT * FROM users WHERE id 123;确认条件能查到自己想改的那几条再加上 UPDATEUPDATE users SET age 30 WHERE id 123;如果有一次性改多行的需求用事务包住并加上 LIMIT 控制单次影响行数。MySQL 的 UPDATE 和 DELETE 支持 LIMIT这是个非常好用的保险措施DELETE FROM users WHERE age 18 ORDER BY id LIMIT 100;这个写法每次只删 100 条配合循环可以控制大表删除产生的锁时长。在删除几百万行数据的场景千万不要一条 DELETE 不带分批直接跑锁表时间长是小把主从延迟拉满是大。还有一个重要关键字 ON DELETE CASCADE很多人会在外键上用它。它有风险删除父表记录会悄悄删掉子表多条记录。生产环境我一般不用物理级联改成应用层处理免得哪天误删父表连带数据不可恢复。4.3 事务关键字COMMIT、ROLLBACK、SAVEPOINT事务是为了保证数据一致性。最经典的例子是转账A 扣 100B 加 100两个动作要么都成功要么都失败。MySQL 中InnoDB 引擎才支持事务MyISAM 不支持这是选择引擎时的一个大前提。默认情况下一条 DML 语句自动隐式提交。如果你想手动控制多条语句作为一个整体需要显式开启事务START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;如果第二条语句执行失败可以 ROLLBACKSTART TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; UPDATE account SET balance balance 100 WHERE user_id 2; ROLLBACK;COMMIT 提交后数据永久生效ROLLBACK 回滚到事务开始之前的状态。还有一种更细的控制SAVEPOINT。比如一个事务中有 3 步操作第 2 步出错但不想回滚第 1 步可以在第 1 步后设置保存点START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; SAVEPOINT sp1; UPDATE account SET balance balance 100 WHERE user_id 2; -- 这里出错 ROLLBACK TO SAVEPOINT sp1; UPDATE account SET balance balance 50 WHERE user_id 3; COMMIT;这种做法的好处是只回滚部分操作代码逻辑上更灵活。不过现实中多数场景还是整体回滚简单可靠SAVEPOINT 用的不算多。事务还有一个经典问题事务里别做太重的查询和外部接口调用。我见过同事在事务里写远程 HTTP 调用结果接口超时 30 秒数据库连接一直被占用最后连接池爆掉。短事务是铁律长事务要拆。4.4 锁相关关键字LOCK TABLES 与 SELECT ... FOR UPDATE并发写同一行数据时事务隔离是数据库自己保证的但业务上需要主动加锁的场景很常见。比如库存扣减如果两条请求同时读到库存 10然后都执行减 1最终结果可能是 9而不是 8。要避免这个问题可以用行级锁。MySQL 中常见的悲观锁写法是 SELECT ... FOR UPDATESTART TRANSACTION; SELECT stock FROM products WHERE id 1 FOR UPDATE; -- 这里拿到锁其他事务修改同一行会等待 UPDATE products SET stock stock - 1 WHERE id 1; COMMIT;FOR UPDATE 锁定的是查出来的行事务提交或回滚才释放。注意它只对事务里的查询生效如果不在事务中SELECT 的执行完自动提交锁马上释放等于没锁。还有 LOCK TABLES 和 UNLOCK TABLESLOCK TABLES products WRITE; -- 在锁定期间可以写 UNLOCK TABLES;LOCK TABLES 是锁定整张表粒度大、并发差一般只在维护或数据迁移时用业务编码里不建议直接使用。对线上业务来说行锁更加友好。这里有个很隐蔽的坑如果查询没有走索引InnoDB 可能会锁全表因为在扫描过程中它需要锁住所有判断过的行。所以 FOR UPDATE 查询的条件一定要走索引否则你以为是行锁实际把整表都锁了。5. DDL 与索引关键字表结构设计的根基5.1 CREATE、ALTER、DROP 的关键差异CREATE 用于创建数据库对象最常见的是建表CREATE TABLE users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, age INT DEFAULT 0, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;ALTER 用于修改已存在的表结构。常用场景是增加列、删除列、修改列定义ALTER TABLE users ADD COLUMN email VARCHAR(100) DEFAULT NULL AFTER name; ALTER TABLE users MODIFY COLUMN age INT UNSIGNED DEFAULT 0; ALTER TABLE users DROP COLUMN email;这里注意 CHANGE 和 MODIFY 的区别MODIFY 只能改列类型和默认值CHANGE 可以同时重命名列。ALTER TABLE users CHANGE COLUMN name username VARCHAR(50) NOT NULL;DROP 用于删除整个表DROP TABLE users;和 DELETE 不一样DROP 是 DDL执行后表结构连同数据一起没了且不能回滚。DELETE 是 DML表还在可以用事务回滚。还有一个 TRUNCATETRUNCATE TABLE users;TRUNCATE 清空数据、保留表结构性能比 DELETE 快很多但它也不是事务安全的而且会重置自增 ID。所以生产环境里清空小表用 DELETE清空大表备份数据后可以用 TRUNCATE但必须知道后果。另外ALTER TABLE 在大表上执行时会锁表或引起长时间阻塞特别是加列、改字符集。如果遇到千万级数据表要改结构建议用在线 DDL 工具或者在维护窗口操作不要直接上来 ALTER。5.2 PRIMARY KEY、UNIQUE、INDEX 的选择索引是 MySQL 性能优化中最重要的关键字组合。建表时常用 PRIMARY KEY、UNIQUE KEY、INDEX/KEYKEY 和 INDEX 是同义词MySQL 里可以混用。主键是聚簇索引InnoDB 表必须要有主键否则 MySQL 会自己找一个唯一索引当主键找不到还会生成隐藏主键对空间和性能都不好。推荐使用自增整型主键因为 B 树按顺序插入更快页分裂少。UNIQUE 是唯一约束保证字段值不重复自动创建唯一索引。适合手机号、身份证号这类业务上不允许重复的字段CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20) NOT NULL, UNIQUE KEY uk_phone (phone) );普通索引用 INDEX 创建CREATE INDEX idx_users_name ON users(name);组合索引要遵循最左前缀原则。比如建了索引 (a, b, c)CREATE INDEX idx_a_b_c ON users(a, b, c);现在这三种查询能用到索引aa,ba,b,c。但是如果直接查 b 或 c索引就用不上。最左前缀原则的原因是 B 树组合索引先按第一列排序第一列相同才按第二列排。设计组合索引时把区分度高、查询频率高的列放左边。同时要避免冗余有了 (a,b,c)再建 (a,b) 就是重复。我曾经看到有人对同一张表建了 8 个索引单条写入反而慢得不行因为每次写数据都要同步维护所有索引。5.3 FOREIGN KEY 与外键约束注意FOREIGN KEY 用于定义外键保持关联表的数据一致性。例子CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, amount DECIMAL(10,2), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) );加了外键之后插入订单时如果 user_id 在 users 表里不存在会直接报错。删除用户时如果这个用户有订单默认会拒绝删除除非指定 ON DELETE CASCADE 或 SET NULL。但是实际开发中我见过很多团队刻意不用外键。原因大致有三点一是外键约束增加每次写入时的检查开销二是在分库分表架构下外键根本无法跨库生效三是误删除的级联动作风险太大。所以很多场景里外键逻辑放在应用层自己校验。我的建议是学习阶段一定要会写外键理解它的语义项目开发里小项目可以用外键线上高并发、分布式系统则谨慎使用宁可在应用层里多写一次校验。5.4 常用类型修饰关键字NOT NULL、DEFAULT、AUTO_INCREMENTNOT NULL、DEFAULT、AUTO_INCREMENT 严格来说是列属性但在建表语句中经常会和关键字一起出现面试和实际排查时常遇到所以值得一提。NOT NULL 表示该列不允许 NULL。设计表时业务上必填的字段尽量设置为 NOT NULL 并给默认值。这样做的理由是NULL 在索引和聚合上处理比较特殊而且容易在应用层出现空指针。DEFAULT 用于指定默认值。使用 DEFAULT 要注意MySQL 8.0 开始支持表达式默认值比如时间戳默认当前时间CREATE TABLE logs ( id INT PRIMARY KEY AUTO_INCREMENT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );但请不要把 DEFAULT NULL 和 NOT NULL 对着来。一个字段如果加了 NOT NULL DEFAULT 0那么插入时不给值就自动填 0不会报错。AUTO_INCREMENT 是 MySQL 的自增关键字通常用于主键。它要求该列必须是索引列。默认起始值是 1如果想调整ALTER TABLE users AUTO_INCREMENT 1000;自增 ID 不会因为删除行而回退这是个经典现象。如果有一天你删了最大 ID 的行再插入新行时 ID 不会复用会继续往上走。这个特性和业务无关不要依赖。6. 进阶关键字与 SQL 优化实战EXPLAIN 与 WITH6.1 EXPLAIN 解读执行计划EXPLAIN 是 MySQL 用来查看 SQL 执行计划的关键字用法很简单直接在 SQL 前面加 EXPLAINEXPLAIN SELECT id, name FROM users WHERE age 18;执行计划结果里有几个关键列要知道。type 表示访问类型从好到差常见的是 const、eq_ref、ref、range、index、ALL。ALL 就是全表扫描一般需要优化到 range 或更优。possible_keys 显示可能用到的索引key 是实际用到的索引。rows 是一个估算值表示扫描行数这个数字越大SQL 越值得优化。Extra 里如果出现 Using filesort 或 Using temporary通常意味着排序或去重没走索引要小心。举一个我处理过的慢查询例子。一条 SQL 跑了 3 秒SELECT user_id, COUNT(*) FROM orders WHERE status 1 GROUP BY user_id;EXPLAIN 一看type 是 ALLrows 是 500 万Extra 里有 Using temporary。优化方式很简单给 (status, user_id) 建组合索引让 WHERE 和 GROUP BY 都能走索引执行时间直接降到 0.2 秒。我在团队里立过一条规矩任何线上慢查询日志里出现的 SQL排查的第一步就是 EXPLAIN而不是拍脑袋改业务逻辑。EXPLAIN 能告诉你 MySQL 实际是怎么执行的很多优化方向都是从这里推出来的。6.2 WITH (CTE) 与临时表的思考MySQL 8.0 开始支持 WITH 语句也就是公用表表达式 CTE。以前写复杂的嵌套子查询一层套一层可读性很差。用 WITH 可以把中间结果抽出来WITH recent_orders AS ( SELECT user_id, SUM(amount) AS total FROM orders WHERE order_date 2025-01-01 GROUP BY user_id ) SELECT * FROM recent_orders WHERE total 1000;这个写法相当于定义了一个临时的命名结果集SQL 本身的执行逻辑被拆成了可读的步骤。和直接写子查询相比CTE 的语义更清晰还能在后面多次引用避免重复代码。CTE 也能做递归查询比如查询组织架构的层级树不过递归的写法相对冷门日常维护中如果遇到记得递归深度过大时要注意性能。在 MySQL 5.7 及更早版本里没有 WITH我一般用临时表或者干脆多写子查询。如果是报表类任务临时表方案挺好把中间结果落到临时表还能加索引。但如果只是单条查询临时表的创建和删除代价反而更高。6.3 CASE WHEN 条件逻辑CASE WHEN 是 SQL 里的条件分支关键字作用类似于代码里的 if else。常见的用法是按条件分组统计。比如把成绩分为优良中差SELECT CASE WHEN score 90 THEN 优秀 WHEN score 60 THEN 及格 ELSE 不及格 END AS level, COUNT(*) AS cnt FROM student_scores GROUP BY level;这条 SQL 先把分数映射成等级再按等级统计人数。注意 GROUP BY 在这里可以引用 SELECT 里的别名 levelMySQL 允许在标题这一级这么写但 WHERE 里不行逻辑顺序决定了一切。CASE WHEN 还能做简单的行列转换。例如统计每月订单量时可以把按月汇总的结果铺成宽表这在报表里非常实用。使用 CASE WHEN 时要注意一个坑CASE 如果没有 ELSE不匹配时结果就是 NULL统计时容易被忽略。建议每条 CASE 都带上 ELSE保证逻辑完整。6.4 DISTINCT 与 GROUP BY 去重选择DISTINCT 用于去掉结果中的重复行。最简单的去重SELECT DISTINCT city FROM users;如果是对多个字段去重DISTINCT 后面跟多个列SELECT DISTINCT city, age FROM users;这里表示的是 (city, age) 的组合不重复而不是分别对 city 和 age 去重要区分清楚。统计某列不同值的数量用 COUNT(DISTINCT column)SELECT COUNT(DISTINCT user_id) FROM orders;DISTINCT 和 GROUP BY 在去重场景上功能接近。单列去重两者差不多但 GROUP BY 更灵活可以同时计算聚合值。例如查每个城市的用户数SELECT city, COUNT(*) FROM users GROUP BY city;这个需求用 DISTINCT 做不到。所以大多数去重统计我习惯直接上 GROUP BY避免在应用中再多查一次。6.5 UNION 与 UNION ALL 的区别UNION 把多个 SELECT 的结果合并成一个结果集并自动去除重复行。UNION ALL 则保留所有行不去重。看例子SELECT name FROM users_a UNION SELECT name FROM users_b;如果 users_a 和 users_b 有同名的人UNION 只会保留一次。换成 UNION ALL就会保留两次。UNION 的去重需要额外的内存和操作性能通常比 UNION ALL 差。如果业务上能接受重复或者本来结果就是互斥的直接用 UNION ALL 更省资源。结构上UNION 要求两边 SELECT 的列数一致并且对应列的数据类型兼容。MySQL 默认使用第一个 SELECT 的列名作为结果列名所以别名要写在第一个查询里。我实际使用 UNION ALL 比较多比如把不同业务线相同结构的数据汇总到一起。此时如果其中一条查询返回大量数据建议每段查询都加上明确的条件避免各自扫描全表。7. 关键字避坑速查表这一节是踩坑实录的汇总。下面这些错误基本上每个团队都会碰到几个。报错信息涉及的关键字原因与解决方案Syntax error near orderORDER / 保留字字段名撞了保留字给字段加反引号或者直接改字段名Unknown column xxx in where clauseSELECT 别名WHERE 执行在 SELECT 之前不能引用 SELECT 别名改用 HAVING 或子查询Invalid use of group functionWHERE SUMWHERE 不能使用聚合函数条件挪到 HAVINGDuplicate entry xxx for key uk_phoneUNIQUE插入的数据在唯一索引上重复先查询确认是否已存在No database selectedUSE还没指定数据库执行 USE 库名或者连接时带上库名Table doesnt existDROP / SELECT表名写错或没切库检查大小写和库名Data too long for column nameVARCHAR插入值超过列长度调整列类型或截断数据Lock wait timeout exceededFOR UPDATE / 事务事务锁等待超时检查是否有长事务持有锁Using filesort / Using temporaryORDER BY / GROUP BY排序或分组没走索引考虑加对应索引Out of range value for column数值类型插入字段超出类型范围检查数值和列类型这张表不是我坐在电脑前编出来的而是这些年排查问题的过程中沉淀下来的。每一条原因都对应一个场景排查的时候照着表格过一遍很多问题能节省半小时。还有一个高频问题不在报错信息里但在实际业务层面很要命UPDATE 和 DELETE 忘带 WHERE。这种操作没有报错只有事后对比数据才能发现所以我才反复强调带着 SELECT 先查一遍重要数据先备份这是比任何关键字技巧都重要的工作习惯。写到最后的一点经验我自己的体会是MySQL 关键字本身不难真正难的是组合使用时的逻辑顺序和性能代价。很多人写完一条 SQL能跑出结果就觉得没问题但换个数据量、换个索引性能差距可能达到百倍。关键字是死的执行计划是活的。把 EXPLAIN 当成日常工具比背一百个关键字的使用方法都管用。另外我习惯所有 SQL 关键字统一大写表名字段名统一小写每行只放一个字段复杂查询先格式化再执行。这种习惯不能让你写出更高级的语法但能让你在成百上千行的 SQL 里一眼看出问题所在。编排和规范本身也是避开关键字坑的一种方式。