记一次简单的SQL调优

这篇具有很好参考价值的文章主要介绍了记一次简单的SQL调优。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

前言

相信大家对后端数据库的SQL都不会陌生,但是有时候我们会无意间就写出一堆奇怪的SQL,可能当时还没有发现,但是没关系,客户后期会告诉你的,这也没有关系,只要后期不是你负责解决bug就好

可是很不幸有时候这种问题就是分配给你解决 。。。。。。

下面就是我遇到的某个问题:界面上某个查询数据的功能,被用户反馈响应越来越慢…从最初部署好的秒响应,到三四秒响应,再到现在的将近五六秒才响应…这对于一个查询而言可以说是慢的雅痞了。

用户:
记一次简单的SQL调优

经理:那个谁(指我),(抬头对视一眼)对,就你,去把这个问题解决下。

我:(飞身闪退几十米)不熟,别来沾边!

经理:(抬头皱眉)嗯?什么意思?

我:
记一次简单的SQL调优

经理掏出西瓜头,熟练带上
记一次简单的SQL调优
我: ?????????

经理:家人们,谁懂啊,遇到个下头程序员,九敏SOS。。。
记一次简单的SQL调优
经理:那你这个月的KPI可能不够工资可能会,emmm…你自己看着办吧

我:
记一次简单的SQL调优

排查

那就开始排查吧,哎,不相干也得干啊,不然窝囊费去哪里拿呢。不过有位将军曾经说过:
记一次简单的SQL调优
记一次简单的SQL调优
一想到他,我小腹突然升起一股暖流,莫名的力量让我突然的站起来!此时手机里响起工资到账的提示音
记一次简单的SQL调优

记一次简单的SQL调优
开干开干,先瞅一眼环境

系统数据库为ORACLE,索引结构为B+树,student表数据大概是120w行。

如果是查询慢的话,那定位到具体的查询语句就知道问题所在了,果然没过多久就定位到了查询语句(以下语句内容均为虚构 但执行遇到的情况一样):

		select * from student s
			left  join campus c on s.campusId = c.campusId 
        where 
			s.studentId = ,
			s.name like ,
			s.idCard = ,
			s.phone =   ,
			s.addmissonDate >= ,
			s.addmissionDate <= 
        order by s.admissionDate desc

先分析该SQL,上述SQL语句中显示了两个表的连接查询,一个是学生表 -- student ,存储了学生的基本信息,一个是校区表 - campus,存储了校区的基本信息

连接条件为学生表里面的校区id -- campusId ,对应校区表的campusId

查询条件为学生的学号,姓名,身份证,电话以及开学日期(addmissionDate)的起止时间,并且所有条件可选。

再以adminssionDate逆序排列

上述查询的目的是,根据条件查出对应学生及校区的信息

分页使用框架PageHelper,该框架处理后,在控制台输出的执行语句为:

		#默认查询
        SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM PAGEHELPER_ROW_ID FROM (
			select * from student s
				left  join campus c on s.campusId = c.id
			order by s.admissionDate desc
        ) TMP_PAGE
        ) WHERE PAGEHELPER_ROW_ID <= 10 AND PAGEHELPER_ROW_ID > 0

		#带查询条件
         SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM PAGEHELPER_ROW_ID FROM (
			select * from student s
				left  join campus c on s.campusId = c.campusId 
			where 
				#以下条件都是可选
				s.studentId = ,
				s.name like ,
				s.idCard = ,
				s.phone =  ,
				s.addmissonDate >= ,
				s.addmissionDate <= 
			order by s.admissionDate desc
        ) TMP_PAGE
        ) WHERE PAGEHELPER_ROW_ID <= 10 AND PAGEHELPER_ROW_ID > 0

注:该语句在数据库直接执行的执行时间为2、3s左右

在上述语句中返回字段我这里就图个方便,简单写成*,全部返回了,具体现实里还是根据自己需要的字段返回即可。

两个表中的索引都只有对应的主键索引campusId 并非校区表主键),无其他索引

好了,大家可以找找上述SQL的问题。

分析

问题分析

下面将进行问题的分析

SQL的执行流程

首先我们来分析这个SQL的结构,可以看出执行顺序是:

  • 先进行左连接,得到两个表之间所有数据的连接结果集
  • 然后通过where子句过滤掉不符合条件的列(如果有查询条件的话)
  • 再进行一个admissionDate逆序排列
  • 最后是分页

存在的问题

