mysql - 性能不佳的查询需要帮助

Question

我有以下查询，它工作正常，但速度很慢，并且返回结果的速度不够快，无法满足我的需要。

它显示了 11 个位置中最常被选中的 9 名球员。

有没有人看到更好的写法？

select PlayerName, RankName, Position
from
(
select Opener1 as Player from Team
where CompetitionIDAuto = 1
union all
select Opener2 as Player from Team
where CompetitionIDAuto = 1
union all
select Bat1 as Player from Team
where CompetitionIDAuto = 1 
union all
select Bat2 as Player from Team
where CompetitionIDAuto = 1 
union all
select Bat3 as Player from Team
where CompetitionIDAuto = 1 
union all
select WK as Player from Team
where CompetitionIDAuto = 1 
union all
select AR1 as Player from Team
where CompetitionIDAuto = 1 
union all
select Bowl1 as Player from Team
where CompetitionIDAuto = 1 
union all
select Bowl2 as Player from Team
where CompetitionIDAuto = 1  
union all
select Bowl3 as Player from Team
where CompetitionIDAuto = 1 
union all
select Bowl4 as Player from Team
where CompetitionIDAuto = 1 
) a
Inner Join Player on a.Player = Player.PlayerIDAuto
Inner Join RankValue on Player.ODIRank = RankValue.RankIDAuto
Inner Join PlayerPosition on Player.ODIPosition = PlayerPosition.PlayerPositionIDAuto
group by PlayerName
order by count(*) desc
Limit 0,9

Answer 1

如果我对你的问题的理解正确，最好的策略是将你的主源表分成两部分，这样，而不是:

PlayerID_role1 PlayerID_role2 PlayerID_role3 ... CompetitionIDAuto

一个表(团队)看起来像:

PlayerID RoleID CompetitionID

再加一个单独的表格(例如竞赛)来自动计算竞赛，其中将包括所有其他竞赛信息，例如

CompetitionIDAuto CompetitionName CompetitionDate...

然后你可以这样查询:

select PlayerName, RankName, Position from Team where CompetitionIdAuto = 1
Inner Join Player on Team.PlayerID = Player.PlayerIDAuto
Inner Join RankValue on Player.ODIRank = RankValue.RankIDAuto
Inner Join PlayerPosition on Player.ODIPosition = PlayerPosition.PlayerPositionIDAuto
group by Player.PlayerIDAuto
order by count(*) desc
Limit 0,9

注意:不要按 PlayerName 分组，因为 PlayerIDAuto(我猜)是一个自动递增的主索引字段，而 PlayerName 很可能根本不是索引。根据定义，按非索引字段分组很慢。 (您也可以对当前表的查询进行此改进)。