MySQL优化秘籍：如何提升DML性能

以下内容将从表结构设计、索引策略、事务与锁、批量操作、配置调优等多个角度，结合代码示例、ASCII 图解与详细说明，系统讲解如何在 MySQL 中提升 DML（插入、更新、删除）操作的性能。

为什么要关注 DML 性能？
表结构与存储引擎选择
1. 合适的数据类型与列设计
2. InnoDB vs MyISAM：权衡与选择
索引策略：少而精的原则
事务与并发控制
批量 DML 操作优化
架构与分区：减小单表负担
1. 水平分表（Sharding）与分库
2. 表分区（Partitioning）
配置优化：InnoDB 参数与硬件配置
进阶技巧与注意事项
小结

1. 为什么要关注 DML 性能？

业务写入压力：在高并发场景下，大量的插入、更新、删除操作会直接影响系统响应与吞吐。
磁盘与 IO 限制：每次写操作都需要将数据写入磁盘，如何减少磁盘写入次数、避免不必要的随机 IO 是核心问题。
锁竞争与死锁：并发的写操作会引发锁等待甚至死锁，进一步拖慢事务完成速度。
长事务与回滚开销：大事务不仅持有更多锁，还会生成大量 Undo Log，回滚时代价更高。

如果 DML 性能不佳，可能导致业务“写不动”，后台队列堆积、延迟攀升，进而影响用户体验和系统稳定性。

2. 表结构与存储引擎选择

2.1 合适的数据类型与列设计

使用“最窄”字段类型
- 如无符号（UNSIGNED）的整型用于 ID、计数等。避免用过大的 BIGINT（8 字节）代替 INT（4 字节），除非确实会超过 21 亿。
```
-- 如果用户数量预计 < 4 亿，可用 INT UNSIGNED
user_id INT UNSIGNED NOT NULL AUTO_INCREMENT
```
- 日期/时间类型：用 DATE 存储日期即可，当无需时分秒；减少 DATETIME 8 字节占用。
精确选择字符类型
- VARCHAR(n)：按需设定长度，避免过度浪费。
- 如果字段长度固定，可使用 CHAR(n)，在少量列、且高查询频次场景下略优于 VARCHAR。
- 对于只需存布尔值，可使用 TINYINT(1) 而不是 VARCHAR(5)。
避免冗余列、拆分宽表
- 如果单表列数很多，且经常插入时只填充部分列，导致行记录大小过大，会带来磁盘与缓存开销。可将不常用列拆到扩展表。
合理使用 ENUM / SET
- 当某列只允许少量枚举值时，ENUM('male','female') 只占 1 字节，而 VARCHAR(6) 则占 6 字节，且比较速度更快。
```
gender ENUM('M','F') NOT NULL DEFAULT 'M'
```

2.2 InnoDB vs MyISAM：权衡与选择

InnoDB（推荐）
- 支持事务、行级锁、崩溃恢复，适合高并发写场景。
- 但每次写操作会产生 Undo Log、Redo Log，磁盘 IO 开销更大。
MyISAM
- 不支持事务，使用表级锁，写并发性能较差；删除/更新会阻塞全表。
- 适合以读为主、写比较少的场景，如日志归档表。

建议：绝大多数在线事务系统都采用 InnoDB；如果有只做批量写入的归档表，在负载极低的情况可考虑 MyISAM，但要注意恢复与数据完整性无法保障。

3. 索引策略：少而精的原则

索引能加速查询（DQL），但对写（DML）有额外开销。每一次插入、更新、删除都要维护所有相关索引。

3.1 主键与聚簇索引的影响

InnoDB 将主键作为聚簇索引，数据行本身在 B+Tree 叶子节点上存储。
插入时如果主键是 自增整型（AUTO_INCREMENT），新行直接附加到分页末尾，避免页面分裂，写性能最佳。
如果使用 UUID 或随机主键，插入时会随机在聚簇索引中分散写入，导致更多页面分裂与磁盘随机 IO，性能大幅下降。

-- 推荐做法：顺序自增主键
CREATE TABLE t1 (
  id   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  data VARCHAR(255)
) ENGINE=InnoDB;

-- 非推荐：随机主键（易导致页面分裂）
CREATE TABLE t2 (
  id BINARY(16) PRIMARY KEY,  -- 存储随机 UUID
  data VARCHAR(255)
) ENGINE=InnoDB;

