前言
对于个人站长和中小团队来说，MySQL 是最常用的数据库之一。当网站流量从几百人增长到几万人时，数据库往往最先成为瓶颈。很多站长遇到网站变慢的第一反应是「加服务器」，但事实上，通过合理的数据库优化，往往能在不增加硬件成本的情况下让性能提升数倍甚至数十倍。
本文从实践经验出发，系统性地讲解 MySQL 性能优化的各个层面，从最基础的慢查询分析，到索引优化、查询改写，再到高并发场景下的架构设计，帮助你在不同的流量阶段采取正确的优化策略。
一、找到性能瓶颈：慢查询日志分析
在动手优化之前，必须先找到问题所在。MySQL 提供了慢查询日志（Slow Query Log），记录执行时间超过阈值的 SQL 语句。
开启慢查询日志
# 临时开启（重启后失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';
# 永久开启（修改 my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1使用 mysqldumpslow 分析日志
# 按平均查询时间排序，查看前10条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 按查询次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log使用 SHOW PROFILE 分析单条查询
SET profiling = 1;
-- 执行你的 SQL 查询
SELECT * FROM posts WHERE views > 10000;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;通过 SHOW PROFILE 可以清楚地看到查询的各个阶段耗时：Sending data、Creating sort index、Sorting result 等，从而定位具体瓶颈。
二、索引优化：最有效的性能提升手段
索引是数据库优化中最具性价比的手段。一个合理的索引可以将千万级数据表的查询时间从数秒降到毫秒级。
索引设计的基本原则
第一，为 WHERE 子句中的字段建立索引。这是最直观的规则——查询条件里用到的字段，优先考虑加索引。
第二，为 ORDER BY 和 GROUP BY 涉及的字段建立索引。很多开发者只关注 WHERE 条件，忽略排序分组带来的性能损耗。如果查询需要同时过滤和排序，可以建立联合索引同时覆盖两个需求。
第三，为 JOIN 连接字段建立索引。以常见的文章表和评论表 JOIN 为例，关联字段 post_id 如果缺少索引，MySQL 需要对评论表进行全表扫描。
联合索引的最左前缀原则
联合索引 (a, b, c) 实际上创建了三个索引：(a)、(a, b)、(a, b, c)。查询条件必须从最左列开始才能用到索引。
正确使用：
WHERE a = 1 -- 用到索引
WHERE a = 1 AND b = 2 -- 用到索引
WHERE a = 1 AND b = 2 AND c = 3 -- 用到索引无法用到索引：
WHERE b = 2 -- 跳过了 a
WHERE c = 3 -- 跳过了 a 和 b常见索引误用
过多的索引不是好事。每张表的索引建议控制在 5 个以内。索引会减慢 INSERT、UPDATE、DELETE 的速度，因为每次数据变更都需要更新所有索引。
尽量避免在区分度很低的字段上建索引，比如性别字段（只有男/女两种值），这类索引的过滤效果很差。
三、查询优化：改写低效 SQL
避免 SELECT 和 SELECT COUNT()
-- 不推荐
SELECT * FROM posts WHERE category_id = 5;
-- 推荐：只取需要的字段
SELECT id, title, created_at FROM posts WHERE category_id = 5;合理使用 LIMIT 分页
传统的 LIMIT 分页在翻到后面几页时会有严重的性能问题：
-- 越到后面越慢：需要扫描并丢弃前面100000条记录
SELECT * FROM posts ORDER BY id LIMIT 100000, 20;优化方案：使用游标分页（Cursor-based Pagination）
-- 记录上一页最后一条的 id
SELECT * FROM posts WHERE id > 100000 ORDER BY id LIMIT 20;用 EXISTS 代替 IN
当子查询的结果集很大时，EXISTS 通常比 IN 更快：
-- 不推荐（子查询整个表）
SELECT * FROM posts WHERE category_id IN (SELECT id FROM categories WHERE status = 1);
-- 推荐（只要找到一条立即返回）
SELECT * FROM posts WHERE EXISTS (SELECT 1 FROM categories WHERE categories.id = posts.category_id AND status = 1);四、表结构优化
选择合适的数据类型
数据类型的优化原则：最小化存储空间。
- 能用 TINYINT（1字节）就别用 INT（4字节）
- 能用 VARCHAR(50) 就别用 TEXT
- IP 地址用 INT UNSIGNED 存储，配合 INET_ATON/INET_NTOA 函数
- 时间用 DATETIME（5字节）而不是 VARCHAR
水平分表
当单表数据量超过 500 万行时，考虑水平分表。最常用的方式是取模分表：
-- 根据用户 ID 的尾数分 10 张表
user_0, user_1, ..., user_9
-- 查询时根据用户 ID 计算表名
$table = 'user_' . ($userId % 10);五、MySQL 配置优化
以下是一些关键参数的优化建议：
[mysqld]
# InnoDB 缓冲池大小（建议设置为可用内存的 70%-80%）
innodb_buffer_pool_size = 4G
# 查询缓存（8.0 已废弃，5.7 以下可用）
query_cache_type = 1
query_cache_size = 256M
# 连接数
max_connections = 500
# 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M
# 日志
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2 # 非严格模式提升写入性能六、高并发架构设计
当单机 MySQL 无法满足需求时，需要考虑分布式架构：
读写分离
主库负责写入，从库负责读取。通过 ProxySQL 或 MySQL Router 实现自动路由。
引入缓存层
在数据库前面加一层 Redis 缓存，热点数据直接走缓存，大幅减少数据库压力。
典型策略：
- 第一次查询从 MySQL 读取，写入 Redis
- 后续查询直接从 Redis 获取
- 数据更新时删除或刷新 Redis 缓存（Cache Aside Pattern）
总结
数据库优化是一个系统工程，没有银弹。正确的做法是从慢查询分析开始，找到瓶颈，然后针对性地选择索引优化、查询改写、结构设计或架构升级中的一种或多种手段。建议每个季度对数据库进行一次全面的慢查询分析，及时发现新出现的性能问题。记住，优化不是一次性的工作，而是随着业务增长持续进行的过程。