一、性能优化的核心维度
MySQL 性能优化需从 硬件层、配置层、存储层、查询层 四个维度协同发力，形成闭环优化：
1. 硬件与环境基础
CPU：MySQL 对多核 CPU 支持较好，但单查询主要依赖单线程（除非使用并行查询），需避免 CPU 过载（如频繁上下文切换）。
内存：内存是优化核心，足够的内存可减少磁盘 I/O（通过缓存数据和索引），建议内存容量至少覆盖热数据量。
磁盘 I/O：优先使用 SSD 替代 HDD，降低随机 I/O 延迟；若用 HDD，可通过 RAID 提升吞吐量和可靠性。
网络：确保数据库服务器与应用服务器之间网络带宽充足，避免网络延迟成为瓶颈（尤其分布式场景）。
2. 配置参数优化（核心考点）
MySQL 的配置文件（my.cnf 或 my.ini）中的参数直接影响性能，需根据硬件规格和业务场景（读多 / 写多 / 高并发）调整。以下是 必调核心参数：
（1）内存相关参数（减少磁盘 I/O）
innodb_buffer_pool_size
作用：InnoDB 存储引擎的缓存池，用于缓存数据页、索引页、undo 日志等，是最重要的性能参数。
优化建议：例：32GB 内存服务器可设为 20G（20GB）。
专用数据库服务器：设置为物理内存的 50%-70%（避免占用过多内存导致系统 OOM）。
注意：若数据量远小于内存，设为数据量的 1.2 倍即可（避免浪费）。
innodb_log_buffer_size
作用：InnoDB 重做日志（Redo Log）的内存缓冲区，减少磁盘写入频率。
优化建议：默认 16MB，写密集场景（如高并发插入）可调至 64MB-256MB（避免频繁刷盘）。key_buffer_size
作用：MyISAM 存储引擎的索引缓存（MyISAM 已逐渐被淘汰，但仍需关注）。
优化建议：若使用 MyISAM，设为内存的 10%-20%；纯 InnoDB 场景可设为 64MB-128MB 即可。
（2）I/O 优化参数（提升磁盘效率）
innodb_flush_log_at_trx_commit
作用：控制 Redo Log 的刷盘策略，平衡性能与数据安全性。
取值与场景：
0：每秒刷盘一次，性能最高但风险最高（崩溃可能丢失未刷盘的事务，适合非核心业务）。
2：事务提交时写入 OS 缓存，每秒由 OS 刷盘，崩溃时可能丢失 1 秒数据，性能中等。
1（默认）：事务提交时立即刷盘，最安全但性能最低（适合金融等强一致性场景）。
innodb_flush_method
作用：定义 InnoDB 如何与文件系统交互刷盘，减少 OS 缓存二次拷贝。
优化建议：Windows 系统设为 unbuffered。Linux 系统优先设为 O_DIRECT（直接写入磁盘，绕过 OS 缓存，减少内存占用）。
innodb_file_per_table
作用：开启后每个表单独生成 .ibd 文件，而非共享表空间（ibdata1）。
优化建议：必须开启（1），便于单表管理、收缩空间和提高 I/O 效率。
（3）并发与连接参数（支撑高并发）
max_connections
作用：允许的最大并发连接数，避免连接数不足导致 “Too many connections” 错误。
优化建议：同时设置 max_user_connections 限制单用户连接，防止恶意占用。结合服务器内存调整，每个连接约占用 2MB-10MB 内存，32GB 内存可设为 1000-2000。
wait_timeout 与 interactive_timeout
作用：控制空闲连接的超时时间，释放无效连接资源。
优化建议：默认 8 小时（28800 秒），可缩短至 300 秒（5 分钟） 或 600 秒，减少连接池浪费。
innodb_lock_wait_timeout
作用：事务等待行锁的超时时间，避免长事务阻塞。
优化建议：默认 50 秒，根据业务调整，短事务场景可设为 10-30 秒，快速失败减少阻塞。
（4）查询优化参数（提升执行效率）
query_cache_size 与 query_cache_type
注意：MySQL 8.0 已移除查询缓存！5.7 及以下版本需关注：
作用：缓存查询结果，适合读多写少、查询重复率高的场景。
优化建议：写频繁场景建议禁用（query_cache_type=0，query_cache_size=0），避免缓存失效开销。
join_buffer_size
作用：表连接时的缓存大小，优化多表连接性能。
优化建议：默认 256KB，不宜过大（全局参数，每个连接都会分配），可设为 1MB-4MB，结合业务中连接查询的复杂度调整。
sort_buffer_size
作用：排序操作的内存缓冲区，减少磁盘临时表排序。
优化建议：默认 256KB，可设为 1MB-8MB（根据单查询排序数据量，过大可能导致内存紧张）。
二、存储引擎与表结构优化
1. 存储引擎选择
优先使用 InnoDB：支持事务、行级锁、崩溃恢复，适合大多数业务场景（尤其是写密集和高并发场景）。
避免使用 MyISAM：不支持事务和行锁，崩溃后恢复慢，仅适合只读、小表场景。
2. 表结构设计优化
合理选择数据类型：
用 INT 替代 BIGINT（除非确需存储超过 20 亿的数字），VARCHAR(n) 替代 CHAR(n)（节省空间）。
时间用 DATETIME 或 TIMESTAMP（而非字符串），枚举值用 ENUM 替代 VARCHAR。
添加合适的索引：
二级索引：为查询频繁的字段（如 WHERE、JOIN、ORDER BY 后的字段）建立索引，但避免过度索引（影响写入性能）。
主键索引：每个表必须有主键，推荐自增 INT 或 BIGINT（避免 UUID 等无序值导致索引碎片）。
分表分库：
大表（千万级以上）需拆分：水平分表：按时间、用户 ID 等拆分（如按月份分表 order_202301、order_202302）。
垂直分表：将大表拆分为小表（如将用户表拆分为 user_base（基本信息）和 user_extend（扩展信息））。
三、查询语句优化（提升执行效率）
即使配置最优，低效查询仍会成为瓶颈，需通过 EXPLAIN 分析执行计划 优化：
1. 避免全表扫描
确保查询条件（WHERE）中的字段有索引，避免 SELECT *（只查需要的字段）。
例：低效查询 SELECT * FROM user WHERE name = 'xxx' → 优化：为 name 建索引，改为 SELECT id, name FROM user WHERE name = 'xxx'。
2. 优化连接查询
小表驱动大表：JOIN 时将小表作为驱动表（减少外层循环次数）。
避免 JOIN 过多表：超过 3 张表的连接需评估必要性，可通过分表或业务优化减少连接。
3. 减少排序和临时表
ORDER BY 字段尽量用索引排序（避免 Using filesort）。
避免 GROUP BY 无索引的字段，必要时用 FORCE INDEX 强制使用索引。
四、场景化优化策略
1. 读多写少场景（如电商商品列表）
核心目标：提升查询吞吐量，减少读延迟。
配置优化：调大 innodb_buffer_pool_size（缓存更多热数据和索引）。
开启查询缓存（5.7 及以下，query_cache_type=1），但需确保查询重复率高。
增加从库，实现读写分离（主库写，从库读）。
2. 写密集场景（如日志上报、高频交易）
核心目标：提升写入效率，减少锁冲突。
配置优化：调大 innodb_log_buffer_size 和 innodb_log_file_size（减少 Redo Log 切换频率）。
设 innodb_flush_log_at_trx_commit=2（平衡安全与性能）。
关闭 binlog_sync（非主从场景），或调大 sync_binlog=100（减少 binlog 刷盘次数）。
表设计：用 INSERT ... VALUES (),(),() 批量插入替代单条插入。
3. 高并发场景（如秒杀、直播互动）
核心目标：支撑大量并发连接，减少阻塞。
配置优化：调大 max_connections 和 back_log（连接队列长度）。
启用连接池（如 PgBouncer、Druid），避免频繁创建销毁连接。
减少事务长度：将长事务拆分为短事务，避免行锁持有时间过长。
五、性能评估与监控
优化效果需通过量化指标验证，常用工具和指标：
1. 关键性能指标（KPIs）
吞吐量：每秒处理的查询数（QPS）、每秒事务数（TPS）。
响应时间：平均查询耗时、95%/99% 分位响应时间（更能反映用户体验）。
资源利用率：CPU 使用率（建议 ≤70%）、内存使用率、磁盘 I/O 利用率（避免 I/O 饱和）。
2. 监控工具
MySQL 自带工具：
SHOW STATUS：查看连接数（Threads_connected）、QPS（Queries/ 时间）、锁等待（Innodb_row_lock_waits）等。
SHOW PROCESSLIST：实时查看活跃连接和慢查询。
第三方工具：
pt-query-digest：分析慢查询日志，定位低效 SQL。
Prometheus + Grafana：可视化监控 CPU、内存、I/O 等指标
通过以上策略，可系统性提升 MySQL 性能。核心原则是：基于场景调配置，基于数据建索引，基于执行计划优查询，平衡性能、安全性和资源利用率。