MySQL索引相关

R2部队
149 阅读10分钟

1、MySQL的B+树结构是如何存储数据的？

MySQL的InnoDB存储引擎使用B+树索引来存储数据。B+树是一种多路搜索树，特别适合于磁盘存储，因为它的每个节点可以存储多个键值对，减少了磁盘I/O操作。下面以user表为例，详细解释B+树是如何存储数据的。

user表结构

假设user表的结构如下：

CREATE TABLE user(
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT;
    phone VARCHAR(20),
    email VARCHAR(100)
);
B+树的特点

B+树的主要特点包括：

  1. 所有数据都存储在叶子节点：叶子节点包含实际的数据记录；
  2. 每个叶子节点可以包含多个键值对：每个叶子节点可以存储多个键值对，减少了树的高度。
  3. 叶子节点之间通过指针连接：叶子节点之间通过指针连接，方便范围查询。
B+树的存储结构

假设user表中有以下数据：

INSERT INTO user(id, name, age phone, email) VALUES
(1, 'alice', 30, '1234567890', 'alice@example.com'), 
(2, 'Bob', 25, '2345678901', 'bob@example.com'),
(3, 'Charlie', 35, '3456789012', 'Charlie@example.com'),
(4, 'David', 28, '4567890123', 'david@example.com'),
(5, 'Eve', 22, '5678901234', 'eve@example.com');
B+树的构建

假设每个节点最多存储2个键值对(为了简化示例)， B+树的构建过程如下：

  1. 插入1： · 根节点：[1]
  2. 插入2： · 根节点：[1, 2]
  3. 插入3： · 根节点：[1, 2, 3] · 超过节点容量，分裂为[1]和[2, 3]，创建新的根节点[2] · 根节点：[2] · 左子节点：[1] · 右子节点：[2, 3]
  4. 插入4： · 右子节点[2, 3]超过容量，分裂为[2]和[3, 4] · 根节点：[2, 3] · 左子节点：[1] · 中间子节点：[2] · 右子节点：[3, 4]
  5. 插入5： · 右子节点[3, 4]超过容量，分裂为[3]和[4, 5] · 根节点：[2, 3, 4] · 左子节点：[1] · 中间子节点[2] · 右子节点：[3] · 最右子节点：[4, 5]
B+树最终结构

最终的B+树结构如下：

                [2, 3, 4]
                / |  |  \
            [1] [2] [3] [4, 5]

叶子节点存储数据 每个叶子节点存储实际的数据记录：
· [1]存储1, 'alice', 30, '1234567890', 'alice@example.com'
· [2]存储2, 'Bob', 25, '2345678901', 'bob@example.com'
· [3]存储3, 'Charlie', 35, '3456789012', 'Charlie@example.com'
· [4, 5]存储4, 'David', 28, '4567890123', 'david@example.com'和5, 'Eve', 22, '5678901234', 'eve@example.com'

范围查询

假设要查询id在2到4之间的记录：

  1. 从根节点[2, 3, 4]开始，找到2的位置。
  2. 从[2]节点开始，找到2的记录。
  3. 通过叶子节点之间的指针，依次访问[3]和[4, 5]节点，找到3和4的记录。
小结

B+树是通过将所有数据存储在叶子节点，并通过指针连接叶子节点，使得范围查询非常高效。每个节点可以存储多个键值对，减少了树的高度，从而减少了磁盘I/O操作。这种结构特别适合于数据库中的索引存储。

2、普通的索引的查找过程是什么样的(为什么说需要查询两次)？

在数据库中，普通索引(例如B+树索引)的查找过程通常涉及两次I/O操作，这是因为索引和数据存储在不同的地方。具体来说，普通索引查找过程可以分为两个主要步骤：索引查找和数据查找。

普通索引查找过程

假设我们为id列创建了一个索引，索引的结构如下：

  1. 索引查找
  • 索引结构：假设索引是一个B+树，每个节点存储id和指向实际数据的指针(通常是数据的物理地址或行号)。
  • 查找过程：当执行查询 select * from user where id = 3 时，首先在索引树中查找id = 3;
    · 从根节点开始，根据id的值逐级向下查找，直到找到包含id = 3的叶子节点。
    · 叶子节点中存储了 id = 3的记录的物理地址或行号。
  1. 数据查找
  • 数据结构：实际的数据存储在数据页中，每个数据页包含多个数据记录。
  • 查找过程：根据索引查找步骤中找到的物理地址或行号，访问数据页，读取实际的数据记录。
    · 例如，索引查找步骤中找到i=3的记录的物理地址是0x123456，则访问该地址，读取id = 3的记录。
为什么需要两次I/O操作
  1. 索引查找：第一次I/O操作是读取索引页，查找索引树中的id值，找到对应的物理地址或行号。
  2. 数据查找：第二次I/O操作是根据索引查找结果中的物理地址或行号，读取数据页，获取实际的数据记录。
    备注 - 优化：为了减少I/O操作，可以使用索引覆盖(Convering Index)。覆盖索引是指索引中包含了查询所需的所有列，这样在索引查找过程中可以直接从索引中获取所有需要的数据，而不需要再进行数据查找。
    例如，如果查询select id , name from user where id = 3, 并且id和name都在索引中，那么只需要一次I/O操作即可完成查询。
小结

普通的索引查找过程通常需要I/O操作，一次用于查找索引，另一次用于读取实际的数据记录。通常使用覆盖索引，可以减少I/O操作，提高查询性能。

索引覆盖

索引覆盖（Covering Index）是指索引中包含了查询所需的所有列。在这种情况下，查询可以直接从索引中获取所有需要的数据，而不需要再进行数据查找。示例：假如我们为id和name列创建了一个复合索引

