MySQL索引优化面试题及答案（实战口语版）

MySQL索引优化面试题及答案（实战口语版）一、基础必问（初级面试高频）1、什么是索引？索引的作用是什么？答案：索引可以简单理解成数据表的目录，是帮助数据库快速检索数据的存储结构。核心作用就两个：一是大幅提升查询速度，避免全表扫描；二是减少数据库IO消耗。但索引不是越多越好，它会额外占用磁盘空间，同时会降低新增、修改、删除的执行效率，因为数据变更时索引也要同步更新。2、MySQL常用索引类型有哪些？日常开发用的最多的是哪种？答案：常见类型：主键索引、唯一索引、普通索引、联合索引、全文索引。日常开发最常用的是联合索引，其次是普通索引、唯一索引。全文索引一般只用于简单文本搜索，业务中用得很少。简单区分：主键索引非空唯一、一张表只能一个；唯一索引允许为空、值唯一；普通索引无限制，只用于加速查询。3、主键索引和普通索引的区别？答案：1、主键索引是聚簇索引，普通索引是二级索引；2、主键索引字段必须非空且唯一，普通索引字段可重复、可为空；3、InnoDB表必须有聚簇索引，默认用主键充当，没有主键就用唯一索引，都没有会自动生成隐藏主键；4、普通索引查询需要先查索引树拿到主键，再回表查数据，主键索引直接查到完整数据。4、InnoDB为什么用B+树做索引，不用B树、哈希、二叉树？答案：这个是面试高频，说重点就行：1、摒弃二叉树：层数太高，数据量大时查询、IO次数多，效率极低；2、摒弃哈希索引：不支持范围查询、排序、模糊查询，只适合等值查询，业务场景太局限；3、优于普通B树：B+树所有数据都在叶子节点，非叶子节点只存索引键，内存能存更多索引，树的层数更少、IO更少；同时叶子节点是有序链表，范围查询、排序、分页特别快。二、进阶核心题（中级面试重点）5、什么是联合索引？最左前缀原则怎么理解？答案：联合索引就是给多个字段一起建的索引，比如index(a,b,c)。最左前缀原则核心：索引生效必须匹配索引的最左开头字段。还是index(a,b,c)举例：能走索引：wherea=?、wherea=?andb=?、wherea=?andb=?andc=?失效场景：whereb=?、wherec=?、whereb=?andc=?（跳过了最左a）补充：查询中如果用到了最左字段，中间字段断了，后面的索引也会失效，比如a=?andc=?，只有a字段索引生效，c不走索引。6、什么是回表？什么是覆盖索引？怎么避免回表？答案：回表：普通二级索引只存索引字段和主键，查询时先通过二级索引找到主键，再通过主键索引查完整数据，多一次查询操作，就是回表，会拖慢性能。覆盖索引：查询的所有字段，全部都包含在索引里，不需要回表。举例：建索引index(name,age)，查询selectname,agefromuserwherename=?，直接从索引就能拿到全部数据，无需回表。避免回表核心：通过设计联合索引，把查询、筛选、排序的字段都包含进索引，实现覆盖索引。7、索引失效的常见场景有哪些？（面试必背）答案：日常开发90%的索引失效都是这几种情况，直接背：1、违反最左前缀原则，跳过联合索引最左字段；2、索引字段做运算、函数操作，比如whereleft(phone,11)='xxx'、whereage+1=20；3、字段类型隐式转换，最常见：字符串字段不加引号，wherephone4、使用!=、<>、notin、notexists（大量数据场景基本失效）；5、like以%开头，比如%张三、%三；6、or连接非索引字段，两个字段一个走索引一个不走，整体失效；7、MySQL优化器判断全表扫描比索引更快，主动放弃索引（数据量少、索引选择性差）。8、什么是索引选择性？为什么选择性差的字段不建议建索引？答案：索引选择性就是字段不重复数据的比例。选择性越高，索引效果越好，比如手机号、身份证、用户名，唯一性高，适合建索引；性别、状态、是否删除这种字段，值只有2-3种，选择性极低，不适合建索引。原因：选择性差的字段，查询出来的数据占比极高，MySQL还不如直接全表扫描，索引基本失效，还白白占用空间、拖慢写入性能。9、orderby、groupby、sortby怎么优化？为什么有时候排序不走索引？答案：核心原则：筛选字段和排序字段要在同一个联合索引里，且顺序匹配最左前缀。比如常用查询：wherea=?orderbyb，索引就要建index(a,b)。排序不走索引的常见原因：1、筛选、排序字段拆分了两个索引；2、联合索引顺序不对，比如wherea=?orderbyb，索引建了index(b,a)；3、排序字段使用了函数、不同排序规则（一个asc一个desc）。如果索引无法优化，大数据量排序会出现Usingfilesort、Usingtemporary，性能很差。三、实战场景优化（高级面试）10、平时上线前怎么检查SQL索引是否生效？答案：工作中直接用explain分析，重点看三个字段：1、type：优先级system>const>eq_ref>ref>range>index>all，绝对不能出现all（全表扫描）；2、key：显示实际用到的索引，null就是没走索引；3、Extra：出现Usingfilesort（文件排序）、Usingtemporary（临时表），说明SQL需要优化。11、一张表索引太多有什么问题？线上怎么处理冗余索引？答案：索引不是越多越好，索引过多：占用磁盘空间、数据增删改效率大幅下降、索引维护成本高。冗余索引场景：比如已有index(a,b)，再建index(a)，后者就是冗余，因为联合索引已经包含了单字段a的查询能力。线上处理：优先删除冗余、重复、长期未使用的索引，保留高频查询、覆盖核心业务的索引。12、limit分页深度过大怎么优化？比如limit100000,10答案：深度分页直接查会扫描10万条数据再丢弃，极慢。日常最优优化方案：主键索引分页回溯原SQL：select*fromuserlimit100000,10优化后：select*fromuserwhereid>100000limit10原理：利用主键有序、索引高效，直接定位起始位置，避免扫描大量无效数据。补充：前端分页尽量禁止深度翻页，只保留上一页、下一页，从业务层面规避问题。13、千万级大表索引优化思路是什么？答案：分四个维度，工作中通用思路：1、先抓核心：根据高频查询SQL，建立联合覆盖索引，解决全表扫描、文件排序问题；2、清理冗余：删除无用、重复、低选择性索引，减少写入开销；3、规避失效：统一字段类型、禁止索引字段运算、规范like查询、避免or滥用；4、特殊优化：大表禁止频繁加索引（会锁表），低峰期操作；超大数据量表考虑分表、分区。14、为什么like'xxx%'能走索引，like'%xxx'不行？答案：B+树索引是有序前缀存储的。like'xxx%'是前缀匹配，可以通过索引快速定位前缀开头的数据，走索引扫描；like'%xxx'是后缀匹配、'%xxx%'是模糊匹配，无法利用有序前缀，只能全表扫描，索引直接失效。四、高频反问面试题（面试官最爱追问）15、建索引的最佳顺序是什么？答案：优先顺序：等值字段>范围字段>排序分组字段。等值放最左，范围放中间或最后，排序字段放最后，最大化贴合最左前缀，同时覆盖查询、排序、分组。16、unique索引和普通索引哪个查询更快？答案：查询速度几乎没区别。区别在写入：唯一索引需要校验唯一性，写入稍慢；普通索引无需校验，写入更快。查询场景下两者效率一致。17、count(*)、count(1)、count(字段)