SQL 什么时候应该使用主键或索引？

When should I use primary key or index?

提问by Sein Kraft

When should I use a primary key or an index?

什么时候应该使用主键或索引？

What are their differences and which is the best?

它们有什么区别，哪个最好？

采纳答案by Michael Borgwardt

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

• 一张表只能有一个主键，除了极少数例外，每个表都应该有一个。
• 主键是隐式的UNIQUE- 不能有多个具有相同主键的行，因为它的目的是唯一标识行。
• 主键永远不能是NULL，因此它所包含的行必须是 NOT NULL

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

一个表可以有多个索引，索引不一定是UNIQUE。存在索引有两个原因：

• 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.

• 强制唯一性约束（可以在声明列 UNIQUE 时隐式创建这些约束）
• 以提高性能。在 WHERE 子句以及 JOIN 中比较相等或“大于/小于”在具有索引的列上要快得多。但请注意，每个索引都会降低更新/插入/删除性能，因此您应该只在实际需要它们的地方使用它们。

回答by marapet

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.

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

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 performanceis declining. Measurethe difference, and if you work with SQL Server learn how to read execution plans.

根据您的个人经验或在性能下降时添加索引。衡量差异，如果您使用 SQL Server，请学习如何阅读执行计划。

回答by alejandrobog

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. 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).
3. 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.

两者的区别在于：

1. 构成表主键的列不能为 NULL，因为根据定义，主键不能为 NULL，因为它有助于唯一标识表中的记录。构成唯一索引的列可以为空。这里值得一提的一点是，不同的 RDBMS 对此有不同的处理方式 –> SQL Server 和 DB2 不允许在唯一索引列中存在多个 NULL 值，而 Oracle 允许多个 NULL 值。这是跨 RDBMS 设计/开发/移植应用程序时需要注意的事情之一。
2. 表上只能定义一个主键，因为您可以在表上定义许多唯一索引（如果需要）。
3. 此外，在 SQL Server 的情况下，如果您使用默认选项，那么主键将创建为聚集索引，而唯一索引（约束）将创建为非聚集索引。这只是默认行为，如果需要，可以在创建时更改。

回答by Michael Madsen

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.

主键也可用作索引，因此您无需添加与主键转换相同列的索引。

回答by nvogel

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 DBMS 中，至少有两种方法可以创建候选键，即使用 PRIMARY KEY 约束或对 NOT NULL 列使用 UNIQUE 约束。每个 SQL 表都有一个 PRIMARY KEY 约束，这是一个被广泛观察到的约定。使用 PRIMARY KEY 约束是传统智慧和完全合理的做法，但它通常没有实际或逻辑上的区别，因为大多数 DBMS 将所有键视为平等。当然，每个表都应该至少强制执行一个候选键，但是这些键是由 PRIMARY KEY 还是 UNIQUE 约束强制执行通常并不重要。原则上，重要的是候选键，而不是“主”键。

回答by Rick James

Every table should have a PRIMARY KEY.

每张桌子都应该有一个PRIMARY KEY.

Many types of queries are sped up by the judicious choice of an INDEX. It maybe that the best index is the primary key. My point is that the queryis the main factor in whether to use the PK for its index.

明智地选择 INDEX 可以加快许多类型的查询。这可能是最好的指标是主键。我的观点是，查询是是否使用 PK 作为其索引的主要因素。