本文是针对Mysql索引原理剖析的入门级文章，主要围绕以下四个话题展开对索引相关原理的描述。

  • 一丶索引基本概念
  • 二丶(B+)-Tree索引基本实现
  • 三丶关于Mysql索引常见术语解疑(聚族索引，非聚族索引，最左前缀原则, 索引覆盖,哈希索引,自适应哈希索引)
  • 四丶索引局限性
一丶索引基本概念

对照着生活中的概念，数据库索引的概念理解起来比较容易。数据库的索引相当于书籍的目录。

  • 书籍目录： 查阅书籍内容时可对照着目录，直接定位到想查阅内容的具体页数，避免逐页翻书的工作量
  • 数据库索引:某条查询sql语句可以对照数据表中的索引，直接定位到符合查询条件的数据行的物理地址，避免对数据表进行全表扫描的工作。 上述概念关于索引的概念很简单，但是包含了很多的信息量。进一步挖掘如下:
  1. 索引是建立数据表上的，每张数据表都可以有自己的索引。并且数据表中的索引可以有多个，但是不能设置重复的索引。
  2. 索引更具体一点来说，其是建立在数据表的相关列上, 毕竟只有索引建立在列上才能和查询条件相关联嘛。若索引在一个列上建立称为单一索引，若索引在多个列上建立成为复合索引。在表table1中的col1，col2，col3列上建立名为indx1的索引的sql语句如下: create index idx1 on table1(col1,col2,col3) 这里需要特别注意，索引和建立索引时的数据列顺序有关，比如在col3，col2，col1这三个列上建立名为idx2的索引和上述idx1索引是不同的索引。这是一个非常重要的概念
  3. 索引是一个独立于数据表的结构，就像书籍一样，目录和正文分属于不同的部分。数据表的内容发生了改变了，那么索引的结构也会发生相应调整，简而言之，索引的更新和数据表内容的更新保持一致。 该条具体一点就是，在对建立了索引的相关列进行增删改操作时，会附加维护索引结构的相关操作。未建立索引的列则不需要考虑这个性能消耗。
  4. 索引提高查询的性能，索引通过避免全表扫描，减少扫描行的数量来提高查询的性能。
  5. 对于一条简单的查询来说，是如何使用索引查询的呢？ select * from table1 where col1 = A and col2 = B and col3 = C 这条查询就利用idx1这个索引。目前来看，非常简单查询条件和索引列完全一致就可以利用索引完成查询避免全表扫描。此处注意，查询条件的顺序和索引建立是的顺序是一致的，后续的关于索引的最左前缀原则会进一步描述，此处记住是一致的就行 ####二丶(B+)-Tree索引基本实现 (B+)-Tree是一个数据结构，是一个平衡的多路查找二叉树。Mysql innodb引擎中建立的索引默认就是基于(B+)-Tree实现的索引。
  • B-Tree和(B+)-Tree数据结构基本概念 关于B-Tree和(B+)-Tree数据结构具体特性参见该篇文章，本文借用上篇文章中的图来剖析B-Tree是如何构建索引的。 上图就是B+树和B树结构图，非常清晰。B树和B+树之间有两点最大的不同:
  1. B+树的叶子节点存储了所有的数据，非叶子节点中存储的是比较关键字。而B数所有的节点都会存储数据。例如，在B+树中查找数字26的过程是 p1->p3->26,最终在叶子节点里找到了待查找数字26。在B树中查找数字26，查找的顺序是p2->26,在非叶子节点中查找到了数据就返回。
  2. B+树的叶子节点之间存在一个指针连接，B树不存在指针连接。B+树这种设计结构能带来什么好处呢？ B+树所有的数据都存储在叶子节点，那么顺着叶子节点从左往右即可完成对数据的遍历，极大了简化了排序操作。这也是mysql设计索引是采用B+树的原因，不仅仅能方便查找，而且有助于排序，在mysql的索引中叶子节点之间数双向链表可正反遍历，更加灵活
  • B+树和Mysql索引之间关系 介绍关于B+树数据结构的相关内容后，如何将其与索引联系在一起呢？请看下图
三丶关于Mysql常见术语的解疑
1. 聚族索引和非聚族索引

聚族索引和非聚族索引指的是存储结构 Mysql中InnoDB存储引擎是采用聚族索引的存储方式，是在主键上建立的聚族索引，MyISAM则是非聚族索引的方式。下文引用《高性能Mysql第三版167页的一张图解释下聚族索引和非聚族索引》


