mysql查询慢

MySQL 查询慢完整优化方案

一、先定位：找到慢 SQL

开启慢查询日志

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

分析慢查询日志

# 取出耗时最长的前10条SQL
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

实时查看慢请求

show processlist;
select * from information_schema.processlist where command != 'Sleep';

二、分析为什么慢：使用 EXPLAIN
在慢 SQL 前加 EXPLAIN：

EXPLAIN SELECT * FROM user WHERE name = 'zhangsan';

重点观察字段

type：ALL 全表扫描 = 严重问题；应该const > eq_ref > ref > range > index > ALL
key：为 NULL 表示未命中索引
rows：扫描行数越大越慢
Extra：出现 Using filesort/Using temporary 需立即优化

三、SQL 层面优化（最常见、见效最快）

索引失效场景（高频）

左模糊：like '%xxx%'
字段运算：age + 1 = 20
隐式类型转换：varchar 字段用数字查询
使用 != / not in / is not null 导致索引失效
order by 字段与查询条件不匹配，引发文件排序

2. 禁止无脑 SELECT *
只查询业务需要的字段，减少 IO、内存消耗与网络传输。

3. 深度分页优化（LIMIT 大偏移）（不分页不用考虑）

-- 差
SELECT * FROM user LIMIT 1000000,10;
 
-- 优（延迟关联）
SELECT a.* FROM user a
JOIN (SELECT id FROM user ORDER BY id LIMIT 1000000,10) b
ON a.id = b.id;

4. JOIN 优化

小表驱动大表
关联字段必须建立索引
避免字符串关联，优先使用数字/主键
避免超过 3 张表 JOIN

5. 子查询优化
能 JOIN 则不用子查询，尤其避免 in (select ...) 大结果集。

四、索引设计优化

1. 建索引原则

高频查询字段建索引
区分度高的字段建索引
联合索引遵循最左前缀原则
范围条件放最后
避免冗余索引（重复、功能重叠）

最左前缀原则：联合索引（a,b,c），必须从最左边第一个字段开始用，中间不能跳过，否则索引失效。

例子：索引：idx (name, age, gender)

✅ 能命中索引（符合最左）

where name = ?
where name = ? and age = ?
where name = ? and age = ? and gender = ?

❌ 不能命中 / 部分失效

where age = ? （缺 name，直接失效）
where gender = ? （缺 name、age，直接失效）
where name = ? and gender = ? （跳过 age，只用到 name）

2. 联合索引示例

where a=? and b=? order by c
索引：idx_a_b_c(a,b,c)

3. 索引禁忌

索引不是越多越好，写入性能会急剧下降
低区分度字段（性别、状态）不适合单独建索引
避免在 text/blob 上建普通索引

五、表结构优化

字段尽可能 NOT NULL，使用默认值
优先使用数字类型（int < bigint < varchar）
大字段（text/blob）垂直拆表
避免过多 varchar 大长度
时间字段用 datetime / timestamp

六、MySQL 配置优化

核心参数

innodb_buffer_pool_size = 70% ~ 80% 物理内存
innodb_log_file_size = 1G ~ 4G
innodb_flush_log_at_trx_commit = 1（安全）/2（性能）
sync_binlog = 1（安全）/1000（性能）
max_connections = 1000
join_buffer_size = 2M
sort_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M

七、高并发架构优化

1. 加缓存（Redis）

热点数据缓存
减轻数据库查询压力

2. 读写分离

主库写，从库读
解决读多写少场景瓶颈

3. 分库分表

水平分表：按用户ID/时间范围拆分
垂直分库：按业务模块拆分（用户库、订单库）

4. 异步化削峰

使用 MQ/Kafka 异步写入
避免大量并发直接打数据库

5. 历史数据归档

冷数据迁移至归档库
保持业务表轻量化

八、完整排查流程（实战版）

开启慢查询日志，抓取最慢 SQL
EXPLAIN 分析执行计划
检查是否无索引/索引失效
检查是否全表扫描、文件排序、临时表
优化 SQL 语句与索引结构
优化分页、JOIN、子查询
调整 MySQL 配置
架构升级：缓存、读写分离、分库分表

九、一句话总结

80% 的慢查询来自无索引或索引失效，15% 来自 SQL 写法烂，5% 来自配置与架构。
先优化索引与 SQL，再调配置，最后升级架构。