mysql - 组合键 VS 主键 + 非唯一索引

Question

这是我的:

表格内容:cat_id product_id data1 data2 etc.
这些类别显然不是唯一的。产品 ID 是唯一的。

2 个查询:
1 -- SELECT * WHERE cat_id = :cat - 必须尽可能快
2 -- 选择 * WHERE product_id = :prodId
在第二个选择中，我可以添加:AND cat_id = :cat

什么效率更高？

• 1 - cat_id 上的索引(不唯一)(适合选择 1)
• 2 - product_id 的主键(唯一 -> 非常适合选择 2)
• 3 - cat_id 上的索引(不唯一) + product_id 上的 PK(分别适用于 1 和 2)
• 4 - 复合 [cat_id+product_id] 的唯一约束(适用于 1 和 2)
• 5 - 与 4 相同，但将组合定义为 PK
• 6 - 复合(4 或 5)+ 单一索引/PK

有关信息，我将在每个类别中有大约 20 种产品和很多类别(比如 3000)- 并且(因为它在表中是唯一的)一种产品仅属于一个类别-事实上，这不是真的猫和产品，这是为了简化解释；)

谢谢!

Answer 1

没有主键的数据库只是半成品，根据您的说法，product_id 是主键的理想候选者，所以让我们选择它。主键将用于

SELECT * WHERE product_id = :prodId

and cat_id = :cat_id 是否成为查询的一部分并不重要，除非您有数千个 cat_ids 与每个 product_id .

然后在 cat_id 上选择一个索引。这将用于

SELECT * WHERE cat_id = :cat

如果数据的基数很好，这将非常快。这意味着表中的 cat_id 分布广泛。 cat_id 的索引不会在第一个查询中使用。因此，您有两个不同的索引，并且可以预期两个查询都非常快。

因为 [cat_id+product_id] != [product_id+cat_id] 当涉及到索引时，如果您只有一个复合索引，那么其中一个会很慢。

例如，假设我们现在在 (cat_id, product_id) 上有一个复合索引以下查询无法使用此索引。

 SELECT * FROM tablename WHERE product_id = :prodId

但是这两个查询都可以使用 (cat_id, product_id) 索引

SELECT * FROM tablename WHERE cat_id = :cat_id and product_id = :prodId
SELECT * FROM tablename WHERE cat_id = :catId

综上所述。选择 1 和 2。但是，如果 cat_id 的数量很少，或者每个 product_id 关联的 cat_id 很多，请选择 4，但要确保主键也在地方。