02-索引概述

所有MySQL列类型都可以被索引，对相关列使用索引是提高SELECT操作性能的最佳途径。根据存储引擎可以定义每个表的最大索引数和最大索引长度，每种存储引擎（如 MyISAM、InnoDB、BDB、MEMORY等）对每个表至少支持16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。

MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引。MySQL目前还不支持函数索引，但是支持前缀索引，即对索引字段的前N个字符创建索引。前缀索引的长度跟存储引擎相关，对于MyISAM存储引擎的表，索引的前缀长度可以达到1000字节长，而对于InnoDB存储引擎的表，索引的前缀长度最长是767字节。请注意前缀的限制应以字节为单位进行测量，而CREATE TABLE语句中的前缀长度解释为字符数。在为使用多字节字符集的列指定前缀长度时一定要加以考虑。

MySQL 中还支持全文本（FULLTEXT）索引，该索引可以用于全文搜索。但是当前最新版本（5.0）中只有MyISAM存储引擎支持FULLTEXT索引，并且只限于CHAR、VARCHAR和TEXT列。索引总是对整个列进行的，不支持局部（前缀）索引。

也可以为空间列类型创建索引，但是只有 MyISAM 存储引擎支持空间类型索引，且索引的字段必须是非空的。

默认情况下，MEMORY存储引擎使用HASH索引，但也支持BTREE索引。

索引在创建表的时候可以同时创建，也可以随时增加新的索引。创建新索引的语法为：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[USING index_type]

ON tbl_name (index_col_name,. .)

index_col_name:

col_name [(length)] [ASC | DESC]

也可以使用ALTER TABLE的语法来增加索引，语法与CREATE INDEX类似，可以查询帮助获得详细的语法，这里不再复述。

例如，要为city表创建10个字节的前缀索引，代码如下：

mysql> create index cityname on city (city(10));

Query OK, 600 rows affected (0.26 sec)

Records: 600 Duplicates: 0 Warnings: 0

如果以city为条件进行查询，可以发现索引cityname被使用：

mysql> explain select * from city where city = 'Fuzhou' \G

1. row

id: 1

select_type: SIMPLE

table: city

type: ref

possible_keys: cityname

key: cityname

key_len: 32

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

索引的删除语法为：

DROP INDEX index_name ON tbl_name

例如，想要删除city表上的索引cityname，可以操作如下：

mysql> drop index cityname on city;

Query OK, 600 rows affected (0.23 sec)

Records: 600 Duplicates: 0 Warnings: 0