您需要显式地创建索引,还是在定义主键时隐式创建索引?MyISAM和InnoDB的答案一样吗?


当前回答

主键总是自动索引且惟一。因此,注意不要创建冗余索引。

例如,如果您这样创建了一个表

CREATE TABLE mytable (foo INT NOT NULL PRIMARY KEY, bar INT NOT NULL, baz INT NOT NULL,
  UNIQUE(foo), INDEX(foo)) ENGINE=InnoDB;

因为您想要索引主键并对其强制唯一性约束,所以实际上最终会在foo上创建三个索引!

其他回答

主键总是自动索引且惟一。因此,注意不要创建冗余索引。

例如,如果您这样创建了一个表

CREATE TABLE mytable (foo INT NOT NULL PRIMARY KEY, bar INT NOT NULL, baz INT NOT NULL,
  UNIQUE(foo), INDEX(foo)) ENGINE=InnoDB;

因为您想要索引主键并对其强制唯一性约束,所以实际上最终会在foo上创建三个索引!

索引最好用于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.

我想这就是答案

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)

是的,我们可以把主键列看作是任何其他索引列,只是带有主键的约束。

在大多数情况下,我们既需要主键,也需要索引表中的列/列,因为我们对表的查询可能会基于非主键的列/列来过滤行,在这种情况下,我们通常也会索引那些列/列。

尽管这在2009年被问到,但我认为我应该发布一个关于主键的MySQL文档的实际引用。 http://dev.mysql.com/doc/refman/5.5/en/optimizing-primary-keys.html

表的主键表示列或列集 用在最重要的问题上。它有一个相关的索引, 快速查询性能

MySQL 5.0参考参考:http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

大多数MySQL索引(PRIMARY KEY, UNIQUE, INDEX和FULLTEXT)都是 存储在b树中。例外情况是空间数据类型上的索引 使用r -树,内存表也支持散列索引。