MySQL-SQL优化10大最经典案例详解

SQL优化思路+经典案例分析 大家好,捡田螺的小男孩。SQL调优这块呢,大厂面试必问的。最近金九银十嘛,所以整理了SQL的调优思路,并且附几个经典案例分析。 阅读详情

前言

在应用开发的早期,数据量少,开发人员开发功能时更重视功能上的实现,随着生产数据的增长,很多SQL语句开始暴露出性能问题,对生产的影响也越来越大,有时可能这些有问题的SQL就是整个系统性能的瓶颈。

SQL优化一般步骤

1、通过慢查日志等定位那些执行效率较低的SQL语句

2、explain 分析SQL的执行计划

需要重点关注type、rows、filtered、extra。

type由上至下,效率越来越高

  • ALL 全表扫描

  • index 索引全扫描

  • range 索引范围扫描,常用语<,<=,>=,between,in等操作

  • ref 使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中

  • eq_ref 类似ref,区别在于使用的是唯一索引,使用主键的关联查询

  • const/system 单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询

  • null MySQL不访问任何表或索引,直接返回结果 虽然上至下,效率越来越高,但是根据cost模型,假设有两个索引idx1(a, b, c),idx2(a, c),SQL为"select * from t where a = 1 and b in (1, 2) order by c";如果走idx1,那么是type为range,如果走idx2,那么type是ref;当需要扫描的行数,使用idx2大约是idx1的5倍以上时,会用idx1,否则会用idx2

Extra

  • Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。

  • Using temporary:使用了临时表保存中间结果,性能特别差,需要重点优化

  • Using index:表示相应的 select 操作中使用了覆盖索引(Coveing Index),避免访问了表的数据行,效率不错!如果同时出现 using where,意味着无法直接通过索引查找来查询到符合条件的数据。

  • Using index condition:MySQL5.6之后新增的ICP,using index condtion就是使用了ICP(索引下推),在存储引擎层进行数据过滤,而不是在服务层过滤,利用索引现有的数据减少回表的数据。

3、show profile 分析

了解SQL执行的线程的状态及消耗的时间。 默认是关闭的,开启语句“set profiling = 1;”

SHOW PROFILES ;
SHOW PROFILE FOR QUERY  #{id};

4、trace

trace分析优化器如何选择执行计划,通过trace文件能够进一步了解为什么优惠券选择A执行计划而不选择B执行计划。

set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;

5、确定问题并采用相应的措施

  • 优化索引

  • 优化SQL语句:修改SQL、IN 查询分段、时间查询分段、基于上一次数据过滤

  • 改用其他实现方式:ES、数仓等

  • 数据碎片处理

场景分析

案例1、最左匹配

索引

KEY `idx_shopid_orderno` (`shop_id`,`order_no`)

SQL语句

select * from _t where orderno=''

查询匹配从左往右匹配,要使用order_no走索引,必须查询条件携带shop_id或者索引(shop_id,order_no)调换前后顺序

案例2、隐式转换

索引

KEY `idx_mobile` (`mobile`)

SQL语句

select * from _user where mobile=12345678901

隐式转换相当于在索引上做运算,会让索引失效。mobile是字符类型,使用了数字,应该使用字符串匹配,否则MySQL会用到隐式替换,导致索引失效。

案例3、大分页

索引

KEY `idx_a_b_c` (`a`, `b`, `c`)

SQL语句

select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;

对于大分页的场景,可以优先让产品优化需求,如果没有优化的,有如下两种优化方式,
一种是把上一次的最后一条数据,也即上面的c传过来,然后做“c < xxx”处理,但是这种一般需要改接口协议,并不一定可行。
另一种是采用延迟关联的方式进行处理,减少SQL回表,但是要记得索引需要完全覆盖才有效果,SQL改动如下

select t1.* from _t t1, (select id from _t where a = 1 and b = 2 order by c desc limit 10000, 10) t2 where t1.id = t2.id;

案例4、in + order by

索引

KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)

SQL语句

select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10