ASCII 图解：顺序 vs 随机插入

聚簇索引 B+ Tree 叶子节点：
┌──────────────────────────────┐
│ [ 1 ] [ 2 ] [ 3 ] [ 4 ] [ 5 ] │  ← 顺序插入，新记录追加到右侧，不分裂
└──────────────────────────────┘

随机插入：
┌──────────────────────────────┐
│ [ 2 ] [ 4 ] [ 6 ] [ 8 ] [ 10 ] │
└──────────────────────────────┘
  ↑新插入 7，需要在中间插，触发页面分裂
┌─────┬─────┐    ┌─────┬─────┐
│ [2] │ [4] │    │ [7] │ [8] │
│ [6] │ [8] │ →  │ [10]│     │
│ [10]│     │    │     │     │
└─────┴─────┘    └─────┴─────┘

3.2 二级索引的维护开销

InnoDB 的二级索引叶子节点存储主键值作为“指针”，因此每次插入/更新二级索引列都要：
1. 在二级索引 B+Tree 中插入/删除键值、记录主键；
2. 如果二级索引被修改，还需在聚簇索引中根据主键定位数据。

二级索引维护示意

orders 表有主键(order_id)，二级索引 (user_id)  

orders:
┌───────────────────┐
│ order_id (PK)     │
│ user_id (KEY)     │
│ total_amt         │
└───────────────────┘

插入一行 (order_id=101, user_id=5):
 1. 聚簇索引插入 order_id=101
 2. 二级索引插入 user_id=5, pointer=101

建议：
- 二级索引只创建在 DQL 常用的查询列上，避免冗余索引。
- 当 DML 写性能要求高时，审慎评估是否需要创建过多二级索引。

3.3 覆盖索引与索引下推

覆盖索引（Covering Index）
- 当查询所需列都包含在同一索引中，MySQL 可以直接从索引页读取数据，无需访问聚簇索引的表页（回表）。
- 比如：
```
CREATE INDEX idx_user_status_amount
  ON orders(user_id, status, total_amt);

-- 查询时只访问 user_id、status、total_amt
SELECT status, SUM(total_amt)
  FROM orders
 WHERE user_id = 5
 GROUP BY status;
```
  这时 MySQL 可以只扫描索引 idx_user_status_amount，无需回表，效率更高。
索引下推（Index Condition Pushdown，ICP）
- 在 MySQL 5.6+，执行范围查询或复合索引查询时，会将部分过滤条件在索引层过滤，减少回表行数。
- 例如：
```
CREATE INDEX idx_date_status 
  ON orders(order_date, status);

SELECT * FROM orders
 WHERE order_date >= '2023-10-01'
   AND order_date < '2023-10-02'
   AND status = 'shipped';
```
  在检索 order_date 范围时，MySQL 会在索引层先过滤 status = 'shipped' 的行，减少回表数量。

3.4 避免索引失效：常见误区

在索引列上使用函数或表达式

-- 索引 user_id 无效
SELECT * FROM users WHERE YEAR(created_at) = 2023;

应改为范围查询：

SELECT * FROM users
 WHERE created_at >= '2023-01-01'
   AND created_at < '2024-01-01';

隐式类型转换导致索引不可用

-- 如果 user_id 是 INT 列，但传入字符串，可能触发类型转换
SELECT * FROM users WHERE user_id = '123';

虽然 MySQL 可以隐式转换，但最好保持类型一致：

SELECT * FROM users WHERE user_id = 123;

前缀匹配导致索引只能部分使用
```
-- 像这样前缀通配，索引不会命中索引范围
SELECT * FROM users WHERE email LIKE '%@example.com';
```
通常需要在应用层进行精准匹配，或使用全文索引、逆序存储等技巧。

4. 事务与并发控制

4.1 合理控制事务范围

最小化事务包裹的 SQL 数量
- 不要把太多业务逻辑（如网络调用、业务计算）放在一个事务里；获取主键、准备数据、计算逻辑都可在事务外完成，只将必要的 DML 操作放在事务中。

-- 不佳示例：事务中做耗时计算
START TRANSACTION;
  SELECT * FROM users WHERE user_id = 1 FOR UPDATE;
  -- ↓ 耗时操作，如调用外部接口、IO 等
  -- DO EXPENSIVE COMPUTATION...
  UPDATE users SET balance = balance - 100 WHERE user_id = 1;
