MySQL数据库优化实战指南：慢查询分析与性能提升10倍的方法-瀚煜云服

文章最后更新时间：2026-04-13 22:02:23

MySQL是互联网应用最广泛的数据库，但随着数据量增长，很多应用会遇到查询变慢、数据库负载高等问题。本文分享MySQL优化的实战经验，从慢查询分析到索引优化，帮你把数据库性能提升10倍。

一、为什么MySQL会变慢？

1. 数据量增长

一张表从1万行增长到1000万行，查询性能可能下降100倍。

2. 缺少索引

没有索引的查询需要全表扫描，数据量越大越慢。

3. SQL写法问题

如 SELECT *、隐式类型转换、不当的JOIN等。

4. 硬件瓶颈

内存不足、磁盘IO慢、CPU过载。

二、慢查询日志：找出拖慢系统的SQL

开启慢查询日志

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

执行时间超过1秒的SQL都会被记录到慢查询日志。

查看慢查询日志

cat /var/log/mysql/slow.log

使用mysqldumpslow分析

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

-t 10：显示最慢的10条SQL

三、EXPLAIN分析查询执行计划

在SQL前加上 EXPLAIN，可以查看查询的执行计划：

EXPLAIN SELECT * FROM orders WHERE user_id = 100;

关键字段说明：

type：查询类型，从好到差：const、eq_ref、ref、range、index、ALL
key：实际使用的索引
rows：扫描行数，越少越好
Extra：额外信息，如 Using filesort、Using temporary 需要优化

优化目标：

type 不要出现 ALL（全表扫描）
rows 尽可能小
Extra 不要出现 Using filesort、Using temporary

四、索引优化：最重要的优化手段

创建索引

ALTER TABLE orders ADD INDEX idx_user_id(user_id);
CREATE INDEX idx_user_id ON orders(user_id);

创建联合索引

ALTER TABLE orders ADD INDEX idx_user_status(user_id, status);

索引设计原则

WHERE 条件字段优先建索引
区分度高的字段优先（区分度=不同值数量/总行数）
联合索引要遵循最左前缀原则
不要在索引列上做函数运算

查看表的所有索引

SHOW INDEX FROM orders;

删除无用索引

ALTER TABLE orders DROP INDEX idx_old;

五、常见SQL优化方法

1. 避免 SELECT *

只查询需要的字段，减少网络传输和内存消耗：

SELECT id, name, email FROM users WHERE id = 1;

2. 使用 LIMIT 分页

分页查询使用延迟关联：

-- 低效
SELECT * FROM orders ORDER BY id DESC LIMIT 1000000, 10;

-- 高效
SELECT * FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id DESC LIMIT 1000000, 10) t
ON o.id = t.id;

3. 避免隐式类型转换

-- 如果 user_id 是 INT 类型
-- 低效（会全表扫描）
SELECT * FROM orders WHERE user_id = '100';

-- 高效
SELECT * FROM orders WHERE user_id = 100;

4. 合理使用 JOIN

-- 小表驱动大表
SELECT * FROM orders o
INNER JOIN (SELECT id FROM users WHERE status = 1) u
ON o.user_id = u.id;

5. 批量操作代替循环

-- 低效：循环插入1000条
for (i=0; i<1000; i++) {
  INSERT INTO logs VALUES (...);
}

-- 高效：批量插入
INSERT INTO logs VALUES (...), (...), ...;

六、数据库配置优化

主要参数调优（my.cnf）：

innodb_buffer_pool_size = 4G
# 缓冲池大小，建议设为可用内存的70%

max_connections = 500
# 最大连接数，根据实际需求设置

query_cache_size = 256M
# 查询缓存，大数据量时可关闭

slow_query_log = 1
# 开启慢查询日志

一般在线上环境，innodb_buffer_pool_size 是最关键的参数，尽量将热点数据放入内存。

七、表结构设计优化

选择合适的数据类型

能用 TINYINT 不用 INT
能用 VARCHAR(50) 不用 VARCHAR(255)
固定长度用 CHAR，可变长度用 VARCHAR
时间字段用 DATETIME 或 TIMESTAMP

读写分离

将读操作和写操作分离到不同服务器：

主库（写）→ 从库1（读）、从库2（读）

分库分表

数据量超过千万级时，考虑水平分表：

按时间分表：orders_2024、orders_2025
按用户ID取模分表：orders_0、orders_1、orders_2

八、常用诊断命令

SHOW PROCESSLIST;           -- 查看当前所有连接
SHOW STATUS LIKE 'slow%';   -- 查看慢查询统计
SHOW TABLE STATUS;           -- 查看表信息
SHOW FULL PROCESSLIST;       -- 查看完整连接信息