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'
复制代码 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,来完成两个标量子查询的功能。- 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;
复制代码 语句改写如下:
[code]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 Index range scan on aa using idx_alertdate over ('2025-05-15' 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' |