MySQL是最流行的关系型数据库之一，性能优化是每个后端程序员必须掌握的技能。今天就来分享一些MySQL数据库性能优化的实战经验。

1. 索引优化

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

索引类型：

  • 普通索引：最基本的索引
  • 唯一索引：索引列的值必须唯一
  • 主键索引：特殊的唯一索引，一个表只能有一个
  • 联合索引：多个字段组成的索引
  • 全文索引：用于全文搜索

索引优化原则：

  1. 最左前缀原则：联合索引遵循最左前缀，查询条件必须包含索引的最左列
  2. 避免在索引列上使用函数、运算、类型转换
  3. 尽量使用覆盖索引，避免回表查询
  4. 索引不是越多越好，过多索引会影响写入性能
  5. 定期分析慢查询，优化索引
-- 创建联合索引
CREATE INDEX idx_category_status_created ON blog_posts(category_id, status, created_at);

-- 查看索引使用情况
EXPLAIN SELECT * FROM blog_posts WHERE category_id = 2 AND status = 'published' ORDER BY created_at DESC;

2. SQL语句优化

避免SELECT *：只查询需要的字段，减少数据传输和内存使用。

-- 不推荐
SELECT * FROM blog_posts;

-- 推荐
SELECT id, title, excerpt, created_at FROM blog_posts WHERE status = 'published';

避免子查询，改用JOIN：JOIN通常比子查询效率更高。

-- 子查询（效率低）
SELECT * FROM blog_posts WHERE category_id IN (SELECT id FROM blog_categories WHERE status = 1);

-- JOIN（效率高）
SELECT p.* FROM blog_posts p JOIN blog_categories c ON p.category_id = c.id WHERE c.status = 1;

分页优化：深分页性能差，使用游标分页或延迟关联。

-- 普通分页（深分页慢）
SELECT * FROM blog_posts ORDER BY id DESC LIMIT 10000, 10;

-- 延迟关联（快）
SELECT p.* FROM blog_posts p JOIN (SELECT id FROM blog_posts ORDER BY id DESC LIMIT 10000, 10) t ON p.id = t.id;

避免在WHERE中使用OR：OR可能导致索引失效，改用UNION。

3. 表结构优化

选择合适的数据类型：

  • 能用小类型就不用大类型（TINYINT vs INT）
  • 字符串长度合理，不要都用VARCHAR(255)
  • 日期时间用DATETIME/TIMESTAMP，不要用字符串
  • 枚举类型用ENUM或TINYINT

避免NULL： NULL字段会占用额外空间，查询时需要特殊处理，尽量设置默认值。

适当冗余： 适当的字段冗余可以减少JOIN，提升查询性能。

分表分库： 数据量过大时考虑分表分库，如按时间分表、按ID哈希分表。

4. 配置优化

InnoDB缓冲池： innodbbufferpool_size是最重要的参数，建议设置为物理内存的60%-80%。

[mysqld]
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
max_connections = 500
query_cache_type = 0
query_cache_size = 0

连接数： max_connections根据实际需求设置，不要过大。

查询缓存： MySQL 8.0已移除查询缓存，不建议使用。

5. 慢查询分析

开启慢查询日志，定期分析优化。

-- 开启慢查询
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

使用mysqldumpslow或pt-query-digest分析慢查询日志。

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

6. 其他优化技巧

  1. 使用连接池：避免频繁创建销毁数据库连接
  2. 读写分离：主库写，从库读，提升并发能力
  3. 缓存：使用Redis等缓存热点数据，减少数据库压力
  4. 批量操作：批量插入、更新比单条操作效率高
  5. 定期维护：OPTIMIZE TABLE整理碎片，ANALYZE TABLE更新统计信息

总结

MySQL性能优化是一个系统工程，需要从索引、SQL、表结构、配置、架构等多个方面入手。优化没有银弹，需要根据实际情况分析和调整。希望这篇文章能帮助大家提升MySQL性能。