本文最后更新于 2025-03-25，文章超过7天没更新，应该是已完结了~

① 分页查询代码实现

假设 每页需要 10 条数据，并且我们从两张表中查询，第一页和第二页的查询需要根据不同的 OFFSET 来控制。

查询第一页：

假设第一页的查询结果如下：

  • 第一张表返回 6条 数据。

  • 第二张表返回 10条 数据。

  • 因此第一页的 总数据：6条（来自表1）+ 4条（来自表2）= 10条。

const pageSize = 10; // 每页显示10条数据

// 假设我们有当前的分页页码
let currentPage = 1;

// 第一页查询
function queryPage(currentPage) {
  // 假设当前页需要查询10条数据

  // 每张表的分页 offset
  let offset1 = (currentPage - 1) * pageSize; // 第一张表的 offset
  let offset2 = (currentPage - 1) * pageSize; // 第二张表的 offset

  // 分别查询每张表的数据
  let result1 = queryTable1(offset1, pageSize);  // 第一张表查询数据
  let result2 = queryTable2(offset2, pageSize);  // 第二张表查询数据

  // 合并数据并根据时间或其他字段排序
  let allResults = mergeResults(result1, result2);

  // 返回查询结果
  return allResults;
}

// 假设查询函数，根据 offset 和 limit 查询每张表的数据
function queryTable1(offset, limit) {
  return db.query(`SELECT * FROM table1 ORDER BY createdAt LIMIT ${limit} OFFSET ${offset}`);
}

function queryTable2(offset, limit) {
  return db.query(`SELECT * FROM table2 ORDER BY createdAt LIMIT ${limit} OFFSET ${offset}`);
}

// 合并查询结果，并按字段（如时间）排序
function mergeResults(result1, result2) {
  // 合并两个表的结果
  let combinedResults = [...result1, ...result2];

  // 按照创建时间（或其他字段）排序
  return combinedResults.sort((a, b) => a.createdAt - b.createdAt);
}

查询第二页：

查询第二页时，假设 第一页的查询结果 已经返回了：

  • 第一张表：返回了 6条数据。

  • 第二张表：返回了 4条数据。

此时，第二张表的剩余数据仍然没有被完全查询出来，因此我们要继续从 第二张表 查询剩余的 6条数据。在第二页的查询中，第一张表的 OFFSET 已经到达了 6条数据的末尾，而第二张表的 OFFSET 应该是 4，因为第一页已经查询了前 4 条。

// 查询第二页
function queryPage(currentPage) {
  // 假设第一页已经查询了6条数据，第二张表已经查询了4条

  let offset1 = 6;  // 第一张表，已经取了6条
  let offset2 = 4;  // 第二张表，已经取了4条，剩余6条需要从offset=4开始取

  // 查询第二页的数据
  let result1 = queryTable1(offset1, pageSize);  // 第一张表继续查询
  let result2 = queryTable2(offset2, pageSize);  // 第二张表继续查询剩余数据

  // 合并数据并排序
  let allResults = mergeResults(result1, result2);

  // 返回查询结果
  return allResults;
}

分页逻辑要点：

  1. 每张表独立分页：每张表的分页是独立的。分页时，OFFSET 会随着查询进度而变化，确保每张表的查询从上次的结果继续。

  2. 合并后统一排序：在查询到的数据合并后，按照某一排序字段（如 createdAt）对结果进行排序，以确保合并后数据的顺序是正确的。

  3. 返回分页数据：在合并后，再进行分页切割，确保每次返回的是当前页的数据。

② 在高并发环境下，如何确保每查询准确

1. 使用事务隔离级别（Transaction Isolation Levels）

在数据库中，事务隔离级别决定了一个事务在执行过程中对其他事务的可见性。在高并发情况下，合理配置事务隔离级别可以避免一些并发问题。

