You need to enable JavaScript to run this app. 使用CONCAT的慢SQL优化:规避CONCAT提升多表查询性能
优惠活动
大模型
产品
解决方案
定价
更多

使用CONCAT的慢SQL优化:规避CONCAT提升多表查询性能

移除CONCAT函数优化查询性能

当前编写的关联查询依赖CONCAT函数拼接复合键完成匹配,实测执行耗时较长,目标是在不改动业务逻辑的前提下移除CONCAT依赖,提升查询效率。数据库简化版结构与测试样例可在公开示例环境查看。

表关联关系

  • 单条quote_request可关联多条quote_request_next_matches记录、多条quote_matches记录
  • 单条quote_request_next_matches记录归属于单个quote_request与单个partner
  • 单条quote_match对应1条关联的disposition_code记录

业务查询目标

查询返回满足以下条件的全部quote_request的ID:

该quote_request通过quote_request_next_matches绑定的所有合作方(partner),均不存在关联了state = 7状态的disposition_code的quote_matches记录

当前执行的慢查询语句

SELECT DISTINCT result.quote_request_id FROM (
  SELECT quote_requests.id as quote_request_id, quote_request_next_matches.partner_id as partner_id 
  FROM quote_requests
    JOIN quote_request_next_matches ON quote_request_next_matches.quote_request_id = quote_requests.id
    JOIN partners ON quote_request_next_matches.partner_id = partners.id
    WHERE partners.id IN (1, 2, 3, 4, 5, 6, 7)
) as result 
LEFT JOIN quote_matches ON quote_matches.quote_request_id = result.quote_request_id
WHERE CONCAT(result.quote_request_id, '_', result.partner_id) NOT IN (
  SELECT CONCAT(quote_requests.id, '_', partners.id)
    FROM quote_requests
    JOIN quote_request_next_matches ON quote_request_next_matches.quote_request_id = quote_requests.id
    JOIN partners ON quote_request_next_matches.partner_id = partners.id
    JOIN quote_matches ON CONCAT(quote_matches.quote_request_id, '_', quote_matches.partner_id)
      = CONCAT(quote_requests.id, '_', partners.id)
    JOIN disposition_codes ON disposition_codes.disposition_codable_id = quote_matches.id AND
      disposition_codes.disposition_codable_type = 'QuoteMatch' AND
      disposition_codes.state = 7
    WHERE partners.id IN (1, 2, 3, 4, 5, 6, 7)
)

执行计划实测结果

通过EXPLAIN ANALYZE采集的执行详情如下,总执行耗时约6秒:

