背景
查询字段其实比较多,我选择聚焦在瓶颈点上,让我们开始吧
功能背景简介:
我们在一个进入数据中心的入口设置了一台记录人员进出的机器,由保卫员操作记录人员进出(通过换取通关卡的方式,在换取通关卡时,记录进入时间,在归还通关卡时,记录离开时间),业务方需要知道某段时间内在数据中心内的人数、次数 、具体进入的人、进入的人的进入时长等。
按照功能背景,我们建立了一张表
CREATE TABLE"USER"."ENTER_HISTORY"
{
"ID" NUMBER,
"LOGIN_TIME" VARCHAR2(128),
"LOGOUT_TIME" VARCHAR2(128),
"USER_ID" VARCHAR2(128),
PRIMARY KEY ("ID")
};
COMMENT ON COLUMN "USER"."ENTER_HISTORY"."ID" IS "id";
COMMENT ON COLUMN "USER"."ENTER_HISTORY"."LOGIN_TIME" IS "进入时间";
COMMENT ON COLUMN "USER"."ENTER_HISTORY"."LOGOUT_TIME" IS "离开时间";
COMMENT ON COLUMN "USER"."ENTER_HISTORY"."USER_ID" IS "用户标识";
复制代码
我们以2021-12-01 00:00:00到2022-01-01 00:00:00的时间为例,统计在此时间在数据中心的人数(进出、停留的人数)
SELECT
SUM(DISTINCT EH.USER_ID)
FROM USER.ENTER_HISTORY EH
WHERE
NOT (
EH.LOGOUT_TIME<'2021-12-01 00:00:00'
OR
EH.LOGIN_TIME>'2022-01-01 00:00:00'
)
复制代码
SQL语句写成这样的原因如下图,排除红色的两块区域,剩下的就都是符合条件的记录
业务问题1:
某一天,业务反馈,保卫员没有将每张卡都执行归还操作,即没有将离开时间给记录,导致业务在查询人数时一直有些记录干扰,业务提出,将没有离开时间的,记为(进入时间+1天/或当前时间--视哪个时间比较近),考虑到此数据是同步机器数据,如果在我们这里直接修改,会导致数据不一致,因此考虑在查询时下功夫,考虑到查询逻辑,在离开时间不存在时,将离开时间统一为(进入时间+1天)进行计算即可
改写的SQL语句如下
NVL函数
语法
NVL(eExpression1, eExpression2)
如果 eExpression1 的计算结果为 null 值,则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值,则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值,则 NVL( ) 返回 .NULL.。
SELECT
SUM(DISTINCT EH.USER_ID)
FROM USER.ENTER_HISTORY EH
WHERE
NOT (
NVL(EH.LOGOUT_TIME,TO_CHAR(
TO_DATE(EH.LOGIN_TIME,'yyyy-mm-dd hh24:mi:ss')+1
,'yyyy-mm-dd hh24:mi:ss')))
<'2021-12-01 00:00:00')
OR
EH.LOGIN_TIME>'2022-01-01 00:00:00'
)
复制代码
业务问题2
改写语句后,起初因为功能刚上线没有多少数据,因此查询效率被忽略
过了几个月,业务反馈查询时等待时间过长,叫我们看看能不能弄快点
于是我又看起了这条语句,未建立索引
步骤1:建索引
按照此语句进行查询,在DBEAVER上看执行计划发现并没有走索引,因为我们还没创建索引
以我们使用到的SQL,涉及到两个查询字段,且是同一级,我们可以只用一个字段建立索引,或者是建立两个字段的联合索引,这里采取建立联合索引,建立索引语句如下
CREATE INDEX IDX_EH_LOGINOUT_TIME ON USER.ENTER_HISTORY(LOGIN_TIME,LOGOUT_TIME)
复制代码
建立索引后,在DBEAVER上查看执行计划,
发现还是没走索引
此时开始分析没走索引的原因
1、查询字段使用了函数
2、优化器觉得使用全表比使用索引更快
步骤2:改写SQL
这里有个关于ORACLE的NOT语句的一个小知识
我们的条件
NOT (
NVL(EH.LOGOUT_TIME,TO_CHAR(
TO_DATE(EH.LOGIN_TIME,'yyyy-mm-dd hh24:mi:ss')+1
,'yyyy-mm-dd hh24:mi:ss')))
<'2021-12-01 00:00:00')
OR
EH.LOGIN_TIME>'2022-01-01 00:00:00'
)
复制代码
实际上会被改写成
NVL(EH.LOGOUT_TIME,TO_CHAR(
TO_DATE(EH.LOGIN_TIME,'yyyy-mm-dd hh24:mi:ss')+1
,'yyyy-mm-dd hh24:mi:ss')))
>='2021-12-01 00:00:00')
AND
EH.LOGIN_TIME<='2022-01-01 00:00:00'
复制代码
所以可以排除NOT的影响
对于查询列使用到NVL和TO_DATE,我采取判空和将计算改到数值侧的方式,更改后如下
(
(EH.LOGOUT_TIME IS NOTNULL
AND
EH.LOGOUT_TIME >= '2021-12-01 00:00:00')
OR
(EH.LOGOUT_TIME IS NULL
AND
EH.LOGIN_TIME >= TO_DATE('2021-12-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+1
,'yyyy-mm-dd hh24:mi:ss')))
)
AND
EH.LOGIN_TIME<='2022-01-01 00:00:00'
复制代码
改写后再次查看执行计划,发现还是不走索引
步骤3:开启ORACLE强制索引
我们判断可能是因为建立的字段是文本值,ORACLE在建立执行计划的时候,优化器不选择此索引。此时由于我们觉得走索引效率可能会更高,因此我们强制让SQL走索引,语句如下
强制索引
SELECT /*+index(t pk_emp)*/ * FROM EMP T --强制索引,/*...*/第一个星星后不能由空格,里边内容结构为加号index(表名 空格 索引名) --如果表用了别名,注释里的表也要使用别名 复制代码
SELECT /*+index(EH IDX_EH_LOGINOUT_TIME)*/
SUM(DISTINCT EH.USER_ID)
FROM USER.ENTER_HISTORY EH
WHERE
(EH.LOGOUT_TIME IS NOT NULL
AND
EH.LOGOUT_TIME >= '2021-12-01 00:00:00')
OR
(EH.LOGOUT_TIME IS NULL
AND
EH.LOGIN_TIME >= TO_DATE('2021-12-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+1
,'yyyy-mm-dd hh24:mi:ss')))
)
AND
EH.LOGIN_TIME<='2022-01-01 00:00:00'
复制代码
此时查看执行计划,发现已经走了索引
其实我们最好是在建立字段的时候考虑字段的功能,设置合适的格式,例如在这里用字符串存储时间是不合适的,但是由于我在做这个功能的时候这个表就已经在了,所以没去做改动。
步骤4:尝试开启ORACLE并行模式
但是,由于实际的查询范围,在一些查询时间跨度比较大的时候,实际上相当于全表查询,索引的作用并不大,例如这个功能是从6月份上线,如果查询6月份到现在的数据,就相当于全表查询。团队的DBA给出建议,说开启ORACLE的并行模式。但我在实际使用后比较性能发现差别不大。修改后SQL如下
ORACLE并行模式
语法
/ +parallel(table_short_name,cash_number) /
强行启用并行度来执行当前SQL,可以强行启用Oracle的多线程处理功能。多核同时工作,来提高效率。
SELECT /*+parallel(EH,4)*/
SUM(DISTINCT EH.USER_ID)
FROM USER.ENTER_HISTORY EH
WHERE
(EH.LOGOUT_TIME IS NOT NULL
AND
EH.LOGOUT_TIME >= '2021-12-01 00:00:00')
OR
(EH.LOGOUT_TIME IS NULL
AND
EH.LOGIN_TIME >= TO_DATE('2021-12-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+1
,'yyyy-mm-dd hh24:mi:ss')))
)
AND
EH.LOGIN_TIME<='2022-01-01 00:00:00'
复制代码
总结
实际上在真实环境上,是直接查出所有数据,在程序内进行统计,以下是改造前后的大致性能数据
性能比较,表内总数5w3,查询七月份到明年一月份的时间,约3.8w条数据,
优化前:耗时约4s
优化后:耗时约4s
总的来说,这是一次不成功的优化案例,但其中的思路、做法和做法希望值得大家参考
如果你对本文章有建议或疑问,欢迎在下面进行留言,一起交流
我是Vapire,一个普通的全栈开发。
以开发的角度看问题,用开发的方式学知识。
参考
ORACLE的强制索引_weixin_30765319的博客-CSDN博客
Oracle的NVL函数用法 - 黄景新 - 博客园 (cnblogs.com)
oracle并行模式(Parallel) - 幻星宇 - 博客园 (cnblogs.com)
Oracle Hints,Oracle并行模式(Parallel) / +parallel(t,4) / 在SQL调优中的重要作用许恒的博客-CSDN博客oracle并行parallel