sql-show-indexes

SQL - 如何显示索引

为什么要显示索引？

数据库索引对于查询性能至关重要。它们就像书中的索引一样，允许数据库快速查找数据而无需扫描整个表。作为开发人员或数据库管理员，你通常需要列出表上的索引，以便：

• 验证模式： 确保已按预期创建了正确的索引。
• 故障排除性能： 分析慢查询是否缺少必要的索引或使用了低效的索引。
• 优化查询： 在编写或重构复杂查询之前，了解可用的索引。
• 管理数据库对象： 识别可以删除的未使用或冗余索引，以节省空间并减少写入开销。

标准方法：INFORMATION_SCHEMA

获取索引信息最便携和标准的方法是查询 INFORMATION_SCHEMA.STATISTICS 视图。此模式由 ANSI SQL 标准定义，在大多数现代关系数据库（包括 PostgreSQL、MySQL 和 SQL Server）中都可用。

语法

SELECT
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    NON_UNIQUE
FROM
    INFORMATION_SCHEMA.STATISTICS
WHERE
    TABLE_SCHEMA = 'your_database_name'
    AND TABLE_NAME = 'your_table_name';

示例

首先，让我们创建一个包含主键（会自动获得索引）和多列索引的示例表。

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    DepartmentID INT NOT NULL
);

-- 在 LastName 和 FirstName 上创建非唯一索引
CREATE INDEX IX_Employees_Name ON Employees(LastName, FirstName);

现在，让我们查询 INFORMATION_SCHEMA 来查看我们的索引（示例使用通用模式名 public，请根据需要调整）。

SELECT
    INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX, NON_UNIQUE
FROM
    INFORMATION_SCHEMA.STATISTICS
WHERE
    TABLE_SCHEMA = 'public'
    AND TABLE_NAME = 'employees';

输出（概念）

输出将显示每个索引中的每一列。

索引名称	列名	索引内序号	非唯一
PRIMARY	ID	1	0
IX_Employees_Name	LastName	1	1
IX_Employees_Name	FirstName	2	1

SEQ_IN_INDEX 表示列在多列索引中的位置。NON_UNIQUE 对于唯一索引是 0（假），对于非唯一索引是 1（真）。

数据库特定方法

尽管 INFORMATION_SCHEMA 是标准，但大多数数据库也提供其自己的原生命令或系统视图，这些有时可以提供更详细的、特定于供应商的信息。

在 PostgreSQL 中显示索引

在 psql 命令行工具中，你可以使用 \d 元命令来获取用户友好的视图。

\d your_table_name

-- 示例：
\d employees

此命令显示表列、索引、约束和其他详细信息。要以编程方式查询，你可以使用 pg_indexes 系统视图。

SELECT * FROM pg_indexes WHERE tablename = 'employees';

在 MySQL 中显示索引

MySQL 提供了方便的 SHOW INDEX 语句。

SHOW INDEX FROM TableName;

-- 示例：
SHOW INDEX FROM Employees;

在 SQL Server 中显示索引

一种传统方法是使用 sp_helpindex 系统存储过程。

EXEC sp_helpindex 'TableName';

-- 示例：
EXEC sp_helpindex 'dbo.Employees';

对于更详细和现代的编程访问，推荐查询 sys.indexes 等系统目录视图。

SELECT
    i.name AS index_name,
    c.name AS column_name,
    ic.key_ordinal
FROM
    sys.indexes i
INNER JOIN
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE
    i.object_id = OBJECT_ID('dbo.Employees');

实际应用：将索引与查询性能联系起来

列出索引是第一步。下一步是查看数据库是否实际使用了它们。这通过 EXPLAIN 或 EXPLAIN ANALYZE 命令完成（语法因数据库而异）。

-- PostgreSQL / MySQL 示例
EXPLAIN ANALYZE SELECT * FROM Employees WHERE LastName = 'Smith';

当你运行此命令时，查询计划将告诉你是否使用了“索引扫描（Index Scan）”（好，它使用了你的索引）或“顺序扫描（Sequential Scan）”/“全表扫描（Table Scan）”（不好，它读取了整个表）。通过列出你的索引并分析查询计划，你可以有效地诊断和解决性能瓶颈。