企业官网建设流程全解析

1. 为什么"增删改查"这四个字值得单独拎出来讲

MySQL 的增删改查，说穿了就是 INSERT、SELECT、UPDATE、DELETE 四条语句。刚接触数据库的人会觉得这东西半小时就学完了，语法简单，敲几个例子就跑通了。但真在生产环境待过一段时间就会发现，出问题的往往不是复杂的事务隔离、不是主从切换、也不是分库分表，而是某个同事写了一句没带 WHERE 的 UPDATE，或者一条 LIMIT 深翻页的 SELECT 把数据库 CPU 打满。这篇文章面向的是刚上手 MySQL、或者写了一阵子 CRUD 但心里没底的同学，我会把这四个动作从语法、原理、性能、安全四个角度完整拆一遍，尽量做到看完就能照着自己练、照着改代码。

我写这篇东西的出发点很简单：网上讲增删改查的文章太多了，但大部分停留在"语法演示"这一层——建一张学生表，插两条数据，查出来，改一改，删掉，结束。这种文章看完还是不会用。真正要写对这四个操作，你需要知道字段类型怎么选、索引怎么配合、批量操作多少条一批合适、更新的时候怎么防止误伤、删除到底该物理删还是软删。这些才是日常写业务代码时天天要面对的问题，我下面会一条条摊开讲。

1.1 先复盘一次典型的批量更新事故

讲具体语法之前，先说说我早年踩过的一个坑，这个坑基本能解释清楚为什么 UPDATE 值得单独用一节来讲。当时是给一个内部管理系统做数据修正，需求是把某张订单表里状态为"待处理"且创建时间早于某个日期的记录，批量改为"已关闭"。我写的语句大概是这样的：

UPDATE t_order SET status = 'CLOSED' WHERE status = 'PENDING';

问题是，我在测试环境练手的时候忘了加时间条件，而本地库只有几十条数据，跑得飞快，看不出任何异常。等到这条语句被放进一个临时脚本、对着测试环境执行时，测试环境有几百万行历史数据，全部是待处理状态，一条语句直接把整个表刷成了关闭。更麻烦的是当时没有开 binlog 的行格式记录，恢复只能靠备份，丢了大半天数据。

这件事给我的教训不是"写 SQL 要小心"这种空话，而是三个非常具体的习惯：第一，任何 UPDATE 和 DELETE 语句，先把 WHERE 条件单独拿出来跑一次 SELECT COUNT(*)，确认影响行数和预期一致；第二，批量操作必须分批，每批几百到几千行，跑完看一批日志；第三，所有批量脚本必须包在一个显式事务里，先 BEGIN，跑完核对，再 COMMIT，中途不对就 ROLLBACK。这三个习惯我到现在都保持着，后面每个小节讲具体操作时都会再提到。

1.2 一条语句在存储引擎里到底走了哪几步

理解增删改查的性能差异，得先知道一条语句从客户端发出去到数据落盘，中间经过了什么。以 InnoDB 为例，一条 UPDATE 的执行大致是这么个链条：客户端通过连接协议把 SQL 发到服务端，解析器做词法语法分析，优化器根据统计信息选索引，执行器调用存储引擎接口，InnoDB 先在缓冲池（Buffer Pool）里找到对应数据页，如果页不在内存就从磁盘读进来，然后修改内存中的页，同时把这次修改以 redo log 的形式顺序写进重做日志，把旧值写进 undo log 用于回滚和 MVCC，事务提交时根据innodb_flush_log_at_trx_commit的设置决定何时刷盘。

这套机制解释了两件事。一是为什么 UPDATE 比 SELECT 慢得多——它要同时写 redo、写 undo、维护索引页。二是为什么"删除数据后表文件不变小"——InnoDB 删除只是把记录标记为删除、把空间标记为可复用，页内的空洞需要后续插入来填补，真正回收空间要靠重建表。这些底层行为直接决定了后面章节里很多写法的取舍，比如为什么大批量删除要分批、为什么 DELETE 之后要做 OPTIMIZE。

SELECT 这条路径相对轻，但也有它的讲究。它要走连接器做权限校验，走分析器、优化器，最后执行器按执行计划逐行取数据。如果查询走的是覆盖索引，引擎层直接从索引里返回需要的列，不用回表；如果走的是全表扫描，那就是一页一页读，数据量大时 IO 压力非常明显。这也是为什么 EXPLAIN 里 type 字段从const到ALL有那么大差别——它反映的正是这条路径走了多少弯路。

1.3 练习环境的准备思路

