无法优化mySQL查询

Question

我正在运行一个查询，从MySQL数据库中检索一些游戏级别。查询本身大约需要0.00025秒才能在包含40个级别字符串的基础上执行。我认为这是令人满意的，直到我收到一条来自网站主机的消息，告诉我优化下面的查询，否则脚本将被删除，因为它给他们的服务器带来了很大的压力。

我尝试过使用explain和explain扩展优化，并相应地调整列(添加索引)，但性能总是相同的。我还注意到，MySQL并没有在可用索引的地方使用索引，而是进行了全表扫描。

解释扩展的结果：

table   id  select_type  type   possible_keys   key     key_len   ref            rows   Extra
users   1   SIMPLE       ALL    PRIMARY,id      NULL     NULL     NULL           7      Using temporary; Using filesort
AllTime 1   SIMPLE       ref    PRIMARY,userid  PRIMARY  4        Test.users.id  1  

查询：

    SELECT users.nickname, AllTime.userid, AllTime.id, AllTime.levelname, AllTime.levelstr
    FROM AllTime
    INNER JOIN users
    ON AllTime.userid=users.id
    ORDER BY AllTime.id DESC
    LIMIT ($value_from_php),20;

表:用户

| id(int)                   | nickname(varchar) | 
| (Primary, Auto_increment) |                   |
|---------------------------|-------------------|
| 1                         | username1         |
| 2                         | username2         |
| 3                         | username3         |
| ...                       | ...               |

和AllTime

| id(int)                   | userid(int) | levelname(varchar) | levelstr(text) |
| (Primary, Auto_increment) | (index)     |                    |                |
|---------------------------|-------------|--------------------|----------------|
| 1                         | 2           | levelname1         | levelstr1      |
| 2                         | 2           | levelname2         | levelstr2      |
| 3                         | 3           | levelname3         | levelstr3      |
| 4                         | 1           | levelname4         | levelstr4      |
| 5                         | 1           | levelname5         | levelstr5      |
| 6                         | 1           | levelname6         | levelstr6      |
| 7                         | 2           | levelname7         | levelstr7      |

是否有优化此查询的方法?或者，为了避免警告，从php调用两个连续的查询会更好吗？

我只是在学习MySQL，所以在回复时请考虑到这些信息，谢谢:)

Answer 1

我假设你在使用InnoDB。

对于内部联接，MySQL通常以最少行开始表，在本例中为users。但是，由于您只想让最新的20条AllTime记录与相应的user记录连接起来，所以实际上应该从AllTime开始，因为它是从LIMIT开始的，所以它将是较小的数据集。

使用STRAIGHT_JOIN强制执行连接顺序：

SELECT users.nickname, AllTime.userid, AllTime.id, AllTime.levelname, 
   AllTime.levelstr
FROM AllTime
STRAIGHT_JOIN users
ON users.id = AllTime.userid
ORDER BY AllTime.id DESC
LIMIT ($value_from_php),20;

它应该能够使用AllTime表上的主键，并按降序执行。它会在相同的页面上抓取所有的数据。

它还应该使用用户表上的主键来获取id和昵称。如果有两个以上的列，您可以添加一个多列覆盖索引(id，昵称)来提高速度。

如果可以的话，将levelstr列转换为VARCHAR，以便将数据存储在与其他数据相同的页面上，否则，它必须分别提取文本列。这假定您的列在InnoDB的8000字节行限制之下。除非去掉文本列，否则无法避免USING TEMPORARY。

最有可能的是，您的主机已经通过使用慢速查询日志来标识该查询，该日志可以标识所有不使用索引的查询，或者由于Using temporary而将其标记为红色。

Answer 2

看起来查询没有问题。

1. 检查应用程序代码。很可能问题在代码中。
2. 检查MySQL查询执行计划
   • 可能你缺少了一个索引

​

1. 确保在应用程序和数据库中缓存数据(fyi，有时可以将整个数据库加载到应用程序内存中)
2. 确保使用连接池
3. 创建一个视图(一个很小的改进机会)
4. 尝试删除"Order“子句(同样，这将提高性能的可能性非常小)。

Answer 3

查询本身大约需要0.00025秒.我从网站主机收到一条消息，告诉我优化下面提到的查询，否则脚本会被删除，因为它给服务器带来了很大的压力。

询问网站主机有关此查询为何被标记为注意的更多详细信息。一个琐碎的查询不会对任何事情造成压力，除非它经常被调用。

找出该查询运行了多少次。我敢跟你打赌，你的网站正被机器人敲击，每分钟被执行数百次或数千次。如果是的话，那就是你真正的问题。