多表联查产生重复行问题排查及优化方案咨询

问题背景 #

我有payment_process、process、booking_extras三张表，用户预订附加服务时会向这三张表插入数据。当每张表各有2行数据时，执行联查SQL返回了6行重复结果。我尝试过子查询结合GROUP BY的方式解决，但不确定该方案的性能问题，希望得到更优的去重办法。

原联查SQL #

SELECT
DISTINCT
p.booking_id AS PBID,
p.process_id AS PID,
p.payment_method,
p.price,
p.paid,
p.created_at,
be.km,
be.km_50,
be.km_100,
be.km_200,
be.km_300,
be.km_500,
be.to_date,
pc.text,
pc.is_owner
FROM process pc
INNER JOIN payment_process p
ON p.process_id = pc.id
LEFT OUTER JOIN booking_extras be
ON be.booking_id = p.booking_id
WHERE pc.booking_id = $1 LIMIT 100;

查询结果 #

[
  {
    "pbid": 2,
    "pid": 5,
    "payment_method": "100% Bar vor Ort",
    "price": 300,
    "paid": 0,
    "created_at": "2023-03-23T16:40:09.271Z",
    "km": null,
    "km_50": 1,
    "km_100": null,
    "km_200": null,
    "km_300": null,
    "km_500": null,
    "to_date": null,
    "text": "Buchung",
    "is_owner": 1
  },
  {
    "pbid": 2,
    "pid": 5,
    "payment_method": "100% Bar vor Ort",
    "price": 300,
    "paid": 0,
    "created_at": "2023-03-23T16:40:09.271Z",
    "km": null,
    "km_50": null,
    "km_100": null,
    "km_200": 1,
    "km_300": null,
    "km_500": null,
    "to_date": null,
    "text": "Buchung",
    "is_owner": 1
  },
  {
    "pbid": 2,
    "pid": 6,
    "payment_method": "100% Bar vor Ort",
    "price": 25,
    "paid": 0,
    "created_at": "2023-03-23T16:37:38.811Z",
    "km": null,
    "km_50": 1,
    "km_100": null,
    "km_200": null,
    "km_300": null,
    "km_500": null,
    "to_date": null,
    "text": "Buchung",
    "is_owner": 1
  },
  {
    "pbid": 2,
    "pid": 6,
    "payment_method": "100% Bar vor Ort",
    "price": 25,
    "paid": 0,
    "created_at": "2023-03-23T16:37:38.811Z",
    "km": null,
    "km_50": null,
    "km_100": null,
    "km_200": 1,
    "km_300": null,
    "km_500": null,
    "to_date": null,
    "text": "Buchung",
    "is_owner": 1
  },
  {
    "pbid": 2,
    "pid": 7,
    "payment_method": "100% Bar vor Ort",
    "price": 100,
    "paid": 0,
    "created_at": "2023-03-23T16:43:02.549Z",
    "km": null,
    "km_50": 1,
    "km_100": null,
    "km_200": null,
    "km_300": null,
    "km_500": null,
    "to_date": null,
    "text": "Buchung",
    "is_owner": 1
  },
  {
    "pbid": 2,
    "pid": 7,
    "payment_method": "100% Bar vor Ort",
    "price": 100,
    "paid": 0,
    "created_at": "2023-03-23T16:43:02.549Z",
    "km": null,
    "km_50": null,
    "km_100": null,
    "km_200": 1,
    "km_300": null,
    "km_500": null,
    "to_date": null,
    "text": "Buchung",
    "is_owner": 1
  }
]

现有解决方案SQL #

