如何调优一台 MySQL 服务器：缓冲池（buffer pool）、慢查询日志（slow query log）和连接？

Question

IT Interview Practice · Accepted Answer

服务器调优遵循一个循环：**测量、找到瓶颈、修一处、再测量。**收益最高的三个方面是 InnoDB 缓冲池、慢查询日志和连接处理。

## InnoDB 缓冲池 —— 头号旋钮

缓冲池把数据页和索引页缓存在 RAM 中；当你的工作集能装进它时，读取就是内存速度，磁盘 I/O 几乎消失。在专用数据库服务器上，把它设为 RAM 的约 50–75%。

```sql
SET GLOBAL innodb_buffer_pool_size = 12 * 1024 * 1024 * 1024;  -- 12 GB

-- 健康检查：命中率应在约 99%+
SHOW ENGINE INNODB STATUS\G
--   Buffer pool hit rate 999 / 1000     <- 良好；接近 1000/1000 = 基本都在 RAM 中
```

## 慢查询日志 —— 找出该修什么

```sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;   -- 记录任何超过 500 毫秒的查询
-- 然后用 pt-query-digest / mysqldumpslow 聚合，给最差的查询排名，
-- 并对每一条做 EXPLAIN。大多数收益来自缺失的索引，而非某个服务器设置。
```

## 连接

每个连接都要消耗内存和线程。不要把 `max_connections` 设得高到天上去以掩盖泄漏 —— 成千上万个连接的踩踏会拖垮服务器。在应用中使用**连接池**（或像 ProxySQL 这样的代理）以维持一个小而复用的连接集，并关注 `Threads_connected` 与 `Threads_running`。

```sql
SHOW GLOBAL STATUS LIKE 'Threads_running';   -- 有多少个真正在干活
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
```

## 进攻顺序

先修最靠前的慢查询（建索引） —— 这通常胜过任何配置更改。然后把缓冲池调到合适大小。只有到那时才去调 `max_connections`、`innodb_log_file_size` 和刷盘设置。一次只改一个变量，然后再测量。

## 为什么这很重要

大多数"MySQL 很慢"的事故，都是少数几条未建索引的查询、或缓冲池太小装不下工作集 —— 而不是什么高深的调优。资深工程师会首先查看慢日志和缓冲池命中率，去修真正的瓶颈，而不是盲目地拧配置旋钮。

InnoDB 缓冲池 —— 头号旋钮

慢查询日志 —— 找出该修什么

连接

进攻顺序

为什么这很重要

相关问题