【MySQL进阶之路 | 高级篇】索引的声明与使用

1. 索引的分类

MySQL的索引包括普通索引，唯一性索引，全文索引，单列索引和空间索引.

• 从功能逻辑上说，索引主要分为普通索引，唯一索引，主键索引和全文索引.
• 按物理实现方式，索引可以分为聚簇索引和二级索引.
• 按作用字段个数进行划分，分为单列索引和联合索引.

(1). 普通索引

在创建普通索引时，不附加任何限制条件，只是用于提高查询速度.这类索引可以创建在任何数据类型上，其值是否唯一或非空，要由字段本身的完整性约束条件决定.建立索引后，可以通过索引进行查询.

(2). 唯一索引

使用UNIQUE参数可以设置唯一索引，在建立唯一索引时，限制该索引的值必须是唯一的.但允许是空值.在一张表里可以有多个唯一索引.

(3). 主键索引

主键索引是一种特殊的唯一性索引.在唯一约束的基础上添加了非空的约束，一张表里只能有一个主键索引.这是由主键索引的物理实现方式决定的.因为数据存储在文件中只能按照一种顺序进行存储.

(4). 联合索引

联合索引是在表的多个字段上创建一个索引，该索引指向创建的多个字段.可以通过几个字段进行查询，但查询条件中使用这些字段的第一个字段时才会被使用.使用联合索引时遵循最左前缀原则.

(5). 全文索引

使用参数FULLTEXT可以设置索引为全文索引.在定义索引的列上支持值的全文查找，允许在这些索引列中插入空值和重复值.全文索引只能创建在CHAR, VARCHAR, TEXT类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度.

(6). 小结

• InnoDB : 支持b+树，full-text等索引，不支持hash索引.
• MyISAM : 支持b+树，full-text等索引，不支持hash索引.
• Memory : 支持b+树，hash索引等，不支持b+树.

2. 创建索引

MySQL支持多种方法在单个或多个列上创建索引，在创建表的定义语句中CREATE TABLE中指定索引列，使用ALTER TABLE语句在已经创建的表中创建索引，或者使用CREATE INDEX语句在已经存在的表中添加索引.

(1). 创建表时添加索引

CREATE TABLE demo1(
demo1_id INT PRIMARY KEY AUTO_INCREMENT,
demo_fname VARCHAR(15) UNIQUE KEY,
demp_lname VARCHAR(20),
INDEX demo1_index_lname(demp_lname)
);

• 隐式的方式创建索引.在声明有主键约束，唯一性约束，外键约束的字段上，会自动的添加相关的索引.
• 显式的方式创建普通索引.
• 我们可以通过show index from 表名的方式来查看表中的索引.
• 我们只能在创建表时通过定义主键约束的方式隐式定义主键约束.

(2). 创建表后添加索引

1). ALTER TABLE ... ADD...

CREATE TABLE demo2(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
books VARCHAR(20)
);ALTER TABLE demo2 ADD INDEX demo2_index_books(books);ALTER TABLE demo2 ADD UNIQUE KEY demo2_uq_name(name);show index from demo2;

2). CREATE INDEX ... ON ...

CREATE TABLE demo3(
id INT PRIMARY KEY,
fname VARCHAR(20),
lname VARCHAR(20)
);CREATE INDEX demo3_index_fname ON demo3(fname);CREATE UNIQUE INDEX demo3_uq_lname ON demo3(lname);show index from demo3;

3. 删除索引

(1). 方式1 : ALTER TABLE ... DROP

ALTER TABLE demo3 DROP INDEX demo3_index_fname;show index from demo3;

(2). 方式2 : DROP INDEX ... ON ...

DROP INDEX demo3_uq_lname ON demo3;

注意 : 添加了AUTO_INCREMENT自增约束的列的索引是不可以被删除的.因为自增约束依赖于主键约束或唯一键约束.删除了列的索引，其约束也跟着被删除了.AUTO_INCREMENT失去了依赖的主键/唯一键约束，即会报错.