MySQL性能优化（七）— 慢查询全解析

摘要

本文聚焦MySQL慢查询优化，从慢查询日志配置、EXPLAIN分析、索引优化、SQL重写到服务器参数调优，系统化解析慢查询的诊断流程与解决方案。结合实际案例，提供可落地的优化方法，帮助开发者快速定位并解决数据库性能瓶颈。

一、慢查询的核心价值与诊断基础

1.1 慢查询日志的核心作用

慢查询日志是MySQL性能优化的”黑匣子”，通过记录执行时间超过long_query_time阈值的SQL语句，为开发者提供精确的性能分析数据。配置示例：

-- 启用慢查询日志（需重启或执行FLUSH LOGS）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 设置阈值为2秒
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

实际场景中，建议将阈值设置为业务可接受的合理值（如电商系统可设为0.5秒），避免记录过多无关日志。

1.2 慢查询的常见特征

• 全表扫描：type=ALL且未使用索引
• 索引失效：函数操作导致索引无法使用（如WHERE DATE(create_time) = '2023-01-01'）
• 复杂连接：多表JOIN未优化连接顺序
• 排序分组：ORDER BY/GROUP BY未使用索引
• 大事务：单次事务处理数据量过大

二、慢查询诊断四步法

2.1 第一步：定位慢查询

使用mysqldumpslow工具分析慢查询日志：

# 按执行时间排序（前10条）
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

输出示例：

Count: 5  Time=3.2s (16s)  Lock=0.00s (0s)  Rows=1000.0 (5000), root[root]@localhost
  SELECT * FROM orders WHERE customer_id = N ORDER BY create_time DESC

此结果明确显示某SQL执行5次累计耗时16秒，需优先优化。

2.2 第二步：EXPLAIN深度分析

对可疑SQL执行EXPLAIN：

EXPLAIN SELECT * FROM orders WHERE customer_id = 100 ORDER BY create_time DESC;

关键字段解读：

• type：访问类型（ALL<index<range<ref<eq_ref<const）
• key：实际使用的索引
• rows：预估扫描行数
• Extra：额外信息（Using filesort/Using temporary）

若出现Using filesort且type=ALL，表明存在严重性能问题。

2.3 第三步：索引优化策略

复合索引设计原则

遵循”最左前缀”原则，示例：

-- 错误设计：无法使用customer_id条件
ALTER TABLE orders ADD INDEX idx_status (status);
-- 正确设计：覆盖常用查询条件
ALTER TABLE orders ADD INDEX idx_customer_create (customer_id, create_time);

索引选择性计算

通过CARDINALITY评估索引价值：

SELECT 
  table_name, 
  index_name, 
  cardinality/COUNT(*) AS selectivity 
FROM information_schema.STATISTICS 
WHERE table_schema = 'your_db'
GROUP BY table_name, index_name
ORDER BY selectivity ASC;

选择性低于5%的索引建议删除。

2.4 第四步：SQL重写技巧

避免SELECT *

-- 优化前
SELECT * FROM products WHERE category_id = 5;
-- 优化后（仅查询必要字段）
SELECT id, name, price FROM products WHERE category_id = 5;

拆分复杂查询

将多表JOIN拆分为多个简单查询，通过应用层合并数据。

限制结果集

-- 优化前（可能返回大量数据）
SELECT * FROM logs WHERE create_time > '2023-01-01';
-- 优化后（分页+排序）
SELECT * FROM logs 
WHERE create_time > '2023-01-01' 
ORDER BY id 
LIMIT 1000 OFFSET 0;

三、进阶优化方案

3.1 服务器参数调优

关键参数配置：

[mysqld]
# 缓冲池大小（建议为物理内存的50-70%）
innodb_buffer_pool_size = 8G
# 排序缓冲区（复杂排序时增大）
sort_buffer_size = 4M
# JOIN缓冲区
join_buffer_size = 2M
# 查询缓存（MySQL 8.0已移除）
# query_cache_size = 0

3.2 分区表应用

对大表按时间/范围分区：

CREATE TABLE sales (
  id INT NOT NULL,
  sale_date DATE NOT NULL,
  amount DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

3.3 读写分离实践

通过ProxySQL实现自动路由：

# ProxySQL配置示例
mysql_variables={
  "mysql-server_version": "5.7.20",
  "mysql-monitor_username": "monitor",
  "mysql-monitor_password": "monitor_pass"
}
mysql_users=(
  {
    username = "app_user",
    password = "app_pass",
    default_hostgroup = 10,
    transaction_persistent = 1
  }
)
mysql_query_rules=(
  {
    rule_id = 1,
    active = 1,
    match_pattern = "^SELECT.*FOR UPDATE",
    destination_hostgroup = 20,
    apply = 1
  },
  {
    rule_id = 2,
    active = 1,
    match_pattern = "^SELECT",
    destination_hostgroup = 10,
    apply = 1
  }
)

四、典型案例解析

案例1：电商订单查询优化

问题SQL：

SELECT * FROM orders 
WHERE customer_id = 1000 
AND status = 'completed' 
ORDER BY create_time DESC 
LIMIT 10;

优化过程：

1. 执行EXPLAIN发现未使用索引
2. 添加复合索引：ALTER TABLE orders ADD INDEX idx_cust_status_time (customer_id, status, create_time)
3. 优化后执行时间从3.2秒降至0.05秒

案例2：报表统计慢查询

问题SQL：

SELECT 
  DATE(create_time) AS day,
  COUNT(*) AS orders,
  SUM(amount) AS total
FROM orders
WHERE create_time BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY DATE(create_time);

优化方案：

1. 添加生成列：ALTER TABLE orders ADD COLUMN order_date DATE GENERATED ALWAYS AS (DATE(create_time)) STORED
2. 创建索引：ALTER TABLE orders ADD INDEX idx_order_date (order_date)
3. 修改SQL为：

   SELECT 
   order_date AS day,
   COUNT(*) AS orders,
   SUM(amount) AS total
   FROM orders
   WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
   GROUP BY order_date;

   执行时间从12秒降至0.8秒。

五、持续优化机制

1. 慢查询监控：通过Prometheus+Grafana构建实时监控面板
2. 定期分析：每周执行慢查询日志分析，更新优化清单
3. 版本升级：关注MySQL新版本的性能改进（如8.0的直方图统计）
4. 压力测试：使用sysbench模拟高并发场景验证优化效果

结语

MySQL慢查询优化是一个系统工程，需要结合日志分析、执行计划解读、索引设计、SQL重写和服务器调优等多维度手段。建议开发者建立标准化的优化流程：定位问题→分析原因→实施优化→验证效果→文档记录。通过持续优化，可使数据库响应时间降低90%以上，显著提升系统整体性能。