动手之前先把环境搭好，别在公司的测试库上练手，太危险。我一般用两种方式：一是本地装一个 MySQL 8.0，用 Docker 起一个容器最省事，映射 3306 端口，挂一个数据目录方便重启后数据还在；二是直接用一个云上的免费试用实例，好处是不占本地资源，坏处是网络延迟会影响你对批量操作耗时的判断。

客户端工具方面，命令行 mysql 客户端必须会用，因为线上出问题时往往只有命令行可用；图形化工具按个人习惯选一个顺手的就行，它的主要价值是看表结构、看执行计划比较直观。我自己的习惯是写脚本和小查询走命令行，需要反复调 SQL 的时候用图形工具，两边不冲突。

练习数据要造够量。几百条数据看不出索引的效果，也测不出分页的坑。建议至少造十万行级别，用存储过程循环插或者用脚本生成 CSV 再 LOAD DATA 导入都行，后者更快，也顺便练了批量导入。数据有了规模，后面讲索引、讲分页优化、讲分批更新，你才能真实感受到差别。

2. 建库建表：后面所有操作的地基

增删改查写得顺不顺，一半取决于表结构设计得对不对。我见过太多项目，字段类型随手选、字符集不统一、该建的索引没建，结果查询慢就怪数据库，其实问题在建表那天就埋下了。这一节把建库建表的关键决策过一遍，重点讲清楚每个选择背后的理由。

2.1 字符集、排序规则与存储引擎的选择

字符集这块只有一个建议：统一用utf8mb4，排序规则用utf8mb4_general_ci或 MySQL 8.0 默认的utf8mb4_0900_ai_ci。原因很实际，MySQL 里的utf8实际是utf8mb3，最多三个字节，存不了四字节的字符，比如某些特殊符号和生僻字，存进去要么报错要么变成问号。早些年很多系统用 utf8，后来做用户昵称功能时就撞墙了，只能整库改字符集，那个迁移成本非常高。

建库语句可以写成这样：

CREATE DATABASE demo_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

存储引擎基本没有悬念，选 InnoDB。它支持事务、支持行级锁、支持外键、支持崩溃恢复，这四点是业务系统的刚需。MyISAM 表锁、不支持事务，除非你在做只读的日志归档类场景，否则没必要考虑。有个细节要注意：建表时不写 ENGINE 会用服务器默认引擎，而不同环境默认值可能不一样，所以我在建表语句里习惯显式写上ENGINE=InnoDB，避免测试和生产行为不一致。

还有一个容易忽略的点是排序规则对比较和索引的影响。utf8mb4_general_ci里的ci表示大小写不敏感，也就是'abc'和'ABC'在 WHERE 条件里是相等的。如果你的业务要求用户名区分大小写，那要么换utf8mb4_bin，要么在查询时用BINARY强制比较。这个坑我在做登录功能时踩过——用户注册了Tom，用tom也能登录进去，产品经理觉得这是 bug，其实是排序规则在起作用。同一个库里不同表的排序规则不一致，做 JOIN 时还可能报排序规则冲突的错，所以统一是王道。

2.2 字段类型选型：从一张用户表说起

字段类型选错带来的问题往往是隐性的：存储空间浪费、索引变大、隐式类型转换导致索引失效。我拿一张常见的用户表举例，把每个字段的选型理由说清楚。

CREATE TABLE t_user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', username VARCHAR(32) NOT NULL COMMENT '用户名', phone VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号', age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄', balance DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '余额', status TINYINT NOT NULL DEFAULT 1 COMMENT '状态 1正常 0禁用', is_deleted TINYINT NOT NULL DEFAULT 0 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), KEY idx_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

主键用BIGINT UNSIGNED自增，不用INT。INT 上限二十一亿，看着够用，但订单、日志、消息这类表增长很快，真到上限再改主键类型是要停机的事。自增主键的好处是插入时顺序写，页分裂少；如果业务需要全局唯一 ID，可以用雪花算法生成，但注意那类 ID 是无序的，插入会造成随机写，建议配合innodb_buffer_pool_size和适当的分区策略。

金额字段一定用DECIMAL，绝对不要用FLOAT或DOUBLE。浮点数在二进制里存不下精确的十进制小数，0.1 + 0.2算出来是0.30000000000000004这种结果，拿来做账目对账会出现一分钱对不上的情况。DECIMAL(12,2)表示总共 12 位有效数字，其中小数 2 位，最大能存到十亿级别，对绝大多数业务够用。

