12 KiB
12 KiB
tags, create time
| tags | create time | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
|
2026-05-16 00:30 |
常见踩坑
概述
MySQL 的设计有许多「反直觉」的行为。本章汇总最常见的陷阱和误区，帮助你在编码阶段就规避这些问题。
1. ORDER BY 产生 FileSort
-- ❌ 看似有索引，但实际上触发了 FileSort
CREATE INDEX idx_status ON orders(status);
EXPLAIN SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC;
-- Extra: Using where; Using filesort
-- 为什么？idx_status 只按 status 排序，created_at 是无序的
-- 虽然 WHERE 能用索引过滤，但 ORDER BY 无法利用该索引
-- ✅ 解决方法：创建联合索引
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
-- 现在的 EXPLAIN:
-- key: idx_status_created
-- Extra: Using where ← No filesort! 因为索引里已经是有序的
flowchart LR
IDX["联合索引 idx(status, created_at)"]
IDX -->|"WHERE status=? ORDER BY created_at"| PERF["✅ 完全利用索引顺序"]
IDX -->|"WHERE status=? LIMIT 100"| PERF
single_idx["单列索引 idx(status)"]
single_idx -->|"WHERE status=? ORDER BY created_at"| SORT["❌ Filesort<br/>内存排序"]
style PERF fill:#00D866,color:#fff
style SORT fill:#EE5A24,color:#fff
[!TIP] 教学要点 问题：为什么已有索引还不够？ 回答：B+树索引只能保证第一列有序。当 WHERE 固定了第一列的值后，剩余行的第二列仍然保持插入顺序，而非目标字段的排序顺序。
2. COUNT(*) vs COUNT(1) vs COUNT(column)
-- 三者语义不同，但在 InnoDB 中 COUNT(*) 最快
SELECT COUNT(*) FROM users; -- 统计行数（忽略 NULL）
SELECT COUNT(1) FROM users; -- 同 COUNT(*)（InnoDB 优化相同）
SELECT COUNT(email) FROM users; -- 统计 email 非 NULL 的行数
SELECT COUNT(DISTINCT city) FROM users; -- 去重计数
-- InnoDB 优化细节
-- COUNT(*) 直接扫描聚簇索引的最左叶子页开始计数（O(N)）
-- COUNT(column) 除了扫描还需要检查是否为 NULL → 稍慢
-- COUNT(DISTINCT) 需要额外排序/去重 → 最慢
graph TB
subgraph "性能对比 (由快到慢)"
A["COUNT(*)<br/>只数行数"] -->|快 10\%~30\%| B["COUNT(1)<br/>优化器等价"]
B -->|稍慢| C["COUNT(col)<br/>需判 NULL"]
C -->|最慢| D["COUNT(DISTINCT col)<br/>需排序去重"]
end
style A fill:#00D866,color:#fff
style B fill:#7BED9F,color:#000
style C fill:#FFA500,color:#fff
style D fill:#EE5A24,color:#fff
[!TIP] 结论
- 如果你只是想统计行数 → 用
COUNT(*)COUNT(1)和COUNT(*)在 InnoDB 中是完全等价的（优化器视为同一计划）- 不要在 COUNT 中放列名除非你真的要排除 NULL 值
问题思考：为什么
COUNT(*)不需要逐行判断字段值？因为 InnoDB 内部维护了行计数器，直接从聚簇索引叶子节点遍历即可。
3. TIMESTAMP 自动更新陷阱
CREATE TABLE events (
id BIGINT PRIMARY KEY,
name VARCHAR(100),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP -- ← 这里！
);
INSERT INTO events (name) VALUES ('Meeting');
-- created_at = 2026-05-16 10:00:00
-- updated_at = 2026-05-16 10:00:00
-- 即使只修改 name，updated_at 也会自动更新
UPDATE events SET name = 'New Meeting' WHERE id = 1;
-- updated_at = 2026-05-16 10:05:00 ← 自动更新了！
-- 但如果修改的是 created_at 呢？
UPDATE events SET created_at = '2026-01-01 00:00:00' WHERE id = 1;
-- updated_at 同样会自动更新为当前时间！
-- 这就是陷阱——修改任何字段都会触发 ON UPDATE
[!WARNING] 独立创建时间和更新时间
-- 推荐模式：created_at 不使用 ON UPDATE，updated_at 用 ON UPDATE CREATE TABLE good_events ( id BIGINT PRIMARY KEY, name VARCHAR(100), created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 不受 ON UPDATE 影响 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
[!TIP] 为什么修改 created_at 也会触发 ON UPDATE？
ON UPDATE CURRENT_TIMESTAMP作用于整行而非特定列。任何字段的更新都会让updated_at刷新为当前时间。这不是 bug，是 MySQL 的设计语义——"此行被修改了"。
4. VARCHAR 长度计算
-- utf8mb4 下，VARCHAR(n) 的最大字节数 = n × 4
-- MySQL 一行最大 65535 字节
-- ❌ 看起来合理，实际超限
CREATE TABLE bad_table (
col1 VARCHAR(16383), -- 16383 × 4 = 65532 bytes + 2 bytes length prefix = 65534 ✓
col2 CHAR(1) -- 1 × 4 = 4 bytes + overhead = ❌ 超出 65535 行限制
);
-- ERROR 1118: Row size too large
-- ✅ 解决方案
CREATE TABLE good_table (
col1 VARCHAR(16382), -- 留余量
col2 LONGTEXT -- 大文本走外存
);
[!WARNING] 关于 65535 的常见误区
- 65535 是行级别的硬限制（不含 TEXT/BLOB 等外部存储字段）
VARCHAR(n)需要 1~2 字节的长度前缀来记录实际数据长度innodb_large_prefix=ON时可突破到约 8KB（配合 DYNAMIC 页格式），但跨引擎兼容性差，不建议依赖问题思考：为什么 TEXT/BLOB 不计入行大小限制？因为它们的数据存储在溢出页（overflow pages）中，行内只保留一个 20 字节的指针。
5. LEFT JOIN 条件位置
-- ❌ 把 LEFT JOIN 的过滤条件放在 WHERE，等效于 INNER JOIN
SELECT u.name, o.amount
FROM users u LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid';
-- WHERE o.status 会把 LEFT JOIN 的结果过滤掉不匹配的行 → 变成 INNER JOIN
-- ✅ 正确：把条件放在 ON 子句
SELECT u.name, COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.amount ELSE 0 END), 0)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid';
-- ✅ 或者：过滤右表应该在 WHERE 中显式检查 NULL
SELECT u.name, o.amount
FROM users u LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid' OR o.id IS NULL;
[!TIP] LEFT JOIN vs INNER JOIN 的快速判断 口诀：左表的过滤条件放 WHERE（不影响左表完整性），右表的过滤条件放 ON（保留左表全量）。
问题思考：如果 WHERE 中的列来自两张表（
WHERE u.city = 'Beijing' AND o.status = 'paid'）会怎样？ 回答：此时 WHERE 先执行 JOIN，再对结果集做二次过滤。这仍然是 LEFT JOIN，只是部分数据被 WHERE 筛掉了——它不会退化为 INNER JOIN。
6. 隐式类型转换导致索引失效
-- phone 是 VARCHAR(20)
ALTER TABLE users ADD INDEX idx_phone (phone);
-- ❌ 传入了 INT，MySQL 自动把 phone 转成 INT 再比较
SELECT * FROM users WHERE phone = 13800138000;
-- EXPLAIN: type=ALL, Rows=1000000 → 索引失效
-- ✅ 始终传入字符串
SELECT * FROM users WHERE phone = '13800138000';
-- EXPLAIN: type=ref, key=idx_phone → 走索引
-- 类似陷阱
SELECT * FROM users WHERE id = '12345';
-- varchar 列传 int 同理，MySQL 会做隐式转换
[!WARNING] 隐式转换的核心规则
- MySQL 的转换优先级：INT > VARCHAR。当列类型为 VARCHAR、值为 INT 时，MySQL 会把每一行的 VARCHAR 值转为 INT再比较 → 索引失效。
- 反之（列是 INT、传入 VARCHAR），MySQL 会将传入值转为 INT → 索引依然生效。
- 结论：永远让参数类型与列类型严格一致。这是后端 ORM 框架最容易忽略的点。
7. datetime 的精度与范围
-- MySQL 8.0 默认 datetime(0) — 不带小数秒
CREATE TABLE t1 (dt DATETIME);
INSERT INTO t1 VALUES ('2026-05-16 10:30:45.123456');
SELECT dt FROM t1;
-- 输出: 2026-05-16 10:30:45 ← 微秒丢失！
-- ✅ 如果需要保留精度
CREATE TABLE t2 (dt DATETIME(6));
INSERT INTO t2 VALUES ('2026-05-16 10:30:45.123456');
SELECT dt FROM t2;
-- 输出: 2026-05-16 10:30:45.123456
graph LR
subgraph "DATETIME vs TIMESTAMP"
A["DATETIME<br/>存储: 8 bytes<br/>范围: 1000~9999<br/>不受时区影响"] --> B["适合业务时间<br/>如订单创建时间"]
C["TIMESTAMP<br/>存储: 4 bytes<br/>范围: 1970~2038<br/>受 server 时区影响"] --> D["适合审计字段<br/>如最后登录时间"]
end
style B fill:#00D866,color:#fff
style D fill:#FFA500,color:#fff
[!WARNING] TIMESTAMP 有上限问题 TIMESTAMP 的最大值是
2038-01-19 03:14:07。如果你的系统需要在 2038 年之后使用，务必改用DATETIME，否则会遇到无法插入数据的诡异 bug。这也是为什么很多团队统一只用 DATETIME。
8. AUTO_INCREMENT 删除后的断号问题
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50)
);
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie'), ('Dave');
DELETE FROM users WHERE id IN (2, 3);
-- 当前 id: 1, 4
INSERT INTO users (name) VALUES ('Eve');
-- Eve 的 id = 5（不会复用 2 或 3）
[!WARNING] 自动递增号的特性
- AUTO_INCREMENT 不会回收已删除的值。这是设计使然——保证全局唯一且有序追加写友好。
- 如果业务需要紧凑编号（如内部工号），必须手动处理（如重建表或使用序列表），但不建议在生产系统这样做。
- InnoDB 的 AUTO_INCREMENT 锁在内存中（mysql.auto_increment_helper 表），即使删除所有行也不会归零（除非 TRUNCATE）。
9. LIKE 前缀通配符导致全表扫描
ALTER TABLE products ADD INDEX idx_name (name);
-- ❌ 前缀通配符 → 索引失效
SELECT * FROM products WHERE name LIKE '%手机%';
-- EXPLAIN: type=ALL → 全表扫描！
-- ✅ 前缀固定 → 可以利用索引（最左前缀匹配）
SELECT * FROM products WHERE name LIKE '手机%';
-- EXPLAIN: type=range → 走索引范围扫描
-- ✅ 替代方案：使用全文索引
ALTER TABLE products ADD FULLTEXT INDEX ft_name (name);
SELECT * FROM products WHERE MATCH(name) AGAINST('手机');
[!TIP] LIKE 索引利用规则速查
模式 是否走索引 原因 'abc%'✅ 可定位起始位置 '%abc'❌ 未知起始位置 '%abc%'❌ 同上 建议：对中文搜索需求优先使用 Elasticsearch 等专业搜索引擎，不要依赖 LIKE。
10. 可重复读（RR）下的幻读陷阱
-- Session A -- Session B
BEGIN; BEGIN;
INSERT INTO orders ...; ← 成功！
COMMIT; COMMIT;
SELECT COUNT(*) FROM orders; -- 结果包含了 B 插入的行
sequenceDiagram
participant A as Session A
participant DB as InnoDB
participant B as Session B
A->>A: BEGIN
A->>DB: SELECT COUNT(*)
Note over A,DB: 读到 10 行
B->>B: BEGIN
B->>DB: INSERT row_11
B->>DB: COMMIT
A->>DB: SELECT COUNT(*)
Note over A,DB: RR 级别下仍读到 11 行<br/>（Read View 未刷新）
A->>DB: UPDATE ... WHERE id > 100
Note over A,DB: 间隙锁发现新行<br/>→ 阻塞等待 B 的事务释放
[!WARNING] RR 隔离级别的幻读是「有条件」的
- 普通 SELECT 不会看到其他事务的新数据吗？不一定。InnoDB 的快照读只读第一次创建 Read View 时的状态，但 DML（UPDATE/DELETE）会刷新视图并感知新行。
SELECT ... FOR SHARE / FOR UPDATE会使用 next-key lock 阻止幻读——这才是真正的「不可幻读」。- MySQL 的 RR ≠ SQL 标准定义的「完全隔离幻读」。这是最容易产生误解的地方之一。
11. 关联笔记
- hhs/MySQL/03-索引与查询优化/15-EXPLAIN 完全指南 — 通过 EXPLAIN 识别上述问题
- hhs/MySQL/03-索引与查询优化/14-联合索引与最左前缀 — 索引失效的典型原因
- hhs/MySQL/03-索引与查询优化/17-查询改写技巧 — 各种问题的改写方案