MySQL 我的索引好吗？

Question

我想知道我的索引设置在 MySQL (InnoDB) 中是否正确。我不是索引方面的专家，但从我读到的有关索引的内容来看，我希望能很好地理解它们。为了确保我是对的，我想问一下这个社区的成员。

我有两个表:

1) "users", with fields: id, fullname, status, sex,...

2) "orders", with fields id, id_user, order_date

当我找到订单时，我通过“id_user”将其链接到表“users”(id)。

大约有 1 mio 的用户，“订单”中的“状态”字段可以有 10 个不同的值。

主索引

我在字段“users:id”上有一个 PRIMARY 索引，我相信它在我执行诸如“SELECT FROM users USE INDEX (PRIMARY) WHERE (id=33)”之类的查询以及我将订单链接到某个用户。我在表“订单”中有类似的索引。这可能没问题。

问题

因为我会按状态搜索订单，然后将其链接到正确的用户，所以我在表“订单”中的两个字段上创建了一个索引:

Index name "status__id_user", fields (status, id_user)

我列出新订单并将其与正确用户链接的查询如下所示:

SELECT * FROM orders USE INDEX (status__id_user), users USE INDEX (PRIMARY) WHERE (orders.status='new') AND (orders.id_user = users.id)

问题

我是否需要两个字段的索引只是因为我链接到带有字段“id_user”的用户表？如果我在“订单”表中的索引仅按字段“状态”，它会加快速度还是相同？在这种情况下，将选择 100,000 条记录，然后 MySQL 会将其链接到“用户”表以获取正确的用户。

我的问题是我的索引是否有助于加速这部分查询:

AND (orders.id_user = users.id)

谢谢!

Answer 1

这样写查询:

SELECT *
    FROM orders
    JOIN users  ON orders.id_user = users.id
    WHERE orders.status='new'

不要指定要使用的索引。

从 WHERE 子句开始，看起来这个可能很有用:INDEX(status)。但是由于“状态”听起来像是基数较低的“标志”，优化器可能决定忽略索引并简单地进行表扫描。 这没问题。 这没问题，因为当索引不是很有选择性时，执行表扫描比在索引和数据之间反弹更快。在任何情况下，将决定权交给优化器。

现在它正在处理订单，它需要JOIN到users。唯一的方法是在 id 上建立索引。该名称(“id”)暗示它可能是 PRIMARY KEY，是吗？ (请提供SHOW CREATE TABLE。)

Index cookbook

你提到的另一个查询应该写成

SELECT * FROM users WHERE id=33

而且，正如已经讨论过的，id 上的索引(PRIMARY KEY？)是正确的。

INDEX(status, id_user) 没有优势(至少对于给定的 SELECTs)。您的选择包括所有列 (*)；如果它只获取了 id_user，那么这样的索引将是“覆盖”的，并且具有一些优势。