SQL优化实战从Explain执行计划到千万级数据查询调优全解析慢查询拖垮整个系统一个Explain就能定位90%的性能瓶颈。在实际开发中我们每天都在和数据库打交道但真正懂SQL调优的人却少之又少。很多人遇到查询慢的第一反应就是加索引结果越加越慢系统反而更卡。今天这篇文章我会用真实案例带你走一遍从发现问题、分析问题到解决问题的完整流程看完之后你会发现SQL优化并没有想象中那么玄乎。一、SQL优化为什么这么重要在互联网公司里数据库几乎是所有业务的核心引擎。订单系统、用户系统、日志系统全部依赖数据库来读写数据。一旦某条SQL写得不好轻则接口响应变慢重则直接把数据库拖死造成全线服务不可用。我之前经历过一次线上事故一条统计报表的SQL在凌晨跑批时把主库CPU干到了100%导致订单创建接口全部超时持续了将近20分钟。后来排查发现就是一个简单的多表关联查询少写了一个过滤条件导致全表扫描了一张800万行的表。所以说SQL优化不是锦上添花的技能而是每个后端开发必须掌握的基本功。二、ExplainSQL优化的第一把钥匙想要优化SQL首先得学会看病。而MySQL提供的Explain命令就是最好的诊断工具。很多人写了几年SQL从来没用过Explain这是非常可惜的。我们先来看一个简单的案例。假设有一张用户订单表orders结构如下字段名 类型 说明id bigint 主键user_id bigint 用户IDstatus tinyint 订单状态create_time datetime 创建时间amount decimal 订单金额现在有一条查询SQLsqlSELECT * FROM orders WHERE user_id 1001 AND status 1;我们用Explain看一下它的执行计划---------------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |---------------------------------------------------------------------------------------| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 5000 | Using where |---------------------------------------------------------------------------------------☆ 从这张结果中我们能读出几个关键信息1、type列显示为ALL说明这条SQL进行了全表扫描这是性能最差的情况。2、possible_keys和key都为NULL说明没有用到任何索引。3、rows显示为5000意味着MySQL需要逐行扫描5000条记录才能找到结果。这个结果一目了然这条SQL没有走索引需要优化。三、索引策略不是建得越多越好很多开发人员有一个误区觉得索引建得越多查询就越快。其实恰恰相反索引是一把双刃剑。☆ 索引的优点很明显1、大幅提升查询速度特别是范围查询和排序操作。2、能够有效减少需要扫描的数据行数。☆ 但索引也有代价1、每张表上的每个索引都会占用磁盘空间索引越多占用越大。2、写入操作INSERT、UPDATE、DELETE会变慢因为每次写数据都要同步更新索引树。3、过多的索引会让MySQL优化器在选择执行计划时产生困惑反而可能选错索引。回到刚才的例子我们给user_id和status分别加上索引sqlALTER TABLE orders ADD INDEX idx_user_id (user_id);ALTER TABLE orders ADD INDEX idx_status (status);再次执行Explain-----------------------------------------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |-----------------------------------------------------------------------------------------------------------------| 1 | SIMPLE | orders | ref | idx_user_id,idx_status | idx_user_id | 8 | const | 120 | Using where; Using index |-----------------------------------------------------------------------------------------------------------------☆ 这次的变化非常明显1、type从ALL变成了ref说明走了非唯一索引扫描。2、key列显示用到了idx_user_id这个索引。3、rows从5000降到了120扫描行数减少了97%以上。不过这里还有一个问题status这个字段的区分度很低大部分订单的status都是1已完成所以单独建status索引效果并不好。更好的做法是建一个联合索引sqlALTER TABLE orders DROP INDEX idx_user_id;ALTER TABLE orders DROP INDEX idx_status;ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);再看Explain结果--------------------------------------------------------------------------------------------------------------------| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |--------------------------------------------------------------------------------------------------------------------| 1 | SIMPLE | orders | ref | idx_user_status | idx_user_status | 16 | const | 45 | Using where; Using index |--------------------------------------------------------------------------------------------------------------------☆ 联合索引的效果更好1、rows进一步降到了45行说明MySQL精准定位到了需要的数据。2、Extra中出现了Using index说明这是一个覆盖索引连回表操作都省了。四、查询优化案例一个订单统计SQL的调优之路下面分享一个我在实际项目中遇到的真实案例。业务需求是查询最近30天内每个用户的订单总金额并且只统计金额大于100元的订单。最初开发写的SQL是这样的sqlSELECT user_id, SUM(amount) as total_amountFROM ordersWHERE create_time DATE_SUB(NOW(), INTERVAL 30 DAY)AND amount 100GROUP BY user_idORDER BY total_amount DESCLIMIT 10;这条SQL在数据量只有几万行的时候跑得还行但当订单表增长到500万行之后查询时间直接飙到了8秒多。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口https://blog.csdn.net/Start_mswin 复制到【浏览器】打开即可,宝贝入口https://pan.quark.cn/s/b42958e1c3c0 宝贝https://pan.quark.cn/s/1eb92d021d17作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围 复制整篇文章
