mysql多线程插入优化_InnoDB与MyISAM并发插入对比-mysql教程-PHP中文网

mysql 5.6起已移除insert delayed，myisam不支持事务和行锁，并发插入仅限表尾追加；innodb应使用insert...on duplicate key update实现原子插入，批量插入需控制批次大小、禁用autocommit并合理配置buffer pool。

mysql多线程插入优化_innodb与myisam并发插入对比

MyISAM 的 `INSERT DELAYED` 已被移除，别再查旧文档了

MySQL 5.6 起就彻底删掉了 INSERT DELAYED，8.0 更是连语法都报错。很多老项目迁移时卡在这儿——以为加个 DELAYED 就能“自动排队”，结果直接 ERROR 1064。MyISAM 本身不支持事务和行锁，所谓“并发插入”只在表尾追加时有效，一旦有 DELETE 或 UPDATE 留下空洞，后续插入会触发表级锁等待。

实操建议：

确认 MySQL 版本：SELECT VERSION();，若 ≥5.6，直接放弃 INSERT DELAYED
MyISAM 表若真要高并发写，必须保证：无删除、无更新、无索引（或仅主键）、数据文件不碎片化
用 SHOW TABLE STATUS LIKE 'tbl_name' 查 Data_free，值 > 0 说明有碎片，OPTIMIZE TABLE 可临时缓解，但治标不治本

InnoDB 的 `INSERT ... ON DUPLICATE KEY UPDATE` 是并发安全的底线

InnoDB 默认走行锁 + next-key lock，但普通 INSERT 在唯一索引冲突时才触发锁升级。真正稳住多线程插入的关键，是把“先查后插”的逻辑收束成原子操作——INSERT ... ON DUPLICATE KEY UPDATE 就是干这个的。它比应用层加分布式锁或数据库 SELECT + INSERT 组合快得多，也避免了幻读导致的重复插入。

常见错误现象：

没建唯一索引（比如只靠业务逻辑判断“不存在”），ON DUPLICATE KEY 根本不触发
用 REPLACE INTO 替代，实际是 DELETE + INSERT，会自增 ID 跳变、触发两次 binlog、锁更重
在非主键/唯一键字段上做冲突判断，语句直接报错 ERROR 1062，不会进 UPDATE 分支

示例（确保 uk_order_id 是 UNIQUE 索引）：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

INSERT INTO orders (order_id, user_id, status) 
VALUES (123, 456, 'pending') 
ON DUPLICATE KEY UPDATE status = VALUES(status);

批量插入必须控制 `innodb_buffer_pool_size` 和事务大小

单次插入 10 万行？别硬扛。InnoDB 的写入瓶颈不在磁盘，而在 buffer pool 溢出、redo log 切换、以及长事务拖慢 purge 线程。盲目加大 innodb_buffer_pool_size 反而可能挤占 OS 文件缓存，让顺序写变成随机写。

关键参数与取舍：

innodb_buffer_pool_size 建议设为物理内存的 50%–75%，但不要超过 80%，留内存给 OS 和连接线程
每批 INSERT 行数控制在 1k–5k，太小则网络和解析开销占比高，太大则事务日志暴涨、锁持有时间过长
显式用 BEGIN; ... COMMIT; 包裹批次，禁用 AUTOCOMMIT=1，否则每行都是独立事务
如果表有二级索引，批量插入前可临时 ALTER TABLE ... DISABLE KEYS（仅 MyISAM 有效），InnoDB 不支持该语法，别试

并发线程数不是越多越好，`innodb_thread_concurrency` 设 0 反而是默认最优解

这个参数在 MySQL 5.7+ 默认是 0，意味着不限制并发线程数，由 InnoDB 自行调度。设成正整数（比如 16）反而可能造成线程频繁挂起/唤醒，增加上下文切换开销。真实瓶颈往往在磁盘 IOPS、redo log 刷盘速度或 CPU 解析 SQL 的能力，而不是“允许几个线程同时进引擎”。

排查方向优先级：

用 SHOW ENGINE INNODB STATUS\G 看 SEMAPHORES 部分，若 os_waits 高，说明锁争用或 IO 等待严重
查 innodb_row_lock_waits 和 innodb_row_lock_time_avg，确认是否因热点行（如计数器、状态字段）引发锁等待
观察 Threads_running 是否长期 > 30，结合 Slow_queries 判断是不是某条慢查询拖垮了整个连接池

多线程插入时最易被忽略的点：所有线程共用同一张表的自增锁（auto-inc lock）。即使你用 INSERT ... SELECT 或 LOAD DATA，只要涉及自增主键，InnoDB 就会在 insert 语句执行期间持锁。解决办法只有两个：改用 UUID/雪花 ID，或确保插入语句显式指定主键值（跳过自增逻辑）。