MySQL 最容易踩的 15 个坑及避坑指南

MySQL 因为开源、免费、性能不错，在国内使用非常广泛。但在日常开发中，我们经常会遇到各种容易“踩坑”的情况，导致查询变慢、数据不一致，甚至系统崩溃。这篇文章就结合实战，梳理了 MySQL 开发中最常见的 15 个坑，并给出对应的优化策略，希望对你有帮助。

1. 查询不加 WHERE 条件

一些开发者习惯一次性查出整张表的所有数据，然后在内存中处理业务逻辑，认为这样代码更高效。

反例：

SELECT * FROM users;

这种做法，数据量小的时候看不出问题。但数据量一旦增长，每个操作都要扫描全表，极易导致内存溢出（OOM），处理速度也会急剧下降。

正例：

SELECT * FROM users WHERE code = '1001';

使用具体的 WHERE 条件先过滤数据，再做处理，既安全又高效。

2. 没有使用索引

线上系统刚上线时数据较少，没加索引也能跑。但随着用户量增加，数据呈指数级增长，某天你会突然发现查询变得极慢。

比如这个查询：

SELECT * FROM orders WHERE customer_id = 123;

给 customer_id 字段加上索引，速度会大幅提升：

CREATE INDEX idx_customer ON orders(customer_id);

3. 不处理 NULL 值

统计时经常忽略 NULL 的影响，导致结果与预期大相径庭。

反例：

SELECT COUNT(name) FROM users;

这条语句只统计了 name 字段不为 NULL 的记录数，并不是总行数。

如果想统计所有记录，应该用 COUNT(*)：

SELECT COUNT(*) FROM users;

这样才能得到真正的总行数。

4. 数据类型选错

有些开发者在建表时随意使用 VARCHAR(255)，不仅浪费存储，还会降低查询性能。

反例：

CREATE TABLE products (
    id INT,
    status VARCHAR(255)
);

如果字段值只有几种固定状态，完全可以用更小的整形类型。例如改为 tinyint：

CREATE TABLE products (
    id INT,
    status tinyint(1) DEFAULT '0' COMMENT '状态 1：有效 0：无效'
);

这样既节省空间，又提升效率。

5. 深分页问题

分页查询是日常操作，一般我们都用 LIMIT：

SELECT * FROM users LIMIT 0,10;

当数据量很大时，前几页性能可能尚可。但翻到第 10 万页时，查询会变得异常缓慢，这就是“深分页”问题。

为什么会慢？因为 MySQL 需要先扫描前面所有的数据，再丢弃掉不需要的行，只返回最后 10 条。下面给出三种优化方案。

5.1 记录上一次的 ID

核心思路：避免重复扫描已经查过的数据。配合自增主键，记录上一次查询结果的最大 ID，下次翻页时直接从该 ID 之后开始。

SELECT id,name FROM orders
WHERE id > 1000000
LIMIT 100000,10;

假设上次查询返回的最大 ID 是 1000000，这次从 1000001 开始取 10 条。但要注意：

• 仅适合连续翻页（上一页/下一页），不适用于随机跳页。
• 要求 ID 字段必须连续自增。

5.2 使用子查询

先用子查询找出符合条件的少量主键，再用主键去关联原表取完整数据。这样可以走覆盖索引，大幅减少回表次数。

SELECT * FROM orders WHERE id IN (
  SELECT id FROM (
    SELECT id FROM orders WHERE create_time > '2024-08-11' LIMIT 100000, 10
  ) t
);

原本的 SQL 扫描 100010 条记录，却要回表 100010 次；而子查询方式只回表 10 次，性能自然提升。

5.3 使用 INNER JOIN 关联查询

与子查询思路类似，通过 JOIN 先把满足条件的主键集筛选出来，再做关联查询。

SELECT o1.* FROM orders o1
INNER JOIN (
   SELECT id FROM orders
    WHERE create_time > '2024-08-11'
    LIMIT 100000,10
) AS o2 ON o1.id = o2.id;

这种方式同样能有效减少回表，加快查询速度。

6. 没有用 EXPLAIN 分析查询

一条 SQL 跑得慢，不先看执行计划就盲目调整，往往事倍功半。

正例：

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

EXPLAIN 会告诉你查询到底是怎么执行的：是否走索引、扫描了多少行、使用了哪种连接方式等，帮助你精准定位瓶颈。想深入了解，可以看这篇：SQL性能优化神器。

7. 字符集设置不当

很多人习惯将字符集设为 utf8，我自己以前也这样干过。但后来踩坑了：用户输入表情符号时，程序直接报错；汉字也可能变成乱码，体验极差。

建表时建议直接使用 utf8mb4：

CREATE TABLE messages (
    id INT,
    content TEXT
) CHARACTER SET utf8mb4;

utf8mb4 能够支持更多字符，包括常用的中文汉字和 Emoji 表情，避免乱码烦扰。

