MySQL中MyISAM的表级写锁如何通过并发插入参数优化-mysql教程-PHP中文网

能，concurrent_insert=2仅对insert有效且需写入表尾；其本质是物理层追加避锁，非锁粒度变细；optimize table可重置状态但会锁表。

mysql中myisam的表级写锁如何通过并发插入参数优化

concurrent_insert=2 能否真正缓解写锁阻塞？

能，但只对 INSERT 有效，且必须满足“写入发生在表尾”这一物理前提。MyISAM 的并发插入本质是绕过写锁的“物理层捷径”，不是锁粒度变细。当 concurrent_insert=2 时，即使表中间存在空洞（被 DELETE 留下的碎片），新记录仍被强制追加到文件末尾，从而避开与读锁的冲突——因为读操作只锁定数据块，不锁定文件尾部追加区域。

常见错误现象：SELECT 长时间等待，SHOW PROCESSLIST 显示大量 Locked 状态，但 Table_locks_waited 增长缓慢。这往往说明问题不在锁调度，而在并发插入未生效：可能 concurrent_insert 仍是默认值 1，且表已存在空洞；或客户端发的是 INSERT ... SELECT 这类需全表扫描的语句，触发了写锁而非并发插入路径。

确认当前值：SELECT @@concurrent_insert;，线上环境建议直接设为 2
修改方式（重启生效）：concurrent_insert = 2 加入 my.cnf；或运行时临时设置：SET GLOBAL concurrent_insert = 2;
注意：该参数对 REPLACE INTO、INSERT ... ON DUPLICATE KEY UPDATE 无效，它们始终走写锁流程

为什么 max_write_lock_count=1 比 low_priority_updates=1 更可控？

max_write_lock_count 是按“写锁次数”计数的硬闸，而 low_priority_updates 是软降级——它仅影响新发起的写请求优先级，对已在写锁队列中排队的请求无效。当突发大量写入时，low_priority_updates=1 可能让读请求永远等不到机会（写队列持续有新请求涌入），而 max_write_lock_count=1 则保证每处理 1 次写锁后，立刻放行所有等待中的读锁，形成确定性的读写交替节奏。

使用场景：报表类系统每日凌晨批量写入 + 白天高频查询，此时用 max_write_lock_count=1 可避免凌晨任务拖垮白天响应；若业务写入本身就很零散（如用户行为日志），则 low_priority_updates=1 更轻量。

检查效果：SHOW STATUS LIKE 'Table_locks%'; 中 Table_locks_waited 应明显下降
风险点：设得太小（如 =1）会导致写吞吐骤降，需结合监控 QPS 和平均写延迟调整
不要混用：同时开启两者可能引发调度逻辑混乱，官方文档明确建议二选一

OPTIMIZE TABLE 不只是整理碎片，它重置 concurrent_insert 行为

OPTIMIZE TABLE 对 MyISAM 来说不只是回收空洞，它会重建整个 .MYD 文件，并将 concurrent_insert 的“是否允许并发插入”状态重置为“可启用”。也就是说，即使你设了 concurrent_insert=2，如果表长期未优化、且存在大量 DELETE，MyISAM 内部可能仍按“有洞”逻辑拒绝并发插入——直到你显式执行一次 OPTIMIZE TABLE。

MySQL

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

下载

容易踩的坑：在高负载期间执行 OPTIMIZE TABLE 会锁表（写锁），导致所有读写阻塞。它不是在线操作。

安全做法：在低峰期执行，配合 myisam_sort_buffer_size 调大加速（默认 8M，可设为 256M）
验证是否生效：优化后查 information_schema.TABLES 中 DATA_FREE 字段应接近 0
替代方案：若无法停写，可用 mysqldump 导出 + 删除原表 + 重建导入，效果等同但更可控

INSERT DELAYED 在现代 MySQL 中已废弃，别再依赖

INSERT DELAYED 在 MySQL 5.6 中已被标记为废弃，5.7 完全移除。它的设计前提是单线程写入队列 + 表级锁，与现代多核 CPU 和 SSD I/O 模型严重脱节。实际测试表明，在并发 > 50 的场景下，INSERT DELAYED 的吞吐反而比普通批量 INSERT 低 30% 以上，因为其内部队列锁竞争激烈，且无法利用 bulk_insert_buffer_size 优化。

现在更有效的替代是：LOAD DATA INFILE（服务端本地文件）或客户端分批组装 INSERT INTO t VALUES (),(),()...（控制每批 200–500 行）。