我应该在什么时候使用主键或索引?

When should I use a primary key or an index?

我应该在什么时候使用主键或索引?

What are their differences and which is the best?

他们的区别是什么，哪一个是最好的?

5 个解决方案

#1

Basically, a primary key is (at the implementation level) a special kind of index. Specifically:

基本上，主键(在实现级别)是一种特殊的索引。具体地说:

• A table can have only one primary key, and with very few exceptions, every table should have one.
• 一个表只能有一个主键，除了很少的例外，每个表都应该有一个主键。
• A primary key is implicitly UNIQUE - you cannot have more than one row with the same primary key, since its purpose is to uniquely identify rows.
• 主键是隐式惟一的——您不可能有超过一行具有相同的主键，因为它的目的是惟一地标识行。
• A primary key can never be NULL, so the row(s) it consists of must be NOT NULL
• 主键不能为空，因此它包含的行必须不是空的

A table can have multiple indexes, and indexes are not necessarily UNIQUE. Indexes exist for two reasons:

一个表可以有多个索引，而且索引不一定是唯一的。索引的存在有两个原因:

• To enforce a uniquness constraint (these can be created implicitly when you declare a column UNIQUE)
• 要执行无统一约束(可以在声明列为惟一时隐式地创建这些约束)
• To improve performance. Comparisons for equality or "greater/smaller than" in WHERE clauses, as well as JOINs, are much faster on columns that have an index. But note that each index decreases update/insert/delete performance, so you should only have them where they're actually needed.
• 以提高性能。在包含索引的列中，对于等号或“大于/小于”的比较以及连接的比较要快得多。但是请注意，每个索引都会降低更新/插入/删除性能，因此您应该只在真正需要它们的地方使用它们。

#2

Differences

A table can only have one primary key, but several indexes.

一个表只能有一个主键，但是有几个索引。

A primary key is unique, whereas an index does not have to be unique. Therefore, the value of the primary key identifies a record in a table, the value of the index not necessarily.

主键是唯一的，而索引不一定是唯一的。因此，主键的值标识表中的记录，索引的值不一定。

Primary keys usually are automatically indexed - if you create a primary key, no need to create an index on the same column(s).

主键通常被自动索引——如果您创建了主键，则不需要在同一列上创建索引。

When to use what

Each table should have a primary key. Define a primary key that is guaranteed to uniquely identify each record.

每个表应该有一个主键。定义一个主键，确保惟一地标识每个记录。

If there are other columns you often use in joins or in where conditions, an index may speed up your queries. However, indexes have an overhead when creating and deleting records - something to keep in mind if you do huge amounts of inserts and deletes.

如果在连接中或在条件中经常使用其他列，则索引可能会加快查询速度。但是，在创建和删除记录时，索引有一个开销—如果您做了大量的插入和删除，请记住这一点。

Which is best?

None really - each one has its purpose. And it's not that you really can choose the one or the other.

没有一个是真的——每一个都有它的目的。这并不是说你真的可以选择其中一个或另一个。

I recommend to always ask yourself first what the primary key of a table is and to define it.

我建议您首先问问自己表的主键是什么，然后定义它。

Add indexes by your personal experience, or if performance is declining. Measure the difference, and if you work with SQL Server learn how to read execution plans.

根据个人经验添加索引，或者如果性能下降。测量差异，如果您使用SQL Server学习如何读取执行计划。

#3

This might help Back to the Basics: Difference between Primary Key and Unique Index

这可能有助于我们回到基本的问题:主键和惟一索引之间的区别

The differences between the two are:

两者的区别是:

1. Column(s) that make the Primary Key of a table cannot be NULL since by definition, the Primary Key cannot be NULL since it helps uniquely identify the record in the table. The column(s) that make up the unique index can be nullable. A note worth mentioning over here is that different RDBMS treat this differently –> while SQL Server and DB2 do not allow more than one NULL value in a unique index column, Oracle allows multiple NULL values. That is one of the things to look out for when designing/developing/porting applications across RDBMS.
2. 使表的主键不能为空的列(s)，因为根据定义，主键不能为空，因为它有助于惟一地标识表中的记录。组成唯一索引的列可以为空。这里值得一提的是，不同的RDBMS处理这种不同的方法——当SQL Server和DB2在唯一的索引列中不允许超过一个NULL值时，Oracle允许多个空值。这是在设计/开发/移植RDBMS时需要注意的事情之一。
3. There can be only one Primary Key defined on the table where as you can have many unique indexes defined on the table (if needed).
4. 可以在表上定义一个主键，因为您可以在表上定义许多惟一的索引(如果需要)。
5. Also, in the case of SQL Server, if you go with the default options then a Primary Key is created as a clustered index while the unique index (constraint) is created as a non-clustered index. This is just the default behavior though and can be changed at creation time, if needed.
6. 同样，对于SQL Server，如果使用默认选项，那么主键将被创建为集群索引，而惟一索引(约束)将被创建为非集群索引。这只是默认行为，如果需要，可以在创建时更改。