时间字段用DATETIME还是TIMESTAMP，取决于你要不要自动时区转换。TIMESTAMP占 4 字节，存储时转成 UTC，读取时按会话时区转换，范围到 2038 年；DATETIME占 5 字节，存什么读什么，范围到 9999 年。我的习惯是业务时间统一DATETIME，配合DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP，就不用在代码里手动赋值了，也不会因为服务器时区配置不一致出现时间错乱。

状态字段用TINYINT而不是VARCHAR。一是省空间，二是方便建索引和做范围统计。有人喜欢用ENUM，我不推荐，因为加一个枚举值要改表结构，而且 ENUM 在排序时是按定义顺序排的，容易出反直觉的结果。

2.3 索引设计的第一版草稿

索引不是越多越好。每个索引都是一棵 B+ 树，插入、更新、删除时都要同步维护，写多读少的表上索引过多会明显拖慢写入。我的做法是先按查询需求列一张清单，再合并同类项。以用户表为例，典型查询有：按用户名登录、按手机号查用户、按注册时间做报表统计。那就对应三个索引：uk_username、idx_phone、idx_created。

联合索引要遵守最左前缀原则。比如你建了KEY idx_a_b_c (a, b, c)，那么WHERE a = ?、WHERE a = ? AND b = ?、WHERE a = ? AND b = ? AND c = ?都能用上，但WHERE b = ?单独查就用不上。所以设计联合索引时，把区分度高、查询频率高的字段放左边。这里说的区分度，就是COUNT(DISTINCT col) / COUNT(*)的值，越接近 1 越适合做索引前缀。

还有个细节是索引字段不要太长。VARCHAR(255)建索引，前缀部分占用空间大，索引树高，查询效率下降。如果确实要对长字符串做唯一约束或前缀查询，可以用前缀索引，比如KEY idx_name (username(16))，但要注意前缀索引不支持覆盖索引，也不能用于ORDER BY全字段排序。我在实际项目里的经验是：能用短字段建索引就别用长的，手机号、订单号这类天然短的字段最省心。

3. 插入数据：INSERT 的写法与批量优化

插入看起来最简单，但它是数据进入系统的第一道关，写法不对会在后面埋下很多麻烦。这一节从小批量写、冲突处理、大批量导入三个层面讲。

3.1 单条插入与批量插入的写法差异

单条插入的语法没什么可说的：

INSERT INTO t_user (username, phone, age, balance) VALUES ('zhangsan', '13800000001', 28, 100.00);

要点是两个：一是显式列出字段名，不要用INSERT INTO t_user VALUES (...)这种省略字段的写法。因为表结构一旦加字段或调整顺序，省略字段的语句就会报错或者把数据插到错误的列上，这种故障排查起来很费时间。二是字段顺序和值顺序要一一对应，写的时候对齐一下，比事后查错省事。

批量插入是重点。下面这种写法一次插入三行，比写三条 INSERT 快得多：

INSERT INTO t_user (username, phone, age) VALUES ('user01', '13800000011', 25), ('user02', '13800000012', 26), ('user03', '13800000013', 27);

为什么快？因为每条独立 INSERT 都是一次完整的事务提交，涉及日志刷盘、网络往返；合并成一条语句后，只有一次解析、一次事务开销。实测下来，批量插入比逐条插入快几倍到十几倍不等，数据量越大差距越明显。

但批量也不是越大越好。一条 SQL 太长会受max_allowed_packet限制，MySQL 8.0 默认 64MB，5.7 默认 4MB。另外一条超大语句在服务端解析时占用内存多，出错时整条回滚，重试成本高。我的经验值是每批 500 到 1000 行，或者按数据大小控制在几百 KB 到 1MB 之间。用代码里的Lists.partition(list, 500)这类工具很容易实现分批。

注意：批量插入时如果其中一行违反唯一约束，整条语句会失败回滚，前面插进去的也白插。要么提前在内存里去重，要么用下面讲的冲突处理语法。

3.2 主键冲突的三种处理策略

真实业务里经常遇到"存在就更新、不存在就插入"的需求，MySQL 给了几种写法，行为差别很大，选错了会出问题。

第一种是INSERT IGNORE：

INSERT IGNORE INTO t_user (username, phone) VALUES ('zhangsan', '13800000001');

遇到唯一键冲突时它不报错，直接跳过这一行。好处是简单，坏处是它会忽略所有错误，包括数据超长、类型不匹配这类本该暴露的问题，等于把异常吞掉了。我在生产代码里基本不用它，只在导数据时临时用一下。

