印刻数据库

外观

MySQL 核心参数列表 ​

连接与线程参数 ​

max_connections ​

  • 默认值：151
  • 推荐值：根据服务器资源和业务需求调整，一般 500-2000
  • 作用：限制 MySQL 服务器允许的最大并发连接数
  • 注意事项：
    • 过高的值可能导致内存耗尽
    • 建议根据 max_connections * (sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size) 估算内存使用
    • MySQL 8.0 支持动态调整

max_user_connections ​

  • 默认值：0（无限制）
  • 推荐值：根据用户需求设置，如 100-500
  • 作用：限制单个用户的最大并发连接数
  • 注意事项：
    • 0 表示无限制
    • 用于防止单个用户占用过多连接资源

thread_cache_size ​

  • 默认值：
    • MySQL 5.7：64
    • MySQL 8.0：64（自动调整）
  • 推荐值：50-200
  • 作用：缓存线程数，减少线程创建和销毁的开销
  • 注意事项：
    • 对于高并发场景，适当增大该值
    • MySQL 8.0 会自动调整，可设置为 0 让系统自动管理

thread_stack ​

  • 默认值：
    • Linux：256KB
    • Windows：256KB
  • 推荐值：默认值即可，一般无需调整
  • 作用：每个线程的堆栈大小
  • 注意事项：
    • 过大的值会导致内存使用增加
    • 过小的值可能导致线程栈溢出

wait_timeout ​

  • 默认值：28800（8小时）
  • 推荐值：600-3600（10分钟-1小时）
  • 作用：服务器关闭非活动连接之前等待的秒数
  • 注意事项：
    • 过小的值可能导致频繁的连接断开和重建
    • 过大的值可能导致连接资源占用过多

interactive_timeout ​

  • 默认值：28800（8小时）
  • 推荐值：600-3600（10分钟-1小时）
  • 作用：服务器关闭交互式连接之前等待的秒数
  • 注意事项：
    • 交互式连接指使用 mysql 客户端等交互式工具建立的连接
    • 建议与 wait_timeout 设置相同的值

内存参数 ​

innodb_buffer_pool_size ​

  • 默认值：
    • MySQL 5.7：128MB
    • MySQL 8.0：自动设置为物理内存的 50%
  • 推荐值：物理内存的 50%-80%
  • 作用：InnoDB 缓冲池大小，用于缓存数据和索引
  • 注意事项：
    • 最重要的性能参数之一
    • 建议设置为物理内存的 50%-80%
    • 对于 dedicated MySQL 服务器，可设置为物理内存的 70%-80%
    • MySQL 8.0 支持动态调整

innodb_buffer_pool_instances ​

  • 默认值：
    • MySQL 5.7：根据 innodb_buffer_pool_size 自动设置
    • MySQL 8.0：根据 innodb_buffer_pool_size 自动设置
  • 推荐值：
    • 对于 innodb_buffer_pool_size < 8GB：1
    • 对于 innodb_buffer_pool_size >= 8GB：4-8
  • 作用：InnoDB 缓冲池实例数量
  • 注意事项：
    • 每个实例有自己的锁和管理结构
    • 过多的实例会增加内存开销和锁竞争
    • 建议每个实例大小不小于 1GB

key_buffer_size ​

  • 默认值：
    • MySQL 5.7：8MB
    • MySQL 8.0：8MB
  • 推荐值：
    • 对于 MyISAM 表：物理内存的 10%-20%
    • 对于 InnoDB 表：16MB-64MB
  • 作用：MyISAM 索引缓冲区大小
  • 注意事项：
    • 主要用于 MyISAM 表的索引缓存
    • 对于主要使用 InnoDB 的系统，该值不需要太大

sort_buffer_size ​

  • 默认值：256KB
  • 推荐值：256KB-2MB
  • 作用：每个会话的排序缓冲区大小
  • 注意事项：
    • 每个连接都会分配该大小的缓冲区
    • 过大的值会导致内存使用激增
    • 建议设置为适中值，如 256KB-2MB

read_buffer_size ​

  • 默认值：128KB
  • 推荐值：128KB-1MB
  • 作用：每个会话的顺序读取缓冲区大小
  • 注意事项：
    • 用于 MyISAM 表的顺序扫描
    • 每个连接都会分配该大小的缓冲区
    • 过大的值会导致内存使用激增

