索引可以理解为表的目录,它实质上是一种满足特定查找算法的数据结构,它是在数据库的存储引擎层实现的,而不是在服务层,所以不同的存储引擎的索引也不一定相同。
索引用于优化查询,快速找出某一列中有特定值的行,如果没有索引,会对表中数据进行顺序查询,表越大,花费时间越多。但如果要访问表中大部分的行,则顺序读取要快得多,因为避免了对索引的磁盘搜索。
索引的好处:
1、可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
2、通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
3、可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
4、在使用分组group by和排序order by子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
5、通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能
索引的不利方面:
1、创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
2、索引需要占物理空间,建立的索引越多,那么需要的空间就会越大。
3、当对表中的数据进行增加、删除和修改的时候,索引也要随之改变,进行动态的维护,这样就降低了数据的维护速度。
所以在创建索引的时候,要考虑清楚在哪个列上创建,创建哪种索引
创建索引的一般原则:
1、在经常需要查询的列上创建索引;
2、经常需要范围查询的列,因为索引已经排序,这样利用其加快查询速度
3、出现在where子句中的列,而不是出现在select后的列
4、使用唯一索引,索引列的基数越大,索引的效果越好。例如出生年月列比性别列更适合做索引,性别列只有两种结果,不管搜索哪一行,都会得到大约一半的行,而出生年月列具有多种不同的值,很容易区分出各行。
3、使用短索引,在对字符串字段使用前缀索引时,只要所有行的前n个字符是唯一的,也就是能够将各行区分开来的,就对前n个字符进行索引。这样不但能节省索引空间,还能使得查询更快
4、不要过度索引,每一个索引都要占用额外的磁盘空间,并降低写操作的性能,并且在修改表时,索引需要进行更新或重构,因此索引越多,所花的时间就越长,反而降低了查询效率
5、对于InnoDB存储引擎的表,它是按照一定的顺序来保存的,首先是主键,若没有主键,按唯一索引,如果也没有唯一索引,按照该表自动生成的一个内部列的顺序进行保存。所以,尽量自己指定主键,并且要尽可能选用较短的数据类型的列进行索引,因为InnoDB的普通索引都会保存主键的键值,选用较短的数据类型,可以有效的减少索引的磁盘占用空间,提高索引的缓存效果
MySQL目前支持4种索引:
1、B-tree索引(Blanced tree 平衡树索引),大部分存储引擎都支持,并且应用广泛
当使用>,<,>=,<=,!=,between这种范围查询,或者like ''等操作符时,一般选择Btree
2、HASH索引,至于MEMORY支持
3、R-tree(空间索引),是MyISAM的一个特殊索引
适用于=,>=,<=这样的等式比较,也是能使用整个关键词来搜索
4、Full-text(全文索引),MyISAM支持
创建索引:
create index|unique|fulltext [index_name] using [index_type] on [table_name](col_name[(length)] [ASC|DESC])
length用于前缀索引时指定索引几个字节
index_type指索引类型
也可以在create table和alter table语句中创建索引
create table emp (
->id int not null auto_increment primary key,
->name varchar(11) not null,
->salary int not null,
->index index_name(name)
);
alter table emp add index index_name(name);
索引的分类:
1、普通索引 alter table emp add index index_name(name);
2、唯一索引 alter table emp add unique index_name(id);
3、多列索引:alter table emp add index index_name(id,name,info(10));
4、全文索引:alter table emp add fulltext index index_name(name);
5、空间索引:alter table emp add spatial index index_name(name);
删除索引:
drop index index_name on table_name;
查看索引:
show index from ts
扩展:
在创建索引时,可以指定在更新或删除表父表时,对子表的相应操作,指定参数包括:restrict、no action、cascade、set null(父表、子表是外键(foreign key)中的概念,父表即含被参考字段的表,子表就是参考了另外一张表中字段的表)
---restrict和no action相同,表示在父表中,当遇到与子表有关联的字段时,不允许更新或删除
---cascade表示父表在更新或操作时,字表对应字段也进行相应的更新和删除
---set null表示父表在更新或删除时,字表的对应字段设置为null
例:create table emp (id int not null primary key auto_increment,
name varchar(11) not null,
salary int not null
foreign key (id) references emp2 (id) on delete restrict on update casacde) engine-innodb default charset=utf8;
on delete restrict on update casacde表示当父表进行删除操作时,如果子表中有对应(参照)的字段时,该删除操作不能进行,当父表有更新操作时,如果子表对应字段也进行相应的更新
如有错误,敬请指正~~