MySQL性能优化实践

Tips

本文基于MySQL 5.7/8.0版本，汇总了常见的MySQL性能优化方法，适用于中小型应用系统。

1. 性能优化概述

数据库性能是整个应用系统性能的基础。根据我的经验，MySQL性能优化可以从以下几个方面入手：

查询优化：优化SQL语句结构和执行计划
索引优化：设计合理的索引结构
数据库结构优化：优化表结构设计
配置优化：调整MySQL服务器参数
硬件优化：升级服务器配置和磁盘I/O

本文将按照从高频到低频、从简单到复杂的顺序，介绍这些优化方法。

2. 查询优化

查询优化是最直接且效果显著的优化方式，不需要更改服务器配置，更具可行性。

2.1 使用EXPLAIN分析查询

在优化查询前，首先使用EXPLAIN命令了解查询的执行计划：

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

EXPLAIN输出结果中需要关注的关键指标：

字段名	含义	优化目标
type	访问类型	从最差到最好依次是: ALL < index < range < ref < eq_ref < const
key	使用的索引	应该使用预期的索引
rows	扫描的行数	尽量减少扫描行数
Extra	额外信息	避免出现"Using filesort"和"Using temporary"

2.2 优化SELECT查询

**避免使用SELECT ***：仅查询需要的列，减少数据传输量

-- 不推荐
SELECT * FROM orders WHERE order_date > '2023-01-01';

-- 推荐
SELECT order_id, order_date, customer_id FROM orders WHERE order_date > '2023-01-01';

避免使用函数对索引字段做操作：会导致索引失效

-- 不推荐（无法使用索引）
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 推荐
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

使用LIMIT限制结果集大小：尤其是大数据量查询时

-- 分页查询时使用LIMIT
SELECT id, title FROM articles ORDER BY created_at DESC LIMIT 20 OFFSET 40;

2.3 优化JOIN查询

合理使用连接类型：避免不必要的外连接(LEFT JOIN)

-- 如果只需要匹配的记录，使用INNER JOIN而非LEFT JOIN
SELECT u.username, o.order_id 
FROM users u 
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

确保JOIN条件字段已索引：两表连接的字段都应该建立索引
小表驱动大表：让小的结果集驱动大表的连接

-- 假设tags表比articles表小，以下写法更佳
SELECT a.title, t.name 
FROM tags t 
JOIN articles a ON t.article_id = a.id
WHERE t.type = 'frontend';

2.4 子查询优化

尽量用JOIN代替子查询：大多数情况下JOIN比子查询效率更高

-- 不推荐
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'active');

-- 推荐
SELECT o.* 
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'active';

避免在循环中执行子查询：可能导致大量重复查询

3. 索引优化

索引是提升查询性能的关键，但索引不是越多越好，需要权衡查询和写入性能。

3.1 索引设计原则

为常用查询条件创建索引：WHERE、ORDER BY、GROUP BY、JOIN子句中出现的列
选择区分度高的列建立索引：如ID、手机号，避免如性别这类低区分度的列
考虑联合索引的列顺序：最左前缀原则，将高频查询和高选择性的列放在前面
控制索引数量：通常单表索引不超过5个，过多索引会影响写入性能

3.2 创建高效索引

主键索引：选择自增ID作为主键，避免使用UUID等随机值

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100)
);

单列索引：为高频查询条件创建索引

CREATE INDEX idx_username ON users(username);

联合索引：根据查询模式创建多列索引

-- 适用于同时按订单状态和日期范围查询
CREATE INDEX idx_status_date ON orders(status, order_date);

覆盖索引：包含查询所需的所有字段，避免回表操作

-- 创建一个包含查询所需全部字段的索引
CREATE INDEX idx_title_date ON articles(title, created_at);

-- 使用该索引的查询（索引覆盖了所有字段，无需回表）
SELECT title, created_at FROM articles WHERE title LIKE 'MySQL%';

3.3 索引维护

定期检查和优化索引：

查找未使用的索引：

SELECT * FROM sys.schema_unused_indexes;

查找重复索引：

