主键是否在 MySQL 中自动索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1071180/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is the primary key automatically indexed in MySQL?
提问by Alex Miller
Do you need to explicitly create an index, or is it implicit when defining the primary key? Is the answer the same for MyISAM and InnoDB?
是否需要显式创建索引，还是在定义主键时隐式创建？MyISAM 和 InnoDB 的答案是否相同？
回答by Emil H
The primary key is always indexed. This is the same for MyISAM and InnoDB, and is generally true for all storage engines that at all supports indices.
主键始终被索引。这对于 MyISAM 和 InnoDB 是相同的，并且对于所有支持索引的存储引擎通常都是如此。
回答by ist_lion
According to http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.htmlit would appear that this is would be implicit
根据http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html看来这是隐含的
回答by fyrye
Even though this was asked in 2009 figured I'd post an actual reference to the MySQL documentation on primary keys. http://dev.mysql.com/doc/refman/5.5/en/optimizing-primary-keys.html
尽管这是在 2009 年被问到的，但我还是会在主键上发布对 MySQL 文档的实际参考。 http://dev.mysql.com/doc/refman/5.5/en/optimizing-primary-keys.html
The primary keyfor a table represents the column or set of columns that you use in your most vital queries. It has an associated index, for fast query performance
表的主键表示您在最重要的查询中使用的一列或一组列。它有一个关联的索引，用于快速查询性能
For MySQL 5.0 reference see: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
有关 MySQL 5.0 参考，请参阅：http: //dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
Most MySQL indexes(PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions are that indexes on spatial data types use R-trees, and that MEMORY tables also support hash indexes.
大多数 MySQL索引（PRIMARY KEY、UNIQUE、INDEX 和 FULLTEXT）都存储在 B 树中。例外情况是空间数据类型的索引使用 R 树，并且 MEMORY 表也支持散列索引。
回答by Patrick Gryciuk
The primary key is implicitly indexed for both MyISAM and InnoDB. You can verify this by using EXPLAIN on a query that makes use of the primary key.
MyISAM 和 InnoDB 的主键都是隐式索引的。您可以通过在使用主键的查询上使用 EXPLAIN 来验证这一点。
回答by Rick
You do not have to explicitly create an index for a primary key... it is done by default.
您不必为主键显式创建索引……默认情况下已完成。
回答by guest
I guess this is the answer
我想这就是答案
mysql> create table test(id int primary key, s varchar(20));
Query OK, 0 rows affected (0.06 sec)
mysql> show indexes from test \G
*************************** 1. row ***************************
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
回答by Masood Ul Hassan
Indexes are best used on columns that are frequently used in where clauses, and in any kind of sorting, such as "order by". You might be working on a more complex database, so it's good to remember a few simple rules.
索引最适合用于 where 子句中经常使用的列，以及任何类型的排序，例如“order by”。您可能正在处理更复杂的数据库，因此最好记住一些简单的规则。
- Indexes slow down inserts and updates, so you want to use them carefully on columns that are FREQUENTLY updated.
Indexes speed up where clauses and order by. Remember to think about HOW your data is going to be used when building your tables. There are a few other things to remember. If your table is very small, i.e., only a few employees, it's worse to use an index than to leave it out and just let it do a table scan.
Indexes really only come in handy with tables that have a lot of rows.
Another thing to remember, that is a con in the situation of our employee's database, is that if the column is a variable length, indexes (as well as most of MySQL) perform much less efficiently.
Don't forget joins too! Indexed join fields speed things up.
- 索引会减慢插入和更新的速度，因此您希望在频繁更新的列上谨慎使用它们。
索引加速 where 子句和 order by。记住在构建表时要考虑如何使用数据。还有一些其他的事情要记住。如果您的表非常小，即只有几个员工，那么使用索引比不使用索引并让它进行表扫描更糟糕。
索引实际上只在有很多行的表中派上用场。
另一件要记住的事情是，在我们员工数据库的情况下，如果列是可变长度的，索引（以及大多数 MySQL）的执行效率会低得多。
也不要忘记加入！索引连接字段加快了速度。
回答by dr_
The primary key is always automatically indexed and unique. So, beware not to create redundant indexes.
主键总是自动索引并且是唯一的。因此，请注意不要创建冗余索引。
For instance, if you created a table as such
例如，如果您创建了一个这样的表
CREATE TABLE mytable (foo INT NOT NULL PRIMARY KEY, bar INT NOT NULL, baz INT NOT NULL,
UNIQUE(foo), INDEX(foo)) ENGINE=InnoDB;
because you want to index the primary key and enforce an uniqueness constraint on it, you'd actually end up creating three indexes on foo!
因为您想索引主键并对其实施唯一性约束，您实际上最终会在foo!
回答by Anshul Sharma
One can think of a primary key column as any other indexed column with the constraints of primary key brings with it.
可以将主键列视为具有主键约束的任何其他索引列。
In most of the use cases we need both primary key, and indexed column/columns in a table, because our queries to table may filter rows based on column/columns which is not primary key, in that case we usually index those column/columns as well.
在大多数用例中，我们需要表中的主键和索引列/列，因为我们对表的查询可能会根据不是主键的列/列过滤行，在这种情况下，我们通常对这些列/列进行索引以及。
