MySQL索引

索引

1.索引概述

1.1 MySQL索引概述

​ 索引是一种将数据库中单列或者多列的值进行排序的结构。应用索引，可以大幅度提高查询的速度。
​ 用户通过索引查询数据，不但可以提高查询速度，也可以降低服务器的负载。用户查询数据时，系统可以不必遍历数据表中的所有记录，而是查询索引列。一般过程的数据查询是通过遍历全部数据，并寻找数据库中的匹配记录而实现的。与一般形式的查询相比，索引就像一本书的目录。而当用户通过目录查找书中内容时，就好比用户通过目录查询某章节的某个知识点。这样就为用户在查找内容过程中，缩短大量时间，帮助用户有效地提高查找速度。所以，使用索引可以有效地提高数据库系统的整体性能。
​ 应用MySQL数据库时，并非用户在查询数据的时候，总需要应用索引来优化查询。凡事都有双面性，使用索引可以提高检索数据的速度，对于依赖关系的子表和父表之间的联合查询时，可以提高查询速度，并且可以提高整体的系统性能。但是，创建索引和维护需要耗费时间，并且该耗费时间与数据量的大小成正比；另外，索引需要占用物理空间，给数据的维护造成很多麻烦。
​ 整体来说，索引可以提高查询的速度，但是会影响用户操作数据库的插入操作。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序。所以，用户可以将索引删除后，插入数据，当数据插入操作完成后，用户可以重新创建索引。

​ 说明：
​ 不同的存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持16个索引。总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。索引有两种存储类型，包括B树（BTREE）索引和哈希（HASH）索引。其中，B树为系统默认索引方法。

1.2　MySQL索引分类

​ MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

1．普通索引

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

2．唯一性索引

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

3．全文索引

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

4．单列索引

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

5．多列索引

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

6．空间索引

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

2.　创建索引

​ 创建索引是指在某个表中至少一列中建立索引，以便提高数据库性能。其中，建立索引可以提高表的访问速度。本节通过几种不同的方式创建索引。其中包括在建立数据库时创建索引、在已经建立的数据表中创建索引和修改数据表结构创建索引。

2.1　在建立数据表时创建索引

​ 在建立数据表时可以直接创建索引，这种方式比较直接，且方便、易用。在建立数据表时创建索引的基本语法结构如下。

1
2
3
4
5
6
7
8
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参数表示降序排列。

1．普通索引创建

​ 创建普通索引，即不添加UNIQUE、FULLTEXT等任何参数。

​ 下面创建表名为score的数据表，并在该表的id字段上建立索引，其主要代码如下：

1
2
3
4
5
6
7
8
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)
);

​ 在命令提示符中使用SHOW CREATE TABLE语句查看该表的结构，在命令提示符中输入的代码如下。

1
show create table score;
2．创建唯一性索引

​ 创建唯一性索引与创建一般索引的语法结构大体相同，但是在创建唯一索引的时候，需要使用UNIQUE参数进行约束。
​ 创建一个表名为address的数据表，并指定该表的id字段上建立唯一索引，其代码如下所示。

1
2
3
4
5
6
create table address(
id int(11) auto_increment primary key not null,
name varchar(50),
address varchar(200),
UNIQUE INDEX address(id ASC)
);

​ 说明
​ 虽然添加唯一索引可以约束字段的唯一性，但是有时候并不能提高用户查找速度，即不能实现优化查询目的。所以，读者在使用过程中需要根据实际情况来选择唯一索引。

3．创建全文索引

​ 与创建普通索引和唯一索引不同，全文索引的创建只能作用在CHAR、VARCHAR、TEXT类型的字段上。创建全文索引需要使用FULLTEXT参数进行约束。
​ 　创建一个名称为cards的数据表，并在该表的number字段上创建全文索引，其代码如下。

1
2
3
4
5
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;

​ 在命令提示符中应用SHOW CREATE TABLE语句查看表结构，其代码如下。

1
SHOW CREATE TABLE cards;
    说明
    只有MyISAM类型的数据表支持FULLTEXT全文索引，InnoDB或其他类型的数据表不支持全文索引。当用户在建立全文索引的时候，返回“ERROR 1283 (HY000): Column 'number' cannot be part of FULLTEXT index”的错误，则说明用户操作的当前数据表不支持全文索引，即不为MyISAM类型的数据表。
4．创建单列索引

​ 创建单列索引，即在数据表的单个字段上创建索引。创建该类型索引不需要引入约束参数，用户在建立时只需指定单列字段名，即可创建单列索引。
例11.4　创建名称为telephone的数据表，并指定在tel字段上建立名称为tel_num的单列索引，其代码如下。

1
2
3
4
5
6
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语句查看表的结构。
​ 说明
​ 数据表中的字段长度为50，而创建的索引的字段长度为20，这样做的目的是为了提高查询效率，优化查询速度。

5．创建多列索引

​ 与创建单列索引相仿，创建多列索引即指定表的多个字段即可实现。
例11.5　创建名称为information的数据表，并指定name和sex为多列索引，其代码如下。

1
2
3
4
5
6
7
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)
);

​ 应用SHOW CREATE TABLE语句查看创建多列的数据表结构。
​ 需要注意的是，在多列索引中，只有查询条件中使用了这些字段中的第一个字段（即上面示例中的name字段）时，索引才会被使用。
​ 说明
​ 触发多列索引的条件是用户必须使用索引的第一字段，如果没有用到第一字段，则索引不起任何作用，用户想要优化查询速度，可以应用该类索引形式。

