2025年mysql主键和索引（mysql主键索引和聚集索引）

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

从功能逻辑按照上说，索引主要有 4 种：普通索引、唯一索引、主键索引、全文索引。
按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。
按照作用字段个数进行划分，分成单列索引和联合索引。

注意：不同的存储引擎支持的索引类型也不一样

InnoDB：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM：支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory：支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive：不支持 B-tree、Hash、Full-text 等索引;

1、创建表的时候创建索引

隐式的索引创建：

显式的索引创建：

UNIQUE、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引;
INDEX与KEY 为同义词，两者的作用相同，用来指定创建索引;
index_name 指定索引的名称，为可选参数，如果不指定，那么 MySQL 默认 col_name 为索引名;
col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择;
length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度;
ASC 或 DESC 指定升序或者降序的索引值存储。
特例：主键索引使用主键约束的方式来创建。

1、创建普通索引

在book表中的year_publication字段上建立普通索引，SQL语句如下:

通过命令查看索引有没有创建成功

EXPLAIN语句输出结果的各个行我们在下一章讲解，这里主要关注两个字段

possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引
key行时MySQL实际选用的索引

可以看到，possible_keys和key值都为idx_bname，查询时使用了索引

2、创建唯一索引

3、主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引，语法:

随表一起建索引:

删除主键索引:

修改主键索引：必须先删除掉（drop）原索引，再新建（add）索引

4、创建单列索引

5、创建组合索引

举例：创建表 book4，在表中的 book_id、book_name和 info字段上建立组合索引，SQL 语句如下：

注意上面三行依次是book_id,book_name,info，与我们创建索引时指定的顺序是严格对应的。在查询时会遵守最左索引原则，先进行book_id条件的比较，然后再进行book_name比较，最后才是info。因此注意把最常用的查询字段放在索引的最左边。

6、创建全文索引

FULLTEXT全文索引可以用于全文搜索，并且只为、和列创建索引。索引总是对整个列进行，不支持局部（前缀）索引。

举例1：创建表test4，在表中的 info 字段上建立全文索引，SQL 语句如下:

在 MySQL 5.7 及之后版本中可以不指定最后的 ENGINE 了，因为在此版本中 InnoDB 支持全文索引。

语句执行完毕后，用SHOW CREATE TABLE查看表结构：

由结果可以看到，info字段上已经成功建立了一个名为futxt_idx_info的FULLTEXT索引。

举例2：

创建了一个给 title 和 body 字段添加全文索引的表。

举例3：

不同于 like 方式的的查询:

全文索引用 match+against 方式查询:

明显的提高查询效率

注意点

使用全文索引前，搞清楚版本支持情况；
全文索引比like + % 快 N倍，但是可能存在精度问题
如果需要全文索引的是大量数据，建议先添加数据，再创建索引。

2、在已经存在的表上创建索引

在已经存在的表中创建索引可以使用 ALTER TABLE 语句或者 CREATE INDEX 语句。

1. 使用 ALTER TABLE 语句创建索引

2. 使用 CREATE INDEX 创建索引

CREATE INDEX 语句可以在已经存在的表上添加索引，在 MySQL 中， CREATE INDEX 被映射到一个 ALTER TABLE 语句上，基本语法结构为：

举例1：在book表的comment字段上建立名为的普通索引

举例2：在book表中的book_id字段上建立名为uk_idx_bid的唯一索引，SQL语句如下：

举例3：在book表的book_id、book_name、info字段上建立联合索引，SQL语句如下：

MySQL中删除索引使用ALTER TABLE或DROP INDEX语句，两者可实现相同的功能，DROP INDEX语句在内部被映射到一个ALTER TABLE语句中

1. 使用 ALTER TABLE 删除索引：

ALTER TABLE删除索引的基本语法格式如下：

练习：删除book表中名称为idx_bk_id的唯一索引

首先查看book表中是否名称为idx_bk_id的索引，输入SHOW语句如下：

下面删除该索引，输入删除语句如下：

提示

添加AUTO_INCREMENT约束字段的唯一索引不能被删除（）

2. 使用 DROP INDEX 语句删除索引：

DROP INDEX删除索引的基本语法格式如下：

练习：删除book表中名称为idx_aut_info的组合索引，SQL语句如下：

语句执行完毕，使用SHOW查看索引是否删除：

可以看到，book表中已经没有名称为idx_aut_info的组合索引，删除索引成功。

提示删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。