MySQL数据库性能优化实战：从慢查询到毫秒级响应

6 3 月, 2026 61点热度 0人点赞 0条评论

引言

在生产环境中，数据库性能问题往往是系统瓶颈的根源。一个未经优化的SQL查询可能从几毫秒拖慢到数秒，直接影响用户体验。本文将分享MySQL性能优化的实战经验，帮助你从慢查询排查到最终实现毫秒级响应。

一、慢查询日志：发现性能问题的第一步

MySQL的慢查询日志是性能优化的起点。启用它可以记录所有执行时间超过阈值的SQL语句。

1.1 启用慢查询日志

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

-- 临时启用慢查询日志（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 记录超过1秒的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 记录未使用索引的查询

1.2 永久配置（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
log_slow_admin_statements = 1

二、EXPLAIN：分析SQL执行计划

EXPLAIN命令是MySQL性能分析的核心工具，它可以显示MySQL如何执行你的SQL语句。

2.1 基本用法

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

2.2 关键字段解读

字段	含义	优化目标
type	访问类型	至少达到range，最好ref或const
key	实际使用的索引	不为NULL
rows	预估扫描行数	越小越好
Extra	额外信息	避免Using filesort、Using temporary

2.3 type字段性能排序（从优到差）

system > const > eq_ref > ref > range > index > ALL

三、索引优化：性能提升的关键

3.1 创建合适的索引

-- 单列索引
CREATE INDEX idx_email ON users(email);

-- 复合索引（注意列顺序）
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 覆盖索引（避免回表）
CREATE INDEX idx_covering ON products(category_id, price, name);

3.2 复合索引的最左前缀原则

-- 索引：(a, b, c)
SELECT * FROM table WHERE a = 1;           -- ✓ 使用索引
SELECT * FROM table WHERE a = 1 AND b = 2; -- ✓ 使用索引
SELECT * FROM table WHERE b = 2;           -- ✗ 不使用索引
SELECT * FROM table WHERE a = 1 AND c = 3; -- △ 只使用a列

3.3 索引失效的常见场景

-- ❌ 对索引列使用函数
SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';
-- ✓ 优化后
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

-- ❌ 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- phone是VARCHAR
-- ✓ 优化后
SELECT * FROM users WHERE phone = '13800138000';

-- ❌ LIKE以%开头
SELECT * FROM users WHERE name LIKE '%张三%';
-- ✓ 优化后（使用全文索引）
SELECT * FROM users WHERE MATCH(name) AGAINST('张三');

四、SQL语句优化实战

4.1 避免SELECT *

-- ❌ 不推荐
SELECT * FROM users;

-- ✓ 推荐（只取需要的列）
SELECT id, name, email FROM users;

4.2 优化分页查询

-- ❌ 深分页性能差（扫描100010行，丢弃100000行）
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 10;

-- ✓ 优化方案1：使用子查询
SELECT * FROM orders 
WHERE id <= (SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 1)
ORDER BY created_at DESC LIMIT 10;

-- ✓ 优化方案2：记录上次查询的最大ID
SELECT * FROM orders 
WHERE created_at < '2024-01-01 00:00:00' 
ORDER BY created_at DESC LIMIT 10;

4.3 优化JOIN查询

-- ❌ 小表驱动大表
SELECT * FROM large_table 
INNER JOIN small_table ON large_table.id = small_table.large_id;

-- ✓ 优化：确保JOIN列有索引，小表驱动大表
-- MySQL优化器通常会自动处理，但可以手动调整
SELECT * FROM small_table 
INNER JOIN large_table ON small_table.large_id = large_table.id;

4.4 使用UNION ALL替代OR

-- ❌ 可能导致索引失效
SELECT * FROM users WHERE status = 1 OR status = 2;

-- ✓ 使用UNION ALL（两个查询都能用索引）
SELECT * FROM users WHERE status = 1
UNION ALL
SELECT * FROM users WHERE status = 2;

五、表结构优化

5.1 选择合适的数据类型

-- ❌ 浪费空间
CREATE TABLE users (
    id BIGINT,              -- 实际只需要INT
    status TINYINT(4),      -- 可以用TINYINT(1)或ENUM
    name VARCHAR(255)       -- 根据实际长度调整
);

-- ✓ 优化后
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT,
    status TINYINT(1),
    name VARCHAR(50)
);

5.2 垂直分表

-- 原始表（字段过多）
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    email VARCHAR(100),
    -- ... 20个常用字段
    bio TEXT,              -- 不常用
    avatar LONGBLOB,       -- 不常用
    settings JSON          -- 不常用
);

-- 垂直分表后
CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    email VARCHAR(100),
    -- ... 常用字段
);

CREATE TABLE users_extra (
    user_id INT,
    bio TEXT,
    avatar LONGBLOB,
    settings JSON
);

六、配置参数优化

6.1 关键配置项（my.cnf）

[mysqld]
# 连接数
max_connections = 500
thread_cache_size = 50

# 缓冲池（物理内存的50-70%）
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 8

# 日志
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M

# 刷新策略
innodb_flush_log_at_trx_commit = 2  # 0=最快但不安全，1=最安全，2=折中
innodb_flush_method = O_DIRECT

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

七、实战案例：从5秒到50毫秒

7.1 问题场景

电商订单查询接口，随着数据量增长，查询时间从50ms增长到5秒。

7.2 排查过程

-- 1. 查看慢查询日志
SELECT * FROM mysql.slow_log;

-- 2. 分析 problematic SQL
EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 
AND status IN ('pending', 'paid') 
ORDER BY created_at DESC 
LIMIT 20;

-- 发现问题：type=ALL，rows=500万，Using filesort

7.3 优化方案

-- 1. 创建复合索引
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 2. 优化SQL（只取必要字段）
SELECT id, order_no, amount, created_at 
FROM orders 
WHERE user_id = 12345 
AND status IN ('pending', 'paid') 
ORDER BY created_at DESC 
LIMIT 20;

-- 3. 结果：type=range，rows=50，查询时间50ms

八、监控与持续优化

8.1 使用Performance Schema

-- 启用Performance Schema
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';

-- 查看最耗时的SQL
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000000 as avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;