综合主键和二级覆盖索引:二级索引是否需要包含主键列？

Question

MySQL。InnoDB。

假设有一个复合主键'a，b‘。还有另一栏“C”我有一个查询点击'a，C‘考虑两项索赔。(A)“关于'c‘的二级索引为我提供了一个覆盖指数。”(B)“必须包括'a，c‘的二级索引。”

这是真的吗？

对于背景，InnoDB向每个辅助索引添加主键。

Answer 1

掩护；是，"A“。有用的；这取决于。按照这个顺序，有效的二级指数将是(c,a,b)。

这是一个SELECT的“覆盖”，它在任何组合和任何安排中都只包含a、b、c。

但该指数在其他方面可能没有用。

WHERE c=32   -- the secondary index will probably be used, even if not covering
WHERE a=12   -- will prefer the PK
WHERE b=22   -- can't use that index for filtering, but may be able to "cover".
WHERE a > 55 AND c > 55  -- may use `c` part of secondary, or `a` part of PK
WHERE DATA(c) = CURDATE()  -- will scan whole index (cf "sargable")

等等

InnoDB在每个辅助键的BTree中包含PK列的值(在您的情况下是a,b)，这样它就可以在数据的BTree中定位整个行。

(上面的信息对于InnoDB是正确的，但对于MyISAM则不正确。)

最左边的vs覆盖

最左边的规则是，在计算WHERE、GROUP BY或ORDER BY时，只使用最左边的列。一旦遇到没有用的列(索引中的列)，索引的排序和查找功能通常会被忽略。

WHERE b = 888 AND c = 999

将只使用c部分的INDEX(c,a,b)。这是因为所有带有c=999的行在索引中都很好地相邻。但是b=888行分散在它们中间。

报道侧重于一种不同的技术。

数据存储在由BTree命令的PRIMARY KEY (参见维基百科)中。每个“二级”索引存储在不同的BTree中，但按二级索引的列(S)和PK列(S)排序。也就是说，将您的INDEX(c)看作是c,a,b命令的BTree。a,b的用途，以便您可能需要的任何其他列都可以通过使用a,b查找数据BTree中的完整行来获取。

如果查询只需要这3列，则索引称为“覆盖”。这避免了进入其他列的其他数据BTree的额外步骤。这个额外的步骤是通过“覆盖”节省的费用。

请注意，“最左边”关心列的顺序；“覆盖”不关心“。在任何特定的查询中都可能起作用。

(这个词是我编的。)假设你有：

SELECT d FROM tbl WHERE c = 123

为了有效地查找，您需要INDEX(c)。因此，任何以c开头的索引都会提高WHERE的效率。

要创建“覆盖”索引，请注意查询中只存在d和c。所以，INDEX(d,c)，INDEX(c,d)，甚至INDEX(x,y,d,c)都会报道。

将两者结合在一起：

INDEX(c, d)  -- is optimal

或

INDEX(c, d, ...) -- You may have other queries that would benefit from this.

另一个例子是：

SELECT *        -- may as well forget about "covering"
    FROM tbl
    WHERE user_id = 888
    ORDER BY post_date DESC
    LIMIT 10

这个查询很漂亮，因为它给出了一个例子，说明索引甚至可以处理LIMIT：

INDEX(user_id,     -- is a simple "=" and completely handles the WHERE
      post_date)   -- the index can be used for ordering
                   -- it can stop after 10 items  :)