第二种是ON DUPLICATE KEY UPDATE，这个最常用：

INSERT INTO t_user (username, phone, age) VALUES ('zhangsan', '13800000001', 30) ON DUPLICATE KEY UPDATE age = VALUES(age), updated_at = NOW();

冲突时执行后面的 UPDATE 部分。注意VALUES(age)这种写法在 MySQL 8.0.20 之后被标记为不推荐，新版本建议用别名：

INSERT INTO t_user (username, phone, age) VALUES ('zhangsan', '13800000001', 30) AS new_row ON DUPLICATE KEY UPDATE age = new_row.age, updated_at = NOW();

第三种是REPLACE INTO。这个我要重点提醒：它的行为是先删除旧行再插入新行。听起来和更新差不多，但副作用不少——自增主键会跳号，旧行上的其他字段会被重置成默认值，如果有外键引用会触发级联操作，DELETE 触发器也会被激活。除非你明确需要"整行覆盖"的语义，否则别用。

还有个容易忽略的点：ON DUPLICATE KEY UPDATE的返回值。插入成功返回 1，更新成功返回 2，值没变化返回 0。有些框架根据这个返回值判断是新增还是修改，如果字段值恰好没变，会误判成插入失败，这个坑要留意。

3.3 大批量导入的实操参数

数据迁移或者初始化时，动辄几十万上百万行，这时候用 INSERT 就不合适了，应该用LOAD DATA：

LOAD DATA LOCAL INFILE '/data/users.csv' INTO TABLE t_user FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (username, phone, age);

这条语句比 INSERT 快一个数量级，因为它绕过了 SQL 解析层，直接走批量加载路径。配套要调几个参数：innodb_flush_log_at_trx_commit在导入期间可以临时设为 2 或 0，减少刷盘次数；unique_checks和foreign_key_checks在确认数据干净的前提下可以临时关闭；autocommit设为 0，导入完再统一提交。

导入完成后记得把参数改回来。我见过有人导完数据忘了恢复innodb_flush_log_at_trx_commit，结果那个实例跑了很久都处于非严格持久化状态，一次意外重启丢了几分钟数据。这种参数调整最好写成脚本，导入前后各跑一次，别靠记忆。

4. 查询数据：把 SELECT 写对写快

SELECT 是四类操作里写法最多、坑也最多的一类。这一节按从简单到复杂的顺序，把条件、排序分页、多表关联、聚合、执行计划几个部分讲清楚。

4.1 基础查询与条件组合的边界

最基本的查询是SELECT * FROM t_user WHERE status = 1。这里第一个建议就是：线上代码不要写SELECT *。原因有三个：一是把不需要的大字段也读出来，增加网络传输和内存开销；二是表结构加了字段后，返回结果会变，可能让上层代码序列化出错；三是没法用覆盖索引优化，本来只查索引就能拿到的数据，因为要取全部列不得不回表。

条件组合里最常见的坑是隐式类型转换。比如phone字段是VARCHAR，你写WHERE phone = 13800000001（没加引号），MySQL 会把字段值转成数字比较，索引直接失效，全表扫描。这个错误非常隐蔽，因为结果往往是对的，只是慢。我排查过一个接口从 20ms 变成 2s 的问题，最后就是少写了一对引号。

NULL的处理也要注意。WHERE age != 18不会返回age为NULL的行，因为NULL参与任何比较结果都是UNKNOWN。要包含空值得写WHERE age != 18 OR age IS NULL。另外COUNT(age)不统计NULL，COUNT(*)才统计所有行，做报表时这两个结果可能不一样。

IN和OR的选择上，如果条件字段有索引，IN一般能用上索引，而OR连接不同字段时经常退化成全表扫描。MySQL 5.6 之后有 index merge 优化，部分场景能把 OR 拆成两次索引扫描再合并，但效果不如直接写UNION ALL稳定。我的习惯是：同一个字段的多个值用IN，不同字段的"或"关系用UNION ALL显式合并。

4.2 排序分页：LIMIT 深翻页为什么慢

ORDER BY created_at DESC LIMIT 20这种写法很常见，数据量小的时候没问题，翻到后面就出事：

SELECT id, username FROM t_user ORDER BY created_at DESC LIMIT 1000000, 20;

这条语句的实际执行过程是：按created_at索引取出前 1000020 行，把前 1000000 行丢掉，返回最后 20 行。也就是说它扫了一百万行只为了给你 20 行，越翻越慢。

解决办法有两种。第一种是延迟关联，先用覆盖索引拿到主键，再回表取数据：