read_rnd_buffer_size ​

  • 默认值：256KB
  • 推荐值：256KB-4MB
  • 作用：每个会话的随机读取缓冲区大小
  • 注意事项：
    • 用于 MyISAM 表的随机读取
    • 每个连接都会分配该大小的缓冲区
    • 过大的值会导致内存使用激增

join_buffer_size ​

  • 默认值：256KB
  • 推荐值：256KB-4MB
  • 作用：每个会话的连接缓冲区大小
  • 注意事项：
    • 用于表连接操作
    • 每个连接都会分配该大小的缓冲区
    • 过大的值会导致内存使用激增

InnoDB 存储引擎参数 ​

innodb_data_file_path ​

  • 默认值：ibdata1:12M:autoextend
  • 推荐值：根据实际需求设置，如 ibdata1:512M:autoextend
  • 作用：InnoDB 数据文件路径和大小
  • 注意事项：
    • 第一个数据文件必须大于等于 12MB
    • autoextend 表示自动扩展
    • 建议将 ibdata1 设置为固定大小，避免频繁扩展

innodb_file_per_table ​

  • 默认值：
    • MySQL 5.6 及以上：ON
    • MySQL 5.5 及以下：OFF
  • 推荐值：ON
  • 作用：是否为每个 InnoDB 表创建独立的数据文件
  • 注意事项：
    • ON 表示每个表的数据和索引存储在单独的 .ibd 文件中
    • 便于表空间管理和清理
    • 建议开启

innodb_log_file_size ​

  • 默认值：
    • MySQL 5.7：48MB
    • MySQL 8.0：50331648（约 48MB）
  • 推荐值：256MB-4GB
  • 作用：InnoDB 重做日志文件大小
  • 注意事项：
    • 较大的日志文件可以减少 checkpoint 频率，提高性能
    • 但会增加崩溃恢复时间
    • 建议设置为 256MB-4GB
    • 多个日志文件的总大小不应超过 innodb_buffer_pool_size 的 1/2

innodb_log_files_in_group ​

  • 默认值：2
  • 推荐值：2-4
  • 作用：InnoDB 重做日志文件组中的文件数量
  • 注意事项：
    • 默认为 2 个文件
    • 建议保持默认值或设置为 4

innodb_log_buffer_size ​

  • 默认值：
    • MySQL 5.7：16MB
    • MySQL 8.0：16777216（16MB）
  • 推荐值：16MB-128MB
  • 作用：InnoDB 日志缓冲区大小
  • 注意事项：
    • 用于缓存重做日志，减少磁盘 I/O
    • 对于写入密集型应用，可适当增大

innodb_flush_log_at_trx_commit ​

  • 默认值：1
  • 推荐值：
    • 生产环境：1
    • 测试环境：0 或 2
  • 作用：控制事务提交时重做日志的刷新策略
  • 注意事项：
    • 1：每次事务提交都刷新到磁盘，最安全但性能最差
    • 2：每次事务提交都写入操作系统缓存，每秒刷新到磁盘
    • 0：每秒将日志缓冲区写入磁盘并刷新，性能最好但最不安全

innodb_flush_method ​

  • 默认值：
    • Linux：fsync
    • Windows：unbuffered
  • 推荐值：O_DIRECT
  • 作用：InnoDB 数据和日志文件的刷新方法
  • 注意事项：
    • O_DIRECT：绕过操作系统缓存，直接写入磁盘，适合使用 RAID 控制器的场景
    • O_DSYNC：使用 O_DSYNC 方式刷新
    • fsync：使用 fsync() 函数刷新

innodb_lock_wait_timeout ​

  • 默认值：50
  • 推荐值：50-300
  • 作用：InnoDB 事务等待行锁的超时时间（秒）
  • 注意事项：
    • 超过该时间未获取锁的事务会被回滚
    • 对于长时间运行的事务，可适当增大

innodb_deadlock_detect ​

  • 默认值：ON
  • 推荐值：ON
  • 作用：是否启用死锁检测
  • 注意事项：
    • 对于高并发系统，死锁检测可能带来性能开销
    • 但禁用死锁检测可能导致事务长时间等待
    • 建议保持启用

