sql优化思维

文章目录前言一、如何锻炼这种优化思维核心原则就一句话三个灵魂拷问一个具体的锻炼方法二、需要了解业务吗这个优化中需要知道的业务知识不需要知道的结论三、用什么工具测试慢 SQL工具 1MySQL 自带 — EXPLAIN最常用工具 2查看实际执行时间工具 3DBeaver 自带的功能工具 4慢查询日志生产环境四、实战从 0 到优化的完整步骤五、一句话速成心法前言三个层面思维方法、业务理解、工具使用。一、如何锻炼这种优化思维核心原则就一句话能不查的就不查能少查的就少查能提前过滤就提前过滤。对应回这个优化优化前 查所有流程 → 行转列 → 再过滤先查10万行再扔掉9.9万行 优化后 先圈出我的流程 → 只查这100个流程的变量只查100行三个灵魂拷问每次写/改 SQL 时问自己拷问对应优化原 SQL 的问题① 我需要查哪些行用 WHERE 先过滤没有先过滤全部查完再说② 同样的数据查了几遍用 CTE 只查 1 次行转列重复了 8 遍③ 能不能先缩小范围再做复杂计算先查 ID 再查详情直接对全表做行转列一个具体的锻炼方法拿到慢 SQL先画出数据流向图输入act_hi_varinst10万行 │ ├─ 子查询1→ 行转列 →10万行 ├─ 子查询2→ 行转列 →10万行 ← 重复 ├─ 子查询3→ 行转列 →10万行 ← 重复 │ └─ 最后过滤WHEREBELONGsrm→ 还剩1万行90%白查了看到这个图问题一目了然重复→ 用 CTE查太多→ 先圈定范围二、需要了解业务吗需要但不需要很深。这个优化中需要知道的业务知识知识点为什么需要不知道会怎样每个用户只看到自己的流程才能用WHERE assignee当前用户缩小范围不敢加这个条件BELONG‘srm’ 只筛选出 SRM 模块提前过滤不相关数据过滤时机放错了审批人有8种状态才知道 UNION ALL 有8个分支各是什么含义不敢合并/不敢改不需要知道的知识点不需要知道审批流程的具体业务规则不影响 SQL 优化每种审批状态的业务含义只要知道查询条件就行前端怎么展示这些数据不影响 SQL 优化结论您只需要知道这个 SQL 是用来查什么的查的是谁的数据这个程度就够了。比如这个 SQL您只需要知道查的是当前登录用户的待办/已办列表每个用户只能看到自己的知道这两点就足够推理出应该先按用户 ID 过滤再查其他数据。三、用什么工具测试慢 SQL工具 1MySQL 自带 — EXPLAIN最常用-- 在 SQL 前面加 EXPLAINEXPLAINSELECT...FROM...输出解读列含义好坏type访问方式const,ref,rangeALL全表扫描rows预估扫描行数几百几万Extra额外信息Using indexUsing temporary,Using filesort您的项目实战-- 优化前看 rowsEXPLAINSELECT...FROMact_hi_varinst...-- rows: 100000 ← 全表扫描-- type: ALL ← 没有索引-- 优化后看 rowsEXPLAINWITHuser_proc_instAS(...)...-- rows: 100 ← 只查100行-- type: range ← 走索引工具 2查看实际执行时间-- MySQL 开启执行时间SETprofiling1;-- 执行你的 SQLSELECT...;-- 查看执行时间SHOWprofiles;-- 输出Duration: 3.2 sec ← 3.2秒慢工具 3DBeaver 自带的功能DBeaver 中选中 SQL → 右键 → 选“Explain Plan”执行计划Operation|Object|Cost|Rows|-----------------------------|-------------------|-------|-------|TABLESCAN(FULL)|act_hi_varinst|10000|100000|GROUPBY||||NESTEDLOOPJOIN||||直接看出全表扫描和预估行数。工具 4慢查询日志生产环境-- 查看慢查询是否开启SHOWVARIABLESLIKEslow_query%;-- 设置慢查询阈值超过1秒的记录SETlong_query_time1;-- 查看慢查询日志文件路径SHOWVARIABLESLIKEslow_query_log_file;四、实战从 0 到优化的完整步骤假设您现在接手一个慢 SQL第1步找到慢SQL└─ DBeaver 执行时转圈圈 → 卡了几秒 └─ 或从慢查询日志捞出来 第2步EXPLAIN分析 └─ 看到 typeALL全表扫描 └─ 看到 rows100000└─ 看到 ExtraUsing temporary用了临时表 第3步画数据流向图 └─ 输入10万行 → 子查询重复8次 → 最后才过滤 第4步问三个问题 ① 需要查哪些行 → 只查当前用户的 ② 查了几遍 →8遍 ③ 能先缩小吗 → 能先查用户ID第5步改写 └─ 用CTE先圈范围 第6步对比验证 └─EXPLAIN看 rows 从10万 →100└─ 实际执行从3秒 →200毫秒五、一句话速成心法遇到慢 SQL先问能不能提前干掉不需要的数据再问同样的东西查了几遍。就像打扫房间先扔掉垃圾不需要的过滤掉再收拾剩下的对需要的数据做计算而不是先全部摆出来再分类全表扫描后再过滤这个项目里的优化本质上就是把先全部查出来再说改成了先确定要查什么再去查。