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;
结果:
删除其他的索引亦是如此.