MySQL:如何选择和处理 "n to n"相关数据以提高性能

Question

作业:
我需要选择具有多对多关系的数据并寻找性能良好的解决方案。我目前有两个可行的解决方案(见下文)。

示例/说明:
竞赛由组织插入。一场比赛可以没有、一个或多个组织作为发起人。我只需要每场比赛的结果中包含一次发起人的 ID 列表。

数据结构:
- 表“竞赛”:(id，名称)
- 表“组织”:(id，名称)
- 表“competition_organization”:(competitionID、organizationID)

所需结果:
|id|姓名|promoter_list|
|1|欧洲冠军联赛|1241|
|2|FIFA世界杯|1240|
|3|FIFA 世界杯预赛 - 非洲|1240, 1242|

开发平台: Cold Fusion

数据库: MySQL

基于给定答案的附加说明:
- 我的问题的主要意图是找到一种比过去更好地处理这种关系的方法。比赛只是我需要的一个例子。
- 我试图让它更简单，也许我太多地忽略了一个事实。在我的应用程序中，我还需要组织名称。因此，我加入了组织表。
- 竞赛的相关信息比我在本例中描述的要多。我的应用程序中的查询使用了与其他表的多个联接。

<小时/>

解决方案1:
- 查询选择比赛数据
- 循环结果
- 将每个记录存储在循环内的数组中
- 用于为结果/循环中的每个记录选择启动子的附加查询
- 将启动子 ID 和另一个查询循环添加到数组中

主要查询:

SELECT competition.id, competition.name
FROM competition
WHERE ...

循环内的附加启动子查询:

SELECT DISTINCT organisation.id 
FROM organisation 
INNER JOIN competition_organisation 
ON competition_organisation.organisationID = organisation.id 
WHERE competition_organisation.competitionID = competition.id[currentrow]#

<小时/>

解决方案 2:
- 仅使用一个带有子选择的查询
- 循环结果
- 将每个记录存储在循环内的数组中

SELECT competition.id, competition.name,
(
    SELECT CONVERT(GROUP_CONCAT(organisation.id SEPARATOR ', ') USING utf8)
    FROM organisation
    WHERE organisation.id in 
    (
        SELECT DISTINCT competition_organisation.organisationID 
        FROM competition_organisation
        WHERE competition_organisation.competitionID = competition.id
    )
) AS promoter_list
FROM competition
WHERE ...

<小时/>

解决方案3(由Spencer7593提出):

SELECT c.id, 
       c.name,
       CONVERT(GROUP_CONCAT(DISTINCT o.id ORDER BY o.id) USING utf8) AS promoter_id_list,
       CONVERT(GROUP_CONCAT(DISTINCT o.name ORDER BY o.id) USING utf8) AS promoter_list
FROM competition c
LEFT JOIN competition_organisation c_o ON c_o.competitionID = c.id
LEFT JOIN organisation o ON o.id = c_o.organisationID
GROUP BY c.id, c.name

(我稍微更改了代码并添加了组织名称)

<小时/>

解决方案 4(由 Thorsten Kettner 提出，由 Rick James 优化):

SELECT id, name,
       ( SELECT CONVERT(GROUP_CONCAT(organisationID SEPARATOR ', ') USING utf8)         
             FROM competition_organisation
             WHERE competitionID = c.id
       ) AS promoter_id_list,
       ( SELECT CONVERT(GROUP_CONCAT(organisation.name SEPARATOR ', ') USING utf8)          
             FROM competition
             left join competition_organisation on competition_organisation.competitionID = competition.id
             left join organisationen on organisationen.id = competition_organisation.organisationID
             WHERE competitionID = c.id
       ) AS promoter_list
    FROM competition AS c

(还添加了组织名称，希望以正确的方式)

<小时/>

性能比较:

解决方案 1 - 100 条记录:~30ms + (100 x ~1ms) = ~130ms
解决方案 1 - 1000 条记录:~70ms + (1000 x ~1ms) = ~1070ms

解决方案 2 - 100 条记录:~5500ms
解决方案 2 - 1000 条记录:~48000ms

解决方案 3 - 100 条记录:~120ms
解决方案 3 - 1000 条记录:约 210 毫秒

解决方案 4 - 100 条记录:~110ms
解决方案 4 - 1000 条记录:约 200 毫秒

<小时/>

如您所见，解决方案 2 的性能很差。

• 是否有选项可以优化解决方案 2 查询以显着提高性能？
• 还有我没有想到的替代解决方案吗？
• 或者我应该保留解决方案 1？

<小时/>

结论:

我决定采用 Spencer 的解决方案 3。 3 和 4 的性能几乎相同。但 3 的代码更简单，并且与我现有的查询完美匹配，特别是与他们的左连接。

我对结果非常满意。性能已大大提高，将来我需要更少的代码/文件。

非常感谢您的帮助!

Answer 1

解决方案3:

利用外连接操作和 MySQL 特定的 GROUP_CONCAT 聚合函数返回以逗号分隔的 Organizationid 值列表。

-- SHOW VARIABLES LIKE 'group_concat_max_len';
-- SET group_concat_max_len = 1048576;

SELECT c.id      AS id
     , c.name    AS name
     , GROUP_CONCAT(DISTINCT p.organisationid ORDER BY p.organisationid) AS promoter_list
  FROM competition c
  LEFT
  JOIN competition_organisation p
    ON p.competitionid = c.id
 GROUP
    BY c.id
     , c.name
 ORDER
    BY c.id
     , c.name

请注意，如果 GROUP_CONCAT 生成的字符串长度超过 group_concat_max_len，该字符串将被静默截断为允许的长度。 (没有错误，没有警告)。

将返回的字符串的字节长度与系统变量的值进行比较，以检测字符串是否被截断。

<小时/>

如果需要或有充分的理由，organization 表也可以包含在查询中。

SELECT c.id      AS id
     , c.name    AS name
     , GROUP_CONCAT(DISTINCT o.id ORDER BY o.id) AS promoter_list
  FROM competition c
  LEFT
  JOIN competition_organisation p
    ON p.competitionid = c.id
  LEFT
  JOIN organisation o
    ON o.id = p.organisationid
 GROUP
    BY c.id
     , c.name
 ORDER
    BY c.id
     , c.name