MySQL底层数据逻辑及模型

  • B-tree
  • B+tree
  • 为什么MySQL不用hash数据结构？
  • 索引数据量
  • 为什么innodb建表时，必须建主键，且推荐使用整型的自增主键？
  • MySQL的执行流程图：


在学习MySQL数据结构前，需要先大致了解一下一些基础的数据结构，比如：二叉树、红黑树、Hash表、链表、B-tree、B+tree。

B-tree

mysql的逻辑结构 mysql底层逻辑_mysql的逻辑结构


B-tree数据结构中，每个节点都包含数据，如果数据量较大的话，那么每个节点的度（节点的数据存储个数）的信息就会比较少，包含的节点少，磁盘io次数就会增多。在范围查找时，需要每次都从根节点去查找数据。

B+tree

mysql的逻辑结构 mysql底层逻辑_MySQL_02


较B-tree数据结构而言。B+tree数据结构中，数据都统一存储在末端的叶子节点上，每个叶子节点上会同时存储上一个叶子节点与下一个叶子节点的地址信息。范围查找时不需要每次都从根节点开始。B+tree的底层思想就是减少磁盘io次数。

为什么MySQL不用hash数据结构？

这是因为hash在进行精确查找时，虽然速度会很快，但是hash不支持范围查找，模糊查找时更是无法获取hash值，当然还有hash碰撞的问题，所以MySQL使用B+tree更合适。

索引数据量

mysql的逻辑结构 mysql底层逻辑_mysql_03


对于B+tree数据结构数据大小的解读（上图为InnoDB存储引擎示意图）：

第一行：一个节点大约16k，索引用bigint类型大约占8个字节，用int更小，这个取决于主键的大小，空白部份表示下个节点的磁盘空间地址，占6个字节，一个节点大约可以放1170个索引。

第二行：原理同第一行。

第三行：叶子节点包含date元素，可能是索引所在磁盘地址（MyISAM），也有可能是索引所在行的磁盘数据（InnoDB），大约是1k，一个叶子节点16k，大约能放16个索引。

整个三行的树全部放满，大约能放1170 * 1170 * 16，约等于两千多万字节索引数据。

为什么innodb建表时，必须建主键，且推荐使用整型的自增主键？

如果没有主键的话，innodb会从第一列开始找一列不重复的列来组织整张表的所有数据，如果没有找到，则会默默的在后台建一列隐藏列rowid，来帮助维护组织B+tree。MySQL的资源是非常宝贵的，尽量自己做。不推荐使用uuid，因为uuid既不是整型，又不是自增。uudi字符串比较大小得逐位比较，且需要先转换为Ascii码，这样比较耗费资源。要求自增是因为自增的话，每次插入都会在末尾插入，而非自增插入如果在中间位置，可能会导致该位置重新分裂，所以应尽量减少分裂发生。

MySQL的执行流程图：

mysql的逻辑结构 mysql底层逻辑_数据结构_04