#4

Keys and indexes are quite different concepts that achieve different things. A key is a logical constraint which requires tuples to be unique. An index is a performance optimisation feature of a database and is therefore a physical rather than a logical feature of the database.

键和索引是实现不同事物的完全不同的概念。一个键是一个逻辑约束，它要求元组是唯一的。索引是数据库的性能优化特性，因此是物理而不是数据库的逻辑特性。

The distinction between the two is sometimes blurred because often a similar or identical syntax is used for specifying constraints and indexes. Many DBMSs will create an index by default when key constraints are created. The potential for confusion between key and index is unfortunate because separating logical and physical concerns is a highly important aspect of data management.

两者之间的区别有时是模糊的，因为通常使用相似或相同的语法来指定约束和索引。在创建关键约束时，许多dbms将在默认情况下创建索引。键和索引之间可能出现混淆是不幸的，因为分离逻辑和物理关注点是数据管理的一个非常重要的方面。

As regards "primary" keys. They are not a "special" type of key. A primary key is just any one candidate key of a table. There are at least two ways to create candidate keys in most SQL DBMSs and that is either using the PRIMARY KEY constraint or using a UNIQUE constraint on NOT NULL columns. It is a very widely observed convention that every SQL table has a PRIMARY KEY constraint on it. Using a PRIMARY KEY constraint is conventional wisdom and a perfectly reasonable thing to do but it generally makes no practical or logical difference because most DBMSs treat all keys as equal. Certainly every table ought to enforce at least one candidate key but whether those key(s) are enforced by PRIMARY KEY or UNIQUE constraints doesn't usually matter. In principle it is candidate keys that are important, not "primary" keys.

至于“主”键。它们不是“特殊”类型的键。主键是表的任意一个候选键。在大多数SQL DBMSs中，至少有两种创建候选键的方法，即使用主键约束或在非空列上使用唯一约束。每个SQL表都有一个主键约束，这是一个非常普遍遵守的约定。使用主键约束是一种传统的智慧和完全合理的做法，但它通常不会产生实际的或逻辑上的差异，因为大多数dbms都将所有键视为平等的。当然，每个表都应该至少执行一个候选键，但是这些键是由主键执行的还是惟一的约束通常并不重要。原则上，重要的是候选键，而不是“主”键。

#5

The primary key is by definition unique: it identifies each individual row. You always want a primary key on your table, since it's the only way to identify rows.

根据定义，主键是唯一的:它标识每一行。您总是需要表上的主键，因为这是标识行的惟一方法。

An index is basically a dictionary for a field or set of fields. When you ask the database to find the record where some field is equal to some specific value, it can look in the dictionary (index) to find the right rows. This is very fast, because just like a dictionary, the entries are sorted in the index allowing for a binary search. Without the index, the database has to read each row in the table and check the value.

索引基本上是一个字段或一组字段的字典。当您要求数据库查找某个字段等于某个特定值的记录时，它可以在字典(索引)中查找以找到正确的行。这非常快，因为就像字典一样，条目在索引中排序，允许进行二进制搜索。如果没有索引，数据库必须读取表中的每一行并检查其值。

You generally want to add an index to each column you need to filter on. If you search on a specific combination of columns, you can create a single index containing all of those columns. If you do so, the same index can be used to search for any prefix of the list of columns in your index. Put simply (if a bit inaccurately), the dictionary holds entries consisting of the concatenation of the values used in the columns, in the specified order, so the database can look for entries which start with a specific value and still use efficient binary search for this.

通常，您需要为需要过滤的每个列添加一个索引。如果搜索特定的列组合，可以创建一个包含所有这些列的索引。如果这样做，可以使用相同的索引搜索索引中的列列表的任何前缀。简单地说(如果有点不准确的话)，字典以指定的顺序保存由列中使用的值连接组成的条目，因此数据库可以查找以特定值开头的条目，并且仍然使用高效的二进制搜索。

For example, if you have an index on the columns (A, B, C), this index can be used even if you only filter on A, because that is the first column in the index. Similarly, it can be used if you filter on both A and B. It cannot, however, be used if you only filter on B or C, because they are not a prefix in the list of columns - you need another index to accomodate that.

例如，如果列上有一个索引(A、B、C)，即使只过滤A，也可以使用这个索引，因为这是索引的第一列。同样，如果您同时对A和B进行过滤，也可以使用它。但是，如果您只对B或C进行过滤，则不能使用它，因为它们不是列列表中的前缀——您需要另一个索引来容纳它。

A primary key also serves as an index, so you don't need to add an index convering the same columns as your primary key.

主键也可以作为索引，所以不需要添加一个索引，该索引与主键使用相同的列。