mysql - 为大表放置哪些 SQL 索引

Question

我有两个大表，我主要从中进行选择，但具有 2 个连接的复杂查询非常慢。

第一个表是 GameHistory，我在其中存储每个已完成游戏的记录(我在单独的表中有 15 个游戏)。字段:id、date_end、game_id、..

第二个表是 GameHistoryParticipants，我在其中存储参与特定游戏的每个玩家的记录。字段:player_id、history_id、is_winner

今天获取顶级玩家的查询非常慢(20 秒以上)。查询:

SELECT p.nickname, count(ghp.player_id) as num_games_today  
FROM `GameHistory` as gh 
INNER JOIN GameHistoryParticipants as ghp ON gh.id=ghp.history_id    

INNER JOIN Players as p ON p.id=ghp.player_id     

WHERE TIMESTAMPDIFF(DAY, gh.date_end, NOW())=0 AND gh.game_id='scrabble' 

GROUP BY ghp.player_id ORDER BY count(ghp.player_id) DESC LIMIT 10

第一个表有 150 万条记录，第二个表有 350 万条记录。我应该放置什么索引？ (我尝试了一些，但都很慢)

Answer 1

您只对今天的记录感兴趣。但是，您可以使用 TIMESTAMPDIFF 搜索整个 GameHistory 表来检测这些记录。即使您在该列上有索引，也无法使用它，因为您在该字段上使用了函数。

您应该在 game_id 和 date_end 字段上都有一个索引。然后直接询问date_end值:

WHERE gh.date_end >= DATE(NOW())
AND gh.date_end < DATE_ADD(DATE(NOW()), INTERVAL 1 DAY)
AND gh.game_id = 'scrabble' 

最好在 date_end 的日期部分上建立索引，而不是在带有 date_end 的整个时间上建立索引。然而，这在 MySQL 中是不可能的。因此，请考虑单独为日期部分添加另一列 trunc_date_end ，您将用插入前触发器填充该列。然后，您将在 trunc_date_end 和 game_id 上建立一个索引，这应该可以帮助您立即找到所需的记录。

WHERE gh.trunc_date_end = DATE(NOW())
AND gh.game_id = 'scrabble'