1. 面试复盘WHERE与HAVING的本质区别在数据库查询中WHERE和HAVING这两个子句经常让初学者感到困惑。它们看似都能过滤数据但实际应用场景和底层机制却大不相同。WHERE子句作用于原始数据表的行级过滤它在GROUP BY分组之前执行。这意味着WHERE条件中不能包含聚合函数因为它处理的是单条记录。例如SELECT * FROM employees WHERE salary 5000;HAVING子句则是对分组后的结果进行过滤它在GROUP BY之后执行。因此HAVING条件中可以使用聚合函数SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) 8000;关键区别WHERE过滤行HAVING过滤组。WHERE在分组前执行HAVING在分组后执行。2. MySQL索引骨架解析2.1 索引的基本结构MySQL索引的底层实现主要采用B树结构。与二叉树不同B树具有以下特点多路平衡查找树每个节点可包含多个键值所有数据都存储在叶子节点非叶子节点只存键值叶子节点通过指针连接形成链表这种结构使得范围查询效率高O(logN)时间复杂度全表扫描可以直接遍历叶子节点链表树高度通常维持在3-4层减少IO次数2.2 索引的物理存储InnoDB引擎中索引和数据统一存储在.ibd文件中。聚簇索引的叶子节点直接包含完整行数据二级索引的叶子节点则存储主键值。这种设计带来几个重要特性主键查询只需一次索引查找二级索引查询需要回表先查二级索引再查主键索引插入数据时会影响多个索引结构3. 索引使用的最佳实践3.1 索引失效的常见场景即使建立了索引以下情况仍可能导致索引失效使用函数操作索引列WHERE YEAR(create_time) 2023隐式类型转换WHERE user_id 100user_id是整型前导模糊查询WHERE name LIKE %张使用OR条件且部分列无索引不符合最左前缀原则的复合索引查询3.2 索引优化技巧覆盖索引让查询所需字段都包含在索引中避免回表-- 建立(name,age)复合索引 SELECT name, age FROM users WHERE name LIKE 张%;索引下推MySQL 5.6特性能在存储引擎层提前过滤数据-- 对于索引(a,b), 即使a用范围查询b条件也能下推 SELECT * FROM table WHERE a 10 AND b 20;索引合并优化器自动合并多个单列索引的扫描结果-- 当name和age都有独立索引时 SELECT * FROM users WHERE name 张三 OR age 30;4. 高级索引策略4.1 自适应哈希索引InnoDB会自动为频繁访问的索引页建立哈希索引加速等值查询。这个特性完全自动管理无需人工干预只对等值查询有效WHERE a 10占用额外内存可通过innodb_adaptive_hash_index参数控制4.2 全文索引对于文本内容的搜索常规B树索引效率低下。MySQL提供全文索引特性-- 创建全文索引 ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body); -- 使用全文搜索 SELECT * FROM articles WHERE MATCH(title,body) AGAINST(数据库优化 IN NATURAL LANGUAGE MODE);全文索引采用倒排索引结构支持自然语言搜索布尔搜索必须包含-必须排除相关性评分排序5. 索引监控与维护5.1 索引使用情况分析通过performance_schema或EXPLAIN分析索引使用效率-- 查看索引使用统计 SELECT * FROM sys.schema_index_statistics WHERE table_schema your_db; -- 检查未使用的索引 SELECT * FROM sys.schema_unused_indexes;5.2 索引维护操作定期维护可以提高索引效率-- 重建索引InnoDB ALTER TABLE table_name ENGINEInnoDB; -- 分析表更新统计信息 ANALYZE TABLE table_name; -- 优化表整理碎片 OPTIMIZE TABLE table_name;对于大型表建议在业务低峰期进行维护操作避免锁表影响业务。6. 索引设计实战案例6.1 电商平台索引设计典型商品表查询场景-- 高频查询1分类状态价格区间 SELECT * FROM products WHERE category_id 5 AND status 1 AND price BETWEEN 100 AND 500 ORDER BY sales DESC LIMIT 20; -- 最佳索引设计 ALTER TABLE products ADD INDEX idx_cat_status_price_sales (category_id, status, price, sales);6.2 社交平台时间线查询用户动态表查询优化-- 查询好友最新动态 SELECT * FROM posts WHERE user_id IN (1,5,8) AND create_time 2023-01-01 ORDER BY create_time DESC LIMIT 10; -- 复合索引设计 ALTER TABLE posts ADD INDEX idx_user_time (user_id, create_time);这种设计利用了索引的有序性避免额外的排序操作。