一、問題描述
????????生產環境中,核心業務表數據量隨業務增長迅速膨脹,原統計查詢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
ISV_LIST T_SYS_USER_LIST := T_SYS_USER_LIST();
BEGINSELECT T_SYS_USER(ID, NAME, AGE)BULK COLLECT INTO V_LISTFROM SYS_USERWHERE (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>