SELECT
pc.text,
pc.is_owner,
payment_process.booking_id, payment_process.process_id, payment_process.price, payment_process.paid, payment_process.payment_method, payment_process.created_at,
booking_extras.km,
booking_extras.km_50,
booking_extras.km_100,
booking_extras.km_200,
booking_extras.km_300,
booking_extras.km_500,
booking_extras.to_date
FROM process pc
INNER JOIN (
  SELECT 
    payment_process.booking_id, 
    payment_process.process_id, 
    payment_process.price,
    payment_process.paid,
    payment_process.payment_method,
    payment_process.created_at
  FROM payment_process
   GROUP BY payment_process.booking_id, payment_process.process_id, payment_process.price, payment_process.paid, payment_process.payment_method, payment_process.created_at
) payment_process ON payment_process.process_id = pc.id
LEFT OUTER JOIN (
  SELECT 
    booking_extras.id, 
    booking_extras.km, 
    booking_extras.km_50,
    booking_extras.km_100,
    booking_extras.km_200,
    booking_extras.km_300,
    booking_extras.km_500,
    booking_extras.to_date
    FROM booking_extras
     GROUP BY 
     booking_extras.id, 
     booking_extras.km, 
     booking_extras.km_50,
     booking_extras.km_100,
     booking_extras.km_200,
     booking_extras.km_300,
     booking_extras.km_500,
     booking_extras.to_date
) booking_extras ON pc.booking_extras_id = booking_extras.id
WHERE pc.booking_id = $1 GROUP BY
 pc.id, pc.text, pc.is_owner,
  payment_process.booking_id, payment_process.process_id, payment_process.price, payment_process.paid, payment_process.payment_method, payment_process.created_at,
  booking_extras.km,
  booking_extras.km_50,
  booking_extras.km_100,
  booking_extras.km_200,
  booking_extras.km_300,
  booking_extras.km_500,
  booking_extras.to_date
LIMIT 50;

问题分析与优化方案 #

重复原因 #

原SQL的核心问题是关联条件错误：booking_extras通过booking_id和payment_process关联，当一个booking_id对应多条booking_extras和多条payment_process记录时，会产生笛卡尔积，导致结果重复。而你的现有方案中已经用到了pc.booking_extras_id = booking_extras.id这个正确关联，说明原SQL的关联逻辑本身就错了。

现有方案的性能问题 #

现有方案中的子查询GROUP BY完全冗余——没有使用聚合函数，只是将所有字段分组，效果等价于DISTINCT，但会额外生成临时表增加IO开销；外层又重复执行GROUP BY，进一步降低查询效率。

优化方案 #

方案1：修正关联条件（最优）

直接使用process和booking_extras的直接关联字段，从根源避免笛卡尔积：

SELECT DISTINCT
    p.booking_id AS PBID,
    p.process_id AS PID,
    p.payment_method,
    p.price,
    p.paid,
    p.created_at,
    be.km,
    be.km_50,
    be.km_100,
    be.km_200,
    be.km_300,
    be.km_500,
    be.to_date,
    pc.text,
    pc.is_owner
FROM process pc
INNER JOIN payment_process p ON p.process_id = pc.id
LEFT OUTER JOIN booking_extras be ON be.id = pc.booking_extras_id -- 修正关联条件
WHERE pc.booking_id = $1 
LIMIT 100;

方案2：窗口函数去重（保留原关联逻辑时使用）

如果业务上必须通过booking_id关联，可使用窗口函数按唯一标识分组取第一条，避免重复：

SELECT 
    PBID, PID, payment_method, price, paid, created_at,
    km, km_50, km_100, km_200, km_300, km_500, to_date,
    text, is_owner
FROM (
    SELECT
        p.booking_id AS PBID,
        p.process_id AS PID,
        p.payment_method,
        p.price,
        p.paid,
        p.created_at,
        be.km,
        be.km_50,
        be.km_100,
        be.km_200,
        be.km_300,
        be.km_500,
        be.to_date,
        pc.text,
        pc.is_owner,
        -- 按process_id和booking_extras.id分组，取每组第一条
        ROW_NUMBER() OVER(PARTITION BY p.process_id, be.id ORDER BY p.created_at) AS rn
    FROM process pc
    INNER JOIN payment_process p ON p.process_id = pc.id
    LEFT OUTER JOIN booking_extras be ON be.booking_id = p.booking_id
    WHERE pc.booking_id = $1
) t
WHERE rn = 1
LIMIT 100;

内容的提问来源于stack exchange，提问作者newexe91