Mysql索引面试题

1.索引

1.1 索引原理

        索引(Index)是帮助MySQL高效获取数据的数据结构。用来快速寻找具有特定值，索引的底层使用B+树来实现。未创建索引时，执行MySQL查询时，会从第一个记录开始扫描整个表的所有记录，直到查找到符合要求的记录，表中的记录越多，查询的代价就越大。但是作为搜索条件的列上创建了索引，可迅速得到目标记录所在的位置；大大提高了海量数据的检索速度。

索引虽然能提高查询速度，但同时也有其他影响：

1.1.1 索引带来的影响：

占用磁盘空间

当对表中的数据进行增删改的操作时，除了要维护数据表，还有维护二叉树，对速度会有一定的影响。

创建一个索引并不能解决所有的查询问题，需要分别给字段建立索引，创建索引和维护索引都需要耗费时间，耗费的时间会随着数据量的增加而增加。

索引是以空间来换时间。

不合理的使用索引反而会造成负担。

1.2 常见索引

主键索引（primary key）

唯一索引（unique）

普通索引（index）

全文索引（fullText）

1.2.1 索引的几种类型或分类

从物理结构上可以分为聚集索引和非聚集索引两类：

聚簇索引指索引的键值的逻辑顺序与表中相应行的物理顺序一致，即每张表只能有一个聚簇索引，也就是我们常说的主键索引；

非聚簇索引的逻辑顺序则与数据行的物理顺序不一致。

从应用上可以划分为几类：

主键索引：特殊的唯一索引，也成聚簇索引，不允许有空值，并由数据库帮我们自动创建；

唯一索引：索引列中的值必须是唯一的，但是允许为空值。

普通索引：MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了提高查询效率。

组合索引：组合表中多个字段创建的索引，遵守最左前缀匹配规则；

全文索引：只有在 MyISAM 引擎上才能使用，同时只支持 CHAR、VARCHAR、TEXT 类型字段上使用。

1.3 索引操作

1.3.1创建主键索引

第一种方式：

在创建表的时候，直接在字段名后指定primary key：

create table user1(id int primary key,name varchar(30));

第二种方式：

在创建表的最后，指定某列或者某几列为主键索引：

create table user2(id int ,name varchar(30),primary key(id));

第三种方式：

创建表后再添加主键索引：

create table user3(id int,name varchar(30));

alter table user3 add primary key(id);

主键索引的特点：

一个表中，最多有一个主键索引（可以使用复合主键）

主键索引的效率最高（主键不可重复）

创建主键索引的列，它的值不能为NULL，且不能重复（非空，唯一）

主键索引的列基本上是int类型

1.3.2创建唯一索引

唯一索引的字段为 unique，创建方式与主键索引一致，也有三种方法来创建。

唯一索引的特点：