SQL优化面试题及详细答案（实战干货版）

SQL优化面试题及详细答案（实战干货版）一、基础认知类（入门必问）1.工作中为什么会出现SQL慢查询？常见原因有哪些？参考答案：日常开发中慢查询基本都是业务和索引搭配不当导致的，核心常见原因有这几种：1）无索引/索引失效：查询字段、关联字段没建索引，或者写法问题导致索引用不上；2）索引设计不合理：索引建太多、冗余索引，或者联合索引顺序不对，不遵循最左前缀原则；3）返回数据量过大：不加limit、全表批量查询、一次性查询整表数据；4）SQL写法不规范：大量使用select*、子查询嵌套过深、join过多、or/in滥用；5）数据和表结构问题：单表数据量过大（千万级以上）、字段类型不合理、大字段冗余；6）数据库配置问题：连接池、缓存、内存参数配置过低，导致查询效率下降。2.你日常排查慢SQL的完整流程是什么？参考答案：我工作中固定一套排查流程，高效定位问题：第一步：通过数据库慢查询日志、项目监控平台，抓取执行时间长、扫描行数多的慢SQL；第二步：对目标SQL执行explain，查看执行计划，重点关注type、key、rows、Extra字段；第三步：判断问题根源，是没走索引、索引失效，还是扫描数据量太大、表关联不合理；第四步：针对性优化SQL写法或新增、调整索引；第五步：优化后再次执行explain验证执行计划，线上测试对比执行耗时，确认优化生效。3.select*为什么不推荐使用？有哪些替代方案？参考答案：不推荐的核心原因：1）浪费IO和内存：会查询表中所有字段，包含大文本、超长字段，传输、解析、存储都会消耗资源；2）无法使用覆盖索引：如果查询字段超出索引包含的字段，必须回表查询数据，增加耗时；3）兼容性隐患：后续表新增字段后，查询会自动带出新增字段，可能导致代码解析报错；4）数据冗余传输：业务只需要少数字段，却返回全量数据，浪费网络带宽。替代方案：明确写出需要查询的字段，按需选取，业务简单场景也可按需精简字段。二、索引核心优化（面试重点）1.什么是联合索引的最左前缀原则？举例说明参考答案：联合索引是按字段顺序组合存储的，数据库查询时，必须匹配索引最左侧的连续字段，才能命中索引，跳过最左字段会直接索引失效。举例：建立联合索引(a,b,c)能命中索引的场景：wherea=?、wherea=?andb=?、wherea=?andb=?andc=?索引失效的场景：whereb=?、wherec=?、whereb=?andc=?（跳过了最左a字段）补充：日常建联合索引，会把高频查询、等值查询、区分度高的字段放在前面。2.哪些常见写法会导致索引失效？（高频考点）参考答案：这是工作中最常遇到的索引失效场景，基本全覆盖面试考点：1）索引字段使用函数、运算：比如whereleft(name,2)='xx'、whereage+1=20；2）隐式类型转换：索引字段是字符串类型，查询用数字，如wherephone3）not、!=、<>、notin作用于索引字段；4）like查询左模糊、全模糊：like'%xx'、like'%xx%'，仅右模糊like'xx%'可走索引；5）or连接非索引字段：or前后只要有一个字段无索引，整体索引失效；6）联合索引不满足最左前缀原则；7）MySQL判断全表扫描比索引查询更快时，会自动放弃索引（数据量少、索引区分度低时常见）。3.覆盖索引是什么？有什么优化优势？参考答案：覆盖索引指：查询的所有字段，全部包含在索引中，数据库查询时不需要回表查询原始数据，直接通过索引就能拿到全部结果。比如：索引建立为(id,name)，查询语句selectid,namefromuserwhereid=?，就是覆盖索引。优化优势：1）避免回表操作，大幅减少IO开销，查询速度大幅提升；2）执行计划Extra字段会显示Usingindex，是最优查询状态；3）适合高频简单查询场景，是日常优化最常用的手段。4.为什么不建议建过多索引？参考答案：索引不是越多越好，索引只优化查询，会劣化写入、更新、删除性能：1）每次insert、update、delete数据时，数据库需要同步更新所有相关索引，索引越多，写入耗时越长；2）索引会占用磁盘空间，过多索引会增加数据库存储压力；3）优化器选择索引时，需要遍历所有索引，索引过多会增加SQL解析、优化耗时；日常原则：高频查询字段建索引，低频查询、低区分度字段（如性别、状态）不建索引，及时清理冗余索引。三、Explain执行计划专项1.日常看explain，重点关注哪几个字段？优先级排序是什么？参考答案：我优先看核心4个字段，优先级：type>key>rows>Extra1）type：查询类型，是判断SQL优劣的核心最优到最差：system>const>eq_ref>ref>range>index>all日常优化目标：至少达到range、ref级别，坚决避免all（全表扫描）2）key：实际本次查询命中的索引，为null则说明没走索引3）rows：数据库预估扫描的行数，数值越小，查询效率越高4）Extra：额外信息，重点关注Usingfilesort（文件排序）、Usingtemporary（临时表），这两个是严重性能问题，必须优化。2.什么是Usingfilesort和Usingtemporary？如何优化？参考答案：1）Usingfilesort：文件排序，说明orderby字段没有索引，数据库无法利用索引有序性，只能额外排序，数据量大时极慢；优化：给orderby字段建立索引，或调整联合索引顺序，让查询、排序字段都命中索引。2）Usingtemporary：临时表，常见于groupby、distinct、多表关联分组场景，数据库需要创建临时表存储数据，开销极大；优化：优先给分组、去重字段建索引，避免groupby、distinct作用于无索引字段，精简关联表和查询条件。四、SQL写法实战优化1.in和exists的区别？什么时候用哪个？参考答案：核心逻辑：in：先查询子表全部数据，再和主表匹配；exists：遍历主表数据，逐行匹配子表是否存在数据。使用场景：1）主表数据量大、子表数据量小：用in；2）主表数据量小、子表数据量大：用exists；补充：MySQL新版本对in和exists做了优化，差距不大，但大数据量场景仍需遵循以上原则，另外尽量避免notin，极易出现索引失效和空数据坑。2.union和unionall的区别？开发中如何选择？参考答案：1）union：合并查询结果后，会自动去重、排序，性能差，需要创建临时表；2）unionall：直接拼接结果，不去重、不排序，性能远高于union；开发原则：能使用unionall绝不使用union。只有业务明确需要去重时，才用union，其余场景全部用unionall。3.limit大分页为什么慢？如何优化？参考答案：问题原因：limit100000,10时，数据库会先扫描前100010条数据，舍弃前100000条，只返回后10条，偏移量越大，扫描数据越多，耗时越长。常用优化方案：1）主键分页优化（推荐）：通过whereid>上一页最后idlimit10，避免大偏移量；2）延迟关联：先通过索引查出主键，再关联查询完整数据，减少回表开销；3）业务层面限制：禁止前端超大分页查询，分页太深引导用户筛选条件，不允许无限制下拉。五、高阶场景优化（进阶面试）1.单表数据量多大需要分表？分表有哪些方案？参考答案：常规经验：MySQL单表数据量超过500万~1000万，或者单表容量超过10G，查询、写入性能会明显下降，需要考虑分表优化。常见分表方案：1）水平分表：按数据维度拆分，比如按时间、用户ID、订单ID拆分，每张表结构一致，数据拆分存储；适合数据持续增长的业务（订单、日志、流水）；2）垂直分表：按字段拆分，把大字段、低频查询字段拆分到副表，主表保留高频短字段；解决单表字段过多、大字段拖慢查询的问题。2.什么是索引下推（ICP）？有什么作用？参考答案：索引下推是MySQL5.6及以上版本的优化特性，简单说就是：在索引遍历阶段，优先过滤索引中的条件，不需要回表再过滤数据。举例：联合索引(a,b)，查询wherea=1andblike'%xx'，开启ICP后，会在索引层面直接过滤b的条件，减少回表的行数。核心作用：减少回表查询次数，降低IO开销，提升联合索引的查询效率，日常无需手动开启，默认开启即可。3.日常开发中，JOIN查询如何优化？参考答案：1）小表驱动大表：用数据量小的表作为驱动表，大表作为被驱动表，减少循环匹配次数；2）关联字段必须建索引：所有joinon后的关联字段，务必建立索引，避免关联全表扫描；3）先过滤再关联：查询条件优先过滤数据，缩小数据集后再进行join，不要先关联大表再筛选条件；4）减少join表数量：单次查询尽量控制在3张表以内，避免多表嵌套关联；5）避免笛卡尔积：确保关联条件有效，防止无匹配条件导致数据暴涨。六、综合面试真题（问答压轴）1.说一说你过往最成功的一次SQL优化案例，优化前后效果？参考答案（实战话术，可直接复用）：之前做订单业务时，有一个订单列表查询接口，线上响应很慢，高峰期超时严重。排查过程：通过慢日志抓取对应SQL，explain查看发现是全表扫描，type为all，同时查询包含orderbycreate_time，存在Usingfilesort，单表数据800万左右。问题根源：查询的条件、排序字段都没有联合索引，SQL写法还使用了select*，导致每次查询都扫描全表+文件排序。优化操作：1）重构SQL，按需查询字段，改成精准字段查询，实现覆盖索引；2）根据查询条件和排序规则，建立联合索引（状态、用户ID、创建时间），适配最左前缀；3）优化大分页逻辑，改成主键偏移分页；优化效果：优化前单次查询耗时1.5~3秒，高峰期可达5秒以上；优化后耗时稳定在10ms以内，彻底解决接口超