GreatSQL優(yōu)化技巧：使用 FUNCTION 代替標(biāo)量子查詢
GreatSQL優(yōu)化技巧：使用 FUNCTION 代替標(biāo)量子查詢
導(dǎo)語
本文案例涉及標(biāo)量子查詢，何為標(biāo)量子查詢呢？一般來說，介于 SELECT 與 FROM 之間的子查詢就叫標(biāo)量子查詢，返回單行單列結(jié)果，可做為最外層 SELECT 結(jié)果集的一列。
舉個(gè)例子：
SELECT e.employee_name,
(SELECT dept_name FROM hr_dept d WHERE d.dept_id=e.dept_id) AS deptname
FROM hr_employee e;
SELECT dept_name FROM hr_dept d WHERE d.dept_id=e.dept_id這部分即為標(biāo)量子查詢。
標(biāo)量子查詢的特點(diǎn)，主查詢返回多少行，子查詢就會(huì)被執(zhí)行多少次，這是天然的嵌套查詢，標(biāo)量子查詢的執(zhí)行效率對(duì)SQL整體的效率影響很大。
因此如果主查詢返回的結(jié)果集大時(shí)，不推薦使用標(biāo)量子查詢，會(huì)考慮將標(biāo)量子查詢改為外連接，優(yōu)化器就有更多的可選擇空間，可以考慮使用HASH JOIN, 而不使用NEST LOOP。
但也有一些案例不適用做外連接改寫，本文案例就是不適合做外連接改寫的例子，感興趣的可接著往下看。
SQL 案例
SQL案例語句：(實(shí)際業(yè)務(wù)場(chǎng)景太復(fù)雜，截取片段來說明本文主題)
SELECT (CASE
WHEN aa.alert_type = '8' AND aa.alarm_geotype = '全市' THEN
(concat('',
(SELECT COUNT(1)
FROM t2 ca
WHERE ca.accepttime >= concat(aa.alert_date, ' 00:00:00')
AND ca.accepttime <= concat(aa.alert_date, ' 23:59:59')
AND instr(ca.key_word, aa.key_word) > 0)))
WHEN aa.alert_type = '8' AND aa.alarm_geotype = '區(qū)縣' THEN
(concat('',
(SELECT COUNT(1)
FROM t2 ca
WHERE ca.accepttime >= concat(aa.alert_date, ' 00:00:00')
AND ca.accepttime <= concat(aa.alert_date, ' 23:59:59')
AND instr(ca.key_word, aa.key_word) > 0
AND instr(ca.city_, aa.city_) > 0)))
END) alert_value
FROM t1 aa
WHERE aa.ALERT_DATE >= '2025-05-15'
AND aa.ALERT_DATE <= '2025-06-15'
為想做實(shí)驗(yàn)驗(yàn)證的小伙伴提供了建表語句與測(cè)試數(shù)據(jù)
CREATE TABLE t1(alert_type VARCHAR(10),
alarm_geotype VARCHAR(20),
alert_date VARCHAR(10),
key_word VARCHAR(100),
city_ VARCHAR(100),
KEY idx_alertdate(alert_date)
);
CREATE TABLE t2(accepttime VARCHAR(50),
key_word VARCHAR(500),
city_ VARCHAR(100),
KEY idx_accepttime(accepttime)
);
DELIMITER //
CREATE OR REPLACE PROCEDURE p1() IS
BEGIN
FOR i IN 1..2000 LOOP
INSERT INTO t1 VALUES('8','全市',TO_CHAR(SYSDATE-RAND()*300,'yyyy-mm-dd'),'a',NULL);
END LOOP;
FOR i IN 1..2000 LOOP
INSERT INTO t1 VALUES('8','區(qū)縣',TO_CHAR(SYSDATE-RAND()*300,'yyyy-mm-dd'),'a','b');
END LOOP;
FOR i IN 1..100000 LOOP
INSERT INTO t2 VALUES(TO_CHAR(SYSDATE-RAND()*31,'yyyy-mm-dd hh24:mi:ss'),'a','b');
END LOOP;
END;
//
DELIMITER ;
CALL P1;
語句分析
主查詢對(duì)t1表按 alert_date 查詢一個(gè)月的數(shù)據(jù)，數(shù)據(jù)量為400條左右，SELECT部分含兩個(gè)標(biāo)量子查詢，子查詢的關(guān)聯(lián)條件為多個(gè)非等值關(guān)聯(lián)，查詢項(xiàng)為聚合匯總項(xiàng)count。這樣的情況下，不好改寫成外連接，好在主查詢返回的行數(shù)不多，如果子查詢效率高的話，這個(gè)語句就沒有性能問題。
執(zhí)行計(jì)劃分析
本SQL執(zhí)行計(jì)劃如下：
*************************** 1. row ***************************
EXPLAIN: -> Index range scan on aa using idx_alertdate over ('2025-05-15' <= alert_date <= '2025-06-15'), with index condition: ((aa.alert_date >= '2025-05-15') and (aa.alert_date <= '2025-06-15')) (cost=178.01 rows=395) (actual time=0.045..19.089 rows=395 loops=1)
-> Select #2 (subquery in projection; dependent)
-> Aggregate: count(1) (cost=2310.76 rows=1) (actual time=206.479..206.480 rows=1 loops=188)
-> Filter: ((ca.accepttime >= concat(aa.alert_date,' 00:00:00')) and (ca.accepttime <= concat(aa.alert_date,' 23:59:59')) and (instr(ca.key_word,aa.key_word) > 0)) (cost=1199.51 rows=11113) (actual time=2.724..206.195 rows=3141 loops=188)
-> Index range scan on ca (re-planned for each iteration) (cost=1199.51 rows=100033) (actual time=0.033..163.140 rows=100000 loops=188)
-> Select #3 (subquery in projection; dependent)
-> Aggregate: count(1) (cost=2310.76 rows=1) (actual time=228.676..228.677 rows=1 loops=207)
-> Filter: ((ca.accepttime >= concat(aa.alert_date,' 00:00:00')) and (ca.accepttime <= concat(aa.alert_date,' 23:59:59')) and (instr(ca.key_word,aa.key_word) > 0) and (instr(ca.city_,aa.city_) > 0)) (cost=1199.51 rows=11113) (actual time=0.078..228.418 rows=3088 loops=207)
-> Index range scan on ca (re-planned for each iteration) (cost=1199.51 rows=100033) (actual time=0.032..162.289 rows=100000 loops=207)
1 row in set, 7 warnings (1 min 26.19 sec)
SQL總體耗時(shí)86s，兩個(gè)標(biāo)量子查詢，執(zhí)行計(jì)劃顯示其掃描方式為： Index range scan on ca (re-planned for each iteration)，優(yōu)化器在SQL執(zhí)行過程中重新規(guī)劃執(zhí)行計(jì)劃，實(shí)際每次掃描行數(shù)為100000，為全表的行數(shù)，這表示并沒有用到索引來定位，每次都是全表掃描，單次耗時(shí)超過160ms，很顯然，這里不符合期待，子查詢效率低，執(zhí)行多次導(dǎo)致SQL整體性能差。
子查詢?yōu)槭裁磿?huì)全表掃描呢，從建表語句上可以看到accepttime 列上是有索引的，根據(jù)語義可以推斷出，子查詢只需要查詢一天的數(shù)據(jù)，如果能用上索引，SQL整體性能一下子就能提升上來。推測(cè)優(yōu)化器處理非等值關(guān)聯(lián)的標(biāo)量子查詢時(shí)，可能算法上存在一定缺陷。
那既然明確了SQL怎樣執(zhí)行效率會(huì)高，現(xiàn)在就是想辦法讓SQL按照自己指定的執(zhí)行計(jì)劃來執(zhí)行。
優(yōu)化方案
那么怎樣才能讓子查詢用到索引呢，在這里我想到了借助 FUNCTION 來實(shí)現(xiàn)，因?yàn)?FUNCTION 內(nèi)部可以當(dāng)作一個(gè)獨(dú)立的SQL來執(zhí)行，相當(dāng)于對(duì)原SQL進(jìn)行了拆分。
下面創(chuàng)建兩個(gè)簡單的 FUNCTION，來完成兩個(gè)標(biāo)量子查詢的功能。
DELIMITER //
CREATE OR REPLACE FUNCTION getcntbyall(v_date varchar(20),v_keyword varchar(50))
RETURN INT
IS
cnt INT:=0;
BEGIN
SELECT COUNT(1) INTO cnt
FROM t2 ca
WHERE ca.ACCEPTTIME >=
concat(v_date, ' 00:00:00')
AND ca.ACCEPTTIME <=
concat(v_date, ' 23:59:59')
AND instr(ca.key_word, v_keyword) > 0;
RETURN cnt;
END;
//
DELIMITER ;
DELIMITER //
CREATE OR REPLACE FUNCTION getcntbycity(v_date varchar(20),v_keyword varchar(50),v_city varchar(50) )
RETURN INT
IS
cnt INT:=0;
BEGIN
SELECT COUNT(1) INTO cnt
FROM t2 ca
WHERE ca.ACCEPTTIME >=
concat(v_date, ' 00:00:00')
AND ca.ACCEPTTIME <=
concat(v_date, ' 23:59:59')
AND instr(ca.key_word, v_keyword) > 0
AND instr(ca.city_, v_city) > 0;
RETURN cnt;
END;
//
DELIMITER ;
語句改寫如下：
SELECT (CASE
WHEN aa.alert_type = '8' AND aa.alarm_geotype = '全市' THEN
(concat('',
getcntbyall(aa.alert_date,aa.key_word)))
WHEN aa.alert_type = '8' AND aa.alarm_geotype = '區(qū)縣' THEN
(concat('',
getcntbycity(aa.alert_date,aa.key_word,aa.city_)))
END) alert_value
FROM t1 aa
WHERE aa.ALERT_DATE >= '2025-05-15'
AND aa.ALERT_DATE <= '2025-06-15'
執(zhí)行計(jì)劃如下：
*************************** 1. row ***************************
EXPLAIN: -> Index range scan on aa using idx_alertdate over ('2025-05-15' <= alert_date <= '2025-06-15'), with index condition: ((aa.alert_date >= '2025-05-15') and (aa.alert_date <= '2025-06-15')) (cost=178.01 rows=395) (actual time=0.300..10.349 rows=395 loops=1)
1 row in set (9.53 sec)
FUNCTION 的執(zhí)行計(jì)劃并不會(huì)在主體計(jì)劃中顯示，但是從總體耗時(shí)來看，使用了FUNCTION 總體耗時(shí)9.5s，比原SQL性能(86s)提升了9倍。為什么使用FUNCTION 能提升SQL的執(zhí)行效率，這是因?yàn)镕UNCTION 內(nèi)部SQL執(zhí)行時(shí)使用了索引。下面為 FUNCATION 內(nèi)部語句的執(zhí)行計(jì)劃。
greatsql> explain analyze
-> SELECT count(1)
-> FROM t2 ca
-> WHERE ca.ACCEPTTIME >=
-> concat('2025-05-30', ' 00:00:00')
-> AND ca.ACCEPTTIME <=
-> concat('2025-05-30', ' 23:59:59')
-> AND instr(ca.key_word, 'a') > 0\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(1) (cost=1768.51 rows=1) (actual time=15.010..15.010 rows=1 loops=1)
-> Filter: (instr(ca.key_word,'a') > 0) (cost=1447.01 rows=3215) (actual time=0.069..14.607 rows=3215 loops=1)
-> Index range scan on ca using idx_accepttime over ('2025-05-30 00:00:00' <= accepttime <= '2025-05-30 23:59:59'), with index condition: ((ca.accepttime >= <cache>(concat('2025-05-30',' 00:00:00'))) and (ca.accepttime <= <cache>(concat('2025-05-30',' 23:59:59')))) (cost=1447.01 rows=3215) (actual time=0.059..12.758 rows=3215 loops=1)
1 row in set (0.02 sec)
原標(biāo)量子查詢，單次執(zhí)行耗時(shí)約160ms，而FUNCTION內(nèi)部單次執(zhí)行耗時(shí)15ms，因此SQL整體性能得到很大的提升。
優(yōu)化總結(jié)
優(yōu)化器給出的執(zhí)行計(jì)劃不符合預(yù)期時(shí)，可以有目的的改寫SQL，使其按照既定的高效計(jì)劃來執(zhí)行，此處標(biāo)量子查詢使用了聚合函數(shù)，且非等值條件關(guān)聯(lián)，無法改為外連接，選擇使用FUNCTION來獨(dú)立部分SQL，調(diào)整了執(zhí)行計(jì)劃，起到了很好的調(diào)優(yōu)效果。
所謂SQL改寫，并不是隨便的來嘗試，首先改寫人要知道執(zhí)行計(jì)劃怎樣走才能高效，其次就是為了要實(shí)現(xiàn)想要的執(zhí)行計(jì)劃，去調(diào)整SQL的寫法來達(dá)到自己的目的。
Enjoy GreatSQL ??
關(guān)于 GreatSQL
GreatSQL是適用于金融級(jí)應(yīng)用的國內(nèi)自主開源數(shù)據(jù)庫，具備高性能、高可靠、高易用性、高安全等多個(gè)核心特性，可以作為MySQL或Percona Server的可選替換，用于線上生產(chǎn)環(huán)境，且完全免費(fèi)并兼容MySQL或Percona Server。
相關(guān)鏈接： GreatSQL社區(qū) Gitee GitHub Bilibili
GreatSQL社區(qū)：
社區(qū)博客有獎(jiǎng)?wù)鞲逶斍椋?a target="_blank" rel="noopener nofollow">https://greatsql.cn/thread-100-1-1.html
技術(shù)交流群：
微信：掃碼添加
GreatSQL社區(qū)助手微信好友，發(fā)送驗(yàn)證信息加群。
浙公網(wǎng)安備 33010602011771號(hào)