聚集索引

概念定义

只有一个索引能“成为表”：它的叶页就是数据行，表按该键序物理排列。聚集键上的范围扫描变成相邻页的顺序读；其余索引——二级索引——都指向这个结构。

工作机制

InnoDB 中主键即聚集索引，二级索引叶节点存主键而非行偏移，因此二级命中要再做一次聚集树查找，除非二级索引覆盖查询。SQL Server 允许任选唯一键或非键列做聚集。

使用场景

插入密集表想要单调递增或时间有序的键，让插入变成追加而非分裂；范围过滤的查询想让聚集键与过滤条件对齐；读多写少、依赖二级索引的模型想要覆盖型二级索引避开两段式下降。

常见误区

“每个索引都是聚集的”不成立——表只有一种物理顺序。随机 UUID 主键把插入打散到全树，写入吞吐与缓存效率在规模上劣化；超宽主键会膨胀所有二级索引。重建聚集顺序等于物理重写整表，维护窗口要按此规划。

代码示例

-- 每个邮箱保留 created_at 最新的一行，标记其余为重复
WITH ranked AS (
  SELECT
    id,
    email,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC, id DESC
    ) AS rn
  FROM subscribers
)
SELECT id, email, created_at, (rn = 1) AS is_primary
FROM ranked
ORDER BY email, rn;

-- 一次性清扫：删除全部非首行
WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM subscribers
)
DELETE FROM subscribers s
USING ranked r
WHERE s.id = r.id
  AND r.rn > 1;

-- 上游堵漏：唯一索引阻止重复再次写入
CREATE UNIQUE INDEX CONCURRENTLY idx_subscribers_email
  ON subscribers (email);

export class SegmentTree {
  private readonly size: number;
  private readonly tree: number[];

  constructor(values: readonly number[]) {
    if (!Array.isArray(values)) throw new TypeError("values must be an array");
    for (const value of values) {
      if (typeof value !== "number" || !Number.isFinite(value)) {
        throw new TypeError("values must contain finite numbers");
      }
    }
    this.size = values.length;
    this.tree = new Array<number>(Math.max(4, this.size * 4)).fill(0);
    if (this.size > 0) this.build(values, 1, 0, this.size - 1);
  }

  sum(left: number, right: number): number {
    if (!Number.isInteger(left) || !Number.isInteger(right)) {
      throw new TypeError("range bounds must be integers");
    }
    if (left < 0 || right >= this.size || left > right) throw new RangeError("invalid range");
    return this.query(1, 0, this.size - 1, left, right);
  }

  update(index: number, value: number): void {
    if (!Number.isInteger(index) || index < 0 || index >= this.size) {
      throw new RangeError("index out of range");
    }
    if (typeof value !== "number" || !Number.isFinite(value)) {
      throw new TypeError("value must be a finite number");
    }
    this.assign(1, 0, this.size - 1, index, value);
  }

  private build(values: readonly number[], node: number, start: number, end: number): void {
    if (start === end) {
      this.tree[node] = values[start] ?? 0;
      return;
    }
    const middle = (start + end) >> 1;
    this.build(values, node * 2, start, middle);
    this.build(values, node * 2 + 1, middle + 1, end);
    this.tree[node] = this.tree[node * 2] + this.tree[node * 2 + 1];
  }

  private query(node: number, start: number, end: number, left: number, right: number): number {
    if (left <= start && end <= right) return this.tree[node];
    const middle = (start + end) >> 1;
    let total = 0;
    if (left <= middle) total += this.query(node * 2, start, middle, left, right);
    if (right > middle) total += this.query(node * 2 + 1, middle + 1, end, left, right);
    return total;
  }

  private assign(node: number, start: number, end: number, index: number, value: number): void {
    if (start === end) {
      this.tree[node] = value;
      return;
    }
    const middle = (start + end) >> 1;
    if (index <= middle) this.assign(node * 2, start, middle, index, value);
    else this.assign(node * 2 + 1, middle + 1, end, index, value);
    this.tree[node] = this.tree[node * 2] + this.tree[node * 2 + 1];
  }
}

const tree = new SegmentTree([2, 4, 6, 8, 10]);
console.log(tree.sum(1, 3));
tree.update(2, 0);
console.log(tree.sum(0, 4));
console.log(tree.sum(2, 2));