MySQL 性能调优实战指南

最近在优化公司的几个老项目，发现很多性能问题其实都出在数据库上。今天整理一下这段时间踩过的坑和一些实用的调优经验，希望能帮到同样在和慢查询较劲的朋友。

先说说为什么要调优

你可能也遇到过这种情况：刚上线的时候系统跑得飞快，过了几个月用户量上来了，突然就卡得不行。后台日志一看，全是数据库超时。这时候加机器、扩容固然是一种办法，但如果 SQL 本身写得有问题，加再多资源也是治标不治本。

调优的本质其实就是让数据库少做无用功，该用索引的时候用索引，该走缓存的时候走缓存。

第一步：找出慢查询

工欲善其事必先利其器。你得先知道哪些查询慢，才能有的放矢。

开启慢查询日志

在 my.cnf 里加上这几行：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

long_query_time 设成 2 秒，意思是超过 2 秒的查询都会被记录。根据你的业务场景，这个值可以调整。我一般会设得更严格一点，比如 0.5 秒，这样能抓到更多潜在问题。

重启 MySQL 后，慢查询就会自动记录到日志里。

分析慢查询

日志文件可能会很大，手动翻不太现实。用 mysqldumpslow 这个工具能快速汇总：

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

这条命令会按照查询时间排序，显示最慢的 10 条。输出大概长这样：

Count: 152  Time=5.23s (794s)  Lock=0.00s (0s)  Rows=1250.5 (190076)
  SELECT * FROM orders WHERE user_id = N AND status = 'S'

看到没？这个查询执行了 152 次，平均耗时 5.23 秒，总共花了 794 秒。这种就是重点优化对象。

索引优化：最直接有效的手段

80% 的慢查询都是因为缺索引或者索引用得不对。

查看查询的执行计划

在查询前面加上 EXPLAIN，就能看到 MySQL 到底是怎么执行的：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 1001 AND status = 'pending';

重点关注几个字段：

• type: 连接类型，从好到坏依次是 const > eq_ref > ref > range > index > ALL。如果看到 ALL，说明全表扫描，基本就是性能杀手。
• key: 实际使用的索引。如果是 NULL，说明没用到索引。
• rows: 预计扫描的行数。这个数字越大越慢。
• Extra: 额外信息。如果出现 Using filesort 或 Using temporary，说明查询需要额外的排序或临时表，性能会受影响。

建立合适的索引

假设上面那个查询没用到索引，我们可以这样加：

CREATE INDEX idx_user_status ON orders(user_id, status);

这是一个联合索引（也叫复合索引）。注意字段顺序很重要：把查询频率高、区分度大的字段放前面。

为什么这样？因为 MySQL 的 B+ 树索引是从左到右匹配的。如果你的 WHERE 条件是 user_id = 1001，这个索引能用上；但如果条件只有 status = 'pending'，这个索引就废了。

有个口诀：最左前缀原则。索引可以命中前面几列的任意组合，但不能跳过。

几个常见的坑

1. SELECT * 的代价

-- 不好的写法
SELECT * FROM users WHERE email = 'test@example.com';

-- 好的写法
SELECT id, username, email FROM users WHERE email = 'test@example.com';

SELECT * 会把所有字段都查出来，包括你根本不需要的大字段（比如 BLOB 或 TEXT）。这不仅浪费网络带宽，还可能导致索引失效。

2. 函数会破坏索引

-- 索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';

-- 正确写法
SELECT * FROM orders 
WHERE created_at >= '2024-01-01 00:00:00' 
  AND created_at < '2024-01-02 00:00:00';

只要在索引字段上用了函数，索引就失效了。改成范围查询就能继续走索引。

3. 隐式类型转换

-- phone 是 VARCHAR 类型，但查询用了数字
SELECT * FROM users WHERE phone = 13800138000;

-- 应该用字符串
SELECT * FROM users WHERE phone = '13800138000';

类型不匹配时 MySQL 会做隐式转换，这也会导致索引失效。

配置参数调优

除了 SQL 层面，MySQL 服务器本身的配置也很关键。

InnoDB 缓冲池

这是最重要的参数，没有之一。InnoDB 会把数据和索引都缓存在这里。

[mysqld]
innodb_buffer_pool_size = 8G

一般建议设置为物理内存的 60%-80%。比如你服务器有 16G 内存，可以分配 8-12G 给缓冲池。

查看当前缓冲池使用情况：

SHOW STATUS LIKE 'Innodb_buffer_pool%';

重点看 Innodb_buffer_pool_read_requests 和 Innodb_buffer_pool_reads 这两个值。前者是逻辑读（从缓存读），后者是物理读（从磁盘读）。如果物理读比例过高，说明缓冲池不够大。

连接数

max_connections = 500
max_connect_errors = 1000

连接数不是越大越好。每个连接都会占用内存，太多连接会拖垮服务器。根据业务量和服务器配置合理设置。

查看当前连接数：

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

如果 Max_used_connections 接近 max_connections，说明需要调大这个值。

查询缓存（MySQL 5.7 及以下）

query_cache_type = 1
query_cache_size = 256M