in查询在MySQL底层是通过n*m的方式去搜索,类似union,但是效率比union高。
in查询在进行cost代价计算时(代价 = 元组数 * IO平均值),是通过将in包含的数值,一条条去查询获取元组数的,因此这个计算过程会比较的慢,所以MySQL设置了个临界值(eq_range_index_dive_limit),5.6之后超过这个临界值后该列的cost就不参与计算了。因此会导致执行计划选择不准确。默认是200,即in条件超过了200个数据,会导致in的代价计算存在问题,可能会导致Mysql选择的索引不准确。

处理方式,可以(order_status, created_at)互换前后顺序,并且调整SQL为延迟关联。

案例5、范围查询阻断,后续字段不能走索引

索引

KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)

SQL语句

select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10

范围查询还有“IN、between”

案例6、不等于、不包含不能用到索引的快速搜索。(可以用到ICP)

select * from _order where shop_id=1 and order_status not in (1,2)
select * from _order where shop_id=1 and order_status != 1

在索引上,避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等

案例7、优化器选择不使用索引的情况

如果要求访问的数据量很小,则优化器还是会选择辅助索引,但是当访问的数据占整个表中数据的蛮大一部分时(一般是20%左右),优化器会选择通过聚集索引来查找数据。

select * from _order where  order_status = 1

查询出所有未支付的订单,一般这种订单是很少的,即使建了索引,也没法使用索引。

案例8、复杂查询

select sum(amt) from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01';
select * from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01' limit 10;

如果是统计某些数据,可能改用数仓进行解决;
如果是业务上就有那么复杂的查询,可能就不建议继续走SQL了,而是采用其他的方式进行解决,比如使用ES等进行解决。

案例9、asc和desc混用

select * from _t where a=1 order by b desc, c asc

desc 和asc混用时会导致索引失效

案例10、大数据

对于推送业务的数据存储,可能数据量会很大,如果在方案的选择上,最终选择存储在MySQL上,并且做7天等有效期的保存。
那么需要注意,频繁地清理数据,会造成数据碎片,需要联系DBA进行数据碎片处理。

SQL 优化案例 考虑删除该字段的索引,或者将索引创建在不常更新的字段上,以提高写入性能。: 通过查询日志或性能监控工具,分析索引的使用情况。如果某个索引在过去的几个月内没有被使用,可以考虑删除该索引,降低维护成本。: 在搜索中,使用了不适合的索引类型,如在文本字段上使用 B+树索引。: 在一个大型用户管理系统中,发现某些索引很少被使用,导致维护成本高。经常被更新,但该字段上却创建了索引,导致写入性能下降。: 将查询条件改为范围查询,并确保。在某些情况下,这样的查询可能更高效。: 在合并多个查询结果时,使用了。 阅读详情

相关推荐

MySQL慢SQL优化实战:10大经典案例解析

数据库查询优化是提升系统性能的关键环节,其中慢SQL问题尤为常见。通过EXPLAIN分析执行计划、监控扫描行数等核心指标,可以快速定位性能瓶颈。索引优化、查询重写和参数调优是三大基础优化手段,能有效解决80%的慢查询问题。本文基于电商、社交等典型场景,详解OR条件优化、分页查询陷阱、数据类型匹配等高频问题,特别针对MySQL 8.0的索引跳跃扫描、哈希连接等新特性给出适配方案。通过建立慢查询监控体系和优化案例库,开发团队可系统性地提升SQL性能。

weixin_32466193的博客 263

MySQL-SQL优化

前言 在应用开发的早期,数据量少,开发人员开发功能时更重视功能上的实现,随着生产数据的增长,很多SQL语句开始暴露出性能问题,对生产的影响也越来越大,有时可能这些有问题的SQL就是整个系统性能的瓶颈。 SQL优化一般步骤 1、通过慢查日志等定位那些执行效率较低的SQL语句 2、explain 分析SQL的执行计划 需要重点关注type、rows、filtered、extra。 type由上至下,效率越来越高 ALL 全表扫描 index 索引全扫描 range 索引范围扫描,常用语&lt.

m0_50180963的博客 1437

sql优化实战案例及mysql执行计划详解