该过程中存在的问题:

  1. 先左连接再通过过滤条件去筛选,这会导致无论查询条件如何都会导致两个表做一个全表扫描
  2. 默认查询排序未走索引,由于默认的SQL无查询条件,那么基本就不会走索引了
  3. 未给常用的查询条件组合创建组合索引
  4. 查询条件顺序问题要把越常用的条件放前面,用的越少的放后面。我们知道索引结构为B+树是遵从最左匹配原则的,但是对like这样的模糊查询,很大概率不会走索引。比如我要查一个名叫xx辉的人,那么我的查询条件就是一个辉这种,就无法使用后续索引了。当然上述是基于组合索引的情况,也可以创建一个为该列创建一个全文索引

优化思路

  • 先执行where条件过滤不需要的行,再进行左连接
  • 提示默认查询排序走索引,创建addmissionDate列相关的索引,可以通过设置查询条件addmissionDate的默认值来提示查询语句使用改索引
  • 为常用查询条件创建索引
  • 调整查询条件的顺序

验证解决

验证执行流程

我们先验证SQL的流程

看下执行计划:

#默认无字段查询
explain plan for 
        SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM PAGEHELPER_ROW_ID FROM (
			select * from student s
				left  join campus c on s.campusId = c.campusId 
			order by s.admissionDate desc
        ) TMP_PAGE
        ) WHERE PAGEHELPER_ROW_ID <= 10 AND PAGEHELPER_ROW_ID > 0


#全字段查询 - 这里先不看
explain plan for 
        SELECT * FROM ( SELECT TMP_PAGE.*, ROWNUM PAGEHELPER_ROW_ID FROM (
			select * from student s
				left  join campus c on s.campusId = c.campusId 
			where 
				s.studentId = ,
				s.name like ,
				s.idCard = ,
				s.phone =  ,
				s.addmissonDate >= ,
				s.addmissionDate <= ,
				
			order by s.admissionDate desc
        ) TMP_PAGE
        ) WHERE PAGEHELPER_ROW_ID <= 10 AND PAGEHELPER_ROW_ID > 0

我们先以无查询条件的语句为例,运行以下命令查看:

	select * from table(DBMS_XPLAN.DISPLAY);

可以看到以下输出:

 Id  | Operation                 | Name              | Rows  |  Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                   |   130M|    18G|       |  2927K  (1)| 00:01:55 |
|*  1 |  VIEW                     |                   |   130M|    18G|       |  2927K  (1)| 00:01:55 |
|   2 |   COUNT                   |                   |       |       |       |            |          |
|   3 |    VIEW                   |                   |   130M|    16G|       |  2927K  (1)| 00:01:55 |
|   4 |     SORT ORDER BY         |                   |   130M|    11G|    13G|  2927K  (1)| 00:01:55 |
|*  5 |      HASH JOIN RIGHT OUTER|                   |   130M|    11G|       | 10268   (4)| 00:00:01 |
|   6 |       TABLE ACCESS FULL   | CAMPUS            |   231 |  9009 |       |     3   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL   | STUDENT           |  1131K|    63M|       |  9936   (1)| 00:00:01 |

可以看到执行流程以及关键的点,跟我们上面的猜测差不多。值得注意的是,执行计划受很多因素影响,并不一定准确。我的建议是看前面几列主要的信息即可,后半截的可以忽略

一些执行计划的基本知识

此部分用于介绍上述得执行计划中各列的信息
在 Oracle 的执行计划中,各列的含义如下:

  • Id操作的唯一标识符,用于标识执行计划中的每个操作,带*表示关键操作。
  • Operation:执行的具体操作类型。例如,"TABLE ACCESS"表示对表进行访问,"INDEX SCAN"表示对索引进行扫描,"SORT"表示排序操作,等等。
  • Name与操作相关的对象的名称。例如,表名、索引名或者视图名。
  • Rows:操作返回的行数估计
  • Bytes:操作返回的字节数估计
  • TempSpc:操作使用的临时表空间大小(如果需要)。
  • Cost (%CPU):操作的成本估计,通常用于衡量操作执行的相对代价。较小的成本表示较低的开销。这个值的大小反映了操作执行所需的资源、IO 开销、CPU 消耗等方面的估计。由于成本值是相对的,没有具体的单位,它主要用于比较不同操作的开销,而不是表示实际的执行时间或资源消耗。
  • Time:操作的估计执行时间

Operation中常见的操作:

  • VIEW:表示执行计划中的步骤涉及到对一个或多个视图的访问和处理

  • TABLE ACCESS:表示对表进行访问。这可能包括全表扫描FULL)或根据索引进行访问(BY INDEX ROWID、BY INDEX ROWID BATCHED等)。

  • INDEX SCAN:表示对索引进行扫描,通常是通过索引键值的顺序访问索引中的数据。

  • INDEX RANGE SCAN:表示通过索引范围扫描,根据索引的范围条件来选择索引键值。

  • NESTED LOOPS:表示嵌套循环连接,通常用于执行连接操作,其中对于外部输入的每一行,内部表被扫描一次。

  • HASH JOIN:表示哈希连接,用于执行连接操作。它使用哈希算法将两个输入数据集的行组合在一起。

  • SORT:表示排序操作,用于对结果进行排序。可能是排序输入数据或为了执行连接或分组操作而进行的排序。

  • AGGREGATE:表示聚合操作,用于计算聚合函数(如SUM、COUNT、AVG)的结果。

  • FILTER:表示过滤操作,用于根据条件筛选行。

  • UNION-ALL:表示合并多个查询结果的操作,包括所有行,不去重。