COMMIT;

-- 改进：将耗时操作放到事务外
SELECT balance FROM users WHERE user_id = 1;  -- 只读
-- ↓ 耗时计算
-- 调用外部服务、复杂计算...
START TRANSACTION;
  SELECT balance FROM users WHERE user_id = 1 FOR UPDATE;
  UPDATE users SET balance = balance - 100 WHERE user_id = 1;
COMMIT;

事务中避免执行会导致长锁的操作
- 如大范围的 DELETE、ALTER TABLE、OPTIMIZE TABLE 等，尽量在低峰期或拆分为小批次。

4.2 批量提交 vs 单次提交

对于多条写操作，批量提交（一次性在一个事务中执行所有 INSERT/UPDATE/DELETE）能减少网络往返与事务开销，但如果操作量过大，事务会过长持锁、使用大量 Undo Log。
折中方案：将“大事务”拆分为多个“中等事务”，如每 1000 行为一批，既减少网络开销，又限制单次事务长度。

-- 批量插入示例，拆分成每 1000 行提交
SET @batch_size = 1000;
SET @i = 0;

-- 假设有一个临时表 tmp_data(…) 存储待插入行
WHILE 1=1 DO
  INSERT INTO real_table (col1, col2, ...)
    SELECT col1, col2, ...
    FROM tmp_data
    LIMIT @i, @batch_size;
  IF ROW_COUNT() = 0 THEN
    LEAVE;
  END IF;
  SET @i = @i + @batch_size;
END WHILE;

4.3 行级锁与锁等待示意

4.3.1 行锁示意

当两个并发事务都要修改同一行时，会发生锁等待或死锁。

事务 A                          事务 B
-------                         -------
START TRANSACTION;              START TRANSACTION;
SELECT * FROM accounts          SELECT * FROM accounts
 WHERE acc_id = 1 FOR UPDATE;   WHERE acc_id = 2 FOR UPDATE;  
 -- 锁定 acc_id=1               -- 锁定 acc_id=2

-- A 尝试修改 acc_id=2 (等待 B 先释放)
UPDATE accounts SET balance = balance - 100 WHERE acc_id = 2;

-- B 尝试修改 acc_id=1 (等待 A 先释放)
UPDATE accounts SET balance = balance - 200 WHERE acc_id = 1;

此时 A 等待 B，B 等待 A，形成死锁。InnoDB 会回滚其中一个事务。

4.3.2 事务隔离与 DML 性能

READ COMMITTED：每次读取只锁行级别的查找，减少间隙锁（Gap Lock）发生，适合高并发写场景。
REPEATABLE READ（默认）：防止幻读，但会使用临键锁（Next-Key Lock），导致范围更新/插入产生更多锁冲突。

-- 在高并发写场景下，可考虑设置
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

5. 批量 DML 操作优化

5.1 多行插入（Bulk Insert）

单条 INSERT：

INSERT INTO users (username, email) VALUES ('a','a@example.com');
INSERT INTO users (username, email) VALUES ('b','b@example.com');

网络往返 2 次，性能较差。

多行 INSERT：

INSERT INTO users (username, email) VALUES
  ('a','a@example.com'),
  ('b','b@example.com'),
  ('c','c@example.com');

网络往返仅 1 次，显著提升插入吞吐。

5.1.1 批量插入与事务结合

START TRANSACTION;
INSERT INTO orders (user_id, order_date, total_amt) VALUES
  (1, '2023-10-01', 100.00),
  (2, '2023-10-01', 200.00),
  (3, '2023-10-01', 150.00);
INSERT INTO orders (user_id, order_date, total_amt) VALUES
  (4, '2023-10-02', 120.00),
  (5, '2023-10-02', 300.00),
  (6, '2023-10-02', 80.00);
COMMIT;

将多行插入安排在一个事务中，减少事务提交次数与同步磁盘写入的开销。

5.2 LOAD DATA INFILE 高速导入

当需要从文件批量导入大量数据时，LOAD DATA INFILE 性能远超 INSERT。
示例：将 CSV 文件导入 users 表。

-- CSV 文件示例 user_data.csv：
-- alice,alice@example.com,2023-10-01 12:00:00
-- bob,bob@example.com,2023-10-02 13:30:00

LOAD DATA INFILE '/path/to/user_data.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(username, email, created_at);

