目录

存储引擎是MyISAM

非聚簇索引

主键索引：

 普通(辅助)索引：

存储引擎是InnoDB

聚簇索引

主键索引：

普通(辅助)索引：

回表查询

创建索引

创建主键索引

主键索引的特点：

创建唯一索引

唯一索引的特点：

创建普通索引

查询索引

删除索引

删除主键索引

删除其他索引(普通索引、唯一键索引)

---

存储引擎是MyISAM

非聚簇索引

MyISAM存储引擎采用B+树作为索引结构，B+树的叶子节点中存储的是主键值或普通键值对应的用户数据的地址。

可以同通过以MyISAM为存储引擎的表的文件构成来验证非聚簇索引的索引数据和用户数据是分开的：

在数据库test1_db中创建一个table：mtest

然后查看test1_db中的mtest表文件：

mtest.MYD就是表mtest的数据，mtest.MYI就是表mtest的索引数据，可以看到这两个文件是分开的。

这种用户的数据与索引数据分开的索引就是非聚簇索引。

主键索引：

 普通(辅助)索引：

采用MyISAM存储引擎表的普通索引和主键索引没有太大的区别，只不过就是普通索引的索引值可以重复，主键索引值则不能重复。

存储引擎是InnoDB

聚簇索引

InnoDB存储引擎采用B+树作为索引结构，B+树的叶子节点中存储的是主键值和对应的用户数据。

同样在数据库test1_db中创建一个table：itest

查看test1_db中的itest表文件：

itest.ibd是itest表的数据和索引数据。

这种用户数据和索引数据在一起的索引就是聚簇索引。

主键索引：

普通(辅助)索引：

回表查询

采用InnoDB存储引擎的表的非主键索引即普通索引的索引结构如上图，可以看到，叶子节点存储的是索引数据对应得主键值，而不是用户数据。使用普通索引查询数据时，要进行两次索引查找，第一次是先通过普通索引找到要查询数据记录得主键值，然后再依据主键值通过主键索引进行查找用户数据，这种查询方式就是回表查询。

为什么InnoDB的普通索引结构的叶子节点中存储的不是用户数据而是主键值？

因为主键索引结构的叶子节点已经存储了用户数据，建立索引结构也是需要空间的，如果普通索引的叶子节点也存储了用户数据那么就重复存储了，造成不必要的空间浪费。因此这种做法是为了节省空间。

创建索引

创建主键索引

1.创建表时直接在字段名后加primary key

create table test1(id int primary key,name varchar(10))

2.创建表时在表的最后指定某字段

create table test1(id int,name varchar(10),primary key(id));

3. 表创建后添加

alter table test1 add primary key(id);

主键索引的特点：

1.一个表中只能有一个主键索引，可以使用复合主键

2.查找效率高

3.主键列的值不能为null，且不能重复

4.主键索引的列基本是int

创建唯一索引

常见唯一索引和创建主键索引写法一样，将primary key换成unique即可。

唯一索引的特点：

1.一个表中可以有多个唯一索引

2.查询效率高

3.唯一索引列不能有重复值。

创建普通索引

1.

mysql> create table test4(
    -> id int,
    -> email varchar(30),
    -> index(email)
    -> );

2.

alter table test4 add index(email);

3.创建一个索引名为idx_name的索引

create index idx_name on test4(email);

查询索引

show keys from 表名 \G;

我们创建一个含有主键索引、唯一键索引、普通索引的表：

mysql> create table test_db(
    -> id int unsigned primary key,
    -> tel_number char(11) unique,
    -> name varchar(10) not null,
    -> index(name)
    -> );

查看表test_db的结构：

PRI：主键约束。UNI：唯一键约束。MUL：值可以重复。

查看表test_db的索引：

 

删除索引

删除主键索引

alter table 表名 drop primary key;

删除其他索引(普通索引、唯一键索引)

1.

alter table 表名 drop index 索引名;

这里的索引名就是show keys from 表明中的Key_name。

 2.

drop index 字段名 on 表名;

 创建索引的原则

1.更新频繁的字段不适合创建索引。

2.不会在where子句后出现的字段不适合创建索引。

3.唯一性太差的字段不适合创建索引，即使频繁作为查询条件。