CRUD这四个字母几乎每个做后端开发的人第一天就会背但真正把“增删查改”写出艺术感、写出性能、写出安全性的人说实话不多。我见过太多上线之后才被慢查询拖垮的表也见过不少因为一条DELETE没带WHERE导致全表清空的惨案。这个系列我打算分上下两篇上篇先把最核心的插入、查询、更新、删除讲透下篇再讲事务、锁、批量优化和常见误操作防护。今天这篇我尽量用实际会遇到的业务场景来拆解而不是只贴语法。0. 动笔之前先把表和数据的准备做好很多人喜欢上来就写SQL我习惯先花几分钟把环境理清楚。CRUD说到底是围绕表结构展开的表设计不合理后面的增删查改全是坑。0.1 建库建表的正确姿势CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE shop;这里有个细节很多人不注意字符集一定要用utf8mb4而不是utf8。utf8在MySQL里最多只能存3个字节的字符像emoji表情这类4字节字符会直接报错或者变成乱码。我接手过一个老项目用户昵称里带个就写入失败排查半天才发现是建表时的字符集问题。表结构我用一个电商订单场景来举例这是CRUD最典型的应用场景CREATE TABLE t_user ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 主键ID, username varchar(32) NOT NULL COMMENT 用户名, phone varchar(20) DEFAULT NULL COMMENT 手机号, level tinyint(4) NOT NULL DEFAULT 0 COMMENT 会员等级 0普通 1银卡 2金卡, balance decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT 账户余额, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_username (username), KEY idx_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;几个关键选择说一下id用bigint unsigned而不是int。现在可能觉得够用但一旦数据量涨到几千万int的上限就很尴尬。与其后面做迁移不如一开始就留足余量。username加唯一索引这是业务上天然的唯一键既保证了数据一致性又为查询提供了索引。created_at和updated_at都设置默认值这样插入的时候少写两个字段更新的时候MySQL还会自动维护updated_at省掉不少手动操作。0.2 填充测试数据别用手工一条条插写教程或调试代码时我习惯用存储过程或者一段脚本生成测试数据这样数量可控、字段可控排查问题的时候也方便定位。DELIMITER $$ CREATE PROCEDURE init_test_data(IN num INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i num DO INSERT INTO t_user (username, phone, level, balance) VALUES ( CONCAT(user_, i), CONCAT(138, LPAD(i, 8, 0)), FLOOR(RAND()*3), ROUND(RAND()*10000, 2) ); SET i i 1; END WHILE; END$$ DELIMITER ; CALL init_test_data(1000);这里用CONCAT拼字符串、用LPAD补位比直接写死数据灵活得多。1000条数据足够我们后面演示各种查询场景了。1. INSERT语句的进阶玩法单条插入、批量插入与返回主键插入是CRUD的第一步但很多人在这一步就踩坑。我最常看到的写法是一条条循环插入性能差不说还容易触发锁竞争。1.1 基础插入与自增主键的返回问题INSERT INTO t_user (username, phone, level, balance) VALUES (zhangsan, 13800000001, 1, 100.00);插入之后如果业务需要用到新生成的id比如新增用户后要初始化他的订单你该怎么办如果是MyBatis可以在insert标签里加useGeneratedKeys和keyProperty让数据库自增主键回填到实体对象里insert idinsertUser parameterTypeUser useGeneratedKeystrue keyPropertyid INSERT INTO t_user (username, phone, level, balance) VALUES (#{username}, #{phone}, #{level}, #{balance}) /insert如果是纯JDBC用Statement.RETURN_GENERATED_KEYS拿到ResultSet再取主键。记着一个原则插入后立刻需要主键就用数据库自带的能力去拿千万别先按某个业务字段反查一遍多一次查询不说还可能在并发下查到别人的记录。1.2 批量插入一条SQL搞定而不是循环万次批量插入是提升写入效率最直接的手段。同样是插入1000条分1000次执行哪怕每次只花1ms也要1秒多而一条批量插入常常连10ms都不到差距非常明显。INSERT INTO t_user (username, phone, level, balance) VALUES (user_a, 13800000002, 0, 11.00), (user_b, 13800000003, 1, 22.00), (user_c, 13800000004, 2, 33.00);批量插入时几个实践要点每批数据量建议控制在500到1000条之间。太多的话单条SQL过大网络传输和事务日志都会带来额外开销。批量插入的总数据量要适可而止比如一次插5万条可能需要拆分成50批每批暂停一下。我见过一次插几十万条数据导致主从延迟爆表的案例。批量插入如果中途有一两条数据违反约束整批都会回滚。如果业务上允许“跳过错误行继续”可以用INSERT IGNORE不过要谨慎使用它会忽略所有种类的错误而不仅仅是重复键。1.3 有则更新无则插入ON DUPLICATE KEY UPDATE在用户注册、签到打卡、每日统计这类场景里经常遇到“这条记录存在就更新某些字段不存在就插入”的需求。传统做法是先查再判断代码写起来麻烦还有并发窗口期的风险。MySQL提供了ON DUPLICATE KEY UPDATE一句SQL就搞定INSERT INTO t_user (username, phone, level, balance) VALUES (zhangsan, 13800000001, 2, 888.00) ON DUPLICATE KEY UPDATE phone VALUES(phone), level VALUES(level), balance VALUES(balance);这条SQL执行时如果username唯一索引没有冲突就正常插入如果冲突了就把后面指定的字段更新掉。注意它的触发条件是任何唯一索引冲突不只是主键所以要确保你预期中的唯一键确实是唯一键别让其他唯一索引“意外触发”更新逻辑。顺带提一版MySQL 8.0.20以后的写法VALUES()函数被标记为废弃推荐用别名方式INSERT INTO t_user (username, phone, level, balance) VALUES (zhangsan, 13800000001, 2, 888.00) AS new ON DUPLICATE KEY UPDATE phone new.phone, level new.level, balance new.balance;2. SELECT的拆解艺术从简单查询到聚合与分页查询是整个CRUD里最灵活、也最容易出问题的部分。同样的数据查询语句写得不一样性能可能差出几十倍。这节我把SELECT按业务场景拆开讲。2.1 基础查询的几个习惯查全表SELECT * FROM t_user;查特定字段SELECT id, username, level FROM t_user;条件过滤SELECT id, username, balance FROM t_user WHERE level 2 AND balance 500;这几条简单但习惯上注意几点线上业务代码里SELECT *能不用就不用。表字段一多网络传输的数据量就上去了而且万一表结构变了SELECT *的结果集变化可能直接导致程序解析出错。查询时把真正需要的列写清楚既清晰又高效。WHERE条件的字段若有索引就能走索引查询若没有索引就是全表扫描。这张表我建了idx_phone那按phone过滤会很快如果按balance过滤就得全表扫了。能加LIMIT就先加LIMIT。哪怕你确定结果只有一条也写上LIMIT 1让MySQL知道没必要继续扫下去。2.2 ORDER BY排序排序字段到底走不走索引查询结果排序是刚需SELECT id, username, balance FROM t_user WHERE level 2 ORDER BY balance DESC LIMIT 10;这个语句有个隐藏问题level有索引吗没有。balance排序能走索引吗即使给balance建了索引因为前面有WHERE level 2的过滤MySQL也大概率不会单独用balance索引去排序而是先过滤再filesort。想让排序走索引一个方式是建立联合索引(level, balance)这样level过滤和balance排序都能走同一棵索引树。这就是后面第五章要讲的索引设计逻辑查询和排序往往是联合索引一起考虑的。如果数据量大且无法走索引排序优先考虑减少参与排序的行数。LIMIT写不写对排序的负担影响很大MySQL拿到LIMIT 10后排序过程中只需要保留前10条候选即可。2.3 聚合查询GROUP BY与HAVING的分工统计每个会员等级的人数、总余额SELECT level, COUNT(*) AS user_count, SUM(balance) AS total_balance, AVG(balance) AS avg_balance FROM t_user GROUP BY level ORDER BY level;如果想过滤聚合之后的结果比如只查人数超过300的等级SELECT level, COUNT(*) AS cnt FROM t_user GROUP BY level HAVING cnt 300;很多新手会把HAVING写成WHERE然后发现报错。记住分工WHERE是聚合前对原始记录过滤HAVING是聚合后对分组结果过滤。还有两个经典问题要强调ONLY_FULL_GROUP_BY模式MySQL 5.7及以后默认开启下SELECT的字段必须要么是GROUP BY里的列要么是聚合函数包裹的列。像SELECT username, COUNT(*) ... GROUP BY level这种写法直接报错因为同一个分组内username有多个值MySQL不知道该取哪个。COUNT(*)和COUNT(1)在InnoDB里性能没有本质区别别被网上的老谣言误导。真正有区别的是COUNT(某字段)它会跳过该字段为NULL的行。2.4 分页查询LIMIT越深越慢怎么破分页是最常见也最容易被性能坑到的查询SELECT id, username, level FROM t_user ORDER BY id LIMIT 10 OFFSET 0;上面的查询没问题但翻到后面SELECT id, username, level FROM t_user ORDER BY id LIMIT 10 OFFSET 990;这个查询其实需要扫描前1000行然后丢掉前990行OFFSET越大越慢。到100万页的时候一条分页查询拖慢整个接口一点不夸张。简单的优化思路是“延迟关联”或“基于索引的游标分页”。基于索引的游标分页适合App下拉加载这类场景记住上一页最后一个id下一页直接从它后面取SELECT id, username, level FROM t_user WHERE id 1000 ORDER BY id LIMIT 10;这个写法能直接用主键索引定位不管翻到多深性能都稳定。缺点是页码跳转会麻烦适合“加载更多”模式。延迟关联则适合传统页码分页SELECT t1.id, t1.username, t1.level FROM t_user t1 INNER JOIN ( SELECT id FROM t_user ORDER BY id LIMIT 10 OFFSET 900 ) t2 ON t1.id t2.id;先快速定位出页码范围内的主键再用主键回表拿完整字段可以显著减少回表次数。2.5 模糊查询要不要用LIKE什么时候该放弃SELECT id, username FROM t_user WHERE username LIKE user_1%;这种前缀匹配的LIKE是可以走索引的user_1%能利用普通索引做范围扫描。但下面这种SELECT id, username FROM t_user WHERE username LIKE %user_1%;前导通配符会让索引失效只能全表扫。如果业务上真的需要模糊匹配文本内容要么改用全文索引要么上专门搜索引擎在MySQL里硬扛这种查询数据量一大就是灾难。2.6 JOIN与子查询连接条件别写漏继续用用户和订单的场景。先补充一张订单表CREATE TABLE t_order ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, user_id bigint(20) unsigned NOT NULL COMMENT 用户ID, amount decimal(10,2) NOT NULL COMMENT 订单金额, status tinyint(4) NOT NULL DEFAULT 0 COMMENT 订单状态 0待支付 1已支付 2已取消, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;查询每个用户名下的订单数量SELECT u.username, COUNT(o.id) AS order_cnt FROM t_user u LEFT JOIN t_order o ON o.user_id u.id GROUP BY u.id, u.username;写JOIN时两个最常见的坑连接条件漏掉。ON o.user_id u.id写成WHERE o.user_id u.idINNER JOIN可能没太大区别但LEFT JOIN就会变异成过滤效果左表的主记录被过滤掉结果不符合预期。GROUP BY只写u.idSELECT里又带了u.username。MySQL的ONLY_FULL_GROUP_BY会报错但从逻辑上也确实不对。2.7 去重与条件聚合统计去重查询SELECT DISTINCT level FROM t_user;统计每个等级的用户数同时想看其中余额大于100的人数SELECT level, COUNT(*) AS total_cnt, SUM(CASE WHEN balance 100 THEN 1 ELSE 0 END) AS rich_cnt FROM t_user GROUP BY level;条件聚合用SUM(CASE WHEN ...)非常常用比先查出来再在应用层统计高效得多一次查询全部搞定。注意这里也用了COUNT配合过滤条件逻辑要写清楚。3. UPDATE与DELETE改错和删错是整个CRUD里最后悔的两件事插入和查询出问题最多性能差一些、报错重试一下。而UPDATE和DELETE一旦写错WHERE条件影响的是真实数据尤其在生产环境可能需要数据恢复才能补救。3.1 UPDATE的标准姿势与常见坑UPDATE t_user SET balance balance - 50, level 2 WHERE id 1001;这里几个要点balance balance - 50这种写法是原子操作不用先查出来再减。如果先查出来在应用层减去再UPDATE并发情况下可能产生丢失更新。一定要带上WHERE不带WHERE就是全表更新。哪怕是在测试库养成先SELECT看一眼的习惯也好确认要影响的行数符合预期再执行UPDATE或者DELETE。如果更新的字段涉及多个值可以用CASE WHEN批量更新减少SQL条数。这个后面批量场景再展开讲。更新后想确认影响了多少行在MySQL客户端里会返回Rows matched、Changed和Warnings三个值。有意思的是如果你把balance设置成和原来一样Changed会是0但Rows matched是1说明MySQL确实定位到了这行只是没有实际修改。这个特性可以用来判断记录是否存在。3.2 删除操作DELETE、TRUNCATE与DROP的边界删除数据有三种等级很多人混着用-- 删除符合条件的行 DELETE FROM t_user WHERE id 1001; -- 清空表但保留表结构 TRUNCATE TABLE t_user; -- 连表结构一起删除 DROP TABLE t_user;三个操作的区别值得背下来操作是否走事务能否回滚是否重置自增ID执行效率触发BEFORE/AFTER触发器DELETE走能回滚不重置慢逐行删除触发TRUNCATE隐式提交不能回滚重置为1快直接释放数据页不触发DROP隐式提交不能回滚表都没了极快不触发生产环境里TRUNCATE和DROP操作一定要慎之又慎。我见过有人把TRUNCATE当DELETE用的跑完才发现整表数据没了还好有备份。养成习惯执行TRUNCATE和DROP之前先在结构上确认表名、确认当前所在库最好再检查一遍环境标识。3.3 误删数据的急救思路从备份恢复到延迟从库删除操作的安全底线是“有备份有从库”。常见稳妥方案定时全量备份加binlog增量备份出问题后先恢复到最近全量时刻再回放binlog到误删之前的那一秒。有条件的话维护一个延迟复制的从库延迟5分钟到1小时左右。一旦误操作发生在延迟窗口里及时停止复制从库上还能找到误操作前的数据。具体恢复操作依赖备份工具和binlog篇幅有限不展开。但一句话必须说没有恢复预案的DELETE就是拿职业生命去赌。3.4 软删除业务上删不等于物理上删很多业务系统里的删除其实是逻辑删除也就是给数据打一个删除标记查询时自动过滤掉。ALTER TABLE t_user ADD COLUMN is_deleted tinyint(4) NOT NULL DEFAULT 0 COMMENT 0正常 1已删除;实际执行删除时UPDATE t_user SET is_deleted 1 WHERE id 1001;查询时默认过滤掉SELECT id, username, level FROM t_user WHERE is_deleted 0;软删除的好处是数据还能找回来审计追溯也方便。代价是每次查询都要记得带is_deleted 0条件一旦漏掉已删除的数据就会混入结果。这种场景适合在索引设计上把is_deleted纳入联合索引不然过滤条件也会被浪费掉。这个下篇讲索引时会细说。4. 实操排查CRUD语句在慢查询日志里的真实表现前面讲的都是写法问题但很多人在本地测试时感觉不到差别一上生产数据量一大问题就暴露了。这节用一个实际排查过程讲讲怎么通过慢查询日志看一条SQL到底慢在哪。4.1 打开慢查询日志先定位是哪条SQL慢通用配置如下slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ONlong_query_time 1表示超过1秒的SQL都会记录。设置完重启MySQL服务生效。然后查看慢查询日志tail -f /var/log/mysql/slow.log看到类似这样的记录# Query_time: 2.345s Lock_time: 0.001s Rows_sent: 10 Rows_examined: 1200000 SELECT id, username, level FROM t_user WHERE level 2 ORDER BY balance DESC LIMIT 10;关键信息是Rows_examined: 1200000说明这条SQL扫描了120万行最后只返回10行。这就是典型的全表扫描性能当然差。4.2 用EXPLAIN分析执行计划对可疑SQL执行EXPLAIN SELECT id, username, level FROM t_user WHERE level 2 ORDER BY balance DESC LIMIT 10;结果里几个核心字段要会看type如果是ALL代表全表扫描如果是ref或range说明用上了索引。key实际使用的索引名。如果为NULL说明没有可用索引。rows预估扫描行数数量级上的参考。Extra出现Using filesort说明排序没有走索引出现Using temporary说明用了临时表都是需要警惕的信号。结合上面这条SQLtypeALL加上ExtraUsing filesort基本可以确认过滤条件全表扫排序也在内存中执行。优化方向就是加联合索引(level, balance)ALTER TABLE t_user ADD INDEX idx_level_balance (level, balance);再跑一次EXPLAINtype变成refrows会大幅下降Extra里的Using filesort也消失因为level过滤后再按balance排序已经能直接利用索引的有序性。4.3 别被索引骗了有时建了索引也没走有时候索引建了但SQL写法导致索引没生效。举几个经典场景对索引列做了函数或运算WHERE DATE(created_at) 2024-01-01即使created_at有索引也用不上。改成WHERE created_at 2024-01-01 AND created_at 2024-01-02就能走索引。隐式类型转换phone列是varchar类型查询写成WHERE phone 13800000001数字类型和字符串类型比较MySQL会把字符串转成数字索引同样可能失效。前导通配符的LIKE前面讲过了%xxx%用不上索引。联合索引没用最左前缀。idx_level_balance建好之后如果只按balance过滤不按level过滤这个索引也用不上。这些坑排查起来都很扎心因为表结构看起来没问题问题全在查询写法上。这也是为什么我建议团队里的CRUD代码必须过一遍EXPLAIN不要等到上线后靠慢查询日志才知道哪里慢了。5. 索引与CRUD的相互影响为什么有些表越写越慢CRUD不只是“写出的SQL能不能跑通”背后还有一个隐形玩家叫索引。索引让SELECT变快但会让INSERT、UPDATE、DELETE变慢因为每次写入都要同步维护索引结构。这个平衡关系值得单独交代。5.1 索引如何加速查询又如何拖慢写入InnoDB的索引是B树结构。查询走索引等于从一棵平衡树上快速定位目标而不是从第一行开始逐行扫。但每次INSERT除了往聚簇索引里插入记录还要往所有二级索引里插入对应的索引项每次UPDATE如果更新了索引列还要同步修改索引DELETE同理。索引越多写入的维护成本越高。所以建索引的原则是给高频查询字段建不为“可能用到”而建。一张表建个四五个索引算正常建十几个就要反思了维护成本可能比查询收益还大。5.2 回表与覆盖索引的性能差异假设我们有idx_phone索引执行SELECT phone, level FROM t_user WHERE phone 13800000001;MySQL先通过二级索引idx_phone找到匹配的记录然后还需要根据主键去聚簇索引回表一次才能拿到level字段这个过程叫回表。如果把索引改成联合索引(phone, level)ALTER TABLE t_user DROP INDEX idx_phone; ALTER TABLE t_user ADD INDEX idx_phone_level (phone, level);再执行同样的查询level字段可以直接从索引树里拿到不需要回表这叫覆盖索引。覆盖索引能显著减少磁盘IO是查询优化的常用手段。代价是索引本身变大了写入维护成本也更高。嵌套业务场景里尤其要注意“为了某个查询加字段到索引里”的度。5.3 联合索引的最左前缀原则(level, balance)这个联合索引相当于建了(level)和(level, balance)两组索引。查询只要能命中最左列level就能用上这棵索引树。反过来如果查询条件只包含balance没有level就用不上这个联合索引。这就是最左前缀原则也是设计联合索引时最主要的约束。实践中怎么用这原则假设一个场景业务查询有时候按username查有时候按username created_at查有时候只查created_at范围。建索引时应该建(username, created_at)联合索引一个索引同时覆盖前两种查询如果建(created_at, username)那按username的查询就无法充分利用索引了。5.4 索引下推看似不相关其实影响UPDATE和DELETE的扫描范围MySQL 5.6引入的索引下推在很多场景里是个隐形加速器。比如联合索引(level, balance)情况下执行SELECT * FROM t_user WHERE level 2 AND balance 500;没有索引下推时MySQL先用level 2从索引里找到一批主键然后回表再在表上判断balance 500。有了索引下推balance 500这个条件直接下推到索引遍历过程中提前过滤掉大量不回表的候选行。这个机制对UPDATE和DELETE同样生效因为这些操作也要先定位符合条件的行。定位范围变小了锁定的行数也少了整体效率自然上去了。5.5 止血型优化一次UPDATE的锁范围为什么那么大处理一条UPDATE时如果WHERE条件没有索引InnoDB需要扫描所有行来判断哪些命中。扫描过程中InnoDB会给扫描到的行加锁锁的范围很可能比最终要更新的行大得多这就容易引发锁竞争和死锁。典型的错误示范UPDATE t_user SET level 2 WHERE balance 100;balance没有索引MySQL扫描一行锁一行最后虽然只更新一部分行但锁的范围可能覆盖了整个表。高并发场景下这条UPDATE会堵住后续其他行的写入。优化方式是先把目标行的主键查出来再按主键更新UPDATE t_user SET level 2 WHERE id IN ( SELECT id FROM ( SELECT id FROM t_user WHERE balance 100 LIMIT 1000 ) tmp );内层SELECT先利用主键或者全表扫出一个较小的ID集合外层UPDATE按主键精确定位锁的范围大幅缩小。这里嵌套一层SELECT是为了绕开MySQL不允许在更新同一张表的子查询里直接查询的限制。6. 事务边界与CRUD的原子性怎么保证一批操作要么全成、要么全不成CRUD里最隐蔽的问题往往不在单条SQL而在多条SQL之间的“一致性”。转账、下单、扣库存这类业务天然要求一批操作要么都成功要么都失败不能出现“扣了钱但没加余额”“扣了库存但没生成订单”这种状态。6.1 手动事务的写法与隔离级别的选择MySQL默认每条SQL都是自动提交的多条SQL之间没有原子性保证。需要手动包事务START TRANSACTION; -- 扣减余额 UPDATE t_user SET balance balance - 50 WHERE id 1001; -- 增加余额 UPDATE t_user SET balance balance 50 WHERE id 1002; -- 一切正常才提交 COMMIT;如果中间任何一步出错执行ROLLBACK回滚前面所有操作全部撤销。事务的隔离级别对CRUD影响很大。MySQL默认是REPEATABLE READ这个级别下普通查询是快照读一旦事务开始同一条SELECT多次执行结果一致不会看到其他事务未提交的数据。实际开发中记住几个经验事务要短不要在事务里做耗时的外部调用。事务越长持锁时间越久死锁概率越高。刚开启事务后先按主键或唯一键定位要操作的行让锁尽早加上、尽早释放。避免在事务里做“先查再改”的逻辑如果要用查询结果作为条件去更新尽量一条UPDATE直接写条件。6.2 死锁的常见成因与观察命令死锁的经典成因是两个事务互相持有对方需要的锁。比如事务A先更新订单1再更新订单2事务B先更新订单2再更新订单1。两者交错执行就死锁了。MySQL遇到死锁会自动回滚代价较小的事务但业务代码里应该主动避免死锁。最基本的法门是多个事务访问相同资源时按相同的顺序操作。比如规定所有更新都按主键从小到大执行死锁概率就会大幅下降。如果排查死锁用SHOW ENGINE INNODB STATUS;关注LATEST DETECTED DEADLOCK段里面详细记录了死锁涉及的两条SQL、锁类型和持有锁的位置。这个命令是排查死锁的第一入口。6.3 推荐写法把多条CRUD包进一个事务的边界里举个实际例子用户下单成功后要同时更新钱包余额和生成流水记录。如果把两条SQL分开执行不做事务一旦第二条失败用户余额已经扣了但流水没记上。包进事务后START TRANSACTION; -- 扣余额 UPDATE t_user SET balance balance - 30 WHERE id 1001; -- 记账 INSERT INTO t_balance_log (user_id, amount, type) VALUES (1001, -30, ORDER_PAY); COMMIT;注意一个细节如果balance - 30会导致负数需要在UPDATE里带条件UPDATE t_user SET balance balance - 30 WHERE id 1001 AND balance 30;通过受影响行数来判断是否扣款成功。如果Rows matched是0说明余额不足直接回滚。这种写法比先SELECT再判断再UPDATE更安全避免并发下两个事务同时读到余额充足的情况。7. 实战示例一个用户管理后台的CRUD组合拳前面把INSERT、SELECT、UPDATE、DELETE分别拆开讲了现在用一个完整的“用户管理后台”功能串起来模拟真实业务代码里如何组合使用这些操作。7.1 用户注册插入并返回新用户IDSTART TRANSACTION; INSERT INTO t_user (username, phone, level, balance) VALUES (new_user_01, 13900000001, 0, 0.00); SET new_id LAST_INSERT_ID(); INSERT INTO t_balance_log (user_id, amount, type, remark) VALUES (new_id, 0, REGISTER, 新用户注册赠送0元); -- 其他初始化逻辑可继续扩展 COMMIT;LAST_INSERT_ID()在同一个会话里返回的是上一次自增主键值多线程环境互不影响。用事务包裹保证用户和初始化流水一起写入。7.2 用户列表查询条件组合、分页、排序后台列表页最常见的需求搜索框里可能有用户名、用户等级、注册时间范围列表按注册时间倒序分页展示。SELECT id, username, phone, level, balance, created_at FROM t_user WHERE username LIKE CONCAT(user_, %) AND level 1 AND created_at 2024-01-01 AND created_at 2024-02-01 ORDER BY id DESC LIMIT 10 OFFSET 0;如果用户没有填用户名但填了等级和时间范围就需要动态拼SQL不要用一个固定的语句硬扛所有筛选条件。不同的条件组合走了不同的索引路径索引设计就得考虑到这些组合。比如用户经常按level created_at组合筛选联合索引(level, created_at)就是比单独给level和created_at建两个索引更合理。7.3 批量更新用户等级运营要把一批用户提升到某个等级条件可能来自一个ID列表UPDATE t_user SET level 2 WHERE id IN (1001, 1005, 1009, 1012);如果是基于某个条件的大批量更新比如把所有余额小于10的用户标记为普通用户建议分批执行而不是一条UPDATE更新全表UPDATE t_user SET level 0 WHERE id IN ( SELECT id FROM ( SELECT id FROM t_user WHERE balance 10 LIMIT 500 ) tmp );分批执行的好处是单次事务持锁时间短不影响其他业务读写日志回放也更好控制。如果这批更新半路失败可以断点续跑不会出现“前面更新了后面没更新”的两难局面。7.4 用户注销软删除与数据保留的平衡用户主动注销账户时直接物理删除风险太大涉及订单、流水、日志等关联数据。我的实践是软删除加数据脱敏START TRANSACTION; UPDATE t_user SET is_deleted 1, phone CONCAT(del_, id, 取消) WHERE id 1001; -- 同时处理关联的订单状态比如把未完成订单标记为取消 UPDATE t_order SET status 2 WHERE user_id 1001 AND status 0; COMMIT;手机号做了脱敏处理防止隐私数据继续裸露在库里但ID保留着后续审计还能关联。这种组合操作再次体现了事务的价值用户状态和订单状态要么一起变更要么都不变。8. 几个像毛细血管一样影响CRUD的细节习惯最后分享一些零散但非常重要的实操习惯。很多线上事故不是大设计出问题而是小细节没注意。8.1 SQL关键字风格统一读代码的人会感谢你关键字用大写还是小写MySQL不区分但团队协作时最好统一。我习惯关键字全部大写表名、字段名小写。这样一大段SQL扫过去视觉层次清晰哪里是关键字、哪里是表名一眼可知。SELECT id, username FROM t_user WHERE level 2;比select id, username from t_user where level 2;看起来清爽尤其SQL复杂时优势更明显。8.2 使用参数化查询别拼字符串在应用程序里写SQL凡是涉及用户输入的地方一律用参数化或预编译比如JDBC的PreparedStatement、MyBatis的#{}不要用${}拼接字符串。// 推荐 String sql SELECT id, username FROM t_user WHERE username ?; PreparedStatement ps conn.prepareStatement(sql); ps.setString(1, inputUsername);// 危险 String sql SELECT id, username FROM t_user WHERE username inputUsername ;后者一旦用户输入 OR 11这样的内容SQL语义就被篡改了。参数化不仅能防注入还能让MySQL复用执行计划性能也更好。MyBatis里#{}是参数化${}是直接替换字符串后者要尽最大可能避免尤其是动态排序字段、动态表名这类场景必须做白名单校验。8.3 定期清理无用的索引和冗余索引索引不是越多越好。一张表里(phone)和(phone, level)这两个索引前者就是冗余的因为后者已经覆盖了前者的功能。定期通过这样的查询找出重复索引SELECT s1.table_name, s1.index_name, s2.index_name AS redundant_index FROM information_schema.STATISTICS s1 JOIN information_schema.STATISTICS s2 ON s1.table_name s2.table_name AND s1.seq_in_index s2.seq_in_index AND s1.index_name ! s2.index_name WHERE s1.seq_in_index 1;清理冗余索引能让写入更快、占用空间更小。8.4 给CRUD语句建立回归测试集SQL也是代码也会随业务迭代而调整。团队内部可以维护一个小的SQL回归测试集每次表结构变更或SQL改写后跑一遍测试集确认查询结果和预期一致。尤其是加了索引、改了查询条件后有些SQL的返回顺序可能变化业务上如果依赖了返回顺序就会悄悄出bug。这里不是让你写复杂的自动化测试哪怕把常用SQL存成一个文件定期手工跑一遍核对都能避免很多低级回归。写在最后的实操体会CRUD看着简单真正写好需要理解背后的数据结构、锁机制、执行计划。我自己的习惯是任何一条要上生产的SQL哪怕再简单都先跑一下EXPLAIN确认type不是ALL、rows没有大到离谱再允许它进入代码库。这个系列的下篇会继续讲事务隔离级别实战、死锁排查、批量写入优化、慢查询治理以及常见的误操作防护方案。到时候见。