5 建立索引

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」叙述建立主索引键：

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

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