MySQL数据库的索引

索引的介绍

• 数据库的索引与书的目录十分相似，主要是为了提高从表中检索数据的速度。由于数据存储在数据库表中，所以索引是创建在数据库表对象上，由表中的一个字段或多个字段生成的键组成，这些键储存在数据结构BTree或哈希表中，通过MySQL可以快速有效地查找与键值相关联的字段。
• 根据索引的存储类型，可分为：BTREE索引和HASH索引。MyISAM与InnoDB支持BTREE索引。
• 数据库对象的索引出现，除了可以提高数据库管理系统的查找速度，而且可以保证字段的唯一性，从而实现数据库表的完整性。
• MySQL支持6种索引，分别是：普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。

什么条件下适合创建索引？

1. 经常被查询的字段，即在where子句中出现的字段。
2. 在分组的字段，即在group by子句中出现的字段。
3. 存在依赖关系的子表与父表之间的联合查询，即主键与外键的字段。
4. 设置唯一完整性约束的字段。
5. 行数很多的表记录一般超过300行适合建立索引。

什么条件下不适合创建索引？

1. 在查询中很少被使用的字段。
2. 拥有许多重复值的字段。
3. 行数不多的表记录。

创建索引

创建普通索引

1. 在创建表时创建普通索引
   基本语法：

CREATE TABLE table_name(
     属性名 数据类型，
     ......
     属性名 数据类型,
     INDEX|KEY [索引名](属性名1 [(长度)] [ASC|DESC])
);

INDEX与KEY参数都用来指定字段为索引，ASC用来指定升序排序，DESC用来指定降序排序。MySQL所支持的存储引擎对每个表至少支持16个索引，总索引的长度至少为256个字节。
例：创建t_dept表，并在deptno上创建普通索引，创建好之后我们可以通过SHOW CREATE TABLE t_dept来查看：

2. 在现有表上创建索引
   基本语法：

CREATE INDEX 索引名
       ON 表名(属性名 [(长度)] [ASC|DESC]);

例：在现有t_dept表的dname上创建普通索引：

3. 通过修改表ALTER来创建索引
   基本语法：

ALTER TABLE table_name 
      ADD INDEX|KEY 索引名 (属性名 [(长度)] [ASC|DESC]);

例：在现有t_dept表的loc上创建普通索引：

创建唯一索引

• 唯一索引就是创建索引时，限制索引的值必须是唯一的，通过该类型的索引，可以更快地查询某条记录。创建索引的方式有两种。分为手动索引和自动索引：
• 自动索引：在数据库表里设置完整性约束时，该表就会自动创建索引。
• 手动索引：手动在表上创建索引。
• 当设置表中的某一个字段设置为主键或唯一完整性约束时，系统就会自动创建关联该字段的唯一索引。

1. 创建表时创建唯一索引
   基本语法

CREATE TABLE table_name(
     属性名 数据类型，
     ......
     属性名 数据类型,
     UNIQUE INDEX|KEY [索引名](属性名1 [(长度)] [ASC|DESC])
);

2. 在现有表上创建索引
   基本语法：

CREATE UNIQUE INDEX 索引名
       ON 表名(属性名 [(长度)] [ASC|DESC]);

3. 通过修改表ALTER来创建索引
   基本语法：

ALTER TABLE table_name 
      ADD UNIQUE INDEX|KEY 索引名 (属性名 [(长度)] [ASC|DESC]);

不再举例了基本上与创建普通索引一样。

创建全文索引

• 全文索引主要关联在数据类型为char、varchar和text的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。
• MySQL在3.23版本开始支持全文索引，只能在存储引擎为MyISAM的数据库表上创建全文索引。在默认情况下全文索引的搜索方式不区分大小写，如果全文索引所关联的字段为二进制数据类型，则以区分大小写的搜索方式执行。

1. 创建表时创建唯一索引
   基本语法

CREATE TABLE table_name(
     属性名 数据类型，
     ......
     属性名 数据类型,
     FULLTEXT INDEX|KEY [索引名](属性名1 [(长度)] [ASC|DESC])
)ENGINE=MyISAM;

2. 在现有表上创建索引
   基本语法：

CREATE FULLTEXT INDEX 索引名
       ON 表名(属性名 [(长度)] [ASC|DESC]);

例：为t_dept表中的loc创建全文索引：

3. 通过修改表ALTER来创建索引
   基本语法：

ALTER TABLE table_name 
      ADD FULLTEXT INDEX|KEY 索引名 (属性名 [(长度)] [ASC|DESC]);

创建多列索引

• 通俗的来说，多列索引基本上是和普通索引一样，但是多列索引关联的不是一个字段，而是多个字段。虽然可以通过关联的字段进行查询，但是只有查询条件中使用了关联字段中的第一字段，多列索引才会被使用。

1. 创建表时创建唯一索引
   基本语法

CREATE TABLE table_name(
     属性名 数据类型，
     ......
     属性名 数据类型,
     INDEX|KEY [索引名](属性名1 [(长度)] [ASC|DESC],
                       ......
                       属性名n [(长度)] [ASC|DESC])
);

2. . 在现有表上创建索引
   基本语法：

CREATE INDEX 索引名
       ON 表名(属性名1 [(长度)] [ASC|DESC]
              ......
              属性名n [(长度)] [ASC|DESC])
);

3. 通过修改表ALTER来创建索引
   基本语法：

ALTER TABLE table_name 
      ADD INDEX|KEY 索引名 (属性名1 [(长度)] [ASC|DESC]
                           ......
                           属性名n [(长度)] [ASC|DESC])
);

例：为t_dept表的loc与dname创建多列索引

删除索引

基本语法：

DROP INDEX index_name
     ON table_name;

例：删除表中index_dname_loc索引：

DROP INDEX index_dname_loc
           ON t_dept;