MySQL数据结构选择

系列文章目录

一、MySQL数据结构选择
二、MySQL性能优化explain关键字详解

前言

  本篇着重剖析MySQL索引，以及底层关于索引数据结构的选择。
  数据结构网址：数据结构

一、索引

1.1、什么是索引

  索引类似于一本书的目录，可以让数据库在检索数据时快速定位目标行，而不需要逐一扫描整个表。假设需要在《计算机网络》这本书中找到数据链路层（某一行的数据）这一章，如果不通过目录（索引），则需要一页一页地去翻，直到找到目标为止。

1.2、构建索引的过程

  构建索引的过程，实际上是将数据库中的一列或多列的数据按照特定规则构建成一种有序的数据结构，构建索引的过程，可以分为以下的步骤：

1. 收集列的数据： MySQL 读取需要构建索引的列的数据。（如果是主键索引，MySQL 会读取整个主键列的数据）
2. 排序数据： 将列的数据按照值的大小排序，排序是为了在后续查找过程中减少搜索范围。
3. 构建树形结构： 以下文会提及的B+树数据结构为例，首先会将排序后的数据分成多个小块，每个块称为一个页（Page），然后会构建树形结构，最底层的节点称为叶子节点，每个叶子节点存储一个范围的数据。叶子节点之间按顺序通过双向链表连接，便于范围查询，上一层节点会存储下一层节点中的关键数据（最小值）。
4. 将索引结构存储到磁盘： 索引的每个节点存储在磁盘的特定页中，以供后续查询。
     也就是说，索引是帮助Mysql高效获取数据的排好序的数据结构

1.3、索引的更新和维护

  在数据表发生增删改操作时，索引也需要维护：

• 插入数据：将新值插入到索引结构中，可能需要分裂节点。（每个节点有大小限制，超过大小则会触发分裂）
• 删除数据：从索引中移除对应值，可能会合并节点以保持平衡。
• 更新数据：更新索引列时，等价于删除旧索引和插入新索引。

  所以在单体架构下，推荐使用整型的自增主键，顺序插入以避免频繁地发生节点分裂。至于为什么推荐使用int类型，而不是UUID，是因为在构建B+树的结构时，确定新的节点应该在目标节点的左侧/右侧。数值之间的比较，速度远大于字符串之间的比较。（字符串的比较，是将完整的字符串拆成单个字符，然后转换成ASCII码进行比较）

  索引在加速查询的同时，一定程度上也会影响插入和删除的效率，所以索引并非越多越好

1.4、索引的查询和管理

1. 查看表中的索引：

SHOW INDEX FROM table_name;

2. 创建索引：

-- 普通索引
CREATE INDEX idx_column_name ON table_name(column_name);

-- 联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 唯一索引
CREATE UNIQUE INDEX idx_unique_name ON users(name);

3. 删除索引：

DROP INDEX idx_column_name ON table_name;

1.5、InnoDB 和 MyISAM 的索引实现

  MyISAM 的索引文件和数据文件是分离的，也就是MyISAM是非聚集索引。其索引文件存放在*.MYI中，数据文件存放在*.MYD中。而InnoDB 的索引实现是聚集的，也就是表数据文件本身就是按B+Tree组织的一个索引结构文件。

1.6、联合索引和最左前缀法则

  假设我某张表建立了一个name,age,address的联合索引，同时表中有如下数据，构建索引的过程如下：

| name | age | address |
|--------|-----|-----------------|
| Alice | 30 | New York |
| Bob | 25 | California |
| Alice | 25 | Los Angeles |
| Bob | 30 | Chicago |

  则联合索引键按 name, age, address 的顺序将数据组织成以下结构：

(Alice, 25, Los Angeles)
(Alice, 30, New York)
(Bob, 25, California)
(Bob, 30, Chicago)

  首先对联合索引中的第一个字段的值进行排序，相同的则对二，三字段依次排序，根据这样的排序规则，很容易理解，为何联合索引的最左前缀法则中，必须要左到右的连续列进行查询，如果跳过了一列，则无法利用后续列。例如，对于联合索引 (name, age, address)，可以使用：name 或 name, age 或 name, age, address，不能单独使用：age 或 address：根本原因在于，在构建索引时，排序是按照联合索引从左到右的顺序依次进行的，如果跳过了最左列，那后续的字段必然是没有排序完成的，即只有在明确 name 的值后，才能进一步判断age和 address 的值。

