深入MySQL数据库的索引（3）

在创建表时指定索引

要想在发布 CREATE TABLE 语句时为新表创建索引，所使用的语法类似于 ALTER TABLE 语句的语法，但是应该在您定义表列的语句部分指定索引创建子句，如下所示：

CREATE TABLE tbl_name

(

…

INDEX index_name (column_list),

KEY index_name (column_list),

UNIQUE index_name (column_list),

PRIMARY KEY index_name (column_list),

…

)

与ALTER TABLE 一样，索引名对于 INDEX 和 UNIQUE 都是可选的，如果未给出，MySQL 将为其选一个。另外，这里KEY时INDEX的一个别名，具有相同的意义。

有一种特殊情形：可在列定义之后增加 PRIMARY KEY 创建一个单列的PRIMARY KEY 索引，如下所示：

CREATE TABLE tbl_name

(

i INT NOT NULL PRIMARY KEY

)

该语句等价于以下的语句:

CREATE TABLE tbl_name

(

i INT NOT NULL,

PRIMARY KEY (i)

)

前面所有表创建样例都对索引列指定了 NOT NULL。如果是 ISAM 表，这是必须的，因为不能对可能包含 NULL 值的列进行索引。如果是 MyISAM 表，索引列可以为 NULL，只要该索引不是 PRIMARY KEY 索引即可。

在CREATE TBALE语句中可以某个串列的前缀进行索引(列值的最左边 n 个字符)。

如果对某个串列的前缀进行索引，应用 column_list 说明符表示该列的语法为 col_name(n) 而不用col_name。例如，下面第一条语句创建了一个具有两个 CHAR 列的表和一个由这两列组成的索引。第二条语句类似，但只对每个列的前缀进行索引：

CREATE TABLE tbl_name

(

name CHAR(30),

address CHAR(60),

INDEX (name,address)

)

CREATE TABLE tbl_name

(

name CHAR(30),

address CHAR(60),

INDEX (name(10),address(20))

)

你可以检查所创建表的索引：

mysql> SHOW INDEX FROM tbl_name;

+----------+------------+----------+--------------+-------------+-

| Table | Non_unique | Key_name | Seq_in_index | Column_name |

+----------+------------+----------+--------------+-------------+-

| tbl_name | 1 | name | 1 | name |

| tbl_name | 1 | name | 2 | address |

+----------+------------+----------+--------------+-------------+-

在某些情况下，可能会发现必须对列的前缀进行索引。例如，索引行的长度有一个最大上限，因此，如果索引列的长度超过了这个上限，那么就可能需要利用前缀进行索引。在 MyISAM 表索引中，对 BLOB 或 TEXT 列也需要前缀索引。

对一个列的前缀进行索引限制了以后对该列的更改;不能在不删除该索引并使用较短前缀的情况下，将该列缩短为一个长度小于索引所用前缀的长度的列。

总结

本节对索引的类型，已经如何创建索引做了介绍，其中涉及三个比较重要的SQL语句——ALTER TABLE、CREATE/DROP INDEX和CREATE TABLE，注意它们的用法。

索引最重要的功能是，通过使用索引加速表的检索，有关这方面的知识，将在第十章数据库优化中介绍。

思考题

1、建立一个如下所述的表：

data：FLOAT列，使用随机函数填充数据

birth：DATETIME列，填充当前时间。

然后，请录入几条数据。最后计算data列的平均值、总和、极值，并且按照data列降序排序检索值。

2、分别使用标准SQL模式和扩展正规表达式模式匹配，匹配上面创建的表，假设你创建表的当前日期为2001-01-01，用模式匹配检索出birth列包含该日期的值。(实际上，上面的表中记录都是同一日期录入的，因此实际将返回全部记录。)

3、为前几章使用的数据表创建索引：

student：为id段创建一个PRIMARY索引，为english、chinese和history创建一个多列索引。

pet：为name和owner段创建一个多类索引。

4、删除为pet表创建的索引。