MySQL WHERE与HAVING区别及索引优化实战

1. 面试复盘WHERE与HAVING的本质区别在数据库查询中WHERE和HAVING这两个子句经常让初学者感到困惑。作为MySQL核心面试考点理解它们的差异需要从执行机制层面入手。1.1 执行阶段的根本差异WHERE子句在数据分组前进行过滤它作用于原始数据行。当执行包含WHERE的查询时数据库引擎会先扫描表数据根据WHERE条件筛选出符合条件的行然后再对这些行进行分组操作。-- WHERE在GROUP BY之前执行 SELECT department, AVG(salary) FROM employees WHERE hire_date 2020-01-01 -- 先筛选入职日期符合条件的记录 GROUP BY department;而HAVING子句在数据分组后进行过滤它作用于分组结果集。这意味着HAVING可以访问聚合函数的结果-- HAVING在GROUP BY之后执行 SELECT department, AVG(salary) as avg_salary FROM employees GROUP BY department HAVING avg_salary 10000; -- 对分组后的平均薪资进行筛选1.2 使用聚合函数的权限WHERE子句不能直接使用聚合函数如COUNT、SUM、AVG等因为此时尚未进行分组计算。如果需要基于聚合结果过滤必须使用HAVING-- 错误示例WHERE中使用聚合函数 SELECT department, COUNT(*) FROM employees WHERE COUNT(*) 5 -- 这里会报错 GROUP BY department; -- 正确写法 SELECT department, COUNT(*) as emp_count FROM employees GROUP BY department HAVING emp_count 5;1.3 性能影响对比WHERE在早期阶段过滤数据通常能显著减少需要处理的数据量。例如一个包含100万条记录的表如果WHERE条件能过滤掉90%的数据后续GROUP BY只需要处理10万条记录。而HAVING是在完成所有分组计算后才应用过滤条件如果过早使用HAVING而放弃WHERE过滤可能导致不必要的计算开销-- 低效写法 SELECT product_id, SUM(quantity) FROM order_details GROUP BY product_id HAVING product_id IN (1,2,3); -- 先对所有数据分组再过滤 -- 优化写法 SELECT product_id, SUM(quantity) FROM order_details WHERE product_id IN (1,2,3) -- 先过滤减少数据量 GROUP BY product_id;经验法则尽可能在WHERE中完成基础过滤HAVING仅用于必须基于分组结果的过滤。2. MySQL索引机制深度解析2.1 索引的底层数据结构MySQL最常用的InnoDB引擎采用B树作为索引结构。与普通B树不同B树具有以下特点非叶子节点仅存储键值不存储实际数据使得单个节点能容纳更多键值叶子节点形成有序链表支持高效的范围查询所有数据都存储在叶子节点查询路径长度一致(图示B树的三层结构高度为3时可支持约2000万条记录)2.2 聚簇索引与二级索引聚簇索引Clustered Index的特点是叶子节点存储完整数据行每张表只能有一个聚簇索引InnoDB默认使用主键作为聚簇索引-- 创建带有主键的表会自动建立聚簇索引 CREATE TABLE users ( id INT PRIMARY KEY, -- 聚簇索引键 name VARCHAR(50), email VARCHAR(100) );二级索引Secondary Index的特点叶子节点存储主键值而非完整数据查询需要回表操作通过主键二次查找可创建多个二级索引-- 添加二级索引 ALTER TABLE users ADD INDEX idx_name (name);2.3 索引选择与优化原则最左前缀原则复合索引(a,b,c)只能用于WHERE a?WHERE a? AND b?WHERE a? AND b? AND c?但无法用于WHERE b? 或 WHERE c?覆盖索引优化当索引包含所有查询字段时可避免回表-- 创建覆盖索引 ALTER TABLE orders ADD INDEX idx_cover (customer_id, order_date, amount); -- 以下查询可以直接使用索引数据 SELECT customer_id, order_date, amount FROM orders WHERE customer_id 1005;索引选择性原则选择区分度高的列建索引。计算选择性SELECT COUNT(DISTINCT status)/COUNT(*) AS selectivity FROM orders; -- 结果越接近1选择性越好2.4 索引失效的常见场景隐式类型转换字段定义为VARCHAR但用数字查询-- phone是varchar类型时 SELECT * FROM users WHERE phone 13800138000; -- 失效 SELECT * FROM users WHERE phone 13800138000; -- 有效使用函数操作SELECT * FROM logs WHERE DATE(create_time) 2023-01-01; -- 失效 SELECT * FROM logs WHERE create_time BETWEEN 2023-01-01 AND 2023-01-02; -- 有效LIKE通配符前置SELECT * FROM products WHERE name LIKE %手机%; -- 全表扫描 SELECT * FROM products WHERE name LIKE 苹果%; -- 可能使用索引OR条件不当使用-- 当name有索引而age无索引时 SELECT * FROM users WHERE name 张三 OR age 30; -- 全表扫描3. 高级索引应用场景3.1 索引下推优化(ICP)MySQL 5.6引入的Index Condition Pushdown优化允许在存储引擎层提前过滤数据-- 表结构 CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), age INT, dept VARCHAR(20), INDEX idx_name_dept (name, dept) ); -- 没有ICP时执行流程 -- 1. 通过name LIKE 张%定位索引范围 -- 2. 回表读取完整记录 -- 3. 在server层检查dept销售部 -- 启用ICP后执行流程 -- 1. 通过name LIKE 张%定位索引范围 -- 2. 在存储引擎层直接检查dept销售部 -- 3. 只对符合条件的记录回表 SET optimizer_switch index_condition_pushdownon; EXPLAIN SELECT * FROM employees WHERE name LIKE 张% AND dept 销售部;3.2 多范围读取优化(MRR)当执行范围查询时传统方式是按主键顺序多次随机I/O。MRR优化先收集键值再排序转为顺序I/O-- 启用MRR SET optimizer_switch mrron,mrr_cost_basedoff; EXPLAIN SELECT * FROM salaries WHERE emp_no BETWEEN 10010 AND 20000 ORDER BY salary DESC;3.3 索引合并优化当WHERE条件包含多个独立索引时MySQL可能使用Index Merge策略-- 表有index(a)和index(b) EXPLAIN SELECT * FROM table1 WHERE a 1 OR b 2; -- 可能使用索引合并策略4. 实战问题排查与优化4.1 慢查询分析流程确认慢查询日志已开启SHOW VARIABLES LIKE slow_query_log%; SET GLOBAL slow_query_log ON;使用EXPLAIN分析执行计划EXPLAIN FORMATJSON SELECT * FROM orders WHERE user_id 100 ORDER BY create_time DESC;关键指标解读type列从优到差 system const eq_ref ref range index ALLExtra列Using filesort需要额外排序Using temporary使用临时表Using index覆盖索引4.2 常见性能问题解决方案问题1分页查询变慢-- 低效写法偏移量大时 SELECT * FROM logs ORDER BY id LIMIT 100000, 20; -- 优化方案1使用索引覆盖延迟关联 SELECT t.* FROM logs t JOIN (SELECT id FROM logs ORDER BY id LIMIT 100000, 20) tmp ON t.id tmp.id; -- 优化方案2记录上次查询的最大ID SELECT * FROM logs WHERE id 100000 ORDER BY id LIMIT 20;问题2大表添加索引-- 在线DDL工具(pt-online-schema-change) pt-online-schema-change --alter ADD INDEX idx_email (email) Ddatabase,tusers -- MySQL 8.0原生在线DDL ALTER TABLE users ADD INDEX idx_email (email), ALGORITHMINPLACE, LOCKNONE;问题3索引统计信息不准确-- 手动更新统计信息 ANALYZE TABLE orders; -- 查看索引基数 SHOW INDEX FROM orders;4.3 监控与维护建议定期检查未使用索引SELECT * FROM sys.schema_unused_indexes;监控索引使用情况-- 开启性能schema UPDATE performance_schema.setup_instruments SET ENABLED YES WHERE NAME LIKE statement/%; -- 查询索引使用统计 SELECT * FROM sys.schema_index_statistics;维护建议避免过度索引每个额外索引会增加写操作开销对于VARCHAR长字段考虑前缀索引ALTER TABLE articles ADD INDEX idx_title (title(20));定期使用OPTIMIZE TABLE重整表空间针对MyISAM