你肯定見(jiàn)過(guò)這種 SQL。

查一次三十秒，一天還跑八百回，而且每次吐出來(lái)的結(jié)果一模一樣。你盯著執(zhí)行計(jì)劃看半天，索引加了，統(tǒng)計(jì)信息收了，連 VACUUM 都跑了一遍，沒(méi)轍，照樣慢。

為啥？因?yàn)檫@壓根不是執(zhí)行計(jì)劃的鍋。毛病出在 SQL 本身在干重復(fù)勞動(dòng)：同一份結(jié)果算了一遍又一遍，或者一條寫(xiě)得稀爛的語(yǔ)句你還沒(méi)權(quán)限改，它就只能一遍遍那么低效地跑著。這種慢，優(yōu)化器是真救不了，得換個(gè)思路。別讓它重復(fù)，把算過(guò)的結(jié)果直接拿來(lái)用?？臻g換時(shí)間，就這么簡(jiǎn)單。

這篇文章聊三個(gè)專門(mén)干這活的好東西：物化視圖、Query Mapping、函數(shù)結(jié)果集緩存。

前言

物化視圖、Query Mapping、函數(shù)結(jié)果集緩存，這三個(gè)功能都是人大金倉(cāng)的 KingbaseES 數(shù)據(jù)庫(kù)提供的性能優(yōu)化手段。

為了讓你更清晰地理解它們，我將這三個(gè)功能整理成了一份表格：

下面，我來(lái)為你逐一詳細(xì)解釋每個(gè)功能。

1. Query Mapping：不改代碼也能優(yōu)化SQL

這是一個(gè)非常實(shí)用的功能，它允許DBA（數(shù)據(jù)庫(kù)管理員）在不修改應(yīng)用程序源碼的情況下，優(yōu)化有性能問(wèn)題的SQL語(yǔ)句。

• 工作原理：你可以在數(shù)據(jù)庫(kù)中預(yù)先創(chuàng)建一條“映射規(guī)則”，指定一個(gè)“源SQL”（低效的）和“目標(biāo)SQL”（高效的）。當(dāng)數(shù)據(jù)庫(kù)收到匹配的“源SQL”時(shí)，會(huì)自動(dòng)替換成“目標(biāo)SQL”來(lái)執(zhí)行。

• 兩種工作模式：

  • TEXT（文本）模式：進(jìn)行簡(jiǎn)單的字符串匹配和替換，速度快，適合快速解決一些明確的SQL問(wèn)題。

  • SEMANTICS（語(yǔ)義）模式：會(huì)進(jìn)行語(yǔ)法和語(yǔ)義檢查，替換更精準(zhǔn)、更安全，適合對(duì)準(zhǔn)確性要求高的場(chǎng)景。

• 典型應(yīng)用場(chǎng)景：

  • 性能調(diào)優(yōu)：例如，自動(dòng)將耗時(shí)的 UNION 操作替換為更快的 UNION ALL。

  • 數(shù)據(jù)庫(kù)遷移：在將其他數(shù)據(jù)庫(kù)遷移到KingbaseES時(shí)，自動(dòng)將源庫(kù)的SQL語(yǔ)法轉(zhuǎn)換為KingbaseES兼容的語(yǔ)法。

2. 物化視圖：為復(fù)雜查詢加速

你可以把物化視圖看作一個(gè)“快照”。它把一個(gè)復(fù)雜、耗時(shí)的查詢結(jié)果，預(yù)先計(jì)算好并像普通表一樣存儲(chǔ)在數(shù)據(jù)庫(kù)中。

• 與普通視圖的區(qū)別：普通視圖只是一個(gè)“虛擬表”，每次查詢時(shí)都會(huì)重新執(zhí)行SQL去拿數(shù)據(jù)。而物化視圖存儲(chǔ)的是真實(shí)的數(shù)據(jù)，因此查詢它的速度和在普通表中查詢一樣快。

• 數(shù)據(jù)刷新：由于物化視圖是靜態(tài)的“快照”，當(dāng)基表（源表）數(shù)據(jù)變化后，它需要被刷新才能保持最新。KingbaseES支持手動(dòng)或自動(dòng)的刷新機(jī)制。

3. 函數(shù)結(jié)果緩存：避免重復(fù)計(jì)算

這個(gè)功能專注于優(yōu)化SQL語(yǔ)句中函數(shù)的執(zhí)行效率。

