MySQL 索引面试题 Top20大厂高频考点摘要: 本文精选 MySQL 索引高频面试题 Top20涵盖 B 树原理、聚簇索引、覆盖索引、联合索引最左前缀原则、索引失效场景等核心考点。深入解析 B 树高度计算、页分裂机制、索引下推优化等进阶知识配合实战案例和性能对比数据。掌握这些知识点轻松拿下阿里、腾讯、字节等大厂 Offer建议收藏 反复研读面试前必看关键词: MySQL 索引、B 树、聚簇索引、覆盖索引、最左前缀原则、索引优化、EXPLAIN、大厂面试导读: 索引是 MySQL 面试的重中之重本文精选 20 道高频索引面试题涵盖 B 树、聚簇索引、覆盖索引、索引失效等核心考点。掌握这些轻松过大厂 一、B 树原理5 题Q1: 为什么 MySQL 使用 B 树而不是 B 树答案要点:1. B 树只有叶子节点存储数据非叶子节点只存索引 → 单页能存储更多索引项树更矮胖IO 次数更少 2. B 树叶子节点用链表连接 → 范围查询只需遍历链表无需中序遍历 3. B 树查询性能稳定 → 所有查询都要走到叶子节点性能可预测 4. B 树性能不稳定 → 可能根节点就找到也可能在叶子节点找到Q2: B 树的高度一般是多少如何计算答案要点:假设 - 主键 BIGINT8 字节 - 指针6 字节 - 页大小16KB - 每行数据1KB 计算 1. 非叶子节点每个索引项 14 字节每页可存 16KB/14B ≈ 1170 个 2. 叶子节点每行 1KB每页可存 16 行 高度计算 - 高度 1:16 行 - 高度 2:1170×16 ≈ 1.8 万行 - 高度 3:1170×1170×16 ≈ 2000 万行 结论 - 百万级数据树高 3 层 - 千万级数据树高 3-4 层 - 亿级数据树高 4-5 层Q3: 一棵 B 树能存多少行数据答案要点:理论计算 - 树高 3 层约 2000 万行 - 树高 4 层约 20 亿行 实际限制 1. InnoDB 限制表空间最大 64TB 2. 主键范围BIGINT 约 9×10^18 3. 性能考虑建议单表不超过 2000 万行 最佳实践 - 超过 1000 万行考虑分库分表 - 超过 5000 万行必须分库分表Q4: 自增 ID 做主键有什么优势答案要点:优势 1. 顺序插入避免页分裂 - 随机值会导致频繁页分裂 - 页分裂影响写入性能 2. 减少碎片提高空间利用率 - 顺序插入页利用率高 - 随机插入碎片多 3. 范围查询高效 - 自增 ID 天然有序 - 范围查询直接扫描 4. 锁竞争少 - 顺序插入锁范围小 - 随机插入锁范围大 对比 - 自增 ID插入性能 100% - UUID插入性能 60-70% - 随机字符串插入性能 50-60%Q5: 什么是聚簇索引和非聚簇索引答案要点:聚簇索引主键索引 - 数据和索引在一起 - 叶子节点存储完整行数据 - 一张表只有一个聚簇索引 - 查询无需回表 非聚簇索引二级索引 - 索引和数据分开 - 叶子节点存储主键值 - 需要回表查询完整数据 - 一张表可以有多个 示例 PRIMARY KEY(id) → 聚簇索引 INDEX(name) → 非聚簇索引叶子节点存 id 二、索引类型5 题Q6: 什么是覆盖索引有什么优点答案要点:定义查询的列都在索引中无需回表查询数据行 示例 CREATE INDEX idx_name_age ON users(name, age); SELECT name, age FROM users WHERE name 张三; -- 覆盖索引 优点 1. 避免回表减少 IO 次数 2. 提高查询性能尤其是随机 IO 3. 降低缓冲池压力 4. 减少锁竞争 场景 - COUNT(*) 统计 - ORDER BY 排序 - GROUP BY 分组 - 去重 DISTINCTQ7: 联合索引是什么最左前缀原则是什么答案要点:联合索引多个字段组成的索引 CREATE INDEX idx_a_b_c ON t(a, b, c); 最左前缀原则 - 从索引的最左边开始匹配 - 不能跳过中间字段 - 遇到范围查询停止 示例索引 idx_a_b_c ✅ a1 ✅ a1 AND b2 ✅ a1 AND b2 AND c3 ✅ a1 AND c3跳过 bc 不生效 ❌ b2没有最左前缀 ❌ c3没有最左前缀Q8: 唯一索引和普通索引的区别答案要点:唯一索引 - 不允许重复值 - 允许 NULL但只能有一个 NULL - 用于保证数据唯一性 - 约束 索引双重作用 普通索引 - 允许重复值 - 允许多个 NULL - 只用于加速查询 性能差异 - 唯一索引更新时需要检查唯一性 - 普通索引更新时无需检查 - 唯一索引略慢于普通索引Q9: 全文索引适用于什么场景答案要点:适用场景 - 文本搜索LIKE %xxx% - 模糊匹配 - 关键词检索 - 文章、博客、评论等 示例 CREATE FULLTEXT INDEX idx_content ON articles(content); SELECT * FROM articles WHERE MATCH(content) AGAINST(MySQL 优化); 特点 1. 支持分词 2. 支持相关性排序 3. 支持布尔运算 4. 只支持 InnoDB 和 MyISAM 注意 - 中文需要分词插件 - 小数据量性能不如 LIKE - 大数据量性能优势明显Q10: 空间索引是什么有什么用途答案要点:空间索引用于地理位置数据的索引 使用 R-Tree 数据结构 示例 CREATE TABLE pois ( id BIGINT PRIMARY KEY, name VARCHAR(100), latitude DECIMAL(10,7), longitude DECIMAL(10,7), SPATIAL INDEX idx_location (latitude, longitude) ); 用途 1. 附近的人/POI 2. 路径规划 3. 区域查询 4. LBS 服务 查询 SELECT * FROM pois WHERE ST_Distance_Sphere(POINT(116.4, 39.9), POINT(longitude, latitude)) 1000; 三、索引优化5 题Q11: 索引失效的场景有哪些至少说 5 种答案要点:-- 1. 模糊查询 LIKE %xxxSELECT*FROMusersWHEREnameLIKE%张%;-- 2. OR 连接条件SELECT*FROMusersWHEREid1ORname张三;-- 3. 函数操作SELECT*FROMusersWHEREYEAR(created_at)2026;-- 4. 类型隐式转换SELECT*FROMusersWHEREphone13800138000;-- VARCHAR-- 5. NOT、!、SELECT*FROMusersWHEREstatus1;-- 6. IS NULL / IS NOT NULLSELECT*FROMusersWHEREnameISNOTNULL;-- 7. 联合索引不满足最左前缀SELECT*FROMusersWHEREb2;-- 索引 (a,b,c)-- 8. 范围查询右边的字段SELECT*FROMusersWHEREa1ANDb2;-- 索引 (a,b)Q12: 如何判断一条 SQL 是否使用了索引答案要点:方法 1: EXPLAIN 分析 EXPLAIN SELECT * FROM users WHERE name 张三; 关注字段 - type: const/ref/range 表示使用索引 - key: 显示使用的索引名 - rows: 扫描行数越少越好 - Extra: Using index 表示覆盖索引 方法 2: 慢查询日志 开启慢查询日志查看未使用索引的 SQL 方法 3: performance_schema SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10; 方法 4: 执行计划缓存 SHOW WARNINGS; -- 查看优化后的 SQLQ13: 索引越多越好吗为什么答案要点:不是索引过多会带来问题 缺点 1. 占用存储空间 - 每个索引都占用磁盘空间 - 索引越大缓冲池命中率越低 2. 降低写入性能 - INSERT/UPDATE/DELETE 需要同步更新索引 - 索引越多写入越慢 3. 增加维护成本 - DDL 操作变慢 - 统计信息更新开销大 4. 优化器选择困难 - 索引太多可能导致选错索引 - 优化时间增加 最佳实践 - 单表索引数建议不超过 5 个 - 联合索引优先使用联合索引 - 定期清理删除未使用的索引Q14: 如何优化慢 SQL答案模板:六步法 1. 定位问题 SQL - 慢查询日志 - performance_schema - SHOW PROCESSLIST 2. 收集信息 - EXPLAIN 分析执行计划 - SHOW CREATE TABLE 看表结构 - SHOW INDEX 看索引情况 3. 分析问题 - type 是否 ALL全表扫描 - rows 是否过多 - Extra 是否有 Using temporary/filesort 4. 制定方案 - 添加索引 - 改写 SQL - 调整表结构 5. 实施优化 6. 验证效果 - 对比优化前后性能 - 灰度上线 - 持续监控Q15: 什么是索引下推有什么作用答案要点:索引下推ICP, Index Condition Pushdown MySQL 5.6 引入的优化技术 原理 - 将 WHERE 条件下推到存储引擎层 - 在索引遍历时就过滤数据 - 减少回表次数 示例 CREATE INDEX idx_name_age ON users(name, age); SELECT * FROM users WHERE name LIKE 张% AND age 20; 优化前 1. 遍历索引找到 name张%的记录 2. 回表查询完整数据 3. 过滤 age20 的条件 优化后ICP 1. 遍历索引时同时过滤 name 和 age 2. 只回表符合条件的记录 效果 - 减少回表次数 - 提升查询性能 30-50% 四、实战案例5 题Q16: 深分页如何优化答案要点:问题LIMIT 9999980, 20 → 扫描 1000 万行丢弃 9999980 行只要 20 行 → 利用率 0.0002%性能极差 优化方案 1. 延迟关联推荐⭐⭐⭐⭐⭐ SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders LIMIT 9999980, 20 ) tmp ON o.id tmp.id; 2. 主键范围 SELECT * FROM orders WHERE id 9999980 LIMIT 20; 3. 禁止翻到最后一页 限制最大页数100 页 4. 使用搜索引擎 ES 负责搜索和分页 MySQL 存储完整数据 效果 8 秒 → 0.5 秒提升 16 倍Q17: 如何为字符串字段添加索引答案要点:方案 1: 前缀索引推荐 CREATE INDEX idx_phone_prefix ON users(phone(7)); -- 只索引前 7 位节省空间 方案 2: 全文索引 CREATE FULLTEXT INDEX idx_name ON users(name); -- 适合模糊查询 方案 3: 哈希列 索引 ALTER TABLE users ADD COLUMN phone_hash BIGINT; UPDATE users SET phone_hash CONV(SUBSTRING(phone, 1, 15), 10, 16); CREATE INDEX idx_phone_hash ON users(phone_hash); 方案 4: 压缩编码 对于固定格式手机号可以转为 INT 存储 phone: 13800138000 → 13800138000 (BIGINT) 注意事项 1. 字符集统一utf8mb4 2. 排序规则一致 3. 避免隐式转换Q18: COUNT(*)、COUNT(1)、COUNT(col) 的区别答案要点:区别 1. COUNT(*) - MySQL 专门优化 - 不关心 NULL - 性能最优 ⭐⭐⭐⭐⭐ 2. COUNT(1) - 等价于 COUNT(*) - 历史遗留写法 - 性能相同 ⭐⭐⭐⭐⭐ 3. COUNT(col) - 忽略 NULL 值 - 需要回表检查 - 性能较差 ⭐⭐⭐ 官方建议 推荐使用 COUNT(*)因为它最清晰 测试数据 - COUNT(*): 0.5 秒 - COUNT(1): 0.5 秒 - COUNT(name): 2.5 秒需要检查 NULLQ19: ORDER BY 如何优化答案要点:方案 1: 在排序字段上添加索引 ⭐⭐⭐⭐⭐ CREATE INDEX idx_created_at ON orders(created_at); SELECT * FROM orders ORDER BY created_at; 方案 2: 利用索引的有序性 索引本身就是有序的 SELECT * FROM users ORDER BY name; -- name 有索引 方案 3: 覆盖索引避免回表 CREATE INDEX idx_name_email ON users(name, email); SELECT name, email FROM users ORDER BY name; 方案 4: 避免 filesort Extra 中出现 Using filesort 需要优化 注意事项 1. 方向一致性ASC/DESC 2. 联合索引最左前缀 3. 避免函数操作Q20: 如何解决死锁问题答案要点:产生原因 1. 交叉更新A 持有 1 请求 2B 持有 2 请求 1 2. 索引不同导致锁范围不同 3. 间隙锁导致的范围冲突 解决方案 1. 固定顺序访问表和行 ⭐⭐⭐⭐⭐ 所有事务都按相同顺序更新 2. 一次性锁定所有资源 减少锁持有时间 3. 使用乐观锁替代悲观锁 SELECT ... FOR UPDATE → 版本号 4. 缩短事务长度 避免长事务 5. 设置超时时间 innodb_lock_wait_timeout50 6. 降低锁粒度 行锁 → 表锁 案例 // 错误不同顺序 事务 A: update(id1) → update(id2) 事务 B: update(id2) → update(id1) // 正确统一顺序 所有事务都按 id 从小到大更新 七、互动话题“你还想了解哪些 MySQL 索引面试题”欢迎提问✅ 面试真题✅ 技术难点✅ 实战经验 关注我:CSDN: 行者-全栈开发公众号眼皮很沉每日更新技术干货