MySQL索引优化高频面试题及详细答案（实战向）

MySQL索引优化高频面试题及详细答案（实战向）一、索引基础核心面试题1、什么是索引？索引的本质作用是什么？答案：索引是数据库中用于快速检索数据的磁盘数据结构，相当于数据表的“目录”。核心作用两点：1、大幅降低查询IO次数：没有索引时查询数据需要全表扫描，逐行比对数据；建立索引后，可通过索引结构快速定位数据磁盘位置，减少磁盘读写。2、加速查询、排序、分组：索引有序特性，可直接避免查询后的filesort排序、groupby临时表计算，提升执行效率。补充：索引只会提升读性能，会轻微降低写（增删改）性能，因为数据变更时需要同步维护索引结构。2、MySQL常用索引类型有哪些？各自适用场景？答案：日常开发最常用4种索引，InnoDB引擎为主：1、主键索引（聚簇索引）每张表唯一、非空、不重复，InnoDB主键索引的叶子节点存储整行数据。查询效率最高，优先使用自增ID、雪花ID作为主键。2、普通索引（二级索引）最基础的索引，允许重复、可为空，叶子节点存储索引列值+主键值。适用于普通条件查询、筛选字段。3、唯一索引索引列值唯一、不可重复，允许为空。适用于手机号、身份证、用户账号等唯一业务字段，既加速查询又做唯一性约束。4、联合索引（复合索引）多个字段组合建立的索引，遵循最左匹配原则。是日常优化核心，适用于多条件联合查询场景。拓展：还有全文索引（适配文本模糊查询）、空间索引（地理坐标），业务中使用较少。3、聚簇索引和非聚簇索引的核心区别？答案：该问题针对InnoDB引擎，是面试高频点：1、存储结构不同聚簇索引（主键索引）叶子节点存储整行数据，整张表的数据都存在主键索引中；非聚簇索引（普通/唯一/联合索引）叶子节点只存储索引列+主键ID，不存储完整数据。2、查询流程不同主键查询：直接通过聚簇索引找到整行数据，一次索引查找；普通索引查询：先通过二级索引找到主键，再通过主键回表查询完整数据，也就是回表查询。3、数量不同一张表只能有一个聚簇索引，可以有多个非聚簇索引。4、什么是回表查询？如何避免回表？答案：1、回表查询：通过二级索引查询时，只能拿到主键ID，需要再次扫描聚簇索引获取完整行数据，这个二次查询的过程就是回表，会增加查询开销。2、避免方式：使用覆盖索引让查询的所有字段，全部包含在联合索引中，查询时直接通过二级索引拿到全部所需数据，无需回表。示例：建立索引(name,age)，查询selectagefromuserwherename='张三'，直接命中覆盖索引，无回表。二、索引核心原理与失效场景（面试重点）5、联合索引的最左匹配原则是什么？答案：联合索引遵循最左前缀匹配，索引生效的核心是从索引最左侧字段开始连续匹配。举例：建立联合索引(a,b,c)有效场景：wherea=?、wherea=?andb=?、wherea=?andb=?andc=?失效/部分失效场景：1、跳过最左字段：whereb=?、wherec=?，索引完全失效；2、中断匹配：wherea=?andc=?，a字段索引生效，c字段失效，无法用到完整索引。优化原则：建立联合索引时，把高频等值查询字段放前面，范围查询字段放最后。6、MySQL索引失效的常见场景有哪些？（必背）答案：日常开发90%的索引失效都来自以下场景，面试直接口述即可：1、违背最左匹配原则：联合索引跳过左侧字段、字段匹配中断。2、索引列使用函数运算：whereleft(name,2)='张'、wheredate(create_time)=xxx，索引失效。3、索引列进行四则运算：whereage+10=30，索引失效。4、隐式类型转换：字符串索引字段用数字查询（wherephone=1380000而非'1380000'），MySQL自动转换类型导致索引失效。5、使用!=、<>、notin、notexists：大概率索引失效，触发全表扫描。6、like左模糊/全模糊查询：like'%张三'、like'%张三%'索引失效；仅右模糊like'张三%'索引有效。7、or连接非索引字段：wherename=?orstatus=?，如果status无索引，整体索引失效。8、数据量基数过低：索引字段重复率极高（如status只有0、1两个值），MySQL优化器会判断全表扫描更快，主动放弃索引。7、为什么like%xxx会索引失效？likexxx%可以走索引？答案：MySQL索引B+树是有序前缀结构，只能根据前缀快速匹配数据。1、like'xxx%'：匹配前缀数据，可以利用B+树有序特性快速定位，走索引；2、like'%xxx'/'%xxx%'：无固定前缀，无法通过索引有序性筛选，只能全表逐行匹配，索引失效。拓展：如果业务必须模糊全匹配，可使用MySQL全文索引或ES搜索引擎。8、in和exists的区别？会不会导致索引失效？答案：1、核心区别：in：先查询子表所有数据，再和主表做匹配，适合子表数据量小、主表数据量大；exists：遍历主表，逐行匹配子表数据，适合主表数据量小、子表数据量大。2、索引问题：正常情况下in、exists不会导致索引失效；只有大范围in、数据量超大时，优化器可能放弃索引走全表扫描。禁忌：禁止使用notin，极易索引失效且存在空值坑。三、实战索引优化面试题（业务场景）9、如何正确设计联合索引？有什么设计规范？答案：遵循三大核心规范，适配所有业务场景：1、等值优先，范围靠后等值查询（=、in）字段放前面，范围查询（>、<、between、like前缀匹配）放最后。例：wherea=?andb>?，索引建(a,b)，不能建(b,a)。2、高频查询字段优先业务中经常作为查询条件、筛选、排序的字段，放在联合索引靠前位置。3、避免冗余索引已有联合索引(a,b,c)，无需单独建(a)、(a,b)索引，前缀索引可以覆盖前置查询场景，冗余索引会增加写性能开销。10、什么是索引下推（ICP）？有什么作用？答案：索引下推是MySQL5.6及之后的优化特性，全称索引条件下推。作用：在联合索引查询中，在索引层级先过滤非等值条件，减少回表次数。举例：索引(name,age)，查询wherename='张三'andage>18无ICP：通过name找到所有数据，全部回表后再过滤age；有ICP：在索引中先筛选出name=张三且age>18的数据，只对符合条件的数据回表，大幅减少回表开销。总结：ICP核心就是先筛再回表，提升联合索引查询效率。11、什么是索引选择性？为什么选择性低的字段不适合建索引？答案：1、索引选择性：指索引字段不重复数据的比例，选择性越高，索引价值越大。主键、唯一字段选择性100%，最优。2、低选择性字段：比如状态字段status（0/1）、性别字段，数据高度重复。不建索引原因：第一、索引筛选后仍然会返回大量数据，和全表扫描差距不大；第二、MySQL优化器会直接放弃索引，走全表扫描，索引完全失效；第三、占用磁盘空间，增加增删改的索引维护开销，得不偿失。12、订单表、日志表大表如何做索引优化？答案：大表索引优化是面试实操高频，核心方案如下：1、优先建立时间+业务字段联合索引大表查询基本都带时间范围，遵循等值在前、时间范围在后，例：(user_id,create_time)。2、杜绝单字段索引单字段索引容易触发回表、索引失效，优先用覆盖索引满足查询。3、定期清理无效索引删除长期未使用、冗余、重复索引，减少写入压力。4、避免在大表频繁新增/修改索引大表建索引会锁表、耗IO，生产环境需低峰期操作，或使用在线DDL。5、冷热数据分离历史冷数据归档，减少主表数据量，从根源降低索引查询压力。四、高阶优化与踩坑面试题13、主键为什么推荐使用自增ID，不推荐UUID？答案：InnoDB聚簇索引是有序B+树结构，主键有序性直接决定性能：1、自增ID：有序递增，新增数据直接追加在索引树末尾，无页分裂、页合并，写入性能极高；2、UUID：无序随机字符串，新增数据会随机插入索引树中间，频繁触发页分裂、页碎片，严重降低写入性能，且UUID占用存储空间更大，二级索引存储主键也会更占空间。折中方案：业务需要唯一无序ID时，使用雪花算法ID，兼顾有序性和唯一性。14、什么是索引碎片？如何清理？答案：1、索引碎片：数据表长期增删改，会导致索引B+树产生空闲空间、数据页不连续，就是索引碎片。碎片过多会导致查询IO增加、效率下降。2、清理方式：方式一：optimizetable表名，自动整理碎片、收缩空间，适合中小表；方式二：altertable表名engine=InnoDB，重建表和索引，彻底清理碎片，适合大表。注意：生产环境操作会锁表，必须低峰期执行。15、explain执行计划中，key、extra字段如何判断索引是否优化到位？答案：面试核心看这几个关键字段，快速判断优化效果：1、key：实际使用的索引，不为null说明走索引，null说明全表扫描，需要优化；2、Extra关键标识：-Usingindex：命中覆盖索引，无回表，最优状态；-Usingwhere：索引失效，需要服务端过滤数据，需要优化索引；-Usingfilesort：未走索引排序，产生文件排序，严重影响性能，必须优化；-Usingtemporary：查询产生临时表，常见于groupby、distinct，需要优化索引。16、为什么索引不能建太多？上限是多少？答案：1、索引是双刃剑：索引越多，读越快，写越慢。数据表新增、修改、删除数据时，需要同步更新所有索引结构，索引过多会极大增加写入IO和CPU开销。2、资源占用：每个索引都会占用磁盘空间，过多索引会大幅增加磁盘占用，拖慢数据库整体性能。3、行业规范：单表索引不超过5-8个，单条联合索引字段不超过5个，超出后收益极低、副作用极大。五、综合面试压轴题17、请口述一套完整的MySQL索引优化流程答案：工作中标准优化流程，面试可直接完整口述：1、定位慢SQL：通过慢查询日志、数据库监控工具，抓取执行耗时久、扫描行数多的SQL；2、分析执行计划：用explain查看SQ