📌 PDF：大白话说Java面试题 — 03-Mysql篇
第26题：日常工作中你是怎么优化 SQL 的
📚 回答：
- 核心考点： 大厂面试要求系统性地阐述SQL优化的完整流程（发现问题→分析原因→制定方案→验证效果），掌握常用优化手段，并能结合实际案例说明。面试官常追问：“慢查询怎么定位？”、“EXPLAIN主要看哪些字段？”、“索引建了为什么查询还是慢？”
1. SQL优化完整流程
核心方法论：定位 → 分析 → 优化 → 验证
慢查询日志/业务反馈 → EXPLAIN分析执行计划 → 定位瓶颈 → 制定优化方案 → 上线验证 → 持续监控
2. 第一步：定位慢查询
2.1 开启慢查询日志
— 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
— 开启慢查询日志（生产环境需谨慎）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; — 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON; — 记录未走索引的查询
— 慢查询日志文件位置
SHOW VARIABLES LIKE 'slow_query_log_file';
2.2 分析慢查询日志
# 使用mysqldumpslow工具分析
mysqldumpslow -s t -t 10 /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：全表扫描
EXPLAIN SELECT * FROM users WHERE name = '张三';
— type=ALL, key=NULL → 需要建索引
— 问题2：文件排序
EXPLAIN SELECT * FROM users ORDER BY create_time;
— Extra=Using filesort → 需要为create_time建索引
— 问题3：临时表
EXPLAIN SELECT DISTINCT age FROM users GROUP BY age;
— Extra=Using temporary → 优化GROUP BY/DISTINCT
— 问题4：索引失效
EXPLAIN SELECT * FROM users WHERE YEAR(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 * FROM orders WHERE user_id = 123 ORDER BY create_time DESC LIMIT 10;
— 原索引：无 或 只有(user_id)
— 问题：file sort
— 优化：创建联合索引(user_id, create_time)
CREATE INDEX idx_user_time ON orders(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 * FROM orders ORDER BY id LIMIT 100000, 10;
— 方案1：主键范围查询（id连续）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;
— 方案2：覆盖索引+JOIN（通用）
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders ORDER BY id LIMIT 100000, 10
) tmp ON o.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 * FROM orders
WHERE user_id = 123
ORDER BY create_time DESC
LIMIT 10;
问题定位：
- 无索引，type=ALL，全表扫描100万行
- Extra=Using filesort，需要额外排序
优化方案：
— 1. 创建联合索引
CREATE INDEX idx_user_time ON orders(user_id, create_time);
— 2. 修改查询（只查必要字段）
SELECT id, order_no, amount, create_time FROM orders
WHERE user_id = 123
ORDER BY create_time DESC
LIMIT 10;
效果：
- 扫描行数：100万 → 86
- 查询时间：1.2秒 → 0.01秒
- Extra：Using index condition（无filesort）
案例2：报表统计优化
原始SQL：
SELECT DATE(create_time) AS dt, COUNT(*)
FROM orders
WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY DATE(create_time);
问题定位：
- WHERE条件没有使用索引（函数处理create_time）
- 全表扫描
优化方案：
— 1. 改写WHERE条件，使用范围查询
SELECT DATE(create_time) AS dt, COUNT(*)
FROM orders
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'
GROUP BY DATE(create_time);
— 2. 添加覆盖索引
CREATE INDEX idx_time ON orders(create_time);
— 3. 考虑使用汇总表（每日提前统计）
CREATE TABLE order_daily_stats (
stat_date DATE PRIMARY KEY,
order_count INT
);
效果：
- 扫描行数：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：
Q3：联合索引顺序怎么确定？
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：
💡 面试官想要的满分总结：
"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%内存）
- 应用层：缓存、读写分离
第四步：验证效果
- 重新EXPLAIN对比
- 压测验证QPS/RT
- 上线后监控慢查询日志
一句话：SQL优化的本质是减少扫描行数和减少回表次数，核心工具是EXPLAIN，核心手段是索引优化。"
觉得对您有帮助，麻烦点点关注啦，您的关注是我创作的最大动力~ 🎯
