六. 索引的设计和使用

1. 概述

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

MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引。MySQL目前还不支持函数索引，但是支持前缀索引，即对索引字段的前N个字符创建索引。

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

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

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

创建索引的语法：

-- 命令格式
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON table_name (index_col_name,...);

index_col_name:col_name[(length)][ASC|DESC]

-- 例如，要为student表创建10个字节的前缀索引，代码如下：
CREATE INDEX student_name_index ON student(stu_name(10));

删除索引的语法：

DROP INDEX index_name ON table_name;

2. 设计索引的原则

1. 最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列。
2. 使用唯一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。例如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“M”和“F”，则对此进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行。
3. 使用短索引。如果对字符串进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，有一个CHAR(200)列，如果在前10个或20个字符内，多数值是唯一的，那么就不需要对整个列进行索引。
4. 利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
5. 不要过度索引。
6. 对于InnoDB存储引擎的表，记录默认按照一定的顺序保存。如果有明确的定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果都没有，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的，索引当表中同时几个列都是唯一的，都可以作为主键时，要选择最常作为访问条件的列作为主键。，InnoDB表的普通索引都会保存主键的键值，索引主键要尽可能选择较短的数据类型。