DevCN开发者的创作与交流社区
RSS
菜单
全部文章快讯开发科技深度热点
技术文章/开发

第 35 章 MySQL踩坑：SQL写法篇

站长
内容摘要

本篇收拾的是"SQL 写得能跑，但写得很烂"这一类坑——SELECT *、隐式转换、深分页、忘记 WHERE 的批量更新、单条循环插入。这些写法在小数据量下完全看不出问题，一旦数据量上到百万级，就会以慢查询、锁等待、主从延迟的形式集中爆发。前置知识是你已经会写基础 SELECT、JOIN、分页和聚合。学完之后，你应该能一眼看出一段业务 SQL 里哪些写法是定时炸弹，并给出更高效的等价改写。本系列所有示例基于 MySQL 8.0 语法书写，本示例基于 MySQL 8.0 环境。

📖 本篇简介：本篇收拾的是"SQL 写得能跑，但写得很烂"这一类坑——SELECT *、隐式转换、深分页、忘记 WHERE 的批量更新、单条循环插入。这些写法在小数据量下完全看不出问题，一旦数据量上到百万级，就会以慢查询、锁等待、主从延迟的形式集中爆发。前置知识是你已经会写基础 SELECT、JOIN、分页和聚合。学完之后，你应该能一眼看出一段业务 SQL 里哪些写法是定时炸弹，并给出更高效的等价改写。本系列所有示例基于 MySQL 8.0 语法书写，本示例基于 MySQL 8.0 环境。

目录

  • 坑 1：select 星号滥用
  • 坑 2：where 里隐式类型转换
  • 坑 3：对索引列套函数和运算
  • 坑 4：深分页 limit 大偏移量
  • 坑 5：忘写 where 的全表更新删除
  • 坑 6：关联子查询被反复执行
  • 坑 7：不必要的 distinct 去重
  • 坑 8：大表 count star 慢
  • 坑 9：join 列类型或字符集不一致
  • 坑 10：单条循环插入没批量
  • 坑 11：小表驱动大表写反了
  • 坑 12：在业务里用 offset 翻到最后一页
  • 速查表：SQL写法坑点速查
  • 注意事项：写 SQL 时的自查清单
  • 练习题：动手自测与参考

坑 1：select 星号滥用

场景 / 错误示范：

-- 错误示范：图省事，查询列表永远 SELECT *
SELECT * FROM orders WHERE user_id = 100;

-- 一个订单表有 30 个字段，接口实际只用 3 个
-- 结果：多传了 27 个字段的网络字节，还让覆盖索引失效
-- 更糟的是 SELECT * 没法做覆盖索引优化

为什么是坑：SELECT * 会把行里所有列都取出来，包括你根本用不到的大文本字段（text、json、blob）。这不仅增加网络传输和内存消耗，还让"覆盖索引"这个优化彻底失效——因为你要的列不在索引里，必须回表。表结构一改，SELECT * 的代码还容易崩。

正确做法：

-- 正确做法：只取需要的列
SELECT id, user_id, amount, create_time
FROM orders
WHERE user_id = 100;

-- 这样才能配合覆盖索引（18 章讲过）
-- EXPLAIN 的 Extra 出现 'Using index' 就是覆盖索引生效

加分点：写代码时养成"列名一个个列出来"的习惯，虽然麻烦，但好处是表结构变更时编译期就能发现问题，而且 SELECT 列稳定后才能建覆盖索引。SELECT * 只在临时排查问题时用，别进生产代码。

坑 2：where 里隐式类型转换

场景 / 错误示范：

-- 表：用户表，phone 是 varchar，有索引
CREATE TABLE user (
  id    BIGINT PRIMARY KEY,
  phone VARCHAR(20),
  KEY idx_phone (phone)
);

-- 错误示范：手机号被当成数字传进来，没加引号
SELECT * FROM user WHERE phone = 13800001111;
-- 索引失效，全表扫描（详见索引篇坑 2）

为什么是坑：MySQL 在比较不同类型时会自动做类型转换，这个转换往往是对索引列套函数，导致索引失效。更隐蔽的是它不一定报错，只是慢，等你发现时已经全表扫了几百万行。

