Oracle（21）什么是聚集索引和非聚集索引？

在数据库管理系统中，索引是一种用于加速数据检索的结构。主要有两种类型的索引：聚集索引（Clustered Index）和非聚集索引（Non-Clustered Index）。它们在数据存储和检索方面有不同的实现和用途。

聚集索引（Clustered Index）

聚集索引决定了表中数据的物理存储顺序。一个表只能有一个聚集索引，因为数据行只能有一种物理排序方式。聚集索引通常用于主键，因为它能提高查询性能。

特点

1. 物理排序：数据行按照索引键的顺序物理存储。
2. 唯一性：每个表只能有一个聚集索引。
3. 性能：对于范围查询和排序操作，聚集索引性能较高。

创建聚集索引的示例

-- 创建一个表
CREATE TABLE employees (employee_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department VARCHAR(50)
);-- 创建聚集索引
CREATE CLUSTERED INDEX idx_employee_id
ON employees(employee_id);

在这个示例中，employee_id是主键，并且是聚集索引的一部分。数据行将按照employee_id进行物理排序。

非聚集索引（Non-Clustered Index）

非聚集索引不改变表中数据的物理存储顺序。它创建一个单独的结构（通常是B-tree），该结构包含索引键和指向数据行的指针。一个表可以有多个非聚集索引。

特点

1. 逻辑排序：数据行的物理存储顺序不变，但索引键在索引结构中排序。
2. 多个索引：每个表可以有多个非聚集索引。
3. 性能：对于精确查找和特定列的查询，非聚集索引性能较高。

创建非聚集索引的示例

-- 创建一个表
CREATE TABLE employees (employee_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department VARCHAR(50)
);-- 创建非聚集索引
CREATE NONCLUSTERED INDEX idx_last_name
ON employees(last_name);-- 创建另一个非聚集索引
CREATE NONCLUSTERED INDEX idx_department
ON employees(department);

在这个示例中，last_name和department列上创建了非聚集索引。尽管数据行的物理顺序不变，但索引加速了基于这些列的查询。

查询索引信息

可以通过系统视图查询索引的相关信息：

-- 查询所有索引
SELECT name AS index_name,type_desc AS index_type,is_unique,is_primary_key
FROM sys.indexes
WHERE object_id = OBJECT_ID('employees');

示例

以下是一个完整的示例，展示了如何创建、使用和查询聚集索引和非聚集索引：

-- 创建表
CREATE TABLE employees (employee_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department VARCHAR(50)
);-- 创建聚集索引
CREATE CLUSTERED INDEX idx_employee_id
ON employees(employee_id);-- 创建非聚集索引
CREATE NONCLUSTERED INDEX idx_last_name
ON employees(last_name);-- 插入数据
INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (1, 'Alice', 'Smith', 'HR'),(2, 'Bob', 'Johnson', 'IT'),(3, 'Charlie', 'Brown', 'Finance');-- 查询表数据
SELECT * FROM employees;-- 查询索引信息
SELECT name AS index_name,type_desc AS index_type,is_unique,is_primary_key
FROM sys.indexes
WHERE object_id = OBJECT_ID('employees');

总结

聚集索引和非聚集索引是提高数据库查询性能的重要工具。聚集索引决定了表中数据的物理存储顺序，适用于主键和范围查询；非聚集索引则创建一个单独的结构，用于加速特定列的查询。理解和正确使用这两种索引类型，对于优化数据库性能至关重要。