GreatSQL优化技巧：手动实现谓词下推

GreatSQL优化技巧：手动实现谓词下推

导语

最近总是听到用 AI 来优化 SQL 的言论，今天心血来潮试了一下，把表结构、统计信息、SQL语句、执行计划都告诉AI，AI给出了一大堆的建议，它会从索引，语句改写，参数调整各个方面给出优化策略，看似面面俱到，但是如果不懂优化理论，随便使用其给出的优化建议，可能的结果就是，一顿操作猛如虎，一看战绩零杠五。所以本人还是老老实实的总结优化技巧吧，这些案例技巧或许某天会成为 AI 的营养餐。
SQL 案例

SQL 案例语句：(实际业务场景太复杂，截取片段来说明本文主题)

1. SELECT ta.*, tb.*
2.   FROM (SELECT *
3.           FROM (SELECT a.contactid,
4.                        a.subs_number,
5.                        a.log_time,
6.                        ROW_NUMBER() OVER(PARTITION BY a.contactid, a.subs_number ORDER BY a.log_time DESC) rn,
7.                        a.log_id
8.                   FROM a
9.                  WHERE a.contactid IS NOT NULL
10.                    AND a.log_time >= '2025-05-30 00:00:00'
11.                    AND a.log_time <= '2025-06-02') cc
12.          WHERE rn = 1) ta
13.   LEFT JOIN (SELECT b.*,
14.                     ROW_NUMBER() OVER(PARTITION BY b.basesn ORDER BY b.create_time DESC) rn
15.                FROM b
16.               WHERE b.create_time IS NOT NULL) tb
17.     ON ta.contactid = tb.basesn
18.    AND tb.rn = 1

复制代码

语句分析

SQL有两个派生表ta，tb，这两表做left join 左外连接。派生表ta，作为左外连接的左表，内层表a有log_time过滤条件，该列有单列索引，查询两三天的数据数据量命中几百行，查询一个月左右的数据量命中几千到1万左右。派生表tb，作为左外连接的右表，内层表b全表百万级别的数据量，条件create_time is not null过滤性不好。两个派生表都使用了窗口函数ROW_NUMBER()
执行计划分析

语句实际执行计划如下：

1.    CREATE TABLE a(log_id bigint,CONTACTID INT,subs_number INT,log_time datetime,PRIMARY KEY (log_id),KEY idx_logtime(log_time));
2.    CREATE TABLE b(id bigint PRIMARY KEY,basesn INT,create_time datetime,KEY idx_basesn(basesn));
3.    
4.    delimiter //
5.    
6.    CREATE OR REPLACE PROCEDURE P1() IS
7.    BEGIN
8.      FOR I IN 1 .. 10000 LOOP
9.         INSERT INTO a(log_id,contactid,subs_number,log_time) VALUES(i,TRUNC(rand()*8000),TRUNC(rand()*9000),SYSDATE-rand()*90);
10.      END LOOP;
11.      
12.      FOR I IN 1 .. 1000000 LOOP
13.         INSERT INTO b(id,basesn,create_time) VALUES(i,TRUNC(rand()*800000),SYSDATE-rand()*90);
14.      END LOOP;
15.    END;
16.    //
17.    delimiter ;

复制代码

两表ta，tb使用Nested loop方式进行连接，ta表作为外层驱动表，结果集rows为331。 tb表作为内层循环表，循环扫描331次，这些都消耗不多。
此SQL耗时多的步骤在对tb的内层表b进行排序(Sort)，做窗口函数聚合计算(Window aggregate)，再做物化处理(Materialize)这三个步骤了，对一百万的数据做这些处理耗时约6s，虽然只执行一次，但对SQL性能的影响是很大的。现在问题聚焦于能不能减少做这些处理的数据量。
从b表的统计信息看，关联字段basesn的选择性不错，本SQL最终结果集也只有331行，关联字段对b表的过滤条件是很好的，当前优化器的行为表现是，因为有窗口函数聚合运算，主查询的关联谓词条件无法推入到tb派生表的内部。了解了这一点，想办法改写语句，让关联字段起到过滤作用。
优化方案

这里我想到的解决方案是：对外层查询表的列CONTACTID去重处理，关联到tb内层查询中，对满足关联条件的数据做Sort，Window aggregate，Materialize这些处理。
为什么增加这一层关联与原语句等价呢，就当作思考题吧，可在评论区评论噢！
语句改写参考如下：

1. greatsql> SHOW index FROM a;
2. +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3. | Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
4. +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5. | a     |          0 | PRIMARY     |            1 | log_id      | A         |       10000 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
6. | a     |          1 | idx_logtime |            1 | log_time    | A         |        9990 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
7. +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
8. 2 rows in set (0.00 sec)
10. greatsql> SHOW index FROM b;
11. +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
12. | Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
13. +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
14. | b     |          0 | PRIMARY    |            1 | id          | A         |      916864 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
15. | b     |          1 | idx_basesn |            1 | basesn      | A         |      515268 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
16. +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
17. 2 rows in set (0.00 sec)

复制代码

可以看出改写后的SQL耗时0.03s，比原来的6.2s，性能提升了约200倍。表面上SQL是比原来复杂了一点，但整体执行效率却得到了很大的提升。
总结

SQL优化的核心思想是减少I/O开销，无论什么优化技巧都是围绕这个主题，根据SQL具体情况演变出的形形色色的方法而已。万变不离其宗，本案例也是如此。
通过手动改写SQL，实现谓词下推，减少了内层表需要处理的数据量，从而提升了SQL性能。
当然，我们期待GreatSQL的优化器能在未来实现这一算法，自动实现谓词下推，不用改动SQL，即可高效执行SQL。
无论哪种数据库的优化器，都会或多或少存在一定缺陷，我们优化DBA需要做的就是，理解其缺陷，再利用现有资源，帮助其找到好的执行计划，来提升SQL性能。

Enjoy GreatSQL
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！