1、在mysql中,索引由数据中一列或者多列组合而成,创建索引的目的是优化数据库的查询速度,其中,用户创建的索引指向数据库中具体数据所在的位置。当用户通过索引查询数据库中的数据时,不需要遍历所有数据库中的所有数据。这样幅度提高了查询效率。与一般形式的查询相比,索引就像一本书的目录,当用户通过索引查找数据时,就好比用户通过目录查询某章节的某个知识点。
2、创建和维护索引需要耗费时间,并且该耗费时间和数据量的大小成正比。
3、整体来说,索引可以提高查询速度,但是会影响用户的插入操作。因为,向有索引的表中插入记录时,数据库系统会按照索引进行排序。所以,用户可以将索引删除后,插入数据,当数据插入操作完成后,用户可以重新创建索引。
4、mysql索引分类:普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引。
5、创建普通索引,不添加UNIQUE、FULLTEXT等任何参数,如
create table score (id int(11) auto_increment primary key not null,
name varchar(50) not null,
math int(5) not null,
englisth int(5) not null,
chinese int(5) not null,
index(id));
查询该表结构:show create table score; 可以得出
CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`math` int(5) NOT NULL,
`englisth` int(5) NOT NULL,
`chinese` int(5) NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
其中 KEY `id` (`id`)就是索引值。
6、创建唯一索引
create table address(
id int(11) auto_increment primary key not null,
name varchar(50),
address varchar(200),
UNIQUE INDEX address(id ASC));
查询该表结构:show create table address; 可以得出
CREATE TABLE `address` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`address` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `address` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
7、全文索引的创建只能作用于CHAR,VARCHAR,TEXT类型的字段上,使用FULLTEXT参数约束, 仅MyISAM类型的数据表支持FULLTEXT全文索引。
create table cards(
id int(11) auto_increment primary key not null,
name varchar(50),
number varchar(200),
info varchar(50),
FULLTEXT KEY cards_number(number));
查询该表结构:show create table cards; 可以得出
CREATE TABLE `cards` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`number` varchar(200) DEFAULT NULL,
`info` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `cards_number` (`number`)
) ENGINE=MyISAM DEFAULT CHARSET=gb
8、单列索引
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)));
查询该表结构:show create table telephone; 可以得出
CREATE TABLE `telephone` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`tel` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `tel_num` (`tel`(20))
) ENGINE=MyISAM DEFAULT CHARSET=gbk
数据表中的字段长度为250,索引的字段长度为20,
9、多列索引,以上述表为例,表示为 INDEX info(name , tel);在多列索引中,只有查询条件中使用了这些字段的第一个字段(即name),索引才会被使用。
10、空间索引,需要设置SPATIAL参数,而且只有MyISAM类型表支持该类型索引,而且索引字段必须有非空约束。
create table list(
id int(11) primary key auto_increment not null,
goods geometry not null,
SPATIAL INDEX listinfo(goods));
查询该表结构:show create table list; 可以得出
CREATE TABLE `list` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`goods` geometry NOT NULL,
PRIMARY KEY (`id`),
SPATIAL KEY `listinfo` (`goods`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
备注:首先goods字段不能为空,空间数据类型除了上述的geometry类型外,还包括POINT, LINESTRING, POLYGON等类型。
11、在已创建的数据表中创建索引的方法
(1)create INDEX stu_info ON tbl_studentinfo(sid);// sid 为列名, 普通索引
(2)create UNIQUE INDEX index_id ON tbl_class(cid);// cid为列名, 唯一索引
(3)create FULLTEXT INDEX index_2 ON tbl_xxx(info);//全文索引,info类型只能为VARCHAR, CHAR, TEXT类型,数据表类型必须为MyISAM类型
(4)create INDEX index_3 ON tbl_xxx(address(5));//仅使用address的前5个字符,单列索引
(5)create INDEX index_4 ON tbl_xxx(name, address);//用户必须使用第一字段作为查询条件,否则不能生效。
(6)create SPATIAL INDEX ON tbl_xxx(goods);//空间索引, 数据表必须为MyISAM类型,字段名称必须为非空,且为空间类型,否则不能正常创建空间索引。
12、修改表结构添加索引
alter table 表名 ADD INDEX/UINIQUE INDEX /FULLTEXT / 索引名称(字段名称)
13、删除索引
DROP INDEX index_name ON table_name