找回密码
 立即注册
首页 业界区 安全 GreatSQL优化技巧:使用 FUNCTION 代替标量子查询 ...

GreatSQL优化技巧:使用 FUNCTION 代替标量子查询

辈霖利 2025-7-16 08:47:29
GreatSQL优化技巧:使用 FUNCTION 代替标量子查询

导语

本文案例涉及标量子查询,何为标量子查询呢?一般来说,介于 SELECT 与 FROM 之间的子查询就叫标量子查询,返回单行单列结果,可做为最外层 SELECT 结果集的一列。
举个例子:
  1. SELECT e.employee_name,
  2. (SELECT dept_name FROM hr_dept d WHERE d.dept_id=e.dept_id) AS deptname
  3. 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案例语句:(实际业务场景太复杂,截取片段来说明本文主题)
  1. SELECT (CASE
  2.          WHEN aa.alert_type = '8' AND aa.alarm_geotype = '全市' THEN
  3.           (concat('',
  4.                   (SELECT COUNT(1)
  5.                      FROM t2 ca
  6.                     WHERE ca.accepttime >= concat(aa.alert_date, ' 00:00:00')
  7.                       AND ca.accepttime <= concat(aa.alert_date, ' 23:59:59')
  8.                       AND instr(ca.key_word, aa.key_word) > 0)))
  9.          WHEN aa.alert_type = '8' AND aa.alarm_geotype = '区县' THEN
  10.           (concat('',
  11.                   (SELECT COUNT(1)
  12.                      FROM t2 ca
  13.                     WHERE ca.accepttime >= concat(aa.alert_date, ' 00:00:00')
  14.                       AND ca.accepttime <= concat(aa.alert_date, ' 23:59:59')
  15.                       AND instr(ca.key_word, aa.key_word) > 0
  16.                       AND instr(ca.city_, aa.city_) > 0)))
  17.        END) alert_value
  18.   FROM t1 aa
  19. WHERE aa.ALERT_DATE >= '2025-05-15'
  20.    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,来完成两个标量子查询的功能。
  1. CREATE TABLE t1(alert_type VARCHAR(10),
  2. alarm_geotype VARCHAR(20),
  3. alert_date VARCHAR(10),
  4. key_word VARCHAR(100),
  5. city_ VARCHAR(100),
  6. KEY idx_alertdate(alert_date)
  7. );
  8. CREATE TABLE t2(accepttime VARCHAR(50),
  9. key_word VARCHAR(500),
  10. city_ VARCHAR(100),
  11. KEY idx_accepttime(accepttime)
  12. );
  13. DELIMITER //
  14. CREATE OR REPLACE PROCEDURE p1() IS
  15. BEGIN
  16.   FOR i IN 1..2000 LOOP
  17.     INSERT INTO t1 VALUES('8','全市',TO_CHAR(SYSDATE-RAND()*300,'yyyy-mm-dd'),'a',NULL);
  18.   END LOOP;
  19.   FOR i IN 1..2000 LOOP
  20.     INSERT INTO t1 VALUES('8','区县',TO_CHAR(SYSDATE-RAND()*300,'yyyy-mm-dd'),'a','b');
  21.   END LOOP;
  22.   FOR i IN 1..100000 LOOP
  23.     INSERT INTO t2 VALUES(TO_CHAR(SYSDATE-RAND()*31,'yyyy-mm-dd hh24:mi:ss'),'a','b');
  24.   END LOOP;
  25. END;
  26. //
  27. DELIMITER ;
  28. 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'
您需要登录后才可以回帖 登录 | 立即注册