SQL从慢查询到高效查询实战优化案例

2026-03-24 22:03:02发布 2次浏览

详情描述

案例1：分页查询优化

原始慢查询（数据量500万+）

-- 查询第1000页，每页20条
SELECT * FROM orders 
WHERE status = 'completed'
ORDER BY create_time DESC
LIMIT 20000, 20;
-- 执行时间：1.8秒

问题分析

LIMIT 20000, 20 需要先扫描20020条记录
排序字段无合适索引时全表扫描

优化方案

方案1：使用索引覆盖+延迟关联

-- 先通过索引获取主键
SELECT id FROM orders 
WHERE status = 'completed'
ORDER BY create_time DESC
LIMIT 20000, 20;

-- 再通过主键获取完整数据
SELECT * FROM orders 
WHERE id IN (/*上一步查询的结果*/);
-- 执行时间：0.12秒

方案2：基于游标的分页（连续分页优化）

-- 记录上一页最后一条的create_time和id
SELECT * FROM orders 
WHERE status = 'completed' 
  AND (create_time < '2023-10-01 10:00:00' 
       OR (create_time = '2023-10-01 10:00:00' AND id < 1000))
ORDER BY create_time DESC, id DESC
LIMIT 20;
-- 执行时间：0.03秒

案例2：JOIN查询优化

原始慢查询

SELECT u.*, o.order_count, o.total_amount
FROM users u
LEFT JOIN (
    SELECT user_id, 
           COUNT(*) as order_count,
           SUM(amount) as total_amount
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id
WHERE u.create_time > '2023-01-01'
ORDER BY o.total_amount DESC
LIMIT 100;
-- 执行时间：4.2秒（users表100万，orders表2000万）

问题分析

子查询需要对2000万数据进行全表扫描
大表GROUP BY无索引效率低下
结果集排序无索引

优化方案

方案1：分步查询 + 临时表

-- 步骤1：创建临时索引
CREATE INDEX idx_orders_user_time ON orders(user_id, create_time);
CREATE INDEX idx_users_time ON users(create_time);

-- 步骤2：使用延迟JOIN优化
WITH recent_users AS (
    SELECT id FROM users 
    WHERE create_time > '2023-01-01'
    ORDER BY create_time DESC
    LIMIT 5000  -- 先限制范围
),
user_stats AS (
    SELECT o.user_id,
           COUNT(*) as order_count,
           SUM(o.amount) as total_amount
    FROM orders o
    INNER JOIN recent_users ru ON o.user_id = ru.id
    GROUP BY o.user_id
)
SELECT u.*, us.order_count, us.total_amount
FROM users u
INNER JOIN user_stats us ON u.id = us.user_id
ORDER BY us.total_amount DESC
LIMIT 100;
-- 执行时间：0.45秒

方案2：物化视图（MySQL 8.0+）

-- 创建物化视图
CREATE MATERIALIZED VIEW user_order_stats
REFRESH EVERY 1 HOUR
AS
SELECT user_id,
       COUNT(*) as order_count,
       SUM(amount) as total_amount,
       MAX(create_time) as last_order_time
FROM orders
GROUP BY user_id;

-- 查询时直接使用
SELECT u.*, uos.*
FROM users u
LEFT JOIN user_order_stats uos ON u.id = uos.user_id
WHERE u.create_time > '2023-01-01'
ORDER BY uos.total_amount DESC NULLS LAST
LIMIT 100;
-- 执行时间：0.15秒

案例3：复杂WHERE条件优化

原始慢查询

SELECT * FROM products
WHERE (category_id = 10 OR sub_category_id IN (100, 101, 102))
  AND price BETWEEN 100 AND 1000
  AND stock > 0
  AND (name LIKE '%手机%' OR description LIKE '%智能%')
  AND status = 'active'
ORDER BY sales_volume DESC
LIMIT 50;
-- 执行时间：3.5秒（products表300万）

问题分析

OR条件使索引失效
LIKE模糊查询无法使用索引
多个条件组合导致执行计划不佳

优化方案

方案1：使用UNION优化OR条件

SELECT * FROM products
WHERE category_id = 10
  AND price BETWEEN 100 AND 1000
  AND stock > 0
  AND status = 'active'
  AND (name LIKE '%手机%' OR description LIKE '%智能%')

UNION ALL

SELECT * FROM products
WHERE sub_category_id IN (100, 101, 102)
  AND price BETWEEN 100 AND 1000
  AND stock > 0
  AND status = 'active'
  AND (name LIKE '%手机%' OR description LIKE '%智能%')
ORDER BY sales_volume DESC
LIMIT 50;
-- 执行时间：1.2秒

方案2：全文索引优化LIKE查询（MySQL 5.7+）

-- 创建全文索引
ALTER TABLE products 
ADD FULLTEXT INDEX ft_name_desc (name, description);

-- 修改查询
SELECT * FROM products
WHERE (category_id = 10 OR sub_category_id IN (100, 101, 102))
  AND price BETWEEN 100 AND 1000
  AND stock > 0
  AND status = 'active'
  AND MATCH(name, description) AGAINST('+手机 +智能' IN BOOLEAN MODE)
ORDER BY sales_volume DESC
LIMIT 50;
-- 执行时间：0.3秒

案例4：统计报表优化

原始慢查询

-- 统计每日订单数据
SELECT 
    DATE(create_time) as order_date,
    COUNT(*) as order_count,
    COUNT(DISTINCT user_id) as user_count,
    SUM(amount) as total_amount,
    AVG(amount) as avg_amount,
    MAX(amount) as max_amount,
    MIN(amount) as min_amount
FROM orders
WHERE create_time >= '2023-01-01'
  AND create_time < '2024-01-01'
GROUP BY DATE(create_time)
ORDER BY order_date;
-- 执行时间：25秒（订单表1亿条记录）

问题分析

全表扫描1亿条记录
COUNT(DISTINCT) 消耗大量内存
分组聚合计算密集

优化方案

方案1：预聚合表

-- 创建日统计表
CREATE TABLE order_daily_stats (
    stat_date DATE PRIMARY KEY,
    order_count INT DEFAULT 0,
    user_count INT DEFAULT 0,
    total_amount DECIMAL(15,2) DEFAULT 0,
    max_amount DECIMAL(10,2) DEFAULT 0,
    min_amount DECIMAL(10,2) DEFAULT 0
);

-- 使用定时任务更新
INSERT INTO order_daily_stats
SELECT 
    DATE(create_time) as stat_date,
    COUNT(*) as order_count,
    COUNT(DISTINCT user_id) as user_count,
    SUM(amount) as total_amount,
    MAX(amount) as max_amount,
    MIN(amount) as min_amount
FROM orders
WHERE create_time >= CURDATE() - INTERVAL 1 DAY
GROUP BY DATE(create_time)
ON DUPLICATE KEY UPDATE
    order_count = VALUES(order_count),
    user_count = VALUES(user_count),
    total_amount = VALUES(total_amount),
    max_amount = VALUES(max_amount),
    min_amount = VALUES(min_amount);

-- 查询时直接使用预聚合表
SELECT * FROM order_daily_stats
WHERE stat_date >= '2023-01-01'
  AND stat_date < '2024-01-01'
ORDER BY stat_date;
-- 执行时间：0.01秒

方案2：使用窗口函数（分析型查询）

-- 只查询部分日期，避免全量扫描
WITH daily_data AS (
    SELECT 
        DATE(create_time) as order_date,
        user_id,
        amount
    FROM orders
    WHERE create_time >= '2023-12-01'
      AND create_time < '2024-01-01'
)
SELECT 
    order_date,
    COUNT(*) as order_count,
    COUNT(DISTINCT user_id) as user_count,
    SUM(amount) as total_amount,
    AVG(SUM(amount)) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as weekly_avg_amount
FROM daily_data
GROUP BY order_date
ORDER BY order_date;
-- 执行时间：2.1秒

通用优化技巧总结

1. 索引优化策略

-- 创建复合索引（注意顺序）
CREATE INDEX idx_orders_user_time ON orders(user_id, create_time DESC);

-- 覆盖索引
CREATE INDEX idx_covering ON orders(status, create_time, amount, user_id);

-- 函数索引（MySQL 8.0+）
CREATE INDEX idx_date ON orders((DATE(create_time)));

-- 部分索引
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

2. 查询重写技巧

-- 避免使用 SELECT *
SELECT id, name, email FROM users;

-- 使用 EXISTS 代替 IN（大数据集）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id 
    AND o.amount > 1000
);