一些单位的说明:

  • M” 表示 “百万”。当在 “Rows” 列中看到以 “M” 结尾的值时,它表示行数的估计是以百万为单位的。例如,如果 “Rows” 列显示 “10M”,表示行数估计为 10 百万行。

  • K” 表示 “”。当在 “Cost” 列中看到以 “K” 结尾的值时,它表示成本的估计是以千为单位的。例如,如果 “Cost” 列显示 “100k”,表示成本估计为 100 千,也就是10万。

优化

我们按照上面的思路来,一条一条来优化

先执行where条件过滤不需要的行,再进行左连接

这个简单,修改SQL语句,调整执行顺序,先过滤再左连接

   SELECT * FROM (
		SELECT TMP_PAGE.*, ROWNUM PAGEHELPER_ROW_ID FROM (
			select * from  (
				select * from student where 
					studentId = ,
					name like ,
					idCard = ,
					phone =  ,
					addmissonDate >= ,
					addmissionDate <= 
				order by s.admissionDate desc
			) s left  join campus c on s.campusId = c.campusId
      ) TMP_PAGE
  ) WHERE PAGEHELPER_ROW_ID <= 10 AND PAGEHELPER_ROW_ID > 0

提示排序走索引

这里的话可以设置默认值为合适的值即可

   SELECT * FROM (
		SELECT TMP_PAGE.*, ROWNUM PAGEHELPER_ROW_ID FROM (
			select * from  (
				select * from student where 
					#为默认查询设置默认查询入学时间为7个月前  
					#保证查询会有数据  用户自定义查询可以自己选择时间
					addmissonDate >= (sysdate - 222),
				order by s.admissionDate desc
			) s left  join campus c on s.campusId = c.campusId
      ) TMP_PAGE
  ) WHERE PAGEHELPER_ROW_ID <= 10 AND PAGEHELPER_ROW_ID > 0

创建入学日期的索引student_index_amDate

 create index index_student_amDate on student (addmissionDate)
  tablespace student
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

通过上述操作,通过索引去查找并排序,就能很快地得到结果

为常用查询条件创建索引

其实这条应该放在调整查询条件顺序后面,因为只有确定了查询顺序才好设计索引。但是之前那么写了,我又懒得改,大家这里注意下即可。

可以通过翻看日志记录信息或者别的第三方工具得到常用的查询语句统计结果

比如常用的查询中有一个查询只有名字的模糊查询,那么可以为其单独创建一个全文索引

CREATE INDEX index_student_name ON student(name)
  INDEXTYPE IS CTXSYS.CONTEXT;

还可以是常用学号来查数据,那么就可以再建一个学号的索引:

 create index index_student_sId on student (studentId)
  tablespace student
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

常用电话的话,创建电话索引:

 create index index_student_phone on student (phone)
  tablespace student
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

又或者他们常用学号和入学日期来查数据,那么还可以在创建一个索引

 create index index_student_sIdAndAmDate on student (studentId , addmissionDate)
  tablespace student
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

一定要记住,索引存在的目的就是加快查询速度为一个查询创建一个或者多个索引是很常见的事,索引占的大小一般是表的几倍大小,千万不要舍不得创建索引。

调整查询条件的顺序

最后将模糊查询调整到最后,让查询大都多能走索引即可。这里我只调整了name的位置,其他的条件并未调整,具体的查询条件顺序还是要根据自己的任务需求来定

   SELECT * FROM (
		SELECT TMP_PAGE.*, ROWNUM PAGEHELPER_ROW_ID FROM (
			select * from  (
				select * from student where 
					studentId = ,
					idCard = ,
					phone =  ,
					addmissonDate >= ,
					addmissionDate <= ,
					name like 
				order by s.admissionDate desc
			) s left  join campus c on s.campusId = c.campusId
      ) TMP_PAGE
  ) WHERE PAGEHELPER_ROW_ID <= 10 AND PAGEHELPER_ROW_ID > 0

至此该功能调优算是告一段落了,查询时间从5-6秒降到了0.3-0.6秒左右

记一次简单的SQL调优
能不能再优化?

