SQL插入性能优化技巧_调整索引配置与禁用非必要触发器-SQL-PHP中文网

insert慢的主因是冗余索引、触发器、批量方式不当及buffer pool过小；应删低基数无用索引、禁用非必要触发器、分批插入、调大innodb_buffer_pool_size并优化字段长度。

sql插入性能优化技巧_调整索引配置与禁用非必要触发器

INSERT 很慢？先查查有没有冗余索引在拖后腿

索引不是越多越好，特别是对高频 INSERT 场景。每多一个索引，每次插入就要多写一次 B+ 树，还可能触发页分裂。尤其当表上有多个 UNIQUE 索引或覆盖索引时，性能损耗会叠加。

实操建议：

用 SHOW INDEX FROM table_name 查出所有索引，重点看 Cardinality 低、Seq_in_index 靠前但实际查询几乎不用的索引
删除长期未被 WHERE 或 JOIN 使用的单列索引，比如只为报表导出加的 created_at 独立索引
把多个单列索引合并成复合索引时，注意字段顺序：高频过滤字段放前面，避免 INSERT 时反复定位不同叶子节点

触发器正在悄悄吃掉你的写入吞吐量

哪怕是个空壳触发器（比如只做 SELECT 1），也会强制事务串行化执行，阻塞并发 INSERT。更常见的是日志类触发器，在高并发下直接把 INSERT 变成“写完主表再写日志表”的两段式操作。

实操建议：

用 SHOW TRIGGERS LIKE 'table_name' 检查是否存在非业务强依赖的触发器
临时禁用可接受数据延迟的触发器：执行 SET @disable_audit_trigger = 1，并在触发器开头加 IF @disable_audit_trigger THEN LEAVE proc_label; END IF;
把同步触发逻辑改为异步队列消费（如通过 binlog 解析），但要注意主从延迟带来的数据可见性问题

批量插入别用循环单条 INSERT，但也要避开 VALUES 膨胀陷阱

INSERT INTO t VALUES (...), (...), (...) 是最快的方式，但 MySQL 默认有 max_allowed_packet 限制（通常 4MB），插太多行会直接报错 Packets larger than max_allowed_packet are not allowed。

实操建议：

按每 1000 行一组拼接 VALUES，比盲目堆到 5000 行更稳妥；具体数值可通过 SELECT @@max_allowed_packet 动态计算
避免在 VALUES 中混用不同数据类型（如字符串和数字），MySQL 会隐式转换并放弃部分索引优化
如果字段含 JSON 或大文本，优先考虑 LOAD DATA INFILE，它绕过 SQL 解析层，速度通常快 5–10 倍

innodb_buffer_pool_size 设太小会让 INSERT 变“磁盘直写”

InnoDB 不是直接写磁盘，而是先写 buffer pool 和 redo log。但如果 buffer_pool 远小于活跃数据集，每次插入都得淘汰页、刷脏页、再加载新页——等于把内存当缓存用，却干着磁盘的事。

实操建议：

查当前缓冲池命中率：SHOW ENGINE INNODB STATUS\G，关注 Buffer pool hit rate，低于 990/1000 就该调了
生产环境建议设为物理内存的 60%–75%，但别超过 innodb_buffer_pool_instances * 1GB（默认 8 实例，即上限 8GB），否则实例锁争用反而升高
改完记得重启 MySQL，SET GLOBAL 不生效；同时观察 Innodb_buffer_pool_wait_free 是否归零

最常被忽略的是：索引字段类型是否真的需要 VARCHAR(255)，还是能缩到 VARCHAR(32) —— 这直接影响 B+ 树层级和单页容纳记录数，进而决定每次 INSERT 的 I/O 次数。