Mysql InnoDB索引原理

理解Mysql索引的原理和数据结构有助于我们更好的使用索引以及进行SQL优化，索引是在存储引擎层面实现的，所以不同的引擎实现的索引也有一定的区别，但是在生产环境中，我们最常用的就是InnoDB引擎和B树索引，OK，那本文要讨论的重点也同样是 InnoDB引擎下的B树索引 。

我们建立一个表来进行测试，表的DDL如下所示，我们要关注的是表t_book上的主键索引id和name author publish_date三列组成的索引test_index。

Mysql中的B树索引是使用B+树实现的，关于B+树的数据结构个人认为美团点评技术博客中Mysql索引原理及慢查询优化一文中介绍的非常详实，B+树的数据结构如下图所示。

图中浅蓝色块即磁盘块，根节点磁盘块中存储17和35两个数据，其中指针P1指向小于17的数据，指针P2指向大于17小于35的数据，指针P3指向大于35的数据。显然通过B+树索引查询数据与B+树的高度有关，如上图的B+树索引查找一个叶子节点的数据只需要三次磁盘IO，对于Mysql来说三层的B+树可以索引上百万的数据，这对于查询效率的提升是巨大的。

总结起来Mysql中B树索引有以下关键特点：

Mysql中的B树索引有两种数据存储形式，一种为聚簇索引，一种为二级索引。

InnoDB一般会使用表的主键来作为聚簇索引，如果一个表没有主键（不建议这么玩）InnoDB会选用一个唯一非空索引来代替，如果没有这样的索引，InnoDB会隐式建立一个聚簇索引。聚簇的含义即是数据行和相邻的键值紧凑的存储在一起，占据一块连续的磁盘空间，因此通过聚簇索引访问数据可以有效减少随机IO，通常使用聚簇索引查找比非聚簇索引查找速度更快。以我们建立的表t_book为例，聚簇索引即为自增主键id，其B树索引数据结构可以用下图来表示。

聚簇索引有以下关键特点：

InnoDB的B树索引中除了聚簇索引，就都是二级索引了，二级索引的含义是索引的叶子节点除了存储了索引值，还存储了主键id，在使用二级索引进行查询时，查找到二级索引B树上的叶子节点后还需要去聚簇索引上去查询真实数据，但是这里有一种特殊情况，即查询所需的所有字段在二级索引中都可以获取，此时就不需要再去回表查数据了，这种情况就是索引覆盖（EXPLAIN中EXTRA列中会出现USING INDEX，本文只关注索引结构，不详细讨论索引覆盖等技术的使用，如果深入理解索引的数据结构，索引覆盖等技术也没有那么神秘）。

在我们的测试表t_book中，test_index即为二级索引，由于我们把除了主键id所有的列都作为一个联合索引，所以在这个表上的查询都可以使用索引覆盖技术，但是具体生产环境中也不建议总是采用这种做法，索引列的增加也会增大插入更新数据时的索引更新成本，具体的优化要视具体情况决策。t_book上的二级索引test_index的索引结构由下图表示。

通过以上结构，我们可以推断出二级索引的以下关键特点：

索引覆盖：

最左前缀匹配：

二级索引可以说是我们在Mysql中最常用的索引，通过理解二级索引的索引结构可以更容易理解二级索引的特性和使用。

最后聊点轻松的索引结构，哈希索引就是通过哈希表实现的索引，即通过被索引的列计算出哈希值，并指向被索引的记录。

哈希索引有如下特性：

Mysql索引原理及慢查询优化

高性能Mysql 第三版

索引就是为特定的mysql字段进行一些特定的算法排序，比如二叉树的算法和哈希算法，哈希算法是通过建立特征值，然后根据特征值来快速查找。

1.普通索引:(index)最基本的索引，没有任何限制  目的:加快数据的查询速度

2.唯一索引:(unique)  与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

3.主键索引(primary key) 它 是一种特殊的唯一索引，不允许有空值。

4.复合索引:index(a,b,c)  为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

5.全文索引:fulltext  仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时耗空间。

第一类是myisam存储引擎使用的叫做b-tree结构，

第二类是innodb存储引擎使用的叫做聚簇结构（也是一种 b-tree）。 如下图：

注意：

1.myisam不需要回行处理 

2.innodb不需要回行处理,直接可以获取数据,因为innodb的储存引擎是包含了数据和索引文件的,其主键索引包含了数据,(唯一索引及普通索是没有直接包含数据的)

1、索引列不能参与计算

​ 有索引列参与计算的查询条件对索引不友好（甚至无法使用索引），如from_unixtime(create_time) = '2014-05-29'。

​ 原因很简单，如何在节点中查找到对应key？如果线性扫描，则每次都需要重新计算，成本太高；如果二分查找，则需要针对from_unixtime方法确定大小关系。

因此，索引列不能参与计算。上述from_unixtime(create_time) = '2014-05-29'语句应该写成create_time = unix_timestamp('2014-05-29')。

2、最左前缀匹配

​ 如有索引(a, b, c, d)，查询条件a = 1 and b = 2 and c >3 and d = 4，则会在每个节点依次命中a、b、c，无法命中d。也就是最左前缀匹配原则。

3、冗余和重复索引

​ ​ 冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应当尽量避免这种索引，发现后立即删除。比如有一个索引(A,B)，再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时，比如有人新建了索引(A,B)，但这个索引不是扩展已有的索引(A)

4、避免多个范围条件

        select user.* from user where login_time >'2017-04-01' and age between 18 and 30

​ 比如想查询某个时间段内登录过的用户：它有两个范围条件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但无法同时使用它们 .

