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语句写成这样的原因如下图，排除红色的两块区域，剩下的就都是符合条件的记录

image-20211222163537923.png

业务问题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 Hints，Oracle并行模式（Parallel） / +parallel(t,4) / 在SQL调优中的重要作用许恒的博客-CSDN博客oracle并行parallel

来源：https://juejin.cn/post/7044808527180726286
posted @ 2022-12-20 23:42  程序员小明1024  阅读(719)  评论(0)  编辑  收藏  举报