• 工作原理：當(dāng)一條SQL語(yǔ)句在執(zhí)行時(shí)，如果多次調(diào)用同一個(gè)確定性函數(shù)（即輸入相同，輸出也相同的函數(shù)，如 immutable 或 stable 類(lèi)型的函數(shù)），并且參數(shù)也相同，數(shù)據(jù)庫(kù)只會(huì)真正執(zhí)行一次，后續(xù)調(diào)用直接返回緩存的結(jié)果。

• 適用場(chǎng)景：特別適用于那些被頻繁調(diào)用、但計(jì)算邏輯復(fù)雜的函數(shù)，尤其是在處理大量數(shù)據(jù)行時(shí)，能顯著減少CPU開(kāi)銷(xiāo)。

一、先別急，有個(gè)事你得拎清楚

一個(gè)個(gè)看之前，先把一件事想明白：這哥仨治的是同一種病，重復(fù)計(jì)算。

優(yōu)化器再神，也沒(méi)法幫你跳過(guò)"本來(lái)該算一次、卻被迫算了一百次"這種事。它能替你挑最快的路，可它不會(huì)說(shuō)"這條路我都走過(guò)八百遍了，結(jié)果我背下來(lái)了"。這恰恰是這三個(gè)功能補(bǔ)的位。

它們各自盯的層面不一樣。物化視圖管的是一整個(gè)查詢的結(jié)果，算一次存好，誰(shuí)來(lái)查都給它現(xiàn)成的。Query Mapping 更靠前，SQL 還沒(méi)進(jìn)優(yōu)化器呢，就被它偷偷換掉了。函數(shù)結(jié)果集緩存范圍最小，就管一條 SQL 里被反復(fù)調(diào)用的那個(gè)函數(shù)。

所以差別其實(shí)就倆字，范圍。范圍最大的是物化視圖，最小的是函數(shù)緩存，Query Mapping 夾在中間管語(yǔ)句。下面挨個(gè)說(shuō)。

二、物化視圖：把費(fèi)勁算的結(jié)果先存下來(lái)

先說(shuō)物化視圖。

這名字聽(tīng)著唬人，原理土得很：把一個(gè)查詢的結(jié)果，實(shí)打?qū)嵉卮嫦聛?lái)。普通視圖你知道吧，那玩意兒是個(gè)"別名"，不存數(shù)據(jù)，每次查照樣現(xiàn)算。物化視圖不一樣，它是真存，小數(shù)據(jù)放內(nèi)存，大的落磁盤(pán)。

它最拿手的活兒就一件：把那些算起來(lái)要命的操作，比如大表連接、復(fù)雜分組，提前算好擱那兒。下次再有人查同樣的東西，直接拿現(xiàn)成的，省下重算這一大筆開(kāi)銷(xiāo)。

舉個(gè)我見(jiàn)過(guò)的真事。訂單表 orders，幾千萬(wàn)行，業(yè)務(wù)天天要按用戶匯總訂單數(shù)和金額。寫(xiě)法沒(méi)毛病，就是每次都得全表掃加分組：

-- 每次現(xiàn)算：掃全表 + 分組聚集，數(shù)據(jù)一大就磨嘰
SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total
FROM   orders
GROUP BY user_id;

慢。那建個(gè)物化視圖，把這匯總結(jié)果預(yù)先算出來(lái)存著：

-- 建物化視圖：把耗時(shí)的聚集預(yù)先算好、落盤(pán)
CREATE MATERIALIZED VIEW mv_order_summary AS
SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total
FROM   orders
GROUP BY user_id;

-- 查詢直接打物化視圖，秒級(jí)返回
SELECT * FROM mv_order_summary WHERE user_id = 1001;

想查得更快，給它建個(gè)索引，跟普通表一個(gè)套路：

-- 給物化視圖建索引，按用戶查就快了
CREATE INDEX idx_mv_user ON mv_order_summary(user_id);

但是，重點(diǎn)來(lái)了，也是好多人栽跟頭的地方：底表數(shù)據(jù)變了，物化視圖不會(huì)自己跟著變。 本數(shù)據(jù)庫(kù)的物化視圖不支持自動(dòng)更新，也沒(méi)有增量更新這一說(shuō)，你只能手動(dòng)刷新，而且一刷就是全量重算：

-- 底表更新后，手動(dòng)刷新（全量重算）
REFRESH MATERIALIZED VIEW mv_order_summary;