SELECT * FROM sys.schema_redundant_indexes;

重建索引以减少碎片：

ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name ADD INDEX index_name(column_list);

-- 或使用OPTIMIZE TABLE（会锁表）
OPTIMIZE TABLE table_name;

4. 数据库结构优化

良好的数据库结构设计是性能优化的基础。

4.1 表设计优化

合理使用数据类型：
- 使用最小满足需求的数据类型（如TINYINT vs INT）
- 定长数据优先使用CHAR，变长数据使用VARCHAR
- 使用INT存储IP而非VARCHAR，可使用INET_ATON()和INET_NTOA()转换

-- 存储IP地址的优化方法
INSERT INTO access_logs (ip) VALUES (INET_ATON('192.168.1.1'));
SELECT INET_NTOA(ip) FROM access_logs;

规范化与反规范化平衡：
- 遵循第三范式减少数据冗余
- 适当反规范化提高查询效率（如存储计算结果）
使用适当的存储引擎：
- InnoDB：支持事务、行级锁，适合大多数应用
- MyISAM：读取速度快，适合只读或极少写入的表
- Memory：高速但断电数据丢失，适合临时表

4.2 分区表

当单表数据量超过500万行或大于10GB时，考虑使用分区表：

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION future VALUES LESS THAN MAXVALUE
);

4.3 垂直拆分与水平拆分

垂直拆分：将大表按列拆分为多个表
- 将不常用的大字段（如TEXT类型）拆分到单独的表
- 将经常一起查询的字段放在同一个表
水平拆分：将表按行拆分（通常需要借助中间件如MyCat、ShardingSphere）
- 按ID范围分片
- 按时间区间分片
- 按哈希值分片

5. MySQL配置优化

根据服务器硬件资源和应用特点调整MySQL配置参数。

5.1 内存相关参数

innodb_buffer_pool_size：InnoDB缓冲池大小，通常设置为服务器物理内存的50%-80%

# 对于16GB内存的服务器
innodb_buffer_pool_size = 8G

key_buffer_size：MyISAM表的索引缓存，默认8MB，对于MyISAM应用可适当增大

# 对于MyISAM为主的应用
key_buffer_size = 256M

query_cache_size：查询缓存大小（注意：MySQL 8.0已移除查询缓存功能）

# MySQL 5.7
query_cache_size = 64M
query_cache_type = 1

5.2 连接与线程相关参数

max_connections：最大连接数，默认为151

# 对于繁忙的Web应用
max_connections = 500

innodb_thread_concurrency：InnoDB并发线程数，一般设置为CPU核心数的两倍

# 对于8核CPU
innodb_thread_concurrency = 16

5.3 日志相关参数

slow_query_log：开启慢查询日志
long_query_time：设置慢查询阈值，超过此时间的查询将被记录

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1  # 记录执行时间超过1秒的查询

5.4 I/O相关参数

innodb_flush_log_at_trx_commit：控制日志刷新频率
- 1: 每次事务提交时写入并刷新（最安全，性能最低）
- 0: 每秒写入并刷新（性能最高，安全性最低）
- 2: 每次提交写入，每秒刷新（折中方案）

# 高性能配置，但可能在崩溃时丢失1秒数据
innodb_flush_log_at_trx_commit = 2

innodb_flush_method：控制InnoDB数据和日志写入方式

# Linux系统推荐设置
innodb_flush_method = O_DIRECT

6. 监控与问题排查

持续监控MySQL性能是优化的基础。

6.1 使用性能监控工具

MySQL自带工具：
- Performance Schema
- sys schema
- SHOW PROCESSLIST
- SHOW STATUS

-- 查看当前连接
SHOW PROCESSLIST;

-- 查看全局状态变量
SHOW GLOBAL STATUS LIKE 'Com_%';

-- 使用sys schema找出消耗资源最多的SQL
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

第三方工具：
- MySQL Enterprise Monitor
- Percona Monitoring and Management (PMM)
- Prometheus + Grafana

6.2 常见问题排查

