MySQL索引

目录

索引的概念

聚簇索引 VS 非聚簇索引

 创建索引

创建主键索引

创建唯一键索引

 创建普通索引 

创建全文索引

索引的查询与删除

索引创建原则

---

索引的概念

在数据库中添加索引（为表中的数据记录构建特定的数据结构，如B+树），可以极大减少查找的时间，在一定程度上也会降低数据增删改的效率。

索引分为：

• 主键索引（primary key）。
• 唯一索引（unique）。
• 普通索引（index）。
• 全文索引（fulltext）。

操作系统与磁盘交互的基本单位 

操作系统与磁盘进行IO交互的基本单位是4KB，并不是扇区的512字节。

随机访问： 本次IO所给出的扇区地址与上次IO给出的扇区地址不连续，磁头在两次IO操作之间需要做比较大的移动动作才能找到目标扇区进行IO。

连续访问： 本次IO所给出的扇区地址与上次IO给出的扇区地址是连续的，磁头很快就能找到目标扇区进行IO。

而MySQL与磁盘交互的基本单位是16KB，这个基本数据单元在MySQL这里也叫做Page。

 在Mysql进行CRUD操作时，都是要将将数据加载到内存当中，当操作完内存的数据后，再以特定的刷新策略将内存中的数据刷新到磁盘当中。

MySQL服务器在启动时，会先申请一块内存（Buffer Pool），而磁盘中加载的数据就会保存在Buffer Pool中，刷新数据时也就是将Buffer Pool中的数据刷新到磁盘。

但其实内核中是有内核缓冲区的，MySQL从磁盘读取数据时，需要先将数据从磁盘读取到内核缓冲区，再将数据从内核缓冲区读取到Buffer Pool，刷新也是一样的。

操作系统和磁盘交互的基本单位是4KB，就是指内核缓冲区与磁盘之间是以4KB为单位进行交互的。Mysql不是直接与磁盘交互，而是与内核的缓冲区交互，如图：

当我们对数据进行查找时，MySQL直接将这条记录所在的整个Page都加载到内存当中，从而减少了IO的次数。实际对数据做处理时，内存中是有大量的Page，也要将其管理起来。可在page上建立页目录，从而减少查找的时间。

例如：

 这其实就是B+树，InnoDB的索引结构，当创建表时设置了主键，那么MySQL在底层就会自动将这张表中的的数据以B+树的形式组织起来，保存在Buffer Pool当中。

与B树的区别：B树中的节点中都存有数据，棵树形结构一定会变得更高，综合看查找效率会更低，且B+树的叶子节点还以链表的方式连接起来，更有利于进行数据的范围查找。

聚簇索引 VS 非聚簇索引

MyISAM存储引擎 - 主键索引结构

MyISAM存储引擎采用B+树作为索引的基本数据结构。但MyISAM存储引擎的B+树的叶子结点存放的不是数据记录，而是数据记录对应的地址。

下图为MyISAM存储引擎的主键索引结构，Col1为主键。例如：

 MyISAM存储引擎 - 普通索引结构

与主键索引唯一不同的地方就是普通索引的B+树中的键值可以重复。但由于MyISAM存储引擎的B+树叶子结点中，存储的是对应的数据记录的地址，因此有效数据只会存储一份。

InnoDB存储引擎 - 普通索引结构

采用的也是B+树结构，且普通索引的B+树中的键值可以重复，并且B+树的叶子结点中存储的不是数据记录，而是对应数据记录的主键值。

当根据普通索引查询数据时，会先查找普通索引对应的B+树找到目标记录的主键值，然后再查找主键索引对应的B+树找到目标记录，这个过程就叫做回表查询。

下图为InnoDB存储引擎的普通索引结构，其中Col3为索引列：

 补充：

进行普通索引查询数据时，也不一定需要进行回表查询，因为有可能要查询的就是这条记录对应的主键值，因此查询完普通索引对应B+树后也可完成查询

InnoDB存储引擎的普通索引的B+树叶子结点中没有保存整条数据记录，是为了节省空间，因为同一张表可能会创建多个普通索引，每个普通索引的B+树中都保存一份数据会造成数据冗余，所以通过回表查询主键索引对应的B+来获取整个数据记录，该做法本质一种以时间换取空间的做法。

采用InnoDB存储引擎建立的每张表都会有一个主键，就算用户没有设置，InnoDB也会自动帮你创建一个不可见的主键，因为完整数据记录只会存储在主键索引对应的B+树中的，因此采用InnoDB存储引擎建立的表必须有主键。

聚簇索引： 像InnoDB存储引擎这种，将数据记录与索引结构放在一起的索引方案，叫做聚簇索引。

非聚簇索引： 像MyISAM存储引擎这种，将数据记录与索引结构分离的索引方案，叫做非聚簇索引。

 创建索引

创建主键索引

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

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

创建表以后再添加主键

 主键索引特点：

一个表中，最多有一个主键索引，一个主键可以由多个列同时承担

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

创建主键索引的列，它的值不能为 null ，且不能重复

主键索引的列基本上是 int

创建唯一键索引

与创建主键索引类似，将primary key 替换为 unique即可。

唯一键索引特点：

一个表中，可以有多个唯一索引，一个唯一键可以由多个列同时承担。

唯一索引的查询效率高。

创建唯一索引的列，其列值可以为NULL，但是不能重复。

如果给唯一索引设置NOT NULL属性，则等价于主键索引。

 创建普通索引 

在创建表的最后，指定某列或某几列为普通索引。

 使用alter命令给指定字段添加普通索引

 创建表后，使用create命令给指定字段创建普通索引，并指定索引名

- 创建一个索引名为 idx_name 的索引

 普通索引的特点：

一个表中，可以有多个普通索引，一个普通索引可以由多个列同时承担。

创建普通索引的列，其列值可以为NULL，也可以重复。

创建全文索引

MySQL 提供全文索引机制，但要求表的存储引擎必须是MyISAM ，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx 的中文版 (coreseek) 。

创建表的最后通过fulltext给列创建全文索引

再向表中插入一些数据

 对数据进行查找：

 但此时并没有用到全文索引，在SQL语句前面加上explain可以查看：key对应值为NULL

 要通过全文索引来查询，需要使用match against进行搜索，例如：

 

补充：

MyISAM存储引擎是支持全文索引的，而InnoDB存储引擎是在5.6以后才开始支持全文索引。
如果使用title和body建立全文索引时，会建立一个以fulltext中第一个为列名的复合索引

索引的查询与删除

第一种方法： show keys from 表名

show index from 表名

desc 表名

补充：

Column_name： 表示定义索引的列字段。

Key_name： 表示索引的名称。

Table： 表示创建索引的表的名称。

Non_unique： 表示该索引是否是唯一索引，如果是则为0，如果不是则为1。

 删除主键索引：alter table 表名 drop primary key

删除非主键索引：alter table 表名 drop index 索引名，或drop index 索引名 on 表名

主键索引只有一个，所以删除时不用指定，非主键索引需要指定索引名。

索引创建原则

比较频繁作为查询条件的字段应该创建索引。

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

更新非常频繁的字段不适合创建索引。

不会出现在where子句中的字段不应该创建索引。