MySQL数据库学习之索引

1 什么是索引

• 索引是由数据表的一类或者多列组成
• 创建索引的目的是优化数据库的查询速度
• 创建的索引指向数据库中具体数据所在.
• 创建了索引之后,查找数据时,就不需要遍历所有的数据,大大提高了查询效率.同时降低了服务器的负载率.

优点: 简单来说,索引就像是一本书的目录,我们可以通过这个目录找到具体的数据,从而缩短查询时间.
缺点: 创建索引和维护索引需要耗费时间,同时,索引需要占用物理空间,会给数据的维护造成麻烦.

2 索引的分类

2.1普通索引

即不应用任何限制条件的索引.

2.2唯一索引

使用unique参数修饰可以设置唯一索引,常见索引时,索引的值必须唯一,主键索引时一种特殊的唯一索引.

2.3全文索引

使用fulltest参数可以设置随用为全文索引

注意: 全文索引只能创建在char,varchar或者text类型的字段上.

2.4单列索引

只对应一个字段的索引.

2.5多列索引

在表的多个字段上创建一个索引,该索引指向创建是对应的多个字段,要想应用该索引,用户必须使用这些字段中的第一个字段.

2.6空间索引

使用 spatial 参数可以设置索引为空间索引,空间索引只能建立在空间数据类型上,这样可以提高系统获取空间数据的效率,Mysql中只有MyISAM存储引擎支持空间索引,而且索引的字段不能为空.

3 创建索引

创建索引有三种方式:

• 在建立数据表时创建索引
• 已建立数据表之后添加索引
• 修改数据表结构添加索引

3.1在建立数据表时创建索引

3.1.1 创建普通索引

CREATE TABLE score(
	id INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
	`name` VARCHAR(50) NOT NULL,
	math INT(5) NOT NULL,
	english INT(5) NOT NULL,
	chinese INT(5) NOT NULL,
	INDEX(id)
);

结果:

3.1.2创建唯一索引

CREATE TABLE address(
	id INT(11) AUTO_INCREMENT  PRIMARY KEY NOT NULL,
	`name` VARCHAR(50),
	address VARCHAR(50),
	UNIQUE INDEX address(id ASC)
)CHARSET =utf8;

使用 show create table 可以查看创建表时的结构
结果:

3.1.3 创建全文索引

CREATE TABLE cards(
	id  INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
	`name` VARCHAR(50),
	number BIGINT (11),
	info VARCHAR(50),
	FULLTEXT KEY cards_info(info)
) ENGINE=MYISAM CHARSET =utf8;

结果:

备注说明:
只有MyISAM类型的数据表支持fulltext 全文索引,其他的殷勤不支持全文索引.

3.1.4 创建单列索引

创建单列索引,即在数据表的单个字段上创建索引.创建该类型的索引时不需要引入约束参数,用户只需要在建立是指定字段名即可.

CREATE TABLE telephone(
	id INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
	`name` VARCHAR(50) NOT NULL,
	tel VARCHAR(50) NOT NULL,
	INDEX tel_num (tel(20))
);

说明:
数据表中的字段长度为50,二创建的索引的字段长度为20,这样做的目的是为了提高查询效率.

结果:

3.1.5 创建多列索引

创建多列索引只需要指定表的多个字段即可

说明:
触发多列索引的第一条件是用户必须使用索引的第一字段,如果没有用到弟子字段,则该索引不起任何作用.

#创建所列索引
CREATE TABLE information(
	id INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
	`name` VARCHAR(50) NOT NULL,
	sex VARCHAR(5) NOT NULL,
	birthday VARCHAR(50) NOT NULL,
	INDEX info(`name`,sex)
)CHARSET =utf8;

结果:

3.1.6 创建空间索引

创建空间索引时,需要设置spatial参数,同样的 只有MyISAM类表支持该类型的索引.

# 创建空间索引
CREATE TABLE LIST (
	id INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
	goods GEOMETRY NOT NULL,
	SPATIAL INDEX listinfo(goods)
)ENGINE = MYISAM;

以上的代码中,goods字段不能为空,且数据类型是geometry,该类型是空间数据类型,空间数据类型不能用其他类型代替.其他的空间数据类型还有 point,linestring,polygon等类型.

结果:

3.2在已建立的数据表中创建索引

格式:create [unique | fulltext |spatial ] index index_name
on table_name(属性[(length)] [ASC | DESC])

例如:创建普通索引

CREATE INDEX stuinfo ON student (studentNo);

结果:

其他的索引同理

3.3修改数据表结构添加索引

格式: alter table table_name ADD [unique | fulltext |spatial] index
index_name(属性名[length]) [ASC | DESC ]

例如:添加普通索引

ALTER TABLE student ADD INDEX studentName(studentName(10));

结果:

其他的所以同理.

4 删除索引

创建索引以后,如果用户不再需要该索引以后,则可以删除指定表的索引,被建立的索引如果不常是欧诺个,会占用系统资源,也会导致更新所读下降. 使用 drop 关键字删除索引

格式:drop index index_name on table_name;

例如:删除student表的studentName索引.

DROP INDEX studentName ON student;

结果:

删除其他的索引亦是如此.