SELECT u.id, u.username FROM t_user u INNER JOIN ( SELECT id FROM t_user ORDER BY created_at DESC LIMIT 1000000, 20 ) t ON u.id = t.id;

子查询只查 id，走覆盖索引，不需要回表，扫描成本低很多，拿到 20 个 id 再回表查明细，总共只回表 20 次。

第二种是游标分页，记住上一页最后一条记录的排序值，下一页从它之后开始：

SELECT id, username FROM t_user WHERE created_at < '2024-06-01 12:00:00' ORDER BY created_at DESC LIMIT 20;

这种方式效率最高，但只适合"下一页"这种线性浏览，用户要跳页就不行了。实际产品里可以两种结合：默认游标翻页，需要跳页时用延迟关联。

还有一个常被忽略的点：如果ORDER BY的字段没有索引，MySQL 要做文件排序（Using filesort），数据量大时会在磁盘上做归并，非常慢。所以排序字段尽量纳入索引，最好是联合索引的最后一列，比如KEY idx_status_created (status, created_at)，这样WHERE status = 1 ORDER BY created_at能直接用索引顺序，不需要额外排序。

4.3 多表 JOIN 的驱动表与执行顺序

JOIN 的性能取决于驱动表选得对不对。MySQL 优化器会根据统计信息自动选择，但统计信息可能过期，所以看 EXPLAIN 确认一下是必要的。一般原则是：小表驱动大表，也就是让结果集小的表作为驱动表，去扫大表的索引。

SELECT o.id, o.amount, u.username FROM t_order o INNER JOIN t_user u ON o.user_id = u.id WHERE o.created_at >= '2024-01-01';

这条查询里，如果t_order按时间过滤后只有几千行，而t_user有百万行，优化器应该选t_order作驱动表，用o.user_id去t_user的主键索引查。EXPLAIN 里驱动表显示在第一行，type=ref或range，被驱动表显示type=eq_ref，这是比较理想的组合。

LEFT JOIN和INNER JOIN的区别不只是语义。LEFT JOIN要求左表全部保留，所以左表一定是驱动表，优化器没有选择空间。如果左表很大而右表很小、过滤条件又在右表上，LEFT JOIN会先扫完整个左表，非常慢。这种场景要么改成INNER JOIN（前提是业务允许），要么把过滤条件下推到子查询里先缩小左表。

还有个大坑是 JOIN 字段类型或字符集不一致。比如一边是VARCHAR(32) utf8mb4，一边是VARCHAR(32) utf8，关联时 MySQL 要做字符集转换，索引用不上，直接退化成笛卡尔积式的扫描。这种问题在 EXPLAIN 里表现为被驱动表type=ALL，看到这个基本就能定位。

4.4 聚合查询与 GROUP BY 的注意点

分组统计是报表类需求的标配。COUNT、SUM、AVG、MAX、MIN这几个函数本身不难，难在数据量大时的性能。

SELECT status, COUNT(*) AS cnt, SUM(balance) AS total FROM t_user GROUP BY status;

GROUP BY如果能用上索引，会走索引顺序扫描，避免临时表。如果分组字段没索引，MySQL 会建内部临时表，数据量大时临时表会落盘，性能骤降。EXPLAIN 的 Extra 里出现Using temporary和Using filesort就是信号。

MySQL 5.7 之前GROUP BY会隐式排序，8.0 去掉了这个行为，所以现在需要排序要显式写ORDER BY。另外ONLY_FULL_GROUP_BY模式在 8.0 是默认开启的，SELECT 列表里出现的非聚合字段必须在 GROUP BY 里，否则报错。这个模式建议保持开启，它帮你避免"随便取一行"的语义错误。

COUNT(*)在 InnoDB 里需要遍历索引来计数，没有 MyISAM 那种维护好的行数。数据量到千万级时，COUNT(*)可能要几百毫秒。如果只是做分页总数展示，可以考虑用近似值，或者维护一张计数表，在插入删除时同步更新。

4.5 EXPLAIN 到底该看哪几列

EXPLAIN 是排查查询问题的第一工具，输出列不少，但日常重点看四列：type、key、rows、Extra。

type反映访问类型，从好到坏大致是system > const > eq_ref > ref > range > index > ALL。看到ALL就是全表扫描，数据量大时必须优化；看到index是全索引扫描，虽然比全表好，但通常也意味着没用到合适的过滤条件。

key显示实际使用的索引。如果这里是NULL，说明没走索引，回去检查 WHERE 条件是不是有函数运算、隐式转换、前导模糊匹配。

