每日一句

不要停顿,因为别人会超过你;不要返顾,以免摔倒。 ——阿·雷哈尼

前言概述

索引就如同一本书一样，当用户希望检索海量数据中自己想要的数据信息的时候，例如：用户去查一些生僻的字的时候，最好的办法就是通过字典索引目录，去筛选范围最后定位到了我们想要的数据所在的页数。这样可以大大的提高我们的查询检索速度，所以，使用索引可以大大的提高数据库的检索数据的性能和速度。

正文介绍

如果说上面的前言概述，说的还是过于抽象的化以及不够深入的化，那么就让我们一起迈向MySQL的Innodb存储引擎的领域吧！想跟大家说明的是针对于SQL Server、Oracle、DB2、或者MySQL的MySiam存储引擎是不一定成立的，请大家不要混为一谈。

索引的介绍

众所周知，MySQL数据库的索引从物理结构上区分，主要分为聚集索引和非聚集索引。在平时使用的的MySQL数据库中，创建索引的语句：

create [unique|fulltext|spatial] index index_name
[using index_type]
on table_name(colum [asc/desc]);
复制代码

创建索引

聚集索引

聚集索引一般不用我们专门的语句去做另外的生成，在Innodb存储引擎中，MySQL中的数据是按照主键顺序，那么聚集索引就是按照每张表的主键来构造一个B+树，因此每张表只能有一个聚集索引哦。此外聚集索引存储了行的数据信息，也就是聚集索引其本身就是一个数据，每一个B+树的叶子节点都是一行数据信息，此外数据的排序顺序也是按照聚集索引的顺序进行排序。

一般情况下，聚集索引默认就是主键索引，

非聚集索引

• 复合索引：多个字段进行共同建立负责索引的功能。
• 前缀索引：最左匹配原则，以最左边的元素建立索引。
• 唯一索引：每个索引元素都是唯一的索引。

上面这几种索引类型，一般叫做辅助索引(二级索引)，在这里我们叫做非聚集索引，它们的底层数据结构为B+树。

如果我的表没有建立主键该怎么办呢？

每一个行数据都会存在隐藏字段，比如：ROW_ID、DELETE_SIT,会用ROW_ID来作为聚集索引，但是也不会立刻就会用，还会存在判断和其他字段进行控制。

1. 如果没有主键，就会用一个唯一索引且非空字段进行作为主键，也就是聚集索引；
2. 如果也没有上述的字段及索引的话，就会用上述描述的隐藏字段:ROW_ID作为聚集索引；

注意：针对于自增主键和uuid作为主键的区别，由于主键使用的是聚集索引，因为聚集索引是有序的，如果主键属于自增的ID的话，那么存储的位置肯定是相邻的磁盘位置，这样子的话写入性能很好，但是如果是UUID的话，如果频繁插入的话，就会存在频繁的移动寻址到不同的磁盘快，所以写入性能会很差！。

索引原理

先来了一张主键的表，如下所示，pid是主键

如图所示：分为上下两个部分，上部分为主键索引B+树，下半部分就是磁盘上而是的真是数据，当然执行下面的语句：

select * from table where pid = "11";
复制代码

那么执行过程：

如上图所示：从根节点开始，需要查找三次树操作可以定位到对应的实际数据信息。但是如果不使用索引，那就要在磁盘上，进行逐行扫描，直到找到数据位置。

所以可以看出来，如果速度非常快，但是如果存在着操作数据比如：新增数据、修改数据、删除数据。这种情况需要进行维护相关的索引数据对象，因此我们引入了非聚集索引。

create index table_index on table(name);
复制代码

此时结构图如下所示：

• 可以看到存在了两个B+树的索引，采用了table的name建立了一个新的B+树非聚集索引，因此如果我们每增加一个索引，就会增加表的体积，占用磁盘存储空间。

• 但是请注意非聚集索引的叶子节点，存储的不是完整的数据行，而只是存在了聚集索引的值（主键->属于聚集索引的值）+ 索引字段的数据值。

select * from table where name = "list1";
复制代码

从上面可以看出，首先检索从非聚集索引树开始查询，然后找到了聚簇索引，然后在从聚集索引上找到整体的完整的数据行。

什么情况不去聚集索引树去查询？

如果我们采用了以下的SQL语句，与我上面的SQL语句比较，属于返回name字段。

select name from table where name = "list1";
复制代码

如果上图红线所示，如果在非聚集索引树上找到了想要的数据，如果是这样子：属于覆盖所索引机制，就不会聚集索引树上去查询其他所有的数据信息。

所以hi到在聚集索引的情况下：select col > select * 的速度要快很多，也是因为覆盖索引提高了很高的性能哦，当然不仅于此，解析 * 也会消耗很多性能。

create index table_index on table(birthday);
复制代码

如果执行完上述的SQL以后会创建一个新的索引树。

检索方式及就是会同时去检索同时根据条件去检索这两个非聚集索引，然后在转到聚集索引上去查询所有的数据行。

注意：而且需要考虑的就是不能乱加索引哦，因为每加一个就需要多建立一个索引树，过多的索引树，再维护数据的时候就会越加复杂，会导致操作数据性能大大下降。