-- 想刷新時(shí)還能讓人并發(fā)查、不鎖表，加 CONCURRENTLY（前提是有唯一索引）
CREATE UNIQUE INDEX uk_mv_user ON mv_order_summary(user_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_summary;

所以我跟你講，物化視圖是個(gè)"挑食"的優(yōu)化，不是什么時(shí)候都能往上懟。它最香的就兩種情況。

一種是底表更新少、但查詢又重又勤。報(bào)表、看板就是它的親兒子，數(shù)據(jù)一天才喂一兩次，白天被查成千上萬(wàn)遍，預(yù)先算好等于白撿。

另一種是查外部庫(kù)的表。跨庫(kù)掃描本來(lái)就慢得要死，與其每次都跑外邊去撈，不如用物化視圖把數(shù)據(jù)搬到本地存著，查的時(shí)候走本地：

-- 把訪問(wèn)慢的外部表數(shù)據(jù)，緩存成本地物化視圖（記得定期手動(dòng)刷新保鮮）
CREATE MATERIALIZED VIEW mv_customer AS
SELECT * FROM fdw_customer;

反過(guò)來(lái)，如果你的表分分鐘都在寫(xiě)，刷新又追不上變化，那就別硬上了。緩存剛刷完就過(guò)期，純屬給自己找不痛快。

三、Query Mapping：SQL 還沒(méi)進(jìn)優(yōu)化器，先偷梁換柱

再說(shuō) Query Mapping，這個(gè)我個(gè)人最喜歡，因?yàn)樗?quot;陰"。

啥意思呢，它讓你提前把"源 SQL → 目標(biāo) SQL"的映射關(guān)系存進(jìn)系統(tǒng)表。用戶敲進(jìn)來(lái)的語(yǔ)句只要匹配上，數(shù)據(jù)庫(kù)就偷偷換成目標(biāo)語(yǔ)句去跑，用戶全程毫無(wú)察覺(jué)。是不是有點(diǎn)像給 SQL 戴了個(gè)面具。

它能干兩件事，都特別實(shí)用。一是 SQL 調(diào)優(yōu)：碰到條寫(xiě)得很爛的 SQL，偏偏你還沒(méi)權(quán)限改源碼（八成是第三方系統(tǒng)發(fā)的），這時(shí)候建個(gè)映射，偷偷把它換成等價(jià)但高效的寫(xiě)法，神不知鬼不覺(jué)。二是數(shù)據(jù)庫(kù)遷移：把別家的方言語(yǔ)法，翻譯成本庫(kù)能跑的語(yǔ)法。

它分兩個(gè)級(jí)別，這倆你得記牢。

TEXT 級(jí)別，就是純字符串匹配，啥都不檢查，原樣存原始 SQL 和目標(biāo) SQL。SEMANTICS 級(jí)別高級(jí)點(diǎn)，會(huì)走一遍詞法語(yǔ)法語(yǔ)義檢查，存的是查詢樹(shù)，也就是 SQL 解析后的那個(gè)內(nèi)部形態(tài)。

這里有個(gè)坑我必須提醒你：想跟 Hint 一起用，只能選 TEXT。因?yàn)?Hint 是寫(xiě)在注釋里的，SEMANTICS 存的是查詢樹(shù)，注釋根本留不住，Hint 也就跟著廢了。還有，SEMANTICS 現(xiàn)在搞不定帶 rownum 的語(yǔ)句，碰上記得繞。

用法不復(fù)雜，三步。先開(kāi)開(kāi)關(guān)，再建規(guī)則，然后該查查：

-- 第一步：配置文件里開(kāi)啟（kingbase.conf）
--   enable_query_rule = on

-- 第二步：建一條映射，把低效的 NOT IN 換成高效的 NOT EXISTS
SELECT create_query_rule(
    'qm_tune1',
    'SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2)',          -- 源 SQL（低效）
    'SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id)',  -- 目標(biāo) SQL（高效）
    true,    -- 這條規(guī)則生不生效
    'text'   -- 級(jí)別：text 或 semantics
);

規(guī)則一立，用戶再發(fā)那條 NOT IN，底下實(shí)際跑的是 NOT EXISTS，干凈利落。

還能玩參數(shù)化，這點(diǎn)我挺喜歡。源和目標(biāo)里都能用 $1、$2 代表入?yún)ⅲヅ涞臅r(shí)候按位置對(duì)上：

-- 源 SQL 帶 3 個(gè)參數(shù)，目標(biāo) SQL 只關(guān)心 val=$3 的計(jì)數(shù)
SELECT create_query_rule(
    'qm_count',
    'select id,val from t1 where id<$1 and id>$2 and val=$3',
    'select count(0) from t1 where val=$3',
    true, 'text'
);

