软件测试 | MySQL索引概述-摩杜云开发者社区

所有 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