MySQL的索引好吗? [英] MySQL are my indexes good?
问题描述
我想知道我的索引设置在MySQL(InnoDB)中是否正确.我不是索引方面的专家，但是根据我对索引的了解，我很了解.为了确保我是对的，我想问这个社区的成员.
I am wondering if my Index setup is correct in MySQL (InnoDB). I am not an expert on indexes but from what I read about them I hope I understood well. To make sure I am right, I would like to ask members of this community.
我有两个表:
1)用户"，其字段为:id，全名，状态，性别，...
1) "users", with fields: id, fullname, status, sex,...
2)订单"，其字段ID为id_user，order_date
2) "orders", with fields id, id_user, order_date
找到订单后，我将其通过"id_user"链接到表"users"(id).
When I locate an order I link it through "id_user" to table "users" (id).
大约有1个mio用户，订单"中的状态"字段可以有10个不同的值.
There are around 1 mio of users and "status" field in "orders" can have 10 different values.
主索引
我在"users:id"字段上有一个主索引，当我执行诸如从用户中使用SELECT INDEX(PRIMARY)WHERE(id = 33)进行查询"之类的查询以及将订单链接到以下位置时，我认为该索引很有用某些用户.我在订单"表中有类似的索引.可能没关系.
I have a PRIMARY index on field "users:id", which I believe it is useful when I do queries like "SELECT FROM users USE INDEX (PRIMARY) WHERE (id=33)" and when I will link order to certain user. Similar index I have in table "orders". This is probably ok.
因为我将按订单状态搜索订单，然后将其链接到正确的用户，所以我在表"orders"中的两个字段上创建了索引:
Because I will search orders by their status and then link it to correct user, I created an index on two fields in table "orders":
索引名称"status__id_user"，字段(状态，id_user)
Index name "status__id_user", fields (status, id_user)
我列出新订单并将其与合适的用户链接的查询如下:
My query to list new orders and link it with right users looks like:
SELECT *从订单USE INDEX(status__id_user)，用户USE INDEX(PRIMARY)在(orders.status ='new')AND(orders.id_user = users.id)的情况下
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会将其链接到用户"表以获取正确的用户.
Question
Do I need index on two fields just because I am linking to users table with field "id_user"? Does it speed up things or it would be same if my index in "orders" table is just by field "status"? In that case 100,000 records would be selected and then MySQL would link it to "users" table to get correct users.
我的问题是我的索引是否无论如何都有助于加快查询的这一部分:
My question is if my index helps in anyway to speed up this part of the query:
AND(orders.id_user = users.id)
AND (orders.id_user = users.id)
谢谢！
推荐答案
以这种方式编写查询:
SELECT *
FROM orders
JOIN users ON orders.id_user = users.id
WHERE orders.status='new'
不指定要使用的索引.
从WHERE子句开始，看来可能很有用:INDEX(status).但是由于状态"听起来像基数低的标志"，因此优化器可以决定忽略该索引，而只是进行表扫描. 这可以.之所以可以，是因为在索引不是非常有选择性的情况下，进行表扫描要比在索引和数据之间反弹要快.无论如何，都应将决定权留给优化器.
Starting with the WHERE clause, it looks like this might be useful: INDEX(status). But since "status" sounds like a "flag" with low cardinality, the optimizer may decide to ignore the index and simply do a table scan. This is OK. It is OK because it is faster to do a table scan than bounce between an index and the data, when the index is not very selective. In any case, leave the decision up to the Optimizer.
现在它正在处理orders，它需要从JOIN到users.唯一的方法是在id上有一个索引.该名称("id")暗示它可能是PRIMARY KEY，对吗? (请提供SHOW CREATE TABLE.)
Now that it is dealing with orders, it needs to JOIN to users. The only way to do that is to have an index on id. That name ("id") implies that it might be the PRIMARY KEY, is it? (Please provide SHOW CREATE TABLE.)
您提到的另一个查询应该写
The other query you mentioned should be written
SELECT * FROM users WHERE id=33
而且，正如已经讨论过的，id上的索引(PRIMARY KEY?)是正确的事情.
And, as already discussed, the index (PRIMARY KEY?) on id is the right thing.
INDEX(status, id_user)没有优势(至少对于给定的SELECTs).您的选择包括所有列(*)；如果它只提取了id_user，则这样的索引将是覆盖"的，并且具有一定的优势.
There is no advantage (for the given SELECTs, at least) for INDEX(status, id_user). Your select includes all columns (*); it it had fetched only id_user, then such an index would be "covering", and have some advantage.
这篇关于MySQL的索引好吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