常见的隔离级别：

  • READ COMMITTED：确保查询的数据是已提交的数据，避免脏读。但是，仍然可能发生不可重复读和幻读。

  • REPEATABLE READ：确保在事务期间读取的数据始终一致，防止不可重复读。但是，可能仍然存在幻读问题。

  • SERIALIZABLE：最高级别的隔离，完全避免脏读、不可重复读和幻读，但会影响性能，导致更高的延迟和吞吐量下降。

推荐隔离级别：

  • 如果对 一致性要求较高，尤其是涉及分页查询时，REPEATABLE READ 或 SERIALIZABLE 可能是更好的选择。虽然这些隔离级别会带来一定的性能开销，但它们可以有效防止数据不一致的问题，特别是在高并发场景中。

2. 使用快照隔离（Snapshot Isolation）

如果你的数据库支持 快照隔离（如 SQL Server、PostgreSQL 等），可以通过 快照隔离 来避免 幻读 问题。

  • 快照隔离通过为每个事务提供一致的 数据视图 来确保分页查询的结果在查询开始时的一致性，不受其他事务并发修改数据的影响。

  • 这意味着，在一个分页查询中，即使其他事务正在修改表数据，你的查询结果仍然是基于事务开始时的数据视图。

优点：

  • 避免了读写冲突，确保分页查询结果的稳定性。

  • 无需显式加锁，因此相对于高隔离级别（如 SERIALIZABLE）性能损耗较小。

3. 使用时间戳（Timestamp）或者版本号机制

另一种常见的确保分页查询一致性的方法是 使用时间戳 或 版本号 来标记数据的变化，并根据时间戳或版本号进行数据的排序和一致性检查。

  • 在数据表中增加 版本号 或 更新时间戳 字段，当每次数据发生更改时，自动更新该字段。

  • 在查询时，分页查询可以依赖于 created_at 或 updated_at 字段来确保数据的一致性，避免查询过程中因为数据变化导致不一致的结果。

示例：

  1. 在查询时，使用 ORDER BY created_at 或 ORDER BY version 来进行排序和分页。

  2. 当进行分页查询时，可以确保每个分页的查询基于某个时间点的一致数据（例如，created_at >= '2024-01-01'）。

优点：

  • 对数据变化进行标记，能够确保跨分页查询时的数据一致性。

  • 不需要复杂的事务管理，较为简单高效。

4. 使用乐观锁和悲观锁

乐观锁

乐观锁假设数据冲突的概率较低，因此在查询和更新时不加锁。每次更新时，都会检查数据在操作期间是否被其他事务修改过。通常实现方式是使用 版本号 或 时间戳 字段。

  • 在分页查询时，读取数据时记录下数据的 版本号 或 时间戳。

  • 更新操作时，如果版本号与当前一致，则允许操作，否则就 重试 或 拒绝操作。

悲观锁

悲观锁假设数据冲突的概率较高，因此在数据查询时，会使用 锁机制 来保证数据的一致性。例如，使用 SELECT FOR UPDATE 语句锁定查询结果，确保其他事务不能修改这些数据，直到当前事务完成。

  • 在分页查询时，可以对每个数据行加锁，确保查询结果在读取时不会被其他事务修改。

优缺点：

  • 乐观锁适合冲突少的场景，能提高性能，但在数据量大时可能会有更多的重试。

  • 悲观锁能确保查询一致性，但会降低并发性能，特别是在高并发情况下。

5. 分页查询的 游标 (Cursor) 方法

与基于 OFFSET 的分页方法相比，使用游标（Cursor）分页查询可以避免数据 不一致 或 重复 的问题。

  • 游标分页 通过保留查询的 指针（游标），使得每次查询都可以从上次查询的结果位置继续。

  • 游标分页不依赖于 OFFSET，避免了在数据插入或删除时导致结果集发生变化的问题。

游标分页的优点：

  • 一致性：每次分页查询时，从上次查询位置继续，避免了数据变化（如新增、删除）导致结果不一致的问题。

  • 性能优化：对于大量数据，游标分页比 OFFSET 查询更高效，减少了数据库的扫描量。

6. 定期快照和数据快照隔离

