Question

MySQL 提供许多不同的方式让你建立需要的索引。通常在规划一个数据库的时候，会把表格所需要的索引一并规划好，在这样的情况下，你可以把建立索引的定义，加在“CREATE TABLE”叙述中，建立表格的时候就一起把索引建立好；不过也有可能在使用表格一阵子以后，才发觉有建立索引的需求，在这样的情况下，你可以使用“ALTER TABLE”或“CREATE INDEX”建立需要的索引。

5.1 在建立表格的时候建立索引

在建立表格的叙述中，你会定义出许多表格所需要的字段，在字段的定义中，除了名称、型态与属性，还可以加入“唯一索引”与“主索引键”的定义：

以下列这个建立储存联络簿的表格来说，你可以使用这样的语法在“id”字段后面加入“PRIMARY KEY”，指定“id”字段为主索引键，这表示“id”字段的值不可以重复，而且不可以储存“NULL”值；另外在“email”字段加入“UNIQUE KEY”，指定“email”字段为唯一索引，这表示“email”字段的值不可以重复：

下列是另外一种在“CREATE TABLE”叙述中建立索引的语法：

同样以建立储存联络簿的表格来说，下列两种建立索引语法的效果是一样的：

如果你要建立一般索引（可以重复的索引)，或是要建立包含多个字段的索引时，就一定要把建立索引的定义加在所有字段定义后面：

在建立索引的时候，你可以指定某一个字段为建立索引的字段，不过有时候你只想要为一个字串型态字段的部份资料建立索引，或是指定建立的索引资料，是要依照由小到大，还是由大到小排列。有这样的需求时，你可以依照下列的语法来指定：

以建立联络簿的表格来说，为地址资料“address”字段建立索引的时候，如果你希望建立地址前五个字符的索引资料，而且依照由大到小的顺序。下列的叙述就可以建立这样的索引：

注：只有“CHAR”、“VARCHAR”、“BINARY”与“VARBINARY”型态的字段可以指定制作索引的长度。“ASC”或“DESC”可以使用在任何型态的字段。

如果一个表格使用的储存引擎是“MEMORY”的话，建立索引的时候还可以额外指定索引使用的“算法、algorithm”。使用其它储存引擎的表格，MySQL 会忽略这个设定。索引使用的算法有“BTREE”与“HASH”两种，你可以使用下列的语法来指定索引使用的算法：

默认的“HASH”算法适合用在主索引键和唯一索引，这种算法在搜寻不能重复的资料时，效率会比较好；而“BTREE”算法适合用在可以允许重复资料的一般索引，在搜寻上会比“HASH”有更好的效率。

注：“FULLTEXT”索引只能用在“CHAR”、“VARCHAR”与“TEXT”型态的字段，而且表格使用的储存引擎必须是“MyISAM”。“SPATIAL”索引是“SPATIAL”型态字段专用的，而且表格使用的储存引擎必须是“MyISAM”。这两种索引不会在这里讨论。

5.2 在修改表格的时候建立索引

如果你想要为一个已经存在的表格建立索引的话，你可以在修改表格“ALTER TABLE”中建立索引：

以下列的范例来说，在建立联络簿表格时没有建立索引，你可以使用“ALTER TABLE”叙述建立需要的索引，不过一个“ALTER TABLE”叙述只能建立一个索引：

5.3 使用“CREATE INDEX”建立索引

需要为一个已经存在的表格建立索引，除了使用“ALTER TABLE”叙述建立索引外，还可以使用“CREATE INDEX”叙述建立唯一索引与一般索引：

使用“CREATE INDEX”叙述只能建立唯一索引与一般索引，你还是要使用“ALTER TABLE”叙述建立主索引键：

为一个已经存在的表格建立索引时，要特别注意主索引键与唯一索引这两种索引。如果这个表格没有任何纪录资料的话，那就不会有问题；可是如果表格中已经有纪录了，而且你想要建立一个主索引键时，有可能会发生下列的错误：

为一个已经存在、而且已经有纪录的表格建立唯一索引时，也有可能会发生下列的错误：