mysql - 如何索引该表(a_level、b_level、item_id)

Question

我有一个项目表，每个项目都有一个 a_level、b_level 和一个 item_id。任何 b_level 仅专用于一个 a_level(例如:b_level 14 仅是 a_level 2 的“子级”)

假设我们有数百万个项目，所有这些项目都插入一次，然后只请求 SELECT。

如果我根据 item_id 选择一个项目，那么我需要为 item_id 列建立索引。这将使 MySQL 查找所有数百万个项目，这很糟糕，因为我已经有了 a_level 和 b_level 信息。所以我想如果我根据特定级别选择一个项目并且我在该列上有一个索引，那么MySQL将不必查找所有数百万个项目，而只需查看具有该特定级别的项目。

如果我在a_level、b_level(当然还有item_id)上都进行索引并且SELECT WHERE a_level= b_level= item_id= 会很糟糕吗？我想只有 INDEX on b_level 和 item_id 以及 SELECT WHERE b_level= AND item_id= 就足够了/最好的解决方案？

那么，既然我有 a_level 和 b_level(正如我所说，任何 b_level 都是一个 a_level 的“子级”)，那么为选取一项而创建的最有效的 SELECT 和 INDEX 是什么？

Answer 1

您当然可以为每一列建立索引。如果这样做，MySQL 将使用 index merge优化将多个索引应用于单个查询。但是，为了提高效率，您可能需要使用复合索引(多个列上的单个索引)。 MySQL复合索引按照left-prefix rule进行优化。 。如果 SELECT 语句受到索引左前缀中包含的术语的限制，则使用该索引。例如，如果您有

SELECT * FROM t WHERE a_level = 1 AND b_level = 2

那么适当的索引必须包含 a_level 或 b_level 作为第一列。换句话说，(a_level, b_level) 的索引可以索引诸如

之类的查询

SELECT * FROM t WHERE a_level = 1
SELECT * FROM t WHERE a_level = 1 AND b_level = 2

但不是

SELECT * FROM t WHERE b_level = 2

因为b_level不是索引的左前缀。

您可能首先想要对您最常执行的选择进行基准测试，并基于该选择创建索引，只要它们遵循左前缀规则即可。您可能希望对几个不同的 SELECT 查询使用多个索引，以免覆盖整个表。在不准确了解数据和查询的情况下，要完美回答这个问题并不容易。

但是，如果您确定永远不会再次写入表中，并且空间不是问题，您也可以用索引覆盖整个内容。