SQL调优

SQL调优

SQL调优是提升数据库性能的核心技术，通过优化查询逻辑、索引设计和资源配置，显著提升响应速度与并发能力。以下是系统性调优方法及典型实操场景：

一、核心调优方法

1. 索引优化

   • 原则：避免全表扫描，优先使用覆盖索引
   • 操作：

     -- 创建组合索引（避免回表）
     CREATE INDEX idx_user_order ON orders(user_id, order_date);
     

   • 验证：通过EXPLAIN分析执行计划，确保type=ref或range

2. 查询重写

   • 避免全字段查询：

     -- 优化前
     SELECT * FROM products;
     -- 优化后
     SELECT id, name FROM products WHERE category='electronics';
     

   • 用JOIN替代子查询：

     -- 优化前（子查询）
     SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
     -- 优化后（JOIN）
     SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id;
     

3. 分页优化

   • 传统分页问题：LIMIT 100000, 10 需扫描前10万行
   • 优化方案：

     SELECT * FROM logs 
     WHERE id > (SELECT id FROM logs ORDER BY id LIMIT 100000, 1)
     ORDER BY id LIMIT 10;
     

**二、典型实操场景与解决方案

场景1：电商订单查询缓慢

• 问题描述：
  用户历史订单查询超时（表数据量5000万行）
• 优化步骤：
  1. 分析执行计划：发现WHERE user_id=? AND status='paid'全表扫描
  2. 创建组合索引：ALTER TABLE orders ADD INDEX idx_user_status(user_id, status)
  3. 重写查询：添加ORDER BY create_time DESC LIMIT 20
• 结果：响应时间从12s降至0.2s

场景2：实时报表聚合卡顿

• 问题描述：
  每日销售统计SQL执行超时（涉及多表JOIN）
• 优化步骤：
  1. 用临时表分解查询：

     CREATE TEMPORARY TABLE daily_sales AS
     SELECT product_id, SUM(amount) AS total 
     FROM order_details 
     WHERE order_date = CURDATE();
     

  2. 添加汇总列：ALTER TABLE products ADD COLUMN daily_sale DECIMAL
  3. 定时任务更新：避免实时计算
• 结果：查询时间从8s降至0.5s

三、高级调优技巧

1. 执行计划分析

   • 关键字段解读：
     • rows：扫描行数（需最小化）
     • Extra：避免Using filesort或Using temporary

2. 参数调优

   • 调整缓冲区：

     # my.cnf 配置
     innodb_buffer_pool_size = 80% of RAM
     query_cache_size = 128M
     

3. 冷热数据分离

   • 历史数据归档：

     CREATE TABLE orders_2023_archive AS 
     SELECT * FROM orders WHERE create_date < '2024-01-01';
     

最佳实践：定期使用SHOW PROCESSLIST监控慢查询，结合pt-query-digest工具分析日志。调优本质是平衡资源消耗、数据一致性、响应速度的三角关系。