正确做法：

-- 正确做法：常量和列类型严格对齐
SELECT * FROM user WHERE phone = '13800001111';

-- 应用层：MyBatis 用 #{} 预编译，别用 ${} 字符串拼接
-- 建表时：phone 永远是 varchar，不要为了"省空间"用 bigint

加分点：建表时把同语义字段类型统一（所有 user_id 都是 BIGINT，所有 order_no 都是 varchar(32)），能避免 90% 的隐式转换坑。JOIN 两边列类型不一致也会触发同样的问题。

坑 3：对索引列套函数和运算

场景 / 错误示范：

-- 错误示范一：对索引列做运算
SELECT * FROM orders WHERE id + 0 = 100;        -- 索引失效
SELECT * FROM orders WHERE amount * 100 > 5000; -- 索引失效

-- 错误示范二：字符串拼接后比较
SELECT * FROM user WHERE CONCAT(last_name, first_name) = '张三';
-- 这一列即使有索引也用不上

为什么是坑：和索引篇坑 1 一样，对索引列做任何函数或算术运算，都会破坏 B+Tree 的有序性，优化器只能全表扫。这种写法往往是程序员图"逻辑上等价"随手写的，没意识到代价。

正确做法：

-- 正确做法：把运算挪到等号右边，让索引列保持原样
SELECT * FROM orders WHERE id = 100;
SELECT * FROM orders WHERE amount > 5000 / 100;

-- 字符串拼接查询：改成前缀或范围
SELECT * FROM user WHERE last_name = '张' AND first_name = '三';
-- 或者建联合索引 (last_name, first_name)

加分点：写 WHERE 时盯一个原则——等号左边的列，必须是"裸列"，不能包函数、不能做运算、不能有隐式转换。违反这条，索引基本就废了。

坑 4：深分页 limit 大偏移量

场景 / 错误示范：

-- 错误示范：后台报表翻到第 1000 页，每页 20 条
SELECT * FROM orders
ORDER BY id
LIMIT 20000, 20;
-- MySQL 会先扫描 20020 行，扔掉前 20000 行，只返回后 20 行
-- 越翻越慢，到第几万页时接口直接超时

为什么是坑：LIMIT 20000, 20 的语义是"扫描 20020 行，丢弃前 20000"。MySQL 不知道你要的是第 20001~20020 行，它必须老老实实从头扫。偏移量越大，扫的行数越多，磁盘 IO 和 CPU 都浪费在前 20000 行上。

正确做法：

-- 正确做法一：游标分页（记住上一页最后一个 id，每次往后捞）
-- 第一页：
SELECT * FROM orders ORDER BY id LIMIT 20;
-- 第二页（假设上一页最后 id=200）：
SELECT * FROM orders WHERE id > 200 ORDER BY id LIMIT 20;
-- 这样每次都是从 id=200 往后扫 20 行，O(1)

-- 正确做法二：延迟关联（先通过覆盖索引定位主键，再回表）
SELECT o.* FROM orders o
INNER JOIN (
  SELECT id FROM orders ORDER BY id LIMIT 20000, 20
) t ON o.id = t.id;
-- 子查询走覆盖索引，不回表，速度快很多

加分点：后台管理系统别让用户翻到几百页以后，加个"最多看前 100 页"或者强制用条件过滤。真正的深分页需求（导出全量）走游标 + 异步任务，别让用户在网页上等。

坑 5：忘写 where 的全表更新删除

场景 / 错误示范：

-- 错误示范：本想更新某一个用户的状态，手抖忘写 WHERE
UPDATE user SET status = 0;
-- 全表几千万行全部更新一遍！锁表、主从延迟、业务雪崩

-- 更可怕的是 DELETE：
DELETE FROM orders;
-- 全表删空，还没法轻易回滚（尤其没开事务时）

为什么是坑：UPDATE/DELETE 不带 WHERE 就是全表操作。InnoDB 会逐行加锁、写 undo log、写 redo log、记录 binlog，一张百万行的表这么干，几分钟内数据库就被拖死。更要命的是，如果 autocommit=1，这条语句立刻提交，想回滚都来不及。

