深入MySQL数据库的索引_MySQL

bitsCN.com

索引是加速表内容访问的主要手段，特别对涉及多个表的连接的查询更是如此。这是数据库优化中的一个重要内容，我们要了解为什么需要索引，索引如何工作以及怎样利用它们来优化查询。本节中，我们将介绍索引的特点，以及创建和删除索引的语法。

索引的特点

所有的MySQL列类型能被索引。在相关的列上的使用索引是改进SELECT操作性能的最好方法。

一个表最多可有16个索引。最大索引长度是256个字节，尽管这可以在编译MySQL时被改变。

对于CHAR和VARCHAR列，你可以索引列的前缀。这更快并且比索引整个列需要较少的磁盘空间。对于BLOB和TEXT列，你必须索引列的前缀，你不能索引列的全部。

MySQL能在多个列上创建索引。一个索引可以由最多15个列组成。（在CHAR和VARCHAR列上，你也可以使用列的前缀作为一个索引的部分）。

虽然随着 MySQL 的进一步开发创建索引的约束将会越来越少，但现在还是存在一些约束的。下面的表根据索引的特性，给出了 ISAM 表和 MyISAM 表之间的差别：

表2-1  通道信息特征字对照表

索引的特点 ISAM 表 MyISAM 表
NULL 值
BLOB 和 TEXT 列
每个表中的索引数
每个索引中的列数
最大索引行尺寸
不允许
不能索引
16
16
256 字节 允许
只能索引列的前缀
32
16
500 字节

从此表中可以看到，对于 ISAM 表来说，其索引列必须定义为 NOT NULL，并且不能对 BLOB 和 TEXT 列进行索引。MyISAM 表类型去掉了这些限制，而且减缓了其他的一些限制。两种表类型的索引特性的差异表明，根据所使用的 MySQL 版本的不同，有可能对某些列不能进行索引。例如，如果使用3.23 版以前的版本，则不能对包含 NULL 值的列进行索引。

索引有如下的几种情况：

INDEX索引：通常意义的索引，某些情况下KEY是它的一个同义词。索引的列可以包括重复的值。

UNIQUE索引：唯一索引，保证了列不包含重复的值，对于多列唯一索引，它保证值的组合不重复。

PRIMARY KEY索引：也UNIQUE索引非常类似。事实上，PRIMARY KEY索引仅是一个具有PRIMARY名称的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY。

用Alter Table语句创建与删除索引

为了给现有的表增加一个索引，可使用 ALTER TABLE 或 CREATE INDEX 语句。ALTER TABLE 最常用，因为可用它来创建普通索引、UNIQUE 索引或 PRIMARY KEY 索引，如：

ALTER TABLE tbl_name ADD INDEX index_name  (column_list)
ALTER TABLE tbl_name ADD UNIQUE index_name  (column_list)
ALTER TABLE tbl_name ADD PRIMARY KEY index_name  (column_list)

其中 tbl_name 是要增加索引的表名，而 column_list 指出对哪些列进行索引。一个(col1,col2,...)形式的列表创造一个多列索引。索引值有给定列的值串联而成。如果索引由不止一列组成，各列名之间用逗号分隔。索引名 index_name 是可选的，因此可以不写它，MySQL 将根据第一个索引列赋给它一个名称。ALTER TABLE 允许在单个语句中指定多个表的更改，因此可以在同时创建多个索引。

同样，也可以用ALTER TABLE语句产出列的索引：

ALTER TABLE tbl_name DROP INDEX index_name
ALTER TABLE tbl_name DROP PRIMARY KEY

注意上面第一条语句可以用来删除各种类型的索引，而第三条语句只在删除 PRIMARY KEY 索引时使用；在此情形中，不需要索引名，因为一个表只可能具有一个这样的索引。如果没有明确地创建作为 PRIMARY KEY 的索引，但该表具有一个或多个 UNIQUE 索引，则 MySQL 将删除这些 UNIQUE 索引中的第一个。

如果从表中删除了列，则索引可能会受到影响。如果所删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

例如，对于上面所使用的student为例，你可能想为之创建这样的索引，以加速表的检索速度：

mysql> ALTER TABLE student
-> ADD PRIMARY KEY(id),
-> ADD INDEX mark(english,Chinese,history);

 

这个例子，既包括PRIMARY索引，也包括多列索引。记住，使用PRIMARY索引的列，必须是一个具有NOT NULL属性的列，如果你愿意产看创建的索引的情况，可以使用SHOW INDEX语句：

mysql> SHOW INDEX FROM student;

其结果为：

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

| Table   | Non_unique | Key_name | Seq_in_index | Column_name |

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

| student |          0 | PRIMARY  |            1 | id          |

| student |          1 | mark     |            1 | english     |

| student |          1 | mark     |            2 | chinese     |

| student |          1 | mark     |            3 | history     |

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

由于列数太多，上表并没有包括所有的输出，读者可以试着自己查看。

再使用ALTER TABLE语句删除索引，删除索引需要知道索引的名字，你可以通过SHOW INDEX语句得到：

mysql> ALTER TABLE student DROP PRIMARY KEY,
    -> DROP INDEX mark;

再产看表中的索引，其语句和输出为：

mysql> SHOW INDEX FROM student;
Empty set (0.01 sec)

用CREATEDROP INDEX创建索引

还可以用CREATE INDEX语句来创建索引.CREATE INDEX 是在 MySQL 3.23版中引入的，但如果使用3.23 版以前的版本，可利用 ALTER TABLE 语句创建索引（MySQL 通常在内部将 CREATE INDEX 映射到 ALTER TABLE）。该语句创建索引的语法如下：

CREATE UNIQUE INDEX index_name ON tbl_name (column_list)
CREATE INDEX index_name ON tbl_name (column_list)

tbl_name、index_name 和 column_list 具有与 ALTER TABLE 语句中相同的含义。这里索引名不可选。很明显，CREATE INDEX 可对表增加普通索引或 UNIQUE 索引，不能用 CREATE INDEX 语句创建 PRIMARY KEY 索引。

可利用 DROP INDEX语句来删除索引。类似于 CREATE INDEX 语句，DROP INDEX 通常在内部作为一条 ALTER TABLE 语句处理，并且DROP INDEX是在 MySQL 3.22 中引入的。

删除索引语句的语法如下：

DROP INDEX index_name ON tbl_name

还是上一节的例子，由于CREATE INDEX不能创建PRIMARY索引，所以这里我们值创建一个多列索引：

mysql> CREATE INDEX mark ON student(english,chinese,history);

同样的检查student表，可知：

mysql> SHOW INDEX FROM student;

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

| Table   | Non_unique | Key_name | Seq_in_index | Column_name |

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

| student |          1 | mark     |            1 | english     |

| student |          1 | mark     |            2 | chinese     |

| student |          1 | mark     |            3 | history     |

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

然后使用下面的语句删除索引：

mysql> DROP INDEX mark ON student;

在创建表时指定索引

要想在发布 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 NO bitsCN.com