MySQL索引高频面试题及真实面试答案（接地气版）

MySQL索引高频面试题及真实面试答案（接地气版）一、基础概念类（入门必问）1、什么是索引？通俗解释一下索引就是数据库给数据表建的「目录」。就像看书的目录，不用逐页翻书，直接通过目录定位到对应内容。放在MySQL里，就是加快数据查询速度的磁盘数据结构。核心作用：减少查询时的磁盘IO，避免全表扫描，提升查询效率。注意：索引只加速查询，会降低新增、修改、删除的效率，因为数据变了，索引也要同步更新。2、MySQL常用的索引类型有哪些？各自适用场景从结构上分，最常用的就两种：B+树索引：MySQL默认、最主流。支持范围查询、排序、分组，适配绝大多数业务场景，普通索引、主键索引都是B+树。哈希索引：等值查询极快，不支持范围查询、排序、模糊查询。Memory引擎默认使用，业务中基本不用。从逻辑使用上分：主键索引（聚簇索引）：唯一、非空，一张表只有一个，叶子节点存整行数据。普通索引（二级索引）：允许重复、为空，叶子节点存主键值。唯一索引：不允许重复，允许为空，用于唯一约束字段（手机号、身份证）。联合索引（复合索引）：多个字段组合建索引，日常优化用得最多。全文索引：用于长文本模糊匹配，替代like%xxx%的低效查询。3、聚簇索引和非聚簇索引的区别？聚簇索引（主键索引）一张表只能有一个，默认用主键生成，没有主键则用唯一键，都没有就自动生成隐藏主键。B+树叶子节点存储整行完整数据，索引和数据绑定在一起。查询主键直接命中数据，速度最快。非聚簇索引（二级索引）一张表可以有多个。叶子节点只存索引字段+主键值，不存完整数据。通过二级索引查询数据，需要先查索引拿到主键，再通过主键索引查完整数据，这个过程叫回表。4、什么是回表？怎么避免回表？回表：使用普通索引查询时，索引里没有完整数据，需要拿着主键再去聚簇索引查一次完整数据，多了一次IO，效率变低。避免回表核心方案：覆盖索引让查询的所有字段，都包含在联合索引中，索引里就能拿到全部数据，不需要回表。例：查询selectname,agefromuserwhereid=10；如果建立索引(id,name,age)，就是覆盖索引，无回表。二、B+树原理类（面试高频）5、为什么MySQL索引用B+树，不用B树、二叉树、哈希表？核心原因：适配磁盘IO，减少磁盘读取次数1、不用二叉树：二叉树层高太高，数据量大时树很深，查询要多次磁盘IO，效率极低，还容易失衡。2、不用B树：B树每个节点都存数据，一页能存的索引少，层高更高；B+树只有叶子节点存数据，非叶子节点只存索引键，一页能存更多索引，树更矮，IO次数更少。3、不用哈希表：哈希只支持等值查询，不支持范围查询、排序、分组、模糊查询，业务场景受限太大；且有哈希冲突。B+树核心优势：层高极低，查询稳定、高效；所有数据都在叶子节点，且叶子节点双向链表串联，范围查询、排序超级快；非叶子节点轻量化，内存可缓存，减少磁盘IO。6、B+树叶子节点和非叶子节点分别存什么？非叶子节点（分支节点）：只存索引字段值和子节点指针，不存行数据，作用是路由、定位数据在哪一层。叶子节点：聚簇索引存整行数据；二级索引存索引字段+主键值，且所有叶子节点有序双向链表相连，支持高效范围查询。三、联合索引最左匹配原则（必考）7、什么是最左前缀原则？联合索引遵循从左到右依次匹配的规则，查询条件必须匹配索引的最左前列，索引才会生效。举例：建立联合索引(a,b,c)能走索引的场景：wherea=?、wherea=?andb=?、wherea=?andb=?andc=?失效场景：whereb=?、wherec=?、whereb=?andc=?（跳过最左a，索引直接失效）补充：中间字段断了，后面索引全部失效。比如wherea=?andc=?，只有a字段走索引，c不走。8、联合索引字段顺序怎么设计？（工作实操）核心原则：等值在前，范围在后；高频查询字段在前1、先等值查询字段（=、in），后范围查询字段（>、<、between、like左模糊）2、查询频率高、区分度高的字段放左边原因：范围查询后面的字段，无法走索引，所以范围必须放最后。错误示例：(age,name)（age是范围查询，会导致name索引失效）正确示例：(name,age)四、索引失效场景（面试重中之重）9、列举常见的索引失效场景整理工作中最常遇到的9种失效情况，真实面试直接背：1、不满足最左前缀原则，跳过联合索引最左字段查询2、索引字段做函数操作、运算、类型转换：whereleft(name,2)='xx'、whereage+1=203、隐式类型转换：字符串字段不加引号、数字字段加引号（最坑、最常见）4、like%xxx右模糊、%%xxx%%全模糊（只有likexxx%左模糊走索引）5、or连接查询，左右字段有一个没索引，整体索引失效6、notin、notexists、<>不等于（数据量大时索引失效）7、isnull、isnotnull大部分场景索引失效8、MySQL优化器判断全表扫描比索引更快（比如查询数据占表数据30%以上，索引失效）9、联合索引中，范围查询字段后面的字段索引失效10、为什么字段用函数会导致索引失效？索引是基于原始字段值排序存储的。如果对字段做函数运算，相当于改变了字段的值，数据库无法在原有有序索引树中匹配数据，只能全表扫描，逐行计算后再匹配。解决办法：函数计算尽量放常量侧，不要放字段侧；或者建立函数索引（MySQL8.0支持）。五、索引优化实战问题11、哪些字段不适合建索引？1、数据量小的表：几百条数据，全表扫描比索引更快，没必要建2、区分度极低的字段：比如性别、状态（0/1），重复值太多，索引没有意义3、频繁更新、删除的字段：索引维护成本高，拖慢写操作性能4、长文本字段：varchar超长，索引占用磁盘空间大，效率低，优先用全文索引12、唯一索引和普通索引怎么选？业务有唯一约束（手机号、账号、身份证）：必须用唯一索引，防止重复数据，同时自带索引效果。无唯一约束，仅加速查询：用普通索引。补充：普通索引更新时可以利用changebuffer缓冲，减少磁盘IO；唯一索引必须实时校验唯一性，无法缓冲，写性能略差。13、什么是索引下推（ICP）？作用是什么？索引下推是MySQL5.6之后的优化机制。简单说：在索引遍历阶段，优先过滤索引中的字段条件，减少回表次数。没有ICP：先通过索引匹配数据，全部回表拿到完整数据后，再过滤条件。有ICP：在索引节点就把不满足条件的数据过滤掉，只对符合条件的数据回表，大幅减少IO。14、什么是覆盖索引？优势是什么？查询需要的所有字段，全部包含在索引中，不需要回表查询聚簇索引，就是覆盖索引。优势：杜绝回表，减少磁盘IO，查询性能大幅提升，是日常SQL优化最常用的手段。判断依据：explain执行结果中，Extra字段显示Usingindex。六、高阶面试题（中高级必问）15、explain关键字你常用的字段有哪些？分别代表什么？日常优化只看这6个核心字段，够用且高频：type：查询类型，核心指标。优先级：system>const>eq_ref>ref>range>index>all（all是全表扫描，必须优化）key：本次查询实际用到的索引possible_key：理论上可以用到的索引rows：预估扫描行数，数值越小越快Extra：额外信息，Usingindex（覆盖索引）、Usingfilesort（文件排序，需优化）、Usingtemporary（临时表，需优化）key_len：索引使用长度，可判断联合索引用了几个字段16、Usingfilesort、Usingtemporary怎么优化？Usingfilesort：文件排序原因：orderby字段没有走索引，MySQL自己排序，消耗资源。优化：让orderby字段包含在联合索引中，利用索引有序特性，避免手动排序。Usingtemporary：临时表原因：groupby、distinct字段无索引，数据库创建临时表存储数据。优化：给分组、去重字段建立索引，避免临时表生成。17、大表加索引为什么慢？有什么解决方案？原因：大表数据量多，建索引需要遍历全表数据、生成B+树、写入磁盘，同时会锁表，影响业务。解决方案：1、MySQL5.7+使用onlineDDL，在线加索引，不锁表（仅短暂元数据锁）2、业务低峰期执行加索引操作3、分批次迁移数据，先加索引再同步数据18、索引越多越好吗？为什么？绝对不是。索引是以写性能换读性能。弊端：1、占用磁盘空间，索引文件体积可能远超数据文件2、增删改操作需要同步更新所有索引，写请求性能大幅下降3、索引过多，MySQL优化器选择索引的耗时增加，容易选错索引原则：只给高频查询、慢查询字段建索引，无用索引及时删除。七、场景实操面试题19、场景：select*fromuserwherename=?andage>?orderbycreate_time怎么建索引？根据等值在前、范围在后、排序最后原则：最优索引：(name,age