正确做法：

-- 正确做法一：任何 UPDATE/DELETE 先用 SELECT 确认影响范围
SELECT * FROM user WHERE id = 100;   -- 先查
UPDATE user SET status = 0 WHERE id = 100;  -- 再改

-- 正确做法二：显式开事务，改完确认行数再提交
START TRANSACTION;
UPDATE user SET status = 0 WHERE id = 100;
-- 看 affected rows 是不是 1，不是就 ROLLBACK
COMMIT;   -- 没问题再提交

-- 正确做法三：小批量删，别一次删几百万
DELETE FROM orders WHERE create_time < '2026-01-01' LIMIT 1000;
-- 循环跑，每次 1000 行，控制主从延迟

-- 安全习惯：删数据前先 SELECT COUNT(*) 看规模
SELECT COUNT(*) FROM orders WHERE create_time < '2026-01-01';

加分点：生产库给应用账号禁掉无 WHERE 的 UPDATE/DELETE（用触发器或审计），并且永远在事务里做批量写，确认行数对了再 COMMIT。执行大删除前先备份。

坑 6：关联子查询被反复执行

场景 / 错误示范：

-- 错误示范：子查询里引用了外层表的列，每行都要执行一次
SELECT o.id, o.amount,
  (SELECT MAX(create_time) FROM pay p WHERE p.order_id = o.id) AS last_pay
FROM orders o
WHERE o.create_time >= '2026-10-01';
-- orders 查出来 10 万行，这个关联子查询就要执行 10 万次

为什么是坑：关联子查询（correlated subquery）对外层每一行都执行一次内层查询。外层 10 万行，内层就跑 10 万次，哪怕每次都很快，累计起来也是灾难。MySQL 8.0 对部分子查询做了优化，但别依赖优化器猜你想干什么。

正确做法：

-- 正确做法：改成 JOIN + GROUP BY，一次聚合完
SELECT o.id, o.amount, p.last_pay
FROM orders o
LEFT JOIN (
  SELECT order_id, MAX(create_time) AS last_pay
  FROM pay
  GROUP BY order_id
) p ON p.order_id = o.id
WHERE o.create_time >= '2026-10-01';
-- 内层 pay 先聚合一次，再和 orders 关联，只扫两遍表

加分点：EXPLAIN 里看到 DEPENDENT SUBQUERY 就要警惕，这是关联子查询在每行执行。能用 JOIN 改写就改写，实在改不了的，确认外层结果集很小再用。

坑 7：不必要的 distinct 去重

场景 / 错误示范：

-- 错误示范：为了"去重"无脑加 DISTINCT
SELECT DISTINCT o.user_id, u.name
FROM orders o
JOIN user u ON u.id = o.user_id
WHERE o.create_time >= '2026-10-01';
-- 业务上 user_id 在 orders 里本来就重复（一个用户多笔订单），
-- 加 DISTINCT 会让 MySQL 把结果集临时排序去重，量大了落磁盘

为什么是坑：DISTINCT 会对整行结果去重，MySQL 需要把全部结果拉到临时表或 sort_buffer 里排序，再去掉重复。如果 JOIN 本身一对多导致行数膨胀，正确做法是先搞清楚为什么膨胀——是 JOIN 条件不对，还是真的要去重。无脑 DISTINCT 往往是在掩盖 SQL 设计的问题。

正确做法：

-- 正确做法一：真要"每个用户一笔"，用 GROUP BY 语义更清晰
SELECT o.user_id, MAX(u.name) AS name
FROM orders o
JOIN user u ON u.id = o.user_id
WHERE o.create_time >= '2026-10-01'
GROUP BY o.user_id;

-- 正确做法二：确认 JOIN 条件，不要一对多乱连
-- 如果 orders 一个 user_id 对应多行，又只要用户维度，
-- 用 EXISTS 半连接代替 JOIN：
SELECT u.id, u.name FROM user u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.create_time >= '2026-10-01'
);

