sql-non-clustered-index

SQL 索引：非聚集索引

什么是数据库索引？

数据库索引是一种数据结构，用于提高数据库表上的数据检索操作速度。可以将其想象成一本书后面的索引。您不必阅读整本书（全表扫描）来查找某个主题，而是可以在索引中查找该主题，索引会告诉您要翻到哪个精确的页码（数据的位置）。这要快得多。

聚集索引与非聚集索引

数据库通常有两种主要类型的索引，它们的主要区别在于数据存储方式。

• 聚集索引： 此索引决定了表中数据的物理顺序。由于数据只能以一种物理方式排序，因此每个表只能有一个聚集索引。在许多数据库（如 MySQL 的 InnoDB 和 SQL Server）中，主键会自动成为聚集索引。
• 非聚集索引： 此索引的结构独立于数据行。它包含索引列的值和一个指向相应数据行（即“行定位器”）的指针。由于它是一个独立的结构，因此可以在单个表上拥有多个非聚集索引。

非聚集索引的工作原理

当您在列（例如 City）上创建非聚集索引时，数据库会创建一个新的、已排序的所有城市值列表。此列表中的每个条目都带有一个指向主表中完整数据行的指针。

当您运行 SELECT * FROM Customers WHERE City = 'Mumbai' 时，数据库可以：

1. 快速搜索已排序的非聚集索引以查找 ‘Mumbai’。
2. 找到与 ‘Mumbai’ 相关联的指针。
3. 使用指针直接转到主表中的数据行并检索它们。

在 MySQL 的 InnoDB 引擎中，除主键之外的所有索引都是二级索引，它们在功能上是非聚集的。二级索引中的“指针”实际上是行的主键值。然后数据库使用该主键在聚集索引中查找行。

标准语法

创建索引的标准 SQL 语法（通常默认是非聚集索引）很简单。

CREATE INDEX index_name
ON table_name (column_name);

示例：为 Customers 表创建索引

我们使用一个 Customers 表。在大型、未索引的表上，按 City 筛选的查询会很慢。

CREATE TABLE Customers (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Age INT NOT NULL,
    City VARCHAR(100),
    Email VARCHAR(100) UNIQUE
);

-- Inserting data...
INSERT INTO Customers (Name, Age, City, Email) VALUES
    ('Muffy', 24, 'Indore', 'muffy@example.com'),
    ('Ramesh', 32, 'Ahmedabad', 'ramesh@example.com'),
    ('Komal', 22, 'Hyderabad', 'komal@example.com'),
    ('Khilan', 25, 'Delhi', 'khilan@example.com'),
    ('Chaitali', 25, 'Mumbai', 'chaitali@example.com');

让我们在 City 列上创建一个非聚集索引以加快查找速度。

-- 创建索引以加快按城市搜索的速度
CREATE INDEX idx_customers_city ON Customers(City);

执行此命令不会改变您通过 SELECT * 看到的数据。表的物理顺序保持不变。它只是在后台创建了独立的索引结构。

复合（多列）索引

您也可以在多个列上创建索引。这对于同时按多个列进行筛选的查询很有用。

-- 此索引针对按 City AND Age 筛选的查询进行了优化
CREATE INDEX idx_customers_city_age ON Customers(City, Age);

列的顺序很重要！ (City, Age) 上的索引对于带有 WHERE City = '...' AND Age = '...' 或仅 WHERE City = '...' 的查询最有效。对于只有 WHERE Age = '...' 的查询，它的效果会较差。

如何验证索引是否被使用

仅仅创建索引并不能保证数据库会使用它。查询优化器 (query optimizer) 会做出这个决定。要检查，您可以使用 EXPLAIN（或 EXPLAIN ANALYZE）命令，它会显示查询的执行计划 (execution plan)。

EXPLAIN SELECT ID, Name FROM Customers WHERE City = 'Mumbai';

EXPLAIN 的输出是数据库特定的，但您应该查找指示您的索引（例如 idx_customers_city）正在被使用的信息。如果您看到“Full Table Scan”（全表扫描），则表示数据库正在读取整个表，并且该查询未使用索引。

索引的权衡

索引并非没有成本。它们伴随着您必须考虑的权衡。

优点

• 更快的读取速度： 显著加快在索引列上带有 WHERE、JOIN 和 ORDER BY 子句的 SELECT 查询。

缺点

• 更慢的写入速度： 每次您 INSERT、UPDATE 或 DELETE 一行时，数据库还必须更新该表上的所有索引。过多的索引会降低写入操作的速度。
• 存储空间： 索引占用磁盘空间。对于大型表，索引可能相当大。
• 维护开销： 数据库需要维护索引结构，这会增加少量开销。

关键在于制定策略：为频繁用于搜索和连接的列创建索引，但要避免过度索引表，特别是写入流量大的表。