MySQL调优详解-开发者社区

MySQL调优详解

本章导读
MySQL调优是数据库性能优化的综合实践，涉及参数配置、架构设计、监控诊断等多个维度。本章将系统讲解MySQL参数优化、主从复制配置、读写分离实现等核心内容。
学习目标：
目标1：掌握MySQL核心参数的配置原则和方法
目标2：能够搭建主从复制环境并优化复制性能
目标3：掌握读写分离的实现方式和常见架构
前置知识：熟悉MySQL基本操作，了解SQL优化和表设计
阅读时长：约 22 分钟

一、知识概述

MySQL调优是数据库性能优化的综合实践，包括参数配置优化、主从复制优化、读写分离配置等。

1.1 MySQL调优维度

┌─────────────────────────────────────────────────────────────┐ │ MySQL调优维度 │ ├─────────────────────────────────────────────────────────────┤ │ │ │ 1. 服务器参数 │ │ - 内存配置 │ │ - 连接配置 │ │ - 缓冲区配置 │ │ │ │ 2. 存储引擎 │ │ - InnoDB配置 │ │ - 日志配置 │ │ │ │ 3. 架构优化 │ │ - 主从复制 │ │ - 读写分离 │ │ - 分库分表 │ │ │ │ 4. 查询优化 │ │ - 索引优化 │ │ - SQL改写 │ │ │ └─────────────────────────────────────────────────────────────┘

二、参数配置优化

2.1 内存配置

# ============================================ # my.cnf 关键参数配置 # ============================================ [mysqld] # 基本设置 port = 3306 datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock # 字符集 character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci # 连接配置 max_connections = 500 # 最大连接数 max_connect_errors = 100 # 最大错误连接数 wait_timeout = 28800 # 连接超时时间 interactive_timeout = 28800 # 缓冲区配置（根据服务器内存调整） # 假设服务器内存为16GB # InnoDB缓冲池（最重要的参数，设为物理内存的60-80%） innodb_buffer_pool_size = 10G # InnoDB日志缓冲 innodb_log_buffer_size = 16M # 排序缓冲 sort_buffer_size = 2M # 连接缓冲 join_buffer_size = 2M # 读缓冲 read_buffer_size = 1M read_rnd_buffer_size = 1M # 查询缓存（MySQL 8.0已移除） # query_cache_type = 0 # query_cache_size = 0 # 表缓存 table_open_cache = 2000 # 线程缓存 thread_cache_size = 100 # 临时表大小 tmp_table_size = 64M max_heap_table_size = 64M

2.2 InnoDB配置

# ============================================ # InnoDB 参数配置 # ============================================ # 缓冲池实例数（每个实例至少1GB） innodb_buffer_pool_instances = 10 # 日志文件大小 innodb_log_file_size = 1G # 日志文件数量 innodb_log_files_in_group = 3 # 刷新日志策略 # 0: 每秒刷新 # 1: 每次提交刷新（最安全） # 2: 每次提交写入OS缓存，每秒刷新 innodb_flush_log_at_trx_commit = 1 # 刷新方法 innodb_flush_method = O_DIRECT # IO线程数 innodb_read_io_threads = 8 innodb_write_io_threads = 8 # 并发线程数 innodb_thread_concurrency = 0 # 0表示不限制 # 脏页刷新比例 innodb_max_dirty_pages_pct = 75 # 锁等待超时 innodb_lock_wait_timeout = 50 # 事务隔离级别 transaction-isolation = READ-COMMITTED

2.3 慢查询配置

# ============================================ # 慢查询配置 # ============================================ slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 # 超过1秒记录 log_queries_not_using_indexes = 1 # 记录未使用索引的查询 min_examined_row_limit = 100 # 检查行数超过100才记录

三、主从复制优化

3.1 主库配置

# ============================================ # 主库配置 # ============================================ [mysqld] server-id = 1 log-bin = mysql-bin binlog_format = ROW # ROW格式推荐 binlog_row_image = MINIMAL # 减少binlog大小 expire_logs_days = 7 # binlog保留天数 max_binlog_size = 1G sync_binlog = 1 # 每次提交同步binlog # GTID配置（推荐） gtid_mode = ON enforce_gtid_consistency = ON # 半同步复制（可选） # plugin-load = "rpl_semi_sync_master=semisync_master.so" # rpl_semi_sync_master_enabled = 1 # rpl_semi_sync_master_timeout = 1000

3.2 从库配置

# ============================================ # 从库配置 # ============================================ [mysqld] server-id = 2 log-bin = mysql-bin relay-log = relay-bin read_only = ON # 只读模式 super_read_only = ON # 超级用户也只读 # 复制配置 replicate-ignore-db = mysql # 忽略的库 replicate-ignore-db = information_schema # 并行复制 slave_parallel_type = LOGICAL_CLOCK slave_parallel_workers = 8 # 半同步复制 # plugin-load = "rpl_semi_sync_slave=semisync_slave.so" # rpl_semi_sync_slave_enabled = 1

3.3 主从搭建

-- ============================================-- 主库操作-- ============================================-- 创建复制用户CREATEUSER'repl'@'%'IDENTIFIEDBY'password';GRANTREPLICATIONSLAVEON*.*TO'repl'@'%';FLUSHPRIVILEGES;-- 查看主库状态SHOWMASTERSTATUS;