mysql - 哪些点使sql查询更快

Question

我对 SQL 代码很陌生，我只选择了几列并且主要使用 GROUP BY 函数，但我的代码需要 2 分钟才能显示结果，也许这不是一个很长的查询，但我需要使更快。如何使 SQL 查询更快？

对于我的代码，我有一个包含联赛比赛的表格；

例如:

CustomerID        MatchDate         League              Matches                HomeTeam                AwayTeam
------------------------------------------------------------------------------------------------------------------------
1                 11-12-2006        La Liga             Barcelone-R.Madrid     Barcelona               RealMadrid
2                 10-10-2006        Premier League      Everton-Arsenal        Everton                 Arsenal
3                 09-10-2006        Premier League      Arsenal-Tottenham      Barcelona               RealMadrid
4                 10-10-2006        Bundesliga          Bayern-Mainz           Bayern                  MainZ 

我的目标是计算主队和客队每场比赛的总 ID，以便按联赛、球队和赛季查找每支球队和小组的总观看次数。相同的团队可以是 HomeTeam 和 AwayTeam，所以我使用了这段代码。

SELECT League, SUM(totalnum), Teams, Season FROM 
    (
        (SELECT DATE_FORMAT(MatchDate, '%Y') as 'Season', HomeTeam as Teams, League, count(distinct CustomerID) as "totalnum" 
            FROM MY_TABLE GROUP BY League, Teams, Season ) 
        UNION ALL 
        (SELECT DATE_FORMAT(MatchDate, '%Y') as 'Season', AwayTeam as Teams, League, count(distinct CustomerID) as "totalnum" 
            FROM MY_TABLE GROUP BY League, Teams, Season )
    ) aa
GROUP BY League, Teams, Season
ORDER BY totalnum DESC

我可以得到结果，但我需要更短的时间。哪些点会影响我的查询。

Answer 1

如果您应用这两个索引，我希望您现有的查询应该加速...

CREATE INDEX MY_TABLE_league_home_date_cust
    ON MY_TABLE(
        League, HomeTeam, MatchDate, CustomerID
    );

CREATE INDEX MY_TABLE_league_away_date_cust
    ON MY_TABLE(
        League, AwayTeam, MatchDate, CustomerID
    );

也就是说，我怀疑您的查询中涉及的最高成本是 COUNT(DISTINCT CustomerID)。这样做涉及到必须对所有数据进行排序。这可能意味着以下索引可能会更好...

CREATE INDEX MY_TABLE_cust_league_home_date
    ON MY_TABLE(
        CustomerID, League, HomeTeam, MatchDate
    );

CREATE INDEX MY_TABLE_cust_league_away_date
    ON MY_TABLE(
        CustomerID, League, AwayTeam, MatchDate
    );

不过，另一个观察是，在您的查询中，您 COUNT() 唯一的“家庭客户”，然后是 COUNT() 唯一的“外出客户”，然后 SUM() 它们。这意味着任何去过主场比赛和客场比赛的人都算两次。这是故意的吗？

如果不是故意的，您可能会发现查询成本更高...

SELECT
  Team,
  League,
  DATE_FORMAT(MatchDate, '%Y')   AS Season,
  COUNT(DISTINCT CustomerID)     AS total
FROM 
(
  SELECT CustomerID, League, HomeTeam AS Team, MatchDate FROM MyTable
  UNION ALL
  SELECT CustomerID, League, AwayTeam AS Team, MatchDate FROM MyTable
)
  combined_view
GROUP BY
  Team, League, Season
ORDER BY
  total DESC

不过，我认为您总体上最好的选择是为 Season 添加一个计算列，然后使用第一个索引的略微修改版本...

ALTER TABLE
  MY_TABLE
ADD Season VARCHAR(4) AS (
  DATE_FORMAT(MatchDate, '%Y')
);

CREATE INDEX MY_TABLE_league_home_season_cust
    ON MY_TABLE(
        League, HomeTeam, Season, CustomerID
    );

CREATE INDEX MY_TABLE_league_away_season_cust
    ON MY_TABLE(
        League, AwayTeam, Season, CustomerID
    );

SELECT
  Team,
  League,
  Season,
  COUNT(DISTINCT CustomerID)     AS total
FROM 
(
  SELECT CustomerID, League, HomeTeam AS Team, Season FROM MyTable
  UNION ALL
  SELECT CustomerID, League, AwayTeam AS Team, Season FROM MyTable
)
  combined_view
GROUP BY
  Team, League, Season
ORDER BY
  total DESC