前言

很多个人站长的服务器配置并不高，1 核 1G 甚至更低。网站流量稍微上来一点，MySQL 就成了整个站点的瓶颈：数据库 CPU 飙高、页面打开超时、偶尔还会出现 Too many connections 的报错。这些问题几乎每一个站长都遇到过。本文结合实战经验，从慢查询日志、索引优化、InnoDB 参数调优、安全加固四个方面，系统讲解如何让 MySQL 在低配服务器上跑得更稳更快，全文干货，建议先收藏再慢慢看。

一、先开启慢查询日志，定位问题 SQL

优化数据库的第一步不是去改参数，而是先搞清楚到底是哪些 SQL 在拖后腿。MySQL 自带的慢查询日志就是最好的诊断工具，默认是关闭的，需要手动开启。编辑 MySQL 配置文件（Debian/Ubuntu 是 /etc/mysql/mysql.conf.d/mysqld.cnf，CentOS 是 /etc/my.cnf），在 [mysqld] 段下加入：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

各参数含义：slow_query_log 是开关；slow_query_log_file 指定日志文件路径；long_query_time 表示执行时间超过多少秒的 SQL 会被记录，个人站建议设 1 秒，不要用默认的 10 秒，否则很多慢查询会被漏掉；log_queries_not_using_indexes 会把没有走索引的查询也记下来，对发现索引缺失非常有用。

改完配置文件后重启 MySQL 生效。跑一段时间之后，用 mysqldumpslow 工具对日志做聚合分析，找出最耗时的 SQL：

mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

-s c 表示按次数排序，-t 10 表示只显示前 10 条。如果你的服务器装了 Percona Toolkit，还可以用 pt-query-digest 做更详细的报表，它会统计每条 SQL 的平均耗时、总耗时、扫描行数等指标，一眼就能看出问题所在。

二、用 EXPLAIN 看懂执行计划

定位到慢 SQL 之后，下一步就是用 EXPLAIN 查看它的执行计划，看看 MySQL 到底是怎么执行这条语句的：

EXPLAIN SELECT * FROM posts WHERE author_id = 5 ORDER BY created DESC;

返回结果里重点看三个字段：type 表示访问类型，如果出现 ALL 说明是全表扫描，这是最需要警惕的；key 表示实际用到的索引，如果为 NULL 说明没有用索引；rows 是预估扫描的行数，行数越大说明越慢。常见的导致索引失效的情况有几种：一是 SELECT * 取出了所有列，导致无法使用覆盖索引；二是字段类型不匹配发生隐式转换，比如索引字段是字符串，查询时却传了数字；三是 LIKE 查询用前导通配符，比如 LIKE '%keyword'，这种写法索引完全失效，应该改成 LIKE 'keyword%'。

三、索引优化实战

索引是 MySQL 性能优化的核心，但也最容易犯两个极端：要么一个索引都不建，要么建了一堆用不上的索引。正确的做法是按需创建。首先要区分三种常用索引：普通索引（INDEX）加速查询但没有唯一性约束；唯一索引（UNIQUE INDEX）既加速查询又保证字段值不重复，适合用户名、邮箱这类字段；联合索引（复合索引）是多个字段组合的索引，必须遵循最左前缀原则，比如建立了 (category_id, created) 联合索引，那么查询条件里只有 category_id 能用到它，单独查 created 是用不到的。

其次要理解覆盖索引的概念：如果查询的列都能在索引里找到，MySQL 就不用回表读取数据行，速度会快很多。比如列表页只需要 id 和 title 两个字段，建立 (category_id, id, title) 的联合索引，查询时就能做到索引覆盖。最后提醒一句，索引不是越多越好，每多一个索引，写入和更新时都要多维护一份，磁盘占用也会增加，个人站建议把索引数量控制在 5 个以内，并且定期用下面的语句检查并删除冗余索引：

SHOW INDEX FROM posts;

四、InnoDB 核心参数调优

如果你的 SQL 和索引都没问题，但数据库整体还是慢，那就要考虑内存参数了。对 InnoDB 存储引擎来说，最重要的参数是 innodb_buffer_pool_size，它是 InnoDB 的缓存池，用来缓存表数据和索引，直接决定了热点数据能否在内存里命中。建议设置为物理内存的 50% 到 70%。1G 内存的机器可以给 512M，2G 内存可以给 1G 左右：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SET GLOBAL innodb_buffer_pool_size = 536870912;  # 512M，注意这只是临时生效

