前言
相信大家对后端数据库的SQL
都不会陌生,但是有时候我们会无意间
就写出一堆奇怪的SQL,可能当时还没有发现
,但是没关系,客户后期会告诉你的,这也没有关系,只要后期不是你负责解决bug就好
。
可是很不幸有时候这种问题就是分配给你解决 。。。。。。
下面就是我遇到的某个问题:界面上某个查询数据的功能,被用户反馈响应越来越慢
…从最初部署好的秒响应
,到三四秒响应
,再到现在的将近五六秒
才响应…这对于一个查询而言可以说是慢的雅痞了。
用户:
经理:那个谁(指我),(抬头对视一眼)对,就你
,去把这个问题解决下。
我:(飞身闪退几十米
)不熟,别来沾边!
经理:(抬头皱眉
)嗯?什么意思?
我:
经理掏出西瓜头,熟练带上
我: ?????????
经理:家人们,谁懂啊,遇到个下头程序员,九敏SOS。。。
经理:那你这个月的KPI可能不够
,工资
可能会,emmm…你自己看着办吧
我:
排查
那就开始排查吧,哎,不相干也得干啊
,不然窝囊费
去哪里拿呢。不过有位将军曾经说过:
一想到他,我小腹突然升起一股暖流,莫名的力量让我突然蹭
的站起来!此时手机里响起工资到账的提示音
:
开干开干,先瞅一眼环境
系统数据库为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
的逆序排列
- 最后是
分页
存在的问题
该过程中存在的问题:
-
先左连接再通过过滤条件去筛选
,这会导致无论查询条件如何都会导致两个表做一个全表扫描
-
默认查询排序未走索引
,由于默认的SQL无查询条件
,那么基本就不会走索引了 未给常用的查询条件组合创建组合索引
-
查询条件顺序问题
。要把越常用的条件放前面,用的越少的放后面
。我们知道索引结构为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秒左右
。
能不能再优化?
文章来源:https://www.toymoban.com/news/detail-484674.html
答案是可以
,那样的话就不走框架的分页,将分页逻辑自己丢到内层的student表那里
,这样的话内部查询,student表返回的数据会更少,执行速度也会更快
。但是返回数据给前端的页数据信息,比如总共多少数据,总共有多少页什么的信息,就不是框架处理了,而是我自己处理,又不加钱
,就这样了。文章来源地址https://www.toymoban.com/news/detail-484674.html
下班!!!
到了这里,关于记一次简单的SQL调优的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!