聚族索引: 数据表和索引文件是存储在一起的，位于同一文件。如图所示，以B+树构建的索引，其叶子节点存储了所有的行信息。数据表中的所有数据全部存储在索引的叶子节点中

非聚族索引: 数据表和索引文件是分开存储的，是两个不同的文件。B+树的叶子节点，并不存储行信息，存储的是数据行的物理地址。

InnoDB存储引擎中非主键索引(二级索引)每个叶子节点除了存储索引字段外，额外存储了主键列。通过二级索引检索数据时，先检索到主键列，最后通过主键列在聚族索引中检索相应的数据行，这是一种二次检索的过程。非聚族索引不存在这种过程


2. 索引覆盖

理解索引的存储结构后，理解索引的覆盖就非常简单了。如果select语句所查询的字段全部都是索引列的话，称为索引覆盖。

  1. 对于聚族索引而言，如果满足索引覆盖，那么不用通过主键访问聚族索引。
  2. 对于非聚族索引而言，如果满足索引覆盖，那么不需要再次访问数据表。 在满足索引覆盖的条件下，select语句从索引文件从就可以拿到所查询的数据，而不必访问数据行。
3. 最左前缀原则

最左前缀原则就是Mysql通过索引检索数据时必须遵守的原则。最左前缀原则的内容规定如下，满足如下情况，将使用索引查询。

  • 全值匹配，select语句中的查询条件(查询字段和字段顺序)和索引完全对应。
  • 匹配最左前缀，select语句中的查询条件并未和索引完全匹配。但是和索引最左侧完全匹配。比如index(col1,col2,col3)，查询条件(col1,col2)或者(col1)都成为匹配索引的最左前缀。
  • 匹配列前缀，这是匹配最左前缀长的特殊情况。查询条件是匹配索引第一列的开头部分。比如 like col1 = aaa%。匹配索引第一列与aaa开头的数据行。
  • 匹配范围，针对索引的第一列，使用了范围查询。比如， col1<A。
  • 精确匹配某一个列，范围匹配某一列。比如col1 =A and col2<B。精确匹配的列必须是索引的最左列。 ######4. 哈希索引 哈希索引不同于以B+树为存储结构的索引。哈希以哈希为存储结构组织索引。 hash索引原理比较简答，通过hash计算hashcode。hashcode = hash(col1,co2,..待索引列)。如果遇见hash冲突的话，可以链地址方法解决冲突。hashcode对应存储的value值是相关行的物理地址。哈希索引想比较于B+树构建的索引，其有如下不同:
  • Hash索引检索数据的速度比B+树索引更快
  • Hash索引值只适用于全值匹配查询，查询条件和索引列必须完全一致。B+树所适应的最左前缀原则Hash索引并不适用
  • Hash索引只能满足精确匹配，比如查询条件是==或者！=。并不能满足范围查询的场景。 ######5. 自适应哈希索引 自适应Hash索引是InnoDB存储引擎添加的一种优化策略。InnoDB存储引擎对那些查询频繁的索引条件，构建一个hash索引。下若有相同的查询语句，则直接命中hash索引，而不必走B+树索引，能提高检索速度。自适应Hash索引是innoDB的一种优化策略，对用户而言是透明的。
四丶索引的局限性

有关索引局限性的讨论是一个比较有难度的话题，其不像原理分析那样固定。其在不同的业务场景下会有不同的结论。本文论文几种索引常见分析，并未涵盖所有情况

  1. 什么情况需要建立索引？
  • 当数据表较小时，维护索引的代价将超过索引加速查询所带来的好处。数据表较大时，索引能够极大加速查询，适合创建索引。
  • 对于那些，查询多于增删改的操作，建立索引是合适的。
  1. 在什么列上建立索引
  • 一般而言，选择在选择性比较高的列上建立索引。
  • 选择性 = 不重复的索引列/所有数据行数。选择性越接近于1越好。
  1. 索引建立的顺序。
  • 受限于索引的最左前缀原则，索引建立的顺序并不能是随意的，应该和查询场景相互印证。让索引顺序能满足最多的查询场景
  1. 多列索引和多个单列索引
  • 一般提供选择建立多列索引，而不建立单例索引。多列索引能覆盖单列索引的查询条件。
  1. 是否针对不同的查询条件，建立不同的索引
  • 索引的建立是有代价的，包括索引存储代价，数据增删改的性能下降。对不同查询条件建立索引，需要仔细考虑。 受限于作者水平，关于索引局限性的讨论，并不一定正确。在不同的场景，具有不同的选择。