使用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
相关产品推荐
相关产品推荐

