对于某些参数(MySQL)，SQL查询非常慢。

Question

我正在制作一个PHP后端API，它在MySQL数据库上执行一个查询。这是一个查询：

SELECT * FROM $TABLE_GAMES WHERE 
 ($GAME_RECEIVERID = '$userId'OR $GAME_OTHERID = '$userId')
  ORDER BY $GAME_ID LIMIT 1"

本质上，我将$userId作为参数传递，并获得具有最小$GAME_ID值的行，对于表中有大约30000行匹配行的用户，它的返回值将少于100 ms。但是，我已经添加了新用户，它们大约有<100行匹配行，而且查询速度非常慢，每次大约花费20-30秒。

我很困惑为什么在应该返回低行数的情况下，查询要慢得多，在返回大量行时(特别是因为我有ORDER BY )时，查询速度非常快。

我读过有关参数嗅探的文章，但据我所知，这是SQL服务器的问题，我正在使用MySQL。

编辑

下面是SHOW CREATE语句：

CREATE TABLE游戏( IDint(11) NOT NULL AUTO_INCREMENT, SenderIDint(11) NOT NULL, ReceiverIDint(11) NOT NULL, OtherIDint(11) NOT NULL,时间戳timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (ID) ) ENGINE=MyISAM AUTO_INCREMENT=17275279 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

这是EXPLAIN的输出

+----+-------------+-------+------+---------------+------+---------+-----+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | +----+-------------+-------+------+---------------+------+---------+-----+------+-------+ | 1 | SIMPLE | games | NULL | index | NULL | PRIMARY | 4 | NULL | 1 | +----+-------------+-------+------+---------------+------+---------+-----+------+-------+

id select_type table partitions type possible_keys key key_len ref rows filtered Extra

1 SIMPLE games NULL index NULL PRIMARY 4 NULL 1 19.00 Using where

我试过准备好的陈述，但结果还是一样。

抱歉，我的格式很差，我还在做这件事。

Answer 1

您的查询速度很慢，因为它找不到匹配的记录。在很多行匹配的用户中，找到返回记录的机会要高得多，所有其他条件都是相等的。

当$GAME_RECEIVERID和$GAME_OTHERID不是索引的一部分时，就会出现这种行为，这会促使MySQL在$GAME_ID上使用索引，因为排序是这样的。然而，由于较新的玩家没有玩早期的游戏，实际上有数以百万计的行将不匹配，但仍然需要检查。

不幸的是，即使对老用户来说，随着数据库的增长，这种情况也会变得更糟。理想情况下，您将在$GAME_RECEIVERID和$GAME_OTHERID上添加索引--如下所示：

ALTER TABLE games
ADD INDEX receiver (ReceiverID),
ADD INDEX other (OtherID)

PS:修改一个1700万行表需要一段时间，所以如果在生产中使用它，请确保在维护窗口或类似的情况下这样做。

Answer 2

您需要使用EXPLAIN来分析查询的性能。

即

 EXPLAIN SELECT * FROM $TABLE_GAMES WHERE 
  ($GAME_RECEIVERID = '$userId'OR $GAME_OTHERID = '$userId') 
   ORDER BY $GAME_ID LIMIT 1"

EXPLAIN将提供有关select查询的信息和执行计划。这是一个很好的工具，可以识别查询中的慢度。根据所获得的信息，可以为WHERE子句中使用的列创建WHERE。

CREATE INDEX index_name ON table_name (column_list)

这肯定会提高查询的性能。

Answer 3

这是插补后的查询吗？也就是说，这就是MySQL将要看到的吗？

SELECT * FROM GAMES
     WHERE RECEIVERID = '123'
        OR OTHERID    = '123'
     ORDER BY ID LIMIT 1

然后，这将运行得很快，不管：

SELECT *
    FROM GAMES
    WHERE ID = LEAST(
         ( SELECT MIN(ID) FROM GAMES WHERE RECEIVERID = '123' ),
         ( SELECT MIN(ID) FROM GAMES WHERE OTHERID    = '123' )
                    );

但是，你需要这两种方法：

INDEX(RECEIVERID, ID),
INDEX(OTHERID, ID)

您的查询版本正在扫描表，直到找到匹配的行为止。我的版本会

1. 进行两个索引查找；
2. 为一行获取其他列。

无论用户It有多少行，它都将是相同的、快速的、速度的。

(建议改用InnoDB。)