使用子查询时出现奇怪的性能下降

mftmpeh8 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(338)

我有一个nano aws服务器运行mysql 5.5进行测试。因此，请记住服务器的资源有限（ram、cpu等等）。
我有一张table叫“ gpslocations ". 它的主键上有一个主索引“ GPSLocationID ". 其中一个字段上有另一个二级索引“ userID ". 该表有6583条记录。
运行此查询时：

select * from gpslocations where GPSLocationID in (select max(GPSLocationID) from gpslocations where userID in (1,9) group by userID);

我坐了两排，要花很多时间：

+---------------+---------------------+------------+-----------+--------------------------------------+--------+--------------------------------------+-------+-----------+----------+---------------------+----------------+----------+-----------+-----------+
| GPSLocationID | lastUpdate          | latitude   | longitude | phoneNumber                          | userID | sessionID                            | speed | direction | distance | gpsTime             | locationMethod | accuracy | extraInfo | eventType |
+---------------+---------------------+------------+-----------+--------------------------------------+--------+--------------------------------------+-------+-----------+----------+---------------------+----------------+----------+-----------+-----------+
|          4107 | 2018-09-25 16:38:44 | 58.7641435 | 7.4868510 | e5d6fdff-9afe-44bb-a53a-3b454b12c9c6 |      9 | 77385f89-6b72-4b9e-b937-d2927959e0bd |     0 |         0 |      2.9 | 2018-09-25 18:38:43 | fused          |      455 | 0         | android   |
|          9822 | 2018-10-22 10:29:43 | 58.7794353 | 7.1952995 | 5240853e-2c36-4563-9dc3-238039de411e |      1 | 1fcad5af-c6ef-4bda-8fb2-d6e5688cf08a |     0 |         0 |    185.6 | 2018-10-22 12:29:41 | fused          |      129 | 0         | android   |
+---------------+---------------------+------------+-----------+--------------------------------------+--------+--------------------------------------+-------+-----------+----------+---------------------+----------------+----------+-----------+-----------+
2 rows in set (14.96 sec)

当我执行内部选择时：

select max(GPSLocationID) from gpslocations where userID in (1,9) group by userID;

我很快得到两个值：

+--------------------+
| max(GPSLocationID) |
+--------------------+
|               9822 |
|               4107 |
+--------------------+
2 rows in set (0.00 sec)

当我获取这两个值并在外部选择中手动写入它们时：

select * from gpslocations where GPSLocationID in (9822,4107);

我得到了与第一个查询完全相同的结果，但很快！

+---------------+---------------------+------------+-----------+--------------------------------------+--------+--------------------------------------+-------+-----------+----------+---------------------+----------------+----------+-----------+-----------+
| GPSLocationID | lastUpdate          | latitude   | longitude | phoneNumber                          | userID | sessionID                            | speed | direction | distance | gpsTime             | locationMethod | accuracy | extraInfo | eventType |
+---------------+---------------------+------------+-----------+--------------------------------------+--------+--------------------------------------+-------+-----------+----------+---------------------+----------------+----------+-----------+-----------+
|          4107 | 2018-09-25 16:38:44 | 58.7641435 | 7.4868510 | e5d6fdff-9afe-44bb-a53a-3b454b12c9c6 |      9 | 77385f89-6b72-4b9e-b937-d2927959e0bd |     0 |         0 |      2.9 | 2018-09-25 18:38:43 | fused          |      455 | 0         | android   |
|          9822 | 2018-10-22 10:29:43 | 58.7794353 | 7.1952995 | 5240853e-2c36-4563-9dc3-238039de411e |      1 | 1fcad5af-c6ef-4bda-8fb2-d6e5688cf08a |     0 |         0 |    185.6 | 2018-10-22 12:29:41 | fused          |      129 | 0         | android   |
+---------------+---------------------+------------+-----------+--------------------------------------+--------+--------------------------------------+-------+-----------+----------+---------------------+----------------+----------+-----------+-----------+
2 rows in set (0.00 sec)

当两个简单而快速的查询结合在一起时，有人能解释这种巨大的性能下降吗？
编辑
下面是 explain :

+----+--------------------+--------------+-------+----------------------+--------+---------+------+------+---------------------------------------+
| id | select_type        | table        | type  | possible_keys        | key    | key_len | ref  | rows | Extra                                 |
+----+--------------------+--------------+-------+----------------------+--------+---------+------+------+---------------------------------------+
|  1 | PRIMARY            | gpslocations | ALL   | NULL                 | NULL   | NULL    | NULL | 6648 | Using where                           |
|  2 | DEPENDENT SUBQUERY | gpslocations | range | userNameIndex,userID | userID | 5       | NULL |   11 | Using where; Using index for group-by |
+----+--------------------+--------------+-------+----------------------+--------+---------+------+------+---------------------------------------+
2 rows in set (0.00 sec)

sql mysql

来源：https://stackoverflow.com/questions/53026487/strange-performance-degradation-when-using-a-subquery

1条答案

按热度按时间

fhity93d1#

in 可能有非常糟糕的优化特性。在您的mysql版本中，子查询可能会对中的每一行运行一次 gsplocations . 我认为这个性能问题在以后的版本中已经解决了。
我建议改用相关子查询：

select l.*
from gpslocations l
where l.GPSLocationID = (select max(l2.GPSLocationID)
                         from gpslocations l2
                         where l2.userID = l.userId
                        ) and
      l.userID in (1, 9);

对于这个，你需要一个索引 gpslocations(userID, GPSLocationID) .
另一种选择是 join 方法：

select l.*
from gpslocations l join
     (select l2.userID, max(l2.GPSLocationID)
      from gpslocations l2
      where l2.userID in (1, 9)
     ) l2
     on l2.userID = l.userId
where l.userID in (1, 9);

赞(0）回复(0）举报 2021-06-18

我来回答

使用子查询时出现奇怪的性能下降

1条答案

相关问题

热门标签

最新问答