加分点：DISTINCT 只在"结果真的有重复行"时用。大部分时候，你要的是"每个分组一条"，那是 GROUP BY 的活儿。看到 DISTINCT 先问一句：为什么会重复？

坑 8：大表 count star 慢

场景 / 错误示范：

-- 错误示范：后台分页要显示"共多少条"，直接
SELECT COUNT(*) FROM orders WHERE status = 1;
-- orders 表几千万行，这个 COUNT 要扫半天，接口超时

为什么是坑：InnoDB 的 COUNT(*) 不像 MyISAM 那样有现成的计数（因为 MVCC 下每个事务看到的行数不同），它必须真实地数一遍。带 WHERE 条件的 COUNT 还要逐行过滤，表越大越慢。分页插件每次翻页都查一次总数，直接把数据库压垮。

正确做法：

-- 正确做法一：近似值，用 EXPLAIN 拿到估算行数（误差不大但极快）
EXPLAIN SELECT * FROM orders WHERE status = 1;
-- EXPLAIN 结果里的 rows 字段就是优化器估算的行数

-- 正确做法二：不带精确条件的总数，单独维护一张计数表
-- 或者用 Redis 缓存，写入时 incr/decr

-- 正确做法三：分页接口干脆不查总数，改成"上一页/下一页"
-- 或者只显示"加载更多"，后台系统才显示总数

-- 正确做法四：带条件的精确 count，走覆盖索引
SELECT COUNT(*) FROM orders
WHERE status = 1 AND create_time >= '2026-10-01';
-- 配合 (status, create_time) 联合索引，扫索引不回表

加分点：互联网产品的列表页几乎都不显示"共 xxx 条"，原因就是这个。非要显示，用缓存 + 定时刷新，别让用户点一次接口就 COUNT 一次大表。

坑 9：join 列类型或字符集不一致

场景 / 错误示范：

-- 表 A：user_id 是 bigint
-- 表 B：order.user_id 是 varchar
SELECT * FROM orders o
JOIN user u ON o.user_id = u.id;
-- JOIN 条件两边类型不一致，MySQL 隐式转换，索引失效

-- 更隐蔽：两张表字符集不同（一个 utf8mb4，一个 utf8mb3）
-- JOIN 时要做字符集转换，索引照样失效
CREATE TABLE a (name VARCHAR(50) CHARSET utf8mb4);
CREATE TABLE b (name VARCHAR(50) CHARSET utf8mb3);
SELECT * FROM a JOIN b ON a.name = b.name;   -- 隐式转码

为什么是坑：JOIN 的本质是按连接条件做哈希或嵌套循环。连接列类型不一致，MySQL 要在比较时逐行转换，没法用索引快速匹配。字符集不一致更隐蔽，报错都不报，就是慢。

正确做法：

-- 正确做法：建表时就对齐类型和字符集
CREATE TABLE orders (
  id      BIGINT PRIMARY KEY,
  user_id BIGINT,              -- 和 user.id 都是 bigint
  KEY idx_uid (user_id)
);

-- 已经不一致的，用 CONVERT 显式转换让一边对齐（不如改表结构）
SELECT * FROM orders o
JOIN user u ON o.user_id = CAST(u.id AS UNSIGNED);

-- 字符集不一致：统一改成 utf8mb4
ALTER TABLE b CONVERT TO CHARACTER SET utf8mb4;

加分点：做库表设计评审时，把"同名字段类型、字符集一致"作为一条硬规则。user_id、order_no、商品编码这些跨表关联字段，从第一天就定死类型。

坑 10：单条循环插入没批量

场景 / 错误示范：

# 错误示范：在应用里 for 循环一条一条插
import pymysql
conn = pymysql.connect(host='127.0.0.1', user='root', password='***', database='shop')
cursor = conn.cursor()
for i in range(10000):
    # 每条 INSERT 都走一次网络往返 + 一次事务提交
    cursor.execute("INSERT INTO t(name) VALUES (%s)", (f'name_{i}',))
    conn.commit()   # 每条都提交，极慢
conn.close()

