MySQL性能优化指南

文档

MySQL性能优化指南

MySQL是目前最流行的关系型数据库管理系统之一，本文将介绍一些常用的MySQL性能优化技巧。

索引优化

索引是提高数据库查询性能的最有效方法之一。

创建合适的索引

-- 创建单列索引
CREATE INDEX idx_username ON users(username);

-- 创建联合索引
CREATE INDEX idx_name_email ON users(username, email);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

索引使用原则

最左前缀匹配原则：在使用联合索引时，查询条件要从索引的最左列开始。
选择性高的列优先：索引应该建立在选择性高的列上。
避免过多索引：索引会占用空间并影响写入性能。

查询优化

EXPLAIN分析查询

使用EXPLAIN命令分析SQL查询的执行计划：

EXPLAIN SELECT * FROM users WHERE username = 'admin';

避免SELECT *

明确指定需要的列，避免使用SELECT *：

-- 不推荐
SELECT * FROM users;

-- 推荐
SELECT id, username, email FROM users;

利用LIMIT优化分页

-- 基本分页
SELECT * FROM articles ORDER BY created_at DESC LIMIT 10 OFFSET 20;

-- 优化大偏移量
SELECT * FROM articles a JOIN (
  SELECT id FROM articles ORDER BY created_at DESC LIMIT 20, 10
) b ON a.id = b.id;

数据库设计优化

表设计原则

适当的字段类型：选择合适的数据类型，如用TINYINT代替INT存储布尔值。
避免NULL值：NULL值会使索引、索引统计和值比较变得复杂。
表拆分：将大表拆分为多个小表，可以提高查询性能。

示例：优化表结构

-- 优化前
CREATE TABLE articles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255),
  content TEXT,
  author_name VARCHAR(100),
  author_email VARCHAR(100),
  created_at DATETIME,
  updated_at DATETIME,
  is_published TINYINT(1)
);

-- 优化后：拆分作者信息
CREATE TABLE authors (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  email VARCHAR(100)
);

CREATE TABLE articles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255),
  content TEXT,
  author_id INT,
  created_at DATETIME,
  updated_at DATETIME,
  is_published TINYINT(1),
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

服务器配置优化

重要的MySQL配置参数

# InnoDB缓冲池大小（通常设置为可用内存的50%-80%）
innodb_buffer_pool_size = 4G

# 查询缓存大小
query_cache_size = 64M

# 排序缓冲区大小
sort_buffer_size = 2M

# 临时表大小
tmp_table_size = 32M
max_heap_table_size = 32M

# 最大连接数
max_connections = 500

# 连接超时
wait_timeout = 600
interactive_timeout = 600

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

# 错误日志
log_error = /var/log/mysql/error.log

⚠️

修改MySQL配置参数前，请先了解其作用并在测试环境验证效果。不当的配置可能导致性能下降或服务不稳定。