Hash Join  (cost=16509.50..18165.56 rows=4428 width=196) (actual time=6003.057..6024.333 rows=4778 loops=1)
  Hash Cond: (quote_requests.id = quote_requests_1.id)
  ->  Seq Scan on quote_requests  (cost=0.00..1518.18 rows=52518 width=196) (actual time=0.008..16.382 rows=52518 loops=1)
  ->  Hash  (cost=16454.15..16454.15 rows=4428 width=8) (actual time=6003.032..6003.032 rows=4778 loops=1)
        Buckets: 8192  Batches: 1  Memory Usage: 251kB
        ->  HashAggregate  (cost=16365.59..16409.87 rows=4428 width=8) (actual time=6001.665..6002.460 rows=4778 loops=1)
              Group Key: quote_requests_1.id
              ->  Nested Loop Left Join  (cost=13593.07..16354.52 rows=4428 width=8) (actual time=152.262..5998.400 rows=5778 loops=1)
                    ->  Nested Loop  (cost=13592.78..14029.46 rows=3957 width=8) (actual time=152.237..5976.368 rows=4780 loops=1)
                          ->  Index Only Scan using partners_pkey on partners  (cost=0.14..8.16 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1)
                                Index Cond: (id = 15)
                                Heap Fetches: 1
                          ->  Hash Join  (cost=13592.64..13981.72 rows=3957 width=12) (actual time=152.230..5975.584 rows=4780 loops=1)
                                Hash Cond: (quote_request_next_matches.quote_request_id = quote_requests_1.id)
                                Join Filter: (NOT (SubPlan 1))
                                Rows Removed by Join Filter: 3121
                                ->  Seq Scan on quote_request_next_matches  (cost=0.00..368.31 rows=7913 width=8) (actual time=0.005..3.285 rows=7901 loops=1)
                                      Filter: (partner_id = 15)
                                      Rows Removed by Filter: 9724
                                ->  Hash  (cost=1518.18..1518.18 rows=52518 width=8) (actual time=14.096..14.097 rows=52518 loops=1)
                                      Buckets: 65536  Batches: 1  Memory Usage: 2564kB
                                      ->  Seq Scan on quote_requests quote_requests_1  (cost=0.00..1518.18 rows=52518 width=8) (actual time=0.002..7.403 rows=52518 loops=1)
                                SubPlan 1
                                  ->  Materialize  (cost=11417.98..14351.21 rows=84805 width=32) (actual time=0.016..0.132 rows=2507 loops=7901)
                                        ->  Merge Join  (cost=11417.98..13347.19 rows=84805 width=32) (actual time=128.971..136.558 rows=3123 loops=1)
                                              Merge Cond: ((concat(quote_matches_1.quote_request_id, '_', quote_matches_1.partner_id)) = (concat(quote_requests_2.id, '_', partners_1.id)))
                                              ->  Sort  (cost=8254.59..8259.94 rows=2143 width=16) (actual time=60.705..61.438 rows=8486 loops=1)
                                                    Sort Key: (concat(quote_matches_1.quote_request_id, '_', quote_matches_1.partner_id))
                                                    Sort Method: quicksort  Memory: 1047kB
                                                    ->  Hash Join  (cost=4410.60..8136.02 rows=2143 width=16) (actual time=22.182..31.475 rows=8486 loops=1)
                                                          Hash Cond: (disposition_codes.disposition_codable_id = quote_matches_1.id)
                                                          ->  Bitmap Heap Scan on disposition_codes  (cost=66.41..3786.20 rows=2145 width=4) (actual time=1.120..5.417 rows=8489 loops=1)
                                                                Recheck Cond: ((state = 7) AND ((disposition_codable_type)::text = 'QuoteMatch'::text))
                                                                Heap Blocks: exact=3265
                                                                ->  Bitmap Index Scan on index_by_state_type_id  (cost=0.00..65.87 rows=2145 width=0) (actual time=0.765..0.765 rows=8489 loops=1)
                                                                      Index Cond: ((state = 7) AND ((disposition_codable_type)::text = 'QuoteMatch'::text))
                                                          ->  Hash  (cost=3609.64..3609.64 rows=58764 width=24) (actual time=20.985..20.985 rows=58764 loops=1)
                                                                Buckets: 65536  Batches: 1  Memory Usage: 3726kB
                                                                ->  Seq Scan on quote_matches quote_matches_1  (cost=0.00..3609.64 rows=58764 width=24) (actual time=0.011..11.648 rows=58764 loops=1)
                                              ->  Sort  (cost=3163.40..3183.18 rows=7913 width=16) (actual time=68.225..69.037 rows=7900 loops=1)
                                                    Sort Key: (concat(quote_requests_2.id, '_', partners_1.id))
                                                    Sort Method: quicksort  Memory: 810kB
                                                    ->  Nested Loop  (cost=2174.80..2651.03 rows=7913 width=16) (actual time=30.825..43.602 rows=7901 loops=1)
                                                          ->  Index Only Scan using partners_pkey on partners partners_1  (cost=0.14..8.16 rows=1 width=8) (actual time=0.020..0.023 rows=1 loops=1)
                                                                Index Cond: (id = 15)
                                                                Heap Fetches: 1
                                                          ->  Hash Join  (cost=2174.66..2563.74 rows=7913 width=12) (actual time=30.761..37.113 rows=7901 loops=1)
                                                                Hash Cond: (quote_request_next_matches_1.quote_request_id = quote_requests_2.id)
                                                                ->  Seq Scan on quote_request_next_matches quote_request_next_matches_1  (cost=0.00..368.31 rows=7913 width=8) (actual time=0.015..2.847 rows=7901 loops=1)
                                                                      Filter: (partner_id = 15)
                                                                      Rows Removed by Filter: 9724
                                                                ->  Hash  (cost=1518.18..1518.18 rows=52518 width=8) (actual time=30.410..30.410 rows=52518 loops=1)
                                                                      Buckets: 65536  Batches: 1  Memory Usage: 2564kB
                                                                      ->  Seq Scan on quote_requests quote_requests_2  (cost=0.00..1518.18 rows=52518 width=8) (actual time=0.005..9.120 rows=52518 loops=1)
                    ->  Index Only Scan using index_quote_matches_on_quote_request_id on quote_matches  (cost=0.29..0.57 rows=2 width=8) (actual time=0.004..0.004 rows=1 loops=4780)
                          Index Cond: (quote_request_id = quote_requests_1.id)
                          Heap Fetches: 2672
Planning Time: 1.331 ms
Execution Time: 6024.699 ms

内容的提问来源于stack exchange,提问作者Adrian Nechita

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.20 13:54:03