哪个后端开发没被 MySQL 的增删改查虐过几回平时写接口最常用的就是这四类 SQL 操作但越基础的东西越容易踩坑批量插入怎么提速误删整表怎么恢复UPDATE 为什么执行半天不结束线上数据量一大SELECT 直接教做人。这篇就把 MySQL 表级操作里的增删改查从头到尾捋一遍重点说清楚每个操作背后的执行逻辑、去哪查状态、怎么写才稳适合刚入行的开发、正在做毕设或想补齐基本功的读者。全文不求炫技全是实际项目里能直接拿去用的东西。1. 先说清楚增删改查到底在操作什么1.1 DDL 和 DML 的分工别搞混很多人习惯把“增删改查”理解成对表里数据行的操作但在 MySQL 里有一个概念必须先掰扯清楚对表结构的操作叫 DDLData Definition Language对表数据的操作才叫 DMLData Manipulation Language。咱们常说的增删改查严格讲只是 DML 里的四类操作而标题里“MySQL 表的增删改查”这个表述实际覆盖了两层意思对表本身的操作建表CREATE TABLE、改表结构ALTER TABLE、删表DROP TABLE这是 DDL 的范畴对表内数据的操作插入INSERT、删除DELETE、更新UPDATE、查询SELECT这是 DML 的范畴。我会把重点放在 DML 的四类操作上因为这是日常开发里使用频率最高的语句。但开头先提 DDL是因为一个前提条件绕不开想往表里存数据得先有一张合理设计的表。尤其是新手拿到需求先别急着敲 INSERT先把字段类型、主键、索引规划好后面能省一半的返工时间。顺便提一个很多人忽略的点DDL 在 MySQL 8.0 之前大部分操作会锁表比如给一张线上大表加字段ALTER TABLE 可能把整张表锁住几十分钟期间所有读写都堵住。8.0 版本开始支持很多在线 DDL 操作但也不是所有场景都能瞬间完成。所以生产环境改表结构前一定要看版本并且在低峰期操作。1.2 数据操作的执行路径理解后就不会慌写一句 UPDATEMySQL 底层大概要经历这些环节客户端把 SQL 发给服务端服务端先做语法解析再走查询优化器决定用哪个索引、哪种连接方式真正执行然后调用存储引擎接口去内存和磁盘里找数据找到后按事务规则记录日志最后才把结果返回给你。理解这条执行路径的好处在于排查慢 SQL 时你知道去哪个环节找问题。比如一条 SELECT 特别慢可能是没走索引优化器选错方案也可能是内存里数据没命中全去磁盘扫描了。一条 UPDATE 执行半天可能是锁等待也可能是扫描行数过多。后文排查章节会展开说这里先把“执行是有链路”的这个概念种下后续所有优化思路都建立在这上面。2. 建表是地基比增删改查更重要的前期设计2.1 字段类型选错了后面全是坑既然先说表就从一个我实际踩过的大坑讲起字段类型选错。之前接手一个模拟项目X业务里要记录用户的手机号同事直接建成了 FLOAT 类型。电话号码存到 FLOAT超过一定长度就会变成科学计数法尾部几位全丢失更别说手机号前导零根本存不了。后来排查数据对不上才发现是表结构设计的问题。这个案例说明一个总原则存什么数据先想清楚它的本质再选类型。整数类型INT、BIGINT别用 VARCHAR 存数字除非是那种不参与计算的编码。用 VARCHAR 存数字排序时会按字典序排1、10、2 这种顺序会让你怀疑人生小数类型DECIMAL千万不要用 FLOAT/DOUBLE 存金额。二进制浮点数的精度问题在金额计算上会直接算出差几分钱的结果做财务相关功能时这是严重事故字符串类型VARCHAR 必须指定长度虽然 8.0 之后长度单位是字符不是字节但超长字符串还会导致索引失效和存储膨胀日期时间类型DATETIME 还是 TIMESTAMP 要按业务选时区敏感的场景TIMESTAMP 相对方便但它的范围到 2038 年就到期了别存“遥远未来”的数据。另外一个常被忽略的点字段要加 NOT NULL 约束并设置 DEFAULT 值。很多人建表时偷懒字段全都不设置默认值结果应用层少传一个字段就会收到数据不存在的报错。与其在代码里各种判空不如在表结构层面先把规则定死。2.2 主键和索引怎么规划直接影响后续三条语句主键的选择直接影响 INSERT 的性能和查询的速度。InnoDB 是聚簇索引组织表数据行本身按主键顺序物理排列所以主键建议用自增整数或者对插入友好分布的值。如果用 UUID 做主键每次插入都可能触发页分裂批量插入性能会明显下降而且主键长度大每一层索引都会膨胀占用的内存也更多。索引不是越多越好。索引多写入时维护成本就高每插一条数据都要同步更新所有索引。所以基本原则是高频查询的分支建索引低基数字段比如性别不适合建索引复合索引要遵循最左前缀原则。实操建议建表完第一时间用 SHOW INDEX FROM 表名 检查索引状态别等数据量大再去亡羊补牢。表结构文档也要同步维护字段含义、类型、索引策略都写清楚不然三个月后你自己都看不懂当初为什么建这个索引。3. 增INSERT 语句的进阶用法与提速方案3.1 单条插入与批量插入的本质区别先说最基础的单条插入INSERT INTO user (name, age, email) VALUES (张三, 25, zhangsanexample.com);新手以为单条插入和批量插入只是写法不同其实性能差距极其悬殊。每一条 INSERT 都要经历事务提交、索引更新、日志写入如果你用循环一条条插等于把上面这一整套重复执行一万次。改成批量插入INSERT INTO user (name, age, email) VALUES (张三, 25, zhangsanexample.com), (李四, 30, lisiexample.com), (王五, 28, wangwuexample.com);一条 SQL 插入几百上千行日志binlog、redo log写入次数、客户端与服务端的交互次数、SQL 解析次数都大幅减少实测插入两万行数据批量方式比循环逐条插入快几十倍。我做过一个数据初始化任务最开始逐条插跑了两分多钟改成批量 500 条一组提交压到几秒钟。但批量插入也不是 SQL 越长越好。一次插入行数过多单个事务过大binlog 和回滚段占用会飙升还可能造成主从同步延迟。经验值是单批 500 到 1000 行或者单批数据量控制在几百 KB 以内跑起来最稳妥。3.2 三条常用高级写法INSERT IGNORE、ON DUPLICATE KEY UPDATE、REPLACE INTO业务里边查边插或者重复提交的场景三条语法是必备工具。INSERT IGNORE 用于跳过冲突数据。平时导数据时目标表里可能已有部分记录你希望新数据正常插入重复的直接忽略不报错这就很合适INSERT IGNORE INTO user (id, name) VALUES (1, 张三), (2, 李四);ON DUPLICATE KEY UPDATE 是“存在就更新不存在就插入”典型场景是统计累加。比如记录用户每日登录次数当天首次登录插一条再次登录对 count 字段做累加INSERT INTO login_log (user_id, login_date, count) VALUES (1001, 2024-05-01, 1) ON DUPLICATE KEY UPDATE count count 1;REPLACE INTO 的语义是“碰到唯一键冲突就先删了再插”。听起来便捷但从数据安全和性能角度看要少用。因为冲突时它内部先做 DELETE 再做 INSERT意味着自增 ID 会变化、被删除行关联的外键可能出问题而且日志量更大。除非明确知道这个表的行可以任意丢否则不要习惯性用 REPLACE INTO。3.3 插入时一定要关注的三个隐藏风险第一事务长度。批量插入别在同一个事务里塞几十万条一旦中途出错要回滚回滚日志大得能把实例拖垮。最好是分批次提交每批单独 commit。第二字符集与编码。库、表、字段的字符集要统一否则插入中文容易变乱码而且做关联查询时字符集不一致可能直接导致索引失效。第三SQL 注入风险。应用层拼 SQL 时永远用预编译的占位符不要直接把用户输入拼进 INSERT 语句。数据库安全是底线关于这一点我在后文也准备了一节单独展开。4. 删DELETE、TRUNCATE、DROP 别再傻傻分不清4.1 三种删除方式的作用范围和速度对比“删数据”听起来简单实际存在三种完全不同的操作作用范围、性能、可恢复性都不一样操作作用对象删除内容可否回滚速度DELETE表内数据行按条件删除指定行事务内可回滚慢逐行记录日志TRUNCATE整张表清空所有数据行保留表结构不可回滚快直接释放空间DROP整张表表结构连同数据全部删除不可回滚最快物理移除DELETE 适合精确删若干行受 WHERE 条件控制。TRUNCATE 适合测试环境里快速清空一张表或者业务上确需清空全表并重置自增 ID 的场景。DROP 是删表属于高风险 DDL一旦执行表结构都没了只能靠备份恢复。一个我见过多次的低级事故本打算 DELETE FROM table WHERE id 1结果漏写 WHERE 条件一条语句把整表数据全清了。MySQL 默认 autocommit 开启DELETE 执行完自动提交想回滚都来不及。防呆手段两招一是在测试环境养成先 SELECT COUNT(*) 看影响范围的习惯二是生产环境把 SQL 客户端设置成“禁止不带 WHERE 的 DELETE/UPDATE”。4.2 DELETE 性能问题为什么删除几十万行会特别慢DELETE 不是只在表上擦掉一行记录那么简单。InnoDB 要记录变更到 undo log用于事务回滚标记行删除还要同步维护二级索引如果删除量很大binlog 也会产生大量记录。最麻烦的是删除大量数据后表空间不会自动收缩底层文件还是占着原来的大小这种情况就需要用 OPTIMIZE TABLE 来整理碎片。线上删除大表数据我常用的方式是分批删除DELETE FROM orders WHERE create_time 2023-01-01 LIMIT 5000;循环执行上面的语句每批删除几千行观察主从延迟和系统负载情况再决定要不要继续。好处是单次事务短锁持有时间短不阻塞其他业务。一次 DELETE 几百万行、事务长时间不提交会让其他 UPDATE、SELECT 长时间处于锁等待状态这是线上事故的常见来源之一。4.3 误删之后的保命措施希望你这辈子用不上真出过 DELETE 忘带 WHERE 的事故之后最深刻的体会不是“下次注意”而是备份和恢复机制的优先级。核心三条生产库必须开启 binlog并且设置 binlog_format ROWROW 格式记录了每一行变更前后的完整镜像误删后可以根据 binlog 精确定位并恢复定期做全量备份异地保存不要和数据库放在同一台机器恢复前先恢复到临时实例确认数据没问题再切回生产别直接在线上库做恢复操作。“先恢复再验证再切换”的顺序不能乱。我见过着急恢复在线上库直接执行 binlog 回放结果把后续正常写入的数据也覆盖掉事故二次放大。备份和恢复章节平时不惹眼但真到关键时刻它是唯一能救命的方案。5. 改UPDATE 的正确姿势与并发安全5.1 UPDATE 引发的“幽灵更新”问题UPDATE 的语法本身不复杂UPDATE user SET status 1 WHERE id 1001;但坑往往藏在“更新的临时值”里。一条常见的错误 SQL 是把旧值当作新值计算的基础UPDATE account SET balance balance - 100 WHERE id 1001;如果这条语句在高并发下对同一行频繁执行就可能因并发更新造成数据覆盖或重复扣款。举例说明A、B 两个事务同时读到 balance1000A 想把 balance 改成 900B 也想改成 900依据“先提交则获胜”原则B 基于旧值覆盖了 A。解决方案有几种最常用的是乐观锁UPDATE account SET balance balance - 100, version version 1 WHERE id 1001 AND version 1;如果影响行数为 0说明期间被其他事务改过应用层再重新读取重试。5.2 为什么 UPDATE 也会造成锁等待对同一行执行 UPDATEInnoDB 会给这行加行锁。如果业务里有多个事务长时间不提交其他事务更新这一行时就会一直卡在等待锁的状态界面表现就是接口超时。排查方法在后文会详细写这里先记住两个排查命令-- 查看当前正在执行的进程 SHOW PROCESSLIST; -- 查看事务锁等待情况8.0 可查 performance_schema.data_lock_waits SELECT * FROM performance_schema.data_lock_waits;一条实用经验只要遇到 UPDATE、DELETE 执行卡住第一时间先看是不是有未提交事务占着行锁。很多情况不是 SQL 本身慢而是被别人的事务堵住了。5.3 UPDATE 的几条实务铁律第一UPDATE 必须带 WHERE且 WHERE 条件尽量走索引。不带 WHERE 的 UPDATE 会全表更新不是测试环境千万别碰。WHERE 没走索引时MySQL 要扫描全表把每条记录都锁住检测是否符合条件锁范围极大容易拖垮整个库。第二大批量 UPDATE 同样建议分批执行。一次 UPDATE 几百万行跟 DELETE 一样会造成长事务、大日志、长时间锁表。拆成小事务循环执行每批观察执行时间。第三利用影响行数做幂等。UPDATE 即使条件匹配但值相同影响行数是 0应用层如果以此判断“没更新成功”就会出逻辑 bug所以判断成功与否更可靠的依据是“无报错且行数符合预期”。6. 查SELECT 写得好不好直接决定系统体验6.1 基础查询的骨架与执行顺序SELECT 是四类操作里最常用也最复杂的语法像搭积木子句顺序和内部执行顺序是两回事。书写顺序是 SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT但底层执行顺序是 FROM、WHERE、GROUP BY、HAVING、SELECT、ORDER BY、LIMIT。理解执行顺序的意义你想在 WHERE 里引用 SELECT 里定义的别名是做不到的因为 WHERE 先执行。对比-- 这样写会报错WHERE 里不能使用 SELECT 中的别名 SELECT name AS n FROM user WHERE n 张三; -- 正确写法 SELECT name AS n FROM user WHERE name 张三;GROUP BY 分组统计与 HAVING 过滤分组条件是常用组合。HAVING 在 GROUP BY 之后执行所以它可以引用聚合函数结果这也是它比 WHERE 高级的原因。6.2 统计查询中的常见误区COUNT、SUM 与 NULL 的纠缠COUNT 和 SUM 的差异新手容易搞混。COUNT(column) 不统计 NULL 值而 COUNT(*) 统计所有行SUM(column) 遇到 NULL 当成 0但如果整列全是 NULLSUM 返回 NULL 而不是 0应用层直接拿这个 NULL 去计算会出现意想不到的结果。GROUP BY 分组后往往还需要对总数做一个 WITH ROLLUP 或者用窗口函数算占比。举一个例子统计每个订单状态的数量和占比SELECT status, COUNT(*) AS total, ROUND(SUM(amount), 2) AS total_amount FROM orders GROUP BY status;如果后续要算占比一个写法是嵌套一层或者用窗口函数SELECT status, COUNT(*) AS total, COUNT(*) / SUM(COUNT(*)) OVER() AS ratio FROM orders GROUP BY status;这类写法在报表需求里特别常见建议熟练掌握。6.3 JOIN 查询的建议从驱动表与索引说起多表关联最容易出现的性能问题就是过度使用 JOIN。小表驱动大表、关联字段建索引是我使用 JOIN 时最重要的两个原则。举个例子订单表和用户表关联查询SELECT o.order_id, u.name FROM orders o LEFT JOIN user u ON o.user_id u.id WHERE o.create_time 2024-01-01 ORDER BY o.create_time DESC LIMIT 20;这条 SQL 在 user.id主键上建立关联是天然索引性能不会太差。但如果顺序反过来用 user 驱动 ordersorders.user_id 上没有索引那每次关联都要扫描全表数据量一大就会明显变慢。JOIN 还有一个隐藏风险——数据膨胀。如果关联字段在右表有多条匹配记录结果集行数会翻倍这在 SELECT 中可能看不到异常但一 ORDER BY 加 LIMIT分页就会出现重复数据。排查这类问题最直接的办法是先单独统计关联字段在右表的唯一性确认没有一拖多的情况再做 JOIN。6.4 分页查询LIMIT 深分页为什么越来越慢LIMIT 100000, 20 这种深分页为什么执行特别慢因为 MySQL 实际要把前十万行全部读出来再丢弃只返回最后二十行。越往后翻扫描行数越多。常用优化办法是延迟关联或基于游标。延迟关联的思路是先只查主键再回表SELECT t.* FROM orders t INNER JOIN ( SELECT id FROM orders WHERE create_time 2024-01-01 ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id;子查询里只查 id用不到回表然后外层再按主键关联取完整行性能提升明显。如果业务允许更推荐基于 id 或时间戳做游标分页也就是前端把上一次的最后一条记录的 id 带回来用 WHERE id 上次值 LIMIT 20 的方式翻页响应速度稳定。7. 索引对增删改查的影响与优化思路7.1 索引在四类语句中分别扮演什么角色索引不只是提查询速度的。对增删改查四类操作的影响很多人理解偏了。索引在 SELECT 里是加速扫描在 UPDATE 和 DELETE 里则决定了“先找记录”这一步的代价在 INSERT 里是写入时的维护负担。所以一条 UPDATE 使用主键值作为 WHERE瞬间就能定位行若 WHERE 用无索引字段MySQL 只能全表扫描一边扫一边加锁性能暴跌。这个认知直接影响建索引策略不是所有查询都适合建索引但如果业务里固定出现 WHERE user_id ? 的 UPDATE/DELETE这个字段就值得建索引否则每次更新都是一次全表扫。7.2 为什么 DELETE 慢跟索引也有关系DELETE 语句定位待删记录和 UPDATE 类似WHERE 条件能否走索引决定了删除的范围。比如按 create_time 批量归档数据create_time 上有索引删除就快否则全表扫描删除代价极其夸张。所以生产环境做数据清理一定要先看 WHERE 条件的字段是否已经建索引没建就先补索引再执行 DELETE顺序不要颠倒。7.3 索引设计检查清单区分度高、查询频繁的字段建索引复合索引遵循最左前缀查询条件的顺序要和索引字段顺序对齐索引字段不在表达式中做运算否则索引失效索引字段尽量避免用函数包一层例如 WHERE DATE(create_time) 2024-01-01 不会走索引应改成 WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00用 EXPLAIN 看执行计划确认 key 列有值type 不是 ALL。这里贡献一个我常用的自检 SQL 查询法复杂 SQL 写完后把 EXPLAIN 放在前面执行重点看 type、key、rows 三列。type 为 ALL 就说明全表扫基本需要优化。这不是难事但很多开发没有这个习惯遇到慢查询才发现问题那就晚了。8. 增删改查的安全底线SQL 注入与操作审批8.1 拼 SQL 的代价应用层最常见的 SQL 注入风险就是字符串直接拼进语句。比如SELECT * FROM user WHERE name 用户输入;当用户输入的是 OR 11时拼接后的 SQL 变成SELECT * FROM user WHERE name OR 11;这个条件永远为真等于把整张表的数据查出来了。数据泄露或者信息被人遍历很多就是从这种看似无害的拼接开始的。8.2 防御手段参数化查询是底线无论后端用 Java、Go、PythonORM 框架还是原生驱动都要使用参数化查询或者预编译语句。以 Java 的 JDBC 为例String sql SELECT * FROM user WHERE name ?; PreparedStatement ps conn.prepareStatement(sql); ps.setString(1, userName);占位符方式让数据库把用户输入当作值解析而不是当成 SQL 语法执行从根源上堵住注入。这个建议适用于所有增删改查语句不要只在 SELECT 上防御INSERT、UPDATE、DELETE 同样需要。数据安全问题没有后悔药。8.3 高危操作审批与灰度生产环境里不带 WHERE 的 DELETE、UPDATE 或 DROP TABLE应该有高于普通权限的管理员账号执行且操作前要在低峰期、做备份、写好回滚方案。很多团队发生了误删事故复盘时会发现一个问题开发同学拥有过高的生产库权限。权限最小化、操作双人复核、敏感 SQL 审批这三项执行到位能拦住绝大多数低级操作。9. 常见问题与排查技巧实录9.1 增删改查四大高频报错对照表操作报错信息原因与解决方案INSERTDuplicate entry 1 for key PRIMARY主键或唯一键冲突改用 ON DUPLICATE KEY UPDATE 或在插入前查重INSERTData too long for column字段长度不够调整表结构或检查数据来源DELETECannot delete or update a parent row外键约束阻止删除先处理关联表数据UPDATELock wait timeout exceeded行锁等待超时查未提交事务调整业务并发逻辑SELECTUnknown column in where clause字段名写错检查列名和表结构是否一致通用Table doesnt exist表名不存在或库名未指定SELECT DATABASE() 检查当前库9.2 实际排查案例一条 UPDATE 卡死整个接口某个模拟系统上线第二周用户反馈订单状态更新接口越来越慢最后直接超时。打开慢日志发现 UPDATE 语句平均执行时间从 30ms 涨到十几秒执行计划显示 WHERE 条件字段没有走索引全表扫描的代价随表数据量增长直线上升。原因是对该字段建索引的申请一直没通过DBA 说“数据量不大没必要”结果数据量涨到百万级问题暴露。处理方案业务低峰期给字段补建索引UPDATE 耗时立刻回到毫秒级。更深一层的改进是把这类更新操作走消息队列削峰填谷避免并发直接压到数据库。这个案例的教训是不能只看当前数据量要预判半年到一年的增长量。建索引的决定尽量在数据量小的时候做因为这时加索引成本低等数据大了再加锁表风险和耗时都会放大。9.3 慢查询定位三板斧第一板斧慢查询日志。MySQL 可以配置 long_query_time把超过阈值的 SQL 记录下来再分析哪里慢。设置方法SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;第二板斧EXPLAIN 看执行计划。重点关注前面提到的 type、key、rows 三列。type 为 range 或 ref 是相对理想的结果ALL 则有明显的优化空间。第三板斧SHOW PROCESSLIST 查看实时线程。线上突发性能问题先看有哪些线程在跑阻塞是否集中在某条 SQL 或某个事务上针对瞄定问题再动手不要上来就重启数据库。9.4 几条独家避坑经验我在多个项目里被同一类问题反复教训值得在这里重点强调不要在代码里循环执行单条 INSERT 或 UPDATE。应用层循环一万次每次都是一次网络往返和一次事务提交性能损失是数量级的。能用一条 SQL 或一次 API 批量搞定就不要写循环。MySQL 对大小写和空格敏感度比较迷惑人。表名在 Linux 下区分大小写Windows 下不区分所以代码里表名命名规则要统一最好全部小写加下划线。字段名虽然不区分大小写但写 SELECT 时记得用反引号把特殊字符字段括起来避免与关键字冲突。出现锁等待超时先找未提交事务而不是直接 kill 进程。用 SHOW PROCESSLIST 看到 Command 为 Sleep、Info 为空的连接要重点怀疑是不是有事务一直没提交。找到会话后再判断能不能杀掉。直接重启数据库是最后手段它会把所有内存缓存清空后续可能带来更大的可用性问题。关于批量操作我最常被问到“单批多少条合适”。我的答案不是固定的几百而是要看单条数据的大小。字段很多的大宽表一批 200 条可能就几 MB 了再过大会加重网络和内存负担。所以更实用的方法是按数据量控制批次而不是单纯按条数。这个习惯在数据迁移、归档项目里特别重要。10. 实操总结一套完整流程做下来才算真的会增删改查最后换个视角把前面分散的内容整合成一套完整流程。拿到一个新的表操作需求我通常按下面这个顺序执行第一步确认表结构与索引状态。先查字段类型、主键、索引看看有没有明显的设计问题。第二步判断操作类型和影响范围。如果是 INSERT想清楚单条还是批量插入冲突怎么处理如果是 DELETE/UPDATE先看 WHERE 条件是否走索引计划影响多少行绝不能直接执行。第三步编写 SQL 并在测试库执行 EXPLAIN 验证。尤其 UPDATE、DELETE、复杂 SELECT一条 EXPLAIN 能提前暴露全表扫描的问题。第四步生产库执行时遵循“备份先行、低峰操作、分批执行”三原则。数据量大的操作先备份然后分批次跑时刻观察系统负载。第五步操作完检查数据一致性。用 COUNT、SUM、抽样验证等方式对比业务逻辑预期确认没有漏改、误删。这套流程看着繁琐但一旦养成习惯就能在真正出问题之前拦截住大多数错误。增删改查是 MySQL 最基础的能力而基础能力扎实的人处理起线上疑难杂症往往比只会炫高级语法的人靠谱得多。