MySQL 数据库性能优化实战：从 10 秒到 100 毫秒的蜕变

8 3 月, 2026 51点热度 0人点赞 0条评论

MySQL 数据库性能优化实战：从 10 秒到 100 毫秒的蜕变

在生产环境中，数据库性能问题往往是系统瓶颈的核心。本文将通过真实案例，带你掌握 MySQL 性能优化的核心技巧，让你的查询速度提升 100 倍。

一、性能问题诊断

优化之前，先学会诊断。以下是定位慢查询的关键方法：

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 秒的查询

-- 永久配置（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

1.2 分析慢查询日志

# 使用 mysqldumpslow 分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 使用 pt-query-digest（Percona Toolkit）
pt-query-digest /var/log/mysql/slow.log > /tmp/slow_analysis.txt

二、索引优化策略

索引是性能优化的第一道防线。正确的索引设计可以让查询速度提升几个数量级。

2.1 理解索引类型

-- B-Tree 索引（最常用）
CREATE INDEX idx_user_email ON users(email);

-- 联合索引（注意最左前缀原则）
CREATE INDEX idx_name_age ON users(name, age);

-- 覆盖索引（避免回表）
SELECT id, name FROM users WHERE name = '张三';  -- 如果 idx_name 存在

-- 全文索引（文本搜索）
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);

2.2 索引优化实战

-- 场景：查询订单表中某个用户的最近订单
-- ❌ 慢查询（全表扫描）
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;

-- ✅ 优化：创建联合索引
CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);

-- 使用 EXPLAIN 分析执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;
-- 关注：type（ref 优于 all）、key（使用的索引）、rows（扫描行数）

2.3 索引使用陷阱

-- ❌ 索引失效的常见场景

-- 1. 对索引列使用函数
SELECT * FROM users WHERE DATE(created_at) = '2026-03-08';  -- 索引失效
SELECT * FROM users WHERE created_at >= '2026-03-08' AND created_at < '2026-03-09';  -- 索引生效

-- 2. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- phone 是 VARCHAR，数字会触发转换
SELECT * FROM users WHERE phone = '13800138000';  -- 正确

-- 3. LIKE 以通配符开头
SELECT * FROM users WHERE name LIKE '%张%';  -- 索引失效
SELECT * FROM users WHERE name LIKE '张%';  -- 索引生效

-- 4. OR 条件中部分列无索引
SELECT * FROM users WHERE email = 'test@example.com' OR phone = '13800138000';  -- 如果 phone 无索引，全表扫描

三、查询语句优化

3.1 SELECT 优化原则

-- ❌ 避免 SELECT *
SELECT * FROM users WHERE id = 1;

-- ✅ 只取需要的列
SELECT id, name, email FROM users WHERE id = 1;

-- ❌ 避免在 WHERE 子句中使用计算
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- ✅ 使用范围查询
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

3.2 JOIN 优化

-- 小表驱动大表原则
-- 假设 users 表 1 万行，orders 表 100 万行

-- ✅ 推荐：小表在左
SELECT * FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE u.status = 1;

-- 确保 JOIN 列有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

3.3 分页优化

-- ❌ 深分页性能差（需要扫描前 100000 行）
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 10;

-- ✅ 方案 1：使用子查询（覆盖索引）
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 10
) tmp ON o.id = tmp.id;

-- ✅ 方案 2：使用游标分页（推荐）
SELECT * FROM orders 
WHERE created_at < '2026-03-01 00:00:00' 
ORDER BY created_at DESC LIMIT 10;

四、表结构优化

4.1 数据类型选择

-- 选择合适的数据类型
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- 不用 INT，预留增长空间
    name VARCHAR(50) NOT NULL,  -- 不用 TEXT，长度固定用 VARCHAR
    email VARCHAR(100) NOT NULL,
    status TINYINT NOT NULL DEFAULT 1,  -- 状态用 TINYINT
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_email (email),
    INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4.2 表分区

-- 按时间范围分区（适合日志、订单表）
ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 查询时自动分区裁剪
SELECT * FROM orders WHERE created_at >= '2026-01-01';  -- 只扫描 p2026 分区

五、配置参数调优

[mysqld]
# 内存相关（根据服务器内存调整）
innodb_buffer_pool_size = 4G  # 物理内存的 50-70%
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M

# 连接数
max_connections = 500
thread_cache_size = 100

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

# InnoDB 相关
innodb_flush_log_at_trx_commit = 2  # 1 最安全，2 性能好，0 最快但不安全
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000

# 日志
slow_query_log = 1
long_query_time = 1

六、实战案例：优化前后对比

优化前

-- 查询用户订单统计
SELECT u.id, u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 1
GROUP BY u.id, u.name
HAVING order_count > 5
ORDER BY total_amount DESC
LIMIT 100;

-- 执行时间：12.5 秒
-- 扫描行数：500 万

优化后

-- 1. 添加索引
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_user_amount ON orders(user_id, amount);

-- 2. 优化查询（使用物化视图思想）
CREATE TABLE user_order_stats (
    user_id BIGINT PRIMARY KEY,
    order_count INT,
    total_amount DECIMAL(10,2),
    updated_at TIMESTAMP
);

-- 定期更新统计表
INSERT INTO user_order_stats 
SELECT u.id, COUNT(o.id), SUM(o.amount), NOW()
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 1
GROUP BY u.id
ON DUPLICATE KEY UPDATE 
    order_count = VALUES(order_count),
    total_amount = VALUES(total_amount),
    updated_at = NOW();

-- 3. 查询统计表
SELECT u.id, u.name, s.order_count, s.total_amount
FROM users u
JOIN user_order_stats s ON u.id = s.user_id
WHERE u.status = 1 AND s.order_count > 5
ORDER BY s.total_amount DESC
LIMIT 100;

-- 执行时间：0.08 秒
-- 扫描行数：1 万
-- 性能提升：156 倍

七、监控与持续优化

-- 实时监控关键指标
SHOW STATUS LIKE 'Threads_connected';  -- 当前连接数
SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free';  -- 缓冲池等待
SHOW STATUS LIKE 'Handler_read_rnd_next';  -- 全表扫描次数

-- 查看当前正在执行的查询
SHOW PROCESSLIST;

-- 查看表大小和行数
SELECT table_name, table_rows, data_length, index_length
FROM information_schema.tables 
WHERE table_schema = 'your_database'
ORDER BY data_length DESC;