連參數(shù)順序都能給你打亂交換，靈活得很：

-- 把 id<$1 and val=$2 換成 id<$2 and val=$1，入?yún)㈨樞驅(qū)φ{(diào)
SELECT create_query_rule(
    'qm_swap',
    'select * from t2 where id<$1 and val=$2',
    'select * from t2 where id<$2 and val=$1',
    true, 'text'
);

更絕的是，它還能干"條件下推"這種深度改寫(xiě)。比如外層一個(gè)連接條件，正常它壓不進(jìn) UNION 子查詢里去；建條映射，把語(yǔ)句改成 LATERAL 形式（就是允許內(nèi)層引用外層數(shù)據(jù)那種寫(xiě)法），條件就能推進(jìn)去，少掃一大堆沒(méi)用的數(shù)據(jù)。想看替換之后到底走的啥計(jì)劃，加個(gè)標(biāo)記就行：

-- 看替換后走的是什么計(jì)劃
explain (usingquerymapping)
SELECT count(0) FROM t1, (...) AS v WHERE t1.id = v.id;

規(guī)則管起來(lái)也省心，幾個(gè)函數(shù)來(lái)回用：

SELECT drop_query_rule('qm_tune1');     -- 刪掉某條規(guī)則
SELECT enable_query_rule('qm_tune1');   -- 讓某條規(guī)則生效
SELECT disable_query_rule('qm_tune1');  -- 暫時(shí)關(guān)掉某條規(guī)則
SELECT drop_query_rule();               -- 不傳參 = 刪光所有規(guī)則，悠著點(diǎn)

四、函數(shù)結(jié)果集緩存：同一個(gè)函數(shù)調(diào)一萬(wàn)遍，只算一遍

最后這個(gè)，函數(shù)結(jié)果集緩存，專門(mén)對(duì)付函數(shù)。

你有沒(méi)有寫(xiě)過(guò)這種代碼：寫(xiě)了個(gè)函數(shù)，然后一條 SQL 里反反復(fù)復(fù)調(diào)它。SELECT 里調(diào)一次，WHERE 里又調(diào)一次，或者對(duì)著一堆入?yún)€(gè)調(diào)。函數(shù)本身要是就慢，再乘上調(diào)那么多次，這條 SQL 直接慢到你想砸鍵盤(pán)。

這功能治的就是這個(gè)。原理不繞：函數(shù)一旦標(biāo)成 IMMUTABLE 或者 STABLE，也就是同樣的入?yún)⒂肋h(yuǎn)返回同樣的結(jié)果，數(shù)據(jù)庫(kù)在一條 SQL 跑的期間，就會(huì)把"函數(shù) + 入?yún)?quot;算出來(lái)的結(jié)果存起來(lái)。后面再碰到一模一樣的入?yún)?，直接吐緩存，函?shù)體壓根不執(zhí)行。

得解釋下這幾個(gè)詞，不然容易懵。

IMMUTABLE 最嚴(yán)，入?yún)⒁粯咏Y(jié)果就一樣，跟表、跟時(shí)間都無(wú)關(guān)，絕對(duì)的。STABLE 松一點(diǎn)，只要同一條 SQL 里結(jié)果不變就行，比如去查一張很少改的配置表。VOLATILE 就沒(méi)戲了，結(jié)果會(huì)變的（取當(dāng)前時(shí)間那種），沒(méi)法緩存。

想命中緩存，三個(gè)條件得湊齊：函數(shù)得是 IMMUTABLE 或 STABLE；返回值得是單個(gè)值，不能是集合；入?yún)e超過(guò) 16 個(gè)。

來(lái)，建個(gè)標(biāo)了 IMMUTABLE 的折扣函數(shù)：

-- IMMUTABLE 函數(shù)：同樣的 (price, rate) 永遠(yuǎn)算出同樣的折扣價(jià)
CREATE OR REPLACE FUNCTION cal_discount(price numeric, rate numeric)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT round(price * rate, 2);
$$;

開(kāi)關(guān)一開(kāi)，寫(xiě)條會(huì)反復(fù)調(diào)它的 SQL：

-- 開(kāi)啟函數(shù)結(jié)果集緩存，并設(shè)個(gè)緩存上限
SET function_result_cache = on;
SET function_cache_number = 1000;

