database-index-structure

Database Index Structure and Non-Unique Fields

索引的存储结构

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

Code

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

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

非唯一字段的索引

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

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

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

Code

         [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

CREATE TABLE students (
    id INT PRIMARY KEY,
    age INT,
    name VARCHAR(50)
);

CREATE INDEX idx_age ON students(age);

1. 唯一字段查询：

   sql

   SELECT * FROM students WHERE id = 100;

   • 直接定位到特定行，非常快

2. 非唯一字段查询：

   sql

   SELECT * FROM students WHERE age = 20;

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