mysql索引类型，索引创建，索引使用注意事项

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可以提高数据库中特定数据的查询速度。所有mysql列的类型都可以使用索引，例如：数据库中有两万条记录，现在要执行这样一个查询：select * from table t1 where num=20000。如果没有索引，将会遍历整个表，知道num=20000，这一行数据被找到。如果在num列上创建索引，mysql不需要任何扫描，直接在索引里面找到20000，就可以查到数据，可见索引的查询效率。

索引是在存储引擎中实现的，所以不同的存储引擎，索引不一定完全相同。并且每一种存储引擎也不一定支持所有索引，mysql中索引的存储类型有两种：BTREE和HASH两种。InnoDB和MyIsam存储引擎只支持btree；memory和heap存储引擎支持hash和btree索引。

索引的优点：

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

5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引那么需要的空间就会更大。 

3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

mysql索引的分类：

普通索引和唯一索引

这是最基本的索引，它没有任何限制，允许定义索引的列上存在空值和重复值。

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。

单列索引和组合索引

单列索引即一个索引包含单个列，一个表可以有多个单列索引。

组合索引指在多个字段上创建的索引，只有查询条件使用了这些字段的左边字段，索引才会被使用，就是最左边的字段。

全文索引

在mysql5.6版本以前FULLTEXT索引仅可用于 MyISAM 表，在5.6之后innodb引擎也支持FULLTEXT索引。他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建。

空间索引

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

索引创建的有两种，第一种是创建表的时候创建索引：

CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],

[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

1、unique|fulltext|spatial为可选参数，分别表示唯一索引、全文索引和空间索引；
2、index和key为同义词，两者作用相同，用来指定创建索引
3、col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；
4、index_name指定索引的名称，为可选参数，如果不指定，MYSQL默认col_name为索引值；
5、length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；

6、asc或desc指定升序或降序的索引值存储

 

例如创建普通索引：创建t2表并且在username字段上创建索引。

create table t2(

id  int not null,
username varchar(255)not null,
age int(30)not null,
index(username)

)

EXPLAIN select * from t2 where username='小明'

rows=1表示只扫描了一行数据，

key_len表示索引按字节计算出的长度

key 表示mysql实际选用的索引

创建唯一索引的列子：

 

create table t3(

id  int not null,
username varchar(255)not null,
age int(30)not null,
unique index uniqueidx(username)

)

创建单列索引的列子：

 

create table t4(

id  int not null,
username varchar(255)not null,
age int(30)not null,
index singleidx(username（20）)

)

创建组合索引的例子：

 

create table t5(

id  int not null,
username varchar(255)not null,
age int(30)not null,
index manyidx(id,username,age（100）)

);

注意：组合索引可起到几个索引的作用，但需要查询条件有最左边的列的字段，这样的列集称为最左前缀。例如，id,username,age三个字段，查询时候，条件必须(id,username,age),或者（id,age）这样组合，没有id字段。索引会失效。

创建全文索引的列子：

 

create table t6(

id  int not null,
username varchar(255)not null,
age int(30)not null,
fulltext index fullidx(username）)

) ENGINE=MyISAM;

注意：全文索引的字段只能是，varchar,text,char。

索引创建的有两种，第二种是在已经存在的表上创建索引：

创建语句是：alter table tablename add[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

再已知t1表上给age字段添加唯一索引：

alter table t1 add  unique index uniqueidex(age)

以此类推，添加组合索引，单列索引，空间索引，全文索引。

索引的删除：

1.使用alter table 删除索引的基本语法：alter table tablename drop index indexname；

例如：删除t1表中名为uniqueindex的索引：

alter table t1 drop index uniqueindex;

2.使用drop index 语句删除索引的基本语法：drop index indexname on tablename；

例如：删除t2表上名为manyindex 的组合索引：

drop index manyindex on t2;