
JOIN 的写法,决定优化器有多少选择
导语 INNER JOIN 和 LEFT JOIN 的结果差异人人都能说清楚,但为什么专家总说“能写 INNER JOIN 就别写 LEFT JOIN”?为什么 COUNT 和 C
增删改查的语法半小时就能学会,但运维事故很少是“语法写错”造成的,更多是“一条没走索引的 UPDATE 在高峰期锁了半张表”“批量插入没包事务导致刷盘几百次”“DELETE 完以为空间回来了其实没有”。
本篇是系列第二篇,接着第一篇的 test_ops 库和 users 表,往里写数据、改数据、删数据。每条 DML 都往下挖一层:它锁了什么、事务边界怎么划、空间到底回没回收。
START TRANSACTION;
INSERT INTO users (name, age, city) VALUES
('张三', 28, '北京'),
('李四', 32, '上海'),
('王五', 25, '广州'),
('赵六', 41, '深圳'),
('孙七', 30, '杭州');
COMMIT;
InnoDB 默认 autocommit=1,每条语句自成一个事务,每次提交都要保证 redo log 刷盘。5 条 INSERT 分开执行就是 5 次事务提交;包进一个显式事务只有 1 次提交。批量写入时这个差异直接体现在吞吐上。
同时,显式事务保证了原子性:要么 5 条全成功,要么全回滚,不会出现插了一半的中间状态。
查一下确认:
SELECT * FROM users;
5 条数据,id 从 1 到 5 自动递增。
UPDATE 锁了什么:取决于索引假设张三要从北京搬到成都。先查:
SELECT * FROM users WHERE name = '张三';
确认只有 1 行、id = 1,再改:
UPDATE users SET city = '成都' WHERE name = '张三';
这条语句在 InnoDB 下的锁行为取决于 name 上有没有索引:
REPEATABLE READ 隔离级别下会对扫描到的行加锁,接近锁全表。users 表的 name 上没有索引,所以这条 UPDATE 实际扫描并锁定了全表。测试库只有 5 行无所谓,生产环境几十万行就是事故。
执行前用 EXPLAIN 看访问路径:
EXPLAIN UPDATE users SET city = '成都' WHERE name = '张三';
看 type 列:ALL 是全表扫描,ref 或 range 才是走索引。改数据前先看有没有走索引,不是怕改错,是怕锁太多。 这是本篇最重要的一句话。
如果 SELECT 出来有多行同名“张三”,就应该改用唯一键 id 做条件。用唯一键做 WHERE 最安全,锁范围最小。
改完再查一次确认:
SELECT * FROM users WHERE id = 1;
DELETE 不等于空间回收插一条测试脏数据再删掉:
INSERT INTO users (name, age, city) VALUES ('测试用户', 99, '测试城');
SELECT * FROM users WHERE name = '测试用户';
DELETE FROM users WHERE name = '测试用户';
删除前先 SELECT 确认范围,这是基本习惯。但更值得知道的是:DELETE 只标记删除,空间不会立即还给操作系统。 InnoDB 的聚簇索引页会留下空洞,后续插入可能复用这些空间,但表文件本身不会缩小。
大量删除后要回收空间,需要 OPTIMIZE TABLE(本质是重建表)。而这是锁表级操作,生产环境要选低峰期,或者用 pt-online-schema-change 这类在线工具。知道“删了不等于小了”,才能正确评估一次大批量删除的影响。
-- 精确匹配
SELECT * FROM users WHERE city = '成都';
-- 模糊匹配
SELECT * FROM users WHERE name LIKE '%三%';
-- 排序 + 限制
SELECT name, age FROM users ORDER BY age DESC LIMIT 3;
LIKE '%三%' 前置通配符会导致索引失效,只能全表扫描。数据量小的时候无感,数据量大了就是慢查询来源。这也是为什么模糊搜索通常交给搜索引擎或全文索引,而不是在 MySQL 里用 LIKE '%...%' 硬扛。
DML 的语法简单,风险全在机制层面:锁范围由索引决定,事务边界影响性能和原子性,删除不等于空间回收,前置通配符让索引失效。下一篇建 orders 表,讲 JOIN 的写法如何影响优化器的选择空间,以及聚合函数里 NULL 的语义陷阱。
评论 0