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

📌PDF：大白话说Java面试题 — 03-Mysql篇

第26题：日常工作中你是怎么优化 SQL 的

📚回答：

核心考点：
大厂面试要求系统性地阐述SQL优化的完整流程（发现问题→分析原因→制定方案→验证效果），掌握常用优化手段，并能结合实际案例说明。面试官常追问：“慢查询怎么定位？”、“EXPLAIN主要看哪些字段？”、“索引建了为什么查询还是慢？”

1. SQL优化完整流程

核心方法论：定位 → 分析 → 优化 → 验证

慢查询日志/业务反馈 → EXPLAIN分析执行计划 → 定位瓶颈 → 制定优化方案 → 上线验证 → 持续监控

2. 第一步：定位慢查询

2.1 开启慢查询日志

-- 查看慢查询配置SHOWVARIABLESLIKE'slow_query_log%';SHOWVARIABLESLIKE'long_query_time';-- 开启慢查询日志（生产环境需谨慎）SETGLOBALslow_query_log=ON;SETGLOBALlong_query_time=1;-- 超过1秒记录SETGLOBALlog_queries_not_using_indexes=ON;-- 记录未走索引的查询-- 慢查询日志文件位置SHOWVARIABLESLIKE'slow_query_log_file';

2.2 分析慢查询日志

# 使用mysqldumpslow工具分析mysqldumpslow-st-t10/var/lib/mysql/slow.log# 输出：按查询时间排序，取前10条最慢的SQL

2.3 性能监控指标

指标	命令	说明
慢查询数量	`SHOW STATUS LIKE 'Slow_queries'`	累计慢查询数
查询缓存命中率	`SHOW STATUS LIKE 'Qcache%'`	缓存命中率低需优化
临时表使用	`SHOW STATUS LIKE 'Created_tmp%'`	大量临时表需优化
索引使用情况	`SHOW STATUS LIKE 'Handler_read%'`	判断索引效率

3. 第二步：EXPLAIN分析执行计划

3.1 重点关注的字段

字段	好	差	说明
type	`const`/`ref`/`range`	`ALL`/`index`	访问类型，至少range
key	使用了索引	`NULL`	实际使用的索引
rows	小	大（接近表总行数）	估算扫描行数
filtered	高（>50%）	低（<10%）	过滤比例
Extra	`Using index`	`Using filesort`/`Using temporary`	额外信息

3.2 典型问题识别

-- 问题1：全表扫描EXPLAINSELECT*FROMusersWHEREname='张三';-- type=ALL, key=NULL → 需要建索引-- 问题2：文件排序EXPLAINSELECT*FROMusersORDERBYcreate_time;-- Extra=Using filesort → 需要为create_time建索引-- 问题3：临时表EXPLAINSELECTDISTINCTageFROMusersGROUPBYage;-- Extra=Using temporary → 优化GROUP BY/DISTINCT-- 问题4：索引失效EXPLAINSELECT*FROMusersWHEREYEAR(birthday)=1990;-- type=ALL, key=NULL → 函数导致索引失效

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_id=123ORDERBYcreate_timeDESCLIMIT10;-- 原索引：无 或 只有(user_id)-- 问题：file sort-- 优化：创建联合索引(user_id, create_time)CREATEINDEXidx_user_timeONorders(user_id,create_time);-- 效果：type=ref, Extra=Using index condition（无需filesort）

4.2 查询语句优化

优化手段	低效写法	高效写法	提升原因
避免SELECT *	`SELECT * FROM users`	`SELECT id,name FROM users`	减少网络传输，可能用覆盖索引
深分页优化	`LIMIT 100000,10`	主键范围查询或覆盖索引+JOIN	避免扫描大量无用数据
批量操作	逐条INSERT	`INSERT INTO t VALUES (...), (...)`	减少网络往返
避免函数	`WHERE YEAR(date)=2024`	`WHERE date BETWEEN '2024-01-01' AND '2024-12-31'`	索引生效
避免隐式转换	`WHERE phone=13800138000`	`WHERE phone='13800138000'`	索引生效
用UNION ALL代替UNION	`UNION`（去重）	`UNION ALL`（不去重）	避免去重开销
用EXISTS代替IN（子查询数据量大时）	`WHERE id IN (SELECT ...)`	`WHERE EXISTS (SELECT 1 ...)`	减少结果集

深分页优化详解：

-- 低效：扫描100010行，丢弃100000行SELECT*FROMordersORDERBYidLIMIT100000,10;-- 方案1：主键范围查询（id连续）SELECT*FROMordersWHEREid>100000ORDERBYidLIMIT10;-- 方案2：覆盖索引+JOIN（通用）SELECTo.*FROMorders oINNERJOIN(SELECTidFROMordersORDERBYidLIMIT100000,10)tmpONo.id=tmp.id;

