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'); 

总结

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