Linux LAMP 环境下 MySQL 调优实战
一 基线测量与慢查询定位
- 开启并分析慢查询日志：设置 slow_query_log=1、slow_query_log_file、long_query_time=1~2 秒，用 pt-query-digest 或 MySQL Workbench 找出 Top SQL。
- 使用 EXPLAIN 检查是否走索引、是否出现 全表扫描（type: ALL）、临时表与文件排序。
- 建立监控基线：连接数、QPS/TPS、InnoDB 缓冲池命中率、磁盘 IOPS/延迟、错误与连接超时。
- 借助 MySQLTuner、Percona Toolkit 做安全范围内的参数与健康建议扫描。
- 若仍使用 MyISAM，优先评估迁移到 InnoDB 以获得事务、行锁与一致性优势。
二 索引与 SQL 优化
- 只为高频出现在 WHERE、JOIN、ORDER BY、GROUP BY 的列建立索引；优先使用 复合索引 并遵循最左前缀；避免 过度索引（写放大）。
- 避免 **SELECT ***，只取必要列；减少大表 OFFSET 深翻页，改为 游标/seek 方法（记住上一页最后主键）。
- 能用 JOIN 替代低效子查询；对大结果集使用 LIMIT 与合适的分页策略。
- 对大批量写入使用 批量提交/批量插入，减少事务与网络往返。
三 InnoDB 关键配置与存储优化
- 内存与缓存：将 innodb_buffer_pool_size 设为物理内存的约 70%~80%（专用数据库场景）；启用 innodb_file_per_table。
- 日志与持久化：在追求高吞吐与可接受的崩溃恢复时间之间权衡 innodb_flush_log_at_trx_commit（1 最安全、0/2 更高吞吐）；设置合理的 innodb_log_file_size（如 128M~1G 区间，视负载与恢复目标调整）。
- I/O 能力：SSD 环境下建议 innodb_flush_method=O_DIRECT、关闭 innodb_flush_neighbors=0；根据磁盘能力设置 innodb_io_capacity / innodb_io_capacity_max（如 20000/40000 作为起点）。
- 并发与线程：启用 innodb_buffer_pool_instances（如 8/16 等分片）；通常保持 innodb_thread_concurrency=0（由 InnoDB 自行调度）。
- 连接与会话：合理设置 max_connections（避免过大导致上下文切换与内存压力），并配合 thread_cache_size 减少频繁建连开销。
- 查询缓存：在 MySQL 8.0 已移除；在 5.7 及以下写多场景建议关闭 query_cache（设置 query_cache_size=0）以避免锁争用。
四 架构与运维实践
- 缓存层：引入 Redis/Memcached 缓存热点数据与计算结果，降低数据库读压。
- 连接治理：在应用侧使用 连接池（HikariCP、c3p0 等），避免短连接风暴。
- 表与数据：对历史冷数据 归档/清理；对超大表按业务键做 分区表；必要时考虑 分库分表。
- 维护与健康：定期执行 ANALYZE TABLE/OPTIMIZE TABLE（视引擎与碎片情况），并验证 备份与恢复 流程可用。
- 监控告警：使用 PMM、Prometheus+Grafana 持续观测慢查询、连接、缓冲池命中率、复制延迟等关键指标。
五 参数示例与版本差异