rows是优化器估算的扫描行数，不是精确值，但数量级有参考意义。估算值和实际值差很多，通常是统计信息过期，可以跑一次ANALYZE TABLE更新。

Extra里的信息最丰富。Using index表示覆盖索引，是好事；Using where表示在存储引擎返回后还要过滤；Using filesort表示额外排序；Using temporary表示用了临时表；Using join buffer表示被驱动表没索引，用了连接缓冲。后三个出现时要重点看。

5. 更新数据：UPDATE 的安全边界

UPDATE 是四类操作里最不能出错的。插入错了可以删，查询错了不影响数据，删除好歹还能从备份恢复，而一次错误的批量更新可能把整张表的字段刷成同一个值，恢复起来非常麻烦。这一节讲清楚写法、分批和安全机制。

5.1 基本语法与条件写法

标准写法是：

UPDATE t_user SET balance = balance + 100.00, updated_at = NOW() WHERE id = 1001;

几个要点。第一，SET后面的表达式里可以用字段本身，比如balance = balance + 100，这是原子的，不会出现读出来加完再写回去的并发问题。第二，updated_at如果建表时写了ON UPDATE CURRENT_TIMESTAMP，就不需要手动赋值，但显式写一下更清晰，也不依赖表定义。第三，WHERE 条件尽可能走主键或唯一索引，这样锁的范围最小。如果走的是普通索引，InnoDB 会对扫描到的索引记录加锁；如果完全没有索引，会锁住扫描的所有行，在可重复读隔离级别下还可能加间隙锁，阻塞其他事务的插入。

一个必须养成的习惯：执行 UPDATE 之前，把同样的 WHERE 条件写成 SELECT 先跑一遍，看看影响多少行、都是哪些行。这个动作花不了十秒钟，但能避免绝大多数误操作。如果是脚本批量跑，还要在事务里执行，先看影响行数再决定提交还是回滚。

5.2 批量更新为什么要分批

一次更新几十万行会带来几个问题：长事务持有锁时间长，阻塞其他业务；undo log 膨胀，回滚段压力大；binlog 单个事务过大，主从延迟明显；万一失败整个事务回滚，重试成本高。所以分批是标准做法。

分批的方式有几种。按主键范围切是最稳的：

UPDATE t_order SET status = 'CLOSED' WHERE status = 'PENDING' AND id > 0 AND id <= 10000;

每一批处理一个 id 区间，跑完记录进度，下一批从 10001 开始。这种方式的好处是可断点续跑，中途失败从上次的位置继续就行。

另一种是按时间或状态切，配合LIMIT：

UPDATE t_order SET status = 'CLOSED' WHERE status = 'PENDING' AND created_at < '2024-01-01' LIMIT 1000;

UPDATE ... LIMIT的语义是更新满足条件的前 N 行，循环执行直到影响行数为 0。要注意的是这个语法在 binlog 为 statement 格式时可能造成主从不一致，因为主从上 LIMIT 选中的行可能不同。现在一般都推荐 binlog 用 row 格式，就没这个问题。如果环境是 statement 格式，务必用主键范围切。

批次大小我一般设 500 到 2000 行，具体看单行数据量和索引情况。跑的时候每批之间 sleep 几十毫秒，给主从复制和锁等待留点余地。这些看起来是小事，但在高峰期跑批量任务时，这几毫秒的间隔能明显降低对线上业务的影响。

5.3 并发更新与乐观锁、悲观锁

两个请求同时更新同一行，后写的会覆盖先写的，这叫丢失更新。典型场景是库存扣减、余额变更。解决办法有两种。

悲观锁是查询时就把行锁住：

BEGIN; SELECT stock FROM t_product WHERE id = 1001 FOR UPDATE; -- 业务判断 UPDATE t_product SET stock = stock - 1 WHERE id = 1001; COMMIT;

FOR UPDATE会加排他锁，其他事务在这一行上会阻塞等待，直到当前事务提交。这种方式简单直接，但锁持有时间长，并发高时容易排队甚至死锁，适合冲突概率高的场景。

乐观锁是不加锁，更新时校验版本号：

UPDATE t_product SET stock = stock - 1, version = version + 1 WHERE id = 1001 AND version = 5;

执行后检查影响行数，如果是 0 说明版本已被别人改过，重新读取再试。这种方式并发性能好，适合冲突较少的场景，代价是要在应用层写重试逻辑，且重试次数要设上限，否则高冲突时会一直空转。

