一、索引
索引由数据库表中一列或者多列组合而成,其作用是提高对表中数据的查询速度。
索引是创建在表上面的,是对数据表中一列或者多列的值进行排序的一种结构。
通过索引,查询数据时可以不必读完记录的所有信息,而只是查询索引列。
索引优点:提高检索数据的速度;
索引缺点:创建和维护索引需要耗费时间,耗费时间的数量随着数据量的增加而增加;索引需要占用物理空间,每一个索引要占一定的物理空间,
增加、删除、修改数据时,要动态的维护索引,造成数据的维护速度降低了。
注意:索引可以提高查询速度,但是会影响插入记录的速度。向有索引的表中插入记录时,数据库会按照索引进行排序,这样就降低了插入记录的速度,
插入大量记录时的速度影响更加明显。这种情况下,最好的办法是先删除表中的索引,然后再插入数据。插入完成后,再创建索引。
索引的分类
1、普通索引
2、唯一性索引
3、全文索引
4、单列索引
5、多列索引
6、空间索引
==================================================================================================
索引的设计原则
1、选择唯一性索引
2、为经常需要排序、分组和联合操作的字段建立索引
3、为常作为查询条件的字段建立索引
4、限制索引的数目
5、尽量使用数据量少的索引
6、尽量使用前缀索引
7、删除不再使用或者很少使用的索引
===========================================================================================================
创建索引
一、创建表的时候创建索引
语法格式如下:
create table 表名 ( 属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
……
属性名 数据类型
[unique | fulltext | spatial] index | key
[ 别名 ] ( 属性名1 [(长度)]) [ asc | desc ] )
);
unique是可选参数,表示索引为唯一性索引;
fulltext是可选参数,表示索引为全文索引;
spatial是可选参数,表示索引为空间索引;
index 和 key参数用来指定字段为索引的,两者选择其中之一就可以了,作用是一样的;
“别名”是可选参数,用来给创建的索引取的新的名称;
“属性1”参数指定索引对应的字段的名称,该字段必须为前面定义好的字段;
“长度”是可选参数,其指索引的长度,必须是字符串类型才可以使用;
“asc”和“desc”都是可选参数,“asc”表示升序排序,“desc”表示降序排序;
1、创建普通索引
创建一个普通索引,不需要加任何unique、fulltext、spatial参数。
示例:
create table index1 ( id int,
name varchar(20),
sex varchar(20),
index index1_id (id)
);
show create table index1;
explain select * from index1 where id = 1; /*possible_keys:index1_id;kye:index1_id ;说明索引被引用了*/
2、创建唯一性索引
创建唯一性索引时,需要使用unique参数进行约束。
示例:创建一个表名为index2的表,表中的id字段上建立名为index2_id的唯一性索引,且以升序的形式排列。
create table index2 ( id int,
name varchar(20),
sex varchar(20),
unique index index2_id (id asc)
);
show create table index2;
3、创建全文索引
全文索引,只能创建在char、varchar、text类型的字段上,而且只有myisam存储引擎支持全文索引。
create table index3 ( id int,
info varchar(20),
fulltext index index3_info ( info )
)engine=myisam;
show create table index3;
4、创建单列索引
单列索引是在表的某一个字段上创建索引
subject字段的长度为20,而index4_st索引的长度只有10,这样做的目的是为了提高查询速度。
对于字符型的数据,可以不用查询全部信息,而只查询前面的若干字符信息。
create table index4 ( id int,
subject varchar(30),
index index4_st ( subject(10) )
);
show create table index4;
5、创建多列索引
创建多列索引是在表的多个字段上创建一个索引
create table index5 ( id int,
name varchar(30),
sex varchar(4),
index index5_ns ( name,sex )
);
show create table index5;
可以看出,name和sex字段上已经建立了一个名为index5_ns的单列索引。
多列索引里,只有查询条件使用了这些字段中的第一个字段时,索引才会被使用;
如果没有使用索引中的第一个字段,那么这个多列索引就不会起作用。
6、创建空间索引
创建空间索引时,必须使用spatial参数来设置。而且,创建空间索引时,表的存储引擎必须是myisam类型。而且,索引字段必须有非空约束。
create table index6 ( id int,
space geometry not null,
spatial index index6_sp ( space )
)engine=myisam;
show create table index6;
注意:space字段必须是非空的,而且数据类型是geometry类型。这个类型是空间数据类型。空间数据类型包括:geometry、point、linestring、polygon类型等
==================================================================================================================================
二、在已经存在的表上创建索引
在已经存在的表中,可以直接为表上的一个或几个字段创建索引。格式如下:
create [unique | fulltext | spatial] index 索引名
on 表名 ( 属性名 [(长度)]) [ asc | desc ] );
unique是可选参数,表示索引为唯一性索引;
fulltext是可选参数,表示索引为全文索引;
spatial是可选参数,表示索引为空间索引;
index 用来指定字段为索引的;
“索引名”参数是给创建的索引取的新名称;
“表名”是指需要创建索引的表的名称,该表必须是已经存在的,如果不存在,需要先创建;
“属性名”参数指定索引对应的字段的名称,该字段必须为前面定义好的字段;
“长度”是可选参数,其指索引的长度,必须是字符串类型才可以使用;
“asc”和“desc”都是可选参数,“asc”表示升序排序,“desc”表示降序排序;
1、创建普通索引
create table example0 ( id int,
name varchar(20),
sex varchar(40)
);
show create table example0;
create index index7_id on example0( id );
2、创建唯一性索引
create unique index index8_id on index8( course_id );
3、创建全文索引
create fulltext index index9_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 );
==================================================================================================
三、用alter table语句来创建索引
在已经存在的表上,可以通过alter table语句直接为表上的一个或几个字段创建索引
语法格式如下:
alter table 表名 add [unique | fulltext | spatial] index 索引名 ( 属性名 [(长度)]) [ asc | desc ] );
1、创建普通索引
alter table example0 add index index13_name ( name );
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 表名;
========================================================================================================================
mysql——索引——概念
点赞
收藏