数据库常见概念

索引数据结构

B树，B+树
查询，构建（插入），删除

遍历

前序，中序，后序遍历

磁盘

磁道，io操作与树的层级数量的关系。

磁盘块和扇区是两种不同的存储单位

磁盘块（Disk Block）和扇区（Sector）

1. 磁盘块定义：磁盘块是操作系统用于在磁盘上读写数据的最小单位。通常，磁盘块的大小可以从 512 字节到多个千字节不等（如 4KB、8KB）。

   • 用途：磁盘块用于文件系统的数据存取，通常在高层次上抽象出对磁盘的物理存储结构。

2. 扇区定义：扇区是磁盘硬件中最小的物理存储单位。传统硬盘的扇区大小通常为 512 字节，而新一些的硬盘也支持 4096 字节（4KB）扇区。

   • 用途：扇区是硬盘读取和写入操作的最小粒度，所有数据存储在硬盘上的方式都是以扇区为基础的。

3. 层次关系：

   • 扇区是磁盘的物理最小存储单位，而磁盘块则是操作系统中用于管理和访问数据的抽象层次。多个扇区可以组成一个磁盘块。

4. 性能优化：

   • 文件系统在进行读写操作时，通常以磁盘块为单位来减少访问次数，提高效率。尽管底层硬件是以扇区为单位工作的，但在高层操作时以块为单位更为方便。

一次IO的过程

在磁盘上的一次 I/O 操作的过程可以分为多个步骤，具体如下：

1. 请求生成

   • 应用程序发起请求：当一个应用程序需要读取或写入数据时，它会通过系统调用（如 read 或 write）生成 I/O 请求。

2. 请求传递到操作系统

   • 系统调用：应用程序的 I/O 请求通过系统调用传递给操作系统，操作系统会将请求添加到 I/O 调度队列中。

3. I/O 调度

   • 请求调度：操作系统的 I/O 调度程序会根据调度算法（如先来先服务、最短寻道时间优先等）选择合适的请求进行处理。

4. 找到物理地址

   • 存储映射：操作系统通过文件系统将逻辑地址（文件中的位置）转换为物理地址（磁盘上的位置），确定数据所在的磁盘块或扇区。

5. 发起磁盘操作

   • 磁盘驱动器：操作系统通过磁盘驱动程序向硬件发送命令，指示硬盘执行相应的读或写操作。

6. 磁盘寻址

• 寻道时间：硬盘的磁头移动到请求的磁道上，这个过程称为寻道。寻道时间是影响 I/O 操作延迟的一个重要因素。

7. 数据传输

   • 数据读取或写入：磁头定位到正确的磁道后，数据开始在磁头和磁盘控制器之间传输。读取操作将数据从磁盘读取到内存，写入操作则将内存中的数据写入磁盘，数据必须从磁盘读入内存后才能被CPU使用。

8. 完成状态更新

   • 更新状态：一旦数据传输完成，磁盘控制器会向操作系统发送中断信号，系统更新 I/O 请求的状态。

9. 返回结果

   • 通知应用程序：操作系统处理完 I/O 请求后，会通知应用程序，指示操作已成功完成（或发生错误）。

对于单独一次 I/O 请求来说，处理一个扇区与多个扇区的区别

对于单独一次 I/O 请求来说，处理一个扇区与多个扇区的区别主要体现在以下几个方面：

1. 数据量

   • 单个扇区：

     • 只读取或写入一个扇区（通常为 512 字节或 4KB）的数据，数据量相对较小。

   • 多个扇区：

     • 一次性读取或写入多个扇区，数据量显著增加，通常会包括更多信息或数据块。

2. I/O 时间开销

   • 单个扇区：

     • 由于只涉及一块数据，I/O 操作的持续时间一般较短，但仅能处理有限量的数据。

   • 多个扇区：

     • 因为需要处理更多数据，I/O 操作可能会花费更长时间，但每个请求的相对开销（如寻道时间）会降低。

