深入MySQL数据库的索引

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

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

索引的特点

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

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

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

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

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

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

从此表中可以看到，对于 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) 
 

延伸阅读:
MySQL数据库性能优化系列专题