mysql 笔记二 -- 索引

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