MySQL是目前最流行的关系型数据库，绝大多数Web应用都在用MySQL。但很多网站，随着数据量增长和访问量增加，会出现响应慢、并发上不去、甚至数据库宕机的问题。这些问题，很多时候都是因为MySQL没有做好优化导致的。

数据库是Web应用的核心，也是最容易成为瓶颈的地方。应用服务器可以横向扩展（加机器），但数据库因为有状态（数据），扩展起来比较困难。所以，做好MySQL的性能优化，是提升网站性能和并发能力的关键。

我做过很多MySQL性能优化的项目，从个人博客到电商网站，从几万数据到几千万数据，积累了一些经验。今天分享MySQL性能优化的实战方法，从慢查询分析到高并发架构，帮你系统地优化MySQL。

优化思路

MySQL性能优化，应该遵循一个循序渐进的思路：

  1. 先定位问题：找到慢查询、瓶颈在哪里
  2. SQL和索引优化：优化慢SQL，添加合适的索引（成本最低，效果最明显）
  3. 表结构优化：合理设计表结构，选择合适的数据类型
  4. 配置优化：调整MySQL配置参数，充分利用硬件资源
  5. 架构优化：读写分离、分库分表、集群（成本高，适合大规模）
  6. 缓存策略：用Redis等缓存减轻数据库压力

不要一上来就分库分表、换数据库，大多数性能问题，通过SQL和索引优化就能解决。先做低成本的优化，再考虑高成本的架构优化。

慢查询分析

优化的第一步是定位问题，找到哪些SQL慢。

开启慢查询日志：

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time%';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询记录

也可以在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：MySQL自带的慢查询分析工具
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log  # 按查询时间排序，显示前10条
  • pt-query-digest：Percona Toolkit的工具，功能更强大，分析更详细
pt-query-digest /var/log/mysql/slow.log

EXPLAIN分析执行计划：

找到慢SQL后，用EXPLAIN分析执行计划，看看为什么慢：

EXPLAIN SELECT * FROM posts WHERE category_id = 2 ORDER BY created_at DESC LIMIT 10;

关键列：

  • type：访问类型，从好到差：system > const > eq_ref > ref > range > index > ALL。ALL是全表扫描，需要优化
  • key：实际使用的索引，NULL表示没有使用索引
  • rows：扫描的行数，越大越慢
  • Extra：额外信息，如Using filesort（需要额外排序）、Using temporary（需要临时表），这些都需要优化

索引优化

索引是MySQL性能优化最有效的手段。合适的索引能让查询速度提升几个数量级。

索引的类型：

  • 普通索引（INDEX）：最基本的索引
  • 唯一索引（UNIQUE）：索引列的值必须唯一
  • 主键索引（PRIMARY KEY）：特殊的唯一索引，一个表只能有一个
  • 联合索引（INDEX(col1, col2)）：多个列组成的索引，遵循最左前缀原则
  • 全文索引（FULLTEXT）：用于全文搜索

索引优化原则：

  1. 给WHERE、JOIN、ORDER BY、GROUP BY的列加索引
-- 经常按category_id查询，加索引
ALTER TABLE posts ADD INDEX idx_category_id (category_id);

-- 经常按created_at排序，加索引
ALTER TABLE posts ADD INDEX idx_created_at (created_at);
  1. 联合索引遵循最左前缀原则