性能相关参数 ​

query_cache_type ​

  • 默认值：
    • MySQL 5.7：OFF
    • MySQL 8.0：已移除
  • 推荐值：OFF
  • 作用：查询缓存类型
  • 注意事项：
    • 0/OFF：禁用查询缓存
    • 1/ON：启用查询缓存，缓存所有查询
    • 2/DEMAND：仅缓存带有 SQL_CACHE 提示的查询
    • MySQL 8.0 已移除查询缓存功能

query_cache_size ​

  • 默认值：
    • MySQL 5.7：1MB
    • MySQL 8.0：已移除
  • 推荐值：0
  • 作用：查询缓存大小
  • 注意事项：
    • 仅在 query_cache_type 为 ON 或 DEMAND 时有效
    • MySQL 8.0 已移除查询缓存功能

slow_query_log ​

  • 默认值：OFF
  • 推荐值：ON
  • 作用：是否启用慢查询日志
  • 注意事项：
    • 用于记录执行时间超过 long_query_time 的查询
    • 建议在生产环境启用

long_query_time ​

  • 默认值：10
  • 推荐值：0.5-2
  • 作用：慢查询的阈值（秒）
  • 注意事项：
    • 执行时间超过该值的查询会被记录到慢查询日志
    • 建议设置为 0.5-2 秒

log_queries_not_using_indexes ​

  • 默认值：OFF
  • 推荐值：OFF（生产环境），ON（测试环境）
  • 作用：是否记录未使用索引的查询
  • 注意事项：
    • 启用后会记录所有未使用索引的查询
    • 生产环境可能会产生大量日志，建议禁用

max_allowed_packet ​

  • 默认值：
    • MySQL 5.7：4MB
    • MySQL 8.0：67108864（64MB）
  • 推荐值：64MB-256MB
  • 作用：允许的最大数据包大小
  • 注意事项：
    • 用于限制客户端和服务器之间传输的数据包大小
    • 对于大对象或大量数据传输，需要增大该值

tmp_table_size ​

  • 默认值：16MB
  • 推荐值：64MB-256MB
  • 作用：内存临时表的最大大小
  • 注意事项：
    • 超过该大小的临时表会转换为磁盘临时表
    • 与 max_heap_table_size 取较小值

max_heap_table_size ​

  • 默认值：16MB
  • 推荐值：64MB-256MB
  • 作用：内存表的最大大小
  • 注意事项：
    • 超过该大小的内存表无法插入数据
    • 与 tmp_table_size 取较小值

复制相关参数 ​

server_id ​

  • 默认值：1
  • 推荐值：唯一的服务器 ID
  • 作用：标识复制拓扑中的服务器
  • 注意事项：
    • 复制拓扑中的每个服务器必须有唯一的 server_id
    • 建议使用 IP 地址的最后一段或其他唯一标识

log_bin ​

  • 默认值：OFF
  • 推荐值：ON
  • 作用：是否启用二进制日志
  • 注意事项：
    • 用于复制和数据恢复
    • 主库必须启用
    • 建议在从库也启用，便于故障切换

binlog_format ​

  • 默认值：
    • MySQL 5.7：ROW
    • MySQL 8.0：ROW
  • 推荐值：ROW
  • 作用：二进制日志格式
  • 注意事项：
    • STATEMENT：记录 SQL 语句
    • ROW：记录行级变更
    • MIXED：混合模式
    • 建议使用 ROW 格式，提供最安全的复制

binlog_row_image ​

  • 默认值：FULL
  • 推荐值：FULL
  • 作用：行格式二进制日志中记录的行数据量
  • 注意事项：
    • FULL：记录完整的行数据
    • MINIMAL：仅记录变更的列
    • NOBLOB：记录完整行数据，但不包括 BLOB 和 TEXT 列
    • 建议使用 FULL，确保复制安全

sync_binlog ​

  • 默认值：1
  • 推荐值：
    • 生产环境：1
    • 测试环境：0
  • 作用：控制二进制日志的刷新策略
  • 注意事项：
    • 1：每次事务提交都刷新二进制日志到磁盘
    • 0：由操作系统决定何时刷新
    • N：每 N 个事务刷新一次
    • 建议设置为 1，确保二进制日志的安全性