若服务器与客户端分离，需要使用 LOAD DATA LOCAL INFILE 并在客户端配置允许。
导入前可临时禁用唯一索引检查和外键检查，加快速度；导入后再恢复。

SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;

LOAD DATA INFILE '/path/to/user_data.csv'
INTO TABLE users ...;

SET UNIQUE_CHECKS=1;
SET FOREIGN_KEY_CHECKS=1;

5.3 分批 UPDATE/DELETE

一次性大范围更新：
```
UPDATE orders 
  SET status = 'archived' 
WHERE order_date < '2022-01-01';
```
可能会锁住大量行，导致锁等待严重。

分批更新：

SET @batch = 1000;

REPEAT
  -- 删除符合条件的前 @batch 条记录
  DELETE FROM orders
  WHERE order_date < '2022-01-01'
  LIMIT @batch;

  -- 如果删除行数 < 批量大小，退出
UNTIL ROW_COUNT() = 0 END REPEAT;

同理可对 UPDATE 做类似分批：

SET @batch = 1000;
SET @last_id = 0;

REPEAT
  UPDATE orders
    SET status = 'archived'
  WHERE order_date < '2022-01-01'
    AND order_id > @last_id
  ORDER BY order_id
  LIMIT @batch;

  SET @last_id = (SELECT MAX(order_id)
                  FROM (SELECT order_id FROM orders 
                        WHERE order_date < '2022-01-01' 
                          AND order_id > @last_id 
                        ORDER BY order_id LIMIT @batch) AS temp);

UNTIL ROW_COUNT() = 0 END REPEAT;

5.4 使用临时表或表交换技巧

用途：当需要大量更新/插入而不影响生产表的可用性时，可借助“先写临时表，再交换”策略。

创建新表（与旧表结构相同）
```
CREATE TABLE orders_new LIKE orders;
```

批量插入或批量更新到新表

-- 先将旧表满足条件的行复制到新表
INSERT INTO orders_new
  SELECT * FROM orders WHERE order_date >= '2022-01-01';

-- 对新表做批量更新
UPDATE orders_new SET status = 'archived' WHERE ...;

重命名表交换

RENAME TABLE orders TO orders_old, orders_new TO orders;

删除旧表（可延后）
```
DROP TABLE orders_old;
```

优点：避免长时间锁住生产表；在低峰切换时仅需几毫秒；
缺点：需要额外磁盘空间；切换时需确保无新数据写入（或先将新写入数据增量同步到新表）。

6. 架构与分区：减小单表负担

6.1 水平分表（Sharding）与分库

水平分表
- 将大表按某一维度（如 user_id 范围、哈希）拆分为多张小表，例如：
```
orders_0, orders_1, orders_2, orders_3
```
  根据 user_id % 4 决定写入哪个表。
- 优点：每张表的行数减少，索引及数据页更少，DML 操作锁竞争与 IO 压力显著降低。
- 缺点：跨分表查询复杂度增加，需要应用层做路由。
分库
- 除了分表，还可将不同业务、不同租户的数据放在不同的 MySQL 实例上，单实例压力进一步缓解。

架构示意

+--------------------------------------------+
|       应用层 （分片路由）                  |
|                                            |
|   if user_id % 4 == 0 → orders_0             |
|   if user_id % 4 == 1 → orders_1             |
|   ...                                      |
+--------------------------------------------+
   |            |            |           |
   v            v            v           v
+------+     +------+     +------+     +------+
| MyDB |     | MyDB |     | MyDB |     | MyDB |
|orders_0|   |orders_1|   |orders_2|   |orders_3|
+------+     +------+     +------+     +------+

6.2 表分区（Partitioning）

MySQL 表分区可将一个逻辑表切分为多个物理分区，例如按月份、按范围、按哈希。
默认 InnoDB 表支持如下常见分区类型：RANGE、LIST、HASH、KEY。

6.2.1 RANGE 分区示例

