涉及海量数据的查询SQL建议使用“数据库函数”封装并调用

一、问题描述

        生产环境中，核心业务表数据量随业务增长迅速膨胀，原统计查询SQL因执行计划劣化、索引失效而突然变慢。若按传统流程修复，需要优化SQL、测试、重新打包、发版，并且SQL优化往往需要多轮迭代、持续打磨；若每次调整都要重走发版流程，时间和风险成本将被无限放大。最终无法快速解决问题，易引发用户投诉。

二、“数据库函数封装”方案（代码仅需改一行）

       面对上述问题场景，我们可以将整条海量数据查询SQL封装为数据库函数。应用层仅需调用统一的函数接口，查询逻辑下沉至数据库内部；后续无论加Hint、改索引、调整执行计划，均可通过CREATE OR REPLACE FUNCTION秒级生效，无需再次测试、打包、发版，实现真正的零停机热更与持续迭代优化。

改造前，应用层SQL常规写法

<select id="querySysUserList" parameterType="cn.gdxiash.calculationdemo.entity.SysUser"
            resultType="cn.gdxiash.calculationdemo.entity.SysUser">
    SELECT * FROM SYS_USER 
    <where>
        <if test="name!=null and name!=''">
            NAME LIKE '%' || #{name} || '%'
        </if>
        <if test="age!=null and age>=0">
            AGE = #{age}
        </if>
    </where>
</select>

改造后，应用层SQL写法

<select id="querySysUserList" parameterType="cn.gdxiash.calculationdemo.entity.SysUser"
            resultType="cn.gdxiash.calculationdemo.entity.SysUser">
    SELECT * FROM TABLE(F_GET_SYS_USER_LIST(#{name},#{age}))
</select>

三、简单样例实现

1、创建SYS_USER表

CREATE TABLE "SHANGHAI"."SYS_USER"
(
"ID" VARCHAR2(36) NOT NULL,
"NAME" VARCHAR2(50),
"AGE" NUMBER(3,0),
CLUSTER PRIMARY KEY("ID")) STORAGE(ON "MAIN", CLUSTERBTR) ;

2、创建自定义类型

-- 创建自定义类型
CREATE OR REPLACE TYPE T_SYS_USER AS OBJECT(
ID VARCHAR2(36),
NAME VARCHAR2(50),
AGE NUMBER(3,0));
-- 创建自定义类型集合
CREATE OR REPLACE TYPE T_SYS_USER_LIST AS TABLE OF T_SYS_USER;

3、将业务SQL封装成函数（可随时通过REPLACE进行修改）

-- 创建查询函数
CREATE OR REPLACE FUNCTION F_GET_SYS_USER_LIST(
    P_NAME IN VARCHAR2 DEFAULT NULL,  -- 允许不传
    P_AGE  IN NUMBER   DEFAULT NULL   -- 允许不传
) RETURN T_SYS_USER_LIST
IS
    V_LIST T_SYS_USER_LIST := T_SYS_USER_LIST();
BEGIN
    SELECT T_SYS_USER(ID, NAME, AGE)
    BULK COLLECT INTO V_LIST
    FROM SYS_USER
    WHERE (P_NAME IS NULL OR NAME LIKE '%' || P_NAME || '%')
      AND (P_AGE  IS NULL OR AGE  = P_AGE);
    RETURN V_LIST;
END;

4、将代码的查询语句进行改造

原SQL

<select id="querySysUserList" parameterType="cn.gdxiash.calculationdemo.entity.SysUser"
            resultType="cn.gdxiash.calculationdemo.entity.SysUser">
    SELECT * FROM SYS_USER 
    <where>
        <if test="name!=null and name!=''">
            NAME LIKE '%' || #{name} || '%'
        </if>
        <if test="age!=null and age>=0">
            AGE = #{age}
        </if>
    </where>
</select>

将原SQL修改为查询数据库函数

<select id="querySysUserList" parameterType="cn.gdxiash.calculationdemo.entity.SysUser"
            resultType="cn.gdxiash.calculationdemo.entity.SysUser">
    SELECT * FROM TABLE(F_GET_SYS_USER_LIST(#{name},#{age}))
</select>