php - 提高 MySQL 中 2 个左连接和多个 SUM 的性能

Question

我不是 MySQL 专家，因为它可能从我下面的查询中显示出来，但我已经设法让我的查询给我想要的结果，但对我来说似乎很慢:

SELECT 
    bouts.bout_id,
    bouts.bout_date,
    bouts.winner,
    bouts.result,
    bouts.ended_in_round,
    bouts.total_rounds,
    bouts.verified AS verified,
    CONCAT(opponent.first_name, ' ', opponent.last_name) AS opponent_name,
    opponent.last_name AS opponent_lastname,
    opponent.id AS opponent_id,
    IF(opponent.id = bouts.boxer_1, bouts.boxer_1_weight, bouts.boxer_2_weight) AS opp_weight,
    IF(opponent.id = bouts.boxer_1, bouts.boxer_2_weight, bouts.boxer_1_weight) AS fighter_weight,
    SUM(opp_bouts.ended_in_round) AS opp_total_rounds_boxed,
    SUM(IF(opp_bouts.winner = -1,1,0)) AS opp_draws,
    SUM(IF(opp_bouts.winner = opponent.id,1,0)) AS opp_wins,
    COUNT(opp_bouts.bout_id) AS opp_fights,
    IF(opponent.id = bouts.boxer_1, bouts.bxr2_power_points, bouts.bxr1_power_points) AS boxer_power,
    IF(opponent.id = bouts.boxer_1, bouts.bxr2_chin_points, bouts.bxr1_chin_points) AS boxer_chin,
    IF(opponent.id = bouts.boxer_1, bouts.bxr2_boxer_points, bouts.bxr1_boxer_points) AS boxer_boxer,
    SUM(IF(opponent.id = opp_bouts.boxer_1, opp_bouts.bxr1_power_points, opp_bouts.bxr2_power_points)) AS opp_power,
    SUM(IF(opponent.id = opp_bouts.boxer_1, opp_bouts.bxr1_chin_points, opp_bouts.bxr2_chin_points)) AS opp_chin,
    SUM(IF(opponent.id = opp_bouts.boxer_1, opp_bouts.bxr1_boxer_points, opp_bouts.bxr2_boxer_points)) AS opp_boxer,
    SUM(IF(opponent.id = opp_bouts.boxer_1, opp_bouts.bxr1_exp_points, opp_bouts.bxr2_exp_points)) AS opp_experience,
    MAX(IF(opp_bouts.verified = 1, opp_bouts.bout_date, 0)) AS verified_up_to,
    MAX(opp_bouts.bout_date) AS opp_last_bout
FROM
    bouts
    LEFT JOIN boxers opponent ON opponent.id=IF(bouts.boxer_2 = '" . $boxer_id . "', bouts.boxer_1, bouts.boxer_2)
    LEFT JOIN bouts opp_bouts ON (opp_bouts.boxer_1=opponent.id OR opp_bouts.boxer_2=opponent.id) AND opp_bouts.bout_date < bouts.bout_date
WHERE bouts.bout_date < '" . $to_date . "' AND (bouts.boxer_1 = '" . $boxer_id . "' OR bouts.boxer_2 = '" . $boxer_id . "')
GROUP BY bouts.bout_id
ORDER BY bouts.bout_date DESC

bouts大约有 11,570 行，boxers大约有 86,370 行，目前运行时间为 0.6500 - 0.7500 秒。我在 boxers.id 上有一个主键和 bouts.bout_id , 和关于 bouts.boxer_1 的索引和 bouts.boxer_2 ，但我对使用索引的了解也不太好。

查询这么慢是正常的吗，我能做些什么来帮助加快速度吗？

更新

解释查询结果。如果这不正确，您必须让我知道...

更新 2

回合表结构:

Boxer表结构:

更新 3

好吧，也许会有一些进展......

如果我改变

LEFT JOIN bouts opp_bouts ON (opp_bouts.boxer_1=opponent.id OR opp_bouts.boxer_2=opponent.id) AND opp_bouts.bout_date < bouts.bout_date

到:

LEFT JOIN bouts opp_bouts ON (opp_bouts.boxer_1=opponent.id) AND opp_bouts.bout_date < bouts.bout_date

它将查询速度提高到大约 0.0250，这似乎是一个很大的速度问题。我可以改进 OR 功能还是以不同的方式来做？

更新 4

多亏了 Kickstart，我现在有了下面的代码，速度快多了，但我不是 100% 我已经正确地完成了所有事情……或者我是否应该在某些列上有索引以进一步加快速度？我不确定它会如何扩大并枯萎，但这可能是我能得到的最好的？

bouts现在有 ~46,066 行和 boxers ~90,531，查询在 0.0466 运行，索引在 bout_date 和 boxer_1/2。

