GreatSQL优化技巧：使用 FUNCTION 代替标量子查询
导语
本文案例涉及标量子查询，何为标量子查询呢？一般来说，介于 SELECT 与 FROM 之间的子查询就叫标量子查询，返回单行单列结果，可做为最外层 SELECT 结果集的一列。
举个例子：
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这部分即为标量子查询。
标量子查询的特点，主查询返回多少行，子查询就会被执行多少次，这是天然的嵌套查询，标量子查询的执行效率对SQL整体的效率影响很大。
因此如果主查询返回的结果集大时，不推荐使用标量子查询，会考虑将标量子查询改为外连接，优化器就有更多的可选择空间，可以考虑使用HASH JOIN, 而不使用NEST LOOP。
但也有一些案例不适用做外连接改写，本文案例就是不适合做外连接改写的例子，感兴趣的可接着往下看。
SQL 案例
SQL案例语句：(实际业务场景太复杂，截取片段来说明本文主题)
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 = '区县' 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'
为想做实验验证的小伙伴提供了建表语句与测试数据
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','区县',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;
语句分析
主查询对t1表按 alert_date 查询一个月的数据，数据量为400条左右，SELECT部分含两个标量子查询，子查询的关联条件为多个非等值关联，查询项为聚合汇总项count。这样的情况下，不好改写成外连接，好在主查询返回的行数不多，如果子查询效率高的话，这个语句就没有性能问题。
执行计划分析
本SQL执行计划如下：
*************************** 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总体耗时86s，两个标量子查询，执行计划显示其扫描方式为： Index range scan on ca (re-planned for each iteration)，优化器在SQL执行过程中重新规划执行计划，实际每次扫描行数为100000，为全表的行数，这表示并没有用到索引来定位，每次都是全表扫描，单次耗时超过160ms，很显然，这里不符合期待，子查询效率低，执行多次导致SQL整体性能差。
子查询为什么会全表扫描呢，从建表语句上可以看到accepttime 列上是有索引的，根据语义可以推断出，子查询只需要查询一天的数据，如果能用上索引，SQL整体性能一下子就能提升上来。推测优化器处理非等值关联的标量子查询时，可能算法上存在一定缺陷。
那既然明确了SQL怎样执行效率会高，现在就是想办法让SQL按照自己指定的执行计划来执行。
优化方案
那么怎样才能让子查询用到索引呢，在这里我想到了借助 FUNCTION 来实现，因为 FUNCTION 内部可以当作一个独立的SQL来执行，相当于对原SQL进行了拆分。
下面创建两个简单的 FUNCTION，来完成两个标量子查询的功能。
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 = '区县' 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'
执行计划如下：
*************************** 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 的执行计划并不会在主体计划中显示，但是从总体耗时来看，使用了FUNCTION 总体耗时9.5s，比原SQL性能(86s)提升了9倍。为什么使用FUNCTION 能提升SQL的执行效率，这是因为FUNCTION 内部SQL执行时使用了索引。下面为 FUNCATION 内部语句的执行计划。
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)
原标量子查询，单次执行耗时约160ms，而FUNCTION内部单次执行耗时15ms，因此SQL整体性能得到很大的提升。
优化总结
优化器给出的执行计划不符合预期时，可以有目的的改写SQL，使其按照既定的高效计划来执行，此处标量子查询使用了聚合函数，且非等值条件关联，无法改为外连接，选择使用FUNCTION来独立部分SQL，调整了执行计划，起到了很好的调优效果。
所谓SQL改写，并不是随便的来尝试，首先改写人要知道执行计划怎样走才能高效，其次就是为了要实现想要的执行计划，去调整SQL的写法来达到自己的目的。
349
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