Extra字段的核心价值是暴露SQL的“隐性性能损耗”避免BNL连接;优先追求ICP等索引优化;多值组合出现时(如排序+临时表),必须优先优化索引。

weixin_39650169的博客 979

8个sql优化的案例

了解数据库编译器的特性,才能避规其短处,写出高性能的SQL语句。分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。重写为 JOIN 之后,子查询的选择模式从 DEPENDENT SUBQUERY 变成 DERIVED,执行速度大大加快,从7秒降低到2毫秒。比如下面 UPDATE 语句,MySQL 实际执行的是循环/嵌套子查询(DEPENDENT SUBQUERY),其执行时间可想而知。在前端数据浏览翻页,或者大数据分批导出等场景下,是可以将上一页的最大值当成参数作为查询条件的。

tiankongzhouye的博客 3184

【Mysql】慢SQL优化详解 Mysql案例

前言 影响查询性能因素 –(定量为同一数据库 且并发 数据量一致)依次是 机器配置 查询引擎 表设计 索引 SQL语句 什么是慢SQL 比通常执行慢、或者超过最大执行限定时间,通常是500ms 慢SQL危害 每个SQL执行都需要消耗一定I/O资源 SQL执行时间长,用户的体验较差 慢SQL排查定位方法 slow log MYSQL开启slow log : SET GLOBAL slow_query_log=1; 查看slowlog: SHOW GLOBAL STATUS LIKE ‘%Slow_querie

qq_37472274的博客 1134

MySql学习之慢SQL优化和慢SQL案例

慢查询日志记录慢SQL explain查看分析SQL的执行计划 profile分析执行耗时 Optimizer Trace分析详情 确定慢SQL问题并采用相应的方案 隐式转换、最左匹配、深分页问题、in元素过多、order by 走文件排序、 索引字段上使用(!= 或者 < >)索引字段上使用is null, is not null,索引可能失效、 左右连接,关联的字段的编码格式不一样 、group by使用临时表、delete + in子查询不走索引、 其它索引失效场景,如like '%xxx'

zwb的博客 3312

mysql 经典优化案例_MySQL-SQL优化10大最经典案例详解

SQL语句select * from _t where a = 1 and b = 2 order by c desc limit 10000, 10;对于大分页的场景,可以优先让产品优化需求,如果没有优化的,有如下两种优化方式,一种是把上一次的最后一条数据,也即上面的c传过来,然后做“c < xxx”处理,但是这种一般需要改接口协议,并不一定可行。另一种是采用延迟关联的方式进行处理,减少S...

weixin_35843410的博客 365

大开眼界的十大开源人工智能项目

这是一个强大的开源工具,能让你在不同框架之间自由切换,无需重写代码。它是一个开源工具,能用简单的SQL语言同时处理数据和AI模型,让一切变得有序而高效。Rasa是我开发智能聊天机器人时的首选,它让你轻松掌控机器人对话的每个细节。我经常用它快速验证模型在不同框架下的表现,省去了大量烦躁的调试过程。最近发现的Vapi是一个以API方式提供的语音AI工具,开发者友好程度超乎想象。KNIME是一个以拖拽方式构建数据分析流程的开源工具,仿佛是数据界的乐高积木。它像个忠诚的守门员,确保你的模型始终健康运行。

wjianwei666的专栏 1108

SQL优化 10大经典案例场景实战

SQL优化 10大经典案例场景实战 转录于后端架构师公众号 前言 在应用开发的早期,数据量少,开发人员开发功能时更重视功能上的实现,随着生产数据的增长,很多SQL语句开始暴露出性能问题,对生产的影响也越来越大,有时可能这些有问题的SQL就是整个系统性能的瓶颈。 SQL优化一般步骤 1、通过慢查日志等定位那些执行效率较低的SQL语句 2、explain 分析SQL的执行计划 需要重点关注type、rows、filtered、extra。 type由上至下,效率越来越高 ALL..

地主 2780

新手入门:N-CMAPSS数据集解析与实战指南

