mysql - 如何确定需要为此多连接多条件查询创建哪个索引？

Question

我认为我对如何为复杂查询构建索引缺乏基本的了解。给定以下查询，表 PHOTOS 上的最佳索引是什么？我觉得应该可以知道而无需进行大量的试验和错误(相信我，我已经......)

这是我目前拥有的索引，它给了我使用索引条件；使用临时的；使用文件排序。如果可能的话，我想摆脱临时/文件排序位。

testIndex: photoTypeID, photoID, photographerID, shootID, timeAdded

这是查询(实际上更复杂，但我已将其精简为最少的相关PHOTOS-索引相关内容:

SELECT 
SQL_CALC_FOUND_ROWS

PH.photoID

FROM      PHOTOS                PH
JOIN      PHOTO_TYPES           PT  ON  PT.photoTypeID     = PH.photoTypeID
LEFT JOIN PHOTO_LINKS           PL  ON  PL.photoID         = PH.photoID
LEFT JOIN PAYPAL_ITEMS          PI  ON  PI.photoID         = PH.photoID
LEFT JOIN PHOTOGRAPHER_PRODUCTS PP  ON  PP.photographerID  = PH.photographerID

WHERE PH.shootID = 1234

GROUP BY PH.photoID

ORDER BY PH.timeAdded DESC

LIMIT 0, 100;

解释输出:

id  select_type     table   type    possible_keys       key             key_len ref             rows    filtered    Extra
1   SIMPLE          PH      ref     PRIMARY,testIndex   testIndex       4   const               80632   100         Using index condition; Using temporary; Using filesort
1   SIMPLE          PT      eq_ref  PRIMARY             PRIMARY         1   PH.photoTypeID      1       100         Using index
1   SIMPLE          PL      ref     photoID             photoID         4   PH.photoID          1       100         Using index
1   SIMPLE          PCI     ref     photoID             photoID         5   PH.photoID,const    1       100         Using index
1   SIMPLE          PP      ref     photographerID      photographerID  4   PH.photographerID   4       100         Using where; Using index

Answer 1

您需要一个复合INDEX(shootID, photoID)，正如我所描述的in my indexing cookbook .

• shootID 是 WHERE 中的 =，因此它排在第一位
• 由于所有 WHERE 均已处理，我们可以继续...
• 添加所有GROUP BY，即photoID
• 但是我们必须到此为止，因为 ORDER BY 与 GROUP BY 不同。

所以... ORDER BY 和 LIMIT 将需要临时和文件排序。

但是...还有另一个问题(可能):当您执行JOIN时，(通常)会增加行数。 “明显”的解决方法是执行 GROUP BY 来缩小它。但任何聚合(以及 SQL_CALC_FOUND_ROWS)都会计算夸大的值。这就是“问题”。

所以...让我们尝试在执行JOIN 之前获取计数。可是等等!为什么要有连接？你没有从他们那里获取任何东西。而且，由于它们(大部分)是 LEFT JOIN，因此您不会使用这些 JOIN 进行过滤。

好吧，我无法弄清楚查询的意图，但我会给你一个可能有帮助的提示。

SELECT  photoID
    FROM  
    (
        SELECT  SQL_CALC_FOUND_ROWS
                photoID, photoTypeID, photographerID
            FROM  PHOTOS
            WHERE  shootID = 1234
            ORDER BY  PH.timeAdded DESC
            LIMIT  0, 100 
    ) AS PH
    JOIN  PHOTO_TYPES PT  ON PT.photoTypeID = PH.photoTypeID
    LEFT JOIN  PHOTO_LINKS PL  ON PL.photoID = PH.photoID
    LEFT JOIN  PAYPAL_ITEMS PI  ON PI.photoID = PH.photoID
    LEFT JOIN  PHOTOGRAPHER_PRODUCTS PP  ON PP.photographerID = PH.photographerID
    ORDER BY  PH.timeAdded DESC;

该版本将需要INDEX(shootID, timeAdded)。如果它给你正确的答案，它应该快得多并且不使用 tmp 或文件排序。