如果应用场景允许，可以定期创建数据库 快照，即 数据快照隔离。在分页查询时，可以基于某一时刻的快照进行查询，从而避免高并发数据修改导致的 数据不一致 问题。

快照隔离实现：

  • 通过 数据复制 或 数据库快照，确保分页查询期间数据的一致性。

  • 每次分页查询时，都从数据库快照中读取数据，避免其他事务的并发写入影响查询结果。

7. 使用分布式数据库的强一致性保障

在分布式数据库中，例如 Google Spanner 或 CockroachDB，提供 分布式事务 和 强一致性保障。这些数据库确保数据在多个节点上的一致性，避免了数据不同步或数据丢失的情况。

  • 分布式数据库通常使用 Paxos 或 Raft 协议来确保在高并发情况下的 数据一致性 和 事务原子性。

总结：确保分页查询一致性的关键策略

在高并发环境下，分页查询的一致性和准确性是关键问题。为了确保查询结果的一致性，可以采用以下几种策略：

  1. 使用事务隔离级别：如 REPEATABLE READ 或 SERIALIZABLE，确保查询一致性。

  2. 使用快照隔离：通过数据库的快照机制来确保数据的一致性。

  3. 使用时间戳或版本号：通过标记数据的更新时间或版本，确保分页数据的一致性。

  4. 乐观锁与悲观锁：通过锁机制（乐观锁、悲观锁）来确保数据一致性。

  5. 游标分页：通过游标实现高效且一致的分页查询。

  6. 使用分布式数据库的强一致性保障：在分布式系统中使用强一致性方案来保证数据的一致性。

每种策略有其适用的场景，实际中通常需要结合具体的应用需求和数据库架构来选择合适的策略。

③ 创建合适的索引

在高并发环境下，创建 合适的索引 是提升数据库查询性能的关键。索引能够显著提高查询效率，特别是对于 大数据量 和 复杂查询 的场景。但是，创建不合理的索引也可能导致性能下降（如影响写入性能或增加存储开销）。因此，正确选择和设计索引对于数据库优化至关重要。

如何创建合适的索引

以下是创建和优化索引的关键策略：

1. 基于查询需求选择索引

单列索引

  • 适用场景：当查询涉及单一列的查找、排序、过滤时，可以考虑创建单列索引。

  • 示例：

  • SELECT * FROM orders WHERE order_id = 123;

  • SELECT * FROM users WHERE email = 'test@example.com';

复合索引（多列索引）

  • 适用场景：当查询涉及多个列时，特别是同时使用多个条件（如过滤、排序）时，创建复合索引（即多列索引）通常可以显著提升性能。

  • 设计原则：

  • 字段顺序：复合索引的字段顺序要与查询中 WHERE 子句和 ORDER BY 子句的使用顺序相匹配。

  • 最常查询的字段排在前面：复合索引中最常用的字段（比如经常用作过滤条件的字段）应排在索引的前面。

  • 示例：

CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

这个索引在查询时会提高如下类型的查询效率：

SELECT * FROM orders WHERE user_id = 123 AND order_date BETWEEN '2024-01-01' AND '2024-12-31';

覆盖索引（Covering Index）

  • 适用场景：当查询的列都包含在索引中时，数据库可以直接从索引中返回结果，而无需访问表的数据页。这被称为 覆盖索引，能够大大提高查询性能。

  • 设计原则：

  • 在创建复合索引时，将查询中需要的所有字段（特别是 SELECT 中的字段）包含到复合索引中。

示例：

CREATE INDEX idx_orders_user_date_status ON orders(user_id, order_date, status);

这可以覆盖以下查询：

SELECT user_id, order_date, status FROM orders WHERE user_id = 123 AND order_date BETWEEN '2024-01-01' AND '2024-12-31';

2. 分析查询模式

在创建索引时，最重要的就是分析数据库中的 查询模式，以确保索引能够有效地支持常见的查询。

  • 常用的查询模式：

  • 过滤条件：如 WHERE 子句中的字段。

  • 排序：如 ORDER BY 子句中的字段。

  • 连接条件：如 JOIN 操作中连接的字段。

  • 范围查询：如 BETWEEN、>, < 等运算符。