3. 性能影响

   • 单个扇区：

     • 如果频繁需要短小的数据块，可能会导致较高的 I/O 开销，影响整体性能。

   • 多个扇区：

     • 一次处理多个扇区能提高吞吐量，尤其是在处理大量数据时更有效。

4. 缓存和缓冲管理

   • 单个扇区：

     • 可能不会充分利用缓存，导致频繁的磁盘访问。

   • 多个扇区：

     • 一次性加载多个扇区，缓存命中率可能更高，减少对磁盘的访问次数。

处理不同磁道的扇区与处理同一磁道的扇区之间的主要区别

处理不同磁道的扇区与处理同一磁道的扇区之间的主要区别体现在以下几个方面：

1. 寻道时间

   • 不同磁道的扇区：

     • 需要移动磁头到不同的磁道，导致较高的寻道时间。这是 I/O 操作的主要延迟来源。

   • 同一磁道的扇区：

     • 只需要在同一磁道上切换扇区，寻道时间几乎为零，操作更加快速高效。

2. 旋转延迟

   • 不同磁道的扇区：

     • 磁头移动到新磁道后，可能还需要等待盘片旋转到正确位置，增加整体延迟。

   • 同一磁道的扇区：

     • 在同一磁道上，旋转延迟通常较小，因为磁头已定位，只需等待旋转即可读写。

3. I/O 吞吐量

   • 不同磁道的扇区：

     • 多次寻道和旋转将降低每次 I/O 操作的数据处理吞吐量，影响整体性能。

   • 同一磁道的扇区：

     • 可以实现更高的吞吐量，因为减少了寻道和旋转的时间开销，允许更快的连续操作。

4. 操作复杂性

   • 不同磁道的扇区：

     • 操作比较复杂，涉及多次寻道和旋转，可能需要更复杂的 I/O 调度策略。

   • 同一磁道的扇区：

     • 操作较简单，调度并发处理时更加高效。

处理不同磁道的扇区相较于同一磁道的扇区，主要在寻道时间、旋转延迟、I/O 吞吐量和操作复杂性上存在显著差异。为了提高性能，通常应尽量减少不同磁道之间的访问。

索引树（比如B树，b+树）高度与IO次数的关系

在 B+ 树的索引查询中，I/O 次数的划分可以根据树的层级结构和访问模式来理解。以下是一般情况下的 I/O 次数划分：

1. 树的层级数

   • 每一层通常对应一次 I/O 操作。对于一颗高度为 $h$ 的 B+ 树，通常需要进行 $h$ 次 I/O 来查找到目标数据。

2. 根节点

   • 根节点访问：查询开始时，首先访问根节点，这对应一次 I/O 操作。

3. 中间节点

   • 遍历中间节点：从根节点开始，根据关键字选择下一个子节点，这可能导致多次 I/O 操作。每访问一个中间节点都产生一次 I/O。

4. 叶子节点

   • 叶子节点访问：最终到达叶子节点，进行数据查找或范围查询，这通常也是一次 I/O 操作。

5. 总 I/O 次数

   • 聚合 I/O 操作：如果树的高度为 $h$，则总的 I/O 次数大约为 $h+1$（包括根节点、中间节点和叶子节点的访问）。如果增加数据时导致树的高度增加，I/O 次数也会相应增加。

6. 范围查询的额外 I/O

   • 范围查询：如果查询是范围查询，访问完叶子节点后，可能需要顺序读取相邻的叶子节点，导致额外的 I/O 操作。这个操作的次数取决于范围的大小。

总结：在 B+ 树的索引查询中，I/O 次数主要由树的层级数决定，通常为 $h+1$ 次（其中 $h$ 是树的高度）。范围查询可能会导致额外的 I/O 次数，具体数量依据查询的范围而定。合理设计树的结构可以降低 I/O 次数，提高查询效率。

在 B+ 树的同一层中，通常不会产生多次 I/O 操作：

1. 节点结构

   • 同一层的节点通常已加载到内存：在访问某一层的节点时，如果已经访问过该层的某个节点并将其加载到内存，就会直接在内存中处理，而不会再次产生额外的 I/O 操作。

