暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / InnoDB索引模型概述

InnoDB索引模型概述

扫地僧的故事 2020-07-08
1353
不知道大家在工作中有没有碰到这种情况，开发要么一个索引也不建，要么给你建一堆乱七八糟的索引，每次看得都头大。。我想每个DBA应该都遇到过，生产上一些SQL语句由于没有使用合适的索引而导致的数据库性能问题。所以，这两天又研究了一下MySQL的索引。
MySQL中，索引是在存储引擎层实现的，所以没有统一的索引标准，不同的存储引擎的索引的工作方式都不一样。由于InnoDB存储引擎在MySQL数据库中使用最为广泛，所以研究了InnoDB的索引模型。

整理了以下几点内容：

  • 了解一下B+树

  • 分析InnoDB索引模型

  • 索引维护（页分裂&页合并）

  • 主键设计的重要性

  • 唯一索引中如果有null值，还能保证数据唯一吗？

  • 理解最左前缀原则，避免创建冗余索引

B+树（有序数组链表+平衡多叉树）

B+树的存储方式：

从上图能看到，键值对应的具体数据都在叶子节点上，非叶子节点用来做索引，而叶子接待你中有一个指针指向下一个叶子节点。这样做的好处是，极大的提高了区间访问的性能。

下面这个链接是usfca大学的一个算法网站，能让我们直观的理解B+树是怎么玩的～

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

InnoDB索引模型

在Innodb中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB使用了B+树索引模型，所以数据都是存在B+树中的。每一个索引在InnoDB里面对应一颗B+树。
假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。建表语句如下：
create table T(
    id INT PRIMARY KEY,
    k INT NOT NULL,
    name VARCHAR(16),
    key(k)) engine = InnoDB;
表中R1～R5的(ID,k)的值分别为(100,1),(200,2),(300,3),(500,5),(600,6),两棵索引树的示例示意图如下：

电脑上的工具实在不怎么好用，只能将就着看吧。。。

从上图中能看到，根据叶子节点的内容，索引类型可分为主键索引和非主键索引。


    普通索引和唯一索引就不多说了，看下覆盖索引和复合索引的特点。

  • 覆盖索引：

    如果查询条件使用的是普通索引（或是复合索引的最左原则字段），查询结果是复合索引的字段或是主键，那么不用做回表操作，直接返回结果，减少磁盘IO读写，能较大的提升查询性能。

  • 复合索引：

    根据创建复合索引的顺序，以最左原则进行where检索。

    比如复合索引idx_ab(a,b),那么where a=1 和 where a=1 and b=2 都能使用索引idx_ab，但是单独使用where b=2作为条件查询时，不能使用索引idx_ab(a,b)。

    所以，在创建复合索引时，一定要考虑列的顺序。

    第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。第二个原则就是，尽可能节省空间。

基于主键索引和普通索引的查询有什么区别？
1. select * from T where ID=500;
            主键查询方式，只需要搜索ID索引树；
2. select * from T where k=5;
            普通索引查询方式，则需要先搜索k索引树，得到主键ID的值，再到ID索引树搜索一次。这个过程称为回表。
也就是说，基于非主键索引的查询需要多扫描一颗索引树，因此，我们在应用中应该尽量使用主键查询。
索引维护

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面的索引树图为例，如果插入新的ID为700，则只需在R5的记录后面插入一个新记录。如果新插入的ID值为400，就需要逻辑上挪动后面的数据，空出位置。

而更糟糕的情况是，如果R5所在的数据页数据页已经满了，根据B+树的算法，需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。(页分裂会发生在插入或更新，并且造成页的错位)在这种情况下，性能自然会受影响。

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。

InnoDB用INFORMATION_SCHEMA下的INNODB_METRICS表来跟踪页的分裂数。可以查看其中的index_page_splits 和index_page_reorg_attempts, index_page_reorg_successful统计。

mysql> selectname,subsystem,status,comment from information_schema.INNODB_METRICS where name like '%index_page%';

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，并且索引页的”page-full”百分比低于MERGE_THRESHOLD值，InnoDB会尝试将索引页与相邻索引页合并。合并的过程，可以认为是分裂过程的逆过程。默认的MERGE_THRESHOLD值为50.
InnoDB用 INFORMATION_SCHEMA下的INNODB_METRICS表来跟踪页的合并数。可以查看其中的index_page_merge_attempts和index_page_merge_successful统计。
除了用页合并的方式解决碎片化的问题，也可以使用OPTIMIZE重新整理表。这可能是个很重量级和耗时的过程，但可能是唯一将大量分布在不同区的页理顺的方法。
需要注意的是：合并和分裂的过程中，InnoDB会在索引树上加写锁。在操作频繁的系统中这可能会是个隐患。它可能会导致索引的锁争用。如果表中没有合并和分裂（也就是写操作）的操作，称为“乐观”更新，只需要使用读锁（S）。带有合并也分裂操作则称为“悲观”更新，使用写锁（X）。

参考文章：

https://www.percona.com/blog/2017/04/10/innodb-page-merging-and-page-splitting/

主键设计的重要性
我们应该选择自增主键还是使用含有业务逻辑的字段做主键？
自增主键，是指自增列上定义的主键，在建表语句中一般是这么定义的：NOT NULL PRIMARY KEY AUTO_INCREMENT。插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。
自增主键的插入数据模式，符合了我们前面提到的递增插入场景，每次插入一条新记录，都是追加操作，都不涉及到挪到其他记录，也不会触发叶子节点的分裂。
而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
除了考虑性能外，从存储空间的角度来看，假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约20个字节，而如果用整型做主键，则只要4个字节，如果是长整型则是8个字节。
显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
从性能和存储空间方面考量，自增主键往往是更合理的选择。
业务字段直接做主键的场景，如下：

  1. 只有一个索引；

  2. 该索引必须是唯一索引。

唯一索引中如果有null值，还能保证数据唯一吗？

答案是，不能。

在Oracle中，如果联合uk(a,b),当插入（1，null），（1，null）时会报唯一性错误。

在MySQL中，如果联合uk(a,b),当插入（1，null），（1，null），能成功插入。那么也就意味着，不能保证数据的唯一性了。在去O时，做表结构转换的过程中，这个细节需要考虑进去，不然可能影响还挺大的呢。


理解最左前缀原则，避免创建冗余索引

这段拿出来单独写，主要是因为，经常看到研发提供的建表语句中有类似这种写法：idx_1(a),idx_2(a,b),idx_3(a,b,c)。每次都要和他们解释一遍，我不知道他们到底是怎么想的？

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。所以，千万别再建这种重复的索引了。索引越少越好，维护每个索引都是需要成本的，在修改数据时，每个索引都要进行更新，会降低写入的速度。

END;

今天下班前，自称公众号老手的同事，指导我这个公众号新人说，文章不能写的太长。。所以，还有一些内容（比如，为什么不建议开发使用唯一索引？），我就放到下一篇吧。（我真的不是偷懒。。）

技术
文章转载自扫地僧的故事，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论