mysql - 请帮我优化这个 MySQL SELECT 语句

Question

我有一个查询，在没有其他重要进程运行的情况下，在高性能 SSD 服务器上运行大约需要四分钟。如果可能的话，我想让它更快。

数据库存储了一款名为 Dota 2 的流行视频游戏的比赛历史记录。在这款游戏中，十名玩家(每队五名)每人选择一个“英雄”并进行战斗。

我的查询的目的是根据使用的英雄创建一个过去比赛的列表，以及每个团队有多少“XP 依赖”。对于 200,000 个匹配项(以及 2,000,000 行匹配到英雄的关系表)，查询大约需要四分钟。对于 1,000,000 场比赛，大约需要 15 场比赛。

我对服务器有完全的控制权，因此也欢迎任何配置建议。感谢您的帮助。这是详细信息...

CREATE TABLE matches (
*   match_id BIGINT UNSIGNED NOT NULL,
    start_time INT UNSIGNED NOT NULL,
    skill_level TINYINT NOT NULL DEFAULT -1,
*   winning_team TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (match_id),
    KEY start_time (start_time),
    KEY skill_level (skill_level),
    KEY winning_team (winning_team));

CREATE TABLE heroes (
*   hero_id SMALLINT UNSIGNED NOT NULL,
    name CHAR(40) NOT NULL DEFAULT '',
    faction TINYINT NOT NULL DEFAULT -1,
    primary_attribute TINYINT NOT NULL DEFAULT -1,
    group_index TINYINT NOT NULL DEFAULT -1,
    match_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    win_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
*   xp_from_wins BIGINT UNSIGNED NOT NULL DEFAULT 0,
*   team_xp_from_wins BIGINT UNSIGNED NOT NULL DEFAULT 0,
    xp_from_losses BIGINT UNSIGNED NOT NULL DEFAULT 0,
    team_xp_from_losses BIGINT UNSIGNED NOT NULL DEFAULT 0,
    gold_from_wins BIGINT UNSIGNED NOT NULL DEFAULT 0,
    team_gold_from_wins BIGINT UNSIGNED NOT NULL DEFAULT 0,
    gold_from_losses BIGINT UNSIGNED NOT NULL DEFAULT 0,
    team_gold_from_losses BIGINT UNSIGNED NOT NULL DEFAULT 0,
    included TINYINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (hero_id));

CREATE TABLE matches_heroes (
*   match_id BIGINT UNSIGNED NOT NULL,
    player_id INT UNSIGNED NOT NULL,
*   hero_id SMALLINT UNSIGNED NOT NULL,
    xp_per_min SMALLINT UNSIGNED NOT NULL,
    gold_per_min SMALLINT UNSIGNED NOT NULL,
    position TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (match_id, hero_id),
    KEY match_id (match_id),
    KEY player_id (player_id),
    KEY hero_id (hero_id),
    KEY xp_per_min (xp_per_min),
    KEY gold_per_min (gold_per_min),
    KEY position (position));

查询

SELECT
    matches.match_id,
    SUM(CASE     
        WHEN position < 5 THEN xp_from_wins / team_xp_from_wins     
        ELSE 0    
    END) AS radiant_xp_dependence,
    SUM(CASE     
        WHEN position >= 5 THEN xp_from_wins / team_xp_from_wins     
        ELSE 0    
    END) AS dire_xp_dependence,
    winning_team   
FROM
    matches   
INNER JOIN
    matches_heroes     
        ON matches.match_id = matches_heroes.match_id   
INNER JOIN
    heroes     
        ON matches_heroes.hero_id = heroes.hero_id   
GROUP BY
    matches.match_id

示例结果

match_id   | radiant_xp_dependence | dire_xp_dependence | winning_team

2298874871 | 1.0164                | 0.9689             | 1
2298884079 | 0.9932                | 1.0390             | 0
2298885606 | 0.9877                | 1.0015             | 1

解释

id | select_type | table          | type   | possible_keys            | key     | key_len | ref                            | rows | Extra

1  | SIMPLE      | heroes         | ALL    | PRIMARY                  | NULL    | NULL    | NULL                           | 111  | Using temporary; Using filesort
1  | SIMPLE      | matches_heroes | ref    | PRIMARY,match_id,hero_id | hero_id | 2       | dota_2.heroes.hero_id          | 3213 |
1  | SIMPLE      | matches        | eq_ref | PRIMARY                  | PRIMARY | 8       | dota_2.matches_heroes.match_id | 1    |

机器规范

• 英特尔至强 E5
• E5-1630v3 4/8t
• 3.7/3.8 GHz
• 64 GB 内存
• DDR4 ECC 2133 兆赫
• 2 x 480GB SSD SOFT

数据库

• MariaDB 10.0
• InnoDB

Answer 1

很可能，主要的性能驱动因素是 GROUP BY。有时，在 MySQL 中，使用相关的子查询会更快。因此，尝试像这样编写查询:

SELECT m.match_id,
       (SELECT SUM(h.xp_from_wins / h.team_xp_from_wins)
        FROM matches_heroes mh INNER JOIN
             heroes h   
             ON mh.hero_id = h.hero_id
        WHERE m.match_id = mh.match_id AND mh.position < 5
       ) AS radiant_xp_dependence,
       (SELECT SUM(h.xp_from_wins / h.team_xp_from_wins)
        FROM matches_heroes mh INNER JOIN
             heroes h   
             ON mh.hero_id = h.hero_id
        WHERE m.match_id = mh.match_id AND mh.position >= 5
       ) AS dire_xp_dependence,
       m.winning_team   
FROM matches m;

然后，您需要索引:

• matches_heroes(match_id, position)
• heroes(hero_id, xp_from_wins, team_xp_from_wins)

为了完整起见，您可能还需要这个索引:

• 比赛(match_id, winning_team)

如果您将 order by match_id 添加到查询中，这将更加重要。