Oracle数据库业务SQL优化实战-时间区间查询案例 Oracle数据库业务SQL优化实战-时

背景查询字段其实比较多，我选择聚焦在瓶颈点上，让我们开始吧
功能背景简介： ? 我们在一个进入数据中心的入口设置了一台记录人员进出的机器，由保卫员操作记录人员进出（通过换取通关卡的方式，在换取通关卡时，记录进入时间，在归还通关卡时，记录离开时间），业务方需要知道某段时间内在数据中心内的人数、次数、具体进入的人、进入的人的进入时长等。
按照功能背景，我们建立了一张表

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 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'

改写后再次查看执行计划，发现还是不走索引
步骤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数据库业务SQL优化实战-时间区间查询案例】Oracle Hints，Oracle并行模式（Parallel） /+parallel(t,4)/ 在SQL调优中的重要作用_许恒的博客-CSDN博客_oracle并行parallel