第3题索引的设计原则回答核心考点大厂面试中索引设计原则不仅是“知道什么该建”更要懂“什么不该建”以及“为什么”。面试官期望你从查询模式、数据分布、写入代价三个维度综合权衡给出可落地的设计规范。1. 索引设计的核心目标索引的本质是用空间换时间但代价是写入性能下降和存储成本增加。设计原则的核心是在查询效率和写入/存储成本之间找到平衡点。大厂口诀读多写少加索引写多读少要克制覆盖索引是王道最左前缀不能忘。2. 六大核心设计原则面试必背原则核心要点反例不要这样建大厂最佳实践① 为高频查询字段建索引WHERE/JOIN/ORDER BY/GROUP BY字段优先给从不查的字段加索引用慢查询日志识别高频查询针对性建索引② 高区分度字段优先区分度 COUNT(DISTINCT col)/COUNT(*)越接近1越好给性别字段建单独索引除非查询极少数值区分度 0.1 才考虑建索引③ 长字段用前缀索引只索引前N个字符减少存储和I/O给TEXT/BLOB全字段建索引必须指定前缀前缀长度选择以区分度损失5%为准④ 更新频繁字段谨慎建索引列每更新一次B树就要调整一次给last_login_time建索引更新频繁但查得少的字段牺牲查询换写入⑤ 联合索引遵循最左前缀查询条件必须从索引最左列开始匹配只查第2列却不查第1列等值查询列放左边范围查询列放右边⑥ 用覆盖索引避免回表索引包含查询所需的所有字段SELECT *配合二级索引必回表只查索引中的列SELECT只写必要字段3. 原则详解 大厂面试追问原则①为高频查询字段建立索引为什么索引的目的是快速缩小扫描范围。如果某字段从不作为查询条件建索引只会浪费空间并拖慢写入。大厂追问“怎么识别高频查询字段”回答开启慢查询日志slow_query_logONlong_query_time1用pt-query-digest分析慢日志按查询频率排序对TOP 10的慢查询通过EXPLAIN看是否缺索引原则②高区分度字段优先低区分度需谨慎核心公式区分度 COUNT(DISTINCT col) / COUNT(*)区分度 ≈ 1如手机号、身份证→非常适合建索引区分度 ≈ 0如性别、状态码→通常不适合单独建索引除非数据分布极不均匀大厂追问“性别字段什么时候适合建索引”回答当数据分布极度不均时查询少数值可以用索引。举例订单表status有pending(10条) 和done(100万条)查询WHERE statuspending时索引能快速定位10条记录虽然基数低但有用。量化经验区分度 0.1 → 可以考虑区分度 0.01 → 除非覆盖索引或联合索引否则不建议单独建原则③长字段使用前缀索引语法-- 只索引 content 字段的前50个字符ALTERTABLEarticlesADDINDEXidx_content(content(50));为什么VARCHAR(255)全字段索引每个索引项占255字节页内能存的索引项少 → 树高增加 → I/O增多。大厂追问“前缀长度怎么选”回答目标是在空间和区分度间平衡。通常测试不同长度-- 计算不同前缀长度的区分度SELECTCOUNT(DISTINCTLEFT(content,10))/COUNT(*)ASsel_10,COUNT(DISTINCTLEFT(content,20))/COUNT(*)ASsel_20,COUNT(DISTINCTcontent)/COUNT(*)ASsel_fullFROMarticles;选择区分度接近全字段、但长度较短的值。例如全字段区分度0.95前缀20字符已达0.94则选20。MySQL限制InnoDBREDUNDANT/COMPACT格式前缀最大767字节InnoDBDYNAMIC/COMPRESSED格式前缀最大3072字节多字节字符集UTF8MB4需注意1个字符可能占4字节原则④更新频繁字段谨慎建索引为什么索引是有序数据结构字段值更新可能导致B树页分裂/页合并代价高写入放大更新一行数据需要维护该行所有索引大厂追问“last_login_time每次登录都更新要不要建索引”回答如果查询场景是“统计近7天登录用户”该字段既要更新又要查询解决方案方案1接受写入代价建索引读多写少时方案2用异步统计表 定时任务避免高频更新字段被索引写多读少时方案3用覆盖索引减少回表代价经验法则读写比 10:1 → 可以建索引读写比 3:1 → 慎重考虑是否必要原则⑤联合索引遵循最左前缀联合索引(a, b, c)的命中规则WHERE条件是否命中使用哪些列说明WHERE a1✅a最左列命中WHERE a1 AND b2✅a, b连续两列WHERE a1 AND b2 AND c3✅a, b, c全命中WHERE a1 AND c3✅部分ac用ICP跳过bc无法用于索引查找但可用索引下推过滤WHERE b2❌无缺少最左列全表扫描WHERE a1 AND b2 AND c3✅部分a, bc无效范围查询b会中断后续列大厂追问“为什么要遵循最左前缀底层原理是什么”回答B树索引的排序规则是先按第一列排序第一列相同再按第二列排序。(a, b)联合索引数据先按a排序a相同时按b排序只给b条件时b在整个树中不是全局有序的无法利用索引设计技巧等值查询列放左边范围查询列放右边(a, b)中WHERE a1 AND b2能用完(a,b)反过来(b, a)无法使用区分度高的列放左边能更快缩小范围频繁查询的列放左边让更多查询能用上索引原则⑥用覆盖索引避免回表什么是回表二级索引叶子节点存的是主键值不是完整行数据。查询非索引列时需要先用二级索引找到主键再用主键查聚簇索引两次B树查找。覆盖索引索引包含了查询所需的所有字段无需回表直接在索引上返回结果。示例-- 联合索引idx_name_age (name, age)-- 覆盖索引查询SELECTname,ageFROMusersWHEREname张三;-- 不回表Using index-- 非覆盖索引查询SELECTname,age,cityFROMusersWHEREname张三;-- 回表Using index condition大厂追问“如何判断一条SQL是否用了覆盖索引”回答用EXPLAIN看Extra列Using index→ 使用了覆盖索引不回表Using index condition→ 用了索引索引下推但仍需回表没有Using index→ 需要回表优化技巧避免SELECT *只查必要的字段把高频查询字段放入联合索引变成覆盖索引4. 索引命名规范大厂加分项大厂通常有索引命名规范面试中提到能体现工程素养索引类型命名规范示例普通索引idx_表名_字段名idx_user_name联合索引idx_表名_字段1_字段2idx_order_user_time唯一索引udx_表名_字段名udx_user_email前缀索引idx_表名_字段名_prefixidx_article_content_prefix5. 实战案例电商订单表索引设计业务场景订单表orders字段order_id(主键)、user_id、status、create_time、amount高频查询SELECT * FROM orders WHERE user_id ? ORDER BY create_time DESC LIMIT 10查询某用户最近订单SELECT COUNT(*) FROM orders WHERE status pending统计待处理订单SELECT SUM(amount) FROM orders WHERE create_time BETWEEN ? AND ?日报统计索引设计方案索引设计理由潜在问题及应对主键索引PRIMARY KEY (order_id)自增整型避免页分裂UUID主键会导致二级索引膨胀联合索引idx_user_create (user_id, create_time)覆盖排序字段避免Using filesortuser_id需等值查询覆盖索引idx_status (status) 考虑使用计数表status区分度低若99%订单为done查pending用索引有效否则用异步计数表替代覆盖索引idx_create_time (create_time, amount)日报统计时覆盖amount避免回表若create_time范围太大考虑按月分表反例不要这样做❌ 给status、create_time、user_id分别建三个单列索引 → 浪费空间写入慢❌ 主键用UUID → 页分裂频繁性能差❌ 给amount单独建索引不单独作为查询条件6. 索引失效场景速查面试必考失效场景示例解决方案函数操作WHERE DATE(create_time) 2024-01-01改用范围查询create_time BETWEEN 2024-01-01 00:00:00 AND 2024-01-01 23:59:59隐式类型转换WHERE phone 13800138000phone是VARCHAR保持类型一致phone 13800138000联合索引不满足最左前缀索引(a,b)只查b1要么调顺序要么新建索引使用!或WHERE status ! done考虑用IN包含需要的值LIKE以通配符开头WHERE name LIKE %张改用name LIKE 张%或全文索引OR中包含非索引列WHERE namea OR age30age无索引拆成UNION或给age加索引字符集/排序规则不一致两表JOIN字段字符集不同utf8 vs utf8mb4统一字符集和排序规则7. 总结对比表原则核心要点适用场景不适用场景高频字段建索引WHERE/JOIN/ORDER BY字段读多写少写多读少高区分度优先区分度 0.1唯一性字段性别、状态等低基数字段前缀索引长字符串只索引前N字符VARCHAR/TEXT/BLOB短字段更新频繁谨慎建写入代价高几乎不变的字段频繁UPDATE的字段最左前缀从最左列开始匹配多条件查询跳列查询覆盖索引索引包含所有查询列高频小查询SELECT * 场景面试官想要的满分总结“索引设计核心原则是从查询模式出发在查询效率和写入代价间权衡。六大原则① 高频WHERE/JOIN/ORDER BY字段优先建索引② 高区分度字段优先区分度0.01的除非联合索引否则慎重③ 长VARCHAR/TEXT用前缀索引以区分度损失5%为界④ 更新频繁字段谨慎建读写比3:1时不建⑤ 联合索引遵循最左前缀等值列放左、范围列放右⑥ 尽量用覆盖索引避免回表SELECT时只查必要字段。落地规范用EXPLAIN验证每个索引是否生效定期通过慢查询日志和pt-query-digest识别慢SQL单个表索引数建议不超过5-6个主键用自增整型避免UUID。面试常考的反例性别建单列索引、联合索引跳列、长字符串全字段索引、SELECT *导致回表——这些都要能解释清楚为什么错。”
