【大白话说Java面试题 第96题】【Mysql篇】第26题：日常工作中你是怎么优化 SQL 的？

PDF大白话说Java面试题 — 03-Mysql篇第26题日常工作中你是怎么优化 SQL 的回答核心考点大厂面试要求系统性地阐述SQL优化的完整流程发现问题→分析原因→制定方案→验证效果掌握常用优化手段并能结合实际案例说明。面试官常追问“慢查询怎么定位”、“EXPLAIN主要看哪些字段”、“索引建了为什么查询还是慢”1. SQL优化完整流程核心方法论定位 → 分析 → 优化 → 验证慢查询日志/业务反馈 → EXPLAIN分析执行计划 → 定位瓶颈 → 制定优化方案 → 上线验证 → 持续监控2. 第一步定位慢查询2.1 开启慢查询日志-- 查看慢查询配置SHOWVARIABLESLIKEslow_query_log%;SHOWVARIABLESLIKElong_query_time;-- 开启慢查询日志生产环境需谨慎SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;-- 超过1秒记录SETGLOBALlog_queries_not_using_indexesON;-- 记录未走索引的查询-- 慢查询日志文件位置SHOWVARIABLESLIKEslow_query_log_file;2.2 分析慢查询日志# 使用mysqldumpslow工具分析mysqldumpslow-st-t10/var/lib/mysql/slow.log# 输出按查询时间排序取前10条最慢的SQL2.3 性能监控指标指标命令说明慢查询数量SHOW STATUS LIKE Slow_queries累计慢查询数查询缓存命中率SHOW STATUS LIKE Qcache%缓存命中率低需优化临时表使用SHOW STATUS LIKE Created_tmp%大量临时表需优化索引使用情况SHOW STATUS LIKE Handler_read%判断索引效率3. 第二步EXPLAIN分析执行计划3.1 重点关注的字段字段好差说明typeconst/ref/rangeALL/index访问类型至少rangekey使用了索引NULL实际使用的索引rows小大接近表总行数估算扫描行数filtered高50%低10%过滤比例ExtraUsing indexUsing filesort/Using temporary额外信息3.2 典型问题识别-- 问题1全表扫描EXPLAINSELECT*FROMusersWHEREname张三;-- typeALL, keyNULL → 需要建索引-- 问题2文件排序EXPLAINSELECT*FROMusersORDERBYcreate_time;-- ExtraUsing filesort → 需要为create_time建索引-- 问题3临时表EXPLAINSELECTDISTINCTageFROMusersGROUPBYage;-- ExtraUsing temporary → 优化GROUP BY/DISTINCT-- 问题4索引失效EXPLAINSELECT*FROMusersWHEREYEAR(birthday)1990;-- typeALL, keyNULL → 函数导致索引失效4. 第三步常见优化手段4.1 索引优化最高优先级优化手段示例效果添加索引CREATE INDEX idx_name ON users(name)全表扫描→索引查找覆盖索引CREATE INDEX idx_covering ON users(name, age)避免回表联合索引顺序(user_id, create_time)等值在前范围在后索引最大化利用删除冗余索引idx_name和idx_name_age保留后者减少写开销前缀索引CREATE INDEX idx_content ON articles(content(20))减少索引大小实战案例-- 原SQL查询某用户最近10条订单SELECT*FROMordersWHEREuser_id123ORDERBYcreate_timeDESCLIMIT10;-- 原索引无 或 只有(user_id)-- 问题file sort-- 优化创建联合索引(user_id, create_time)CREATEINDEXidx_user_timeONorders(user_id,create_time);-- 效果typeref, ExtraUsing index condition无需filesort4.2 查询语句优化优化手段低效写法高效写法提升原因**避免SELECT ***SELECT * FROM usersSELECT id,name FROM users减少网络传输可能用覆盖索引深分页优化LIMIT 100000,10主键范围查询或覆盖索引JOIN避免扫描大量无用数据批量操作逐条INSERTINSERT INTO t VALUES (...), (...)减少网络往返避免函数WHERE YEAR(date)2024WHERE date BETWEEN 2024-01-01 AND 2024-12-31索引生效避免隐式转换WHERE phone13800138000WHERE phone13800138000索引生效用UNION ALL代替UNIONUNION去重UNION ALL不去重避免去重开销用EXISTS代替IN子查询数据量大时WHERE id IN (SELECT ...)WHERE EXISTS (SELECT 1 ...)减少结果集深分页优化详解-- 低效扫描100010行丢弃100000行SELECT*FROMordersORDERBYidLIMIT100000,10;-- 方案1主键范围查询id连续SELECT*FROMordersWHEREid100000ORDERBYidLIMIT10;-- 方案2覆盖索引JOIN通用SELECTo.*FROMorders oINNERJOIN(SELECTidFROMordersORDERBYidLIMIT100000,10)tmpONo.idtmp.id;4.3 表结构优化优化手段说明示例拆分大字段将不常查询的TEXT/BLOB分到扩展表用户主表用户详情表分区表按时间分区便于删除和查询历史数据PARTITION BY RANGE (YEAR(create_time))数据类型优化用INT代替VARCHAR、用NOT NULLstatus TINYINT代替status VARCHAR(20)合理使用冗余减少JOIN查询订单表冗余商品名称4.4 系统配置优化参数推荐值作用innodb_buffer_pool_size物理内存70-80%缓存数据和索引innodb_log_file_size1-2GBRedo日志大小innodb_flush_log_at_trx_commit1强一致/2高性能刷盘策略max_connections500-1000最大连接数query_cache_size0MySQL 8.0已移除MySQL 5.7可禁用4.5 应用层优化优化手段说明示例缓存Redis缓存热点数据用户会话、商品详情读写分离主库写从库读报表查询走从库异步处理消息队列削峰订单完成后发积分连接池HikariCP/Druid减少连接创建开销5. 实战案例分析案例1订单列表查询优化原始SQLSELECT*FROMordersWHEREuser_id123ORDERBYcreate_timeDESCLIMIT10;问题定位无索引typeALL全表扫描100万行ExtraUsing filesort需要额外排序优化方案-- 1. 创建联合索引CREATEINDEXidx_user_timeONorders(user_id,create_time);-- 2. 修改查询只查必要字段SELECTid,order_no,amount,create_timeFROMordersWHEREuser_id123ORDERBYcreate_timeDESCLIMIT10;效果扫描行数100万 → 86查询时间1.2秒 → 0.01秒ExtraUsing index condition无filesort案例2报表统计优化原始SQLSELECTDATE(create_time)ASdt,COUNT(*)FROMordersWHEREcreate_timeBETWEEN2024-01-01AND2024-12-31GROUPBYDATE(create_time);问题定位WHERE条件没有使用索引函数处理create_time全表扫描优化方案-- 1. 改写WHERE条件使用范围查询SELECTDATE(create_time)ASdt,COUNT(*)FROMordersWHEREcreate_time2024-01-01ANDcreate_time2025-01-01GROUPBYDATE(create_time);-- 2. 添加覆盖索引CREATEINDEXidx_timeONorders(create_time);-- 3. 考虑使用汇总表每日提前统计CREATETABLEorder_daily_stats(stat_dateDATEPRIMARYKEY,order_countINT);效果扫描行数500万 → 365索引扫描查询时间8秒 → 0.2秒6. 优化checklist面试速记类别检查项命令/方法慢查询是否开启慢查询日志slow_query_logON执行计划type是否为ALL/indexEXPLAIN索引是否使用覆盖索引ExtraUsing index索引是否有冗余索引pt-duplicate-key-checker查询是否SELECT *改为具体字段查询深分页是否优化避免大offset查询是否使用函数/隐式转换检查WHERE条件表结构字段类型是否合理INT vs VARCHAR表结构是否有分区需求大表按时间分区配置innodb_buffer_pool_size是否足够物理内存70-80%7. 面试官追问与高分回答Q1索引建了查询还是慢可能的原因A索引失效函数、隐式转换、不遵循最左前缀区分度低优化器选择全表扫描统计信息过期执行ANALYZE TABLE需要回表查询列不在索引中尝试覆盖索引数据倾斜某个值占比过高Q2如何验证优化效果A执行EXPLAIN对比优化前后type、rows、Extra执行SHOW PROFILE查看详细耗时压测对比QPS和RT上线后观察慢查询日志变化Q3联合索引顺序怎么确定A等值查询列放前面范围查询列放后面高区分度列放前面频繁查询列放前面考虑索引复用(a,b)可覆盖(a)Q4表数据量特别大亿级怎么优化A分区表按时间、按哈希分库分表ShardingSphere、MyCAT冷热数据分离历史数据归档使用列式存储ClickHouse做分析查询读写分离 缓存Q5优化过程中最常用的工具AEXPLAIN分析执行计划慢查询日志mysqldumpslow定位慢SQLSHOW PROFILE查看各阶段耗时pt-query-digest分析慢查询日志SHOW ENGINE INNODB STATUS查看锁信息performance_schema深度性能分析Q6优化SQL的原则是什么A减少扫描行数用索引、减少SELECT字段减少回表次数覆盖索引减少排序和临时表用索引排序减少网络传输批量操作、避免SELECT *面试官想要的满分总结SQL优化是一个系统性工作我的优化流程是定位 → 分析 → 优化 → 验证。第一步定位慢查询开启慢查询日志long_query_time1使用mysqldumpslow或pt-query-digest分析第二步EXPLAIN分析执行计划重点关注type至少range、key不为NULL、rows小、Extra无filesort/temporary识别全表扫描、索引失效、文件排序等问题第三步制定优化方案索引优化添加/优化索引、覆盖索引、联合索引顺序等值在前范围在后查询优化避免SELECT *、深分页优化、批量操作、避免函数/隐式转换表结构优化拆分大字段、分区表、合理数据类型配置优化innodb_buffer_pool_size70-80%内存应用层缓存、读写分离第四步验证效果重新EXPLAIN对比压测验证QPS/RT上线后监控慢查询日志一句话SQL优化的本质是减少扫描行数和减少回表次数核心工具是EXPLAIN核心手段是索引优化。觉得对您有帮助麻烦点点关注啦您的关注是我创作的最大动力~