avatar

目录
database-index-structure

Database Index Structure and Non-Unique Fields

索引的存储结构

大多数关系型数据库使用B树或B+树来存储索引。以B+树为例:

Code
1
2
3
4
5
         [10, 20]
/ | \
[5, 7] [15, 18] [25, 30]
/ | \ / | \ / | \
1 6 8 11 16 19 21 27 35

每个节点包含索引字段的值和指向实际数据行的指针。

非唯一字段的索引

对于非唯一字段,索引结构会略有不同:

  1. 在索引中存储重复值
  2. 对于每个重复值,存储多个指向不同数据行的指针

例如,假设我们有一个 “年龄” 字段的索引:

Code
1
2
3
4
5
         [20, 25]
/ | \
[18, 18] [22, 22] [30, 35]
/ | \ / | \ / | \
17 18* 19 21 22* 24 26 32 38

其中 18 和 22 表示有多个学生的年龄是 18 或 22。

非唯一字段索引的影响

  1. 存储空间:需要更多空间来存储重复值和额外的指针。
  2. 查询性能:
    • 精确匹配查询可能需要额外步骤来检索所有匹配的行。
    • 范围查询通常不受太大影响。
  3. 更新性能:插入、更新和删除操作可能需要更多时间来维护索引结构。

示例查询比较

假设我们有一个学生表,字段包括 id(唯一)和 age(非唯一):

sql
1
2
3
4
5
6
7
CREATE TABLE students (
id INT PRIMARY KEY,
age INT,
name VARCHAR(50)
);

CREATE INDEX idx_age ON students(age);
  1. 唯一字段查询:

    sql
    1
    SELECT * FROM students WHERE id = 100;
    • 直接定位到特定行,非常快
  2. 非唯一字段查询:

    sql
    1
    SELECT * FROM students WHERE age = 20;
    • 首先在索引中找到所有 age = 20 的条目
    • 然后访问每个对应的数据行
    • 性能仍然比全表扫描好,但不如唯一字段查询快

评论