SELECT bout_id, 
        bout_date,
        winner,
        result,
        ended_in_round,
        total_rounds,
        verified,
        opponent_first_name,
        opponent_last_name,
        opponent_id,
        opp_weight,
        fighter_weight,
        SUM(opp_ended_in_round) AS opp_total_rounds_boxed,
        SUM(IF(opp_winner = -1,1,0)) AS opp_draws,
        SUM(IF(opp_winner = opponent_id,1,0)) AS opp_wins,
        COUNT(opp_id) AS opp_fights,
        boxer_power,
        boxer_chin,
        boxer_boxer,
        SUM(opp_power_rating) AS opp_power,
        SUM(opp_chin_rating) AS opp_chin,
        SUM(opp_boxer_rating) AS opp_boxer,
        SUM(opp_exp_rating) AS opp_experience,
        MIN(IF(opp_verified = 1,9999-01-01 , opp_bout_date)) AS verified_up_to,
        MAX(opp_bout_date) AS opp_last_bout
    FROM (

    SELECT bouts.bout_id, bouts.total_rounds, bouts.result, bouts.verified, opp_bouts.verified AS opp_verified, opp_bouts.bout_id AS opp_id, bouts.winner, opp_bouts.winner AS opp_winner, bouts.ended_in_round, opp_bouts.ended_in_round AS opp_ended_in_round, bouts.boxer_2_weight AS opp_weight, bouts.bxr1_power_points AS boxer_power, opp_bouts.bxr1_power_points AS opp_power_rating , opp_bouts.bxr1_chin_points AS opp_chin_rating, opp_bouts.bxr1_boxer_points AS opp_boxer_rating, opp_bouts.bxr1_exp_points AS opp_exp_rating, bouts.bxr1_chin_points AS boxer_chin, bouts.bxr1_boxer_points AS boxer_boxer, bouts.boxer_1_weight AS fighter_weight, bouts.boxer_2 AS opponent_id, bouts.bout_date, opp_bouts.bout_date AS opp_bout_date, opponent.first_name AS opponent_first_name, opponent.last_name AS opponent_last_name
    FROM bouts
    LEFT JOIN boxers opponent ON opponent.id = boxer_2
    LEFT JOIN bouts opp_bouts ON opp_bouts.boxer_1 = opponent.id AND opp_bouts.bout_date < bouts.bout_date
    WHERE bouts.bout_date < 'NOW()'
    AND bouts.boxer_1 = '63514'

    UNION ALL

    SELECT bouts.bout_id, bouts.total_rounds, bouts.result, bouts.verified, opp_bouts.verified AS opp_verified, opp_bouts.bout_id AS opp_id, bouts.winner, opp_bouts.winner AS opp_winner, bouts.ended_in_round, opp_bouts.ended_in_round AS opp_ended_in_round, bouts.boxer_2_weight AS opp_weight, bouts.bxr1_power_points AS boxer_power, opp_bouts.bxr2_power_points AS opp_power_rating, opp_bouts.bxr2_chin_points AS opp_chin_rating, opp_bouts.bxr2_boxer_points AS opp_boxer_rating, opp_bouts.bxr2_exp_points AS opp_exp_rating, bouts.bxr1_chin_points AS boxer_chin, bouts.bxr1_boxer_points AS boxer_boxer, bouts.boxer_1_weight AS fighter_weight, bouts.boxer_2 AS opponent_id, bouts.bout_date, opp_bouts.bout_date AS opp_bout_date, opponent.first_name AS opponent_first_name, opponent.last_name AS opponent_last_name
    FROM bouts
    LEFT JOIN boxers opponent ON opponent.id = boxer_2
    LEFT JOIN bouts opp_bouts ON opp_bouts.boxer_2 = opponent.id AND opp_bouts.bout_date < bouts.bout_date
    WHERE bouts.bout_date < 'NOW()'
    AND bouts.boxer_1 = '63514'

    UNION ALL

    SELECT bouts.bout_id, bouts.total_rounds, bouts.result, bouts.verified, opp_bouts.verified AS opp_verified, opp_bouts.bout_id AS opp_id, bouts.winner, opp_bouts.winner AS opp_winner, bouts.ended_in_round, opp_bouts.ended_in_round AS opp_ended_in_round, bouts.boxer_1_weight AS opp_weight, bouts.bxr2_power_points AS boxer_power, opp_bouts.bxr1_power_points AS opp_power_rating, opp_bouts.bxr1_chin_points AS opp_chin_rating, opp_bouts.bxr1_boxer_points AS opp_boxer_rating, opp_bouts.bxr1_exp_points AS opp_exp_rating, bouts.bxr2_chin_points AS boxer_chin, bouts.bxr2_boxer_points AS boxer_boxer, bouts.boxer_2_weight AS fighter_weight, bouts.boxer_1 AS opponent_id, bouts.bout_date, opp_bouts.bout_date AS opp_bout_date, opponent.first_name AS opponent_first_name, opponent.last_name AS opponent_last_name
    FROM bouts
    LEFT JOIN boxers opponent ON opponent.id = boxer_1
    LEFT JOIN bouts opp_bouts ON opp_bouts.boxer_1 = opponent.id AND opp_bouts.bout_date < bouts.bout_date
    WHERE bouts.bout_date < 'NOW()'
    AND bouts.boxer_2 = '63514'

    UNION ALL

    SELECT bouts.bout_id, bouts.total_rounds, bouts.result, bouts.verified, opp_bouts.verified AS opp_verified, opp_bouts.bout_id AS opp_id, bouts.winner, opp_bouts.winner AS opp_winner, bouts.ended_in_round, opp_bouts.ended_in_round AS opp_ended_in_round, bouts.boxer_1_weight AS opp_weight, bouts.bxr2_power_points AS boxer_power, opp_bouts.bxr2_power_points AS opp_power_rating, opp_bouts.bxr2_chin_points AS opp_chin_rating, opp_bouts.bxr2_boxer_points AS opp_boxer_rating, opp_bouts.bxr2_exp_points AS opp_exp_rating, bouts.bxr2_chin_points AS boxer_chin, bouts.bxr2_boxer_points AS boxer_boxer, bouts.boxer_2_weight AS fighter_weight, bouts.boxer_1 AS opponent_id, bouts.bout_date, opp_bouts.bout_date AS opp_bout_date, opponent.first_name AS opponent_first_name, opponent.last_name AS opponent_last_name
    FROM bouts
    LEFT JOIN boxers opponent ON opponent.id = boxer_1
    LEFT JOIN bouts opp_bouts ON opp_bouts.boxer_2 = opponent.id AND opp_bouts.bout_date < bouts.bout_date
    WHERE bouts.bout_date < 'NOW()'
    AND bouts.boxer_2 = '63514'
) AS my_table GROUP BY bout_id ORDER BY bout_date DESC