高CPU使用率：
- 查看正在执行的查询：SHOW PROCESSLIST
- 检查是否有全表扫描的查询
高内存使用：
- 检查buffer_pool使用情况
- 查看是否有大查询使用临时表
I/O瓶颈：
- 检查慢查询日志
- 使用iostat监控磁盘I/O
- 考虑使用SSD存储
连接数过多：
- 检查应用是否正确关闭连接
- 考虑使用连接池
- 增加max_connections值

7. 常见场景优化案例

7.1 大数据量分页查询优化

常规分页查询在数据量大时性能较差：

-- 性能较差的分页查询
SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 20;

优化方案：使用主键或索引记录位置，避免深度分页：

-- 假设已有一个查询返回了上一页最后一条记录的id=12345
SELECT * FROM orders WHERE id < 12345 ORDER BY id DESC LIMIT 20;

7.2 统计查询优化

统计类查询通常涉及全表扫描或大量数据计算：

-- 性能较差的统计查询
SELECT COUNT(*) FROM orders WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

优化方案：

创建汇总表存储统计数据
利用缓存存储统计结果
使用近似统计方法（如sampling）

-- 创建汇总表
CREATE TABLE order_daily_stats (
    stat_date DATE PRIMARY KEY,
    order_count INT,
    total_amount DECIMAL(12,2),
    updated_at TIMESTAMP
);

-- 每日更新
INSERT INTO order_daily_stats (stat_date, order_count, total_amount, updated_at)
SELECT DATE(create_time), COUNT(*), SUM(amount), NOW()
FROM orders
WHERE create_time >= CURDATE() - INTERVAL 1 DAY
GROUP BY DATE(create_time)
ON DUPLICATE KEY UPDATE 
    order_count = VALUES(order_count),
    total_amount = VALUES(total_amount),
    updated_at = NOW();

7.3 模糊查询优化

前缀模糊查询（LIKE 'abc%'）可以使用索引，但通配符开头的查询（LIKE '%abc'）无法使用索引：

-- 能使用索引
SELECT * FROM products WHERE name LIKE 'Apple%';

-- 无法使用索引
SELECT * FROM products WHERE name LIKE '%Phone%';

优化方案：

使用全文索引（FULLTEXT INDEX）
考虑使用专门的搜索引擎（如Elasticsearch）
使用倒排索引

-- 创建全文索引
ALTER TABLE products ADD FULLTEXT INDEX ft_name_desc(name, description);

-- 使用全文搜索
SELECT * FROM products 
WHERE MATCH(name, description) AGAINST('phone' IN NATURAL LANGUAGE MODE);

8. 多层次缓存策略

使用多层次缓存策略可以显著提升应用性能：

8.1 MySQL查询缓存

MySQL 5.7及以下版本可以使用查询缓存（MySQL 8.0已移除此功能）：

query_cache_type = 1
query_cache_size = 64M

8.2 应用层缓存

本地缓存：使用应用内存缓存频繁使用的数据
分布式缓存：使用Redis等缓存系统

// 伪代码：使用Redis缓存查询结果
function getProductById(id) {
  // 尝试从缓存获取
  let product = redis.get(`product:${id}`);
  
  if (!product) {
    // 缓存未命中，从数据库查询
    product = db.query("SELECT * FROM products WHERE id = ?", [id]);
    // 存入缓存，设置过期时间
    redis.set(`product:${id}`, product, 'EX', 3600);
  }
  
  return product;
}

8.3 数据库代理缓存

使用数据库代理层如ProxySQL实现查询路由和缓存：

# ProxySQL配置示例
mysql_query_rules:
- rule_id: 1
  active: 1
  match_pattern: "^SELECT .* FROM products .*$"
  cache_ttl: 600  # 缓存10分钟
  apply: 1

总结

MySQL性能优化是一个综合性工作，需要从查询优化、索引设计、表结构优化、服务器配置和缓存策略等多方面入手。性能优化没有万能公式，需要根据具体应用场景和业务需求，找到最适合的优化方案。

最后强调一点，在进行任何优化前，应当:

明确性能目标
确定性能瓶颈
一次只改一个参数并测试效果
保持优化记录以便回溯