MySql 实战—— Innodb 索引、数据结构（纠正许多博客中错误的细节）

MySql实战——索引

最近准备给组内测试的同事们分享下MySQL的小知识，然后网上看看排在靠前的博客。发现许多博主画的图，写的东西都不严谨，甚至有许多错误的点。 例如：  B+Tree 节点中关键字与指针；  B+Tree底层叶子节点数据结构；  联合索引数据结构；  聚簇索引数据检索方式；   InnoDB中每页能存储的数据量 ；

前言

MySQL 是由 MySQL AB 公司（目前已经被 SUN 公司收归麾下）自主研发的，目前 IT 行业最流行的开放源代码的数据库管理系统之一，它同时也是一个支持多线程高并发多用户的关系型数据库管理系统。
本篇博客中介绍内容均是基于MySQL InnoDB存储引擎下讨论，下文中不再做强调。
例如：一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了 优化 ，增加了顺序访问指针，

InnoDB

lnnoDB逻辑存储结构

MySQL所有数据都被逻辑地存放在一个空间中 ，我们称之为表空间 （ tablespace ） 。
表空间又由段 （ segment ） 、区 （ extent ） 、页 ( page ） 组成。默认情况下 InnoDB存储引擎有一个共享表空间 ibdata1 ，即所有数据都放在这个表空间内 。
如果我们启用了参数innodb_file_per_table ，则每张表内的数据可以单独放到一个表空间内 。段就是表，区就是连续的几个页，页是最小单位。
在数据目录中(通常是 /var/lib/mysql/ )，会看到它包含有：

• *.frm文件：保存了每个表的元数据，包括表结构的定义等；
• *.ibd文件：开启了独立表空间，存放表的数据和索引的文件。

这里重要的是名为.ibd 的文件。这个文件被分为N个段。每个段都与一个索引相关联。段本身会增长或收缩，下一级为区。一个区仅存在一个段中，并且固定尺寸为1MB(在默认页大小的情况下)。页是区的下一级，默认大小为16KB。因此，一个区最多可包含64页。
一个页可以包含2到N行。一个页可以容纳的行数与行大小有关，这是表结构设计时定义的。InnoDB中有一个规则，至少要在一个页中容纳两行。因此，行大小限制为8000字节。
InnoDB会试着为将来索引记录的插入和更新留下十六分之一的空白页。如果索引记录以连续的顺序被插入（升序或者降序），结果索引页大约是 15/16 为存满

索引

HASH索引

• 数组+链表的结构，通过hash函数计算出key在数组中的位置，然后如果出现hash冲突就通过链表来解决。
• 不支持范围查询
• 无法利用索引排序
• 无法使用like ‘xxx%’模糊查询
• 哈希索引也不支持多列联合索引的最左匹配规则
• 在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。
• hash索引存储的是计算得到的hash值和行指针，而不存储具体的行值，所以通过hash索引查询数据需要进行两次查询(首先查询行的位置，然后找到具体的数据)

这不是本文需要具体展开的内容 就不细说了

多路查找树索引

B-Tree(平衡多路查找树)

• 每个节点至多可以拥有m棵子树
• 若根节点不是叶子节点，则至少有2个孩子
• 非根非叶的节点至少有的Ceil(m/2)个子树(Ceil表示向上取整)
• 所有叶子节点都在同一层，且不包含其它关键字信息
• 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn， P为指针、K为关键字。所以指针数为 关键字数 + 1）
• 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
• 每个节点都保存了关键字的数据，节点出现的关键字不会出现在子节点中

B+Tree聚簇索引

• 数据都保存在叶子节点中，且是有序的
• 非叶子节点的子树 等于关键字数量
• 叶子节点是 双向链表 形式保存兄弟节点的指针

这里要注意：
许多博客中将B+tree 绘制的子节点数与B-Tree一致其实是错误的！
叶子节点间是双向链表 而非单向链表！

B+Tree非聚簇索引

• 叶子节点中保存的是主键的值

为什么MySQL选择B+Tree

• 普通的二叉树可能因为插入的数据最后变成一个很长的链表，哪怕采用红黑树保证平衡，数据量较大情况下依然会出现节点长度过长的问题。（特别是访问深层数据时，需要索引多次，数据结构设计的更为‘矮胖’一点就可以减少访问的层数）
• B-Tree 每个节点 key 和 data 在一起，无法区间查找。（范围查询在数据库中是很常用的）
• B+Tree 只需要去遍历叶子节点就可以实现整棵树的遍历
• B+Tree 的非叶子节点并没有保存关键字的具体数据信息，内部节点相对B-Tree更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了

MySQL数据检索方式（不考虑缓存、用三层的B+Tree来举例）

聚簇索引：

• 从磁盘中获取将根节点页数据至内存（第一次IO）
• 内存中根据二分查找找到对应的子节点的指针
• 从磁盘中获取对应子节点页的数据至内存（第二次IO）
• 内存中根据二分查找找到对应的叶子节点的指针
• 从磁盘中获取对应叶子节点页的数据至内存（第三次IO）
• 内存中根据二分查找找到对应的关键字并获取数据

非聚簇索引 多了一次查找主键ID的过程。
InnoDB叶子节点直接存储数据 和 MyISAM 存储引擎不同 MyISAM索引页和数据页是分开的，叶子节点中存的是指针。

结论知识

• 索引列字段尽量不要太长，否则会导致每页中存储的关键字数量变少，最终导致B+Tree层高快速增长；
• 主键索引最好是自增，因为聚簇索引存储是有序的；

  如果主键ID是随机无序生成：
  1、写入的目标页可能已经刷到了磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入钱不得不先找到并从磁盘读取目标到页到内存中。这将导致大量的随机I/O
  2、InnoDB不得不频繁地做页分裂操作，以便为更新的行分配空间（原有页空间已被占满），页分裂会导致移动大量数据。
  3、因为频繁地页分裂，页会变得稀疏并被不规则地填充，所以最终会产生大量的碎片

顺序自增主键什么情况下会造成更坏的结果？
        对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上届会变成“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置。如果你的服务器版本还不支持innodb_autoinc_lock_mode参数，可以升级到新版本的InnoDB，可能对这种场景会工作得更好。

该问题摘抄自《高性能MySQL第三版》

多列联合索引

• 联合索引最左匹配原则，是因为其实他只是先根据A字段排序后如A的值相等，再根据对应的B字段排序。
• 可以理解为复合索引就是按照用户指定的顺序在节点里多保存了几个字段而不是一个字段，变宽一点。

很多博客中画的图，非叶子节点只存了最左侧列的值当成关键字是错误的!!!

参考文献：

https://www.freesion.com/article/8502997880/

percona英文版原文            csdn翻译中文版

上文中文版参考文献中有一点错误 关于页存满并非是100% 而是 15/16 为存满。InnoDB试着为将来索引记录的插入和更新留下十六分之一的空白页。 详见：MySQL5.1 官方手册