联合索引idxab_c (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

-- 用不到索引（缺少a）
WHERE b = 2
WHERE b = 2 AND c = 3
  1. 区分度高的列适合加索引

索引的区分度（不同值的数量/总行数）越高，索引效果越好。比如用户ID、邮箱区分度高，适合加索引；性别、状态区分度低，不适合单独加索引。

  1. 不要过度索引

索引不是越多越好。索引会占用磁盘空间，降低INSERT/UPDATE/DELETE的速度（因为要同时更新索引）。只给经常查询的列加索引，不要给不常用的列加。

  1. 避免索引失效

以下情况会导致索引失效：

  • 在索引列上使用函数或运算：WHERE YEAR(created_at) = 2015
  • 隐式类型转换：字符串列用数字查询
  • 使用LIKE以%开头：WHERE title LIKE '%关键词%'
  • 使用OR连接非索引列
  • 使用NOT IN、!=、<>

覆盖索引： 如果索引包含了查询需要的所有列，就不需要回表查询数据，这就是覆盖索引，性能很好。

-- 联合索引包含了查询的所有列
ALTER TABLE posts ADD INDEX idx_cat_time_title (category_id, created_at, title);
SELECT title FROM posts WHERE category_id = 2 ORDER BY created_at DESC;

SQL优化

除了加索引，SQL语句本身的优化也很重要。

常见的SQL优化：

  1. 避免SELECT *

只查询需要的列，不要用SELECT 。SELECT 会查询所有列，增加网络传输和内存消耗，也无法使用覆盖索引。

-- 不好
SELECT * FROM posts WHERE id = 1;
-- 好
SELECT id, title, content, created_at FROM posts WHERE id = 1;
  1. 用LIMIT限制返回行数

不要一次查询大量数据，用LIMIT分页。

-- 第1页，每页10条
SELECT * FROM posts ORDER BY created_at DESC LIMIT 0, 10;
-- 第2页
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10, 10;
  1. 深分页优化

偏移量很大时，LIMIT偏移很慢，因为要扫描前面所有行。可以用子查询或记录上次的ID来优化：

-- 慢：偏移10000
SELECT * FROM posts ORDER BY id DESC LIMIT 10000, 10;
-- 快：记录上次最大ID
SELECT * FROM posts WHERE id < 10000 ORDER BY id DESC LIMIT 10;
  1. 避免在WHERE中使用函数

在索引列上使用函数会导致索引失效：

-- 不好（索引失效）
SELECT * FROM posts WHERE YEAR(created_at) = 2015;
-- 好（能用索引）
SELECT * FROM posts WHERE created_at >= '2015-01-01' AND created_at < '2016-01-01';
  1. 用EXISTS代替IN

子查询中，EXISTS通常比IN性能好，尤其是IN后面是大量数据的时候：

-- 用EXISTS
SELECT * FROM posts p WHERE EXISTS (SELECT 1 FROM comments c WHERE c.post_id = p.id);
  1. JOIN优化
  • 用小表驱动大表
  • JOIN的列要加索引
  • 避免JOIN太多表（一般不超过3-4个）
  • 能用INNER JOIN就不用LEFT JOIN（INNER JOIN可以优化连接顺序）
  1. ORDER BY优化
  • ORDER BY的列加索引，避免Using filesort
  • 尽量用索引列排序
  • 避免对不同的列排序（无法用联合索引）
  1. GROUP BY优化
  • GROUP BY的列加索引
  • 可以用ORDER BY NULL禁止GROUP BY默认排序（如果不需要排序）

表结构优化

合理的表结构设计，是性能的基础。

数据类型选择：

  • 用最小的数据类型：能用TINYINT就不用INT，能用INT就不用BIGINT
  • 整数类型优先：整数比字符串比较快，占用空间小
  • 避免NULL：NULL列会占用额外空间，索引和比较更复杂，用默认值代替NULL
  • 字符串长度合理：VARCHAR长度按需设置，不要都设成255
  • 时间用DATETIME或TIMESTAMP：不要用字符串存时间
  • IP地址可以用INT UNSIGNED存储（用INETATON和INETNTOA转换）

表设计原则：

  • 第一范式：列不可再分
  • 第二范式：非主键列完全依赖主键
  • 第三范式：非主键列不传递依赖主键
  • 适度反范式：为了性能，可以适当冗余字段，减少JOIN
  • 大字段拆分：TEXT、BLOB等大字段，单独拆到一张表，避免影响主表查询性能
  • 冷热数据分离：不常用的历史数据，归档到单独的表或库

字符集：

  • 统一用utf8mb4（支持emoji和所有Unicode字符）
  • 数据库、表、列的字符集要一致，避免隐式转换导致索引失效

配置优化

MySQL的配置参数对性能影响很大，默认配置通常很保守，需要根据硬件资源调整。

重要配置参数（my.cnf）：

[mysqld]
# 基础配置
port = 3306
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
default-storage-engine = InnoDB

# 连接配置
max_connections = 500  # 最大连接数，根据并发调整
wait_timeout = 600
interactive_timeout = 600

# InnoDB配置（最重要）
innodb_buffer_pool_size = 2G  # 缓冲池大小，建议设为物理内存的50-70%
innodb_log_file_size = 256M   # 日志文件大小
innodb_log_buffer_size = 8M    # 日志缓冲区
innodb_flush_log_at_trx_commit = 2  # 1最安全，2性能好（每秒刷一次）
innodb_file_per_table = 1      # 每个表独立表空间
innodb_flush_method = O_DIRECT  # 绕过操作系统缓存
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# 查询缓存（MySQL 8.0已移除）
query_cache_type = 1
query_cache_size = 64M

# 排序和临时表
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M

# 慢查询
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

配置原则：

  • innodbbufferpool_size是最重要的参数，越大越好（但要留内存给操作系统和其他进程）
  • 不要盲目调大参数，根据实际负载和硬件调整
  • 调整后用压测验证效果
  • 监控MySQL状态（SHOW STATUS），根据状态调整配置

架构优化

当单库单表无法满足需求时，需要考虑架构优化。

1. 读写分离 大多数Web应用都是读多写少，读写分离能大幅提升读性能。

  • 主库负责写（INSERT/UPDATE/DELETE）
  • 从库负责读（SELECT）
  • 主从复制同步数据
  • 用MySQL Proxy、Atlas、MyCat等中间件，或在应用层做读写分离

2. 分库分表 当单表数据量很大（千万级以上），查询性能下降，需要分库分表。

  • 垂直分库：按业务拆分，不同的业务用不同的库
  • 垂直分表：把大字段拆到单独的表
  • 水平分库：同一个表的数据，按某个维度（如用户ID）分到不同的库
  • 水平分表：同一个库内，把一个表拆成多个表
  • 分库分表中间件：MyCat、Sharding-JDBC、Atlas等

3. 主从复制和高可用

  • 主从复制：数据同步，读写分离，备份
  • 主主复制：双主互备，提高可用性
  • MHA、Keepalived等：实现故障自动切换，高可用

4. 集群

  • MySQL Cluster：官方的集群方案，基于NDB存储引擎
  • Galera Cluster：多主同步复制集群
  • Percona XtraDB Cluster：基于Galera的MySQL集群

架构优化成本高、复杂度大，只有在SQL和索引优化、配置优化都无法满足需求时才考虑。

缓存策略

缓存是减轻数据库压力最有效的手段。大多数Web应用，80%的查询都可以缓存。

缓存层次：

  1. 应用层缓存：本地内存缓存（如PHP的APCu、opcache）
  2. 分布式缓存：Redis、Memcached（最常用）
  3. 数据库缓存：MySQL的query cache、InnoDB buffer pool
  4. CDN缓存：静态资源缓存（图片、CSS、JS）

缓存策略：

  • Cache Aside：先查缓存，没有再查数据库，然后写入缓存
  • Read Through：缓存层负责读取数据库
  • Write Through：写数据时同时写缓存和数据库
  • Write Behind：写数据时只写缓存，异步写数据库（性能好，但有数据丢失风险）

缓存注意事项：

  • 缓存失效：设置合理的过期时间，数据更新时主动删除缓存
  • 缓存穿透：查询不存在的数据，缓存永远不命中，用布隆过滤器或缓存空值解决
  • 缓存雪崩：大量缓存同时失效，用随机过期时间、多级缓存解决
  • 缓存一致性：缓存和数据库的数据一致性问题，根据业务场景选择合适的策略

监控和压测

优化不是一次性的，需要持续监控和迭代。

监控指标：

  • QPS/TPS：每秒查询/事务数
  • 连接数：当前连接数、最大连接数
  • 慢查询数：慢查询的数量和频率
  • 缓存命中率：查询缓存、InnoDB buffer pool命中率
  • 锁等待：行锁、表锁等待时间
  • 主从延迟：主从复制延迟
  • 磁盘IO、CPU、内存：系统资源使用

监控工具：

  • MySQL自带：SHOW STATUS、SHOW PROCESSLIST、INFORMATION_SCHEMA
  • 开源工具：Nagios、Zabbix、Prometheus + Grafana、Percona Monitoring
  • 商业工具：MySQL Enterprise Monitor、Datadog、New Relic

压测工具：

  • sysbench：MySQL基准测试工具
  • mysqlslap：MySQL自带的压测工具
  • JMeter、ab：Web应用压测

优化后一定要压测验证，确保优化有效，没有引入新问题。

总结

MySQL性能优化是一个系统工程，需要从多个层面入手：

  1. 定位问题：慢查询日志、EXPLAIN分析
  2. SQL和索引优化：最有效、成本最低，优先做
  3. 表结构优化：合理设计，选择合适的数据类型
  4. 配置优化：充分利用硬件资源
  5. 架构优化：读写分离、分库分表（大规模才需要）
  6. 缓存策略：用缓存减轻数据库压力
  7. 监控和压测：持续优化，验证效果

记住：不要过度优化，也不要盲目优化。先定位问题，再有针对性地优化。大多数性能问题，通过SQL和索引优化就能解决。

希望这篇文章能帮你做好MySQL性能优化，让你的网站从慢查询到高并发。