本文详细解析了N-CMAPSS数据集的结构与应用,为新手提供航空发动机故障预测的实战指南。通过Python代码示例展示数据加载、探索和特征工程技巧,帮助读者快速掌握剩余寿命(RUL)预测的关键步骤,并分享常见问题解决方案与进阶学习路径。

weixin_29056701的博客 378

二维紧束缚模型求解程序

假如我们已经推导出了二维紧束缚模型的哈密顿量矩阵H(kx,ky)的解析形式,那么在整个布里渊区对角化矩阵就可以得到能带。从技术上说这里面有以下几个问题。 1. 缺少实空间和倒空间的坐标,使得能带难以和布里渊区高对称点对应。 2. 最容易处理的布里渊区是矩形,对于其他多边形,如果全布里渊区mesh画费米面有一定技术困难。 因此一个好的处理框架是首先给出实空间原子坐标,给出实空间哈密顿量,...

wwxy1995的博客 4085

SQL 优化之旅:10 大经典场景全解析

在应用开发的早期,数据量少,开发人员开发功能时更重视功能上的实现,随着生产数据的增长,很多SQL语句开始暴露出性能问题,对生产的影响也越来越大,有时可能这些有问题的SQL就是整个系统性能的瓶颈。

编程点滴 971

微带线贴片天线尺寸的计算,利用Matlab计算+常见的RF计算在线公式

微带线贴片天线尺寸的计算,利用Malatb计算 这几天在使用hfss对矩形天线仿真,每次改变频率和实验的材料,都要重新计算一遍,太麻烦了,所以用matlab进行计算。 知道天线的工作频率:f_r 介质板的介电常数:epsilon_r 介质板厚度:h 就可以计算出矩形天线的长:L 矩形天线的宽:W 对于下面公式的单位要注意,公式以及化简了,要注意单位 由于matlab中的变量不能用希腊字母代替,所以用了该字母的读音 代码如下: f_r=2.45; %工作频率,单位GHz epsilon_r=3.5;

weixin_43404609的博客 2万+

MySQL大表优化方案(应该是最完整最全的了)

水平拆分是通过某种策略将数据分片来存储,分库内分表和分库两部分,每片数据会分散到不同的MySQL表或库,达到分布式的效果,能够支持非常大的数据量。前面的表分区本质上也是一种特殊的库内分表库内分表,仅仅是单纯的解决了单一表数据过大的问题,由于没有把表的数据分布到不同的机器上,因此对于减轻MySQL服务器的压力来说,并没有太大的作用,大家还是竞争同一个物理机上的IO、CPU、网络,这个就要通过分库来解决前面垂直拆分的用户表如果进行水平拆分,结果是:实际情况中往往会是垂直拆分和水平拆分的结合,即将。

Java全能学习+面试指南 9064

网格聚类 Grid Clustering/Grid-based Clustering

本文主要简单介绍了网格聚类的基本概念,优缺点,应用场景,建模时的注意事项,评价指标,实现方法,python示例和模型参数等。

JasonH2021的博客 4829

bcftools实战指南:从VCF文件处理到变异分析全流程解析

在基因组数据分析中,VCF(Variant Call Format)文件是存储基因序列变异信息的标准格式,广泛应用于群体遗传学和生物信息学研究。其核心原理是通过结构化文本记录SNP、INDEL等变异位点的基因型、质量值和注释信息。掌握高效处理VCF文件的技术,对于提升变异检测、数据清洗和下游分析的工作流效率具有重要价值。在生物信息学工具生态中,bcftools作为处理VCF/BCF文件的瑞士军刀,通过模块化设计实现了数据查询、过滤、统计和格式转换等核心功能。其流式处理架构支持管道化操作,能有效应对海量基因组

weixin_29081703的博客 325

Mysql十大经典案例 SQL优化