为什么是坑：单条 INSERT 每次都要网络往返、SQL 解析、事务提交（刷 redo log）。插 1 万行如果一条一条提交，可能要几分钟；批量插入几十秒就搞定。性能差的根源是"网络往返次数"和"提交次数"，不是 SQL 本身。

正确做法：

# 正确做法：批量 VALUES + 一次事务
import pymysql
conn = pymysql.connect(host='127.0.0.1', user='root', password='***', database='shop')
cursor = conn.cursor()

# 一次拼多个 VALUES
rows = [(f'name_{i}',) for i in range(10000)]
# 分批，比如每 1000 行一批，避免单条 SQL 过大
for batch in [rows[i:i+1000] for i in range(0, len(rows), 1000)]:
    cursor.executemany("INSERT INTO t(name) VALUES (%s)", batch)
conn.commit()   # 整批提交一次
conn.close()

-- 等价的 SQL：一条 INSERT 带多组 VALUES
INSERT INTO t(name) VALUES
('name_1'), ('name_2'), ('name_3'), ... ('name_1000');

加分点：批量大小别贪大，每批 500~1000 行合适，单条 SQL 包太大反而撑爆 max_allowed_packet。load data infile 比 INSERT 批量还快一个数量级，大数据量导入用它。

坑 11：小表驱动大表写反了

场景 / 错误示范：

-- 错误示范：A 表 100 行，B 表 1000 万行
-- 优化器一般会自动选小表驱动，但你 hint 错了就完蛋
SELECT /*+ JOIN_ORDER(b, a) */ *
FROM a JOIN b ON a.id = b.a_id;
-- 强制让大表 b 驱动小表 a，嵌套循环就变成 1000 万次

-- 更常见的反例：不用 hint，但 WHERE 条件写错导致大表当驱动表
SELECT * FROM orders o
JOIN user u ON o.user_id = u.id
WHERE u.status = 1;
-- 如果 orders 有索引而 user.status 没索引，MySQL 选错驱动表就慢

为什么是坑：嵌套循环 JOIN 的代价 = 驱动表行数 × 被驱动表每次查找代价。用小表驱动大表，查找次数少；反过来就是灾难。MySQL 优化器大多数时候能选对，但统计信息过期、hint 写错、索引缺失时就会选错。

正确做法：

-- 正确做法一：了解小表驱动原则，写 SQL 时自己心里有数
-- 让结果集小的那张表当驱动表

-- 正确做法二：确认被驱动表的 JOIN 列有索引
-- orders.user_id 必须有索引，否则每次嵌套都全表扫
ALTER TABLE orders ADD KEY idx_uid (user_id);

-- 正确做法三：实在要 hint，用 STRAIGHT_JOIN 强制顺序
SELECT STRAIGHT_JOIN *
FROM user u JOIN orders o ON o.user_id = u.id
WHERE u.status = 1;
-- 让小表 user 当驱动表

加分点：EXPLAIN 看 id 和 rows 列，第一行（id 最小那行先执行）就是驱动表。发现驱动表 rows 特别大、被驱动表没走索引，就是 JOIN 顺序或索引有问题。

坑 12：在业务里用 offset 翻到最后一页

场景 / 错误示范：

-- 错误示范："导出全部订单"功能，前端用 LIMIT offset,size 一页页拉
SELECT * FROM orders ORDER BY id LIMIT 0, 100;
SELECT * FROM orders ORDER BY id LIMIT 100, 100;
SELECT * FROM orders ORDER BY id LIMIT 100000, 100;
-- 越往后越慢，最后导出几十万条要半小时

为什么是坑：和坑 4 一样，OFFSET 越大扫得越多。导出全量数据这种场景，用分页接口逐页拉，等于让数据库重复扫描前面的页，时间复杂度是 O(n²)。

正确做法：

-- 正确做法：导出走游标，记住上一批最后一个 id
-- 第一批
SELECT * FROM orders ORDER BY id LIMIT 1000;
-- 第二批（假设最后 id=10000）
SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 1000;
-- 循环直到取完

-- 工程上更常用：导出直接用 SELECT INTO OUTFILE 或后台异步任务
-- 别让用户在网页上等几万条数据慢慢拉

