聊一聊索引那些事儿

为什么要给表加上主键？

一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐，跟我们平时认知的表很接近。如果给表加上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是平衡树结构，换句话说，整个表就变成了一个索引。没错，整个表变成了一个索引，这就是所谓的聚集索引。这就是为什么一个表只能有一个主键，一个表只能有一个聚集索引，因为主键的作用就是把表的数据格式转换成索引（平衡树）的格式放置。

select * from table where id = 1234;

首先根据索引定位到1234这个值所在的叶节点，然后通过叶节点取到id=1234的数据行。

假如一张表有一亿条数据，需要查找其中某一条数据，按照常规逻辑，一条一条的去匹配的话，最坏的情况下需要匹配一亿次才能得到结果，用大O表示法就是O(n)最坏时间复杂度。如果把这张表转换成平衡树结构，假设这颗树有10层，那么只需要10次IO开销就能查找到所需要的数据，速度以指数级别提升，用大O标记法就是O(log n)。

非聚簇索引

非聚簇索引和聚簇索引一样，同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段，假如给user表的name字段加上索引，那么索引就是name字段中的值构成，在数据该改变时，DBMS需要一直维护索引结构的正确性。如果表中多个字段加上索引，那么就会出现多个独立的索引结构，每个索引（非聚簇索引）互相之间不存在关联。如下图：

每次给字段建一个新索引，字段中的数据就会被复制一份出来，用于生成索引，而通过非聚簇索引可以查到记录对应的主键值，再使用主键的值通过聚簇索引查找到需要的数据，如下图：

不管以任何方式查询表，最终都会利用主键通过聚簇索引来定位到数据，聚簇索引（主键）是通往真实数据所在的唯一路径。

然而，有一种例外可以不使用聚集索引就能查询出所需要的数据，这种非主流的方法称之为“覆盖索引”查询，也就是平时所说的复合索引或者多字段索引查询。

当为字段简历索引以后，字段中的内容会被同步到索引之中，如果一个索引指定为两个字段，那么这两个字段的内容都会被同步至索引之中。

先看看下面这个SQL语句：

//建立索引
create index index_birthday on user_info(birthday);
//查询生日在1991年11月11日出生用户的用户名
select user_name from user_info where birthday = '1991-11-1'

这条SQL语句的执行过程如下：

1. 通过非聚簇索引index_birthday查找birthday等于1991-11-1的所有记录的ID值。
2. 通过得到的主键ID值执行聚簇索引查找，找到主键ID值对应的真实数据（数据行）存储的位置
3. 从得到的真实树中取得user_name字段的值返回，也就是取得最终的结果

我们把birthday字段上的索引改成双字段的覆盖索引

create index index_birthday_and_user_name on user_info(birthday,user_name);

这句SQL语句的执行过程就会变为：
通过非聚簇索引index_birthday_and_user_name查找birthday等于1991-11-1的节点的内容，然而，叶节点中除了有user_name表主键ID的值以外，user_name字段的值也在里面，因此不需要通过主键ID的值去查找数据行的真实存在，直接取得叶节点中user_name的值返回即可。通过这种覆盖索引直接查找的方式，可以省略不使用覆盖索引查找的后面两个步骤，大大提高了查询性能，如下图：