relay_log ​

  • 默认值：主机名-relay-bin
  • 推荐值：根据实际需求设置，如 relay-bin
  • 作用：从库中继日志文件名
  • 注意事项：
    • 从库用于存储从主库接收的二进制日志

relay_log_recovery ​

  • 默认值：
    • MySQL 5.7：ON
    • MySQL 8.0：ON
  • 推荐值：ON
  • 作用：是否启用中继日志自动恢复
  • 注意事项：
    • 启用后，从库启动时会自动恢复损坏的中继日志
    • 建议开启

安全相关参数 ​

skip_name_resolve ​

  • 默认值：OFF
  • 推荐值：ON
  • 作用：是否跳过域名解析
  • 注意事项：
    • ON 表示不进行域名解析，只使用 IP 地址
    • 可以提高连接建立速度，避免 DNS 解析问题
    • 但需要在授权表中使用 IP 地址而非主机名

local_infile ​

  • 默认值：ON
  • 推荐值：OFF
  • 作用：是否允许使用 LOAD DATA LOCAL INFILE 语句
  • 注意事项：
    • 禁用可以提高安全性，防止未经授权的文件访问
    • 如需使用，建议仅在特定用户或场景下启用

secure_file_priv ​

  • 默认值：
    • MySQL 5.7 及以上：NULL（禁用）
    • MySQL 5.6 及以下：空（允许任意目录）
  • 推荐值：指定特定目录，如 /var/lib/mysql-files/
  • 作用：限制 LOAD DATA 和 SELECT ... INTO OUTFILE 语句的文件操作目录
  • 注意事项：
    • NULL 表示禁用相关操作
    • 空字符串表示允许任意目录（不安全）
    • 建议指定特定目录

sql_mode ​

  • 默认值：
    • MySQL 5.7：STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    • MySQL 8.0：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
  • 推荐值：根据实际需求调整，建议启用严格模式
  • 作用：设置 SQL 模式，控制 MySQL 的 SQL 语法和数据验证
  • 注意事项：
    • 严格模式可以提高数据完整性
    • 建议启用严格模式和 ONLY_FULL_GROUP_BY 等选项

其他重要参数 ​

character_set_server ​

  • 默认值：
    • MySQL 5.7：latin1
    • MySQL 8.0：utf8mb4
  • 推荐值：utf8mb4
  • 作用：服务器默认字符集
  • 注意事项：
    • utf8mb4 支持完整的 UTF-8 字符集，包括 emoji
    • 建议使用 utf8mb4

collation_server ​

  • 默认值：
    • MySQL 5.7：latin1_swedish_ci
    • MySQL 8.0：utf8mb4_0900_ai_ci
  • 推荐值：utf8mb4_0900_ai_ci 或 utf8mb4_unicode_ci
  • 作用：服务器默认排序规则
  • 注意事项：
    • 与 character_set_server 对应
    • utf8mb4_0900_ai_ci 是 MySQL 8.0 的默认值，性能较好
    • utf8mb4_unicode_ci 兼容性更好

max_connect_errors ​

  • 默认值：100
  • 推荐值：1000-10000
  • 作用：允许的最大连接错误数
  • 注意事项：
    • 超过该值的主机将被禁止连接
    • 可以使用 FLUSH HOSTS 命令解除禁止

back_log ​

  • 默认值：
    • MySQL 5.7：80
    • MySQL 8.0：151
  • 推荐值：128-512
  • 作用：MySQL 监听队列的最大长度
  • 注意事项：
    • 用于处理连接请求的队列
    • 对于高并发系统，可适当增大

table_open_cache ​

  • 默认值：
    • MySQL 5.7：2000
    • MySQL 8.0：4000
  • 推荐值：2000-10000
  • 作用：表缓存大小
  • 注意事项：
    • 用于缓存打开的表描述符
    • 对于有大量表的系统，可适当增大

table_definition_cache ​

  • 默认值：
    • MySQL 5.7：1400
    • MySQL 8.0：2000
  • 推荐值：1000-5000
  • 作用：表定义缓存大小
  • 注意事项：
    • 用于缓存表定义
    • 对于有大量表的系统，可适当增大

常见问题（FAQ） ​

