MySQL--索引(1)

一、索引概述

​ 1.在MySQL中，索引由数据表中的一列或者多列组成，创建索引的目的是为了优化数据库的查询速度。其中，用户创建的索引指向数据库中具体数据所在的位置。当用户通过索引查询数据库中的数据时，不需要遍历所有数据库中的所有数据，大幅度提高了查询效率。

​ 2.索引的缺点：创建索引和维护需要消耗时间，并且该耗费时间与数据量的大小成正比；另外，索引需要占用物理空间，给数据的维护造成很多麻烦。

​ 整体来说，索引可以提高查询的速度，但是会影响用户操作数据库的插入操作。

二、MySQL索引分类

1.普通索引

​ 不应用任何限制条件的索引，该索引可以在任何数据类型中创建。创建该类型索引后，用户在查询时，便可以通过索引进行查询。

2.唯一性索引

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

3.全文索引

​ 使用FULLTEXT参数可以设置索引为全文索引。全文索引只能在CHAR、VARCHAR或者TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。

4.单列索引

​ 只对应一个字段的索引，可以包括上述3种索引方式。应用该索引的条件指只需保证该索引值对应一个字段即可。

5.多列索引

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

6.空间索引

​ 使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。

三、创建索引

1.在创建数据表时创建索引

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

·UNIQUE: 可选项，表明索引为唯一性索引。

·FULLTEXT:可选项，表明索引为唯一性索引。

·SPATIAL: 可选项，表明索引为空间索引。

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

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

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

·ASC/DESC：可选项，ASC表示升序排序，DESC参数表示将降序排序。

（1）普通索引创建

​ 例：创建user表，在该表的id字段上建立索引。

create table user(
id int(11) auto_increment primary key not null,
name varchar(50) not null,
account varchar(50) not null,
index(id);)

（2）创建唯一性索引

​ 创建唯一性索引与创建一般索引的语法结构大体相同，但是在创建唯一索引的时候，需要使用UNIQUE参数进行约束。例：创建user表，在该表的id字段上建立唯一性索引。

create table user(
id int(11) auto_increment primary key not null,
name varchar(50) not null,
account varchar(50) not null,
UNIQUE index(id ASC);)

（3）创建全文索引

​ 与创建普通索引和唯一索引不同，全文索引的创建只能作用在CHAR、VARCHAR、TEXT类型的字段上。创建全文索引需要使用FULLTEXT参数进行约束。

（4）创建单列索引

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

​ 例：创建名称为telephone的数据表，并指定在tel字段上建立名称为tel_num的单列索引，其代码如下。

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))
);

（5）创建多列索引

​ 与创建单列索引相仿，创建多列索引即指定表的多个字段即可实现。

​ 例：创建information的数据表，并指定name和sex为多列索引，

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));

（6）创建空间索引

​ 创建空间索引时，需要设置SPATIAL参数。