CREATE TABLE orders (
  order_id   BIGINT NOT NULL AUTO_INCREMENT,
  user_id    INT    NOT NULL,
  order_date DATE   NOT NULL,
  total_amt  DECIMAL(10,2),
  PRIMARY KEY (order_id, order_date)
) ENGINE=InnoDB
PARTITION BY RANGE ( YEAR(order_date) ) (
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

上述表会根据 order_date 的年份放入不同分区，查询时 MySQL 可以根据 order_date 过滤掉不相关分区，减少扫描范围。
分区表的 DML 优化：
- DELETE FROM orders WHERE order_date < '2021-01-01'; 可以通过 ALTER TABLE DROP PARTITION p2020; 直接清理历史数据，效率极高；
- 插入新行会定位到对应年份分区，无需扫描全表。

ASCII 分区示意

orders 表真实存储：
┌─────────────┬───────────────┐
│ Partition   │ Data Range    │
├─────────────┼───────────────┤
│ p2021       │ 2021-01-01~   │
│ p2022       │ 2022-01-01~   │
│ pmax        │ 2023-01-01~   │
└─────────────┴───────────────┘

注意：分区键必须是主键的一部分，或包含在唯一索引里；且要谨慎设计分区规则，避免“数据倾斜”导致某个分区过大。

7. 配置优化：InnoDB 参数与硬件配置

7.1 InnoDB Buffer Pool 大小

Buffer Pool：InnoDB 用于缓存数据页、索引页的内存区域。
原则：将常用数据或热点数据尽量缓存到内存，减少磁盘 IO。
- 如果服务器只部署 MySQL，Buffer Pool 可配置为 物理内存的 60%～80%；
- 如果还有其他服务并存，可相应减少。

# my.cnf 示例
[mysqld]
innodb_buffer_pool_size = 24G   # 假设服务器有 32G 内存
innodb_buffer_pool_instances = 8  # 将 24G 划分为 8 个实例，减少竞争

Buffer Pool 实例：在 MySQL 5.7+ 中，Buffer Pool 可以划分为多个实例，减少多线程访问时的锁竞争。每个实例建议至少 1G 大小。

7.2 Redo Log 与 Flush 策略

Redo Log 大小（innodb\_log\_file\_size）
- Redo Log 用于保证事务提交的持久性。过小的 Redo Log 会导致频繁的日志归档（Checkpoint），引发 IO 峰值。
- 通常设置为1G ～ 4G，结合预计算事务量调整。

innodb_log_file_size = 2G
innodb_log_files_in_group = 2  # 默认为 2 个日志文件

Flush 方法（innodb\_flush\_log\_at\_trx\_commit）
- 值为 1（最安全）：每次事务提交时，将 Redo Log 从内存同步到磁盘（fsync），性能最慢，但安全性最高。
- 值为 2：每次事务提交时，只写入操作系统缓存，不立即 fsync；每秒才 fsync 一次。
- 值为 0：事务提交时既不写入操作系统缓存，也不 fsync，每秒写入并 fsync。风险最大，但性能最优。

innodb_flush_log_at_trx_commit = 2

说明：如果业务可以容忍最多丢失 1 秒的提交，建议设置为 2；极端写性能要求下可设置为 0，但需结合外部备份与复制策略。

7.3 批量提交与日志合并

innodb\_flush\_method：决定 InnoDB 如何向磁盘写数据。
- O_DIRECT：避免双重缓存，将 Buffer Pool 直接写入磁盘，减少系统 PageCache 与 BufferPool 竞争。
- fsync：默认方式，先写入 PageCache，再写入磁盘。

innodb_flush_method = O_DIRECT

innodb\_change\_buffering：允许将次要修改缓存在内存，对次级索引批量变更效果更好。
- 可取值 all/inserts/deletes/changes/none。
- 在高写入场景下，推荐启用 all。

innodb_change_buffering = all

7.4 硬件层面：SSD、内存与 CPU

SSD vs HDD
- SSD 提供更低的随机 IO 延迟，对写密集型场景提升显著；
- 如果只能使用 HDD，建议搭配大 Buffer Pool，以尽量缓存数据页。
内存大小
- 足够的内存能让大部分“热数据”常驻 Buffer Pool，大幅减少磁盘读取；
- 同时要考虑连接数、缓存、中间件等对内存的消耗。
CPU 核心数
- InnoDB 越多核心并不意味着 DML 性能线性提升；
- 需要关注锁竞争、Buffer Pool 实例数量等，避免 CPU 空转等待锁。

8. 进阶技巧与注意事项

8.1 禁用不必要的触发器与外键检查

触发器 会在每次 DML 事件触发时执行相应逻辑，影响写性能。
- 在批量导入或批量更新时，可暂时禁用触发器（应用层或脚本负责临时禁用），导入完再恢复。
外键检查 会在插入/更新/删除时进行额外的父子表约束校验；在大批量导入或清理数据时，可临时关闭：

SET FOREIGN_KEY_CHECKS = 0;
-- 批量 DML
SET FOREIGN_KEY_CHECKS = 1;

关闭外键检查后，需保证导入的数据不会破坏参照完整性，否则后续使用可能出错。

8.2 使用带条件的 DML 语句减少扫描

8.2.1 UPDATE … LIMIT

虽然标准 SQL 不支持直接在 UPDATE 中加 LIMIT，MySQL 支持但语义不同：它会更新满足条件的任意 LIMIT 条记录（无 ORDER BY 时结果不定）。可搭配主键范围分批更新。

UPDATE orders
SET status = 'archived'
WHERE order_date < '2022-01-01'
ORDER BY order_id
LIMIT 1000;

不加 ORDER BY 时，MySQL 会选择任意 1000 条匹配行更新；
与分页思路结合，可用主键范围控制批量更新。

8.2.2 DELETE … LIMIT

同理可对 DELETE 分批删除：

DELETE FROM orders
WHERE order_date < '2022-01-01'
ORDER BY order_id
LIMIT 1000;

定时或循环执行该语句，直到没有更多符合条件的行。

8.3 避免大事务带来的副作用

Undo Log、Redo Log 增长：大事务会产生大量 Undo Log，导致回滚缓慢；Redo Log 不断累积，触发 Checkpoint 时可能造成 IO 峰值。
Binlog 瞬时高峰：如果启用了二进制日志，提交大事务时，整个事务会被一次性写入网络带宽与磁盘，容易导致复制延迟。
锁持有时间长：大事务持有行锁或范围锁时间过长，阻塞并发事务。

建议：将大事务拆分为多批中等事务，在应用层或存储过程里分批提交。

8.4 监控与诊断工具

SHOW ENGINE INNODB STATUS
- 查看当前 InnoDB 锁等待、死锁信息与 Checkpoint 进度。
```
SHOW ENGINE INNODB STATUS\G
```
INFORMATION\_SCHEMA.INNODB\_TRX & INNODB\_LOCKS & INNODB\_LOCK\_WAITS
- 查询活跃事务、锁情况、锁等待链，帮助定位死锁和性能瓶颈。
```
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G
```
Performance Schema
- 收集 DML 语句执行耗时、阻塞等待情况。
- 可开启 setup_instruments='wait/lock/innodb/row_lock' 等相关监控。

慢查询日志（slow\_query\_log）

开启慢查询日志，设置 long_query_time，统计耗时过长的事务，重点优化。

slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5  # 0.5 秒以上记录
log_queries_not_using_indexes = ON

9. 小结

要想提升 MySQL DML 性能，需要从以下几个维度协同优化：

表结构与存储引擎
- 精简列类型，避免过宽表；
- 优先选择 InnoDB 引擎，hash/random 主键要慎用。
索引策略
- 遵循“少而精”原则，仅为常用的查询或关联字段建索引；
- 利用覆盖索引减少回表；
- 避免在索引列上使用函数/表达式造成索引失效。
事务与并发控制
- 将业务逻辑中耗时操作移出事务范围；
- 拆分大事务为多批中等事务，减少长锁持有；
- 在高并发写场景下可考虑降低隔离级别至 READ COMMITTED。
批量 DML 优化
- 使用多行 INSERT、LOAD DATA INFILE；
- 按需分批 UPDATE、DELETE，避免全表扫描；
- （必要时）借助临时表与表交换技巧。
架构与分区
- 水平分表／分库，将写入压力分散到多个物理实例；
- 表分区减少单表数据量与查询范围，提高删除/归档效率。
配置与硬件优化
- 充分调大 InnoDB Buffer Pool，减少磁盘 IO；
- 合理设置 Redo Log 大小、innodb_flush_log_at_trx_commit 策略；
- 采用 SSD 存储与足够内存；
- 在参数层面开启 O_DIRECT、change_buffering 等。
进阶技巧
- 导入/更新时临时禁用触发器与外键检查；
- 监控工具及时发现慢查询与锁竞争；
- SQL 层面避免大范围一次性写操作造成的性能瓶颈。

通过上述思路、技巧与实践示例，相信你能系统掌握 MySQL DML 性能优化要点，并在实际项目中持续沉淀与优化，最终实现“写入零瓶颈”、业务高并发场景下数据读写无忧。

目录