MySQL性能优化
在并发查询场景下，大数据表进行并发连表查询时，默认的innodb_buffer_pool大小发生瓶颈，从而导致系统查询数据缓慢，合理调整数据库的缓冲池和示例来优化接口查询速度
查看MySQL缓冲池大小：
-- 查看缓冲池相关参数
SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_read%';
SHOW variables LIKE 'innodb_buffer_pool%';
-- 查看当前值（字节单位）
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- 转换为易读格式（GB/MB）
SELECT
VARIABLE_NAME,
VARIABLE_VALUE AS bytes,
CONCAT(ROUND(VARIABLE_VALUE / 1024 / 1024 / 1024, 2), ' GB') AS GB,
CONCAT(ROUND(VARIABLE_VALUE / 1024 / 1024, 2), ' MB') AS MB
FROM performance_schema.global_variables
WHERE VARIABLE_NAME = 'innodb_buffer_pool_size';
-- 查询MySql8.0命中率（反映缓存效率,>99.9%最佳）
SELECT
( 1 - ( Variable_value / ( SELECT Variable_value FROM information_schema.GLOBAL_STATUS WHERE Variable_name = 'Innodb_buffer_pool_read_requests' ) ) ) * 100 AS buffer_pool_hit_rate
FROM
information_schema.GLOBAL_STATUS
WHERE
Variable_name = 'Innodb_buffer_pool_reads';
-- 查询MySql5.7命中率
SELECT
ROUND( ( 1 - ( Variable_value / ( SELECT Variable_value FROM PERFORMANCE_SCHEMA.global_status WHERE Variable_name = 'Innodb_buffer_pool_read_requests' ) ) ) * 100, 2 ) AS buffer_pool_hit_rate
FROM
PERFORMANCE_SCHEMA.global_status
WHERE
Variable_name = 'Innodb_buffer_pool_reads';MySQL优化参数示例：
[mysqld]
# 基础配置
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
lower_case_table_names = 1 # 避免大小写敏感问题
# 内存分配（核心优化）
innodb_buffer_pool_size = 18G # InnoDB缓冲池，缓存数据和索引，分配约56%内存给InnoDB缓冲池，默认值：128M；OLTP（高并发读写）需增大，OLAP（分析型）可适当降低
innodb_buffer_pool_instances = 8 # 缓冲池实例数（建议与80%CPU核数匹配），高并发或缓冲池≥64GB时需增加
key_buffer_size = 16M # MyISAM表索引缓存（备用）默认值：8M；建议范围：若使用MyISAM表可增大，否则保持默认；场景：纯InnoDB场景可设为16M节省内存
max_allowed_packet = 256M # 每个连接的最大允许数据包大小，默认值：64M，场景：大字段（BLOB、长文本）操作时需增大
thread_stack = 256K # 连接线程的堆栈大小，默认为256KB或512KB
table_definition_cache = 2000 # 存储表定义（比如列数据类型）的缓存大小，范围在1-2048
# 连接与线程
max_connections = 200 # 最大并发连接数（按300-500用户预留），默认值：151，建议范围：根据业务并发量调整，需监控Threads_connected，风险：过高会导致内存溢出（每个连接约占用1-4MB内存）
thread_cache_size = 50 # 线程缓存数，减少线程创建开销，默认值：-1（自动调整），建议范围：根据Threads_created状态值调整（建议为活跃连接的10%-20%）
wait_timeout = 600 # 空闲连接的超时时间（秒）
interactive_timeout = 1800 # 交互连接超时时间，默认值：28800秒（8小时），场景：短连接频繁场景（如Web应用）需减小，避免连接堆积
# 查询优化
query_cache_type = OFF # 关闭查询缓存（MySQL 8.0已移除，低版本可选OFF）场景：读多写少场景可启用（低版本），但高并发写入时建议关闭
query_cache_size = 0 # 查询缓存内存大小，0表示禁用
sort_buffer_size = 4M # 排序缓冲区（避免过大），默认值：256K，建议范围：避免超过2M（每个连接独立分配，过高导致内存浪费）场景：频繁ORDER BY或GROUP BY时可适当增大
join_buffer_size = 4M # 连接操作缓冲区
tmp_table_size = 64M # 内存临时表阈值，默认值：16M，场景：复杂查询（如大表JOIN）需增大，避免频繁磁盘临时表
max_heap_table_size = 64M
read_buffer_size = 2M # 顺序读缓冲
read_rnd_buffer_size = 2M # 随机读缓冲
# InnoDB写入优化（实时监控数据场景）
innodb_flush_log_at_trx_commit = 1 # 事务提交策略（1=安全，2=性能优先）（机器宕机可能丢失1秒数据）
sync_binlog = 0 # 二进制日志写入策略（0=依赖OS刷新）
innodb_log_file_size = 2G # Redo日志大小（提升写入吞吐）默认值：48M（5.6）、96M（5.7+），建议范围：1-4小时写入量，建议总日志大小≥缓冲池的25%，场景：高频写入时增大可减少检查点频率
innodb_log_buffer_size = 256M # 日志缓冲区
innodb_flush_method = O_DIRECT # 直接写磁盘，避免双缓冲
innodb_autoinc_lock_mode = 2 # 提升自增列并发插入性能
# 日志与监控
slow_query_log = 1 # 启用慢查询日志
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2 # 超过2秒的查询视为慢查询
log_queries_not_using_indexes = 1 # 记录未使用索引的查询
binlog_format = ROW # 二进制日志格式（数据一致性）
# 文件与表限制
open_files_limit = 65535 # 打开文件数限制 默认值：5000 场景：表数量多（如分库分表）时需增大，需同步调整系统ulimit
table_open_cache = 4096 # 表缓存数，适应多表场景
innodb_file_per_table = ON # 每表独立表空间 默认值：ON（5.6+）场景：必启用，便于管理和空间回收。
# 其他优化
innodb_io_capacity = 2000 # InnoDB后台任务IO吞吐限制 SSD建议值2000 默认值：200（HDD）、2000（SSD）场景：SSD存储时需增大，提升脏页刷新效率
innodb_io_capacity_max = 4000 # SSD突发IO容量
innodb_thread_concurrency = 0 # 自动调整并发线程数
innodb_read_io_threads = 8 # 读线程数 默认值：4 建议范围：与CPU核数匹配（如16核设8-16）。
innodb_write_io_threads = 8 # 写线程数
[mysqld_safe]
log-error = /var/log/mysql/error.log