MySQL中的索引介绍

MySQL中，索引用于快速找出在某个列中有一特定值的行。

索引优缺点：

优点：可以提高检索数据的速度。

缺点：创建和维护索引需要耗费时间，即会影响增加、删除和修改记录的速度；需要占用物理空间。

索引分类：

1.普通索引：可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。

2.唯一性索引：使用unique参数设置索引为唯一性索引。限制该索引的值必须是唯一的。主键是一种特殊唯一性索引。

3.全文索引：使用fulltext参数设置索引为全文索引。全文索引只能创建在char、varchar或text类型的字段上。只有MyISAM存储引擎支持全文索引。默认搜索执行方式不区分大小写，但索引的列使用二进制排序后，可以执行区分大小写的全文索引。

4.单列索引：在表中的单个字段上创建索引。

5.多列索引：在表的多个字段上创建一个索引。

6.空间索引：使用spatial参数设置索引为空间索引。空间索引只能建立在空间数据类型上。MySQL中的空间数据类型包括geometry和point、linestring和polygon等。只有MyISAM存储引擎支持空间索引，而且索引的字段不能为空。

索引设计原则：

1.选择唯一性索引

2.为经常需要排序、分组和联合操作的字段建立索引

3.为常作为查询条件的字段建立索引

4.限制索引的数目

5.尽量使用数据量少的索引

6.尽量使用前缀来索引

7.删除不再使用或者很少使用的索引

创建索引：

1.创建表的时候创建索引

create table 表名(属性名 数据类型[完整性约束条件]，

属性名 数据类型[完整性约束条件]，

……

属性名 数据类型

[unique|fulltext|spatial] index|key [别名](属性名1 [(长度)] [asc|desc])

);

(1).创建普通索引

create table index1(id int,name varchar(20),sex boolean,index(id));

(2).创建唯一性索引

create table index2(id int unique,name varchar(20),unique index index2_id(id asc));

(3).创建全文索引

create table index3(id int,info varchar(30),fulltext index index3_info(info))engine=myisam;

(4).创建单列索引

create table index4(id int,subject varchar(30),index index4_st(subject(10)));

(5).创建多列索引

create table index5(id int,name varchar(20),sex char(4),index index5_ns(name,sex));

(6).创建空间索引

create table index6(id int,space geometry not null,spatial index index6_sp(space))engine=myisam;

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

create [unique|fulltext|spatial] index 索引名 on 表名 (属性名 [(长度)] [asc|desc]);

(1).创建普通索引

create index index7_id on example0(id);

(2).创建唯一性索引

create unique index index8_id on index8(course_id);

(3).创建全文索引

create fulltext index index8_info on index9(info);

(4).创建单列索引

create index index10_addr on index10(address(4));

(5).创建多列索引

create index index11_na on index11(name,address);

(6).创建空间索引

create spatial index index12_line on index12(line);

3.用alter table语句来创建索引

alter table 表名 add [unique|fulltext|spatial] index 索引名(属性名[(长度)] [asc|desc]);

(1).创建普通索引

alter table example0 add index index12_name(name(20));

(2).创建唯一性索引

alter table index14 add unique index index14_id(course_id);

(3).创建全文索引

alter table index15 add fulltext index index15_info(info);

(4).创建单列索引

alter table index16 add index index16_addr(address(4));

(5).创建多列索引

alter table index17 add index index17_na(name,address);

(6).创建空间索引

alter table index18 add spatial index index18_line(line);

删除索引

drop index 索引名 on 表名;