重学Mysql（2）——关于索引，你需要知道的

索引的存在是为了加速查询过程，是数据库中最重要的组件之一，也是我们最常接触的数据库结构，学习索引可以说对我们能力的提升是立竿见影的。索引为什么可以加速查询，原理大家应该都知道，简单的说就是目录对于书的作用。

学习一个概念最快的方式是了解其数据结构，而了解一个数据结构最快的方式是对比其他数据结构。这里列举三种比较常见的索引数据结构：

• 有序数组：就是一个排好序的数组，应该也是我们最熟悉的数据结构之一，查找时用二分法，时间复杂度为O(logn)，同时支持范围查询，是一个非常适用于查询场景的数据结构。但缺点也很明显，为了保持其元素有序性和连续性，在插入或删除数据时都要移动大量的数据，成本非常高。因此，有序数组的索引只适用于静态数据的索引，也就是数据几乎不会变化。
• 哈希表：根据散列函数构造的一个自动扩容的无序数组，是一个查找，插入和删除效率都非常高的动态数据结构（相对于静态的有序数组），平均时间复杂度都在O(1)。缺点：只支持等值查询，范围查询的效率很低（相当于全表扫描）。在特定场景下使用。
• B+树：一棵多路平衡查找树，数据只存在于叶子节点，非叶子节点保存的是关键字。优点：快速的查询，插入和删除，支持范围查询，最重要的是符合磁盘存取的特点，所以是数据库系统实现索引的首选结构。

由上可知mysq选择B+树来作为索引结构的原因了。下图是mysql中各种存储引擎支持的索引类型

这张图来自mysql官方文档，关于索引更详细的信息可以参考此处。

下文我们主要针对innodb的索引实现进行分析，因为最常用

innodb的索引基于B+树实现，可以分为两大类：聚簇索引（主键索引）和非聚簇索引（普通索引，非主键索引，二级索引）。聚簇索引会在叶子节点保存整行信息，非叶子节点保存主键值，而非聚簇节点在非叶子节点保存相关的索引值，叶子节点只保存索引值和对应主键值，根据主键值可以定位到相应的行数据。两者的区别从下图中可以看出

实际上innodb表又称为索引组织表，innodb的数据都是以聚簇索引的形式保存的，也就是说聚簇索引就是表本身，一张表就是一个聚簇索引（所以一张表包含一个聚簇索引，多个普通索引），其他的辅助索引都是非聚簇索引，用以满足除主键查询以外的各种条件查询。

话说回来，聚簇索引和非聚簇索引叶子节点保存的数据不一样，使用上会有什么区别吗
假设有一个innodb表test，有id，a，b，c字段，主键是id，在a上有一个普通索引

• 当我们执行select * from test where id = 1时，只需搜索一次主键索引就可以获得返回数据
• 当我们执行select * from test where a = 1时，要先搜索a索引得到对应的主键值，然后再拿主键值去主键索引搜索得到对应的行数据，返回主键索引搜索数据的过程称之为回表

可以看出基于普通索引的查询要比基于主键索引的查询多一次回表的过程，这也是他们的主要使用区别。

插播一个小知识：联合索引。联合索引也是普通索引的一种，只不过索引值是由多个字段组成，其余和普通索引没有任何区别，联合索引的排序可以认为是先按照第一个字段排序，第一个字段值相同的则按第二个字段排序，依次类推。

知道了两种索引的特点和使用区别后，该来说一说索引优化的问题了，也是我们平时最关心的问题。innodb内部对索引进行了大量优化，我们只需要了解innodb的优化规则，便可根据它的规则设计出性能良好的索引来。接下来我们来列举几项非常关键的规则

• 覆盖索引——消除回表：当查询条件用到了普通索引或联合索引，并且返回列都包含在普通索引或联合索引中，那么就会在索引中直接取值，不用回表。还是以刚才的例子举例：
  • 当我们执行select id from test where a = 1时，因为a索引中本身就包含id值，根据覆盖索引父子因此就不用再回表了
  • 根据a,b新建一个联合索引ab，执行select a,b,id from test where a > 1 and b > 1，a,b,id都在联合索引ab中，也不需要回表了
• 最左前缀——减少索引数量，扩展索引使用范围：当查询条件使用到了联合索引的前N个字段，或者字符串索引的前N个字符，都会使用索引去加速查询，举例：
  • 先讲联合索引的前N个字段。执行select * from test where a > 1，这个sql显然会使用之前的索引a去加速查询，但如果我们把索引a删掉呢，根据最左前缀规则，字段a是联合索引ab的前1个字段，因此就会使用联合索引ab去加速查询，且速度和索引a是相同的，可以看出这样就节省了索引a所占的空间。但如果执行执行select * from test where b > 1呢，这时候就无法使用联合索引ab了，因为b不是最左前缀（其实如果理解了联合索引的排序规则的话，最左前缀规则就是非常好理解甚至是自然的了，不用死记硬背）
  • 然后是字符串索引的前N个字符。这个规则其实很好理解，小时候我们用厚厚的英文字典书查单词的时候，用的就是字符串索引的最左前缀规则：目录就是字符串索引，根据第一个字母定位到相应的页数，然后往后遍历查找目标单词。其实也可以把字符串索引看成联合索引，字符串中的一个字符就是一个索引字段，前N个字符就是前N个字段，这样理解的话就简单多了。
• 索引下推——减少回表：当查询条件用到了联合索引但是索引没有包含全部的查询条件时，会优先判断联合索引包含的字段，用满足联合索引条件的主键值回表，再判断不在索引中的条件，这种优化应该是非常自然的，能有效的减少回表次数。例如：
  • 执行select * from test where a>1 and b > 1 and c >1，我们当前只有联合索引ab，c不在联合索引中，根据索引下推规则，会先搜索联合索引ab，找出符合a>1 and b > 1的主键值，然后再回表去搜索c>1的数据。如果是先搜索c>1的数据，那就要到主键索引中进行全表扫描了，成本就高得多。

了解了优化规则，该如何学以致用呢

• 减少返回字段，只返回必要的字段，以尽量用上覆盖索引
• 利用最左前缀原则，最大化的使用联合索引，这同时也能增加覆盖索引的使用几率。创建联合索引时需要有以下一些考量
  • 时间：查找频繁的字段建立联合索引并且尽量靠左
  • 空间：数据长度大的字段尽量靠左。因为有时候我们需要单独使用靠右的字段进行查询，这时候就不能利用最左前缀规则，而要单独创建索引，既然要创建，就尽量创建占用空间小的索引。

最后
如果说ACID保证数据库的正确和可靠，那么索引就保证数据库的性能和速度，作为程序员来说索引是更常接触的概念，我们的程序要能跑，还要能跑得快，如何跑得快，索引便是一大关键。而且在我看来，多去分析底层数据结构的优劣，多去考虑算法的平衡，对于我们自身成长地促进，要远比学习数据库的收获要大得多。