使用 PHP 和 MySQL 优化排名页面

Question

我确实需要一些帮助来优化我网站上用于显示排名的表格。我已经阅读了很多有关如何优化查询以及如何正确使用索引的文章，但即使在实施了我认为可行的更改之后，也看不到什么改进。我的快速解决办法是仅使用前 100,000 个排名（每天更新并存储在不同的表中）来提高目前的速度，但我真的不喜欢这个选项。

因此，我有一个存储用户信息的表，如下所示：

表“缓存”：

id    (Primary key)
name
region
country
score

还存储有关用户的其他变量，但我认为它们与此处无关，因为它们未在排名中使用。

用户可以查看 3 个基本排名页面：

世界视图：

SELECT cache name,region,country,score FROM cache ORDER BY score DESC LIMIT 0,26

区域视图：

SELECT name,region,country,score FROM cache WHERE region='Europe' ORDER BY score DESC LIMIT 0,26

和国家视图：

SELECT name,region,country,score FROM cache WHERE region='Europe' AND country='Germany' ORDER BY score DESC LIMIT 0,26

我已经尝试了几乎所有我能想到的索引组合，以帮助减轻数据库的工作，虽然有些似乎有点帮助我找不到一个只返回 26 行的地区和国家查询（只需一个“分数”索引，世界排名就非常快）。

我觉得我可能缺少一些基本的东西，任何帮助将不胜感激！

额外的信息：缓存表目前约为 920 MB，总共略多于 800,000 行。如果您需要更多信息，请告诉我。

Answer 1

您的世界排名受益于分数索引，因为分数是查询中的唯一标准。它排序的逻辑顺序内置于查询中。所以这样很好。

其他查询将受益于区域索引。然而，与@Matt 指出的类似，地区、国家和分数的综合指数可能是最好的选择。请注意，键的三列应按地区、国家、分数顺序排列。

Answer 2

对国家、分数、地区建立一个索引。太多的索引会减慢你的速度。

Answer 3

我们谈论的是多少条记录？

我不是 SQL 专家，但在这种情况下，如果仅仅更改索引并不能解决问题。我会考虑尝试一下表结构，看看我可以获得什么性能增益。

缓存

ID (pk)
LocationId（索引）
姓名
得分

位置

LocationId (pk)
CountryId（索引）也许
RegionId（索引）可能是

国家

CountryId
名称

区域

RegionId
名称

位置

LocationId （主键）
国家/地区 ID
ID 国家

地区

国家 ID
名称

区域

RegionId
Procs 中的名称

临时表将允许您在每种情况下选择位置 ID。它将降低您遇到的问题的整体复杂性：您将解决 1 个查询计划，而不是 3 个查询计划。

工作量会很高，并且在完成之前会得到回报，所以我建议查看索引方法第一的。

祝你好运