索引设计示例：

假设你有一个电商系统的 orders 表，包含字段 order_id, user_id, order_date, status, amount 等。

  • 查询 1：查询某用户的所有订单，按订单日期排序

SELECT * FROM orders WHERE user_id = 123 ORDER BY order_date DESC;

优化方案：创建复合索引 (user_id, order_date)，这样不仅能加速过滤，还能加速排序。

CREATE INDEX idx_user_order_date ON orders(user_id, order_date);

  • 查询 2：查询所有已完成状态的订单，并按金额排序

SELECT * FROM orders WHERE status = 'completed' ORDER BY amount DESC;

优化方案：创建复合索引 (status, amount)，能加速过滤和排序操作。

CREATE INDEX idx_status_amount ON orders(status, amount);

  • 查询 3：查询某时间段内的所有订单

SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

优化方案：创建单列索引 order_date，加速范围查询。

CREATE INDEX idx_order_date ON orders(order_date);

3. 考虑写入性能

虽然索引能加速查询，但每次插入、更新或删除时，索引也需要维护。因此，创建过多的索引或不必要的索引会影响 写入性能。

优化策略：

  • 避免创建不必要的索引：如果某个字段只用于查询一次，或者查询不频繁，避免为该字段单独创建索引。

  • 合并索引：如果多个单列索引的组合在查询中频繁出现，可以考虑将其合并为复合索引，从而减少索引的数量。

示例：

如果查询中经常用到 user_id 和 status 这两个字段进行组合查询，而 user_id 和 status 上有单独的索引，可以考虑将这两个索引合并成一个复合索引，从而减少索引维护的开销。

4. 使用前缀索引（Prefix Index）

对于文本类型字段，特别是 VARCHAR 类型，使用 前缀索引 可以减少索引的存储开销。

  • 适用场景：当查询经常涉及到字符串匹配（如 LIKE 查询）时，可以创建前缀索引。

  • 示例：如果你经常按用户的 email 字段查询，可以创建一个前缀索引：

CREATE INDEX idx_email_prefix ON users(email(10));

这会为 email 字段的前 10 个字符创建索引，从而避免创建整个字段的索引。

5. 定期维护和优化索引

随着数据的增多和查询模式的变化，索引可能会变得不再高效，或者可能会发生碎片化。为了确保索引的高效性，定期的索引 优化 和 重建 非常重要。

  • 重建索引：对于碎片化严重的索引，可以通过重建索引来恢复其性能。

ALTER INDEX idx_orders_user_date REBUILD;

  • 更新统计信息：保持统计信息的最新，数据库优化器才能做出正确的查询计划。

ANALYZE orders;

6. 使用合适的索引类型

不同类型的索引适用于不同的场景。数据库提供了多种索引类型，如 B-tree 索引、哈希索引、全文索引 等。选择适合查询的索引类型可以进一步优化查询性能。

常见的索引类型：

  • B-tree 索引：最常见的索引类型，适用于等值查询、范围查询、排序等操作。

  • 哈希索引：适用于等值查询，但不支持范围查询或排序。

  • 全文索引：用于全文搜索，适用于文本搜索。

总结

创建合适的索引是数据库性能优化的重要组成部分。在高并发环境下，通过分析查询模式、选择合适的索引类型、以及定期优化索引，可以显著提高查询性能并减少数据库的负载。

  • 基于查询需求创建索引：根据查询中涉及的字段和常见的查询模式设计索引。

  • 复合索引与覆盖索引：合理使用复合索引和覆盖索引，减少数据库访问和提高查询效率。

  • 考虑写入性能：尽量减少不必要的索引，避免对写入操作造成过多影响。

  • 定期优化索引：定期重建索引和更新统计信息，确保索引始终处于最佳状态。

通过合理设计索引，可以确保在高并发环境下，数据库查询的 一致性、准确性 和 性能 达到最优。