6．创建空间索引

​ 创建空间索引时，需要设置SPATIAL参数。同样，必须说明的是，只有MyISAM类型表支持该类型索引。而且，索引字段必须有非空约束。
​ 　创建一个名称为list的数据表，并创建一个名为listinfo的空间索引，其代码如下。

1
2
3
4
5
create table list(
id int(11) primary key auto_increment not null,
goods geometry not null,
SPATIAL INDEX listinfo(goods)
)engine=MyISAM;

​ 运行上述代码，创建成功后，在命令提示符中应用SHOW CREATE TABLE语句查看表的结构。
​ 说明
​ 空间类型除了上述示例中提到的GEOMETRY类型外，还包括如POINT、LINESTRING、POLYGON等类型。这些空间数据类型在平常的操作中很少被用到。

2.2　在已建立的数据表中创建索引

​ 在MySQL中，不但可以在用户创建数据表时创建索引，用户也可以直接在已经创建的表中，在已经存在的一个或几个字段上创建索引。其基本的命令结构如下所示。

1
2
CREATE [UNIQUE | FULLTEXT |SPATIAL ] INDEX index_name
ON table_name(属性 [(length)] [ ASC | DESC]);

​ 命令的参数说明如下：
（1）index_name为索引名称，该参数作用是给用户创建的索引赋予新的名称。
（2）table_name为表名，即指定创建索引的表名称。
（3）可选参数，指定索引类型，包括UNIQUE（唯一索引）、FULLTEXT（全文索引）、SPATIAL（空间索引）。
（4）属性参数，指定索引对应的字段名称。该字段必须已经预存在用户想要操作的数据表中，如果该数据表中不存在用户指定的字段，则系统会提示异常。
（5）length为可选参数，用于指定索引长度。
（6）ASC和DESC参数，指定数据表的排序顺序。
与建立数据表时创建索引相同，在已建立的数据表中创建索引同样包含6种索引方式。

1．创建普通索引

​ 　首先，应用SHOW CREATE TABLE语句查看studentinfo表的结构。
​ 然后，在该表中创建名称为stu_info的普通索引，在命令提示符中输入如下命令。

1
create INDEX stu_info ON studentinfo(sid);

​ 输入上述命令后，应用SHOW CREATE TABLE语句查看该数据表的结构。

2．创建唯一索引

​ 在已经存在的数据表中建立唯一索引的命令如下。

1
CREATE UNIQUE INDEX 索引名 ON 数据表名称(字段名称);

​ 其中，UNIQUE是用来设置索引唯一性的参数，该表中的字段名称既可以存在唯一性约束，也可以不存在唯一性约束。

3．创建全文索引

在MySQL中，为已经存在的数据表创建全文索引的命令如下。

1
CREATE FULLTEXT INDEX 索引名 ON 数据表名称(字段名称);

​ 其中，FULLTEXT用来设置索引为全文索引。操作的数据表类型必须为MyISAM类型。字段类型必须为VARCHAR、CHAR、TEXT等类型。

4．创建单列索引

​ 与建立数据表时创建单列索引相同，用户可以设置单列索引。其命令结构如下。

1
ALTER TABLE 表名 ADD  INDEX 索引名称(字段名称(长度));

​ 同样，用户可以设置字段名称长度，以便优化查询速度，提高执行效率。

5．添加多列索引

​ 添加多列索引与建立单列索引类似。其主要命令结构如下。

1
ALTER TABLE 表名 ADD  INDEX 索引名称(字段名称1,字段名称2,…);

​ 使用ALTER修改数据表结构同样可以添加多列索引。与建立数据表时创建多列索引相同，当创建多列索引时，用户必须使用第一字段作为查询条件，否则索引不能生效。

6．添加空间索引

​ 添加空间索引，用户需要应用SPATIAL参数作为约束条件。其命令结构如下。

1
ALTER TABLE 表名 ADD  SPATIAL INDEX 索引名称(字段名称);

​ 其中，SPATIAL用来设置索引为空间索引。用户要操作的数据表类型必须为MyISAM类型，并且字段名称必须存在非空约束，否则将不能正常创建空间索引。

3.　删除索引

​ 在MySQL中，创建索引后，如果用户不再需要该索引，则可以删除指定表的索引。因为这些已经被建立且不常使用的索引，一方面可能会占用系统资源，另一方面也可能导致更新速度下降，这极大地影响了数据表的性能。所以，在用户不需要该表的索引时，可以手动删除指定索引。其中，删除索引可以通过DROP语句来实现。其基本的命令如下。

1
DROP INDEX index_name ON table_name;

​ 其中，参数index_name是用户需要删除的索引名称，参数table_name指定数据表名称，下面应用示例向读者展示如何删除数据表中已经存在的索引。打开MySQL后，应用SHOW CREATE TABLE语句查看数据表的索引，
在用户顺利删除索引后，为确定该索引是否已被删除，用户可以再次应用SHOW CREATE TABLE语句来查看数据表结构。
　 本实例将使用DROP语句从数据表中删除不再需要的索引。
​ 使用DROP语句删除workinfo表的唯一性索引index_id，具体代码如下。

1
DROP INDEX index_id ON workinfo;

4.小结

​ 本章对MySQL数据库的索引的基础知识、创建索引、删除索引进行了详细讲解，创建索引的内容是本章的重点。读者应该重点掌握创建索引的3种方法，分别为创建表的时候创建索引、使用CREATE INDEX语句来创建索引和使用ALTER TABLE语句来创建索引。