写这篇东西的起因是前两天有个转行做后台开发的朋友问我数据库增删改查到底要学到什么程度才算“会了”他刚把 CRUD 四个单词对应的 SQL 背熟结果发现面试官问的全是“联合索引怎么设计”“更新语句把全表锁了怎么办”“删除几百万行怎么不把库拖垮”一个比一个狠。我当时跟他说增删改查这四个字看起来是入门第一课实际上绝大多数生产事故、性能瓶颈、并发问题最后都归结到这四个操作没做扎实上。这篇文章我不打算给你念官方文档就按我这些年实打实维护过的库、填过的坑、复盘过的线上故障把数据库增删改查背后那些真实的技术点、设计取舍和排查思路完整拆一遍。适合刚入门想夯实基础的同学也适合写了两三年 CRUD 想搞明白“为什么”的后端开发者。我会把每条操作的原理、参数、坑、和真实场景全部串起来讲。1. 增删改查的本质这四个操作背后远不止四条SQL先别急着往下写 INSERT、SELECT咱们先把“增删改查”这件事在数据库体系里到底占什么位置聊透。它对应的四个动作——插入、删除、修改、查询在计算机术语里叫 CRUDCreate、Read、Update、Delete。但凡是个业务系统无论你做的是电商、后台管理系统、还是物联网数据平台跑在最底层的永远都是这四类操作。你看到的秒杀、订单流转、库存扣减、用户画像拆到数据库层面全部是一连串 CRUD 的组合。1.1 为什么说增删改查是所有数据库能力的试金石你可以把数据库想象成一个仓库。增删改查就是往仓库里放货、拿货、换货、处理废品这四件事。但一个仓库能不能高效运转取决于货架怎么摆、放货的时候要不要登记、同时来十个人取货怎么协调。对应到数据库就是索引、事务、锁、日志这四套机制而它们全部要通过增删改查体现出来。我在面试候选人的时候经常问一个问题一条 UPDATE 语句执行过程中数据库到底做了哪些事很多人答不上来。实际上它至少涉及解析 SQL、检查权限、走查询计划、定位数据页、加锁、修改内存中的缓存页、写 undo 日志、写 redo 日志、最后刷盘。你看一个最简单的“改”操作背后是存储引擎、事务系统、日志系统、缓冲池管理全部在协同工作。所以把增删改查学扎实等于打通了数据库的任督二脉后面学索引优化、读写分离、分库分表都会顺很多。1.2 业务视角下的CRUD没人关心你写了多少行SQL再往实际业务看增删改查从不是单纯的技术动作。产品要的是“用户下单后库存要扣减”对应的是 UPDATE 库存要“历史订单可追溯”对应的是 DELETE 变更为逻辑删除要“列表页秒开”对应的是查询改写和索引设计。换句话说每个增删改查的背后都承载着一个业务规则你在写 SQL 之前首先要搞清楚这个规则本身是否合理。举个最常见的例子订单删除功能。产品经理说“用户要能删除订单”你如果真写了一行物理 DELETE那么订单关联的支付流水、物流信息、售后记录全都没了后期财务对账、客服查证直接抓瞎。正确的做法看清业务本质——用户看到的“删除”其实是“我不想要它出现在列表里”底层应该做状态字段翻转这就是逻辑删除。这类问题你在写四条 SQL 的时候根本不会想到但只要一上线、一旦出事故回头一定栽在“没把业务想清楚”上。所以我一直建议任何 CRUD 动手之前先花 10 分钟问自己这个操作对应业务上的什么动作数据被改了之后有哪些下游在依赖它2. 核心操作细节每条语句背后都有它的脾性把增删改查拆开看你会发现每个操作的性格完全不一样。INSERT 是新建者风险在于重复和冲突DELETE 是危险分子一不留神就是大面积数据丢失UPDATE 是隐形刺客条件写不好全表遭殃SELECT 是流量入口撑住了就是功臣撑不住就是事故源头。下面我一个一个说。2.1 新增INSERT主键冲突、批量插入与事务边界INSERT 看起来最简单但有几个细节特别容易被忽略。第一是主键冲突。很多新手在插入数据时直接用业务字段当主键比如用用户手机号做主键一旦用户注销后重新注册或者同一手机号要绑定多个账号就直接撞主键了。实践经验是绝大多数业务表都应该使用自增主键或者分布式 ID业务唯一性校验单独用唯一索引控制不要把主键和业务字段混在一起。第二是批量插入性能。逐行 INSERT 在数据量小的时候无所谓但数据量过万之后性能断崖式下跌。原因很简单每条 INSERT 都是一次独立的数据库交互网络往返、事务提交、日志刷盘全都走一遍。改成批量插入之后一次连接执行多条插入性能能提升一个数量级。MySQL 里最常用的写法是INSERT INTO table (col1, col2) VALUES (v1, v2), (v3, v4), ...JDBC 层面还可以用rewriteBatchedStatementstrue参数让驱动自动合并批量语句。这条参数我记得当时调的时候很多人不知道加上之后批量写性能直接翻了好几倍。第三是事务边界。批量插入和事务的配合是个典型的收益与风险博弈一条一个大事务把所有数据都包住好处是某个失败可以全部回滚坏处是锁范围大、回滚日志膨胀一旦中途失败整个事务回滚的时间长得让人崩溃。我在实际生产里见过因为一个 50 万行的批量导入包在一个事务里跑了一个多小时最后因为一条脏数据整个回滚等于白跑。正确做法是把大事务拆成小批次每 500 或 1000 行提交一次同时保留一个批次号失败时只需要重跑没成功的批次。2.2 删除DELETE物理删除、逻辑删除与批量清理DELETE 是我在所有操作里最警惕的一个因为它不可逆。当然在生产环境通常有备份和 Binlog但恢复数据的成本和压力极大。我的习惯是删除类的操作永远先做两步第一步写 WHERE 条件后先 SELECT 一遍确认范围第二步在测试库或者事务里先执行再回滚验证一下影响行数。物理删除就是你通常理解的DELETE FROM table WHERE ...直接移除数据行。它的好处是表体积能真正降下来坏处是无法恢复而且如果删的是关联表的数据外键约束会直接报错或者产生孤立数据。逻辑删除则是不真正删行而是给数据加一个删除标记字段所有查询都自动带WHERE is_deleted 0条件。企业级系统的核心业务表我几乎一律推荐逻辑删除哪怕会有一定的存储冗余和查询条件复杂度但换来的追溯能力和数据安全是实打实的。再说说大批量删除。有些同学会说“数据不要了直接 DELETE 不行吗”行是行但当你要删几十万上百万行的时候事情就变了。一条 DELETE 在这个量级下会长时间持有行锁不断产生 Binlog 和 undo 日志还会拖慢主从同步甚至导致从库延迟到无法接受。我常用的做法是分片删除每次只删一个范围比如DELETE FROM table WHERE id BETWEEN ? AND ? LIMIT 1000循环执行每次循环之间 sleep 几秒给数据库喘口气。这个方法看着笨但在生产环境实测下来非常稳。2.3 修改UPDATE条件陷阱、索引使用与并发覆盖UPDATE 是事故高发区。最常见的低级错误是忘记 WHERE一行UPDATE table SET status 1直接把整张表的状态全部改了。这种错误每个 DBA 都遇到过我在代码评审里看到这种风险的第一反应就是要求必须加条件、必须严格限制影响行数。如果你的数据库支持可以开启安全更新模式MySQL 的sql_safe_updates1它会在没有 WHERE 或者 WHERE 没有走索引时拒绝执行 UPDATE/DELETE等于给你上了一道保险。另一个关键点是 UPDATE 和索引的关系。当你在 WHERE 条件里使用非索引字段时数据库为了找到需要修改的行只能进行全表扫描。全表扫描意味着把每一行都读一遍检查条件在 InnoDB 引擎下还会对扫描到的行加锁哪怕最终不满足条件的行也可能在扫描过程中被锁住并逐渐升级最终就是锁表。我一个真实的案例某交易系统在业务高峰执行一条全表扫描的 UPDATE直接把整张交易明细表锁了近一分钟下游所有查询全部阻塞。排查下来就是 WHERE 用的字段没有索引。所以 UPDATE 的 WHERE 条件字段必须保证有合适的索引。还有一个大家容易忽略的是并发覆盖问题。经典场景就是“先 SELECT 再 UPDATE”两步操作中两条并发请求读到了同一个初始值各自加一后写回结果只加了一次。解决思路有几种把读写合并成一条原子 SQL比如UPDATE inventory SET stock stock - 1 WHERE id ?或者用版本号做乐观锁控制UPDATE inventory SET stock stock - 1, version version 1 WHERE id ? AND version ?。前者适合简单计数后者适合需要做冲突检测的业务。2.4 查询SELECT索引、回表、分页与深分页优化查询是整个 CRUD 里最值得花时间的地方因为读操作的占比通常远高于写操作。一个优秀查询的关键是让它走索引而不是全表扫描。索引说白了就是一本按字母排序的通讯录你按姓氏查人直接翻到对应字母页就行全表扫描就相当于把整本书从第一页翻到最后一页效率天差地别。回表是另一个高频概念。当你用非主键索引二级索引查数据时索引叶子节点存的是主键值查到主键之后还需要再到主键索引里取整行数据这个过程叫回表。回表本身不是问题但如果查询结果集很大回表次数就会爆炸。所以实践中一般用覆盖索引来规避让查询的字段都包含在索引里这样连回表都省了。比如查询只需要id和name那就建一个(id, name)组合的二级索引数据在索引里就有直接返回。分页查询在数据量大以后会遇到经典的深分页问题。LIMIT 100000, 20这种东西看着人畜无害实际数据库要先把前 10 万行全部扫描出来再丢掉效率极低。我之前做过一个千万级订单表的分页接口用户翻到第 100 页之后接口耗时直接从 80ms 涨到 1.8 秒。解决深分页的主流方案有三种一是限制最大页码这个最简单但用户体验不好二是使用游标分页也就是根据上一页最后一条记录的 ID 作为下一页的起始条件WHERE id last_id ORDER BY id DESC LIMIT 20稳定而且高效三是用覆盖索引先查出主键 ID 再做联表SELECT * FROM table WHERE id IN (SELECT id FROM table WHERE ... LIMIT 100000, 20)MySQL 对这两步的优化实测也不错。3. 实操一套完整的增删改查落地流程理论聊了一堆现在我把一套增删改查从设计到实现完整走一遍。假设我们要给一个简单的商品系统做后端接口涉及商品表、库存表、订单表业务需求包括商品上架、修改价格、删商品、列表查询、下单扣库存。我会把每一步怎么做、为什么这么做讲清楚。3.1 从需求到表设计字段、约束与索引动手写 SQL 之前先把表结构设计好。商品表我会这样建CREATE TABLE product ( id BIGINT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(128) NOT NULL, category_id BIGINT NOT NULL, price DECIMAL(10, 2) NOT NULL, status TINYINT NOT NULL DEFAULT 1 COMMENT 1-上架 0-下架, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_category (category_id), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这张表里我做了几个重要设计决策。第一主键用自增BIGINT不用业务字段做物理主键同时预留后续分库分表时的主键扩展空间。第二价格字段用DECIMAL(10, 2)而不是FLOAT因为浮点数在二进制存储中本身有精度问题涉及钱的数据绝对不能用 FLOAT。第三status字段加索引因为“查询上架商品”“统计上架商品数”都是高频操作走索引能省很多时间。第四updated_at用了ON UPDATE CURRENT_TIMESTAMP这个特性会在每次 UPDATE 时自动更新时间戳省去应用层手动维护更新时间的成本。再配合一张库存表和商品表通过商品 ID 关联。CREATE TABLE inventory ( product_id BIGINT PRIMARY KEY, stock INT NOT NULL, version INT NOT NULL DEFAULT 0 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;库存表我故意没用自增主键而是直接用商品 ID 做物理主键因为库存和商品本来就是一对一的关系这个设计能减少一层无谓索引。version字段是预留给乐观锁用的下面下单扣库存的代码里你会看到具体用法。3.2 标准CRUD语句与连接池配置表结构定了之后增删改查的 SQL 就顺理成章了。新增商品INSERT INTO product (product_name, category_id, price, status) VALUES (无线蓝牙耳机, 101, 299.00, 1);修改商品价格UPDATE product SET price 259.00 WHERE id 1 AND status 1;这里特别注意UPDATE 的 WHERE 我带了两个条件id 1是定位目标行status 1是业务约束只有上架商品才能改价。带业务条件不是多此一举而是防止把下架的商品价格也改了这是一种防御式写 SQL 的习惯。删除商品这里我用逻辑删除代替物理删除UPDATE product SET status 0 WHERE id 1;查询上架商品列表带分页SELECT id, product_name, price FROM product WHERE status 1 ORDER BY id DESC LIMIT 20;查询单个商品详情SELECT p.id, p.product_name, p.price, i.stock FROM product p LEFT JOIN inventory i ON p.id i.product_id WHERE p.id 1 AND p.status 1;这些 SQL 看着平淡无奇但每个细节都有讲究。比如列表查询我只 SELECT 需要的字段而不是无脑SELECT *。SELECT *的问题在于第一多返回了created_at、updated_at这类前端根本用不到的数据白白增加网络传输量第二表结构一旦增加字段返回的数据集变大接口响应时间就会莫名变长第三在覆盖索引的场景下SELECT *会直接破坏覆盖索引优化因为索引里根本没存其他字段数据库必须回表才能取全数据性能直线下降。再来说连接池配置这是增删改查性能的一个隐藏变量。Java 里最常用的就是 HikariCP几个关键参数我通常这么配maximumPoolSize: 20 minimumIdle: 5 connectionTimeout: 30000 idleTimeout: 600000 maxLifetime: 1800000maximumPoolSize不是越大越好这个观念很多人拧不过来。连接池里的每个连接在数据库端都对应一个线程、一块内存如果应用有 10 个实例每个实例配 100 个连接那数据库就要准备 1000 个连接。连接数过高反而会让数据库线程反复切换上下文吞吐不升反降。我的习惯是从 10 开始压测逐步往上加找到曲线拐点。另外一个经验是maxLifetime要小于数据库自身的wait_timeout否则连接会在池里被数据库端静默断开应用还拿着失效连接去查就会出现偶发的“连接超时”报错。3.3 代码层CRUD的常见坑SQL 和连接池都就位后应用层代码里也有几个高频坑。第一个就是 SQL 注入。如果你还在用拼字符串的方式构造 SQL赶紧停下来。WHERE product_name 用户输入 这种写法用户输入一个 OR 11你的查询条件就变成了恒真全表数据直接裸奔。正确做法是使用预编译的PreparedStatement或者 ORM 框架的参数绑定让数据库把 SQL 结构和参数分开解析。第二个坑是 N1 查询。典型场景查出商品列表之后循环查库存。如果列表有 100 条商品就会产生 1 条列表查询 100 条库存查询数据库交互次数瞬间爆炸。解决方式是把库存查询合并成一次WHERE product_id IN (...)。我见过很多刚转行的同学在这个问题上栽跟头代码 Review 的时候一眼就能看穿因为数据库慢查询日志里充满了同一结构不同参数的 SQL。这条其实是整个增删改查实操里最经典的性能优化点。第三个坑是 ORM 框架的懒加载问题。比如 Hibernate 或 MyBatis Plus 的关联查询默认可能只在访问关联对象时才去查数据库于是又变成 N1。我的一般原则列表接口一律用查询语句手动指定关联查询或者使用批量查询不依赖懒加载特性。框架的便捷功能在性能面前都要让路不是业务简单就可以随便用。4. 并发与锁增删改查遇到人多的时候怎么扛单机单事务玩得转不算真本事真正的数据库增删改查难点全集中在并发场景。当你系统里同时有两拨人在改同一行数据时就需要一套规则来保证数据不出乱子。这一节我把并发控制完整梳理一遍。4.1 悲观锁与乐观锁两种理念的取舍先解释一个底层概念锁。数据库的锁是为了让并发操作“串行化”。两个请求同时改同一行如果不加控制后写的会覆盖先写的这个叫丢失更新。悲观锁的思路是“我改数据的时候谁也别想动”直接在事务里SELECT ... FOR UPDATE把目标行锁住事务提交后再释放。MySQL 的 InnoDB 引擎下FOR UPDATE会对命中的索引记录加排他锁其他事务的修改会被阻塞。比如库存扣减BEGIN; SELECT stock FROM inventory WHERE product_id 1 FOR UPDATE; -- 应用层判断 stock 0 UPDATE inventory SET stock stock - 1 WHERE product_id 1; COMMIT;这条方案能保证库存不会扣成负数代价是并发性能差。同一时间只有一个人能改这个商品的库存秒杀场景直接会堵到怀疑人生。乐观锁的思路则是“先随便改提交时检查有没有人抢先”。实现方式是版本号机制UPDATE inventory SET stock stock - 1, version version 1 WHERE product_id 1 AND version 5;执行之后检查影响行数如果等于 0说明 version 已经不是 5 了有人抢先修改过业务层需要重试或者提示用户稍后再试。乐观锁不影响并发读只在写提交的瞬间做校验所以适合读多写少、冲突概率低的场景。两种方案没有绝对优劣关键看业务冲突的概率和容忍度。库存这种高冲突场景用悲观锁稳定点赞数、浏览数这种冲突概率低的用乐观锁划算。4.2 死锁的产生与排查死锁是并发增删改查里最让人头疼的问题。它的本质是多个事务互相持有对方需要的锁形成循环等待。经典的例子事务 A 先锁了商品 1 再要锁商品 2事务 B 先锁了商品 2 再要锁商品 1两边都在等对方释放锁谁也不让谁就死锁了。MySQL 的 InnoDB 会自动检测死锁并将其中一个事务回滚让另一个继续。但你不应该把自己的系统安全寄托在数据库的死锁检测上因为它会在检测期间产生性能开销而且被回滚那个事务的代码如果没有做重试逻辑用户就会直接看到错误。预防死锁的经验主要是保持一致的加锁顺序多个事务需要锁多个资源时永远按照同样的顺序加锁。比如操作订单和库存约定所有事务都是先锁订单再锁库存就不容易形成循环等待。排查死锁时MySQL 下最常用的命令是SHOW ENGINE INNODB STATUS;输出里LATEST DETECTED DEADLOCK的部分会给出双方事务的 SQL、持有的锁和等待的锁信息非常详细。我处理过的一个典型死锁案例是两段统计任务同时在做“汇总订单更新商品总销量”一个按订单正序更新一个按订单倒序更新结果在商品行上互锁。最终解决方案就是统一更新顺序同时缩小事务范围把不必要的行排除在锁之外。4.3 事务隔离级别与增删改查的正确姿势事务隔离级别决定了多个事务同时读写时的可见性MySQL 默认可重复读REPEATABLE READOracle 和 PostgreSQL 默认读已提交READ COMMITTED。这个差异对增删改查的影响极其深远。在可重复读级别下同一个事务内两次SELECT看到的数据是一致的哪怕其他事务已经提交了修改。这在某些业务下是好事比如报表统计保证整个事务读到的是一个快照。但副作用是如果不对SELECT加锁它读到的“当前数据库的实际状态”可能不是最新的这对秒杀扣库存这类业务就是致命问题。所以那些把库存扣减写成“先 SELECT 检查库存再 UPDATE”的方案在可重复读下是有隐患的两个事务同时读到库存为 5各自判断”库存足够”然后都去 UPDATE最终库存变成 3 而不是 4。解决方式就是我前面说的要么FOR UPDATE加悲观锁要么用乐观锁版本号要么把判断和扣减合并成一条 UPDATE 的原子操作。另外一个实战建议是尽量保持事务短小。增删改查的事务里不要塞无关的远程调用、文件读写、外部 API 请求。事务越长持有的锁越多死锁概率越大回滚的成本也越高。正确姿势是在事务外先完成所有外部依赖事务内只做数据库操作。5. 常见问题与排查技巧实录最后这一节我把项目里积累的增删改查相关常见问题和排查手段整理成速查表希望对你有实际帮助。5.1 问题速查表现象可能原因排查手段解决方案UPDATE/DELETE 被卡住很久目标行被其他事务锁住SHOW PROCESSLIST查看阻塞会话找到持有锁的会话评估是否 kill优化事务时长系统 CPU 飙升接口变慢慢 SQL 全表扫描开启慢查询日志分析执行计划为 WHERE 字段加索引改写 SQL偶发“连接超时”报错连接池中连接被数据库端断开对比wait_timeout和连接池maxLifetime调整maxLifetime小于wait_timeout主从延迟严重大事务产生大量 Binlog查看从库Seconds_Behind_Master拆分大事务分批提交死锁日志频繁出现多事务加锁顺序不一致SHOW ENGINE INNODB STATUS;统一加锁顺序缩小事务范围列表接口翻页越翻越慢深分页LIMIT过大EXPLAIN 查看扫描行数使用游标分页或 ID 定位分页数据被误更新为同一个值UPDATE WHERE 条件没走索引查看执行计划的 type 是否为 ALL加索引、开sql_safe_updates5.2 排查思路先定位问题再动刀遇到线上增删改查变慢或者卡住我的排查顺序一般是这样。第一步看监控数据库的 QPS、CPU、连接数、慢查询数量四个指标先拉出来确定是整体性能下降还是某条 SQL 变慢。第二步抓慢查询日志MySQL 里设置long_query_time 1把超过 1 秒的语句捞出来。第三步用 EXPLAIN 看执行计划重点关注type字段是不是ALL全表扫描rows字段扫描了多少行Extra里有没有Using filesort文件排序或者Using temporary临时表这两个都是性能杀手。第四步按图索骥该加索引加索引该改 SQL 改 SQL该拆事务拆事务。我之前处理过一次线上事故就是按照这个顺序来的。系统每隔一段时间就会有一次 CPU 毛刺抓到的慢 SQL 是一条统计类查询EXPLAIN 发现它在对一个 500 万行的流水表做全表聚合GROUP BY还触发了文件排序。最终方案是在时间字段上加了组合索引并把GROUP BY的字段和索引最左前缀对齐SQL 从 2.6 秒降到了 120 毫秒CPU 毛刺彻底消失。这个案例印证了一个观点增删改查的大多数性能问题不是数据库不行是语句不行。我个人的习惯是在开发环境里就养成看执行计划的习惯写完一条复杂 SQL 顺手EXPLAIN一下多花半分钟能省掉以后很多线上告警的纠缠。拿不准索引是否生效就用EXPLAIN实际验证不要靠猜。最后再分享一个我踩过不少次坑后总结出来的纪律所有增删改查的 SQL只要走的是生产库一律先在测试环境和预发布环境完整跑一遍尤其是 DELETE 和 UPDATE必须先确认影响行数与预期一致。数据安全这件事从来不是靠运维兜底而是靠写 SQL 的人自己把住最后一关。把增删改查做到这个程度才真正算过关了。