MySQL扫盲

大家可能都知道MySQL中可以通过加索引来提高查询速率，最近各种名字的索引把张三搞得眼花缭乱，主键索引、B树索引、B+树索引、哈希索引、聚簇索引、非聚簇索引、覆盖索引balabala，我们今天就一起来复习一下MySQL中的索引

索引是什么？

MySQL官方文档中指出，索引是加快查询速度的数据结构，根据这句话我们不难看出来，索引就是一种数据结构。不同的索引在不同的数据库引擎中会有差别，我们今天以InnoDB中的索引来进行说明

哈希索引

哈希索引的底层是哈希表，通过对查询值进行哈希值计算，找到其在哈希表中的位置，最优情况下通过哈希算法查询的时间复杂度为O(1)，但是在最差情况下，也就是哈希冲突极其严重的时候，其性能会退化到O(n)，此时哈希索引的效率就会大打折扣，这是哈希索引在实际中使用会比较少的原因之一，还有一点原因是因为哈希索引不支持范围查询，范围查询这里我们先留个伏笔，下文会再做讲解。

B树索引

B树是数据结构中一种比较难的数据结构，他的中文名字叫做多路平衡查找树，B树中每一个内部节点会包含一定数量的键(图中绿色方块，除了图中数字，里面还包括一些别的数据列数据)，键将节点的子树分开，我们先来看一下B树索引在MySQL中长什么样子吧

在图中我画出了一个2-3树的结构图，其中每个节点都包含k个值和k+1个指针(叶子节点的指针没有画)。

我们来看一下查找49这个数据的过程

首先根据根节点指针找到根节点所在磁盘块（图中黑框代表一个磁盘块）
根节点中存储50这个数据，我们发现49<50因此我们去指针P1指向的地址进行第二次读取
根据P1指针我们找到了图中第二层中第一个块的地址，我们发现49>41，因此我们再根据该磁盘块中P3指针指向的地址找到了叶子节点代表的磁盘块，对该块进行读取，找到我们目标值

由于根节点指针是常驻内存的，所以上述查找过程一共经过了三次I/O操作

在B树索引中，所有节点中会存储数据值。这样会导致在块大小固定的时候，块中可以存储的指针数量会相对来说少一些，那么在数据量过大的时候会增加树的整体高度，导致更多的的I/O次数，从而降低效率。

那么如何进行优化呢？这就需要B+树了

B+树

B+树在B树的基础上做了一下优化，使得它可以更好的用于查询。在B+树中，所有数据只存储于叶子节点(图中绿色框),非叶子节点只存储指针和一些索引值，由于不需要存储额外信息，非叶子节点中可以有更多空间来存储指针信息(这里与刚刚B树相比多了一个指针)，这样大大增加了非叶子节点内索引值和指针的数量，降低树的整体高度，由此减少了每次查询所需要的I/O次数，保证了查询的效率。

这里以@三太子敖丙文章中的一段来说明B+树的优势

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。

也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录。（这种计算方式存在误差，而且没有计算叶子节点，如果计算叶子节点其实是深度为4了）

原文链接mp.weixin.qq.com/s/qESZSzHox…

所以我们只需要三次I/O操作就可以10亿条数据中我们想要的值，保证了查询过程的效率。

细心的同学可能发现了，除了非叶子节点不存储数据，B+树还有一点不同，那就是B+树的叶子节点还维护了一个双向指针，这个双向指针可以实现在叶子节点上不需要父节点就可以进行遍历，试想一下如果没有这个双向指针，我们进行叶子节点遍历必须依赖父节点才能确定下一个叶子节点的位置。由此我们可以通过这个双向指针，可以高效进行范围查询，比如我们要查询30到78之间所有的数据，我们可以先找到第一个大于等于30的数据，然后根据指针向后遍历，找到第一个大于78的数据就停止。

范围查找也是B树索引和哈希索引无法解决的问题，B树索引是因为需要回溯，而在哈希索引中，每个索引值都会被哈希算法分配到哈希表不同位置上，无法进行有效的范围查询。

以上三种索引是真真正正符合官方定义的索引结构，我们平常还会接触到一些例如唯一索引、覆盖索引的名词，虽然他们叫做索引，但是不是官方定义的那种数据结构类型索引，下面我们一起来看吧

聚簇索引

聚簇索引是指，叶子节点中包含了索引列信息和数据库表中其他所有列信息，找到索引也就找到了数据，在我们上面给出的B+树的示意图，就是一个聚簇索引。聚簇索引具有唯一性，由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引

聚簇索引的优势：

