印刻数据库

外观

PostgreSQL 索引命名规范 ​

核心概念 ​

索引命名规范是数据库设计和运维的重要组成部分，良好的命名规范有助于：

  • 提高代码可读性和可维护性
  • 快速识别索引类型和用途
  • 避免索引命名冲突
  • 便于自动化脚本生成和管理
  • 统一团队开发规范

命名规则 ​

1. 命名格式 ​

索引命名应遵循以下通用格式：

{table_name}_{column_names}_{index_type}_idx

其中：

  • table_name：表名，使用小写字母
  • column_names：索引列名，多个列名用下划线连接
  • index_type：索引类型缩写（可选，用于区分特殊索引类型）
  • idx：索引后缀，统一使用小写

2. 具体索引类型命名规范 ​

主键索引 ​

主键索引由系统自动创建，命名格式固定为：

pk_{table_name}

示例：

sql
-- 自动创建的主键索引
CREATE TABLE users (
    id SERIAL PRIMARY KEY, -- 自动创建 pk_users 索引
    username VARCHAR(50) NOT NULL
);

唯一索引 ​

唯一索引用于确保列值的唯一性，命名格式：

uk_{table_name}_{column_names}

示例：

sql
CREATE UNIQUE INDEX uk_users_username ON users(username);
CREATE UNIQUE INDEX uk_orders_order_no ON orders(order_no);
CREATE UNIQUE INDEX uk_employees_email_phone ON employees(email, phone);

普通索引 ​

普通索引用于加速查询，命名格式：

idx_{table_name}_{column_names}

示例：

sql
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

复合索引 ​

复合索引包含多个列，命名时按列顺序列出所有列名：

idx_{table_name}_{column1}_{column2}_{column3}

示例：

sql
CREATE INDEX idx_orders_customer_id_created_at ON orders(customer_id, created_at);
CREATE INDEX idx_products_category_id_price ON products(category_id, price);

部分索引 ​

部分索引只包含满足特定条件的行，命名格式：

idx_{table_name}_{column_names}_partial

示例：

sql
CREATE INDEX idx_orders_status_created_at_partial ON orders(status, created_at) WHERE status = 'pending';
CREATE INDEX idx_users_is_active_email_partial ON users(email) WHERE is_active = true;

表达式索引 ​

表达式索引基于列的计算结果，命名格式：

idx_{table_name}_{expression_description}

示例：

sql
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
CREATE INDEX idx_orders_year_month ON orders(EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at));

全文索引 ​

全文索引用于文本搜索，命名格式：

idx_{table_name}_{column_name}_tsvector

示例：

sql
CREATE INDEX idx_articles_content_tsvector ON articles USING GIN(to_tsvector('english', content));

最佳实践 ​

1. 命名长度限制 ​

  • 索引名称不宜过长，建议不超过63个字符（PostgreSQL标识符的最大长度）
  • 对于长表名或多列索引，可适当缩写表名或列名
  • 缩写应保持一致和可读性，如：customer → cust，transaction → trans

2. 命名一致性 ​

  • 同一项目中应保持统一的命名规范
  • 团队应共同遵守并文档化命名规则
  • 建议使用自动化工具或脚本生成符合规范的索引名

3. 避免冗余 ​

  • 索引名称应准确反映其用途，避免不必要的冗余
  • 不要在索引名中重复表名的前缀或后缀
  • 对于单表的唯一索引，可以省略表名（如果不会冲突）

4. 便于维护 ​

  • 索引名称应便于DBA快速识别和管理
  • 建议在数据库文档中记录索引的用途和创建原因
  • 定期审查和清理无用的索引

常见问题（FAQ） ​

Q1：是否需要为所有索引显式命名？ ​

A1：对于主键和唯一约束，PostgreSQL会自动生成索引名，但建议显式命名以保持一致性。对于普通索引，应始终显式命名，避免系统生成的随机名称。

Q2：如何处理长表名和多列索引？ ​

A2：可以采用以下方法：

  1. 适当缩写表名和列名（保持可读性）
  2. 对于超过3个列的复合索引，考虑只包含核心列名
  3. 使用更简洁的表达式描述

示例：

sql
-- 长表名缩写
CREATE INDEX idx_cust_orders_order_no ON customer_orders(order_no);

-- 多列索引简化
CREATE INDEX idx_users_cre_upd ON users(created_at, updated_at);

Q3：如何区分不同类型的索引？ ​

A3：通过索引名中的前缀或后缀来区分：

  • pk_：主键索引
  • uk_：唯一索引
  • idx_：普通索引
  • _partial：部分索引
  • _tsvector：全文索引

Q4：索引名是否区分大小写？ ​

A4：PostgreSQL默认将标识符转换为小写，因此建议始终使用小写字母命名索引，避免大小写带来的混淆。

Q5：如何重命名已存在的索引？ ​

A5：可以使用ALTER INDEX命令重命名索引：

sql
ALTER INDEX old_index_name RENAME TO new_index_name;

建议在非业务高峰期执行此操作，并确保相关应用代码已同步更新。