1. 面试复盘：WHERE与HAVING的本质区别解析
上周面试中被问到一个经典问题："WHERE和HAVING有什么区别？什么时候该用HAVING？"这个问题看似基础，但实际开发中很多人用错。结合MySQL索引优化经验，我来做个深度复盘。
1.1 WHERE的过滤机制
WHERE子句在SQL执行流程中属于"数据提取阶段"的过滤条件。以这个查询为例：
sql复制SELECT department, AVG(salary)
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
WHERE的执行特点：
- 在GROUP BY分组前进行数据筛选
- 作用于原始表的每一行记录
- 可以使用普通列和索引列
- 性能影响：良好的WHERE条件能大幅减少后续处理的数据量
关键点：WHERE条件中应尽量使用索引列，特别是范围查询时。比如上例中的hire_date字段应该建立索引。
1.2 HAVING的过滤逻辑
HAVING子句则是在"数据聚合后"进行过滤：
sql复制SELECT department, AVG(salary) as avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > 10000
HAVING的典型特征：
- 在GROUP BY之后执行
- 只能使用SELECT列表中的列或聚合函数
- 对最终结果集进行筛选
- 性能影响：HAVING处理的是已经聚合的数据，无法利用索引优化
1.3 核心区别对比表
| 维度 | WHERE | HAVING |
|---|---|---|
| 执行阶段 | 数据分组前 | 数据分组后 |
| 可用字段 | 原始表所有列 | 仅分组列和聚合结果 |
| 索引利用 | 可充分利用索引 | 无法使用索引 |
| 性能影响 | 减少原始数据处理量 | 影响最终结果集大小 |
| 典型场景 | 过滤原始记录 | 过滤分组统计结果 |
2. MySQL索引优化实战技巧
2.1 索引失效的常见陷阱
即使建立了索引，这些情况会导致索引失效：
-
在WHERE中对索引列使用函数：
sql复制-- 错误示范 SELECT * FROM users WHERE DATE(create_time) = '2023-01-01' -- 正确写法 SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02' -
使用OR连接非索引列：
sql复制-- 索引失效 SELECT * FROM products WHERE category_id = 5 OR price > 100 -- 优化方案 SELECT * FROM products WHERE category_id = 5 UNION ALL SELECT * FROM products WHERE price > 100 AND (category_id IS NULL OR category_id != 5) -
隐式类型转换：
sql复制-- user_id是varchar类型但传了数字 SELECT * FROM orders WHERE user_id = 10086
2.2 复合索引的最左匹配原则
建立复合索引(a,b,c)时：
-
有效查询：
sql复制WHERE a = 1 WHERE a = 1 AND b = 2 WHERE a = 1 AND b = 2 AND c = 3 -
无效查询：
sql复制WHERE b = 2 WHERE c = 3 WHERE b = 2 AND c = 3
实际案例：用户表查询优化
sql复制-- 原查询
SELECT * FROM users
WHERE city = '杭州' AND age > 25
ORDER BY last_login DESC
-- 最优索引方案
ALTER TABLE users ADD INDEX idx_city_age_login(city, age, last_login)
2.3 覆盖索引的妙用
当查询的所有列都包含在索引中时，可以避免回表操作：
sql复制-- 使用覆盖索引
SELECT user_id, username FROM users
WHERE username LIKE '张%'
-- 对应索引
ALTER TABLE users ADD INDEX idx_username_userid(username, user_id)
实测性能对比：
- 无覆盖索引：需要访问20万行数据，耗时320ms
- 使用覆盖索引：仅访问索引树，耗时28ms
3. 高频面试问题深度解析
3.1 WHERE和HAVING的执行顺序
完整的SQL执行流程：
- FROM子句组装数据
- WHERE条件过滤
- GROUP BY分组
- 计算聚合函数
- HAVING过滤
- 排序ORDER BY
- LIMIT结果集
3.2 索引选择性问题
索引选择性计算公式：
code复制选择性 = 不重复的索引值数量 / 表记录总数
选择率低于30%的列通常不适合单独建索引。例如性别字段只有男/女两种值，建索引反而降低性能。
3.3 EXPLAIN执行计划解读要点
重点关注这些列：
- type：从优到差 system > const > eq_ref > ref > range > index > ALL
- key：实际使用的索引
- rows：预估扫描行数
- Extra：Using index(覆盖索引)、Using filesort(需要额外排序)
4. 实战中的避坑指南
4.1 分页查询优化方案
低效写法：
sql复制SELECT * FROM large_table
ORDER BY create_time DESC
LIMIT 100000, 10
优化方案：
sql复制SELECT * FROM large_table t1
JOIN (
SELECT id FROM large_table
ORDER BY create_time DESC
LIMIT 100000, 10
) t2 ON t1.id = t2.id
4.2 大数据量COUNT优化
避免全表COUNT：
sql复制-- 低效
SELECT COUNT(*) FROM user_logs
-- 优化方案1：使用估算值
SHOW TABLE STATUS LIKE 'user_logs'
-- 优化方案2：维护计数表
CREATE TABLE counter (
table_name VARCHAR(100),
record_count BIGINT
)
4.3 连接查询索引策略
多表连接时的索引建议：
- 确保关联字段有索引
- 小表驱动大表（小表放在JOIN左侧）
- 避免超过3张表的复杂连接
我在实际项目中遇到一个典型案例：一个5表连接查询从8秒优化到0.2秒，关键就是调整了连接顺序和为关联字段添加了复合索引。