索引还是唯一？什么是更好的MySql

So i have a question.

所以我有一个问题。

Say you have a table requests, which represent a graph. There are 3 columns in the request table, A, B, time. A -> B At time. So what each row represents is a directed connection from A (the requestor) to B (the requestee) at Time T (time is just for organizing data, not really for anything else).

假设您有表请求，表示图表。请求表中有3列，A，B，时间。 A - > B当时。因此，每一行代表的是在时间T从A（请求者）到B（被请求者）的定向连接（时间仅用于组织数据，而不是用于其他任何事情）。

So what is faster if say requests were 1,000,000 rows.
Index(A, B) Index(A) and Index(B) Unique(A, B)?

那么如果说请求是1,000,000行，那么更快。指数（A，B）指数（A）和指数（B）唯一（A，B）？

Thanks guys! And A, B are VARCHAR(32) (MD5's)

多谢你们！ A，B是VARCHAR（32）（MD5的）

Sorry, i forgot a typical query.
I need to be able to see if User A (who logged on) has any requests!
I also will need to search to verify that a user has accepted a correct request, A Accepts B.

对不起，我忘记了一个典型的查询。我需要能够看到用户A（谁登录）是否有任何请求！我还需要搜索以验证用户是否接受了正确的请求，A接受B.

So the statements will look like.

所以这些陈述看起来像。

Any new requests?

有什么新要求吗？

SELECT B, time
FROM requests
WHERE A='$id';

Does A have a request from B?

A有B的要求吗？

SELECT time
FROM requests
WHERE A='$A' and B='$B';

2 个解决方案

#1

In this specific case, use a composite index including A and B. Make sure that A is first in the index. That way when you run these two queries, the index would be used for both.

在此特定情况下，使用包含A和B的复合索引。确保A在索引中是第一个。这样，当您运行这两个查询时，索引将用于两者。

#2

Index and unique are two completely different concepts.

索引和唯一是两个完全不同的概念。

Indexes
An index is a hidden extra column holding the same data sorted with a pointer to your real data. Using an index you can

索引索引是一个隐藏的额外列，其中包含使用指向实际数据的指针排序的相同数据。使用索引即可

Quickly find a particular item
快速找到特定的项目
Quickly find a range of items (between x and y)
快速查找一系列项目（x和y之间）
Save time when using order by because the items are presorted
使用order by时节省时间，因为项目是预先排序的
Save time when using group by because group by needs to match identical items
使用group by时节省时间，因为group by需要匹配相同的项目

This is a normal index, it doesn't mind duplicate values, except for the primary key which is always unique.

这是一个普通索引，它不介意重复值，除了主键始终是唯一的。

Unique (Index)
If you want to avoid duplicate values you can put a unique index on it. This will do all of the above, but add an extra check on every update and insert to check whether that values is not already in the database. If you try to insert a duplicate row on a unique column, MySQL will give an error and refuse your insert.
(you cannot make a row unique without using an index)

唯一（索引）如果要避免重复值，可以在其上放置唯一索引。这将完成上述所有操作，但在每次更新时添加额外的检查并插入以检查数据库中是否已存在该值。如果您尝试在唯一列上插入重复行，MySQL将给出错误并拒绝您的插入。（如果不使用索引，则不能使行唯一）

Use of indexes slows inserts and updates down.
Use of unique indexes slows then even more down.

使用索引会减慢插入和更新速度。使用唯一索引会减慢甚至更慢。

However indexes speed up select a lot, a whole lot.
Unique doesn't speed up anything it makes sure you don't accidentally insert a duplicate row.

然而，索引加速选择很多，很多。 Unique不会加速任何事情，确保您不会意外插入重复的行。

When to use indexes and when not
Don't put an index on every field. As stated above it will slow you inserts and updates down.
Always put an index on join criteria. And seriously consider putting an index on column you use in where clauses a lot.
MySQL will refuse to use an index if 50% of your rows have the same value in a field, so forget about indexes on boolean (Y/N) fields, 99% of the time they will not work.
(Indexes in low cardinality fields are not useful)

何时使用索引，何时不使用索引不要在每个字段上放置索引。如上所述，它会减慢插入和更新速度。始终在连接条件上添加索引。并且认真考虑在where子句中使用的列上放置一个索引。如果50％的行在字段中具有相同的值，MySQL将拒绝使用索引，因此忘记布尔（Y / N）字段上的索引，99％的时间它们将无法工作。（低基数字段中的索引无用）

Always assign a primary key
Always assign a primary key on your table though. Preferably of type integer autoincrement. If you don't assign a primary key, MySQL will assign a 'hidden' primary key for you (of type integer autoincrement), but you cannot use the hidden PK to speed up quotes or identify your rows and there are a host of other slowness problems with hidden PK's which make them suck very badly.

始终分配主键始终在表上分配主键。优选整数自动增量型。如果您没有分配主键，MySQL将为您分配一个“隐藏”主键（类型为整数自动增量），但您不能使用隐藏的PK来加速引号或识别您的行，并且还有许多其他键隐藏PK的慢速问题使得它们非常糟糕。

Hope this helps.

希望这可以帮助。

Links:
How MySQL uses indexes: http://dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html
When to use indexes: http://www.howtoforge.com/when-to-use-indexes-in-mysql-databases
More of that stuff: http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm
Finally lurk here if you want to know more about MySQL: http://planet.mysql.com/

链接：MySQL如何使用索引：http：//dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html何时使用索引：http：//www.howtoforge.com/when-to-use -indexes-in-mysql-databases更多的东西：http：//www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm如果你想要最后潜伏在这里了解有关MySQL的更多信息：http：//planet.mysql.com/

#1