当添加其他约束时，MySQL停止使用索引 [英] MySQL stops using index when additional constraints are added

问题描述

使用 EXPLAIN 显示以下查询不使用我的索引，有人可以解释一下发生了什么吗？

Using EXPLAIN reveals that the following query does not use my index, could somebody please explain what is going on?

    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 -- MySQL 5.7 supports Point data type, but it is not indexed in innoDB. I store latitude and longitude as DOUBLE for now
     AND u.longitude    between lon1    and lon2
     AND u.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;

此阶段根本不使用该索引。为了使它工作，我需要从 SELECT 语句中注释掉一堆列，并从 WHERE中删除任何未编入索引的列子句。以下工作：

The index is not used at all at this stage. To get it to work, I need to comment out a bunch of columns from the SELECT statement, and also removed any unindexed columns from the WHERE clause. The following works:

    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 -- MySQL 5.7 supports Point data type, but it is not indexed in innoDB. We store latitude and longitude as DOUBLE for now
     AND u.longitude    between lon1    and lon2
     AND u.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;

我试过的其他事情：

我尝试创建3个不同的单部分索引，以及包含所有3个键的多部分索引。根据文档此处，不应该优化器通过创建符合条件的行的UNION来合并它们，从而进一步加快执行速度？它没有这样做，它仍然选择多部分（覆盖）索引。

Other things I tried:
I tried creating 3 distinct single-part indexes, in addition to my multi-part index that contains all 3 keys. Based on the docs here, shouldn't the optimizer merge them by creating a UNION of their qualifying rows, further speeding up execution? It's not doing it, it still selects the multi-part (covering) index.

任何帮助非常感谢！

Any help greatly appreciated!

推荐答案

这有点难以解释。

使用索引的查询正在使用因为索引是一个覆盖索引。也就是说，索引中的所有列都在查询中。真正被有效使用的唯一部分是纬度的条件。

The query that uses the index is using it because the index is a "covering" index. That is, all the column in the index are in the query. The only part of the index really being used effectively is the condition on latitude.

通常情况下，覆盖指数会有仅查询中提到的列。但是，主键用于引用记录，因此我猜测 users.Id 是表中的主键。并且正在扫描索引的有效值纬度。

Normally a covering index would have only the columns mentioned in the query. However, the primary key is used to reference the records, so I'm guessing that users.Id is the primary key on the table. And the index is being scanned for valid values of latitude.

不使用索引的查询不是使用它有两个原因。首先，列的条件是不等式。索引查找只能使用平等条件和一个不等式。这意味着索引只能在其最有效的方法中用于纬度。其次，查询中的其他列无论如何都需要进入数据页。

The query that is not using the index is not using it for two reasons. First, the conditions on the columns are inequalities. An index seek can only use equality conditions and one inequality. That means the index could only be used for latitude in its most effective method. Second, the additional columns in the query require going to the data page anyway.

换句话说，优化器实际上是在说：为什么还要去索引扫描索引，然后扫描数据页？相反，我可以扫描数据页，一次性获取所有内容。

In other words, the optimizer is, in effect, saying: "Why bother going to the index to scan through the index and then scan the data pages? Instead, I can just scan the data pages and get everything all at once."

您的下一个问题无疑是：但是如何让我的查询更快？我的建议是调查空间索引。

Your next question is undoubtedly: "But how do I make my query faster?" My suggestion would be to investigate spatial indexes.

这篇关于当添加其他约束时，MySQL停止使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！