GreatSQL优化技巧：手动实现谓词下推
<h1 id="greatsql优化技巧手动实现谓词下推">GreatSQL优化技巧：手动实现谓词下推</h1><h2 id="导语">导语</h2>
<p>最近总是听到用 AI 来优化 SQL 的言论，今天心血来潮试了一下，把表结构、统计信息、SQL语句、执行计划都告诉AI，AI给出了一大堆的建议，它会从索引，语句改写，参数调整各个方面给出优化策略，看似面面俱到，但是如果不懂优化理论，随便使用其给出的优化建议，可能的结果就是，一顿操作猛如虎，一看战绩零杠五。所以本人还是老老实实的总结优化技巧吧，这些案例技巧或许某天会成为 AI 的营养餐。</p>
<h2 id="sql-案例">SQL 案例</h2>
<p>SQL 案例语句：(实际业务场景太复杂，截取片段来说明本文主题)</p>
<pre><code class="language-SQL">SELECT ta.*, tb.*
FROM (SELECT *
FROM (SELECT a.contactid,
a.subs_number,
a.log_time,
ROW_NUMBER() OVER(PARTITION BY a.contactid, a.subs_number ORDER BY a.log_time DESC) rn,
a.log_id
FROM a
WHERE a.contactid IS NOT NULL
AND a.log_time >= '2025-05-30 00:00:00'
AND a.log_time <= '2025-06-02') cc
WHERE rn = 1) ta
LEFT JOIN (SELECT b.*,
ROW_NUMBER() OVER(PARTITION BY b.basesn ORDER BY b.create_time DESC) rn
FROM b
WHERE b.create_time IS NOT NULL) tb
ON ta.contactid = tb.basesn
AND tb.rn = 1
</code></pre>
<p>下面支撑该案例 SQL 的测试表结构，符合案例 SQL 特点的测试数据。</p>
<pre><code class="language-SQL"> CREATE TABLE a(log_id bigint,CONTACTID INT,subs_number INT,log_time datetime,PRIMARY KEY (log_id),KEY idx_logtime(log_time));
CREATE TABLE b(id bigint PRIMARY KEY,basesn INT,create_time datetime,KEY idx_basesn(basesn));
delimiter //
CREATE OR REPLACE PROCEDURE P1() IS
BEGIN
FOR I IN 1 .. 10000 LOOP
INSERT INTO a(log_id,contactid,subs_number,log_time) VALUES(i,TRUNC(rand()*8000),TRUNC(rand()*9000),SYSDATE-rand()*90);
END LOOP;
FOR I IN 1 .. 1000000 LOOP
INSERT INTO b(id,basesn,create_time) VALUES(i,TRUNC(rand()*800000),SYSDATE-rand()*90);
END LOOP;
END;
//
delimiter ;
</code></pre>
<p>两表的统计信息如下：</p>
<pre><code class="language-SQL">greatsql> SHOW index FROM a;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| a | 0 | PRIMARY | 1 | log_id | A | 10000 | NULL | NULL | | BTREE | | | YES | NULL |
| a | 1 | idx_logtime | 1 | log_time | A | 9990 | NULL | NULL | YES| BTREE | | | YES | NULL |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
greatsql> SHOW index FROM b;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| b | 0 | PRIMARY | 1 | id | A | 916864 | NULL | NULL | | BTREE | | | YES | NULL |
| b | 1 | idx_basesn | 1 | basesn | A | 515268 | NULL | NULL | YES| BTREE | | | YES | NULL |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
</code></pre>
<h3 id="语句分析">语句分析</h3>
<p>SQL有两个派生表ta，tb，这两表做<code>left join</code> 左外连接。派生表ta，作为左外连接的左表，内层表a有log_time过滤条件，该列有单列索引，查询两三天的数据数据量命中几百行，查询一个月左右的数据量命中几千到1万左右。派生表tb，作为左外连接的右表，内层表b全表百万级别的数据量，条件<code>create_time is not null</code>过滤性不好。两个派生表都使用了窗口函数<code>ROW_NUMBER()</code></p>
<h3 id="执行计划分析">执行计划分析</h3>
<p>语句实际执行计划如下：</p>
<pre><code class="language-SQL">EXPLAIN: -> Nested loop left join(cost=22497.56 rows=0) (actual time=6181.328..6182.085 rows=331 loops=1)
-> Filter: (cc.rn = 1)(cost=1.21..35.91 rows=30) (actual time=2.712..2.794 rows=331 loops=1)
-> Table scan on cc(cost=2.50..2.50 rows=0) (actual time=2.704..2.752 rows=331 loops=1)
-> Materialize(cost=0.00..0.00 rows=0) (actual time=2.698..2.698 rows=331 loops=1)
-> Window aggregate: row_number() OVER (PARTITION BY a.CONTACTID,a.subs_number ORDER BY a.log_time desc ) (actual time=2.189..2.323 rows=331 loops=1)
-> Sort: a.CONTACTID, a.subs_number, a.log_time DESC(cost=149.21 rows=331) (actual time=2.168..2.190 rows=331 loops=1)
-> Filter: (a.CONTACTID is not null)(cost=149.21 rows=331) (actual time=0.156..1.847 rows=331 loops=1)
-> Index range scan on a using idx_logtime over ('2025-05-30 00:00:00' <= log_time <= '2025-06-02 00:00:00'), with index condition: ((a.log_time >= TIMESTAMP'2025-05-30 00:00:00') and (a.log_time <= TIMESTAMP'2025-06-02 00:00:00'))(cost=149.21 rows=331) (actual time=0.147..1.806 rows=331 loops=1)
-> Index lookup on tb using <auto_key0> (basesn=cc.contactid, rn=1)(cost=0.25..766.47 rows=3025) (actual time=18.668..18.668 rows=1 loops=331)
-> Materialize(cost=0.00..0.00 rows=0) (actual time=6178.570..6178.570 rows=1000000 loops=1)
-> Window aggregate: row_number() OVER (PARTITION BY b.basesn ORDER BY b.create_time desc ) (actual time=2153.616..3469.381 rows=1000000 loops=1)
-> Sort: b.basesn, b.create_time DESC(cost=100382.85 rows=998296) (actual time=2153.598..2733.042 rows=1000000 loops=1)
-> Filter: (b.create_time is not null)(cost=100382.85 rows=998296) (actual time=0.075..900.074 rows=1000000 loops=1)
-> Table scan on b(cost=100382.85 rows=998296) (actual time=0.074..316.051 rows=1000000 loops=1)
1 row in set (6.22 sec)
</code></pre>
<p>两表ta，tb使用<code>Nested loop</code>方式进行连接，ta表作为外层驱动表，结果集rows为331。 tb表作为内层循环表，循环扫描331次，这些都消耗不多。</p>
<p>此SQL耗时多的步骤在对tb的内层表b进行排序(Sort)，做窗口函数聚合计算(Window aggregate)，再做物化处理(Materialize)这三个步骤了，对一百万的数据做这些处理耗时约6s，虽然只执行一次，但对SQL性能的影响是很大的。现在问题聚焦于能不能减少做这些处理的数据量。</p>
<p>从b表的统计信息看，关联字段<code>basesn</code>的选择性不错，本SQL最终结果集也只有331行，关联字段对b表的过滤条件是很好的，当前优化器的行为表现是，因为有窗口函数聚合运算，主查询的关联谓词条件无法推入到tb派生表的内部。了解了这一点，想办法改写语句，让关联字段起到过滤作用。</p>
<h3 id="优化方案">优化方案</h3>
<p>这里我想到的解决方案是：对外层查询表的列<code>CONTACTID</code>去重处理，关联到tb内层查询中，对满足关联条件的数据做Sort，Window aggregate，Materialize这些处理。</p>
<p>为什么增加这一层关联与原语句等价呢，就当作思考题吧，可在评论区评论噢！</p>
<p>语句改写参考如下：</p>
<pre><code class="language-SQL">SELECT ta.*, tb.*
FROM (SELECT *
FROM (SELECT a.contactid,
a.subs_number,
a.log_time,
row_number() OVER(PARTITION BY a.contactid, a.subs_number ORDER BY a.log_time DESC) rn,
a.log_id
FROM a
WHERE a.contactid IS NOT NULL
AND a.log_time >= '2025-05-30 00:00:00'
AND a.log_time <= '2025-06-02') cc
WHERE rn = 1) ta
LEFT JOIN (SELECT b.*,
row_number() OVER(PARTITION BY b.basesn ORDER BY b.create_time DESC) rn
FROM b
join (SELECT distinct CONTACTID
FROM a
WHERE CONTACTID IS NOT NULL
AND LOG_TIME >= '2025-05-30 00:00:00'
AND LOG_TIME <= '2025-06-02') a1
ON a1.CONTACTID = b.basesn
WHERE b.create_time IS NOT NULL) tb
ON ta.contactid = tb.basesn
AND tb.rn = 1
</code></pre>
<p>改写后的语句执行计划如下：</p>
<pre><code class="language-SQL">EXPLAIN: -> Nested loop left join(cost=111.18 rows=0) (actual time=14.846..15.281 rows=331 loops=1)
-> Filter: (cc.rn = 1)(cost=1.21..35.91 rows=30) (actual time=2.668..2.747 rows=331 loops=1)
-> Table scan on cc(cost=2.50..2.50 rows=0) (actual time=2.636..2.683 rows=331 loops=1)
-> Materialize(cost=0.00..0.00 rows=0) (actual time=2.630..2.630 rows=331 loops=1)
-> Window aggregate: row_number() OVER (PARTITION BY a.CONTACTID,a.subs_number ORDER BY a.log_time desc ) (actual time=2.214..2.356 rows=331 loops=1)
-> Sort: a.CONTACTID, a.subs_number, a.log_time DESC(cost=149.21 rows=331) (actual time=2.173..2.198 rows=331 loops=1)
-> Filter: (a.CONTACTID is not null)(cost=149.21 rows=331) (actual time=0.089..1.784 rows=331 loops=1)
-> Index range scan on a using idx_logtime over ('2025-05-30 00:00:00' <= log_time <= '2025-06-02 00:00:00'), with index condition: ((a.log_time >= TIMESTAMP'2025-05-30 00:00:00') and (a.log_time <= TIMESTAMP'2025-06-02 00:00:00'))(cost=149.21 rows=331) (actual time=0.071..1.730 rows=331 loops=1)
-> Index lookup on tb using <auto_key0> (basesn=cc.contactid, rn=1)(cost=0.25..2.57 rows=10) (actual time=0.037..0.038 rows=1 loops=331)
-> Materialize(cost=0.00..0.00 rows=0) (actual time=12.159..12.159 rows=382 loops=1)
-> Window aggregate: row_number() OVER (PARTITION BY b.basesn ORDER BY b.create_time desc ) (actual time=11.614..11.781 rows=382 loops=1)
-> Sort: b.basesn, b.create_time DESC(actual time=11.608..11.636 rows=382 loops=1)
-> Stream results(cost=237.31 rows=518) (actual time=1.673..11.394 rows=382 loops=1)
-> Nested loop inner join(cost=237.31 rows=518) (actual time=1.670..11.247 rows=382 loops=1)
-> Filter: (a1.CONTACTID is not null)(cost=214.40..35.91 rows=297) (actual time=1.430..1.545 rows=321 loops=1)
-> Table scan on a1(cost=215.02..221.21 rows=298) (actual time=1.429..1.502 rows=321 loops=1)
-> Materialize(cost=215.00..215.00 rows=298) (actual time=1.428..1.428 rows=321 loops=1)
-> Table scan on <temporary>(cost=179.02..185.21 rows=298) (actual time=1.303..1.349 rows=321 loops=1)
-> Temporary table with deduplication(cost=179.00..179.00 rows=298) (actual time=1.302..1.302 rows=321 loops=1)
-> Filter: (a.CONTACTID is not null)(cost=149.21 rows=298) (actual time=0.110..1.143 rows=331 loops=1)
-> Index range scan on a using idx_logtime over ('2025-05-30 00:00:00' <= log_time <= '2025-06-02 00:00:00'), with index condition: ((a.log_time >= TIMESTAMP'2025-05-30 00:00:00') and (a.log_time <= TIMESTAMP'2025-06-02 00:00:00'))(cost=149.21 rows=331) (actual time=0.108..1.108 rows=331 loops=1)
-> Filter: (b.create_time is not null)(cost=0.48 rows=2) (actual time=0.028..0.030 rows=1 loops=321)
-> Index lookup on b using idx_basesn (basesn=a1.CONTACTID)(cost=0.48 rows=2) (actual time=0.027..0.029 rows=1 loops=321)
1 row in set (0.03 sec)
</code></pre>
<p>可以看出改写后的SQL耗时0.03s，比原来的6.2s，性能提升了约200倍。表面上SQL是比原来复杂了一点，但整体执行效率却得到了很大的提升。</p>
<h2 id="总结">总结</h2>
<p>SQL优化的核心思想是减少I/O开销，无论什么优化技巧都是围绕这个主题，根据SQL具体情况演变出的形形色色的方法而已。万变不离其宗，本案例也是如此。</p>
<p>通过手动改写SQL，实现谓词下推，减少了内层表需要处理的数据量，从而提升了SQL性能。</p>
<p>当然，我们期待GreatSQL的优化器能在未来实现这一算法，自动实现谓词下推，不用改动SQL，即可高效执行SQL。</p>
<p>无论哪种数据库的优化器，都会或多或少存在一定缺陷，我们优化DBA需要做的就是，理解其缺陷，再利用现有资源，帮助其找到好的执行计划，来提升SQL性能。</p>
<hr>
<p>Enjoy GreatSQL 😃</p>
<h2 id="关于-greatsql">关于 GreatSQL</h2>
<p>GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。</p>
<p>相关链接： GreatSQL社区 Gitee GitHub Bilibili</p>
<h2 id="greatsql社区">GreatSQL社区：</h2>
<blockquote>
<p>社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html</p>
</blockquote>
<p><img src="https://img2024.cnblogs.com/other/2630741/202508/2630741-20250827103908642-22764871.png"></p>
<h2 id="技术交流群">技术交流群：</h2>
<blockquote>
<p>微信：扫码添加<code>GreatSQL社区助手</code>微信好友，发送验证信息<code>加群</code>。</p>
</blockquote>
<p><img src="https://img2024.cnblogs.com/other/2630741/202508/2630741-20250827103908892-803718994.png"></p><br><br>
来源：https://www.cnblogs.com/greatsql/p/19060465
頁:
[1]