mysql如何根据错误日志调优_mysql性能优化建议

mysql错误日志（Error.log）不记录慢查询或锁等待，仅反映启动失败、崩溃、权限拒绝、磁盘满、innodb恢复异常等底层问题；查性能应优先看slow_query_log和general_log，error.log仅在innodb日志序列号异常、内存溢出、表损坏、连接数超限等特定场景下间接关联性能。

怎么看 MySQL 错误日志里真正有用的线索

MySQL 的 error.log 本身不记录慢查询或锁等待，它只报启动失败、崩溃、权限拒绝、磁盘满、InnoDB 恢复异常等底层问题。如果你在查性能，却盯着 error.log 找“为什么查询慢”，基本是方向错了。

真正该看的是：slow_query_log（慢日志）和 general_log（全量日志，慎开），而 error.log 只在以下情况才和性能间接相关：

• 出现 InnoDB: The log sequence number ... is in the future! → 表明崩溃恢复出错，可能伴随数据页损坏或刷盘异常
• 反复出现 Out of memory; check if mysqld or some other process is using all available memory → 内存配置（如 innodb_buffer_pool_size）可能设得过大，或存在内存泄漏
• table 'xxx' is marked as crashed and should be repaired → MyISAM 表损坏，会强制降级为表级锁，拖慢所有访问该表的请求
• 大量 Too many connections 报错 → max_connections 不足，但更可能是应用没正确释放连接，或存在长事务阻塞

慢查询日志（slow_query_log）怎么配才不漏不炸

默认 MySQL 关闭慢日志，且即使开启，long_query_time 默认是 10 秒——这对 Web 应用毫无意义。必须调低，但不能盲目设成 0.1 秒，否则日志体积爆炸、IO 拖垮实例。

推荐做法：

• 先临时打开：执行 SET GLOBAL slow_query_log = ON; 和 SET GLOBAL long_query_time = 1.0;（单位秒，支持小数）
• 配合 log_queries_not_using_indexes = ON，但仅用于诊断期，上线后关掉——否则索引缺失的 count(*) 或全表统计也会被记入
• 日志路径用绝对路径，避免 MySQL 启动时因权限或相对路径解析失败导致日志写不进去：slow_query_log_file = /var/log/mysql/mysql-slow.log
• 定期轮转日志：用 mysqldumpslow 分析，或直接 pt-query-digest（Percona Toolkit）解析，别手动 cat 查

从慢日志里快速定位三类高危 SQL

不用全文扫日志，盯住三行关键字段：# Query_time、# Lock_time、# Rows_examined。它们比 SQL 本身更能暴露问题。

• Query_time 高 + Lock_time 高 → 很可能是行锁/表锁争用，检查是否缺少索引导致扫描行数过多，或事务里混了 UPdate + select for UPDATE
• Query_time 一般但 Rows_examined 远大于 Rows_sent（比如 examine 10 万行，只返回 1 行）→ 典型索引失效，常见于 WHERE 条件用了函数（DATE(created_at)）、隐式类型转换（user_id = '123' 而字段是 int）
• 同一条 SQL 多次出现，且 Query_time 波动极大（0.02s ~ 2.5s）→ 很可能受 Buffer Pool 缓存命中率影响，或是执行计划被统计信息误导，可尝试 ANALYZE TABLE 更新统计信息

innodb_buffer_pool_size 设多大才不翻车

这是 MySQL 最关键的性能参数，但它不是越大越好。设太高会导致系统内存不足，触发 OOM Killer 杀 MySQL 进程；设太低则 Buffer Pool 命中率低，频繁读盘。

实操建议：

• 专用数据库服务器：设为物理内存的 70%~80%，但必须预留至少 2GB 给 OS 和其他进程（如备份脚本、监控 agent）
• 和 redis/nginx 共机：砍到 50% 左右，并确认 vm.swappiness = 1（减少交换）
• 线上验证是否合适：查 SHOW ENGINE INNODB STATUSG 中的 Buffer pool hit rate，持续低于 990/1000 就说明不够用
• 注意：该值动态调整需重启（MySQL 5.7+ 支持在线调整，但仅限增加，且要求总大小是 chunk size 的整数倍，chunk size 默认 1MB）

最常被忽略的一点：Buffer Pool 大小影响预读行为和脏页刷新节奏，如果 innodb_io_capacity 没同步调高（比如 SSD 上设成 2000），反而会因刷新跟不上导致 Dirty pages 积压、Free Buffers 耗尽、后续写入卡顿。