SQL语句优化面试题及答案（实战版）

SQL语句优化面试题及答案（实战版）一、基础必考题（初级开发）1.日常开发中，最常见的SQL慢查询原因有哪些？参考答案：1）索引失效：最核心原因，比如like%前缀匹配、or关联无索引字段、隐式类型转换、in/notin大范围数据、orderby字段无索引等；2）查询冗余：select*查询所有字段、返回大量无用数据、未加limit限制结果集；3）关联查询不合理：多表join关联字段无索引、笛卡尔积、不必要的表关联；4）条件写法问题：where条件不精准、对索引字段做函数运算、!=/<>判断导致索引失效；5）数据及表结构问题：单表数据量过大无分表、字段类型不合理、大字段频繁查询；6）数据库配置问题：连接池、缓冲区、超时参数配置不当。2.说说select*为什么不推荐使用？参考答案：1）浪费IO和网络资源：会查询所有字段，包含大文本、长字符串等无用字段，增加磁盘读取和网络传输开销；2）无法使用覆盖索引：select指定字段可触发覆盖索引，无需回表，select*必然回表，查询效率大幅降低；3）维护风险高：后续表新增字段后，查询结果会被动变化，可能导致程序报错、数据解析异常；4）增加数据库内存开销：结果集越大，数据库缓存、临时内存占用越高，容易引发慢查询。3.where、groupby、orderby、having的执行顺序？为什么having尽量少用？参考答案：1）执行顺序：where>groupby>聚合函数>having>orderby>limit2）having缺点：是分组后再过滤数据，需要先完成全量分组、聚合运算，再筛选结果；而where是分组前过滤，提前减少数据量。实操优化：能在where过滤的条件，绝不放到having中，大幅减少分组聚合的数据量。4.limit1000000,10为什么查询极慢？怎么优化？参考答案：1）慢的原因：MySQL分页偏移量过大时，会先查询并扫描前100万条数据，丢弃后再返回后10条，大量数据扫描、回表、排序操作造成性能极差。2）优化方案：-主键自增有序场景：使用whereid>偏移idlimit10替代偏移分页；-大数据分页：采用书签分页、游标分页，禁止大偏移量分页；-业务层面规避：后台分页不支持超大页查询，限制最大页码。二、索引优化核心题（中高级高频）5.哪些场景会导致索引失效？（面试必考核心）参考答案：1）索引字段使用函数运算：left()、substr()、date_format()、加减乘除等；2）模糊查询左匹配：like'%xxx'、like'%xxx%'，仅like'xxx%'可走索引；3）隐式类型转换：字符串字段传数字、数字字段传字符串，MySQL自动转换导致索引失效；4）使用!=、<>、notin、notexists非空判断（大量数据下失效）；5）or连接无索引字段：or前后字段必须都有索引，否则全表扫描；6）联合索引不满足最左前缀原则；7）MySQL优化器判断全表扫描更快（数据量少、索引选择性差），主动放弃索引。6.什么是联合索引最左前缀原则？如何设计联合索引？参考答案：1）最左前缀：联合索引(a,b,c)，查询条件必须包含最左侧的a，才能触发索引；仅查b、仅查c、查b+c均无法走索引。2）联合索引设计原则：-高频查询字段放最左；-等值条件放前面，范围条件（>、<、between、like）放后面；-区分度高的字段优先，区分度低的字段后置；-避免冗余索引，单值索引和联合索引不要重复创建。7.覆盖索引是什么？有什么优化优势？参考答案：1）定义：查询所需的所有字段，全部包含在索引中，MySQL无需回表查询主键数据，直接通过索引返回结果。2）优势：避免回表操作，减少磁盘IO，极大提升查询速度；是日常查询优化性价比最高的方式。3）示例：建立索引(phone,username)，查询selectusernamefromuserwherephone='xxx'直接走覆盖索引。8.in和exists怎么选？有什么区别？参考答案：核心原则：小表驱动大表1）子查询数据量小、主表数据量大：用in；2）子查询数据量大、主表数据量小：用exists；3）原理：in是先查子表，结果集缓存后匹配主表；exists是遍历主表，逐行匹配子表是否存在数据。补充：MySQL8.0后优化器已自动优化，区别不大，但老旧项目、大数据量场景仍需手动区分。三、实战场景优化题（面试压轴）9.业务SQL：select*fromorderwherecreate_time>'2025-01-01'andstatus=1orderbycreate_timedesc很慢，如何优化？参考答案：1）问题分析：大概率是无合适索引，导致查询、排序全表扫描；单独建单索引会出现查询走索引、排序回表文件排序，效率低。2）优化方案：-建立联合索引：(status,create_time)，等值在前、范围在后，符合最左前缀；-替换select*为业务所需字段，开启覆盖索引，避免回表；-增加limit限制，避免返回全量大数据集；-超大数据量可按月分表，减少单表扫描范围。10.多表联查很慢，如何一步步排查优化？参考答案：1）先执行explain分析执行计划，查看是否全表扫描、是否Usingfilesort、Usingtemporary；2）检查所有join关联字段是否建立索引，无索引优先建索引；3）优先小表关联大表，避免笛卡尔积，剔除不必要的关联表；4）where条件提前过滤数据，减少关联前的数据量；5）禁止关联后大范围排序分组，尽量索引排序；6）避免join过多表，超过3张表优先拆分SQL、业务层组装数据。11.为什么不建议在数据库中做大量计算、字符串处理？参考答案：1）数据库核心职责是存储、查询数据，计算、字符串切割、格式化等操作极其消耗CPU；2）函数操作会导致索引失效，引发全表扫描；3）数据库资源昂贵、不易扩容，应用服务器资源廉价、可横向扩容；优化原则：能在业务层处理的计算，绝不丢给数据库。12.explain执行计划中，常见的最差参数有哪些？代表什么问题？参考答案：1）type=ALL：全表扫描，最严重的性能问题，必须优化索引；2）Usingfilesort：文件排序，未走索引排序，需要优化orderby索引；3）Usingtemporary：创建临时表，多出现于groupby、distinct，数据量大极易卡顿；4）rows数值极大：扫描行数过多，索引选择性差或条件过滤无效。四、高阶避坑题（资深开发/架构）13.索引越多越好吗？为什么？参考答案：不是，索引是查询快、写入慢。1）查询场景：索引提升检索效率；2）写入场景：新增、修改、删除数据时，需要同步更新所有索引，索引越多，写入开销越大、锁等待越久；3）索引会占用磁盘空间，过多索引会增加数据库存储压力。原则：高频查询字段建索引，低频查询、高频更新字段不建索引，杜绝冗余索引。14.distinct优化思路？参考答案：1）distinct本质是分组去重，会生成临时表，大数据量极慢；2）优化：优先通过业务逻辑去重，提前过滤重复数据；3）给去重字段建立索引，让索引辅助去重，避免全表比对；4）禁止多字段大范围distinct，可拆分SQL、业务层去重。15.大