-- 這條 SQL 里，同一組 (amount, 0.9) 入?yún)⒌恼{(diào)用只算一次，其余命中緩存
SELECT id, cal_discount(amount, 0.9) AS dp
FROM   orders
WHERE  cal_discount(amount, 0.9) > 100;

再補(bǔ)個(gè) STABLE 的，讀張幾乎不變的匯率表，一個(gè)道理：

-- STABLE 函數(shù)：按幣種查匯率，幣種沒(méi)變結(jié)果就不變
CREATE OR REPLACE FUNCTION get_rate(currency text)
RETURNS numeric
LANGUAGE sql
STABLE
AS $$
    SELECT rate FROM exchange_rate WHERE cur = currency;
$$;

有個(gè)事得重點(diǎn)強(qiáng)調(diào)，是這功能跟物化視圖最不一樣的地方：緩存只在單條 SQL 執(zhí)行期間活著，這條 SQL 一跑完就沒(méi)了。 所以它治不了跨會(huì)話的重復(fù)，它就管一條 SQL 內(nèi)部那點(diǎn)事。想跨會(huì)話緩存？那是物化視圖的活兒，別搞混了。

兩個(gè)開(kāi)關(guān)也好懂，function_result_cache 管開(kāi)不開(kāi)，function_cache_number 管最多存幾個(gè)。

五、那到底用哪個(gè)？看場(chǎng)景下菜碟

道理講完了，真到用的時(shí)候怎么選，才是關(guān)鍵。我給你列個(gè)表，對(duì)號(hào)入座就行：

再啰嗦幾句掏心窩的。

報(bào)表、查多寫(xiě)少的數(shù)據(jù)，閉眼選物化視圖。但刷新頻率你自己心里得有數(shù)，別數(shù)據(jù)都隔夜了還在用昨天的結(jié)果，出了事鍋可是你的。

沒(méi)法改源碼、又非得優(yōu)化某條語(yǔ)句，Query Mapping 頂上。這玩意兒最大的好處是無(wú)侵入，應(yīng)用那邊一行都不用動(dòng)，我個(gè)人特別偏愛(ài)它。

一個(gè)計(jì)算函數(shù)被調(diào)成千上萬(wàn)次的，開(kāi)函數(shù)結(jié)果集緩存。開(kāi)之前一定確認(rèn)函數(shù)確實(shí)是 IMMUTABLE 或 STABLE，別到時(shí)候結(jié)果錯(cuò)了還查不出哪出的毛病。

還有，這三個(gè)不是互斥的，能搭著用。拿 Query Mapping 把低效語(yǔ)句改寫(xiě)成走物化視圖，效果直接疊加，爽得很。

六、收個(gè)尾

說(shuō)到底，這三個(gè)東西干的是同一件事：跟重復(fù)較勁。

物化視圖管結(jié)果級(jí)的重復(fù)，提前算好存著；Query Mapping 管語(yǔ)句級(jí)的重復(fù)，從源頭換掉爛寫(xiě)法；函數(shù)緩存管調(diào)用級(jí)的重復(fù)，一條 SQL 內(nèi)別傻算。仨人各占一段賽道，誰(shuí)也替不了誰(shuí)。

真想用好它們，功夫其實(shí)不在背語(yǔ)法上，而在你能不能先想明白一件事：我這條 SQL 慢，到底慢在哪種重復(fù)上。想清楚了，對(duì)癥下藥，性能蹦一個(gè)數(shù)量級(jí)，真不是吹的。

下次再碰到那種優(yōu)化器都救不了的慢查詢，先別急著罵優(yōu)化器，它也挺冤的。先問(wèn)自己一句：這活兒，是不是在反復(fù)干同一件事？是的話，這仨里頭，總有一個(gè)能收拾它。

這三個(gè)功能共同構(gòu)成了KingbaseES數(shù)據(jù)庫(kù)一套強(qiáng)大的性能優(yōu)化工具集：

• Query Mapping 讓你能低成本、無(wú)侵入地解決棘手的SQL性能問(wèn)題。

• 物化視圖 為高頻、復(fù)雜的查詢提供了極速的訪問(wèn)體驗(yàn)。

• 函數(shù)結(jié)果緩存 則通過(guò)避免重復(fù)計(jì)算來(lái)提升SQL的整體執(zhí)行效率。

到此這篇關(guān)于SQL慢得像蝸牛怎么辦？KingbaseES的物化視圖、QueryMapping和函數(shù)緩存優(yōu)化實(shí)戰(zhàn)的文章就介紹到這了,更多相關(guān)KingbaseES的性能優(yōu)化利器內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論