还有一种更简单的原子更新：

UPDATE t_product SET stock = stock - 1 WHERE id = 1001 AND stock > 0;

把判断条件写进 WHERE 里，靠数据库的行锁保证原子性，影响行数为 0 就说明库存不足。这种方式适合简单的计数型扣减，不需要额外字段，缺点是复杂的业务判断没法塞进 WHERE。

6. 删除数据：DELETE 的三种形态与软删除

删除是四个操作里最需要慎重的，因为它是不可逆的（相对业务而言）。这一节把三种删除方式的区别、软删除设计、误删恢复讲清楚。

6.1 DELETE、TRUNCATE、DROP 到底差在哪

这三条语句看起来都是"删"，行为差别很大，用错了会出事故。

DELETE FROM t_user WHERE id = 1001是逐行删除，会写 undo log 和 redo log，可以回滚，可以带 WHERE 条件，会触发 DELETE 触发器，自增主键不重置。数据量大时很慢，因为要一行行删、维护索引。

TRUNCATE TABLE t_user是 DDL 操作，直接重建表，速度快得多，自增主键重置为 1，不能带 WHERE 条件，不能回滚（因为 DDL 隐式提交），不触发行级触发器。它删完之后表空间会释放，这一点和 DELETE 不同。

DROP TABLE t_user是直接把表结构和数据一起删掉，最彻底，也最危险。

日常业务删除用 DELETE，清空测试表用 TRUNCATE，DROP 只在确定表不再需要时使用。有一点要特别注意：DELETE 全表不会释放磁盘空间，只是把页标记为可复用，如果表文件已经涨得很大，删完数据后还需要OPTIMIZE TABLE重建表来回收空间。这个操作会锁表（8.0 之后是 Online DDL，影响小一些，但仍建议在低峰期做）。

6.2 软删除的设计与索引配合

业务系统里我基本都建议用软删除。原因很实际：用户误删了要恢复、数据分析要保留历史、关联数据可能还有引用。做法是加一个is_deleted字段，删除时改成 1，查询时都带上is_deleted = 0。

UPDATE t_user SET is_deleted = 1 WHERE id = 1001;

这里有个索引上的坑：如果索引建在username上，而软删除的数据也在这个索引里，那么WHERE username = ? AND is_deleted = 0会因为区分度问题扫到很多已删除的记录。解决办法是把is_deleted放进联合索引，比如KEY idx_username_deleted (username, is_deleted)，或者用删除时间字段deleted_at，值为 NULL 表示未删除，配合WHERE deleted_at IS NULL查询。

软删除的另一个问题是唯一约束。用户名唯一，软删了一个用户后，新用户想用同一个名字注册，会因为唯一索引冲突而失败。常见的处理方式是把唯一索引改成(username, is_deleted)组合，但这样同一个用户名只能软删一次。更彻底的做法是用删除时间戳参与唯一约束，或者把软删数据挪到归档表。这块没有银弹，要根据业务约束来选。

6.3 误删之后能做什么

先明确一点：如果 binlog 是开启的且格式为 row，误删之后是有机会恢复的，但操作复杂，需要解析 binlog 把删除反向生成插入语句，或者用备份加 binlog 做时间点恢复。这个过程对普通开发来说门槛不低，所以我更强调事前防范。

事前防范的手段包括：生产库账号权限分级，普通开发只给 SELECT 和受限的 DML；所有 DELETE 和 UPDATE 走代码审查；危险操作前先备份表，比如CREATE TABLE t_user_bak_20240601 AS SELECT * FROM t_user；配置定期全量备份和 binlog 保留策略。这些措施看着笨，但真出事时，它们才是能救命的东西。

7. 常见问题与排查实录

最后这一节把日常遇到的高频问题整理一下，都是些排查思路和速查表，遇到同类问题时能快速定位方向。

7.1 慢查询从哪里入手

发现接口变慢，先确认是不是数据库的问题。看监控里的 QPS、连接数、CPU、慢查询数量，如果慢查询数突然上涨，那基本能确定。接着从慢查询日志里捞 SQL，long_query_time设成 1 秒，log_queries_not_using_indexes打开，日志文件里按耗时排序，取前几条看。

拿到 SQL 之后，第一件事是 EXPLAIN，看type有没有ALL，rows是不是几十万上百万，Extra里有没有Using filesort和Using temporary。然后对照表结构看索引情况，检查 WHERE 条件里有没有函数运算、隐式类型转换、前导模糊匹配。这四类问题占了慢查询的大头。

