MySQL - 聚簇索引

MySQL - 理解聚簇索引

索引是数据库搜索引擎可以用来加速数据检索的特殊查找表。把它们想象成一本书的背面索引。您无需阅读整本书来查找某个主题，而是通过索引直接跳到正确的页面。MySQL 索引主要分为两种类型：聚簇索引（clustered index）和非聚簇索引（non-clustered index，也称为二级索引 secondary index）。

什么是聚簇索引？

聚簇索引决定了表中数据的物理顺序。由于数据行本身只能以一种物理顺序排序，因此一个表只能有一个聚簇索引。

电话簿类比： 一本印刷版的电话簿是聚簇索引的完美现实世界示例。条目（数据）按姓氏（索引列）进行物理排序。当您查找“Smith”时，您是直接在物理排序的数据中导航。您不需要在单独的索引中查找“Smith”来找到页码；数据本身就是索引。

MySQL (InnoDB) 中的聚簇索引

在 MySQL 中，聚簇索引的概念特定于 InnoDB 存储引擎，它是现代 MySQL 版本的默认引擎。MyISAM（较旧的存储引擎）不使用聚簇索引。

InnoDB 没有单独的 CREATE CLUSTERED INDEX 命令。相反，它根据以下层次结构自动创建和管理聚簇索引：

主键（Primary Key）： 如果表有 PRIMARY KEY，InnoDB 将其用作聚簇索引。
第一个 UNIQUE NOT NULL 索引： 如果没有 PRIMARY KEY，InnoDB 会使用第一个只包含 NOT NULL 列的 UNIQUE 索引作为聚簇索引。
隐藏的系统生成 ID： 如果表既没有 PRIMARY KEY 也没有合适的 UNIQUE 索引，InnoDB 会在内部生成一个隐藏的聚簇索引，名为 GEN_CLUST_INDEX，它基于一个包含行 ID 值的合成 6 字节列。

最佳实践： 始终为您的 InnoDB 表显式定义 PRIMARY KEY。不建议依赖隐式隐藏索引，因为您无法控制它。

示例：创建带有聚簇索引的表

让我们创建一个 users 表。通过将 id 定义为 PRIMARY KEY，我们隐式地告诉 InnoDB 将其用作聚簇索引。

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY (username)
) ENGINE=InnoDB;

在这个表中，磁盘上的数据将根据 id 列进行物理排序。

我们可以使用 SHOW INDEX 命令验证表上的索引：

SHOW INDEX FROM users;

输出分析

输出将显示两个索引：

*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: users
   Non_unique: 0
     Key_name: username
 Seq_in_index: 1
  Column_name: username
    Collation: A
  Cardinality: 0
    ...

Key_name: PRIMARY 的索引是我们的聚簇索引。username 索引是一个非聚簇（或二级）索引。

聚簇索引与非聚簇索引的对比

特性	聚簇索引	非聚簇（二级）索引
每个表的数量	仅一个	多个
数据存储	对表的物理数据行进行排序。索引的叶子节点包含实际数据。	具有独立于数据行的单独结构。叶子节点包含指向数据行（聚簇键值）的指针。
主要目的	组织整个表以实现快速访问。	为非主键列上的高效搜索提供替代排序顺序。
查找速度	对于聚簇键的查找和范围查询极其快速，因为数据就在那里。	需要额外的步骤：首先，在二级索引中找到指针，然后使用该指针在聚簇索引中查找数据。

性能影响与最佳实践

选择聚簇索引（即您的主键）对性能有着显著影响：

顺序键与随机键： 始终优先选择窄（narrow）、唯一且单调递增（ever-increasing/monotonic）的主键。AUTO_INCREMENT（自增）整数是理想的选择。这使得新行能够高效地追加到表的末尾。使用像 UUID (v4) 这样的随机值作为主键对性能来说是灾难性的，因为它会强制 InnoDB 将新行插入到表的随机位置，导致页分裂（page splits）和大量的磁盘 I/O。
二级索引大小： 每个二级索引条目都存储一份聚簇键值的副本，以便指向数据行。因此，一个大的聚簇键（例如，长字符串）会使所有二级索引的大小膨胀，浪费空间并降低效率。
范围查询： 对聚簇键列的范围查询（例如，WHERE id BETWEEN 100 AND 200）非常快，因为所需数据在磁盘上是物理上连续存储的。