答案是可以,那样的话就不走框架的分页,将分页逻辑自己丢到内层的student表那里,这样的话内部查询,student表返回的数据会更少,执行速度也会更快。但是返回数据给前端的页数据信息,比如总共多少数据,总共有多少页什么的信息,就不是框架处理了,而是我自己处理,又不加钱,就这样了。文章来源地址https://www.toymoban.com/news/detail-484674.html

下班!!!

到了这里,关于记一次简单的SQL调优的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 记一次挖edusrc漏洞挖掘(sql注入)

    在利用fofa收集信息的时候发现的,我这里直接开始挖洞部分写了。 目标是一个中学的站点,在一次挖洞过程中遇到个sql注入,漏洞已报送平台进行了修复。该文章仅用于交流学习,切勿利用相关信息非法测试,我也是刚入门的小白,欢迎各位大佬指点。 访问url/gywm.asp?id=95

    2024年02月08日
    浏览(44)
  • 记一次生产慢sql索引优化及思考

    夜黑风高的某一晚,突然收到一条运营后台数据库慢sql的报警,耗时竟然达到了60s。 看了一下,还好不是很频繁,内心会更加从容排查问题,应该是特定条件下没有走到索引导致,如果频繁出现慢查询,可能会将数据库连接池打满,导致数据库不可用,从而导致应用不可用。

    2024年02月04日
    浏览(48)
  • 记一次简单的白加黑测试

    前一阵子成了小洋人,所以很长的时间也没更新了,今天更一篇PHP代码审计的吧,此次分享的内容十分的简单,大家简单的看看即可 从上述代码,我们可以看出来,$ip变量接收过来之后通过合并运算符(??)判断是不是空,是空的话就返回114.114.114.114,否则就返回变量本身,说

    2023年04月09日
    浏览(42)
  • 好文分享 | 记一次Oracle12c数据库SQL短暂缓慢问题分析

    本文为墨天轮社区作者 张sir 原创作品,记录了日常运维Oracle数据库过程中遇到的一个慢SQL问题的解决、优化过程,文章内容全面具体、分析到位,且含有经验总结,分享给各位。 这次出问题的数据库比较特殊,承接的系统交易要求很高,SQL基本都是短平快,响应时间基本不

    2024年02月05日
    浏览(57)
  • 一次简单的SQL注入靶场练习

    为了巩固SQL注入以及实战演练的需要,我们来做一次简单的关于SQL注入的实战靶场练习 靶场下载地址: https://download.vulnhub.com/pentesterlab/from_sqli_to_shell_i386.iso 因为是linux系统,大家要注意安装的是linux的Debian系统,系统才能正常的运行,然后在设置里放置iso光盘就可以了。 安

    2023年04月10日
    浏览(40)
  • 记一次eduSRC挖掘

    eduSRC是一个专门收录国内高校漏洞的WEB平台,其以审核快,审核效率高而知名,白帽子提交指定高校漏洞并有证书经历以及Rank奖励,Rank可以在平台上换取衣服、键盘、证书等礼物,同样eduSRC的账号也是比较麻烦才能获得的,我研究了一下发现它有两种获取方法: 1、内部人员

    2024年02月05日
    浏览(40)
  • 记一次git冲突解决

    在提交mr的时候突然遇到了conflict,这时候意识到没有及时pull代码,脑海中想起了隔壁一起入职的同事经常念叨的一句“每天早上来都pull一下代码”。但是已经迟了 我看了一下,主要是同一个文件,master分支上已经被修改过,然后我要mr的代码也在这个文件上进行了修改。因

    2024年02月05日
    浏览(44)
  • 记一次内存泄漏排查

    最近某项目的服务突然告警,cpu超85%,随后就是服务宕机。交付重启服务后恢复正常但是随后不久又开始告警,特别是白天,严重影响客户业务进行。 1、分析日志 查看日志的过程中发现存在内存溢出(OOM),思考要么存在内存泄漏要么业务上触发了某个接口存在大对象,结

    2023年04月16日
    浏览(52)
  • 记一次死锁问题

    最近在做一个需求,碰到了死锁的问题,记录下解决问题的过程 这个需求要改动一个接口,我这边称为A接口,原先的逻辑是A接口内部会调用c方法,c方法是一个dubbo方法, 现在需要再A接口里添加调用B方法,b方法是本地调用。 A接口的入参是某个商品的编码,拿到这个商品编

    2023年04月26日
    浏览(54)
  • 【BugBounty】记一次XSS绕过

    最近一直在看国外的赏金平台,绕waf是真的难受, 记录一下绕过的场景。 一开始尝试XSS,发现用户的输入在title中展示,那么一般来说就是看能否闭合,我们从下面图中可以看到,输入尖括号后被转成了实体。 解释一下什么是html实体编码 HTML实体编码,也即HTML中的转义字符。

    2024年02月08日
    浏览(33)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包