MySQL中密钥，主键，唯一密钥和索引的区别 [英] Difference between Key, Primary Key, Unique Key and Index in MySQL

问题描述

我应该何时使用 KEY ， PRIMARY KEY ， UNIQUE KEY 和 INDEX ？

When should I use KEY, PRIMARY KEY, UNIQUE KEY and INDEX?

推荐答案

KEY 和 INDEX 是MySQL中的同义词。他们的意思是一样的。在数据库中，您可以使用索引来提高数据检索的速度。通常在 JOIN ， WHERE 和 ORDER BY 中使用的列上创建索引code>条款。

KEY and INDEX are synonyms in MySQL. They mean the same thing. In databases you would use indexes to improve the speed of data retrieval. An index is typically created on columns used in JOIN, WHERE, and ORDER BY clauses.

想象一下，您有一个名为 users 的表，并且您想要搜索所有具有姓氏的用户史密斯。如果没有索引，数据库必须遍历表的所有记录：这很慢，因为数据库中的记录越多，查找结果所需的工作就越多。另一方面，索引将帮助数据库快速跳到持有史密斯记录的相关页面。这非常类似于我们人类通过电话簿目录查找姓名的人：我们不会从头到尾搜索目录，只要我们按照某种顺序插入信息，我们就可以用于快速跳到'S'页面。

Imagine you have a table called users and you want to search for all the users which have the last name 'Smith'. Without an index, the database would have to go through all the records of the table: this is slow, because the more records you have in your database, the more work it has to do to find the result. On the other hand, an index will help the database skip quickly to the relevant pages where the 'Smith' records are held. This is very similar to how we, humans, go through a phone book directory to find someone by the last name: We don't start searching through the directory from cover to cover, as long we inserted the information in some order that we can use to skip quickly to the 'S' pages.

主键和唯一键类似。主键是可以唯一标识行的列或列的组合。这是唯一密钥的特例。一个表最多只能有一个主键，但不止一个唯一键。在列上指定唯一键时，表中没有两个不同的行可以具有相同的值。

Primary keys and unique keys are similar. A primary key is a column, or a combination of columns, that can uniquely identify a row. It is a special case of unique key. A table can have at most one primary key, but more than one unique key. When you specify a unique key on a column, no two distinct rows in a table can have the same value.

另请注意，定义为主键或唯一键的列是在MySQL中自动编入索引。

Also note that columns defined as primary keys or unique keys are automatically indexed in MySQL.

这篇关于MySQL中密钥，主键，唯一密钥和索引的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！