Answer 1

使用 IF 来决定几乎所有列，无论您指的是第一个还是第二个拳击手，都可能会减慢速度。

可能最好有 2 个子选择(一个用于 boxer_1 和 boxer_2)，将结果合并在一起，然后计算结果的 SUM 和 COUNT。

编辑。简要示例(不完整，因为我不完全了解所有数据的来源)。将 4 个查询联合在一起(每个 boxer_1 和 boxer_2 中的每个查询 1 个，而第 2 个参加的每个查询一个)。使用此联合查询的结果进行实际计算。

SELECT SomeFields,
SUM(SomeField),
etc
FROM
(
    SELECT bouts.boxer_1 AS boxer, bouts.boxer_2 AS opponent, opp_bouts.boxer_2 AS opponentsPrevOpponent
    FROM bouts
    LEFT JOIN boxers opponent ON opponent.id = bouts.boxer_2
    LEFT JOIN bouts opp_bouts ON opp_bouts.boxer_1 = opponent.id AND opp_bouts.bout_date < bouts.bout_date
    WHERE bouts.bout_date < '" . $to_date . "' 
    AND bouts.boxer_1 = '" . $boxer_id . "' 
    UNION ALL
    SELECT bouts.boxer_1 AS boxer, bouts.boxer_2 AS opponent, opp_bouts.boxer_1 AS opponentsPrevOpponent
    FROM bouts
    LEFT JOIN boxers opponent ON opponent.id = bouts.boxer_2
    LEFT JOIN bouts opp_bouts ON opp_bouts.boxer_2 = opponent.id AND opp_bouts.bout_date < bouts.bout_date
    WHERE bouts.bout_date < '" . $to_date . "' 
    AND bouts.boxer_1 = '" . $boxer_id . "' 
    UNION ALL
    SELECT bouts.boxer_2 AS boxer, bouts.boxer_1 AS opponent, opp_bouts.boxer_2 AS opponentsPrevOpponent
    FROM bouts
    LEFT JOIN boxers opponent ON opponent.id = bouts.boxer_1
    LEFT JOIN bouts opp_bouts ON opp_bouts.boxer_1 = opponent.id AND opp_bouts.bout_date < bouts.bout_date
    WHERE bouts.bout_date < '" . $to_date . "' 
    AND bouts.boxer_2 = '" . $boxer_id . "' 
    UNION ALL
    SELECT bouts.boxer_2 AS boxer, bouts.boxer_1 AS opponent, opp_bouts.boxer_1 AS opponentsPrevOpponent
    FROM bouts
    LEFT JOIN boxers opponent ON opponent.id = bouts.boxer_1
    LEFT JOIN bouts opp_bouts ON opp_bouts.boxer_2 = opponent.id AND opp_bouts.bout_date < bouts.bout_date
    WHERE bouts.bout_date < '" . $to_date . "' 
    AND bouts.boxer_2 = '" . $boxer_id . "' 
) Sub1

这样您就可以在子选择中获取所需的值，而不是在各种聚合函数上使用 IF 语句