由于行数据存储在叶子节点上，同一个物理块中会存储多条行信息，当读取一次块信息时，整个块内容会被加载到内存中，在第二次再查询同一个目标块内容时，就不需要再访问磁盘，加快查询速度
由于所有的数据行都会存储在叶子节点，因此不需要回表进行二次查找
适合排序场景

非聚簇索引

看完了聚簇索引，那么非聚簇索引就很好理解了，非聚簇索引就是叶子节点只存储主键值，不存其他列信息。非聚簇索引一般用来做辅助索引，通过辅助索引找到目标值的主键值，然后再回聚簇索引进行查询

我们以上图为例，我们有四条数据的表，然后右侧是一个根据name值构成的辅助索引，其叶子节点的信息为(name,id),其中id是表的主键信息，左侧是一个聚簇索引，索引值是主键id。此时我们要查询name为王五的company信息，由于聚簇索引的索引列为id，无法直接使用索引，因此我们先通过辅助索引，找到name为王五的叶子节点，查询到王五这条记录对应的id，然后我们根据id再去聚簇索引，找到叶子节点中company信息。

覆盖索引

覆盖索引是指，当我们需要返回的列已经存在于索引值中，那么我们在根据索引进行查询的过程中，找到符合条件的叶子节点时，就不需要再根据主键值回到表中进行查询，因为索引列就包含了我们需要返回的数据，符合条件的我们直接给他返回就好了。

比如我们以还以刚刚非聚簇索引的表作为例子

假设我们现在根据(name,age)作为我们的组合索引(组合索引后面会讲),我们通过SELECR age FROM table WHERE name = '张三'，由于我们的组合索引列包含了name和age字段，并且这里符合最左匹配原则(后面也会讲！),那么我们使用索引列找到符合要求的行就返回索引列中对应的age字段。

组合索引

指多个字段上创建的索引，需要符合最左前缀规则才能使用组合索引

CREATE INDEX index_name ON table_name(cloume1,colume2)

最左前缀匹配原则

最左匹配原则是使用组合索引的一个非常重要的原则，当WHERE条件中的列与组合索引中的列一一对应时，我们可以使用当前匹配的列作为索引前缀查询，如果不匹配或WHERE条件列为范围查询，则后续索引列无效

我们来看一个栗子，假设我们当前有一个组合索引(a.b,c,d,e)

如果我们有条件 WHERE a = 1 AND b=2 AND c= 3 AND d = 4 AND e=5

此时条件列与索引列全部匹配，可以使用(a.b,c,d,e)作为索引进行查询

如果我们有条件 WHERE a = 1 AND b=2 AND c= 3 AND d = 4 AND e>5

此时条件列与索引列全部匹配，可以使用(a.b,c,d,e)作为索引进行查询

如果我们有条件 WHERE a = 1 AND b=2 AND c= 3 AND d >4 AND e>5

此时,a,b,c与索引列匹配，d这里用了范围查询，d的这个范围查询可以生效，但是e就无法使用索引列，因此这个时候使用的索引就是(a,b,c,d)

如果我们有条件 WHERE a = 1 AND b=2 AND c> 3 AND d >4 AND e>5

根据最左匹配原则，只有第一个范围查询可以生效，后续的范围查询列无法作为索引列，此时的索引列就是(a,b,c)

如果我们有条件 WHERE a = 1 AND b=2 AND c= 3 AND d >4 AND e=5

此时,a,b,c与索引列匹配，d这里用了范围查询，d的这个范围查询可以生效，但是e就无法使用索引列，因为范围查询后的列都无法作为索引列，因此这个时候使用的索引就是(a,b,c,d)

如果我们有条件 WHERE a = 1 AND b=2 AND c+1=3 AND d >4 AND e>5

此时我们条件中有一个非等值查询c+1=3，虽然这个表达式在逻辑上等同于c=2，但是MySQL无法把这种显而易见的数学公式转化为等值查询，此时c这一列不会作为索引列，此时用到的索引就是(a,b)

如果我们有条件 WHERE a = 1 AND b=2 AND d =4 AND e=5

由于条件列不存在c这一列，因此只能使用已经匹配的a,b作为索引列

普通索引

是最基本的索引，它没有任何限制。它有以下几种创建方式：

（1）直接创建索引

CREATE INDEX index_name ON table_name(colume_of_index)

（2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name (column_name)

（3）创建表的时候同时创建索引

CREATE TABLE table_test(
	id int(11) NOT NULL AUTO_INCREMENT,
    title char(255),
    time int(10) ,
	PRIMARY KEY  (id)
)

唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：