MySQL(7)：索引

索引简介

优点

• 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的查询速度，这也是创建索引的最主要的原因。
• 在实现数据的参考完整性方面，可以加快表和表之间的连接。
• 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

缺点

• 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占用硬盘空间，除了数据表占用数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这就降低了数据的维护速度。

分类

• 普通索引和唯一索引：普通索引允许在定义索引的列中插入重复值和空值，唯一索引的值必须唯一，但允许有空值。
• 单列索引和组合索引：使用组合索引时遵循最左前缀集合（即只有在查询条件中使用了这些字段的左边字段时，索引才会被使用）
• 全文索引：全文索引类型为FULLTEXT，全文索引可以在CHAR、有ARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。
• 空间索引：略。

创建索引

CREATE  TABLE table_name[col_name data_type]

[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length])[ASC | DESC]

INDEX与KEY为同义词，两者作用相同，用来指定创建索引；col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；index_name指定索引的名称，为可选参数，如果不指定，MySQL默认col_name为索引值；length为可选参数，表示索引的长度，只有字符串类型额字段才能指定索引长度；ASC或DESC指定升序或者降序的索引值存储。

使用EXPLAIN语句查看索引是否正在使用：

EXPLAIN SELECT语句 

在已经存在的表上创建索引

使用ALTER TABLE语句创建索引

ALTER TABLE table_name ADD[UNIQUE|FULLTEXT|SPTIAL]  [INDEX|KEY]

[index_name] (col_name(length)...) [ASC|DESC]

使用SHOW INDEX语句查看指定表中创建的索引：

SHOW INDEX FROM table_name [\G]; 

使用CREATE INDEX创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

ON table_name (col_name[length]...)[ASC | DESC] 

 

删除索引

使用ALTER TABLE 删除索引

ALTER TABLE table_name DROP INDEX index_name; 

注：添加AUTO_INCREMENT 约束字段的唯一索引不能被删除。

使用DROP INDEX语句删除索引

DROP INDEX index_name ON table_name;

注：删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。