第14章 MySQL优化
针对 MySQL 5.7 的调优，可以从多个维度进行，包括服务器硬件配置、MySQL 配置参数调整、查询优化和监控等方面。以下是一些针对 MySQL 5.7 的调优最佳实践：
1.硬件优化
**存储**：使用 SSD 可以显著提高 I/O 性能
**内存**：增加服务器 ECC内存 可以让更多的数据和索引保持在内存中，减少磁盘 I/O，尤其是通过增加 InnoDB 的缓冲池大小。
**CPU**：使用更快的 CPU 或多核 CPU 可以提高计算能力，英特尔 至强xeon 处理器 E3 E5 E7 16C
**RAID：**建议RAID10，兼顾安全及速度
**云服务器：**一步到位，把自己优化掉
2.系统优化
优化内核参数：
文件参数：
系统文件最大打开数 --> Linux里一切皆文件，每一个网络连接其实都算是创建一个文件
调大进程间传递消息的数据量和数据大小
网络调优：
增加SYN建立连接状态的数量
减少SYN建立连接状态时重试发包的次数
减少TIME_WAIT等待时间
快速回收处于TIME_WAIT状态的连接
允许复用处于TIME_WAIT状态的连接
调大处于TIME_WAIT状态的连接最大数量
# 在内核配置文件下添加参数
vim /etc/sysctl.conf
net.core.netdev_max_backlog = 400000
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 819200
# 复用处理TIME-WAIT状态的连接
net.ipv4.tcp_tw_reuse=1
# 更快地回收TIME-WAIT套接字
net.ipv4.tcp_tw_recycle=0
vim limits.conf
nofile 63000
分区挂载：
使用xfs分区
数据目录挂载独立的磁盘
避免使用LVM 软RAID
专机专用：
只允许MySQL服务，不允许任何抢占资源的软件
3.配置优化
连接层：
最大连接数
最大数据包传输大小
慢日志：
开启慢日志
慢语句超时的时间
不走索引的慢语句记录
binlog日志：
开启binlog
binlog日志存储路径
binlog单个文件最大容量
binlog文件保留时间
binlog记录格式
binlog刷盘模式
存储引擎：
InnoDB buffer poll缓存池容量大小 系统内存50-80%
InnoDB 行锁等待超时时间
死锁检测开启
死锁超时自动回滚事务
复制：
开启GTID模式
server_id要不同
从库允许保存binlog
从库可自身信息，用于自动识别、监控等。
将复制信息存储在表中，而非文件中，方便管理和持久化。
修改 /etc/my.cnf 或者 MySQL 的配置文件来调整以下参数：
连接层：
# 最大连接数，决定了同时能够建立的最大连接数。
# 当出现“Too many connections”错误时，可适当调大。
# 建议值：100~3000 或根据实际并发情况调整。
max_connections = 1000
# 当客户端连接出现错误次数达到该值后，服务器会暂时阻断这个客户端的连接。
# 建议值：100（若网络波动频繁可适当调大）。
max_connect_errors = 100
# 非交互连接的超时时间（秒）。当超过此时间没有任何操作后，服务器将断开连接。
# 例如后端脚本或应用程序连接超时。
# 建议值：600（10分钟）或根据实际需要调整。
wait_timeout = 600
# 交互式连接的超时时间（秒）。例如通过 MySQL 客户端终端交互式连接。
# 建议值：600（10分钟），交互式操作一般足够。
interactive_wait_timeout = 600
# MySQL 读取数据包超时时间（秒）。如果在此时间内没有收到数据包，则会断开连接。
# 建议值：30 或更高。
net_read_timeout = 30
# MySQL 写数据包超时时间（秒）。如果在此时间内无法写入数据包（网络异常等），则会断开连接。
# 建议值：30 或更高。
net_write_timeout = 30
# 最大允许的通信数据包大小（字节），通常用于控制 BLOB/TEXT 等大字段插入。
# 需要设置得足够大以保证大数据传输，但也不能过大以免浪费内存。
# 建议值：16M ~ 64M，一般 64M 足够。
max_allowed_packet = 64M
server层：
# 安全更新模式，防止在没有 WHERE 条件时执行 UPDATE 或 DELETE 操作。
# 开启后可减少误操作风险，但可能在某些场景需要临时关闭。
# 建议设置：1
sql_safe_updates = 1
# 是否开启慢查询日志，用于记录执行时间超过 long_query_time 的SQL。
# 建议在生产环境开启，方便排查性能问题。
slow_query_log = ON
# 慢查询日志存放的文件路径。
# 需确保 MySQL 有写入权限，并注意磁盘容量。
slow_query_log_file = /xxx/mysql-slow.log
# 定义慢查询的阈值(秒)，执行时间大于该值将被记录到慢查询日志。
# 建议值：1~5 秒 或根据实际性能要求设置。
long_query_time = 1
# 是否记录未使用索引的查询。
# 建议开启，以便及时发现需要优化的查询。
log_queries_not_using_indexes = ON
# 每秒仅记录一定条数未使用索引的查询，避免日志量过大而影响性能。
# 建议值：10
log_throttle_queries_not_using_indexes = 10
# sort_buffer、join_buffer、read_buffer、read_rnd_buffer 是 MySQL 在对结果集进行排序、连接、扫描等操作时使用的内存空间大小。
# 这些参数通常在会话级别设置，值过大可能引起内存不足；过小则可能影响查询性能。
# 建议根据服务器内存大小和实际业务场景进行设置：
# sort_buffer_size = 2M ~ 4M
# join_buffer_size = 2M ~ 4M
# read_buffer_size = 2M ~ 4M
# read_rnd_buffer_size = 2M ~ 4M
# 这里仅做示例：
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
# tmp_table_size / max_heap_table_size 决定了内存临时表的最大大小，
# 如果超过此大小会自动转为磁盘临时表，影响性能。
# 建议值：256M ~ 1G，需根据内存容量及临时表使用量调整。
tmp_table_size = 256M
max_heap_table_size = 256M
# sql_mode 用于定义 SQL 的语法行为和兼容性。建议保持相对严格的模式，防止潜在的数据问题。
# 常用推荐：STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
sql_mode = "建议保持默认"
# 最大执行时间（毫秒）。超过此时间的语句将被强制终止，可防止低效语句长时间占用资源。
# 建议值：0 表示不限制，如要限制可根据业务需求设置。
max_execution_time = 0
# 锁等待超时时间（秒）。超过此时间将报告锁等待超时错误。
# 建议值：50~120。
lock_wait_timeout = 50
# 设置是否忽略表名大小写的差异。
# 0：区分大小写；1：不区分大小写。Windows 建议使用 1，与 Windows 文件系统一致。
lower_case_table_names = 1
# 线程缓存大小，用于缓存连接线程，减少创建/销毁线程的开销。
# 建议值：32~256，根据并发量和内存情况设置。
thread_cache_size = 64
# 服务器默认字符集，推荐使用 utf8mb4 支持更多字符。
# character_set_server = utf8 或者 utf8mb4
character_set_server = utf8mb4
# 日志时间戳使用的时区。SYSTEM 表示使用操作系统时间。
log_timestamps = SYSTEM
# 在每个客户端连接时执行的初始化语句，一般用于设置字符集等。
# 需与 character_set_server 保持一致。
init_connect = "SET NAMES utf8mb4"
# 是否开启事件调度器，默认 OFF。
# 在不需要定时任务执行时关闭可减少无用开销。
event_scheduler = OFF
# 安全导出/导入文件的路径，用于 LOAD DATA INFILE 或 SELECT ... INTO OUTFILE。
# 建议将其指向一个安全路径，避免数据泄露或被覆盖。
secure-file-priv = /xxx/mysql-files
# 二进制日志过期天数，超过该天数的 binlog 自动删除。
# 建议值：7~30 天，视备份及审计需求而定。
expire_logs_days = 10
# sync_binlog 用来控制每次事务提交时，是否立即将 binlog 刷新到磁盘。
# 1 表示每次事务提交都刷新；安全性高但性能低；可根据业务情况设置。
sync_binlog = 1
# 打开二进制日志，并指定存放路径。
# 注意目录的磁盘空间；生产环境建议与数据目录分开。
log-bin = /opt/log/mysql/blog/mysql-bin
log-bin-index = /opt/log/mysql/blog/mysql-bin.index
# 每个 binlog 文件的大小上限，超过此值会滚动生成下一个 binlog 文件。
# 建议值：100M ~ 1G。
max_binlog_size = 500M
# binlog_format 表示二进制日志的记录格式。推荐使用 ROW 格式更精确，但会增大日志体积。
binlog_format = ROW
# binlog 缓存大小，单个事务可以使用的 binlog 缓存大小。
# 当事务过大时，可增大该值避免写到临时文件。
max_binlog_cache_size = 2G
# 类似 max_binlog_cache_size，只用于语句缓存。
max_binlog_stmt_cache_size = 2G
存储引擎层：
# 事务隔离级别，推荐使用 READ-COMMITTED 或 REPEATABLE-READ。
# READ-COMMITTED 能减少行锁竞争，适合大多数应用场景。
transaction-isolation = "READ-COMMITTED"
# InnoDB 数据文件存放目录
innodb_data_home_dir = /xxx
# InnoDB redo 日志文件存放目录
innodb_log_group_home_dir = /xxx
# InnoDB redo 日志文件大小，增大该值可减少日志切换，但恢复时需要更长的扫描时间。
# 如果修改该值，需要先关闭 MySQL、删除或备份原日志文件，然后再启动 MySQL。
# 建议值：512M ~ 4G。常见为 1G ~ 2G。
innodb_log_file_size = 2048M
# InnoDB redo 日志文件组数量。通常 2~3 个即可，过多无明显收益。
innodb_log_files_in_group = 3
# 事务提交时刷新 redo 日志的策略：
# 0：不实时刷新（可能丢数据），1：实时刷新（最安全、性能一般），2：每秒刷新（安全性和性能折中）。
# 通常在 OLTP 应用中可设置为 1 或 2。
innodb_flush_log_at_trx_commit = 2
# 写数据到 InnoDB 文件时使用的刷新方法。O_DIRECT 能减少缓存重复，fsync 适用于不同操作系统。
# 如果所在文件系统支持 O_DIRECT，可优先使用；否则使用 fsync。
innodb_flush_method = O_DIRECT
# IO 能力设定。用于限制 InnoDB 后台刷脏页及其他 IO 操作的频率。
# 建议值：100 ~ 几千；若磁盘是 SSD，性能较好，可调大。
innodb_io_capacity = 1000
# IO 能力最大值。可在高负载时临时提高写入速度，避免积压过多脏页。
innodb_io_capacity_max = 4000
# InnoDB Buffer Pool 大小。主要用来缓存索引和数据，以提高性能。
# 通常建议配置为物理内存的 50% ~ 80%。需预留内存给系统和其他进程。
# 大型数据库可适当增大；这里示例 64G。
innodb_buffer_pool_size = 64G
# Buffer Pool 的实例数，避免单个 buffer pool 互斥导致的性能瓶颈。
# 一般每 4~8G 分配 1 个实例。示例设为 4。
innodb_buffer_pool_instances = 4
# InnoDB redo 日志缓存大小。若大事务较多，可适当增大。
# 建议值：8M ~ 128M。
innodb_log_buffer_size = 64M
# InnoDB 最大脏页比例，达到该比例后会加速刷脏页，避免崩溃时数据丢失过多。
# 默认值 75。若写入量大可适当调高到 85。
innodb_max_dirty_pages_pct = 85
# InnoDB 行锁等待超时时间（秒）。超过该时间将报错。
# 建议值：10~30。
innodb_lock_wait_timeout = 10
# InnoDB 能打开的表的数量限制，主要影响系统表缓存。
# 建议值：大于数据库内表总数，默认 300。可调整到几万。
innodb_open_files = 63000
# InnoDB 页面清理线程数，配合高并发或大 Buffer Pool 时可增加。
# 建议值：CPU 核心数/4 或根据压测结果设置。
innodb_page_cleaners = 4
# 排序相关的内存缓冲大小（InnoDB 特有）。
# 对大规模排序操作有帮助，但要注意不能过大导致内存不足。
innodb_sort_buffer_size = 64M
# 是否打印出所有死锁信息到错误日志。开启可帮助快速排查死锁。
innodb_print_all_deadlocks = 1
# 当超时或异常时，是否自动回滚事务。
# ON 可以避免长时间占用锁资源。
innodb_rollback_on_timeout = ON
# 是否开启死锁检测。如果大并发环境下经常出现死锁，可关闭以避免大量检测开销。
# 但关闭后不会自动检测死锁，需要通过超时处理。
innodb_deadlock_detect = ON
复制相关：
# relay_log 存放位置，注意磁盘空间。
relay_log = /opt/log/mysql/blog/relay
relay_log_index = /opt/log/mysql/blog/relay.index
# relay log 单个文件大小上限，超过将滚动生成下一个 relay log 文件。
max_relay_log_size = 500M
# 是否自动清理已经应用完成的 relay log。
relay_log_purge = ON
# 开启 relay log 的自动恢复功能，崩溃后自动恢复复制状态，减少手动干预。
relay_log_recovery = ON
# 半同步复制，主库在提交事务后，会等待至少一个从库确认才返回成功。
# 有助于减少数据丢失风险，但会增加主库延迟。
rpl_semi_sync_master_enabled = ON
# 主库等待从库确认的超时时间，单位毫秒。
# 超时后将自动切换为异步复制，避免阻塞过久。
rpl_semi_sync_master_timeout = 1000
# 半同步复制调试级别，范围 0~32，一般不需要特别调整。
rpl_semi_sync_master_trace_level = 32
# 主库等待的从库数量，通常为 1 即可保证至少一台从库收到事务。
rpl_semi_sync_master_wait_for_slave_count = 1
# 当没有从库可用时，主库是否继续等待。如果为 ON，且从库不可用，则会阻塞。
# 推荐 ON，确保数据安全；如果需要高可用则视情况选择。
rpl_semi_sync_master_wait_no_slave = ON
# 主库在事务提交的哪个阶段等待从库。AFTER_SYNC 表示已经写入 binlog 后再等待。
# 另一个常见值为 AFTER_COMMIT。
rpl_semi_sync_master_wait_point = AFTER_SYNC
# 从库是否启用半同步复制。
rpl_semi_sync_slave_enabled = ON
# 半同步复制调试级别，同 master_trace_level。
rpl_semi_sync_slave_trace_level = 32
# Binlog group commit 相关参数，用于批量同步，降低磁盘刷写次数，提高性能。
# binlog_group_commit_sync_delay：延迟指定微秒后再刷盘，可提升吞吐量但增加延时。
# binlog_group_commit_sync_no_delay_count：累计多少事务后再刷盘。
binlog_group_commit_sync_delay = 1
binlog_group_commit_sync_no_delay_count = 1000
# 是否开启 GTID（全局事务 ID），并强制一致性检查。
# 用于更可靠的复制，避免重复或丢失事务。
gtid_mode = ON
enforce_gtid_consistency = ON
# 是否验证主库传给从库的 binlog 校验和数据完整性。
master_verify_checksum = ON
# sync_master_info = 1 表示在每次事务后将复制信息同步到磁盘，提高安全性。
# 如果性能瓶颈较大，可以改为更低频率同步。
sync_master_info = 1
# 跳过自动启动从库复制进程。适用于需要人工控制复制启动的场景。
skip-slave-start = 1
# read_only 与 super_read_only 配合使用可以保护从库、只读环境。
# 若需要切换主从，或做维护时可打开。
# read_only = ON
# super_read_only = ON
# 是否在从库上记录更新到 binlog。若从库还需要继续复制下级从库时，需要设置为 ON。
log_slave_updates = ON
# server_id 表示实例唯一 ID，用于区分不同服务器的复制。
server_id = xxx
# 在主从架构中，从库可向主库报告自身信息，用于自动识别、监控等。
report_host = xxxx
report_port = 3306
# 并行复制的模式与并行线程数。LOGICAL_CLOCK 模式在 5.7+ 中表现较好。
# slave_parallel_workers 表示并行线程数量，需根据负载和硬件评估。
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
# 将复制信息存储在表中，而非文件中，方便管理和持久化。
master_info_repository = TABLE
relay_log_info_repository = TABLE
4.SQL优化
**使用 EXPLAIN**：对查询使用 EXPLAIN 来分析和优化查询执行计划。
**索引优化**：确保使用有效的索引，定期检查和优化索引。
**避免全表扫描**：尽可能修改查询以使用索引，减少全表扫描。
5.监控和维护
**慢语句监控：**
**死锁监控：**
**使用性能模式**：使用 SHOW ENGINE INNODB STATUS 和 SHOW PROCESSLIST 监控数据库操作和性能状况。
**定期备份**：确保有定期的数据库备份和恢复策略。
**日志维护**：监控和维护错误日志、慢查询日志来帮助诊断问题。
6.使用工具和插件
**Percona Toolkit**：使用 Percona Toolkit 来帮助诊断和优化 MySQL 性能。
**MySQLTuner**：运行 MySQLTuner 脚本来分析 MySQL 配置并获得具体的调优建议。
以上是 MySQL 5.7 调优的一些基本方向和建议。实际操作中，每一步的调优都需要仔细考虑，并且可能需要根据具体的工作负载和应用场景进行
调整。在进行重大配置更改后，始终推荐进行充分的测试，以确保改动带来预期的效果。
更新: 2025-01-11 14:56:50