MySQL索引操作详解
本文只介绍索引的基本概念及其创建与删除的基本操作，不涉及索引的底层实现原理的相关内容，后续博文会更新有关索引的底层实现与索引优化的相关知识。
文章目录
在数据库的操作中，经常需要查找特定的数据，例如，当执行"select * from studet where id = 10000"的语句时，MySQL数据库必须从第一条记录开始遍历，直到找到id为10000的数据，这样的效率显然非常低。
为此，MySQL允许建立索引来加快数据表的查询和排序。
索引的概念
数据库的索引就好比新华字典的音序表，它是对数据库中一列或多列的值进行排序后的一种结构，其作用就是提高表中数据的查询速度。MySQL中的索引分为很多种，具体如下：
1、普通索引
普通索引是由KEY或INDEX定义的索引，它是MySQL中的基本索引类型，可以创建在任何数据类型中，其值是否唯一和非空是由字段本身的约束条件所决定。
2、唯一性索引
唯一性索引是由UNIQUE定义的索引，该索引所在字段的值必须是唯一的。
3、全文索引
全文索引是由FULLTEXT定义的索引，它只能创建在char、varchar或text类型的字段上，而且，现在只有MyISAM存储引擎支持全文索引。
4、单列索引
单列索引指的是在表中单个字段上创建索引，它可以是普通索引、唯一索引或者全文索引，只要保证该索引对应表中的一个字段即可。
5、多列索引
多列索引指的是在表中的多个字段上创建索引，只有在查询条件中使用了这些字段中的第一个字段时，该索引才会被使用。
6、空间索引
空间索引是由SPATIAL定义的索引，它只能创建在空间数据类型的字段上，MySQL中的空间数据类型有四种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。需要注意的是，创建空间索引的字段，必须将其声明为NOT NULL，并且空间索引只能在存储引擎为MyISAM的表中创建。
需要注意的是，虽然索引可以提高数据的查询速度，但索引会占用一定的磁盘空间，并且在创建和维护索引时，其消耗的时间是随着数据量的增加而增加的。因此，使用索引时，应该综合考虑索引的优点和缺点。
创建索引
要想使用索引提高数据表的访问速度，首先要创建一个索引。创建索引的方式有以下三种：
- 创建表的时候创建索引
- 使用create index语句已存在的表上创建索引
- 使用alter table语句已存在的表上创建索引
创建表的时候创建索引
创建表的时候可以直接创建索引，这种方式最简单、方便，其基本的语法格式如下：
create table 表名(字段名 数据类型[完整性约束条件]，
字段名 数据类型[完整性约束条件]
···
字段名 数据类型[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
[别名](字段名1 [(长度)]) [ASC | DESC]
);
关于上述语法的相关解释具体如下：
- UNIQUE：可选参数，表示唯一索引
- FULLTEXT：可选参数，表示全文索引
- SPATIAL：可选参数，表示空间索引
- INDEX 和 KEY：用来表示字段的索引，二者选一即可
- 别名：可选参数，表示创建索引的名称
- 字段名1：指定索引对应字段的名称
- 长度：可选参数，用于表示索引的长度
- ASC和DESC：可选参数，其中ASC表示升序排列，DESC表示降序排列
创建普通索引
在t1表中的 id 字段上创建索引：
create table t1
(id INT,
name VARCHAR(20),
score FLOAT,
index(id)
);
上述SQL语句执行完毕后，使用SHOW CREATE TABLE语句可以查看表的结构：
从上述结果可以看出，id 字段上已经创建了一个名称为 id 的普通索引。为了查看索引是否被使用，可以使用 explain 语句进行查看：
从上述执行结果可以看出，possible_keys 和 key 的值都为 id，说明 id 索引已经存在，并且已经开始使用了。
创建唯一性索引
创建一个名为t2的表，在其 id 字段上创建名为unique_id 的唯一性索引，并按照升序排列：
create table t2
(id INT,
name VARCHAR(20) NOT NULL,
score FLOAT,
unique index unique_id(id ASC)
);
从上述结果可以看出，id 字段上已经建立了一个名为 unique_id 的唯一性索引。
创建全文索引
创建一个名为t3的表，在其 name 字段上创建名为 fulltext_name 的全文索引：
create table t3
(id INT NOT NULL,
name VARCHAR(20) NOT NULL,
score FLOAT,
fulltext index fulltext_name(name)
)ENGINE=MyISAM;
从上述结果可以看出，name 字段上已经建立了一个名为 fulltext_name 的全文索引。
需要注意是，目前只有MyISAM存储引擎支持全文索引，InnoDB存储引擎还不支持全文索引。因此，在建立全文索引时，一定要注意表存储引擎的类型，对于经常需要索引的字符串、文字数据等信息，可以考虑存储到MyISAM存储引擎中。
创建单列索引
创建一个名为t4的表，在其 name 字段上创建名为 single_name 、长度为20 的单列索引：
create table t4
(id INT NOT NULL,
name VARCHAR(20) NOT NULL,
score FLOAT,
index single_name(name(20))
);
从上述结果可以看出，name 字段上已经建立了一个名为 single_name 的单列索引，并且索引的长度为20。
创建多列索引
创建一个名为t5的表，对其 id 和 name字段建立名为 multi 的多列索引：
create table t5
(id INT NOT NULL,
name VARCHAR(20) NOT NULL,
score FLOAT,
index multi(id, name(20))
);
从上述结果可以看出， id 和 name字段上已经建立了一个名为 multi 的单列索引。
需要注意的是，在多列索引中，只有查询的条件中使用了这些字段中的第一个字段，多列索引才会被使用，接下来我们做以验证：
我们将id字段作为查询条件，通过explain语句来查看索引的使用情况：
从上述执行结果可以看出，possible_keys 和 key 的值都为 multi，说明 multi 索引已经存在，并且已经开始被使用了。
但是，如果只是用name字段作为查询条件，SQL的执行结果如下所示：
从上述执行结果可以看出，possible_keys 和 key 的值都为 NULL，说明 multi 索引并没有被使用。
创建空间索引
创建一个名为t6的表，在空间类型为 GEOMETRY 的字段上创建名为 sp 的空间索引：
create table t6
(id INT,
space GEOMETRY NOT NULL,
SPATIAL index sp(space)
)ENGINE=MyISAM;
从上述结果可以看出， t6表中的 space 字段上已经建立了一个名为 sp 的空间索引。
需要注意的是，创建空间索引的字段，必须将其声明为NOT NULL，并且空间索引只能在存储引擎为MyISAM的表中创建。
使用CREATE INDEX语句在已经存在的表上创建索引
若想在一个已存在的表上创建索引，可以使用CREATE INDEX语句，具体语法格式如下所示：
create [unique | fulltext | spatial] index 索引名
on 表名(字段名 [(长度)] [ASC | DESC]);
为了做以演示，接下来我们创建表book，该表中没有建立任何索引：
/* 创建基本表book */
create table book(
book int not null,
bookname varchar(255) not null,
authers varchar(255) not null,
info varchar(255) null,
comment varchar(255) null,
publicyear year not null
);
创建好数据表后，接下来演示如何使用 CREATE INDEX 在已存在的表上创建索引。
创建普通索引
在book表中的bookid字段上建立一个名为index_id的普通索引。
/* 创建普通索引 */
create index index_id on book(bookid);
从上述结果可以看出，bookid 字段上已经创建了一个名称为 index_id 的普通索引。
创建唯一性索引
在book表中的 bookid 字段上建立一个名为 uniqueidx 的普通索引。
/* 创建唯一性索引 */
create unique index uniqueidx on book(bookid);
从上述结果可以看出，bookid 字段上已经创建了一个名称为 uniqueidx 的唯一性索引。
创建单列索引
在book表中的 comment 字段上建立一个名为 singleidx 的单列索引
/* 创建单列索引 */
create index singleidx on book(comment);
从上述结果可以看出，comment 字段上已经创建了一个名称为 singleidx 的单列索引。
创建多列索引
在book表中的 authors 和 info 字段上建立一个名为 mulitidx 的多列索引。
/* 创建多列索引 */
create index mulitidx on book(authors(20), info(20));
从上述结果可以看出，authors 和 info 字段上已经创建了一个名称为 mulitidx 的多列索引。
创建全文索引
由于创建全文索引必须使用 MyISAM 存储引擎。因此，我们修改数据表的存储引擎如下：
ALTER TABLE book ENGINE=MyISAM;
但是由于我们之前建立了太多的索引，在MyISAM存储引擎在创建索引的时候，索引键长度是有一个较为严格的长度限制的，所有索引键最大长度总和不能超过1000，而且不是实际数据长度的总和，而是索引键字段定义长度的总和。
我们删除一些索引，便可更改存储引擎成功：
接下来我们在book表的info字段上创建名为 fulltextidx 的全文索引。
/* 创建全文索引 */
create fulltext index fulltextidx on book(info);
从上述结果可以看出，info 字段上已经创建了一个名称为 fulltextidx 的全文索引。
创建空间索引
首先创建数据表t7：
create table t7(
g geometry not null
)ENGINE=MyISAM;
在表中的 g 字段上创建名称为 spatidx 的空间索引。
create spatial index spatidx on t7(g);
从上述结果可以看出，g 字段上已经创建了一个名称为 spatidx 的空间索引。
使用ALTER TABLE 语句在已经存在的表上创建索引
在已经存在的表上创建索引，除了可以使用CREAET INDEX 语句外，还可以使用ALTER TABLE 语句，基本语法格式如下：
ALTER TABLE 表名 ADD [unique | fulltext | spatial] INDEX 索引名
(字段名 [(长度)] [ASC | DESC]);
示例程序如下：
/* 我们还是使用上述示例刚开始使用的基本表book */
/* 创建普通索引 */
-- 在book表中的bookid字段上建立一个名为index_id的普通索引
alter table book add index index_id(bookid);
/* 创建唯一性索引 */
-- 在book表中的bookid字段上建立一个名为uniqueidx的唯一性索引
alter table book add unque index uniqueid(bookid);
/* 创建单列索引 */
-- 在book表中的comment字段上建立一个名为singleindex的单列索引
alter table book add index singleindex(comment(50));
/* 创建多列索引 */
-- 在book表中的authors和info字段上建立一个名为mulitidx的单列索引
alter table book add index mulitidx(authors(20), info(20));
/* 创建全文索引 */
-- 删除表book，重新创建表book，在表中的info字段上创建全文索引
-- 删除表book
DROP TABLE book;
-- 重新创建表book
create table book(
book int not null,
bookname varchar(255) not null,
authers varchar(255) not null,
info varchar(255) null,
comment varchar(255) null,
publicyear year not null
)ENGINE=MyISAM;
-- 在book表的info字段上创建名为fulltextidx的全文索引
alter table book add fulltext index fulltextidx(info);
/* 创建空间索引 */
-- 创建表t8，在表中的space字段上创建名称为spatidx的空间索引
-- 创建数据表t8
create table t8(
space geometry not null
)ENGINE=MyISAM;
-- 在t8表的g字段上创建名称为spatidx的空间索引
alter table t8 add spatial index spatidx(space);
删除索引
由于索引会占用一定的磁盘空间，因此，为了避免影响数据库的性能，应该及时删除不再使用的索引，删除索引方式有以下两种：
- 使用ALTER TABLE 删除使索引
- 使用DROP INDEX 删除索引
使用ALTER TABLE 删除使索引
基本语法格式如下:
ALTER TABLE 表名 DROP INDEX 索引名
删除book表中名称为 fulltextidx 的全文索引：
ALTER TABLE book DROP INDEX fulltextidx;
从上述结果可以看出，book表中名为 fulltextidx 的全文索引已经被删除了。
使用DROP INDEX 删除索引
基本语法格式如下：
DROP INDEX 索引名 ON 表名
删除t7表中名称为 spatidx 的空间索引：
DROP INDEX spatidx ON t7;
从上述结果可以看出，t7表中名为spatidx 的空间索引已经被删除了。
上一篇： itoa函数 atoi函数
下一篇： 冒泡排序和选择排序的实例、区别、优缺点