4.3 表结构优化

优化手段	说明	示例
拆分大字段	将不常查询的TEXT/BLOB分到扩展表	用户主表+用户详情表
分区表	按时间分区，便于删除和查询历史数据	`PARTITION BY RANGE (YEAR(create_time))`
数据类型优化	用INT代替VARCHAR、用NOT NULL	`status TINYINT`代替`status VARCHAR(20)`
合理使用冗余	减少JOIN查询	订单表冗余商品名称

4.4 系统配置优化

参数	推荐值	作用
`innodb_buffer_pool_size`	物理内存70-80%	缓存数据和索引
`innodb_log_file_size`	1-2GB	Redo日志大小
`innodb_flush_log_at_trx_commit`	1（强一致）/2（高性能）	刷盘策略
`max_connections`	500-1000	最大连接数
`query_cache_size`	0（MySQL 8.0已移除）	MySQL 5.7可禁用

4.5 应用层优化

优化手段	说明	示例
缓存	Redis缓存热点数据	用户会话、商品详情
读写分离	主库写，从库读	报表查询走从库
异步处理	消息队列削峰	订单完成后发积分
连接池	HikariCP/Druid	减少连接创建开销

5. 实战案例分析

案例1：订单列表查询优化

原始SQL：

SELECT*FROMordersWHEREuser_id=123ORDERBYcreate_timeDESCLIMIT10;

问题定位：

无索引，type=ALL，全表扫描100万行
Extra=Using filesort，需要额外排序

优化方案：

-- 1. 创建联合索引CREATEINDEXidx_user_timeONorders(user_id,create_time);-- 2. 修改查询（只查必要字段）SELECTid,order_no,amount,create_timeFROMordersWHEREuser_id=123ORDERBYcreate_timeDESCLIMIT10;

效果：

扫描行数：100万 → 86
查询时间：1.2秒 → 0.01秒
Extra：Using index condition（无filesort）

案例2：报表统计优化

原始SQL：

SELECTDATE(create_time)ASdt,COUNT(*)FROMordersWHEREcreate_timeBETWEEN'2024-01-01'AND'2024-12-31'GROUPBYDATE(create_time);

问题定位：

WHERE条件没有使用索引（函数处理create_time）
全表扫描

优化方案：

-- 1. 改写WHERE条件，使用范围查询SELECTDATE(create_time)ASdt,COUNT(*)FROMordersWHEREcreate_time>='2024-01-01'ANDcreate_time<'2025-01-01'GROUPBYDATE(create_time);-- 2. 添加覆盖索引CREATEINDEXidx_timeONorders(create_time);-- 3. 考虑使用汇总表（每日提前统计）CREATETABLEorder_daily_stats(stat_dateDATEPRIMARYKEY,order_countINT);

效果：

扫描行数：500万 → 365（索引扫描）
查询时间：8秒 → 0.2秒

6. 优化checklist（面试速记）

类别	检查项	命令/方法
慢查询	是否开启慢查询日志	`slow_query_log=ON`
执行计划	`type`是否为ALL/index	`EXPLAIN`
索引	是否使用覆盖索引	`Extra=Using 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：优化过程中最常用的工具？

A：

EXPLAIN：分析执行计划
慢查询日志+mysqldumpslow：定位慢SQL
SHOW PROFILE：查看各阶段耗时
pt-query-digest：分析慢查询日志
SHOW ENGINE INNODB STATUS：查看锁信息
performance_schema：深度性能分析

Q6：优化SQL的原则是什么？

A：

减少扫描行数（用索引、减少SELECT字段）
减少回表次数（覆盖索引）
减少排序和临时表（用索引排序）
减少网络传输（批量操作、避免SELECT *）

💡面试官想要的满分总结：

"SQL优化是一个系统性工作，我的优化流程是：定位 → 分析 → 优化 → 验证。

第一步：定位慢查询
开启慢查询日志（long_query_time=1）
使用mysqldumpslow或pt-query-digest分析

第二步：EXPLAIN分析执行计划
重点关注：type（至少range）、key（不为NULL）、rows（小）、Extra（无filesort/temporary）
识别全表扫描、索引失效、文件排序等问题

第三步：制定优化方案
索引优化：添加/优化索引、覆盖索引、联合索引顺序（等值在前，范围在后）
查询优化：避免SELECT *、深分页优化、批量操作、避免函数/隐式转换
表结构优化：拆分大字段、分区表、合理数据类型
配置优化：innodb_buffer_pool_size（70-80%内存）
应用层：缓存、读写分离