二、MySQL数据结构选择

  树的高度与节点访问次数直接相关，每次查询都需要从根节点开始，一层一层地向下访问，直到找到目标叶子节点。而磁盘的访问特点是，随机访问速度慢。因为数据在磁盘上不一定是连续存储的，磁盘读取数据时，需要将磁盘头移动到目标位置，然后读取目标块（页）。这个过程包括寻址时间和传输时间，尤其是寻址时间消耗较大。树越高，查询过程中需要访问的磁盘块（页）越多，每次访问都需要一次磁盘 I/O。所以MySQL选择构建索引的数据结构的根本目标就是，降低树的高度。

2. 1、二叉树（平衡二叉树）

  二叉树每个节点最多只能有两个子节点。每个节点可以视为一个Node对象，包含自身的值，左子节点，右子节点，父节点。平衡二叉树是对于二叉树的改进，在构造时加入了小于根节点的在左，大于根节点的在右，相同的不存这样的原则。但是在极端情况下，会形成链表：
  这样和降低树的高度的目的很显然是互相违背的，并且平衡二叉树，在插入元素时会频繁的发生旋转的情况，对于性能的开销也是比较大的，所以不采用该方案。

2.2、红黑树

  红黑树是针对于平衡二叉树的改进方案，通过变色减少旋转的次数，性能要优于平衡二叉树。但是对于降低树的高度，有后续更好的解决方案。

2.3、Hash表

  Hash 表通过哈希函数将键值映射到特定的存储位置。由于这种映射是离散的，数据没有按顺序排列，导致的一个很大的问题就是只支持单点精确查询，无法做到范围查询。并且Hash 索引依赖完整的键值生成哈希值，只能进行等值匹配，不支持模糊查询。在发生Hash冲突时，也需要浪费一部分性能去进行处理，缺点较多，所以不采用该方案。

2.4、B树

  B树是一种平衡的多路搜索树，其每个节点最多可以有多个子节点，和普通的树相比，每个节点存储多个键值，（平衡二叉树，每个节点只能存储一个键值）并按照从小到大的顺序排列。并且一个节点内的键值将子树划分为不同的范围：

对于一个节点 [K1, K2, K3]：
左子树的所有键 < K1
中间子树的所有键满足K1≤ 键 < K2
右子树的所有键 ≥K3。

  并且所有叶子节点都在同一层，保证了树的高度一致，并且无论是叶子节点，还是其他节点，都存放了数据和索引。一页通常是16K。索引和数据并存，会导致页的数量增多，同样无法控制树的高度，所以不采用该方案。

2.5、B+树

  B+树是针对B树的改进，在B树的基础上，将所有的数据和索引全都存放在叶子节点上，而非叶子节点，只存放关键性的索引（冗余索引），不存放数据。同样一页16k，相比于B树能存放更多的数据。可以显著降低树的高度，并且B+树的叶子节点之间会构成一个双向链表，有利于范围查询。故最终选择使用B+树作为索引的数据结构。

  至于B树和B+树为何每个节点存储多个键值？因为在读取数据/索引时，会将该页加载到内存，然后再进行查找，查找的时间，`远小于将某一页加载到内存的时间。
  假设一棵 B+ 树的节点扇出为 100，树的高度为 ℎ，则第一层（根节点）可以存储 100 个键值。第二层可以存储100^2个键值，第三层可以存放100 ^3个键值，数据量为 1 百万时，树的高度仅需 3 层。并且每个节点存储多个键值，可以充分利用磁盘页的存储空间，减少磁盘页的浪费，并且上面提到，在插入或者删除数据时，索引的结构可能会发生改变，当节点存储多个键值时，插入和删除操作不会频繁导致节点的分裂和合并。（如果一个节点可以存储 100 个键值，只有在键值数量超过 100 或少于 50 时才需要调整。如果一个节点只能存储 1 个键值，插入或删除可能会频繁触发分裂或合并，增加开销。）