-- 使用 JOIN 代替子查询
SELECT u.*, o.total_amount
FROM users u
INNER JOIN (
    SELECT user_id, SUM(amount) as total_amount
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id;

3. 执行计划分析

-- 查看执行计划
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE user_id = 1000;

-- 分析索引使用
SHOW INDEX FROM orders;

-- 查看查询性能
SHOW PROFILE FOR QUERY 1;

-- 启用性能监控
SET profiling = 1;
SELECT ...;
SHOW PROFILES;

4. 数据库配置优化

-- 调整排序缓冲区
SET sort_buffer_size = 16M;

-- 调整连接数
SET max_connections = 200;

-- 调整临时表大小
SET tmp_table_size = 64M;
SET max_heap_table_size = 64M;

-- 启用查询缓存（MySQL 5.7）
SET query_cache_type = 1;
SET query_cache_size = 64M;

监控与维护脚本

-- 查找慢查询
SELECT 
    query,
    exec_time,
    rows_examined,
    rows_sent
FROM mysql.slow_log
WHERE exec_time > 2
ORDER BY exec_time DESC
LIMIT 10;

-- 检查索引使用情况
SELECT 
    table_name,
    index_name,
    rows_selected,
    select_latency
FROM sys.schema_index_statistics
WHERE table_schema = 'your_database'
ORDER BY rows_selected DESC;

-- 自动优化建议
SELECT *
FROM sys.innodb_lock_waits;

SELECT *
FROM sys.statements_with_full_table_scans
LIMIT 10;