-- MySQL 8.0 还可以用游标式的 keyset pagination
SELECT * FROM orders
WHERE id > ?          -- 上一页最大 id
ORDER BY id
LIMIT 1000;

加分点：列表分页和全量导出是两回事。列表给用户看，LIMIT offset 能用（别太深）；导出全量，必须走游标或专门的导出通道，别图省事复用列表接口。

速查表：SQL写法坑点速查

| 坑号 | 坑点 | 典型症状 | 修法 |

|---|---|---|---|

| 1 | SELECT * | 覆盖索引失效、网络慢 | 只取需要的列 |

| 2 | 隐式类型转换 | 索引失效 | 常量加引号，类型对齐 |

| 3 | 列套函数运算 | 索引失效 | 运算挪到右边 |

| 4 | 深分页大偏移 | 越翻越慢 | 游标分页 / 延迟关联 |

| 5 | 无 WHERE 批量写 | 锁表、雪崩 | 先 SELECT 确认，事务里改 |

| 6 | 关联子查询 | 每行执行一次 | 改 JOIN + GROUP BY |

| 7 | 无脑 DISTINCT | 临时表排序 | 改 GROUP BY / EXISTS |

| 8 | 大表 COUNT(*) | 接口超时 | 近似值 / 缓存 / 覆盖索引 |

| 9 | JOIN 列类型字符集不一致 | 索引失效 | 建表时统一 |

| 10 | 单条循环插入 | 慢 10 倍 | 批量 VALUES + 批量提交 |

| 11 | 驱动表写反 | rows 巨大 | 小表驱动，确认被驱动表索引 |

| 12 | 导出用 offset 分页 | O(n²) | 游标 keyset pagination |

注意事项：写 SQL 时的自查清单

每条要上线的业务 SQL，提交前对照这张清单过一遍：

1. 有没有 SELECT *？把不用的列删掉。

2. WHERE 左边的列是不是裸列？有没有包函数、做运算。

3. 字符串常量加引号了吗？类型和列对齐吗？

4. LIMIT 的偏移量会不会太大？超过一万就考虑游标。

5. UPDATE/DELETE 带 WHERE 了吗？影响行数预估过吗？

6. JOIN 的被驱动表有索引吗？两边类型字符集一致吗？

7. DISTINCT 是必要的吗？还是该用 GROUP BY？

8. 大结果集 COUNT 有必要吗？能不能用近似值或缓存？

把这 8 条做成 Code Review 的 checklist，比事后救火轻松得多。

练习题：动手自测与参考

练习 1：SELECT * FROM orders WHERE phone = 13800001111（phone 是 varchar 有索引）为什么慢？怎么改？

<details>

<summary>参考答案</summary>

隐式类型转换：varchar 列和数字比较，MySQL 把列转成数字，相当于套了函数，索引失效。改成 WHERE phone = '13800001111'，常量加引号。

</details>

练习 2：后台接口 LIMIT 100000, 20 很慢，给出两种优化思路。

<details>

<summary>参考答案</summary>

①游标分页：记住上一页最后 id，WHERE id > ? ORDER BY id LIMIT 20；②延迟关联：子查询先用覆盖索引查出 20 个主键，再 JOIN 回原表取整行。

</details>

练习 3：一条 UPDATE 忘写 WHERE， autocommit=1，已经执行了，还有救吗？

<details>

<summary>参考答案</summary>

没救了——语句已自动提交。只能从最近一次备份恢复，或用 binlog 反向回滚。这就是为什么生产环境大表写操作必须显式开事务、先 SELECT 确认、小批量执行。教训：别依赖"我会记得加 WHERE"，靠流程保护。

</details>

下一篇预告：第 36 章 MySQL踩坑：事务与并发篇

— 全文完 —回到顶部 ↑
下载推广海报

文章推广海报

《第 35 章 MySQL踩坑：SQL写法篇》完整推广海报
DISCUSSION

文章回复

0 条公开回复
未登录回复需要审核后公开
还没有回复，欢迎参与讨论。