一、问题背景今天在一台 MySQL 5.7.44 数据库服务器上需要导入一张百万级数据量的大表。导入前先对数据库基础配置进行检查避免因为默认参数过小导致导入失败或性能过慢。服务器基础配置如下CPU2 核 内存8G Swap5G 磁盘空间充足 MySQL版本5.7.44通过查询发现当前 MySQL 仍然使用了较多默认配置SHOW VARIABLES WHERE Variable_name IN ( max_allowed_packet, innodb_buffer_pool_size, innodb_flush_log_at_trx_commit, sync_binlog, log_bin, binlog_format, max_connections, wait_timeout );重点结果如下max_allowed_packet 4194304 -- 4MB innodb_buffer_pool_size 134217728 -- 128MB log_bin OFF innodb_flush_log_at_trx_commit 1其中真正影响本次百万级大表导入的核心短板主要有两个max_allowed_packet太小innodb_buffer_pool_size太小。二、参数一max_allowed_packetmax_allowed_packet用来控制 MySQL 客户端和服务端之间单次通信包允许的最大大小。它主要影响1. 大 SQL 导入 2. 批量 INSERT 3. mysqldump 恢复 4. TEXT、LONGTEXT、BLOB、JSON、大备注字段写入当前默认值只有4MB如果导入 SQL 文件中存在较大的批量 INSERT或者单行数据字段较长就可能出现Packet for query is too large MySQL server has gone away因此本次将其调整为64MB在线调整命令SET GLOBAL max_allowed_packet 64 * 1024 * 1024;验证SHOW GLOBAL VARIABLES LIKE max_allowed_packet; SELECT global.max_allowed_packet/1024/1024 AS max_allowed_packet_MB;结果max_allowed_packet 67108864 64MB需要注意的是max_allowed_packet对新连接生效更稳妥所以调整后建议重新连接 MySQL 再执行导入。三、参数二innodb_buffer_pool_sizeinnodb_buffer_pool_size是 InnoDB 最核心的性能参数用于缓存数据页和索引页。当前值只有128MB对于 8G 内存的服务器来说这个值明显偏小。百万级表导入、查询、索引维护时很容易频繁访问磁盘影响导入效率。本次将其在线调整为4GB执行命令SET GLOBAL innodb_buffer_pool_size 4 * 1024 * 1024 * 1024;验证SHOW GLOBAL VARIABLES LIKE innodb_buffer_pool_size; SHOW STATUS LIKE Innodb_buffer_pool_resize_status;结果innodb_buffer_pool_size 4294967296 4GB Innodb_buffer_pool_resize_status: Completed resizing buffer pool说明 Buffer Pool 已经在线扩容完成。四、为什么没有优先调整其他参数本次数据库中log_bin OFF说明没有开启 binlog因此sync_binlog对当前导入场景影响不大。innodb_flush_log_at_trx_commit当前为1这是最安全的事务刷盘模式。虽然改成2可以提升写入性能但存在极端情况下服务器断电丢失约 1 秒数据的风险。所以本次只优先调整低风险且必要的两个参数max_allowed_packet innodb_buffer_pool_size生产环境优化不能一上来就全参数梭哈。先补最短板才是稳妥路线。五、永久配置写入 my.cnf在线调整只是当前运行期间生效MySQL 重启后可能丢失配置。因此需要写入配置文件。先确认 MySQL 默认读取配置文件mysqld --verbose --help 2/dev/null | grep -A1 Default options结果/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf再确认实际存在的配置文件ls -l /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 2/dev/null结果只有/etc/my.cnf因此永久配置写入/etc/my.cnf。修改前备份cp /etc/my.cnf /etc/my.cnf.bak_$(date %F_%H%M%S)在[mysqld]下加入max_allowed_packet64M innodb_buffer_pool_size4G最终类似[mysqld] datadir/var/lib/mysql socket/var/lib/mysql/mysql.sock bind-address 0.0.0.0 max_allowed_packet64M innodb_buffer_pool_size4G max_connect_errors 100 symbolic-links0 skip-name-resolve skip-host-cache log-error/var/log/mysqld.log pid-file/var/run/mysqld/mysqld.pid注意如果写成下面这样是不生效的#max_allowed_packet64M因为前面有#表示该行被注释。六、最终优化结果本次在线优化完成后max_allowed_packet4MB → 64MB innodb_buffer_pool_size128MB → 4GB验证结果SHOW GLOBAL VARIABLES LIKE max_allowed_packet; SHOW GLOBAL VARIABLES LIKE innodb_buffer_pool_size; SHOW STATUS LIKE Innodb_buffer_pool_resize_status;结果显示max_allowed_packet 67108864 innodb_buffer_pool_size 4294967296 Completed resizing buffer pool说明两个核心参数已经在线生效。七、总结百万级大表导入前不要只盯着磁盘空间更要检查 MySQL 的核心参数。本次重点优化两项1. max_allowed_packet 控制单次 SQL 通信包大小避免大 SQL、大字段导入失败。 2. innodb_buffer_pool_size 控制 InnoDB 缓冲池大小避免默认 128MB 导致频繁磁盘 IO。生产环境建议能在线调整的先在线调整 需要永久保留的同步写入 my.cnf 高风险参数不要盲目修改 导入完成后再做最终验证。一句话总结百万级数据导入先把 4MB 的 packet 和 128MB 的 buffer pool 补起来别让默认配置拖垮导入效率。参考本次优化配置说明
百万级 MySQL 大表导入前，别让这两个默认参数拖垮性能_2026-05-20
一、问题背景今天在一台 MySQL 5.7.44 数据库服务器上需要导入一张百万级数据量的大表。导入前先对数据库基础配置进行检查避免因为默认参数过小导致导入失败或性能过慢。服务器基础配置如下CPU2 核 内存8G Swap5G 磁盘空间充足 MySQL版本5.7.44通过查询发现当前 MySQL 仍然使用了较多默认配置SHOW VARIABLES WHERE Variable_name IN ( max_allowed_packet, innodb_buffer_pool_size, innodb_flush_log_at_trx_commit, sync_binlog, log_bin, binlog_format, max_connections, wait_timeout );重点结果如下max_allowed_packet 4194304 -- 4MB innodb_buffer_pool_size 134217728 -- 128MB log_bin OFF innodb_flush_log_at_trx_commit 1其中真正影响本次百万级大表导入的核心短板主要有两个max_allowed_packet太小innodb_buffer_pool_size太小。二、参数一max_allowed_packetmax_allowed_packet用来控制 MySQL 客户端和服务端之间单次通信包允许的最大大小。它主要影响1. 大 SQL 导入 2. 批量 INSERT 3. mysqldump 恢复 4. TEXT、LONGTEXT、BLOB、JSON、大备注字段写入当前默认值只有4MB如果导入 SQL 文件中存在较大的批量 INSERT或者单行数据字段较长就可能出现Packet for query is too large MySQL server has gone away因此本次将其调整为64MB在线调整命令SET GLOBAL max_allowed_packet 64 * 1024 * 1024;验证SHOW GLOBAL VARIABLES LIKE max_allowed_packet; SELECT global.max_allowed_packet/1024/1024 AS max_allowed_packet_MB;结果max_allowed_packet 67108864 64MB需要注意的是max_allowed_packet对新连接生效更稳妥所以调整后建议重新连接 MySQL 再执行导入。三、参数二innodb_buffer_pool_sizeinnodb_buffer_pool_size是 InnoDB 最核心的性能参数用于缓存数据页和索引页。当前值只有128MB对于 8G 内存的服务器来说这个值明显偏小。百万级表导入、查询、索引维护时很容易频繁访问磁盘影响导入效率。本次将其在线调整为4GB执行命令SET GLOBAL innodb_buffer_pool_size 4 * 1024 * 1024 * 1024;验证SHOW GLOBAL VARIABLES LIKE innodb_buffer_pool_size; SHOW STATUS LIKE Innodb_buffer_pool_resize_status;结果innodb_buffer_pool_size 4294967296 4GB Innodb_buffer_pool_resize_status: Completed resizing buffer pool说明 Buffer Pool 已经在线扩容完成。四、为什么没有优先调整其他参数本次数据库中log_bin OFF说明没有开启 binlog因此sync_binlog对当前导入场景影响不大。innodb_flush_log_at_trx_commit当前为1这是最安全的事务刷盘模式。虽然改成2可以提升写入性能但存在极端情况下服务器断电丢失约 1 秒数据的风险。所以本次只优先调整低风险且必要的两个参数max_allowed_packet innodb_buffer_pool_size生产环境优化不能一上来就全参数梭哈。先补最短板才是稳妥路线。五、永久配置写入 my.cnf在线调整只是当前运行期间生效MySQL 重启后可能丢失配置。因此需要写入配置文件。先确认 MySQL 默认读取配置文件mysqld --verbose --help 2/dev/null | grep -A1 Default options结果/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf再确认实际存在的配置文件ls -l /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 2/dev/null结果只有/etc/my.cnf因此永久配置写入/etc/my.cnf。修改前备份cp /etc/my.cnf /etc/my.cnf.bak_$(date %F_%H%M%S)在[mysqld]下加入max_allowed_packet64M innodb_buffer_pool_size4G最终类似[mysqld] datadir/var/lib/mysql socket/var/lib/mysql/mysql.sock bind-address 0.0.0.0 max_allowed_packet64M innodb_buffer_pool_size4G max_connect_errors 100 symbolic-links0 skip-name-resolve skip-host-cache log-error/var/log/mysqld.log pid-file/var/run/mysqld/mysqld.pid注意如果写成下面这样是不生效的#max_allowed_packet64M因为前面有#表示该行被注释。六、最终优化结果本次在线优化完成后max_allowed_packet4MB → 64MB innodb_buffer_pool_size128MB → 4GB验证结果SHOW GLOBAL VARIABLES LIKE max_allowed_packet; SHOW GLOBAL VARIABLES LIKE innodb_buffer_pool_size; SHOW STATUS LIKE Innodb_buffer_pool_resize_status;结果显示max_allowed_packet 67108864 innodb_buffer_pool_size 4294967296 Completed resizing buffer pool说明两个核心参数已经在线生效。七、总结百万级大表导入前不要只盯着磁盘空间更要检查 MySQL 的核心参数。本次重点优化两项1. max_allowed_packet 控制单次 SQL 通信包大小避免大 SQL、大字段导入失败。 2. innodb_buffer_pool_size 控制 InnoDB 缓冲池大小避免默认 128MB 导致频繁磁盘 IO。生产环境建议能在线调整的先在线调整 需要永久保留的同步写入 my.cnf 高风险参数不要盲目修改 导入完成后再做最终验证。一句话总结百万级数据导入先把 4MB 的 packet 和 128MB 的 buffer pool 补起来别让默认配置拖垮导入效率。参考本次优化配置说明
