问题诊断篇

慢查询识别工具与指标解读

慢查询优化的第一步是建立"工具识别-指标量化"的诊断体系。通过配置MySQL慢查询日志（slow_query_log=1、long_query_time=1）捕获超时SQL，使用EXPLAIN工具分析执行计划，重点关注三个核心指标：

• type：连接类型，从低效到高效依次为ALL（全表扫描）→index→range→ref→eq_ref→const，ALL需优先优化1
• rows：预估扫描行数，数值越小越好
• Extra：Using filesort/Using temporary表示低效操作，Using index为理想状态1

诊断实例：某商品列表查询执行计划显示type=ALL、rows=500000、Extra=Using filesort，执行时间达3秒。通过添加复合索引(category_id, create_time)优化后，type提升为range，耗时降至200ms。

常见慢查询特征分析

慢查询通常具有以下可识别特征：

1. SELECT *：返回不必要字段，增加网络传输和解析成本。电商订单查询场景中，仅需3个字段却返回全部20+字段，导致传输量增加600%1
2. WHERE条件后置：JOIN前未过滤数据，导致中间结果集膨胀。某案例中，1000万行订单表未过滤直接JOIN，I/O资源浪费90%1
3. 缺少索引：连接字段无索引触发全表扫描。用户表user_id未建索引导致JOIN操作耗时10秒+1

真实案例：电商订单查询诊断

业务场景：查询25岁以上用户购买>80元的已支付订单，涉及1000万行orders表、500万行users表和10万行products表。

原始SQL：

sql

SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'PAID' AND u.age > 25 AND p.price > 80;

诊断结果：EXPLAIN显示type=ALL、rows=1000万、key=NULL，执行时间12.3秒，确认存在全表扫描、SELECT *和条件后置问题1。

优化策略篇

索引设计：从基础到覆盖索引

复合索引设计

遵循"过滤字段优先，连接字段随后"原则，为订单表设计：

sql

CREATE INDEX idx_orders_status_userid_productid 
ON orders(status, user_id, product_id);

将高区分度的status放在首位，依次排列连接字段，较随机顺序索引提升效率3倍1。

覆盖索引实现

包含查询所需所有字段，避免回表：

sql

CREATE INDEX idx_orders_cover ON orders(
  status, id, user_id, product_id, amount
);

优化后EXPLAIN显示Extra=Using index，查询耗时从5秒降至0.1秒1。

索引失效场景

• 索引字段使用函数：WHERE SUBSTR(name,1,3)='abc'
• 隐式类型转换：WHERE order_no=12345（字符串字段用数字查询）

SQL语句重构对比

优化目标：提前过滤+字段精简，三种方案对比：

实现方式平均耗时适用场景子查询2.5秒单层级过滤CTE2.3秒复杂逻辑分层视图2.4秒跨查询复用

CTE优化方案：

sql

WITH filtered_orders AS (
  SELECT id, user_id, product_id, amount 
  FROM orders WHERE status = 'PAID'
),
filtered_users AS (
  SELECT id, username FROM users WHERE age > 25
),
filtered_products AS (
  SELECT id, name FROM products WHERE price > 80
)
SELECT o.id, u.username, p.name, o.amount
FROM filtered_orders o
JOIN filtered_users u ON o.user_id = u.id
JOIN filtered_products p ON o.product_id = p.id;

JOIN顺序优化

优化原则：小表驱动大表，减少中间结果集。

实现步骤：

1. 计算过滤后数据量：products(2万) < orders(300万) < users(150万)
2. 按数据量排序调整JOIN顺序：

sql

SELECT o.id, u.username, p.name, o.amount
FROM filtered_products p  -- 最小表优先
JOIN filtered_orders o ON p.id = o.product_id
JOIN filtered_users u ON o.user_id = u.id;

优化效果：执行时间从12.3秒降至0.82秒，性能提升15倍1。

架构进阶篇

分库分表实现

当单表超千万行，分表是必要选择：

按状态分表示例：

sql

-- 创建拆分表
CREATE TABLE orders_paid LIKE orders;  -- 已支付订单
CREATE TABLE orders_unpaid LIKE orders;  -- 未支付订单

-- 优化效果对比
-- 优化前：全表扫描1000万行，500ms
-- 优化后：单表扫描300万行，200ms，性能提升60%<foot-link>[[1](https://mp.weixin.qq.com/s/TIUiIZto2BZuBcMuFDtn5w)]</foot-link>

临时表缓存策略

高频重复查询场景适用：

sql

-- 创建临时表缓存过滤结果
CREATE TEMPORARY TABLE temp_orders AS
SELECT id, user_id, product_id, amount
FROM orders WHERE status = 'PAID';

-- 创建索引加速查询
CREATE INDEX idx_temp_orders ON temp_orders(user_id);

性能对比：

• CPU占用率：80%→25%
• 单次查询耗时：2秒→0.3秒1

读写分离与物化视图

年度订单报表优化：

sql

-- 创建每日刷新的物化视图
CREATE MATERIALIZED VIEW mv_annual_orders
AS
SELECT user_id, COUNT(*) as total, SUM(amount) as sum
FROM orders 
WHERE create_time >= '2025-01-01'
GROUP BY user_id
REFRESH DAILY;

优化效果：报表查询从20分钟→3秒，从库CPU占用下降60%。

监控体系篇

关键指标监控

建立慢查询监控仪表盘，关注三类核心指标：

指标名称推荐阈值数据来源慢查询次数<10次/分钟慢查询日志平均执行耗时<500msPrometheusCPU占用率<70%服务器监控

预警机制配置

yaml

groups:
- name: slow_query_rules
  rules:
  - alert: SlowQuery
    expr: mysql_slow_query_time_seconds > 2
    for: 5m
    labels:
      severity: critical
    annotations:
      summary: "慢查询告警"
      description: "SQL执行时间{{ $value }}秒，超过阈值2秒"

Java开发者防坑指南：10条实战口诀

1. *避免SELECT ，按需取字段减少传输量，为索引优化创造条件1
2. 过滤条件前置，缩小数据集提前过滤使JOIN数据量减少90%1
3. 复合索引先过滤，连接字段随后排索引顺序：过滤字段→连接字段1
4. 覆盖索引全包含，无需回表效率高Using index标识，查询性能提升5倍1
5. 小表优先JOIN，中间结果最小化数据量排序：小表→中表→大表1
6. 高频查询临时表，重复计算靠边站会话级缓存，CPU占用下降68%1
7. 千万大表要分拆，单表数据降量级分表后查询耗时减少60%1
8. EXPLAIN常相伴，type/ref/range是标杆执行计划分析是优化基础1
9. 视图虽好莫滥用，每次查询需重算复杂视图改用物化视图1
10. 监控告警不可少，历史趋势助优化建立全链路SQL性能监控1

感谢关注【AI码力】，获取更多SQL秘籍！