不过要注意，MySQL 8.0 已经移除了查询缓存，因为在高并发写场景下，查询缓存的失效机制反而会拖累性能。如果你用的是 8.0，可以忽略这个参数。

临时表大小

tmp_table_size = 64M
max_heap_table_size = 64M

当查询需要临时表，且临时表大小超过这个限制时，MySQL 会把它写到磁盘，性能会明显下降。适当调大可以避免磁盘 I/O。

表结构设计的一些建议

有时候性能问题根源在设计阶段就埋下了。

选择合适的数据类型

-- 不好的设计
CREATE TABLE users (
    id INT,
    age VARCHAR(10),  -- 年龄用字符串存？
    price DOUBLE,     -- 金额用浮点数会有精度问题
    status VARCHAR(20) -- 状态就几种值，用字符串太浪费
);

-- 好的设计
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    age TINYINT UNSIGNED,
    price DECIMAL(10, 2),
    status ENUM('pending', 'active', 'banned') DEFAULT 'pending'
);

原则很简单：够用就好，不要浪费。INT 够用就别用 BIGINT，能用 TINYINT 就别用 INT。字符串类型更要注意，VARCHAR(255) 和 VARCHAR(50) 的存储开销是不一样的。

避免过度范式化

教科书上讲的三范式在实际项目中有时候反而是束缚。比如订单表需要显示用户名，如果严格按范式，应该关联 users 表查询。但高并发场景下，JOIN 操作很昂贵。

这时候适当冗余数据反而是正确选择：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id INT,
    user_name VARCHAR(50),  -- 冗余字段，避免 JOIN
    total_amount DECIMAL(10, 2),
    created_at TIMESTAMP
);

当然，冗余意味着要维护数据一致性，这需要在代码层面保证。

分区表

当单表数据量超过千万级别，可以考虑分区：

CREATE TABLE logs (
    id BIGINT AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(50),
    created_at DATETIME,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

查询时如果带上分区键（这里是 created_at），MySQL 只会扫描对应的分区，大幅减少数据量。

读写分离和分库分表

当单机撑不住了，就要考虑水平扩展。

读写分离

主库负责写，从库负责读。这个架构能应对大部分读多写少的场景。

# Python 示例：使用不同的连接
from sqlalchemy import create_engine

# 主库（写）
master_engine = create_engine('mysql://user:pass@master-host/db')

# 从库（读）
slave_engine = create_engine('mysql://user:pass@slave-host/db')

def create_order(data):
    with master_engine.connect() as conn:
        conn.execute("INSERT INTO orders (...) VALUES (...)")

def get_orders(user_id):
    with slave_engine.connect() as conn:
        return conn.execute("SELECT * FROM orders WHERE user_id = %s", user_id)

注意主从延迟问题：写入主库后立即从从库读，可能读不到最新数据。对于强一致性要求高的业务（比如支付），还是要从主库读。

分库分表

数据量再大，就要分库分表了。一般按用户 ID 或时间维度拆分：

-- 按用户 ID 哈希分 8 个库
user_id % 8 = 0 -> db_0
user_id % 8 = 1 -> db_1
...

-- 每个库内按时间分表
orders_202401
orders_202402
...

这块比较复杂,涉及到路由规则、跨库事务、聚合查询等问题。生产环境建议用 ShardingSphere 或 MyCat 这样的中间件。

一些监控和诊断工具

Performance Schema

MySQL 自带的性能监控工具，5.7 开始默认开启。可以查看各种维度的性能数据：

-- 查看最慢的 10 条语句
SELECT * FROM sys.statement_analysis 
ORDER BY avg_latency DESC LIMIT 10;

-- 查看锁等待情况
SELECT * FROM sys.innodb_lock_waits;

-- 查看表扫描情况
SELECT * FROM sys.schema_table_statistics_with_buffer 
WHERE table_schema NOT IN ('mysql', 'sys', 'performance_schema');

pt-query-digest

Percona Toolkit 里的工具，比 mysqldumpslow 功能强大得多：

pt-query-digest /var/log/mysql/slow.log > report.txt

生成的报告非常详细，包括查询频率、耗时分布、锁等待等等。

Prometheus + Grafana

生产环境建议用监控系统实时跟踪数据库状态。mysqld_exporter 可以导出 MySQL 的各种指标到 Prometheus，再用 Grafana 做可视化。

关键指标：

• QPS（每秒查询数）
• TPS（每秒事务数）
• 连接数
• 缓冲池命中率
• 慢查询数量
• 主从延迟

总结一下

MySQL 调优是个系统工程，没有银弹。不过有几个原则是通用的：

1. 测量先于优化。别凭感觉，先用工具找出瓶颈在哪。
2. 索引是最低成本的优化手段。花时间研究 EXPLAIN，值得。
3. 避免过早优化。不要一开始就搞分库分表，先把单机潜力压榨干净。
4. 持续监控。性能问题往往是随着数据增长逐渐暴露的，监控能帮你早发现早处理。

调优是个不断迭代的过程。今天的参数明天可能就不合适了，业务在变，数据量在涨，配置也要跟着调整。

最后说一句，再怎么优化也比不上一开始就写好 SQL。养成好习惯，少踩坑，比事后补救省力多了。

---

有问题欢迎留言讨论，大家互相交流学习。