8. SQL 注入风险

拼接 SQL 字符串是典型的安全隐患，很容易被 SQL 注入攻击。比如在动态排序时，直接用参数拼 ORDER BY 字段或排序方式，一旦处理不当就可能出大事。

反例：

String query = "SELECT * FROM users WHERE email = '" + userInput + "';";

应当使用预编译语句（PreparedStatement）：

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM users WHERE email = ?");
stmt.setString(1, userInput);

在 MyBatis 中，尽量使用 #{} 进行参数替换，避免使用 ${}。关于 SQL 注入的更多细节，可以参考这篇文章：卧槽，sql注入竟然把我们的系统搞挂了。

9. 事务问题

更新多个相关表时不加事务，是数据不一致的常见诱因。

反例：

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

用户 1 向用户 2 转账 100 元，如果没有事务，可能出现用户 1 钱已扣掉，用户 2 却没收到的情况。

正确的做法是开启事务：

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

这样即便第二步失败，第一步也会回滚。在 Spring 中，除了声明式 @Transactional，更推荐使用 TransactionTemplate 进行编程式事务管理，可控性更好。

10. 校对规则问题

表和字段可以指定 COLLATE 校对规则，主要分三类：

• _ci（case insensitive）：不区分大小写
• _cs（case sensitive）：区分大小写
• _bin：按二进制存储，也区分大小写

最常用的是 utf8mb4_general_ci（默认不区分大小写）和 utf8mb4_bin。

例如品牌表使用了 utf8mb4_unicode_ci（不区分大小写）：

CREATE TABLE `brand` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(30) NOT NULL COMMENT '品牌名称',
  `create_user_id` bigint NOT NULL COMMENT '创建人ID',
  `create_user_name` varchar(30) NOT NULL COMMENT '创建人名称',
  `create_time` datetime(3) DEFAULT NULL COMMENT '创建日期',
  `update_user_id` bigint DEFAULT NULL COMMENT '修改人ID',
  `update_user_name` varchar(30) DEFAULT NULL COMMENT '修改人名称',
  `update_time` datetime(3) DEFAULT NULL COMMENT '修改时间',
  `is_del` tinyint(1) DEFAULT '0' COMMENT '是否删除 1：已删除 0：未删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='品牌表';

查询时：

SELECT * FROM brand WHERE `name`='yoyo';

这条 SQL 也能匹配到 YOYO。但如果业务代码里直接使用 equals 方法判断字符串，就会因为大小写不一致而出错。此时应改用 equalsIgnoreCase 方法。

11. 使用过多的 SELECT *

习惯用 SELECT * 会把所有字段都取出来，很多实际用不到，白白浪费网络带宽和查询资源。

反例：

SELECT * FROM orders;

只查需要的字段：

SELECT id, total FROM orders;

按需取列，简单又高效。

12. 索引失效

你是否遇到过：明明建了索引，但查询时索引就是没用上，SQL 突然变得奇慢无比？用 EXPLAIN 看一下执行计划，确认是否真的失效了。

常见的索引失效原因，下面这张思维导图总结得很清楚：

如果想进一步了解，可以阅读这篇详细的文章：聊聊索引失效的10种场景，太坑了。避免索引失效的思路其实也可以总结成一句话：用对索引，少走弯路。你可以把这张图收藏起来，方便随时对照检查。索引失效往往是查询性能恶化的元凶，务必重视。

13. 频繁修改表或数据

在高并发场景下，频繁 ALTER 表结构、大批量 UPDATE 或 DELETE，都可能引发锁表，导致大量用户请求堆积，系统响应变慢。

优化建议：

• 大量更新或删除数据时，分批执行，不要一次性处理过多记录。
• ALTER、DROP 等修改表结构的操作，避开业务高峰期，尽量选在凌晨低负载时执行。
• 可以考虑使用 Percona Toolkit、gh-ost 等在线 DDL 工具，在不锁表的前提下完成表结构变更。

14. 没有定期备份

最怕队友误删数据，这种事我自己就遇到过好几次。测试环境整张表被清空，数据全没了才追悔莫及。

定期用 mysqldump 备份是基本操作：

mysqldump -u root -p database_name > backup.sql

配置一个定时任务，每天或每周自动备份一次。关键时刻，通过 mysql 命令就能快速恢复数据，让风险降到最低。

15. 忘了归档历史数据

数据表越来越大，历史数据堆积严重，查询越来越慢。但用户真正关心的往往是近一个月、三个月或一年的数据，一年以前的数据大概率没人翻了。

建议将历史数据迁移到归档库，主库只保留近期热数据。这样既保证了核心业务的查询性能，也满足了极少数的历史查询需求。

---

以上 15 个坑，你踩过几个？希望这份避坑指南能帮你在日常开发中少走弯路。更多数据库实战经验，欢迎到云栈社区一起交流探讨。