第85篇SQL优化实战2026版系列导航《Java 100 天进阶之路》完整目录 |⬅️ 上一篇第84篇MySQL事务与锁 |➡️ 下一篇第86篇MyBatis核心原理待发布一、核心知识点慢查询日志开启、分析、阈值设置EXPLAIN 执行计划type、key、rows、Extra关键字段解读附 B 树图解索引优化覆盖索引、索引下推、最左前缀、三星索引原则分页优化深分页问题与解决方案延迟关联、键集分页、游标分页COUNT 优化COUNT(*)vsCOUNT(列)大数据量替代方案Redis、统计表JOIN 优化小表驱动大表索引连接字段避免笛卡尔积SQL 改写技巧OR转UNION、IN转EXISTS、避免函数操作MySQL 8.0 新特性窗口函数实战对比、通用表表达式CTE、降序索引、不可见索引二、通俗讲解1分钟开心学1. 慢查询日志——SQL 性能的“体检报告”慢查询日志记录执行时间超过阈值的 SQL是定位性能问题的第一把刀。生活类比就像公司给员工做体检查出那些“耗时过长”的体检项重点排查。2. EXPLAIN——SQL 的“CT 扫描仪”通过EXPLAIN可以看到 MySQL 如何执行你的 SQL有没有用索引、扫描了多少行、是否用了文件排序等。图解说明typeALL全表扫描就像把整本书从头到尾翻一遍。typeref通过索引快速定位就像查字典的目录页直接跳到对应页码。建议此处插入 EXPLAIN 执行流程图展示 BTree 索引查找过程直观对比ALL与ref的扫描路径差异3. 深分页——为什么LIMIT 100000,10越来越慢LIMIT 100000,10会先扫描前 100010 行再丢弃前 100000 行导致大量无效 I/O。生活类比从一本书的第 1000 页开始往后读 10 页但你不得不先翻过前 999 页。优化方式是先记住第 1000 页的页码主键直接跳过去。4. 三星索引原则来自《高性能 MySQL》一条查询使用的索引达到以下三个星级的越多性能越好一星索引将等值匹配的列放在最前面WHERE col value。二星索引的顺序与ORDER BY一致避免文件排序。三星索引包含SELECT中所有列覆盖索引避免回表。SQL优化实战必知的三星索引原则。三、实操代码案例 场景说明测试表订单表orders数据量 500 万。CREATETABLEorders(idbigintNOTNULLAUTO_INCREMENT,user_idintNOTNULL,order_novarchar(32)NOTNULL,amountdecimal(10,2)DEFAULTNULL,statustinyintDEFAULT0,create_timedatetimeDEFAULTCURRENT_TIMESTAMP,update_timedatetimeDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(id),KEYidx_user_id(user_id),KEYidx_create_time(create_time))ENGINEInnoDBDEFAULTCHARSETutf8mb4;3.1 慢查询日志配置与查看-- 查看慢查询日志状态SHOWVARIABLESLIKEslow_query_log%;SHOWVARIABLESLIKElong_query_time;-- 临时开启重启失效SETGLOBALslow_query_logON;SETGLOBALlong_query_time2;-- 超过2秒记录-- 永久配置修改 my.cnf[mysqld]slow_query_log1slow_query_log_file/var/log/mysql/slow.log long_query_time2log_queries_not_using_indexes1分析慢查询日志# 使用 mysqldumpslow 工具mysqldumpslow-st-t10/var/log/mysql/slow.log3.2 EXPLAIN 执行计划深度解读字段最优值说明typeconsteq_refrefrangeindexALLALL表示全表扫描必须优化key实际使用的索引名NULL表示未使用索引rows越小越好估算扫描的行数ExtraUsing index覆盖索引、Using index conditionICP避免Using filesort、Using temporary典型优化案例-- 待优化的 SQLSELECT*FROMordersWHEREorder_noORD123456;EXPLAINSELECT*FROMordersWHEREorder_noORD123456;-- type: ALLrows: 全表未使用索引-- 优化加索引ALTERTABLEordersADDINDEXidx_order_no(order_no);EXPLAINSELECT*FROMordersWHEREorder_noORD123456;-- 结果type: refrows: 1Extra: NULL3.3 MySQL 8.0 窗口函数实战场景查询每个用户的最新订单按create_time排序。-- 【旧写法】低效需要子查询聚合SELECTo1.*FROMorders o1WHEREo1.create_time(SELECTMAX(create_time)FROMorders o2WHEREo2.user_ido1.user_id);-- 性能差可能用到临时表-- 【新写法】窗口函数扫描一次即得WITHlatestAS(SELECT*,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYcreate_timeDESC)ASrnFROMorders)SELECT*FROMlatestWHERErn1;-- 性能更好逻辑清晰窗口函数优势扫描全表一次利用内存排序避免多次回表和子查询。3.4 深分页优化从原理到最佳实践1低效写法SELECT*FROMordersORDERBYidLIMIT100000,10;-- 扫描 100010 行丢弃 100000 行2延迟关联通用方案SELECT*FROMorders o1JOIN(SELECTidFROMordersORDERBYidLIMIT100000,10)o2ONo1.ido2.id;-- 重点子查询仅扫描主键走覆盖索引不回表3键集分页Keyset Pagination适合顺序翻页-- 仅适用于 id 自增且连续不能跳页SELECT*FROMordersWHEREid100000ORDERBYidLIMIT10;4游标分页Cursor-based适合 API 设计-- 将上一页最后一条记录的排序字段如 id 或 create_time编码为 Token 传参-- 例如客户端传来 last_id 100000SELECT*FROMordersWHEREid100000ORDERBYidLIMIT10;2026年趋势微服务接口推荐使用游标分页避免前端传参篡改 offset且性能恒定无论翻到第几页耗时都是 O(1)。3.5 COUNT 优化与大数据量替代方案-- 统计所有行推荐SELECTCOUNT(*)FROMorders;-- 走最小二级索引-- 统计 status1 的行数走索引ALTERTABLEordersADDINDEXidx_status(status);SELECTCOUNT(*)FROMordersWHEREstatus1;-- 走 idx_status⚠️ 避坑当单表数据量达到数亿级时COUNT(*)仍然可能很慢。解决方案方案A近似值使用SHOW TABLE STATUS获取近似行数误差可达 50%。方案B实时精确值使用 Redis 原子计数器INCR/DECR实时维护。方案C离线统计定期汇总到统计表如每天凌晨计算。3.6 JOIN 优化与“小表驱动大表”-- 低效大表驱动小表SELECT*FROMlarge_table lJOINsmall_table sONl.keys.key;-- 优化保证小表在前且连接字段有索引SELECT*FROMsmall_table sJOINlarge_table lONs.keyl.key;-- 同时 l.key 必须有索引⭐ 三星索引应用示例-- 查询SELECT user_id, order_no FROM orders-- WHERE user_id123 AND status1-- ORDER BY create_time LIMIT 10;-- 三星索引设计ALTERTABLEordersADDINDEXidx_user_status_time_no(user_id,status,create_time,order_no);-- 一星user_id, status 等值匹配 ✅-- 二星order by create_time 已在索引中 ✅-- 三星SELECT 中的 order_no 也在索引末尾完全覆盖无需回表 ✅3.7 SQL 改写技巧与 MySQL 8.0 新特性-- 1. OR 转 UNION避免索引合并失效SELECT*FROMordersWHEREuser_id123UNIONSELECT*FROMordersWHEREorder_noORD123;-- 2. IN 转 EXISTS子表较大时SELECT*FROMorders oWHEREEXISTS(SELECT1FROMusers uWHEREu.ido.user_idANDu.vip1);-- 3. 避免函数操作-- 错误WHERE DATE(create_time) 2026-01-01-- 正确WHERE create_time 2026-01-01 AND create_time 2026-01-02-- 4. 通用表表达式CTE提高可读性WITHvip_usersAS(SELECTidFROMusersWHEREvip1)SELECT*FROMordersWHEREuser_idIN(SELECTidFROMvip_users);四、避坑要点问题错误写法后果正确做法隐式类型转换WHERE order_no 123不走索引保证字段类型匹配前模糊匹配WHERE name LIKE %abc全表扫描改为后模糊或用 ES索引列参与运算WHERE id 1 100不走索引等号两边独立运算使用 OR 连接不同列WHERE a1 OR b2可能不走索引拆分为 UNIONSELECT *返回所有列包含不需要的浪费 I/O无法覆盖索引只查需要的列LIMIT 大偏移量LIMIT 100000,10深分页性能差延迟关联或游标COUNT(列) 统计行数COUNT(非索引列)慢用COUNT(*)JOIN 字段类型不一致varcharvsint索引失效统一字段类型MySQL 8.0 统计信息过期优化器选错索引ANALYZE TABLE更新定期执行五、面试高频考点2026版Q1如何定位慢 SQL开启慢查询日志设置long_query_time分析慢查询日志文件使用mysqldumpslow或 pt-query-digest。Q2EXPLAIN 的type列有哪些值从好到差排序systemconsteq_refrefrangeindexALL。ALL是全表扫描必须优化。Q3深分页的三种优化方案延迟关联通用、键集分页顺序翻页、游标分页API 设计。其中游标分页在 2026 年的微服务架构中最常用。Q4COUNT(*)在大数据量下的替代方案用 Redis 计数器、SHOW TABLE STATUS近似值、或定时统计表。Q5什么是“三星索引”一星等值匹配列在前二星索引顺序与 ORDER BY 一致三星覆盖索引。根据业务查询设计索引时尽可能满足更多星。Q6JOIN 优化原则小表驱动大表连接字段建索引避免SELECT *尽量用INNER JOIN而非LEFT JOIN业务允许时。Q7MySQL 8.0 有哪些新特性可用于 SQL 优化窗口函数替代复杂子查询、CTE提高可读性、降序索引、不可见索引安全测试、直方图优化器统计信息增强。六、练习题分析有一张 1000 万行日志表logs查询SELECT * FROM logs WHERE create_time 2026-01-01 AND type ERROR非常慢如何优化 思路建立联合索引(type, create_time)按等值查询在前、范围查询在后的原则若只查部分字段可建覆盖索引。改错SELECT * FROM user WHERE YEAR(birthday) 1990如何改写以使用索引 思路改为birthday BETWEEN 1990-01-01 AND 1990-12-31。代码模拟一个深分页查询使用延迟关联优化前后对比统计执行时间。 你的学习进度当前第85篇 / 共108篇 ·进阶篇数据库与持久层框架第83~90篇✅ 已完成基础篇44篇 第91~ 96篇Redis/MQ 第83~85篇 正在学第85篇⏳ 待学习第86~ 90篇MyBatis/JDBC等 第97~108篇 完整目录 学习指南 | 订阅本专栏不错过每一篇 本专栏每篇都包含避坑表 面试高频考点 练习题。每天30分钟100天拿offer 下一篇文章预告《第86篇MyBatis核心原理2026版》内容简介MyBatis 核心组件SqlSession、Executor、StatementHandler、动态 SQL 解析原理、一级/二级缓存机制、#{}vs${}区别、Spring 整合原理、源码阅读技巧。 学完这篇你将彻底理解 MyBatis 底层原理面试不再怕“MyBatis 缓存”问题。福利提醒评论区留言“SQL优化”可领取《SQL 优化实战清单与 EXPLAIN 速查表》PDF。《Java 100 天进阶之路 | 从入门到上岗就业》每天一篇建议收藏 关注一起100天拿offer 点击关注我更新后第一时间收到推送