慢SQL优化实战教学

文章目录一、先搞懂什么样的SQL才算「慢SQL」1.1 生产慢SQL判定标准通用标准二、慢SQL第一步永远先定位再优化别瞎改SQL2.1 必用神器explain 执行计划2.2 explain 只要看3个核心字段新手够用① type 字段最关键② key 字段③ rows 字段三、核心根源99%慢SQL就这几大原因四、索引优化优化慢SQL最见效、提升最快4.1 哪些字段必须建索引4.2 索引最左匹配原则必记4.3 索引失效十大坑日常开发最容易踩五、SQL写法规范优化改完立马提速5.1 永远禁止 select *5.2 小表驱动大表join别乱连5.3 尽量避免子查询改成join5.4 limit 深度分页一定要优化六、业务层面优化最高级、最有效6.1 大查询、统计、报表别查主库6.2 热点数据、高频查询全部放Redis6.3 大表一定要分库分表七、生产慢SQL优化标准流程照着这个步骤排查八、最终总结一句话一、先搞懂什么样的SQL才算「慢SQL」不是报错的SQL才叫有问题执行时间长、扫描行数多、返回数据少就是慢SQL。1.1 生产慢SQL判定标准通用标准单条SQL执行时间超过1秒算轻微慢查询单条SQL执行时间超过3秒线上必须优化扫描行数几十万、上百万结果只返回几十条典型烂SQL必优化。核心本质扫描的数据太多真正用到的数据太少全是无效IO、无效计算。二、慢SQL第一步永远先定位再优化别瞎改SQL优化慢SQL千万别上来就改代码。不改explain的优化都是瞎优化。2.1 必用神器explain 执行计划任何慢SQL前面加个explain一眼看出问题在哪。explainSELECT*FROMuserWHEREname张三;2.2 explain 只要看3个核心字段新手够用① type 字段最关键查询类型好坏级别system gt; const gt; eq_ref gt; ref gt; range gt; index gt; ALL生产红线绝对不能出现 ALLALL就是全表扫描数据一多必慢。② key 字段真正用到的索引。key为null 没走索引直接全表扫描。③ rows 字段扫描了多少行。扫描行数越大SQL越慢。优化核心目标type别为ALLkey要有索引rows尽量小。三、核心根源99%慢SQL就这几大原因没加索引查询字段、条件字段没建索引全表扫描索引失效建了索引写法不对索引作废select * 乱查查一堆用不着的字段不走覆盖索引大量回表join、子查询乱用大表连大表笛卡尔积爆炸分页深度过大limit 100000,10 经典大坑业务逻辑不合理数据库干了不该干的事排序统计全压给SQL。四、索引优化优化慢SQL最见效、提升最快4.1 哪些字段必须建索引一句话WHERE、JOIN、ORDER BY、GROUP BY 后面的字段必须建索引。4.2 索引最左匹配原则必记联合索引\(a,b,c\)查 where a? 走索引查 where a? and b? 走索引只查 b? 或 c? 索引直接失效。索引带头大哥不能丢丢了就不走索引。4.3 索引失效十大坑日常开发最容易踩只要踩中索引直接作废全表扫描select * 不用覆盖索引索引字段做运算、函数where age\120隐式类型转换字符串字段用数字查询like %xxx 左模糊查询or 两边字段不同时加索引not in、not exists 反查逻辑order by 字段没在索引最后数据量太少、mysql觉得走索引不如全表快隔离级别、锁冲突导致索引优化失效长时间长事务不提交索引缓存异常。五、SQL写法规范优化改完立马提速5.1 永远禁止 select *只查需要的字段能走覆盖索引就不用回表查询数据性能直接差十倍起步。很多人习惯随手写SELECT *看似省事实则把没必要的大字段备注、文本、创建时间等全部查出来不仅增加磁盘IO、网络传输还直接导致索引覆盖失效必须回表捞数据越查越慢。-- 烂写法查所有字段不走覆盖索引必然回表性能极差SELECT*FROMorderWHEREuser_id1001;-- 优化写法只查业务需要字段命中覆盖索引无需回表速度飞快SELECTid,order_no,pay_price,create_timeFROMorderWHEREuser_id1001;-- 垃圾SELECT*FROMorderWHEREuser_id1001;-- 推荐SELECTid,order_no,priceFROMorderWHEREuser_id1001;5.2 小表驱动大表join别乱连join 两张表小表放左边大表放右边减少循环匹配次数。禁止大表连大表禁止三张以上表join关联越多匹配逻辑越复杂临时表开销越大。核心原则数据量小的小表当驱动表先循环小表数据量大的大表当被驱动表只做精准匹配减少整体循环匹配次数JOIN性能直接翻倍。-- 烂写法大表驱动小表循环次数多匹配效率低SELECT*FROMbig_order_table bLEFTJOINsmall_user_table sONb.user_ids.id;-- 优化写法小表驱动大表精准匹配减少循环扫描SELECTo.id,o.order_no,s.usernameFROMsmall_user_table sLEFTJOINbig_order_table oONs.ido.user_idWHEREs.id1001;5.3 尽量避免子查询改成join子查询运行时会自动生成临时表临时表没有索引查询匹配全靠遍历数据量大了巨慢。生产实战里能用JOIN关联查询一律不用子查询。-- 烂写法使用子查询生成无索引临时表查询低效SELECT*FROMorderWHEREuser_idIN(SELECTidFROMuserWHERElevel1);-- 优化写法改成JOIN关联查询直接走已有索引无临时表开销SELECTo.id,o.order_noFROMorderoINNERJOINuseruONo.user_idu.idWHEREu.level1;5.4 limit 深度分页一定要优化limit 100000,10数据库要先扫10万行再丢弃巨慢。优化核心方案先靠主键索引做快速分页定位再关联查询其他字段避免扫描大量无效数据深度分页性能提升几十倍。-- 烂写法深度分页先扫10万行再丢弃超级慢SELECT*FROMorderORDERBYidLIMIT100000,10;-- 优化写法主键索引快速定位再关联查询无需扫描无效数据SELECTo.id,o.order_no,o.pay_priceFROMorderoINNERJOIN(SELECTidFROMorderORDERBYidLIMIT100000,10)tempONo.idtemp.idORDERBYo.id;六、业务层面优化最高级、最有效很多慢SQLSQL本身没问题业务设计有问题。6.1 大查询、统计、报表别查主库主库只负责日常业务新增、修改、删除核心读写操作压力必须稳住统计报表、后台数据导出、历史大数据分析这类慢查询、大扫描SQL全部迁移到从库执行绝不碰主库避免拖垮核心业务。-- 烂写法主库执行统计报表大SQL扫描海量数据压垮主库SELECTid,COUNT(*)ASorder_count,SUM(pay_price)AStotal_moneyFROMorderGROUPBYuser_id;优化操作同样的统计SQL代码里直接配置连接从库数据源执行SQL不变压力隔离主库零影响。6.2 热点数据、高频查询全部放Redis首页数据、个人信息、配置参数、秒杀库存不要每次查数据库。高频访问、变动少的热点数据提前缓存到Redis接口查询优先读缓存压根不执行SQL从根源杜绝慢查询。-- 烂写法每次接口请求都查数据库高频并发下数据库压力巨大SELECTid,username,phone,avatarFROMuserWHEREid1001;优化操作用户信息首次查询后存入Redis后续请求直接读Redis不再执行这条查询SQL定时异步更新缓存数据即可。6.3 大表一定要分库分表单表数据量达到千万级别以上不管怎么优化索引、改SQL写法查询性能都会有物理瓶颈读写压力天生扛不住必须做分库分表拆分大表为多个小表从架构上解决慢查询问题。-- 烂写法上亿数据单表查询再怎么加索引范围查询、统计依旧慢SELECT*FROMbig_order_dataWHEREcreate_timeBETWEEN2026-01-01AND2026-05-01;优化操作按时间或用户ID做分片分表查询只路由到对应小分片表扫描数据量骤减SQL无需复杂优化自然变快。七、生产慢SQL优化标准流程照着这个步骤排查拿到慢SQL日志找到执行时间最长、扫描行数最多的SQLexplain 分析执行计划看是不是全表扫描检查是否缺索引或者索引失效修改SQL写法避免索引失效语法优化select字段、join、分页逻辑大查询迁移从库热点数据加缓存超大表考虑分库分表。八、最终总结一句话慢SQL优化核心就三件事别走全表扫描、索引别失效、数据库只干核心读写别让它干统计和查询杂活。