分页优化案例 普通写法: select * from buyer where sellerid=100 limit 100000,5000 普通limit M,N的翻页写法,在越往后翻页的过程中速度越慢,原因mysql会读取表中的 前M+N条数据,M越大,性能就越差。 优化写法: select t1.* from buyer t1, (select id from buyer s...

杨杰超的个人博客 2349

13、多实例任务(multi instance)

多实例任务,是指一个类型的任务需要循环执行多次,包括串行多实例任务和并行多实例任务。

leyoliu的博客 3203

10个最经典的 MySQL 数据库运维案例

笔者在刚开始学习数据库的时候,没少走弯路。经常会遇到各种稀奇古怪的 error 信息,遇到报错会很慌张,急需一个解决问题的办法。跟无头苍蝇一样,会不加思索地把错误粘到百度上,希望赶紧查找一下有没有好的问题处理方法。我想上述这个应该是刚从事数据库的小白都会遇到的窘境。 今天就给大家列举 MySQL 数据库中最经典的十大错误案例,并附有处理问题的解决思路和方法。希望能给刚入行或数据库爱好者一些帮助,...

云计算技术达人 3766

基于STM32设计的智能防盗单车锁_349

本设计基于STM32F103RCT6单片机,研制了一种适用于户外环境的智能防盗单车锁系统。系统集成了GPS定位模块、ADXL345加速度传感器、4G通信模块(Air780E)、OLED显示屏、继电器模拟锁控模块等硬件,通过姿态感知实现车辆异常移动检测,在锁车状态下自动触发蜂鸣器报警并通过短信与APP通知用户。

868

WINCC7.3破解文件

WINCC7.3硬件够破解文件,有需要的下载!WINCC7.3硬件够破解文件,有需要的下载!

MySQL优化之SQL优化详解

sql优化详解

个人博客,欢迎收藏。 1591

假人有限元模型

假人模型,车辆碰撞假人有限元模型,适合做乘员安全的研究者利用

10个经典场景带你玩转SQL优化

in查询在进行cost代价计算时(代价 = 元组数 * IO平均值),是通过将in包含的数值,一条条去查询获取元组数的,因此这个计算过程会比较的慢,所以MySQL设置了个临界值(eq_range_index_dive_limit),5.6之后超过这个临界值后该列的cost就不参与计算了。对于大分页的场景,可以优先让产品优化需求,如果没有优化的,有如下两种优化方式, 一种是把上一次的最后一条数据,也即上面的c传过来,然后做“c < xxx”处理,但是这种一般需要改接口协议,并不一定可行。

greatr42d的博客 791

计算机控制技术及应用

《计算机控制技术及应用》通过总结作者的科研成果和吸收国内外的先进理论、方法和技术,以计算机控制系统的网络化、开放化、智能化和集成化发展趋势为主线索,主要介绍计算机控制系统的基本概念和计算机控制系统的典型形式,计算机控制系统的数学描述与基本分析方法,以及数字PID控制器、最少拍控制系统、达林算法、状态变量设计法等设计方法及其各种改进算法。重点突出计算机控制系统输入输出通道接口的设计方法和典型控制系统的设计案例与实现方法等。

sql 优化 5大步骤+10个案例,堪称SQL优化万能公式

一、前言 在应用开发的早期,数据量少,开发人员开发功能时更重视功能上的实现,随着生产数据的增长,很多SQL语句开始暴露出性能问题,对生产的影响也越来越大,有时可能这些有问题的SQL就是整个系统性能的瓶颈。 二、SQL优化一般步骤 1、通过慢查日志等定位那些执行效率较低的SQL语句 2、explain 分析SQL的执行计划 需要重点关注type、rows、filtered、extra。 type由上至下,效率越来越高。 ALL 全表扫描; index 索引全扫描; range 索引范围扫描,常用语<,

LoveSummer 6177

10 大经典 SQL 优化实例,带你解锁数据库高效性能密码

在应用开发的早期,数据量少,开发人员开发功能时更重视功能上的实现,随着生产数据的增长,很多SQL语句开始暴露出性能问题,对生产的影响也越来越大,有时可能这些有问题的SQL就是整个系统性能的瓶颈。

编程点滴 1868
上一篇: 涨姿势了呀,原来这才是多线程正确实现方式
下一篇: 分布式套路之分库分表漫谈
Java佳佳
博客等级 码龄4年 216粉丝 286原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值