今天是我系统补 MySQL 的第三天主题仍然是 SQL但和前两天已经不一样了。第一天建库建表、导数据第二天的 SQL-1 把增删改查和 where 过滤条件过了一遍到了 Day3-MySQL-SQL-2 这个部分我才真正意识到会写 SQL 和能写好 SQL 之间隔着一整条实践鸿沟。以前我写 SQL 只要能跑出结果就收工排序、去重、连表都是复制旧代码改改字段名。直到上周一个线上统计报表来回改了三次才发现自己连 ORDER BY 的 NULL 排序规则、DISTINCT 的组合逻辑、InnoDB 锁的基本行为都理解得很浅。这篇文章就是第三天的完整记录适合刚学完基础增删改查、想往查询优化和事务方向走的人也适合那些写过不少 SQL 但说不出为什么这样写的朋友。1. 排序去重看起来简单为什么一到业务里就出错1.1 ORDER BY 的隐藏规则不是你想的按顺序输出排序是每个 SQL 新手都会用的功能但业务里的坑往往藏在最基础的地方。先看最容易被忽略的 NULL 排序在 MySQL 中ORDER BY col ASC时 NULL 值默认排在最前面ORDER BY col DESC时 NULL 排在最后面。很多从 Oracle 转过来的人在这里很容易翻车因为 Oracle 的默认行为和 MySQL 正好相反。我知道这个规则是在一次数据流水核对时踩的坑某个报表要按金额降序排却没有过滤 NULL结果一长串空值全堆在顶部乍一看以为是数据重复了。多列排序的生效逻辑也需要特别注意。ORDER BY price DESC, id ASC的含义是先按 price 降序只有当 price 完全相同时才按 id 升序。换句话说如果你只写ORDER BY price DESC在 price 相同的那些行里顺序完全由 MySQL 自己决定。别觉得这无所谓分页查询时如果顺序不稳定第二页和第一页之间就可能出现重复数据或者漏掉数据。我们在取每个分类下价格最高的前十条时几乎总是会加上一个唯一字段作为最后的兜底排序比如ORDER BY category_id, price DESC, id ASC。还有一个关于中文排序的问题。默认字符集和排序规则下ORDER BY name并不是按拼音排而是按字符集对应的规则排。utf8mb4_general_ci下许多汉字排序结果看起来毫无规律。如果业务明确要求按拼音排序可以这样写ORDER BY CONVERT(name USING gbk)。这个技巧在导出 Excel 名单时特别管用否则你拿到的名单顺序会和用户预期完全不一样。1.2 DISTINCT 去重到底去的是什么我见过不少人用SELECT DISTINCT name, age时以为是只对 name 去重随意取一个 age。这是对 DISTINCT 最深的误解。DISTINCT 作用于后面所有列的组合也就是说name和age两列拼接后的值相同才算重复只去掉完全重复的行。如果你真的只想对某一列去重并取出其他字段DISTINCT 解决不了需要用 GROUP BY 或窗口函数。DISTINCT 和 GROUP BY 的核心区别在于GROUP BY 通常配合聚合函数使用会把多行折叠成一行DISTINCT 只是过滤重复行不会聚合。举个例子统计订单表中出现过多少个用户 ID可以用COUNT(DISTINCT user_id)这里 DISTINCT 是在 COUNT 内部起作用而不是写在 SELECT 后面。注意这个形式的 COUNT 不会统计 NULL 值如果 user_id 本身允许为空结果可能比实际用户数少。数据清洗时经常需要针对某一列去重保留其中一条记录。最稳妥的做法不是简单地 DELETE而是利用业务唯一键找出重复组。MySQL 8.0 之后可以直接用 ROW_NUMBER 窗口函数比如把order_no重复的记录按id正序编号保留编号为 1 的那条其余删除。这个思路本身比具体 SQL 更重要后面讲窗口函数时我会再回到这个场景。1.3 分页排序时最容易翻车的组合分页是业务系统里逃不开的操作LIMIT 1000, 20这种写法在小数据量时没什么感觉一旦表里有了几十万行翻到后面就会明显变慢。原因是 MySQL 不是直接从第 1001 行开始读它会把前 1000 行都扫描出来丢弃后再取后面 20 行越往后扫描越多。我处理深分页的常用方案有两个。第一个是延迟关联先只查主键再回原表关联出完整行SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 100000, 20 ) t ON o.id t.id;子查询里只读主键列可以走覆盖索引扫描代价比取所有列小很多。第二个方案是游标分页WHERE id ? ORDER BY id LIMIT 20每次把上一页的最大 id 作为条件传过来。这种方案的前提是排序字段必须具备唯一性如果你用create_time排序而同一秒内有大量记录还是会出现漏数据。所以实际项目中我一般会用id或者(create_time, id)复合排序来保证严格唯一。排序和分页组合时的另一个坑是ORDER BY 后面的字段如果没建索引MySQL 会在内存或磁盘里做 filesort数据量大时性能惨不忍睹。这个我会在慢 SQL 优化那节展开但你现在可以记住一个结论分页越深排序字段有没有索引的差异越明显。2. 窗口函数救了我半天时间这三类场景直接套用2.1 窗口函数和 GROUP BY 的本质差异窗口函数是 MySQL 8.0 引入的一类函数解决了一个 GROUP BY 很难优雅处理的需求既想看到聚合结果又不想丢失明细行。比如统计每个用户当前的订单总金额同时又要保留每一张订单自己的编号用 GROUP BY 只能得到每个用户一行汇总而窗口函数可以做到每行旁边都挂着一个用户汇总值。SELECT order_id, user_id, amount, SUM(amount) OVER(PARTITION BY user_id) AS user_total_amount FROM orders;执行结果里每一行都有 user_id 对应的累计金额但你依然能看到每张订单的原始信息。这类写法在做列表页附带汇总字段时非常有用不需要再单独跑一条聚合 SQL 再用 HashMap 去匹配。需要留意的是窗口函数的执行位置。它在 WHERE 和 GROUP BY 之后才会计算所以你不能直接在 WHERE 里写SUM(amount) OVER(...) 100来过滤。正确的做法是包一层子查询在外层再过滤SELECT * FROM ( SELECT order_id, user_id, amount, SUM(amount) OVER(PARTITION BY user_id) AS user_total_amount FROM orders ) t WHERE t.user_total_amount 100;关于执行顺序我建议你只记结果窗口函数不能在 WHERE 阶段生效。这个限制在实际开发中几乎每周都会遇到知道原因之后就不会再纠结了。2.2 排名统计ROW_NUMBER、RANK、DENSE_RANK 怎么选窗口函数里最容易混淆的是三个排名函数。我用一张成绩表来演示就特别清楚函数分数序列排名结果适用场景ROW_NUMBER()90, 90, 801, 2, 3取前 N 名每条记录有唯一顺序RANK()90, 90, 801, 1, 3体育比赛式排名允许并列但会跳号DENSE_RANK()90, 90, 801, 1, 2并列名次不跳号适合发奖等级实际业务里最常见的用法是每个分组取最新一条。比如每个用户取最近一笔订单可以这样写SELECT * FROM ( SELECT order_id, user_id, create_time, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM orders ) t WHERE t.rn 1;这个需求在没有窗口函数的 MySQL 5.7 里要写用户变量或者自连接代码又长又容易出错。如果你还在 5.7 上我的建议是尽快规划升级哪怕只是到 8.0SQL 的书写体验都会上一个台阶。当然如果暂时不能升级也可以用临时表配合变量实现但可读性和维护成本都高很多。2.3 移动平均与累计值的业务用法移动平均在数据看板和财务对账里特别常用。比如计算每个日期最近三天的平均订单金额SELECT order_date, amount, AVG(amount) OVER( ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg FROM daily_sales;这里ROWS BETWEEN 2 PRECEDING AND CURRENT ROW表示从当前行往前数两行一共三行取平均。如果不写这个窗口范围默认是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW在有重复排序列的情况下计算范围可能会把并列的所有行都包含进去结果就和你想的不一样了。所以涉及移动窗口计算时我习惯明确写出 ROWS 而不是靠记忆猜默认行为。累计值同理比如看每天累计销售额SELECT order_date, amount, SUM(amount) OVER(ORDER BY order_date) AS cumulative_amount FROM daily_sales;还可以用PARTITION BY category让累计值按分类隔离。把这类窗口函数和 GROUP BY 的汇总结果结合可以做出很多以前要写好几条 SQL 才能完成的报表逻辑。我第三天的核心收获之一就在这很多看起来复杂的统计需求其实就是一个窗口函数的事。3. 事务、锁和存储过程我把它们串成了一条线3.1 ACID 和隔离级别事务的四个档位事务是 MySQL 里保证数据不被写坏的机制。银行转账是最经典的类比A 扣 100B 加 100两步必须同时成功或同时失败否则账就对不上。ACID 是四个特性的缩写原子性、一致性、隔离性、持久性。原子性解决要么全做要么全不做一致性保证约束和业务规则不被破坏隔离性让多个事务互不干扰持久性确保提交后数据不丢。真正影响日常开发的是隔离级别。MySQL 默认隔离级别是 REPEATABLE READ也就是可重复读。四个级别从低到高分别是读未提交、读已提交、可重复读、串行化。每个级别解决的脏读、不可重复读、幻读问题可以用一张表看明白隔离级别脏读不可重复读幻读READ UNCOMMITTED可能可能可能READ COMMITTED不会可能可能REPEATABLE READ默认不会不会可能InnoDB 用锁解决SERIALIZABLE不会不会不会你可以用SELECT transaction_isolation;查看当前隔离级别8.0 之前叫tx_isolation。临时改某个会话可以用SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;。我遇到过一个需要实时读取刚提交数据、对可重复读不敏感的业务就单独给那个会话设置为读已提交效果比全局改级别安全得多。3.2 锁的分类表锁、行锁、间隙锁InnoDB 引擎的锁机制是整个事务正确性的基石。按粒度分有表锁、行锁、间隙锁、临键锁、意向锁。行锁听起来比表锁更精细但它依赖索引。如果 UPDATE 或 DELETE 的 WHERE 条件没有索引InnoDB 会退化为锁住全表记录实际效果近似表锁并发度瞬间下降。这也是为什么我在建表时会特别检查 update 条件字段是否有索引。间隙锁很多人听完就忘但它直接影响 REPEATABLE READ 下幻读的解决。简单说当你执行SELECT * FROM orders WHERE id BETWEEN 10 AND 20 FOR UPDATE时InnoDB 不仅锁住已有的 10 到 20 之间的行还会锁住这个范围里不存在的间隙防止其他事务插入新的 id 为 15 的记录。这个机制保证了在同一个事务里跑两次同样的范围查询查出来的结果集完全一致。死锁是最让人头疼的问题。最常见的原因是两个事务按不同顺序更新同一批资源。比如事务 A 先更新 id1 再更新 id2事务 B 先更新 id2 再更新 id1两边互相等对方持有的锁谁也前进不了。排查死锁时先执行SHOW ENGINE INNODB STATUS;看 LATEST DETECTED DEADLOCK 部分里面会明确写出两个事务分别持有什么锁、在等什么锁。日常规避死锁的核心经验就两条保持事务时间短所有事务按相同顺序访问资源。3.3 存储过程什么时候该用什么时候千万别用存储过程就是把一组 SQL 封装成一个可调用的程序。MySQL 的存储过程语法和函数不太一样需要先修改分隔符否则分号会被当成存储过程结束DELIMITER // CREATE PROCEDURE sp_get_user_orders(IN uid INT) BEGIN SELECT * FROM orders WHERE user_id uid; END // DELIMITER ; CALL sp_get_user_orders(1);存储过程的优点是减少应用和数据库之间的网络交互复杂逻辑可以整体放在数据库里执行。适合的场景是批量初始化数据、一次性的数据迁移、定时统计任务。但我不建议把核心业务逻辑写进存储过程原因有三个第一它没法像应用代码一样做单元测试和版本管理第二数据库服务器的 CPU 资源通常比应用服务器更宝贵所有计算都压到数据库上扩容成本很高第三存储过程的调试体验很差报错信息往往不够直观。我的经验是存储过程适合做一次性脚本或内部工具而不适合做对外服务。4. 慢 SQL 优化不是玄学执行计划告诉你怎么下手4.1 慢查询日志定位问题很多系统不是一开始就慢而是数据量涨到一定程度后某几条 SQL 突然变成性能瓶颈。要找到它们先打开慢查询日志。在 MySQL 命令行里可以动态开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;long_query_time单位是秒默认 10 秒太宽松开发环境我会设成 1 秒。日志路径可以通过SELECT slow_query_log_file;查看。需要注意这种动态设置重启后会失效如果要在生产环境长期开启需要写进 my.cnf 配置文件的[mysqld]段。日志文件积累一段时间后可以用自带的mysqldumpslow工具做聚合分析。比如按平均执行时间排序查看最耗时的前几条mysqldumpslow -s at /var/lib/mysql/slow.log聚合结果会把变量变成抽象形式比如WHERE id N这样你看到的就是同一类 SQL 的汇总而不是成千上万条单独的日志。拿到慢 SQL 之后不要急着改索引先去搞懂执行计划。4.2 EXPLAIN 执行计划到底看哪几列EXPLAIN 是优化 SQL 最核心的工具用法就是在任何 SELECT 语句前加一个 EXPLAINEXPLAIN SELECT * FROM orders WHERE user_id 100;返回结果里最值得关注的几列我整理成了一张平时检查用的表列名关注点type连接类型从好到差大致是 const、eq_ref、ref、range、index、ALLkey实际使用的索引NULL 表示没走索引rows预估扫描行数越小越好Extra可能出现的 Using filesort、Using temporary、Using index 等看到type ALL说明是全表扫描通常是 first optimization target。看到Extra Using filesort说明排序没有用到索引MySQL 需要额外排序当数据量大时很伤性能。看到Extra Using temporary说明产生了临时表常见于 GROUP BY 或 UNION 操作同样需要警惕。需要注意的是rows是优化器的预估值不是精确值但不能因为不精确就不看它数量级的差异仍然很有参考意义。我在排查一条慢 SQL 时会先看 key 是否为空再看 type 是否为 ALL接着看 Extra 有没有 filesort 和 temporary。这三步能解决大多数索引缺失和排序问题。4.3 索引失效的五个现场与修复建了索引但查询还是慢往往是索引没被用上。我遇到最多的失效原因有五个前置通配符WHERE name LIKE %abc%无法走 name 索引改成abc%可以。对列使用函数WHERE DATE(create_time) 2025-01-01会让 create_time 索引失效改成范围查询create_time 2025-01-01 AND create_time 2025-01-02。隐式类型转换varchar 字段 phone 用WHERE phone 123查询MySQL 会把字段转成数字比较索引失效写成123即可。OR 连接非索引字段WHERE id 1 OR status 2如果 status 没有索引整个查询可能退化为全表扫描考虑拆成 UNION。联合索引不满足最左匹配索引(category_id, price)查询条件是price 100category_id 不在条件里用不到这个索引。修复原则其实一句话让查询条件里的列保持原样别让它被函数、运算、隐式转换包裹。比如日期范围查询改写后不仅走了索引还更容易利用范围扫描效果立竿见影。联合索引还有一个容易被忽视的规则范围查询之后的列会失效。比如索引(a, b, c)查询WHERE a 1 AND b 100 AND c 5其中 c 的条件无法继续走索引。这种场景要么调整索引列顺序要么把范围列放到最后。设计索引时把等值条件放前面范围条件放后面是基本要求。5. SQL 注入与日常开发防护安全课一节不能少5.1 注入是怎么发生的SQL 注入听起来很像安全专家的专属话题但它实际离普通开发者很近。核心原因是应用在拼接 SQL 时把用户输入的文本直接当成 SQL 语句的一部分改变了原语句的语义。最典型的场景是登录查询、搜索关键字、列表排序字段。如果用户输入了一段特殊的文本而你没有做任何处理数据库看到的就不再是查询某一条记录而可能是返回所有记录甚至删掉某张表。我见过不少项目在早期为了赶进度习惯用字符串拼接的方式组装 SQL。比如String sql SELECT * FROM users WHERE username username 。只要 username 里出现单引号或注释符整个查询逻辑就可能被改写。这问题不是某个语言的专利Java、Python、PHP、Go 都有类似情况区别只是开发者在拼字符串的时候有没有意识到风险。对于 SQL 注入我的立场是不要抱着研究绕过姿势的心态去拿生产库试。理解原理是为了写出防御代码把所有输入都当成不可信数据而不是为了挑战数据库的底线。在自建测试环境里验证原理可以但任何绕过技巧都不应该出现在生产环境讨论清单里。5.2 参数化查询为什么是底线防御 SQL 注入最重要也最有效的手段是参数化查询。它的原理很简单SQL 语句先以模板形式发给数据库完成预编译用户输入作为参数单独传过去数据库把参数当普通值处理而不是 SQL 代码。这样无论用户输入里带什么特殊字符都只是数据不会改变语句语义。以 Java JDBC 为例String sql SELECT * FROM users WHERE username ? AND password ?; try (PreparedStatement ps conn.prepareStatement(sql)) { ps.setString(1, username); ps.setString(2, password); ResultSet rs ps.executeQuery(); }Python 里的cursor.execute(SELECT * FROM users WHERE username ?, (username,))也是同一个道理。几乎所有主流语言和 ORM 都支持参数化查询关键是在编码规范里明确禁止直接拼接 SQL 字符串。有一个容易忽略的点表名、字段名这类结构标识符不能用占位符参数化。比如ORDER BY ?是无法预编译的。这时候需要用白名单映射把允许排序的字段列出来用户只能传枚举值绝不能直接把用户输入拼进 ORDER BY 语句。5.3 输入校验和最小权限参数化查询能防住大多数注入但它不是银弹。剩下的防线是输入校验和数据库账号权限。输入校验不是简单的trim()和长度判断而是根据字段类型做严格校验数字就校验范围日期就校验格式枚举就校验取值。这样即使某处漏写了参数化查询恶意输入也很难构造成功。数据库账号权限是我见过很多公司忽略的点。应用连接数据库时用的账号只应该拥有它自己业务表的最小权限比如 SELECT、INSERT、UPDATE、DELETE绝不应该给 DROP、ALTER 甚至 FILE 权限。一个内部后台系统只需要读写 orders 表那就别拿拥有整个库管理权限的 root 账号去连接。这样即使应用被攻破攻击者也拿不到文件读取或删库的权限。另外数据库密码、连接串配置要纳入代码管理之外的保密渠道不要把强密码硬编码在代码里。定期检查错误日志和慢查询日志也是一种便宜而有效的安全审计方式。6. 今天踩过的坑和整理出来的查漏清单6.1 排序相同时的稳定性问题这个坑在排行榜类需求里特别常见。比如要取销量最高的前 5 条很多人的写法是ORDER BY sales DESC LIMIT 5。当第 5 名和第 6 名销量一样时MySQL 并不保证每次返回同一条记录。第一次查可能取到商品 A第二次查可能取到商品 B后端程序哪怕逻辑完全一样接口返回也在变。解决方法是给排序加一个全局唯一的兜底字段。通常我会写成ORDER BY sales DESC, id ASC。这样即使销量相同也有 id 决定最终顺序结果稳定。这个习惯对分页同样重要前面提到的游标分页也是同一逻辑。6.2 去重后统计翻车统计和去重组合时NULL 坑最隐蔽。COUNT(col)不会统计 NULL 值COUNT(DISTINCT col)也不会统计 NULL。如果某个字段允许为空而你要统计出现过多少种取值记得先明确要不要包含空值。比如统计用户填写的手机号种类空手机号不参与去重统计通常没问题但统计多少个用户填写了邮箱时如果用COUNT(email)没填的用户就会被漏掉而你可能希望他们也算在总数里。数据清洗里的去重则建议不要用先查出重复组再逐条删的笨办法。用窗口函数标记重复行之后再删是最稳的DELETE FROM orders WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER(PARTITION BY order_no ORDER BY id) AS rn FROM orders ) t WHERE t.rn 1 );先在内层子查询给重复的记录按 id 从小到大编号保留 rn1删除其他。MySQL 不允许直接对同一表做子查询更新所以要套两层这个细节很容易踩。6.3 连接查询时 NULL 过滤的陷阱LEFT JOIN 有一个经典误用左表是用户右表是订单想查所有用户以及他们的订单信息很自然写成LEFT JOIN orders o ON u.id o.user_id。如果这时候想在结果里只保留有过订单的用户有人会在 WHERE 里加o.amount 100。这个条件一加LEFT JOIN 实际上变成了 INNER JOIN没有订单的用户会被过滤掉因为右表字段为 NULL不满足条件。正确做法是把右表条件放到 ON 子句里SELECT u.id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.amount 100;这样保留的是所有用户左表用户的订单信息如果不符合条件就显示 NULL。统计用户订单数量时也一样COUNT(o.id)在 LEFT JOIN 下只统计有匹配订单的记录而COUNT(*)会把无订单的用户也算一条两种结果含义完全不同。6.4 顺手记下两个小问题SSL 连接错误和默认值有一天我在连接测试环境 MySQL 8 时报了 SSL 连接错误第一反应以为是 SQL 写错了调试了半天才发现和 SQL 一点关系都没有。MySQL 客户端和服务端默认协商 SSL当服务端证书不受客户端信任时就会报 SSL 相关错误。开发环境临时处理可以在 JDBC 连接串里加useSSLfalseallowPublicKeyRetrievaltrue线上环境则要正确配置 CA 证书或使用 SSL 模式。这个排错经验价值在于看到SSL connection error先别怀疑 SQL把问题定位到连接层能省掉大量时间。另一个小问题给已有表字段设置默认值为 0很多人以为执行完ALTER TABLE就万事大吉。实际上修改默认值只影响之后新插入的行已经存在的 NULL 值不会自动变成 0。如果你需要历史数据也统一为 0必须再执行一次显式的UPDATE table SET col 0 WHERE col IS NULL;。这类DDL 不回头修改存量数据的问题在数据初始化脚本里非常常见。这一天学下来我最明显的感受是SQL 性能和安全问题从来不是某一个单独技巧能解决的而是要把排序规则、索引设计、事务隔离、权限最小化这些基础概念串起来。以后再遇到一条慢 SQL我会先问自己数据库看到这条语句时是会扫全表还是走索引排序会不会产生临时文件事务隔离级别会不会放大锁的竞争当你开始用数据库的执行视角去审视 SQL很多以前靠运气和试错解决的问题都会变成可控的、可以被解释的结论。