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 子句中使用的列上创建索引.

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 的表，并且您想要搜索姓氏为Smith"的所有用户.如果没有索引，数据库将不得不遍历表的所有记录:这很慢，因为数据库中的记录越多，查找结果所需的工作就越多.另一方面，索引将帮助数据库快速跳转到保存史密斯"记录的相关页面.这与我们人类通过电话簿目录查找姓氏的方式非常相似:我们不会从一个封面到另一个封面开始搜索目录，只要我们按某种顺序插入信息，我们就可以用于快速跳转到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屋！