如果 EXPLAIN 看着正常但实际还是慢，就要考虑数据分布的问题。比如status = 1这种条件，如果 99% 的数据都是 1，优化器算下来走索引还不如全表扫描，就会主动放弃索引。这种情况可以强制走索引试试，FORCE INDEX看看效果差异，如果确实快很多，说明统计信息不准，跑一次ANALYZE TABLE，或者调整索引设计。

7.2 锁等待与死锁的排查

锁等待超时报错是Lock wait timeout exceeded，错误码 1205，默认等待 50 秒（innodb_lock_wait_timeout）。死锁报错是Deadlock found when trying to get lock，错误码 1213，MySQL 会主动回滚其中一个事务。

排查锁问题，先查information_schema.innodb_trx看当前有哪些事务在跑，innodb_locks和innodb_lock_waits（8.0 换成了performance_schema.data_locks和data_lock_waits）看谁在等谁。死锁的话直接看SHOW ENGINE INNODB STATUS，输出里LATEST DETECTED DEADLOCK那段会完整记录两个事务的 SQL 和加锁顺序，据此就能判断是哪两条语句交叉加锁了。

避免死锁的基本策略是保持加锁顺序一致。比如两个事务都要更新 A 表和 B 表，那就约定都先更新 A 再更新 B，不要一个先 A 后 B、另一个先 B 后 A。另外事务尽量短，不要在一个事务里做远程调用或耗时计算，锁持有时间越短，冲突概率越低。应用层对死锁要有重试逻辑，一般重试一到两次就能成功。

7.3 常见错误码速查

这张表我建议贴在工位上，遇到报错先对照一遍，能省不少搜索时间。

7.4 几个实操中反复被问到的细节

关于自增主键跳号。很多人发现 id 不连续就以为出问题了，其实正常。事务回滚、REPLACE INTO、INSERT ... ON DUPLICATE KEY UPDATE冲突、批量插入预分配，都会造成跳号。自增 id 的作用是唯一标识，不是连续计数，业务上不要依赖它的连续性做逻辑。

关于UPDATE影响行数为 0。可能是 WHERE 条件没匹配到，也可能是新值和旧值完全相同，MySQL 认为没有变化就不算更新。如果代码靠影响行数判断成功与否，要区分这两种情况，必要时先查一下记录是否存在。

关于时间字段的比较。WHERE created_at >= '2024-01-01'这种字符串和日期比较，MySQL 会做隐式转换，能用索引。但WHERE DATE(created_at) = '2024-01-01'在字段上套了函数，索引就失效了，要改成范围查询created_at >= '2024-01-01' AND created_at < '2024-01-02'。

关于大批量操作的监控。跑批量脚本时，我习惯每批打印一行日志，记录批次序号、影响行数、耗时。跑完之后核对总数是否和预期一致。这个小习惯帮我提前发现过好几次问题，比如某批因为锁等待超时被跳过、某段 id 区间被重复处理。日志不用多复杂，一行文本就够，但一定要有。

7.5 一个可复用的批量操作模板

最后分享一个我在多个项目里用过的批量操作骨架，伪代码形式，语言不限：

batch_size = 1000 last_id = 0 total = 0 while True: rows = query( "SELECT id FROM t_order WHERE status = 'PENDING' AND id > %s " "ORDER BY id LIMIT %s", (last_id, batch_size) ) if not rows: break ids = [r["id"] for r in rows] begin() try: affected = execute( "UPDATE t_order SET status = 'CLOSED' WHERE id IN (%s)" % placeholders(ids) ) commit() except Exception: rollback() raise total += affected last_id = ids[-1] log("batch done, last_id=%s, affected=%s, total=%s" % (last_id, affected, total)) sleep(0.05)

这套模板解决了前面提到的几个问题：按主键切批保证可断点续跑；每批独立事务，失败只影响一批；每批之间 sleep 降低对线上影响；日志可核对。具体到你的业务，把 SELECT 条件和 UPDATE 语句换掉就行，骨架不用动。

我在实际使用中最大的体会是，增删改查这四个操作真正难的不是语法，而是对数据规模、并发场景、失败后果的判断。同样一句 UPDATE，影响十行和影响十万行，需要的防护措施完全不同；同样一个查询，在几千行的配置表上和在千万行的订单表上，写法可能要推倒重来。所以每写一句涉及数据变更的 SQL，先问自己三个问题：影响范围有多大、失败了能不能回滚、并发时会不会互相干扰。把这三个问题想清楚，很多事故根本不会发生。