注意 SET GLOBAL 只在当前运行期生效，重启后失效，必须同步写进配置文件才能持久化。第二个值得调的参数是 innodb_flush_log_at_trx_commit，它控制事务日志刷盘的时机：设为 1 最安全，每次事务提交都刷盘，但性能最差；设为 2 表示每秒刷一次盘，性能好很多，极端情况下可能丢失最后一秒的数据。个人博客类站点对数据丢失容忍度较高，建议设为 2，性能提升非常明显。

第三个常见问题是 Too many connections。默认的 max_connections 只有 151，一旦连接数打满就会报错。可以适当调大到 300，同时把 wait_timeout 和 interactive_timeout 从默认的 28800 秒调小到 300 秒，让空闲连接尽快释放，双管齐下：

SHOW VARIABLES LIKE 'max_connections';
SHOW PROCESSLIST;  # 查看当前连接都在干什么

如果发现某个连接长时间处于 Sleep 状态，说明程序里连接没有及时释放，需要去检查代码里的数据库连接管理。

五、日常维护：碎片整理与统计信息更新

数据库用久了，频繁的增删改会产生大量碎片，表现是数据文件变大、查询变慢。可以用 OPTIMIZE TABLE 整理表碎片：

OPTIMIZE TABLE posts;

但要注意，OPTIMIZE TABLE 会锁表，大表操作时会导致网站短暂不可用。个人站可以选在凌晨流量最低的时候执行，或者干脆用 Percona 的 pt-online-schema-change 在线整理。另外，表结构或数据量发生较大变化后，应该执行 ANALYZE TABLE 更新统计信息，让优化器拿到准确的数据分布，避免选错执行计划。

六、分页查询与连接管理优化

除了慢查询和索引，还有两个日常开发中非常常见、但容易被忽视的优化点。第一个是深分页问题。很多网站的后台列表和前台翻页都习惯用 LIMIT 加 OFFSET 分页，当页码很大的时候，比如查询第 10000 条数据开始的 20 条，MySQL 要先扫描并丢弃前面 10000 条记录，页数越深性能越差。解决办法是改用"游标分页"，也就是记住上一页最后一条记录的 ID，下一页直接用主键定位：

-- 普通深分页（第 500 页，很慢）
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 9980;
-- 游标分页（记住 last_id = 上页最后一条的 id，很快）
SELECT * FROM posts WHERE id < 9980 ORDER BY id DESC LIMIT 20;

第二个是连接管理。PHP 的短连接模式下，每个请求都会新建和销毁数据库连接，连接建立本身有开销。如果用了 Nginx + PHP-FPM，建议开启 PHP-FPM 的持久连接，或者干脆在代码里用连接池。另外要避免在循环里反复执行相同的查询，尽量把循环内的查询合并成一条 IN 查询，减少与数据库的交互次数。把这两点做到位，配合前面的索引优化，大多数网站的数据库压力都能下降一大截。

七、MySQL 安全加固

性能之外，安全同样不能忽视。首先是权限最小化：不要任何程序都用 root 连接数据库，应该为网站单独创建账号，只授予它需要的权限：

CREATE USER 'app'@'localhost' IDENTIFIED BY '一个足够强的密码';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app'@'localhost';
FLUSH PRIVILEGES;

其次是禁用 root 远程登录。检查 user 表里 root 的 host 是否为 localhost，如果不是要立刻改掉：

SELECT user, host FROM mysql.user;
DELETE FROM mysql.user WHERE user = 'root' AND host <> 'localhost';

同时删除默认的匿名账号和 test 数据库。最后是备份，这一点再怎么强调都不为过：每天凌晨用 mysqldump 做全量备份，保留最近 7 天的备份文件：

mysqldump -u root -p --all-databases --single-transaction > /backup/mysql_$(date +%F).sql

--single-transaction 参数可以保证备份期间不锁表，在线备份不会影响网站访问。

七、总结

MySQL 优化是一个循序渐进的过程：先开慢查询日志找出问题 SQL，再用 EXPLAIN 分析执行计划，然后针对性地建索引，最后调整内存参数。对个人站长来说，不需要追求极致的调优，把上面这几步做扎实，低配服务器也能轻松支撑日均几千甚至上万 PV 的网站。记住性能和安全要两手抓，数据库密码、账号权限、定期备份这些基础工作，比任何高深的调优技巧都重要。