MySQL索引

索引是一种特殊的数据库结构，是提高数据库性能的重要方式，可以用来快速查询数据库表中的特定记录，MySQL中所有数据类型都可以被索引。MySQL的索引包括：普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

1、索引概述

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

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存储引擎支持空间检索，而且索引的字段不能为空值。

1.3 MySQL索引命名规范

（1）非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名

（2）唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名

 

2、创建索引

创建索引是指在某个表中至少一列中建立索引，以便提高数据库性能。

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

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

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

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

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

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

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

2.1.1 创建普通索引

示例：创建表名为tb_user的数据表，并在该表的id字段上建立普通索引。

drop table if exists tb_user;
create table tb_user(
	id int(11) auto_increment primary key not null,
	name varchar(50) not null,
	INDEX(id)
);

2.1.2 创建唯一性索引

示例：创建表名为tb_user的数据表，并在该表的id字段上建立一个名称为uniq_id的唯一性索引。

drop table if exists tb_user;
create table tb_user(
	id int(11) auto_increment primary key not null,
	name varchar(50) not null,
	UNIQUE INDEX uniq_id(id ASC)
);

2.1.3 创建多列索引

示例：创建表名为tb_user的数据表，并在该表的id和name字段上建立一个名称为idx_id_name的多列索引。

drop table if exists tb_user;
create table tb_user(
	id int(11) auto_increment primary key not null,
	name varchar(50) not null,
	INDEX idx_id_name(id,name)
);

 

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

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

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

2.2.1 创建普通索引

-- 创建普通索引
CREATE INDEX idx_id ON tb_user(id);

2.2.2 创建唯一性索引

-- 创建唯一性索引
CREATE UNIQUE INDEX uniq_id ON tb_user(id ASC);

2.2.3 创建多列索引

-- 创建多列索引
CREATE INDEX idx_id_name(id,name);

 

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

修改已经存在表上的索引。可以通过ALTER TABLE语句为数据表添加索引，其基本结构如下：

ALTER TABLE table_name ADD [ UNIQUE | FULLTEXT |SPATIAL ] INDEX index_name(属性名 [(length)] [ASC | DESC]);

示例：通过修改数据表结构添加索引。

ALTER TABLE tb_user ADD UNIQUE INDEX uniq_id(id ASC);

 

3、删除索引

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

DROP INDEX index_name ON table_name;

示例：将tb_user表中的名为uniq_id的索引删除。

DROP INDEX uniq_id ON tb_user;