麻烦优化MySQL查询(Trouble optimizing MySQL query)

我正在下面的查询，但不知道如何与进一步的优化着手：

SELECT u.id AS userId, firstName, profilePhotoId, preferredActivityId, preferredSubActivityId, availabilityType,
       3959 * ACOS(COS(radians(requestingUserLat)) * COS(radians(u.latitude)) * COS(radians(u.longitude) - radians(requestingUserLon)) + SIN(radians(requestingUserLat)) * SIN(radians(u.latitude))) AS distanceInMiles
  FROM users u
 WHERE u.id IN (
        SELECT uu.id
          FROM users uu
         WHERE uu.latitude      between lat1    and lat2 -- MySQL 5.7 supports Point data type, but it is not indexed in innoDB. We store latitude and longitude as DOUBLE for now
           AND uu.longitude     between lon1    and lon2
           AND uu.dateOfBirth   between maxAge  and minAge -- dates are in millis, therefore maxAge will have a smaller value than minAge and so it needs to go first
     )
   AND IF(gender       is null, TRUE, u.gender = gender)
   AND IF(activityType is null, TRUE, u.preferredActivityType = activityType)
   AND u.accountState = 'A'
   AND u.id != userId
HAVING distanceInMiles < searchRadius ORDER BY distanceInMiles LIMIT pagingStart, pagingLength;

CREATE INDEX `findMatches` ON `users` (`latitude` ASC, `longitude` ASC, `dateOfBirth` ASC) USING BTREE;

这里的想法是，以具有标识基于用户位置和年龄限定行，使用上面指定的覆盖索引的内查询。在具有几百万行的表，变窄下来到几千而不需要进行全表扫描。此时的减少结果集的完全扫描是不可避免的 - 然后将所得的列被抵靠着更细粒度的条件下，如性别，可用性等进行测试。

这将运行“几乎”如预期， EXPLAIN揭示了内部查询确实使用了覆盖索引的全密钥长度（3列），然后外部查询查找返回的行，通过PK。

问题：
性能是令人满意的，而搜索范围是几百英里之内，但是当我去千里，它开始降低，因为指定的边界内增加的用户数量。该问题也将变得明显，如果搜索范围保持不变，但用户数量增加了几个数量级。下面是我迄今发现的问题：

MySQL的目前不支持LIMIT在内的查询，因此内部查询将返回所有符合条件的userIDs （即千），即使外部查询然后将限制他们只是一个十几。
启用optimizer_trace看着幕后工作表明，仅列latitude我的覆盖指数被用作range 。我不知道这是为什么，特别是因为EXPLAIN表明，完整的索引键长度被使用。

问题：
我怎么解决（2）上述（1）和？之前有人建议使用lat和长空间数据类型，请注意最新的InnoDB引擎（MySQL的V5.7）不支持空间索引，只是空间数据类型。

Answer 1:

您可以简化您的查询：

SELECT u.id AS userId, firstName, profilePhotoId, preferredActivityId, preferredSubActivityId, availabilityType,
       3959 * ACOS(COS(radians(requestingUserLat)) * COS(radians(u.latitude)) * COS(radians(u.longitude) - radians(requestingUserLon)) + SIN(radians(requestingUserLat)) * SIN(radians(u.latitude))) AS distanceInMiles
  FROM users u
   WHERE u.latitude between lat1 and lat2
    AND u.longitude between lon1 and lon2
    AND u.dateOfBirth between maxAge and minAge
    AND IF(gender is null, TRUE, u.gender = gender)
    AND IF(activityType is null, TRUE, u.preferredActivityType = activityType)
    AND u.accountState = 'A'
    AND u.id != userId
HAVING distanceInMiles < searchRadius
ORDER BY distanceInMiles
LIMIT pagingStart, pagingLength;

然后在所有的列上创建索引where子句中，您可以在索引列的顺序播放，开始具有较少的不同值的列（如性别，州）

Answer 2:

我觉得这已经涵盖在其他的答案。有使用数据的索引的查询，并使用该索引的数据，以寻求正确的解决方案之间的差异。后者是最有效的使用索引。前者是有帮助的，但效率却只是在数据页不读。

我想你可以提高你的查询使用exists的，而不是in 。这应该允许外部级别过滤，以提高查询性能：

SELECT u.id AS userId, firstName, profilePhotoId, preferredActivityId, preferredSubActivityId, availabilityType,
       3959 * ACOS(COS(radians(requestingUserLat)) * COS(radians(u.latitude)) * COS(radians(u.longitude) - radians(requestingUserLon)) + SIN(radians(requestingUserLat)) * SIN(radians(u.latitude))) AS distanceInMiles
FROM users u
WHERE EXISTS (SELECT 1
              FROM users uu
              WHERE uu.latitude      between lat1    and lat2  AND
                    uu.longitude     between lon1    and lon2 AND
                    uu.dateOfBirth   between maxAge  and minAge  AND
                    uu.id = u.id
             ) AND
     IF(gender       is null, TRUE, u.gender = gender) AND
     IF(activityType is null, TRUE, u.preferredActivityType = activityType) AND
     u.accountState = 'A' AND
     u.id <> userId
HAVING distanceInMiles < searchRadius
ORDER BY distanceInMiles LIMIT pagingStart, pagingLength;

作为一个说明，表达IF(gender is null, TRUE, u.gender = gender)是相当无意义的，因为它总是计算为true。如果你有一个叫做变量gender ，它不会在这个表达式中使用。的gender将根据MySQL的范围规则和在表中的列进行解释。你应该总是使用像一个前缀var_或p_或东西从表中的列区分参数。

编辑：

我应该提到的是，指数需要包括id与使用第一列exists 。

文章来源: Trouble optimizing MySQL query