养兔子的猴的博客

MySql慢查询优化

MySQL慢查询优化深度指南

一、慢查询定位与分析体系

1.1 慢查询日志配置

# my.cnf核心配置项
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1       # 建议生产环境设为0.5-2秒
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1

关键参数说明：

  • slow_query_log_file：建议使用绝对路径
  • log_slow_filter：可过滤特定类型查询（如admin_commands）
  • log_slow_verbosity：3（记录完整SQL语句）

1.2 慢查询分析工具链

工具功能特性使用场景
mysqldumpslow官方日志聚合工具快速统计高频慢查询
pt-query-digestPercona高级分析工具生成可视化报告
MySQL Workbench图形化执行计划分析复杂查询调优
explain extended扩展执行计划解析查看优化器决策细节

典型分析流程：

# 1. 按执行时间排序
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 2. 按扫描行数排序
mysqldumpslow -s r -t 5 /var/log/mysql/slow.log

# 3. 生成HTML报告
pt-query-digest --html /var/log/mysql/slow.log > report.html

二、执行计划深度解析

2.1 EXPLAIN关键字段解读

EXPLAIN SELECT * FROM orders 
WHERE user_id=100 AND create_time>'2024-01-01';
字段说明优化方向
id查询执行顺序标识子查询优化
select_type查询类型（SIMPLE/PRIMARY/SUBQUERY等）避免复杂子查询
table涉及的数据表关联查询优化
type访问类型（ALL/INDEX/RANGE等）避免全表扫描
possible_keys可用索引索引缺失检查
key实际使用索引索引选择优化
rows预估扫描行数索引覆盖优化
filtered过滤后数据占比WHERE条件优化
Extra额外信息（Using filesort/Using temporary等）排序/临时表优化

2.2 典型问题诊断

案例1：全表扫描

type=ALL
rows=1000000
Extra=Using where

优化方案：

-- 添加合适索引
CREATE INDEX idx_user_create ON orders(user_id,create_time);

案例2：文件排序

Extra=Using filesort

优化方案：

-- 调整索引顺序
CREATE INDEX idx_status_create ON orders(status,create_time);

三、索引优化策略

3.1 索引设计原则

graph TD
    A[索引选择] --> B[高基数列优先]
    A --> C[最左前缀原则]
    A --> D[避免冗余索引]
    A --> E[覆盖索引优先]
    
    B --> B1(如user_id比gender更适合索引)
    C --> C1(联合索引(a,b,c)需按查询顺序使用)
    D --> D1(删除未使用索引)
    E --> E1(索引包含所有查询字段)

3.2 索引优化技巧

3.2.1 覆盖索引实战

-- 优化前（回表查询）
SELECT * FROM users WHERE email='xxx';

-- 优化后（覆盖索引）
CREATE INDEX idx_email_name ON users(email,name);
SELECT email,name FROM users WHERE email='xxx';

3.2.2 前缀索引应用

-- 长字符串优化
CREATE INDEX idx_username ON users(username(10));

3.2.3 组合索引设计

-- 高频查询模式
SELECT * FROM orders 
WHERE status='paid' AND create_time BETWEEN '2024-01-01' AND '2024-06-01'
ORDER BY create_time DESC;

-- 最佳索引顺序
CREATE INDEX idx_status_create ON orders(status,create_time DESC);

四、SQL语句优化实践

4.1 常见问题场景

场景1：隐式类型转换

-- 问题SQL
SELECT * FROM users WHERE phone=13800138000;

-- 优化方案
SELECT * FROM users WHERE phone='13800138000';

场景2：函数索引失效

-- 问题SQL
SELECT * FROM logs WHERE DATE(create_time)='2024-06-05';

-- 优化方案
SELECT * FROM logs 
WHERE create_time >= '2024-06-05 00:00:00' 
AND create_time < '2024-06-06 00:00:00';

4.2 分页优化方案

传统分页问题：

-- 深分页性能差
SELECT * FROM orders ORDER BY id DESC LIMIT 100000,10;

优化方案：

-- 延迟关联法
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id DESC LIMIT 100000,10) AS tmp
ON o.id=tmp.id;

五、数据库架构优化

5.1 分区分表策略

水平分区示例：

CREATE TABLE orders (
    id INT NOT NULL,
    order_date DATE NOT NULL,
    ...
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN MAXVALUE
);

垂直拆分示例：

-- 订单主表
CREATE TABLE orders_main (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2)
);

-- 订单详情表
CREATE TABLE orders_detail (
    detail_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT
);

5.2 缓存架构设计

多级缓存架构：

用户请求
  │
  ├─ Redis缓存层（热点数据）
  │
  ├─ MySQL查询缓存（已弃用）
  │
  └─ 数据库层（InnoDB Buffer Pool）

六、性能监控与持续优化

6.1 监控指标体系

监控维度关键指标优化阈值
查询性能QPS, 平均响应时间>95% <100ms
索引效率索引使用率, 覆盖索引比例>80%
锁竞争锁等待时间, 死锁次数<10次/分钟
缓存命中率InnoDB Buffer Pool命中率>98%

6.2 持续优化流程

graph LR
A[开启监控] --> B[收集慢查询]
B --> C{分析工具}
C -->|pt-query-digest| D[识别TOP SQL]
C -->|EXPLAIN| E[执行计划分析]
D --> F[优化方案设计]
E --> F
F --> G[实施优化]
G --> H[性能验证]
H --> I[回归测试]
I --> J[文档更新]

七、典型优化案例

案例：电商订单查询优化

原始SQL：

SELECT * FROM orders 
WHERE user_id=12345 
AND status IN ('paid','shipped') 
AND create_time BETWEEN '2024-01-01' AND '2024-06-01'
ORDER BY create_time DESC 
LIMIT 20;

优化过程：

  1. 检查执行计划发现全表扫描（type=ALL）
  2. 创建复合索引：
CREATE INDEX idx_user_status_create ON orders(user_id,status,create_time DESC);
  1. 重写查询使用覆盖索引：
SELECT order_id,user_id,status,create_time 
FROM orders 
WHERE user_id=12345 
AND status IN ('paid','shipped') 
AND create_time BETWEEN '2024-01-01' AND '2024-06-01'
ORDER BY create_time DESC 
LIMIT 20;

优化效果：

  • 执行时间从1200ms降至15ms
  • 扫描行数从1.2M降至20

八、最佳实践总结

  1. 预防优于治疗：上线前使用EXPLAIN验证查询计划
  2. 监控先行：生产环境持续监控慢查询和索引效率
  3. 版本控制：建立SQL优化变更日志
  4. 容量规划：根据业务增长预估算索引空间
  5. 灾备方案：重要查询保留历史执行计划快照

避坑指南：

  • 避免在WHERE条件使用OR，改用IN或JOIN
  • 谨慎使用DISTINCT和UNION
  • 大事务拆分为小批次处理
  • 定期执行OPTIMIZE TABLE整理碎片

site logo

Hi,Friend

© 2026 养兔子的猴的博客