2. 顺序访问

   • 顺序读取效率高：在遍历同一层的节点时，如果一次访问了一个节点，接下来的节点可以利用内存中的数据，从而降低 I/O 的需求。

3. 磁盘块读写特性

   • 块读取操作：读取一个块（包含多个节点）的操作比单独读取每个节点要高效，通常在读取同一层节点时，会尽量一次读取多个节点，从而减少 I/O 次数。

4. 缓存机制

   • 使用缓存：操作系统和数据库系统通常会实现缓存机制，访问过的节点会存储在内存缓存中。这意味着再访问同一层的节点时，如果缓存命中，就不会发生 I/O 操作。

总结：在 B+ 树的同一层中，由于节点已加载到内存、顺序访问的高效性以及缓存机制，通常不会出现多次 I/O 操作。探测同一层的不同节点时，如果节点在内存中，访问将不会再触发新的 I/O 请求。

联合索引，聚簇索引

联合索引 (Multi-column Index)

• 定义：联合索引是指在多个列上创建的索引，可以加速涉及多个列的查询条件。
• 创建示例：

  CREATE INDEX idx_name ON table_name (column1, column2);
  

• 使用场景：适合查询中经常使用多个列作为条件的情况。

聚簇索引 (Clustered Index)

• 定义：聚簇索引是将表的数据按照索引的顺序存储，意味着数据行的物理顺序与索引顺序相同。
• 特性：在 PostgreSQL 中，每个表只能有一个聚簇索引，因为数据只能按照一种顺序存储。
• 创建示例：

  CREATE INDEX idx_name ON table_name USING btree (column_name);
  CLUSTER table_name USING idx_name;
  

• 注意：在 PostgreSQL 中，创建聚簇索引的同时需要运行 CLUSTER 命令对表进行重新组织。

关键区别

• 联合索引：可以是多列，不影响物理存储顺序。
• 聚簇索引：作用在整个表上，影响数据的物理存储顺序。

postgres判断是不聚簇索引

SELECT
    c.relname AS index_name,
    i.indisclustered AS is_clustered
FROM
    pg_index i
JOIN
    pg_class c ON c.oid = i.indexrelid
WHERE
    i.indrelid = 'your_table_name'::regclass;

indisclustered 列的值为 true 表明该索引是聚簇索引。

总结

PostgreSQL 支持联合索引和聚簇索引，联合索引可以加速多列条件的查询，而聚簇索引控制数据在磁盘上的排列。适当地使用这些索引可以提高查询性能。

‌‌左前缀原则

SQL 查询的执行顺序

SQL 查询的执行顺序通常与编写顺序不同。以下是常见 SQL 语句各个关键字的执行顺序：

SQL 查询执行顺序

1. FROM：选择数据源（表、视图）。
2. JOIN：执行连接操作，结合多个表的数据。
3. WHERE：过滤数据，应用条件限制结果集。
4. GROUP BY：对结果集进行分组。
5. HAVING：对分组后的数据应用条件过滤。
6. SELECT：选择需要返回的列。
7. DISTINCT：去除重复的结果。
8. ORDER BY：对结果集进行排序。
9. LIMIT / OFFSET：限制返回的记录数量或设置起始位置。

示例顺序说明

假设有以下 SQL 查询：

SELECT DISTINCT name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE e.age > 30
GROUP BY e.name
HAVING COUNT(e.id) > 1
ORDER BY e.name
LIMIT 10;

执行顺序解释

1. FROM / JOIN：首先选择 employees 和 departments 表，并根据 department_id 进行连接。
2. WHERE：筛选出年龄大于 30 的员工。
3. GROUP BY：对筛选后的结果按 name 分组。
4. HAVING：过滤掉分组后记录数不大于 1 的组。
5. SELECT：选择需要返回的 name 列。
6. DISTINCT：排除结果中的重复名称。
7. ORDER BY：按名称排序结果集。
8. LIMIT：限制返回的最多 10 条记录。

原文地址：https://blog.csdn.net/newbieJ/article/details/142550037

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！