Q1: 如何调整 MySQL 参数？ ​

A1: 调整 MySQL 参数的方法有两种：

  1. 临时调整：使用 SET GLOBAL parameter_name = value; 命令，重启后失效
  2. 永久调整：在 my.cnf 或 my.ini 配置文件中修改，重启后生效

Q2: 如何查看当前 MySQL 参数值？ ​

A2: 查看当前 MySQL 参数值的方法：

  • 使用 SHOW VARIABLES LIKE 'parameter_name'; 查看单个参数
  • 使用 SHOW GLOBAL VARIABLES; 查看所有全局参数
  • 使用 SHOW SESSION VARIABLES; 查看当前会话参数

Q3: 如何确定合适的参数值？ ​

A3: 确定合适的参数值需要考虑以下因素：

  • 服务器硬件配置（CPU、内存、磁盘）
  • 业务场景（OLTP、OLAP、混合负载）
  • 数据量大小
  • 并发连接数
  • MySQL 版本

Q4: 哪些参数对性能影响最大？ ​

A4: 对性能影响最大的参数包括：

  • innodb_buffer_pool_size
  • innodb_log_file_size
  • innodb_log_buffer_size
  • innodb_flush_log_at_trx_commit
  • max_connections
  • sort_buffer_size
  • read_buffer_size
  • join_buffer_size

Q5: 如何监控参数调整的效果？ ​

A5: 监控参数调整效果的方法：

  • 使用 SHOW GLOBAL STATUS 查看状态变量
  • 使用 Performance Schema 和 Sys Schema 进行性能分析
  • 使用监控工具（如 Prometheus + Grafana、Zabbix）进行实时监控
  • 进行基准测试，比较调整前后的性能差异

Q6: MySQL 8.0 有哪些参数变化？ ​

A6: MySQL 8.0 的参数变化包括：

  • 移除了查询缓存相关参数（query_cache_type、query_cache_size 等）
  • 默认字符集改为 utf8mb4
  • 增强了 innodb_directories 参数
  • 新增了 innodb_dedicated_server 参数，自动调整 InnoDB 相关参数
  • 调整了一些参数的默认值

Q7: 如何备份和恢复 MySQL 配置？ ​

A7: 备份和恢复 MySQL 配置的方法：

  • 备份：复制 my.cnf 或 my.ini 配置文件
  • 恢复：将备份的配置文件复制到相应位置，重启 MySQL 服务

Q8: 如何处理参数配置错误？ ​

A8: 处理参数配置错误的方法：

  • 查看 MySQL 错误日志，找出具体的错误参数
  • 修正配置文件中的错误参数
  • 重启 MySQL 服务
  • 如果无法启动，可使用 --skip-grant-tables 选项启动，修改配置后重启

Q9: 哪些参数支持动态调整？ ​

A9: 支持动态调整的参数包括：

  • innodb_buffer_pool_size（MySQL 5.7 及以上）
  • max_connections
  • wait_timeout
  • interactive_timeout
  • innodb_log_buffer_size
  • innodb_flush_log_at_trx_commit
  • slow_query_log
  • long_query_time

Q10: 如何优化内存使用？ ​

A10: 优化 MySQL 内存使用的方法：

  • 合理设置 innodb_buffer_pool_size，避免过大导致系统内存不足
  • 调整 sort_buffer_size、read_buffer_size 等会话级参数，避免每个连接占用过多内存
  • 监控内存使用情况，及时调整参数
  • 考虑使用大页内存（HugePages）提高内存使用效率

Q11: 如何优化 InnoDB 性能？ ​

A11: 优化 InnoDB 性能的方法：

  • 合理设置 innodb_buffer_pool_size
  • 调整 innodb_log_file_size 和 innodb_log_files_in_group
  • 优化 innodb_flush_log_at_trx_commit 和 innodb_flush_method
  • 启用 innodb_file_per_table
  • 调整 innodb_buffer_pool_instances

Q12: 如何优化复制性能？ ​

A12: 优化 MySQL 复制性能的方法：

  • 使用 ROW 格式的二进制日志
  • 调整 innodb_flush_log_at_trx_commit 和 sync_binlog
  • 启用从库并行复制
  • 优化网络连接
  • 确保从库硬件配置与主库匹配