SQL查询优化：WHERE与HAVING区别及MySQL索引实战

1. SQL查询中的WHERE与HAVING深度解析

作为数据库工程师，我经常在技术面试中被问到WHERE和HAVING的区别。这两个看似简单的关键字，在实际业务场景中的使用却大有讲究。上周刚结束的一场技术面试中，候选人就因为在聚合查询中错误使用WHERE条件而被扣分。今天我们就来彻底搞懂这对组合，顺便聊聊MySQL索引的那些"小心思"。

WHERE和HAVING最本质的区别在于它们的执行阶段不同。WHERE在数据分组前进行过滤，而HAVING在分组后对结果集进行筛选。举个例子，当我们需要找出销售额超过100万的部门时：

sql复制-- 正确做法
SELECT department, SUM(sales) as total 
FROM orders 
GROUP BY department 
HAVING total > 1000000;

-- 错误示范（会导致语法错误）
SELECT department, SUM(sales) as total 
FROM orders 
WHERE total > 1000000
GROUP BY department;

2. 执行原理与性能影响

2.1 查询处理流程拆解

MySQL执行包含GROUP BY的查询时，处理流程是这样的：

1. 通过WHERE条件过滤原始数据
2. 对过滤后的数据进行排序和分组
3. 计算聚合函数（如SUM、AVG）
4. 最后应用HAVING条件

这个顺序解释了为什么WHERE条件可以使用普通列但不能用聚合结果，而HAVING正好相反。我曾优化过一个报表查询，把HAVING中的普通条件移到WHERE后，性能提升了3倍：

sql复制-- 优化前（错误）
SELECT user_id, COUNT(*) 
FROM logs 
GROUP BY user_id 
HAVING create_time > '2023-01-01';

-- 优化后
SELECT user_id, COUNT(*) 
FROM logs 
WHERE create_time > '2023-01-01'
GROUP BY user_id;

2.2 索引利用差异

WHERE条件能够利用索引，而HAVING不能。这是因为：

• WHERE在引擎层执行时就可以使用索引
• HAVING是在服务器层对结果集进行过滤

这个特性导致了一个常见的性能陷阱：在百万级数据表上，先GROUP BY再HAVING过滤，会比先WHERE过滤再GROUP BY慢几个数量级。

3. MySQL索引的"小心思"

3.1 索引失效的典型场景

即使理解了WHERE和HAVING的区别，MySQL索引仍可能"闹脾气"不工作。以下是几个实战中遇到的案例：

1. 隐式类型转换：当查询条件的类型与列定义不匹配时

sql复制-- user_id是varchar类型，但用了数字查询
SELECT * FROM users WHERE user_id = 123; -- 索引失效

2. 前导模糊查询：LIKE以通配符开头

sql复制SELECT * FROM products WHERE name LIKE '%手机%'; -- 全表扫描

3. 函数操作：对索引列使用函数

sql复制SELECT * FROM orders WHERE DATE(create_time) = '2023-01-01';

3.2 复合索引的最左匹配原则

面试中经常被问到的复合索引问题，其实有个简单的记忆方法：

• 就像电话簿按"姓+名"排序
• 只查"名"时索引无效
• 查"姓"或者"姓+名"时索引有效

sql复制-- 假设有联合索引 (department, position)
SELECT * FROM employees WHERE department = 'IT'; -- 用索引
SELECT * FROM employees WHERE position = 'Engineer'; -- 不用索引

4. 实战优化案例

去年优化过一个电商平台的订单查询，原始SQL是这样的：

sql复制SELECT user_id, COUNT(*) as order_count 
FROM orders 
HAVING order_count > 5 
GROUP BY user_id;

这个查询有三个问题：

1. HAVING和GROUP BY顺序反了
2. 应该先用WHERE过滤无效订单
3. 没有利用user_id的索引

优化后的版本：

sql复制SELECT user_id, COUNT(*) as order_count 
FROM orders 
WHERE status = 'completed'
GROUP BY user_id 
HAVING order_count > 5;

配合(user_id, status)的复合索引，查询时间从2.3秒降到了0.05秒。

5. 高频面试题解析

5.1 WHERE和HAVING可以同时使用吗？

当然可以，而且这是最佳实践。典型的模式是：

• WHERE先过滤掉不需要的数据
• GROUP BY聚合计算
• HAVING对聚合结果筛选

sql复制SELECT product_id, AVG(rating) as avg_rating
FROM reviews
WHERE create_time > '2023-01-01'
GROUP BY product_id
HAVING avg_rating > 4.0;

5.2 什么情况下必须使用HAVING？

当筛选条件包含聚合函数时：

sql复制SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 10000;

5.3 索引为什么有时候不生效？

除了前面提到的场景，还要注意：

• 使用了OR条件（可改用UNION）
• 索引列参与了计算
• 使用了NOT、!=或<>操作符
• 表数据量很小时，优化器可能选择全表扫描

6. 避坑指南

1. 执行计划检查：养成用EXPLAIN分析查询的习惯，我通常在开发阶段会对所有复杂查询做这个检查。

2. 索引选择性原则：为高区分度的列建索引。比如性别字段只有两个值，建索引通常没有意义。

3. 覆盖索引技巧：让索引包含所有查询需要的列，避免回表操作。例如：

sql复制-- 需要创建 (user_id, name) 复合索引
SELECT user_id, name FROM users WHERE user_id = 100;

4. 分页查询优化：不要用LIMIT 10000,10，改用WHERE id > last_id LIMIT 10的方式。

记得有次排查一个慢查询，最后发现是因为开发同学在循环里执行了上千次SELECT * FROM table WHERE id = xxx。改成WHERE id IN (...)后，查询时间从5秒降到了0.1秒。