MySQL查询优化深度指南

MySQL查询优化深度指南

查询优化是MySQL性能调优的核心环节，合理的查询可以提升数倍甚至数十倍的性能。以下是全面的MySQL查询优化策略：

一、基础优化原则

1. 只查询需要的列

   -- 避免
   SELECT * FROM users;
   
   -- 推荐
   SELECT id, name, email FROM users;
   

2. 使用LIMIT限制结果集

   SELECT * FROM products LIMIT 100;
   

3. 避免全表扫描

   • 确保查询使用了适当的索引
   • 对大数据表使用分页查询

二、索引优化策略

1. 合理创建索引

   -- 单列索引
   CREATE INDEX idx_user_name ON users(name);
   
   -- 复合索引
   CREATE INDEX idx_user_composite ON users(last_name, first_name);
   

2. 索引使用原则

   • 最左前缀原则：INDEX(a,b,c) 能用于 WHERE a=?、WHERE a=? AND b=? 但不能用于 WHERE b=?
   • 避免对索引列使用函数：WHERE YEAR(create_time) = 2023 无法使用索引

3. 索引失效场景

   • 使用 !=、<>、NOT IN
   • 使用前导通配符 LIKE '%abc'
   • 隐式类型转换（如字符串列用数字查询）

三、EXPLAIN深度解析

1. 关键字段解读

   • type：从优到差 system > const > eq_ref > ref > range > index > ALL
   • key：实际使用的索引
   • rows：预估需要检查的行数
   • Extra：重要信息如 “Using filesort”, “Using temporary”

2. 优化案例

   EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';
   

四、JOIN优化技巧

1. JOIN执行原理

   • Simple Nested-Loop Join（简单嵌套循环）
   • Block Nested-Loop Join（块嵌套循环）
   • Batched Key Access Join（批量键访问）

2. 优化建议

   • 确保JOIN字段有索引
   • 小表驱动大表（小表放在JOIN左侧）
   • 避免多表JOIN（超过3个表考虑重构）

   -- 优化前
   SELECT * FROM large_table l JOIN small_table s ON l.id = s.large_id;
   
   -- 优化后（小表驱动）
   SELECT * FROM small_table s JOIN large_table l ON s.large_id = l.id;
   

五、子查询优化

1. IN vs EXISTS vs JOIN

   -- 使用JOIN通常更高效
   SELECT * FROM users u 
   WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
   
   -- 可改写为
   SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id;
   

2. 派生表优化

   -- 优化前
   SELECT * FROM (SELECT * FROM logs WHERE create_time > '2023-01-01') t 
   WHERE t.user_id = 100;
   
   -- 优化后
   SELECT * FROM logs 
   WHERE create_time > '2023-01-01' AND user_id = 100;
   

六、排序和分组优化

1. ORDER BY优化

   • 为排序字段建立索引
   • 避免 ORDER BY RAND()
   • 使用延迟关联优化分页

   -- 大表分页优化
   SELECT * FROM products p 
   JOIN (SELECT id FROM products ORDER BY create_time DESC LIMIT 100000, 10) t
   ON p.id = t.id;
   

2. GROUP BY优化

   • 使用 ORDER BY NULL 避免排序
   • 考虑使用松散索引扫描

   SELECT category, COUNT(*) FROM products 
   GROUP BY category ORDER BY NULL;
   

七、高级优化技术

1. 覆盖索引

   -- 索引包含所有查询字段
   CREATE INDEX idx_covering ON orders(user_id, status, amount);
   
   SELECT user_id, status FROM orders WHERE user_id = 100;
   

2. 索引条件下推(ICP)

   • MySQL 5.6+ 特性
   • 将WHERE条件推到存储引擎层处理

3. MRR优化

   • 多范围读取优化
   • 减少随机IO

八、实战优化案例

案例1：优化慢查询

-- 原查询（执行时间2.5s）
SELECT * FROM orders 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY amount DESC LIMIT 100;

-- 优化后（0.05s）
CREATE INDEX idx_order_composite ON orders(create_time, amount);

SELECT * FROM orders FORCE INDEX(idx_order_composite)
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY amount DESC LIMIT 100;

案例2：优化COUNT查询

-- 原查询（全表扫描）
SELECT COUNT(*) FROM users WHERE status = 'active';

-- 优化方案1：使用近似值
SHOW TABLE STATUS LIKE 'users';

-- 优化方案2：维护计数器表

九、工具辅助优化

1. 慢查询日志分析

   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 1;
   

2. Performance Schema

   SELECT * FROM performance_schema.events_statements_summary_by_digest
   ORDER BY sum_timer_wait DESC LIMIT 10;
   

3. 第三方工具

   • pt-query-digest
   • MySQLTuner
   • Percona Toolkit

查询优化是一个持续的过程，需要结合业务特点、数据分布和实际执行计划进行分析。建议每次只做一项变更，并测量其效果，逐步找到最优的查询方案。