mysql 索引详解

索引是一个单独存储在磁盘上的数据库结构，使用索引可以快速找出在某个或多个列中有一特定值的行，提高查询速度。MySQL中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相关；MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

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

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

通过show index from TableName 查看该表索引

索引分类

1. 普通索引和唯一索引

普通索引允许在定义索引的列中插入重复值和空值。
唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
但主键索引是一种特殊的唯一索引，不允许有空值。

create table table1
(
id int not null,
name varchar(255) not null,
unique index one(id)
);

2. 单列索引和组合索引

单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。

单列：

组合：

3. 全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找（不支持局部查找），允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。

4．空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

操作使用索引

create table TableName [colName DataType]
[ unique | fulltext | spatial ] [index | key] [indexName] (colName [length]) [ASC | DESC];

-- UNIQUE 唯一索引, FULLTEXT, 全文索引, SPATIAL 空间索引
-- index与key 用来指定创建索引
-- indexName 指定索引的名称
-- colName 为需要创建索引的字段列

1. 在已有表中添加索引

alter table TableName add 
[ unique | fulltext | spatial ] [index | key] [indexName] (colName [length]) [ASC | DESC];

2. 删除索引

alter table TableName drop index indexName;