CREATE INDEX idx_id_name ON user(id, name);

#假设索引的叶子节点如下：
[1, 'Alice'], [2, 'Bob'], [3, 'Charlie'], [4, 'David'], [5, 'Eve']
#查询SQL
select id, name from user where id = 3;
1.索引查找
    · 从根节点开始，根据id的值逐级向下查找，直到找到包含id = 3的叶子节点。
    · 在叶子节点中找到 id = 3的记录，记录的内容是[3, 'Charlie']。
2.数据查找
    · 由于查询只需要id和name列，而这些列已经在索引中，因此不需要再进行行数查询。
索引，在覆盖索引的情况下，查询可以直接从索引中获取所有需要的数据，而不需要再访问数据页，因此，只需要一次I/O操作即可完成查询。

#对比普通索引
如果使用普通的id列索引，叶子节点可能存储如下内容：
[1, 0x1000], [2, 0x2000], [3, 0x3000], [4, 0x4000], [5, 0x5000]
在这种情况下，查询select id, name from user where id = 3的过程如下：
1. 索引查找：
    · 从根节点开始，根据id的值逐级向下查找，直到找到包含id = 3的叶子节点。
    · 在叶子节点中找到id = 3的记录，记录的内容是[3, 0x3000]。
2. 数据查找：
    · 根据物理地址0x3000，访问数据页，读取实际的数据记录。
    · 读取到的数据记录为3, 'Charlie', 35, '3456789012', 'Charlie@example.com'
    · 从数据记录中提取id 和 name列。
小结：
    · 普通索引：需要两次I/O操作，一次用于查找索引，另一次用于读取实际的数据记录。
    · 覆盖索引：只需要一次I/O操作，因为查询所需的所有列都在索引中，可以直接从索引中获取所有需要的数据。
通过使用覆盖索引，可以显著减少I/O操作，提高查询性能。
一张表有几棵索引树

在数据库中，每个索引都会生成一棵独立的B+树。这意味着，如果一张表上有多个索引，每个索引都会有一棵独立的B+树。

#假设我们为id和name列分别创建了索引
CREATE INDEX idx_id ON user (id);
CREATE INDEX idx_name ON user (name);

#索引树的结构
1. id列的索引树：
    · 叶子节点存储id和只想实际数据的指针
    · 例如：
    [1, 0x1000], [2, 0x2000], [3, 0x3000], [4, 0x4000], [5, 0x5000]
2. name列的索引树：
    · 叶子节点存储name和指向实际数据的指针
    · 例如：
    ['Alice', 0x1000], ['Bob', 0x2000], ['Charlie', 0x3000], ['David', 0x4000], ['Dve', 0x5000]
3. 整行数据的存储
实际的而数据记录存储在数据页中，每个数据页包含多个数据记录。假设数据页的存储如下：
0x1000：[1, 'alice', 30, '1234567890', 'alice@example.com']
0x2000：[2, 'Bob', 25, '2345678901', 'bob@example.com']
0x3000：[3, 'Charlie', 35, '3456789012', 'Charlie@example.com']
0x4000：[4, 'David', 28, '4567890123', 'david@example.com']
0x5000：[5, 'Eve', 22, '5678901234', 'eve@example.com']
#查找过程
查询select id, name from user where id = 3;
1. 索引查找：
    · 从id列的索引树中查找id = 3
    · 找到叶子节点[3, 0x3000]
2. 数据查找
    · 根据物理地址0x3000，访问数据页，读取实际的数据记录
    · 读取到的数据记录为[3, 'Charlie', 35, '3456789012', 'Charlie@example.com']
    · 从记录中提取id和name列。
    
查询select id, name from user where name = 'Charlie';
1. 索引查找：
    · 从name列的索引树中查找name = 'Charlie'
    · 找到叶子节点['Charlie', 0x3000]
2. 数据查找：
    · 根据物理地址0x3000，访问数据页，读取实际的数据记录
    · 读取到的数据记录为[3, 'Charlie', 35, '3456789012', 'Charlie@example.com']
    · 从数据记录中提取id和name列

#覆盖索引
假设我们创建了一个符合索引idx_id_name:
CREATE INDEX idx_id_name ON user(id, name);
在这种情况下，索引树的叶子节点存储id和name，而不需要存储只想实际数据的指针。
索引树的结构：[1, 'Alice'], [2, 'Bob'], [3, 'Charlie'], [4, 'David'], [5, 'Eve']
查询select id, name from user where id = 3
1. 索引查找：
    · 从idx_id_name索引树中查找id = 3
    · 找到叶子节点[3, 'Charlie']
2. 数据查找：
    · 由于查找只需要id和name列，而这些列已经在索引中，因此不需要再进行数据查找。
聚簇索引 or 非聚簇索引

主键索引在数据库中是一种特殊的索引，通常被称为聚簇索引（Clustered Index）。聚簇索引的结构于普通的非聚簇索引（Non-Clustered Index）有所不同，这导致了查询过程也有所不同。

聚簇索引（Clustered Index）
  1. 数据存储：在聚簇索引中，数据行是按照索引键的顺序存储的，也就是说，数据行本身存储在索引的叶子节点中。
  2. 索引结构：聚簇索引的叶子节点不仅包含索引键，还包含完整的数据行。
非聚簇索引（Non-Clustered Index）

  1. 数据存储：在非聚簇索引中，数据行存储在数据页中，而索引的叶子节点只包含索引键和指向数据行的指针。

  2. 索引结构：非聚簇索引的叶子节点包含索引键和指向数据行的指针。

avatar
文章
阅读
粉丝