php - 高 CPU 消耗的 Mysql 请求与 php

Question

我目前正在为一个 iOS 应用测试我所有的 php 服务。其中一个似乎非常快地消耗了我所有的 3 个 cpu 内核(而没有其他)。该服务获取用户实际所在位置最近的 51 个“气泡”(bulles)。这是我的 PHP:

$sql="SELECT *, get_distance('$latitude','$longitude',latitude,longitude) AS proximite
FROM bulles INNER JOIN
(SELECT userId, userFirstName, userLastName, userName, userPictureUrl from user) AS users
ON bulles.auteur = users.userId
INNER JOIN interests
ON bulles.interet1 = interests.id
WHERE auteur = '$userID'
OR auteur
IN (
    SELECT followedId
    FROM following
    WHERE followerId = '$userID'
)
ORDER BY proximite
LIMIT 51";

这是我存储的函数“get_distance”:

FUNCTION `get_distance`(lat1 DOUBLE, lng1 DOUBLE, lat2 DOUBLE, lng2 DOUBLE) RETURNS double
BEGIN
    DECLARE rlo1 DOUBLE;
    DECLARE rla1 DOUBLE;
    DECLARE rlo2 DOUBLE;
    DECLARE rla2 DOUBLE;
    DECLARE dlo DOUBLE;
    DECLARE dla DOUBLE;
    DECLARE a DOUBLE;

    SET rlo1 = RADIANS(lng1);
    SET rla1 = RADIANS(lat1);
    SET rlo2 = RADIANS(lng2);
    SET rla2 = RADIANS(lat2);
    SET dlo = (rlo2 - rlo1) / 2;
    SET dla = (rla2 - rla1) / 2;
    SET a = SIN(dla) * SIN(dla) + COS(rla1) * COS(rla2) * SIN(dlo) * SIN(dlo);
    RETURN (6378137 * 2 * ATAN2(SQRT(a), SQRT(1 - a)));
END

每秒 10 个此服务请求会使 CPU 饱和。我之前在使用 INNER JOIN 语句时遇到过问题，所以它可能来自那里？有什么想法吗？

Answer 1

MySQL 'IN' 的效率非常低(虽然是一个很好的构造)，因为对每一行都重新处理了查询。即

IN (
    SELECT followedId
    FROM following
    WHERE followerId = '$userID'
)

将其替换为 FROM 子句中的表。例如:

...FROM ( SELECT followedId FROM following WHERE followerId = '$userID') AS whatever 
(then in the WHERE put ) OR auteur = whatever.followedId...

干杯。