5、覆盖索引 (能扩展就不新建)

​ 如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处：

1.索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量2.索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多

6、选择区分度高的列作索引

如，用性别作索引，那么索引仅能将1000w行数据划分为两部分（如500w男，500w女），索引几乎无效。

区分度的公式是count(distinct ) / count(*)，表示字段不重复的比例，比例越大区分度越好。唯一键的区分度是1，而一些状态、性别字段可能在大数据面前的区分度趋近于0。

7、删除长期未使用的索引

场景一(覆盖索引 5)

索引应该建在选择性高的字段上（键值唯一的记录数/总记录条数），选择性越高索引的效果越好、价值越大，唯一索引的选择性最高；

组合索引中字段的顺序，选择性越高的字段排在最前面；

where条件中包含两个选择性高的字段时，可以考虑分别创建索引，引擎会同时使用两个索引（在OR条件下，应该说必须分开建索引）；

不要重复创建彼此有包含关系的索引，如index1(a,b,c) 、index2(a,b)、index3(a)；

组合索引的字段不要过多，如果超过4个字段，一般需要考虑拆分成多个单列索引或更为简单的组合索引；

不要滥用索引。因为过多的索引不仅仅会增加物理存储的开销，对于插入、删除、更新 *** 作也会增加处理上的开销，而且会增加优化器在选择索引时的计算代价。

因此太多的索引与不充分、不正确的索引对性能都是毫无益处的。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。

       本节课主要关注InnoDB，但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。

       叶子节点包含了全部数据，其他节点只包含索引列。InnoDB将通过主键聚集数据，也就是说上图中的“被索引的列”就是主键列。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引InnoDB会隐式定义一个主键来作为聚簇索引。

       如果主键比较大的话，那辅助索引将会变的更大，因为 辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间

所以建议使用int的auto_increment作为主键

       主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大值时，下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满。

       聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想 象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些 *** 作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

       因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈的对比）

       不过，如果涉及到大数据量的排序、全表扫描、count之类的 *** 作的话，还是MyISAM占优势些，因为索引所占空间小，这些 *** 作是需要在内存中完成的。

       MyISM使用的是非聚簇索引， 非聚簇索引的两棵B+树看上去没什么不同 ，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于 索引树是独立的，通过辅助键检索无需访问主键的索引树 。

       所以说，聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。 一般要根据这个表最常用的SQL查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引 ，这个要看实际情况。

       聚簇索引和非聚簇索引的数据分布有区别，主键索引和二级索引的数据分布也有区别，通常会让人感到困扰和以外，下面通过一个列子来讲解InnoDB和MyISAM是如何存储数据的：

       该表的主键取值1~10000，按照随机顺序插入并使用optimize table命令做了优化。换句话说，数据在磁盘上的存储方式已是最优，但行的顺序是随机的。列col2的值是从1~100之间随机赋值，所以有很多重复的值。

       MyISAM的数据分布很简单，所以先介绍它。MyISAM按照数据插入的顺序存储在磁盘上，如下图所示：

在行的旁边显示行号，从0开始递增。因为行是定长的，所以MyISAM可以从表的开头跳过所需的字节找到需要的行。

col2上的索引

       事实上，MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引。

       InnoDB支持聚簇索引，所以使用不同的方式存储同样的数据。

       第一眼看上去，感觉和前面的没什么区别，但是该图显示了整个表，而不是只有索引。因为在InnoDB中，聚簇索引就是表，所以不像MyISAM那样需要独立的行存储，这也是为什么MyISAM索引和数据结构是分开的。

       聚簇索引的每一个叶子节点都包含了主键值。事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。

       还有一点和MyISAM不同的是，InnoDB的二级索引和聚簇索引很不相同。InnoDB的二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动时无需更新二级索引中的这个“指针”。

       我们在来看一下 col2索引 。

       每一个叶子节点包含了索引列（这里是col2），紧接着是主键值（col1），上图我们省略了非叶子节点这样的细节。InnoDB非叶子节点包含了索引列和一个指向下一级节点的指针。

       最后，以一张图表示InnoDB和MyISAM保存数据和索引的区别。

       前面讲过，最好使用AUTO_INCREMENT自增列来聚集数据，避免随机的、不连续的、值分布范围大的列做聚簇索引，特别是对于I/O密集型的应用。例如，从性能角度考虑，使用UUID来作为聚簇索引则会很糟糕：他使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

       为了演示这一点，我们做两个基准测试：

1、使用证书ID插入userinfo表，和uuid作为主键的userinfo_uuid表

       userinfo_uuid表跟userinfo表除了主键给为UUID，其他字段都一样

       测试这两个表的设计，首先在一个有足够内存容纳索引的服务器上向这两个表各插入100万条记录。然后向两个表继续插入300万数据，使索引的大小超过服务器的内存容量。测试结果如下：

       向UUID主键插入行不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长，另一方面毫无疑问是由于页分裂和碎片导致的。

       为了明白为什么会这样，来看看往第一个表中插入数据时，索引发生了什么变化。

自整型主键插入

       因为主键是顺序的，所以InnoDB把每一条记录都存在上一条记录的后面。当达到页的最大容量后，下一条记录就会写入到新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满，这也正是所期望的结果。

UUID插入

       因为新行的主键值不一定比之前插入的大，所以InnoDB无法简单的总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置，通常是已有数据的中间位置，并且分配空间。这会正价很多的额外工作，并导致数据分布不够优化。

缺点：

把这些随机值载入到聚簇索引后，也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充。

结论 ：使用InnoDB时应尽可能地按主键顺序插入数据，并且尽可能地单调增加聚簇键的值来插入新行。

---