MySQL学习之索引

一、 索引简介

索引是对数据库表中一列/多列的值进行排序的一种结构，使用索引可提高数据库中特定的查询速度。

1、索引的含义和特点

1）索引是一个单独、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行。

2）索引是在存储引擎中实现的，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。MySQL中索引存储类型有：BTREE和HASH,具体和表的存储索引有关，MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAP存储索引可以支持HASH和BTREE索引。

3）索引的优点：

a. 创建唯一索引，可以保证数据库中的每一行数据的唯一性

b. 大大加快了查询速度

c. 实现数据的参考完整性方面，可以加速表和表之间的连接

d. 使用分组和排序子句进行数据查询，显著减少查询时间

4）缺点：

a.创建/维护索引耗费时间，随着数据量的增加耗费时间也增加

b.索引需要占用磁盘空间

c.当对表中的数据进行增改删操作时也需要动态维护索引

 

2、索引的分类

1）普通索引：允许定义索引的列中插入空值+重复值

2）唯一索引：列值唯一，允许为空值。主键索引是一中特殊的唯一索引，不允许为空。

3）单列索引：索引中只有单个列，一个表中可以有多个单列索引

4）组合索引：、列值组合必须唯一，使用组合索引遵循最左前缀集合

5）全文索引：在定义索引的列上支持值得全文查找，允许为空值+重复值。可以用在char/varchar/text类型的列上。MySQL中只有MyISAM支持全文索引

6）空间索引：对空间数据类型字段创建的索引，MySQL空间数据类型有四种：geomety,point,linesting,polygon.使用spatial关键字进行扩展。创建空间索引的列必须为not null,只能在存储引擎为MyISAM的表上建立。

 

3、索引的设计原则

1）索引并不是越多越好

2）避免对经常更新的表进行过多的索引，并且索引中的列尽可能的少。

3）数据量小的表尽量不要使用索引

4）在条件表达式中经常用的不同列值较多的列上建立索引

5）当唯一性是某种数据本身的特征时指定唯一索引，保证数据的完整性。

6）在频繁进行排序或分组的列上建立索引，如果有多个建立组合索引。

 

 

二、创建索引

1、创建表时创建索引

Create table table_name [col_name data_type]

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

 

[unique|fulltext|spatial]——分别表示三种索引

[index|key]——用于指定创建索引，两者作用一致

 [index_name]——指定索引名字

(col_name[length]) ——col_name为索引值，length为可选参数，表示索引的长度只有字符串类型的字段才能指定索引字段。

实例：

Create table book(

bookid int not null,

bookname varchar(255) not null,

authors varchar(255) not null,

info varchar(255) null,

comment varchar(255) null,

year_publication year not null,

index(year_publication)

);

用explain语句来查看索引是否正在使用

Explain select * from book where year_publication=1990 \G

 

1）创建唯一索引：索引值唯一，允许为空，若为组合索引列值必须唯一。

Create table t1(

id int not null,

Name char(30) not null,

Unique index uniqIdx(id)

);

2)创建单列索引

Create table t2(

id int not null,

name char(50) null,

index SingleIdx(name(20))

);

 

3)创建组合索引

Create table t3(

id int not null,

name char(30) not null,

age int not null,

info varchar(255),

Index MultiIdx(id,name,age)

);

Show create table t3 \G

Explain select * from t3 where id=1 and name=’Joe’ \G

 

4)创建全文索引

全文索引用于全文搜索，只有MyISAM存储支持fulltext索引，且只为char/varchar/text三种类型创建索引，索引总是对整个列进行，不支持前缀索引。

Create table t4(

id int not null,

name char(30) not null,

age int not null,

info varchar(255),

Fulltext index fullTxtIdx(info)

)ENGINE=MyISAM;

5)创建空间索引

空间索引必须在MyISAM类型的表中创建，且空间类型字段必须为非空

Create table t5(

g geometry not null,

Spatial index spatIdx(g)

)engine=MyISAM;

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

Alter table table_name add

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

 

Alter table book add index BkName(bookname(30));

Alter table book add unique index uniqueIdx(bookId);

Alter table book add index BkcmtIdx(comment(50));

Alter table book add index BkAuandInfoIdx(authors(30),info(50));

Create table t6(

id int not null,

info char(255)

)engine=MyISAM;

Alter table t6 add index infoFTIdx(info);

3、使用create index 创建索引

Create [unique|fulltext|spatial] index index_name

On table_name (col_name[length],……) [asc|desc]

 

三、删除索引

1、使用alter table 删除索引

Alter table table_name drop index index_name;

2、使用drop index语句删除索引

Drop index index_name on table_name;