聊聊那些用MySQL时常被忽略但其实挺管用的小技巧和心得分享

这事儿还得从一次半夜被叫起来处理线上问题说起,那会儿系统慢得跟蜗牛一样，查了半天，最后发现罪魁祸首是一个看起来人畜无害的 SELECT COUNT(*)，自打那次以后，我才算真正开始留意那些 MySQL 里不起眼但关键时刻能救命的细节。

*第一点，COUNT() 和 COUNT(列名) 的误解。* 很多人觉得 COUNT(主键) 或者 COUNT(某个带索引的列) 会更快，这是个非常普遍的误区，其实在 MySQL 里，`COUNT()是经过特殊优化的，它会直接找数据量最小的那个非空索引来统计行数，效率往往是最高的，而COUNT(列名)则要多一步：先检查这列的值是不是 NULL，不是 NULL 才计数，如果你就是想统计表里有多少行，别多想，直接用COUNT(*)，别再用COUNT(id)` 了，这是来自《高性能MySQL》这本书里的建议，亲测有效。

第二点，EXPLAIN 不是看一眼就完事儿了。 我们都知道用 EXPLAIN 看 SQL 的执行计划，但很多人就扫一眼 type 字段是不是 “ref” 或者 “range” 就觉得 OK 了，其实真正藏魔鬼的地方在 “Extra” 字段，我以前就吃过亏，看到一个查询 type 是 “index”，觉得用了索引挺好，结果 “Extra” 里明晃晃写着 “Using temporary; Using filesort”，意思是 MySQL 为了完成这个查询，不得不创建临时表和在磁盘上进行排序，速度一下就掉下去了，以后用 EXPLAIN，一定要把 “Extra” 栏里的每个词都查清楚是什么意思，这里面才是性能问题的关键线索。

第三点，批量操作的艺术。 新手最容易犯的错就是喜欢在程序循环里一条一条地执行 INSERT 或 UPDATE，比如要插入一万条数据，就循环一万次，这对数据库来说是灾难性的，因为每次操作都要经历连接、语法分析、优化、执行、关闭这一整套流程，正确的做法是拼成批量语句，INSERT INTO table (a, b) VALUES (1,2), (3,4), (5,6)...，一次插入几百上千条，这个技巧听起来简单，但对性能的提升是数量级的，更新也是一样，能用 CASE WHEN 拼成一条 SQL 批量更新，就绝对不要拆开，这个心得是以前看一个技术博客学到的，博主说他用批量插入把原本需要跑一小时的脚本缩短到了几分钟。

第四点，小心连接池的“幽灵连接”。 我们用连接池本来是为了避免频繁创建销毁连接的开销，但有时候应用跑久了，会发现数据库的连接数莫名其妙满了，查下来又好像没有那么多活跃请求，这很可能是连接池里的连接因为网络波动或其他原因僵死了，但池子还以为它是好的，没有及时清理，这时候可以看看 MySQL 的 wait_timeout 和 interactive_timeout 这两个参数，它们控制着非交互式连接和交互式连接的空闲超时时间，可以适当设短一点（300 秒），同时确保你的应用层连接池有有效的心跳检测机制，定期去“唤醒”一下空闲连接，防止被 MySQL 服务器端主动断开，或者能及时清理掉无效连接，这个坑是我们 DBA 在一次线上故障后分享的，他说很多连接数打满的问题根源都在于此。

第五点，善用“延迟关联”优化深分页。 你肯定遇到过 SELECT * FROM table ORDER BY id LIMIT 10000, 20 这种查询，翻到后面几万页的时候巨慢，这是因为 MySQL 需要先读取 10020 条记录，然后扔掉前面的 10000 条，只返回最后 20 条，数据量一大，效率就惨不忍睹，一个很管用的技巧叫“延迟关联”，先把需要的主键查出来，再回表去拿数据，比如写成：SELECT * FROM table INNER JOIN (SELECT id FROM table ORDER BY id LIMIT 10000, 20) AS t USING (id)，里面的子查询只查主键 id，因为数据量小（只有id），在索引上就能完成排序和分页，速度很快，拿到20个id后再去主表里精确查找，效率高得多，这个技巧在很多数据库优化的文章里都有提到，是解决深分页问题的经典方案。

第六点，枚举字段的“陷阱”与“妙用”。 定义表的时候，我们有时会用 ENUM 类型，status ENUM('pending', 'success', 'failed')，好处是存储空间小，数据严谨，但有个容易被忽略的坑：加选项的成本，比如业务发展，要加一个 ‘cancelled’ 状态，这时候就需要执行 ALTER TABLE 去修改表结构，在大表上这是个危险操作，如果这个状态值是未来很可能频繁增加的，用 VARCHAR 外加一张字典表可能更灵活，但反过来，ENUM 有个妙用：它是有顺序的，你可以直接写 WHERE status > 'success' 这样的条件，这在某些业务逻辑判断时非常方便，可以少写很多 OR。

最后一点，养成随手给 SQL 加注释的习惯。 这不是 MySQL 的功能，而是个人习惯，但极其管用，特别是在业务复杂的系统里，一个看起来莫名其妙的 WHERE 条件，或者一个复杂的 JOIN，如果不加注释，过两个月你自己都忘了当时为啥要这么写，加一行简单的注释，-- 忽略已逻辑删除的数据 或者 -- 因XX业务需求，只统计特定类型，能帮未来的你或者接手的同事省下大量的排查时间，这算是我个人踩过无数坑之后的一点心得吧。

这些技巧单看可能都是小事,但组合起来，能在日常开发中避免很多性能陷阱和运维大坑，数据库用的好不好，很多时候就看对这些细节的把握。