MySQL增删改查避坑指南：索引失效、事务锁与慢查询排查

做后端开发这些年我见过太多人把增删改查当成入门门槛——背完SELECT、INSERT、UPDATE、DELETE四个关键字跑通一个带表单的 Demo就觉得自己会数据库了。真正上了生产环境才发现同样是这四类语句有人写出来跑三年不出事有人写出来上线第二天就收到告警。差别不在语法而在于对MySQL 增删改查背后的行为边界有没有清晰认知一条UPDATE忘了写WHERE会怎样、LIMIT深分页为什么越翻越慢、INSERT IGNORE到底忽略了多少东西、DELETE和TRUNCATE在事务里的表现为什么完全不同。这篇内容围绕 MySQL 数据库最核心的增删改查操作展开从建表定字段的地基讲起逐层拆到写入冲突、查询优化、更新风控、删除分级、事务锁与慢查询排查。适合刚接触 MySQL 想打牢基础的新手也适合已经能写业务代码、但对自己写的 SQL 心里没底的同学。我会尽量把每一步为什么这么写讲透而不是甩一堆语法让你自己悟。1. 增删改查的简单是假象先把操作心智模型立住1.1 语法过关和写对业务之间隔着一条河新手最容易产生的错觉是UPDATE users SET name x WHERE id 1这么简单能有什么坑坑就在于它太简单了简单到你会不假思索地写然后某天在拼接条件的时候少了个WHERE整张表的名字全变成同一个值。我见过真实的事故复盘一位开发在测试库上验证一个批量修正脚本语句写成了UPDATE t_order SET status 2本来想改某个时间段的订单条件在复制粘贴时丢了。所幸那次是测试库但同一个人如果在生产上敲下回车后果就不是重跑一遍能解决的了。所以我建议在脑子里先建立一个模型增删改查不是四个孤立的命令而是四个作用在一张有结构、有约束、有索引、有并发的表上的动作。你写的每一行 SQLMySQL 都要去做解析、选索引、加锁、写日志、改数据页。理解了这个链路你才会明白为什么有些写法快、有些写法慢、有些写法危险。这里有一组容易被忽略的对照关系值得先记住操作主要风险关键防线INSERT主键冲突、批量过大、重复数据唯一索引 冲突处理策略SELECT索引失效、深分页、结果集过大EXPLAIN 覆盖索引 游标分页UPDATE漏写条件、并发覆盖、锁范围过大先 SELECT 后 UPDATE 版本号DELETE误删、锁表、无法回滚逻辑删除 分批 备份这张表不是让你背而是让你意识到四类操作的风险点完全不同防守手段也完全不同。把这张表放在心里后面每一节都会对应到其中一行。1.2 建表定字段CRUD 的地基一步错步步错很多人学增删改查是从INSERT开始的我觉得顺序反了。你应该先学会建一张不容易出事的表再去往里写数据。因为字段类型一旦定错后面所有的增删改查都是带着病跑的。我按实际踩过的顺序说几个点。主键类型。自增主键用INT UNSIGNED还是BIGINT UNSIGNEDINT UNSIGNED的上限是 42 亿左右听起来够用但如果你有一张流水表、日志表按每天几十万条算几年就到顶了。到顶之后AUTO_INCREMENT会用尽插入直接报错而改主键类型的代价在千万级数据上几乎是灾难性的。我的默认选择是无脑BIGINT UNSIGNED多占 4 个字节换的是彻底不用操心。CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL COMMENT 业务单号, user_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 0, remark VARCHAR(255) NOT NULL DEFAULT , created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_created (user_id, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci;金额字段用DECIMAL而不是FLOAT/DOUBLE这一点没有商量余地。浮点数在二进制里存不下精确的 0.1做累加和比对时会出现0.1 0.2 ! 0.3这种事涉及钱的场景一次都不能错。DECIMAL(12,2)表示总共 12 位、小数 2 位整数部分最多 10 位对绝大多数业务足够。时间字段用DATETIME还是TIMESTAMPTIMESTAMP只占 4 字节但它的取值范围卡在 2038 年而且会随服务器时区做转换。如果你的系统要跨时区部署或者不想在 2038 年之前被叫起来改表就用DATETIME。另外created_at和updated_at用默认值自动维护能省掉大量在业务代码里手写时间的麻烦也避免了某些代码路径忘了更新时间的脏数据。字符集统一utf8mb4。老项目里经常看到utf8那是 MySQL 里的utf8mb3只支持最多 3 字节的字符存不了 emoji 和部分生僻字。用户昵称里带一个 emoji 就插入报错这种问题排查起来特别费时间因为报错信息不一定直观。还有一条经验VARCHAR的长度不要随手写 255。长度给大了不会占用实际存储空间VARCHAR是变长的但会影响到内存里的临时表行为和索引长度计算。更实用的是把业务约束定准比如手机号VARCHAR(20)、单号VARCHAR(32)让字段本身成为一层校验。2. INSERT写入环节最容易被想当然的四件事2.1 自增主键和业务主键别把两件事混成一件事新手常见写法是拿业务单号或者 UUID 直接当主键-- 不推荐 CREATE TABLE t_log ( id VARCHAR(36) NOT NULL, content TEXT, PRIMARY KEY (id) );这样写功能上没问题性能上有问题。InnoDB 的表数据是按主键顺序组织在 B 树里的主键是自增整数时每次插入都追加到最右边页分裂少、写入快、索引紧凑。而 UUID 是随机分布的每次插入都可能落在树的任意位置导致频繁的页分裂和随机磁盘写同时二级索引里存的也是这个 36 字节的字符串索引体积会膨胀好几倍。如果业务上确实需要 UUID 作为外部标识正确做法是主键仍然用自增BIGINTUUID 作为普通字段加唯一索引或者使用有序的 UUID 变体比如把时间戳放在前面的版本减少随机性。我个人的习惯是表的物理主键永远是自增整数业务唯一性交给UNIQUE KEY去保证。这样既满足了唯一性约束又不牺牲写入性能。2.2 循环单条插入和批量插入差距不是一点半点我见过一段代码导入一万条数据用了将近 40 秒原因是写了个for循环每条数据执行一次INSERT。每次INSERT都是一次网络往返、一次 SQL 解析、一次事务提交autocommit开启时这些开销跟数据本身的大小关系不大纯粹是固定成本叠加。改成批量插入后同样的数据量降到了一秒以内INSERT INTO t_order (order_no, user_id, amount, status) VALUES (NO20240101001, 1001, 99.00, 0), (NO20240101002, 1002, 199.50, 0), (NO20240101003, 1003, 39.90, 0);批量插入也不是越大越好。单条 SQL 的总大小受max_allowed_packet限制默认通常是 64MB超过会报错。另外一条超大 SQL 在主从复制时也可能带来延迟。我的经验值是每批 500 到 1000 条配合一个循环分批提交。如果导入量特别大还可以考虑LOAD DATA直接从文件导入它绕过了 SQL 解析层速度比批量INSERT更快。回看那次 40 秒到 1 秒的优化本质不是用了批量插入这个技巧而是搞清楚了时间花在哪里大部分时间不在写磁盘而在网络和事务提交的固定开销上。理解了这个你自然知道为什么合并语句有效、为什么分批提交比一次性大事务更稳妥。2.3 主键冲突的三种处理方式副作用各不相同插入数据时撞上唯一索引是高频场景比如并发下的用户注册、重复的订单回调。MySQL 提供了三种写法很多人只知道第一种用错了会埋雷。-- 写法一忽略错误 INSERT IGNORE INTO t_user (phone, name) VALUES (13800000000, zhang); -- 写法二替换 REPLACE INTO t_user (phone, name) VALUES (13800000000, zhang); -- 写法三冲突时更新 INSERT INTO t_user (phone, name) VALUES (13800000000, zhang) ON DUPLICATE KEY UPDATE name VALUES(name), updated_at NOW();INSERT IGNORE的坑在于它忽略的不只是重复键错误。字段超长会被截断后插入、类型转换失败会变成默认值、NOT NULL字段给NULL会被静默替换成默认值——这些本该报错的问题全被吞掉了数据静默出错事后极难追查。我在做数据质量排查时最怕看到的就是这种插进去了但内容不对的情况。REPLACE INTO的语义是先删后插删除旧行会触发该行上的DELETE触发器、影响外键级联还会让自增主键跳号。如果你有从表依赖这个主键行为会非常危险。它看起来像更新实际是删除加插入这个认知差是很多事故的源头。ON DUPLICATE KEY UPDATE是三者里语义最清晰的它真的做更新不删行、不改主键。唯一要注意的是在多唯一索引的表上它可能命中其中任意一个索引所以最好只在一个唯一索引上使用它。另外VALUES()函数在新版本中有被标记为过时的趋势写的时候留意下版本兼容性不行就用显式的别名写法。3. SELECT从能查出结果到查得准、查得快3.1 WHERE 条件的写法直接决定索引走不走查询慢的第一嫌疑人永远是索引失效。我排查过的慢查询里超过一半是条件写法把索引挡住了。下面这些情况是最常见的写法是否走索引原因WHERE user_id 1001是直接匹配索引列WHERE DATE(created_at) 2024-01-01否列被函数包裹索引失效WHERE phone 13800000000否字符串列传数字隐式转换WHERE name LIKE 张%是前缀匹配可用索引WHERE name LIKE %张%否前置通配符无法定位起点WHERE a 1 OR b 2视情况两侧都有索引才可能合并WHERE status ! 1通常否不等于的区分度太低优化器放弃第一条和第二条的对比特别值得说。DATE(created_at) 2024-01-01看起来非常自然但created_at外面套了函数MySQL 无法用索引定位只能全表扫描后逐行计算。改成范围查询就能用上索引-- 慢全表扫描 SELECT * FROM t_order WHERE DATE(created_at) 2024-01-01; -- 快走索引范围扫描 SELECT * FROM t_order WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00;隐式类型转换也是一样的道理。如果phone定义为VARCHAR你写WHERE phone 13800000000不带引号MySQL 会把列值转成数字再比较索引直接失效。这种错误在代码里非常隐蔽因为 SQL 能正常执行、结果也对只是慢不会报错。还有一个新手常犯的SELECT *。它除了传输多余列更容易导致无法使用覆盖索引。本来SELECT id FROM t WHERE user_id 1只查索引就能返回结果走SELECT *就必须回表读整行。在数据量大、并发高的场景下这个差异会被放大很多倍。3.2 深分页为什么越翻到后面越慢LIMIT offset, size的分页写法在业务里太常见了但它在翻到后面时会越来越慢。原因是 MySQL 必须先读出前offset size行再丢弃前offset行。翻到第 10000 页时它其实已经扫过了十万行数据然后扔掉。-- 翻到后面会越来越慢 SELECT * FROM t_order ORDER BY id LIMIT 1000000, 20;有两种改法。第一种是延迟关联先用覆盖索引查出主键再用主键回表SELECT o.* FROM t_order o INNER JOIN ( SELECT id FROM t_order ORDER BY id LIMIT 1000000, 20 ) t ON o.id t.id;这样子查询只扫索引、不回表扫描量虽然没变但每一行的代价小得多。第二种是游标分页也就是记住上一页的最大 id下一页从它之后开始SELECT * FROM t_order WHERE id 1000000 ORDER BY id LIMIT 20;这种方式每页的代价基本恒定是真正意义上的优化。代价是不能再跳到任意页码只能一页页往下翻。所以它适合无限滚动的列表不适合带页码导航的后台管理页面。选哪种取决于产品形态不是纯技术问题。3.3 JOIN 的连接顺序优化器不一定替你选对JOIN写出来能跑但驱动表选错了可能慢十倍。简单说MySQL 的嵌套循环连接是拿驱动表的每一行去被驱动表里找匹配。所以应该让小表做驱动表标准的说法是小表驱动大表。但小指的是过滤后的结果集小不是表的行数少。还有一个陷阱是LEFT JOIN的条件写错位置-- 条件写在 ON 里左表全保留右表不匹配时补 NULL SELECT * FROM t_user u LEFT JOIN t_login l ON u.id l.user_id AND l.type 1; -- 条件写在 WHERE 里会把左连接退化成内连接 SELECT * FROM t_user u LEFT JOIN t_login l ON u.id l.user_id WHERE l.type 1;第二种写法里l.type 1会把l.type为NULL即左表有而右表无匹配的行过滤掉结果等同于INNER JOIN。这个行为很多人不知道写了LEFT JOIN却得到少一截的数据还以为是数据不全。连接字段的类型也要一致。如果一边是VARCHAR、一边是INT同样会触发隐式转换导致索引失效。我在评审 SQL 时看到JOIN就一定会核对两侧字段类型这是最容易埋雷的地方之一。4. UPDATE一条忘写 WHERE 的语句能造成多大损失4.1 把 UPDATE 当成 DELETE 来防这是我在团队里反复强调的一条纪律执行任何 UPDATE 或 DELETE 之前先把 WHERE 条件拿到 SELECT 里跑一遍确认行数和样例数据。-- 第一步先看影响范围 SELECT COUNT(*), MIN(id), MAX(id) FROM t_order WHERE status 0 AND created_at 2024-01-01; -- 第二步确认无误后再改 UPDATE t_order SET status 9 WHERE status 0 AND created_at 2024-01-01;这个习惯的价值在于SELECT的结果会反直觉地救你。你以为只影响几百行结果查出几十万行这时候你就知道条件写漏了。如果是UPDATE直接执行你连后悔的机会都没有因为语句提交后数据已经改了。MySQL 提供了一个原生的保护开关SET SESSION sql_safe_updates 1;打开后UPDATE/DELETE如果没带WHERE或者WHERE里没用索引列语句会被直接拒绝。这个开关在客户端连接里可以默认配置我强烈建议所有人本地环境和测试环境都开着。生产上有些老系统不方便开那就靠流程来兜底——写变更脚本必须双人复核并且先在小范围数据上验证。4.2 影响行数不是你以为的那个数schema层面还有一个容易忽略的细节UPDATE返回的影响行数有两种语义。默认情况下MySQL 返回的是实际被修改的行数如果新值和旧值完全一样这一行不算被修改计数里不包含它。假设表里有 5 行status 0你执行UPDATE t SET status 0 WHERE status 0返回的影响行数是 0不是 5。如果你的业务代码依赖这个返回值做判断比如影响行数大于 0 说明更新成功就会误判成失败。要拿到匹配行数可以在连接参数里加useAffectedRowsfalse不同客户端驱动叫法不同或者干脆在业务上另想办法比如用乐观锁。4.3 并发覆盖两个人同时改同一条数据更新最隐蔽的问题是并发覆盖。A 和 B 同时读到一条记录balance 100A 改成 80 写回B 基于自己读到的 100 改成 90 写回最后余额是 90A 的扣减凭空消失了。这就是典型的丢失更新。解决方法之一是乐观锁加一个版本号字段UPDATE t_account SET balance balance - 20, version version 1 WHERE id 1 AND version 7;执行后检查影响行数如果是 0说明版本号已经被别人改过需要重新读取再试。这种写法把检查版本和更新数据合并成一条原子语句避免了中间被插队的可能。另一种是悲观锁用SELECT ... FOR UPDATE先把行锁住再改。它更直观但锁持有时间长并发高时容易排队还容易和其他事务交叉形成死锁。两种方案没有绝对优劣写多读少、冲突概率高的场景用悲观锁更稳读多写少用乐观锁更轻。我一般默认乐观锁除非业务明确要求强串行。5. DELETE、TRUNCATE、DROP三种删的分级差异5.1 三种删除方式的本质区别这三个词新手经常混用实际行为差别巨大方式删除对象能否回滚是否走事务自增计数速度DELETE FROM t WHERE ...符合条件的行可以是保留慢逐行删DELETE FROM t全部行可以是保留仍逐行删TRUNCATE TABLE t全部行不可以否隐式提交重置为 1很快DROP TABLE t表结构加数据不可以否无最快最关键的一条是TRUNCATE会隐式提交当前事务也就是说你在事务里执行它之前未提交的改动也会一并落盘。这意味着你以为可以ROLLBACK救回来实际并不能。DELETE则写 undo 日志在事务内可以回滚。所以判断标准很清楚需要条件删除、需要回滚、需要触发器联动就用DELETE要清空整表且不打算撤销才用TRUNCATE。清空生产表之前先确认有没有备份这不是废话是真有人栽过。还有一个实际问题是DELETE大范围数据时锁范围过大。一条删一百万行的DELETE会长时间持有大量行锁阻塞其他写入。正确做法是分批删除DELETE FROM t_log WHERE created_at 2023-01-01 ORDER BY id LIMIT 1000;在循环里执行每批之间稍作停顿把一次长事务拆成多次短事务。虽然总耗时可能更长但不会拖垮线上业务。5.2 逻辑删除的取舍该不该真的删业务上删除通常分两种含义物理上从表里消失还是对用户不可见。绝大多数场景应该是后者也就是逻辑删除加一个is_deleted字段或者在deleted_at里记时间。逻辑删除的好处是数据可追溯、可恢复出了问题还能查。但它会带来两个连续的问题需要提前想清楚。第一是唯一索引冲突。如果phone上有唯一索引用户注销后逻辑删除过段时间同一手机号重新注册插入会撞唯一键。解决办法是把唯一索引改成(phone, is_deleted)组合但这样同一手机号只能有一条删除记录。更彻底的做法是用deleted_at时间戳参与唯一索引删除时间不同就不会冲突。第二是索引选择性下降。数据只增不删表会越来越大而查询永远带着is_deleted 0这个条件区分度低帮不上索引什么忙。长期方案是定期归档历史数据到冷表或者用分区表按时间切分。这件事不做两三年后查询性能会明显退化。我的经验是核心业务表用逻辑删除日志类、临时类数据用物理删除加定期清理。别一上来所有表都加is_deleted那样只是把问题往后推。6. 事务与锁多条语句之间的一致性保障6.1 事务的边界划在哪里单条INSERT、UPDATE本身是原子的但业务往往是多步组合扣库存、生成订单、写流水。这三步要么全成要么全不成就需要事务包起来。START TRANSACTION; UPDATE t_stock SET qty qty - 1 WHERE sku_id 100 AND qty 1; INSERT INTO t_order (order_no, sku_id, amount) VALUES (NO001, 100, 99.00); INSERT INTO t_flow (order_no, type, amount) VALUES (NO001, pay, 99.00); COMMIT;边界的划分原则是事务里只放必须一起成功的写操作不要放网络请求、文件读写、耗时计算。我见过在事务里调用第三方接口的代码接口一慢事务就挂几十秒锁一直不放后面所有涉及这些行的请求全部排队。长事务还会导致 undo 日志膨胀影响整个实例的性能。还有一条容易忽略的MySQL 默认autocommit 1也就是每条语句自己就是一个事务。你可能没写BEGIN但代码里连续两次更新之间如果不是同一个事务中间出错就会留下半截数据。用框架的时候要确认事务注解真的生效了——方法内部自调用、异常被吞掉、多数据源切换都会让事务静默失效。6.2 死锁不是偶发故障是顺序问题死锁的经典场景是两个事务以相反顺序更新同一批行-- 事务 A UPDATE t_account SET balance balance - 10 WHERE id 1; UPDATE t_account SET balance balance 10 WHERE id 2; -- 事务 B UPDATE t_account SET balance balance - 10 WHERE id 2; UPDATE t_account SET balance balance 10 WHERE id 1;A 拿到 id1 的锁等 id2B 拿到 id2 的锁等 id1互相等待MySQL 检测到后会自动回滚其中一个报Deadlock found。根本的解决办法是统一加锁顺序。所有涉及这两个账户的操作都按 id 从小到大加锁。这样 A 和 B 的请求会自然排队不会交叉。其次是缩小事务范围、缩短持锁时间以及在应用层对死锁错误做重试——死锁被回滚是正常现象捕获到错误后重试一次通常就成功了不要把它当成系统故障。排查死锁时SHOW ENGINE INNODB STATUS里的LATEST DETECTED DEADLOCK段落会给出两个事务的具体 SQL 和等待的锁这是最直接的信息来源。看到那段输出基本能立刻判断是哪个顺序反了。7. 慢查询的排查链路从发现到定位到解决7.1 EXPLAIN 里真正要看的几个字段EXPLAIN输出字段一大堆新手容易看晕。我实际排查时只盯四个。type连接类型从好到坏大致是system const eq_ref ref range index ALL。看到ALL就是全表扫描看到index是全索引扫描这两个都要警惕。ref和range是正常业务查询的常见状态。key实际用到的索引。如果是NULL说明没走索引结合possible_keys看是没建索引还是建了没用上后者通常是写法问题比如函数包裹。rows预估扫描行数。这个数字乘上实际执行的次数就是大致的工作量。看到一个查询预估扫几十万行基本可以判定有问题。Extra这里的信息量最大。Using filesort表示排序没走索引需要额外排序Using temporary表示用了临时表常见于GROUP BYUsing index是好消息说明用了覆盖索引不用回表Using where表示在存储引擎返回后还要过滤。7.2 一个真实的排查过程前段时间有个列表接口偶尔超时日志里能看到执行时间从 20ms 跳到 3s。我按这个链路走了一遍。第一步开慢查询日志把阈值调到 1s抓到了具体 SQL。是一条带多条件筛选、ORDER BY created_at DESC加LIMIT的查询。第二步EXPLAIN看执行计划。type是ALLkey是NULLrows约 40 万Extra是Using where; Using filesort。全表扫描加额外排序问题很明确。第三步看WHERE里的条件。有三个筛选字段其中两个是高区分度的user_id、status一个是范围条件时间。所以建了一个联合索引(user_id, status, created_at)。第四步再跑EXPLAIN。type变成rangekey用上了新索引但Extra里还有Using filesort。原因是排序字段created_at在索引的第三位而user_id和status都是等值匹配理论上应该能用到索引顺序。查了才发现原来的语句里status用的是IN (0, 1)多个值导致后续字段无法保证有序优化器只好再排一次。第五步把排序需求拆开先按status单独查再合并或者接受这次排序但把LIMIT变小。最终选的是调整索引顺序为(user_id, created_at, status)让排序字段紧跟等值列status放到最后做过滤。调整后Using filesort消失接口耗时稳定在 30ms 以内。这次排查里最值得记住的是第四步索引建对了不等于用对了IN、范围条件、函数都会打断索引的有序性。光看有没有走索引不够还要看走索引之后是不是还在做额外排序。还有一个工具层面的建议慢查询日志不要只在出问题时才开平时就开着用pt-query-digest这类工具定期分析找出出现频次高、总耗时大的语句。单次慢不是最可怕的高频的略慢累积起来对数据库的压力往往更大。8. 我在实际项目里沉淀的几条操作纪律写到这儿把前面散落各节的实践原则收拢一下都是吃过亏之后固定下来的习惯可以直接照着执行。任何写操作上线前先在测试环境用生产数据量的子集跑一遍记录耗时和影响行数。这条听起来麻烦但它能挡住绝大多数本地好好的、线上一执行就锁表的问题。特别是加索引、改字段类型这类 DDL在小表上瞬间完成在大表上可能锁几分钟。所有涉及批量修改的脚本都写成可重入的。也就是重复执行结果一致中途失败可以接着跑。实现方式通常是带上状态条件比如WHERE status 0改完变成 1重跑时不会重复处理。这一点在故障恢复时价值极高。给每张核心表配上created_at和updated_at并且用数据库默认值自动维护。排查数据异常时这两个字段往往是最先提供线索的。有一次定位一个数据错乱问题就是靠updated_at的时间分布发现某个时间点之后写入的数据全都不对从而锁定了一次发版。查询接口尽量明确列名不用SELECT *。这个习惯短期看只是多打几个字长期看是在给自己留优化空间——当你需要加覆盖索引时明确的列名让这件事变得可行。最后说一个心态上的体会。增删改查这四个操作语法半小时就能学完但真正写对、写稳靠的是对数据会怎样变化、锁会锁住谁、索引会怎么走的持续判断。我到现在写一条要跑在生产上的UPDATE还是会先把条件拿去SELECT一遍。这不是不自信是把风险控制在可承受范围内的成本最低的方式。