MySQL——索引

一、MySQL索引

1、索引概述

    1、索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。 

    2、建立索引的目的是加快对表中记录的查找或排序。

2、索引的优点：

1. 建立索引的列可以保证行的唯一性，生成唯一的rowId

2. 建立索引可以有效缩短数据的检索时间

3. 建立索引可以加快表与表之间的连接

4. 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序

3、索引的缺点： 

1. 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大

2. 创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）

3. 会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

二、索引分类

1、普通索引

    普通索引，即不应用任何限制条件的索引，该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或唯一。创建该类型索引后，用户在查询时，便可以通过索引进行查询。在某数据表的某一字段中，建立普通索引后。用户需要查询数据时，只需根据该索引进行查询即可。

2、唯一性索引

    使用 UNIQUE 参数可以设置唯一索引。创建该索引时，索引的值必须唯一，通过唯一索引，用户可以快速定位某条记录，主键是一种特殊唯一索引。

3、全文索引

    使用 FULLTEXT 参数可以设置索引为全文索引。全文索引只能创建在 CHAR、VARCHAR 或者TEXT 类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，查询带有文章回复内容的字段，可以应用全文索引方式。需要注意的是，在默认情况下，应用全文搜索大小写不敏感。如果索引的列使用二进制排序后，可以执行大小写敏感的全文索引。

4、单列索引

    顾名思义，单列索引即只对应一个字段的索引。其可以包括上述叙述的 3 种索引方式。应用该索引的条件只需要保证该索引值对应一个字段即可。

5、多列索引

    多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询。要想应用该索引，用户必须使用这些字段中的第一个字段。

6、空间索引

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

三、创建索引

1、在建立数据表时创建索引

    基本语法：

create table table_name(
属性名 数据类型[约束条件],
属性名 数据类型[约束条件],
……
属性名 数据类型
[UNIQUE | FULLTEXT | SPATIAL] INDEX}KEY
[别名](属性名1 [(长度)][ASC | DESC])
);

    （1）、UNIQUE：可选项，表明索引为唯一性索引。

    （2）、FULLTEXT：可选项，表明索引为全文搜索。

    （3）、SPATIAL：可选项，表明索引为空间索引。

    INDEX 和 KEY 参数用于指定字段索引，用户在选择时，只需要选择其中的一种即可；另外别名为可选项，其作用是给创建的索引取新名称；别名的参数如下。

    （1）、属性名 1：指索引对应的字段名称，该字段必须被预先定义。

    （2）、长度：可选项，指索引的长度，必须是字符串类型才可以使用。

    （3）、ASC/DESC：可选项，ASC 表示升序排列，DESC 参数表示降序排列。

2、普通索引创建

#创建索引为id的score表

create table score(
    id int(10) primary key not null,
    name varchar(45) not null,
    math int(5) not null,
    english int(5) not null,
    chinese int(5) not null,
    index(id)
);

3、创建唯一性索引

#创建名为address的唯一索引

create table score(
    id int(10) primary key not null,
    name varchar(45) not null,
    math int(5) not null,
    english int(5) not null,
    chinese int(5) not null,
    UNIQUE INDEX address(id ASC)
);

4、创建全文索引

#创建名为cards的数据表，并在该表的number字段上创建全文索引

create table cards(
    id int(10) primary key not null,
    name varchar(45),
    number bigint(11),
    info varchar(50),
    FULLTEXT KEY cards_info(info)
)engine=MyISAM

5、创建单列索引

#创建名为telephone的数据表，并指定在tel字段上建立名为tel_num的单列索引

  create table telephone(
      id int(10) primary key not null,
      name varchar(45),
      tel varchar(50) not null,
      index tel_num(tel(20))
  );

6、创建多列索引

#创建名为information的数据表，并指定name和sex为多多列索引

create table information(
    id int(10) primary key not null,
    name varchar(45),    
    sex varchar(50) not null,
    INDEX info(name,sex)4
);

7、创建空间索引

#创建名为list的数据表，并创建一个名为listinfo的空间索引

create table list(
    id int(10) primary key not null,
    goods geometry not null,
    SPATIAL INDEX listinfo(goods)
)engine=MyISAM;

四、在以建立的数据表中创建索引

1、创建普通索引，比如创建一个名为 Normal_index 的普通索引

CREATE INDEX Normal_index ON table_name(field);

2、创建唯一索引

CREATE UNIQUE INDEX only_index ON table_name(field);

3、创建全文索引

CREATE FULLTEXT INDEX fulltext_index ON table_name(field);

4、创建单列索引

CREATE INDEX Single_index ON table_name(field);

5、创建多列索引

CREATE INDEX MoreColumn_index ON table_name(field1,field2);

6、创建空间索引

空间索引的数据类型必须是MyISAM类型，并且字段名称必须存在非空约束，否则不能正常创建空间索引

CREATE SPATIAL INDEX Spatial_index ON table_name(field);

五、修改数据表结构添加索引

1、添加普通索引

ALTER TABLE Normal_index ADD INDEX index_name(attribute(length));

2、添加唯一索引

ALTER TABLE table_name ADD UNIQUE INDEX only_index(field);

3、添加全文索引

ALTER TABLE table_name ADD FULLTEXT INDEX fulltext_index(field);

4、添加单列索引

ALTER TABLE table_name ADD FULLTEXT INDEX Single_index(attribute(length));

5、添加多列索引

ALTER TABLE table_name ADD FULLTEXT INDEX MoreColumn_index(field1,field2);

6、添加空间索引

ALTER TABLE table_name ADD SPATIAL INDEX Spatial_index(field);

六、删除索引

DROP INDEX Index_name ON table_name;