主页 / dba / 问题 / 344499
Accepted
Purushottam Nawale
Asked: 2025-01-02 15:04:41 +0800 CST

在包含 7000 万条记录的大型表上高效创建索引和主键

  • 772

我正在处理一个包含大约 7000 万条记录的表。我需要在这个表上创建一个主键和几个索引。我使用的 SQL 查询如下：

BEGIN;
ALTER TABLE table_name ADD CONSTRAINT table_name_pkey PRIMARY KEY (uniqueid);
CREATE INDEX IF NOT EXISTS table_name_column1_idx ON table_name (column1);
CREATE INDEX IF NOT EXISTS table_name_column2_idx ON table_name (column2);
CREATE INDEX IF NOT EXISTS table_name_column3_idx ON table_name (column3);
CREATE INDEX IF NOT EXISTS table_name_column4_idx ON table_name (column4);
CREATE INDEX IF NOT EXISTS table_name_column5_idx ON table_name (column5);
COMMIT;

但是，执行这些查询需要花费相当长的时间。我没有任何资源限制，因此我正在寻找优化此过程的方法。具体来说，我正在考虑从 Python Django 应用程序并行或同时运行这些 SQL 查询。

我的问题是：

1.我可以使用哪些策略来最小化锁并加快索引过程？
2.是否有任何最佳实践或工具可以帮助有效地管理大型表上的多个索引的创建？
3.在索引创建期间使用 BEGIN; COMMIT; 是否有任何性能优势？

如有任何建议或推荐我将不胜感激。

postgresql

2 个回答

  1. 对于 来说CREATE INDEX，这很简单：不要写CREATE INDEX，总是写CREATE INDEX CONCURRENTLY。CREATE INDEX速度更快，需要的资源更少，但它会锁定表进行写入，因此在生产中，总是使用 CONCURRENTLY。

    但我要单独说明一下：始终检查命令的返回代码。由于任何原因中断的 CREATE INDEX CONCURRENTLY 都会留下无效的索引，该索引无法用于查询，但重复执行CREATE INDEX IF NOT EXISTS将无法完成其创建。

    在添加主键的情况下，可以使用 CONCURRENTLY，但不太明显。需要将命令拆分成几部分。

    ALTER TABLE table_name ADD CONSTRAINT table_name_pkey PRIMARY KEY (uniqueid);

    这在功能上是等效的，无需长锁：

    create unique index concurrently table_name_new_pkey on table_name(uniqueid);
begin;
set local statement_timeout to '1s';
alter table table_name ADD CONSTRAINT table_name_pkey PRIMARY KEY USING INDEX table_name_new_pkey;
commit;

    请注意，主键隐式要求唯一索引，并且所有主键字段都应声明为 NOT NULL。迁移前请检查表定义。如有必要，并且您拥有 postgresql 12 或更高版本，则可以通过这种方式设置不带长锁的 NOT NULL 。

    在索引创建期间使用 BEGIN; COMMIT; 是否有任何性能优势？

    不，相反，它不允许使用任何方法来最小化锁定。

    • 4
  2. Best Answer

    索引创建通常会阻止对目标表的所有写入。为了避免此类锁定，您可以使用CONCURRENTLY子句，该子句允许在索引创建期间进行并发写入。虽然与标准方法相比，这种方法需要更多努力并且需要更长的时间才能完成，但它是生产环境中的首选。

    CREATE INDEX CONCURRENTLY IF NOT EXISTS table_name_column1_idx ON table_name (column1);

    要减少创建索引所需的时间，请考虑增加maintenance_work_mem和max_parallel_maintenance_workers。此设置可以在服务器级别或会话级别进行调整。但是，同时创建多个索引会给系统的 I/O 和内存资源带来压力，因此最好按顺序构建它们。

    CONCURRENTLY由于该方法固有处理多个事务以防止锁定，因此不需要明确定义索引创建语句。

    如果它适合查询工作负载，还可以考虑创建compound indexes或covering indexes代替单独的索引。

    • 1

相关问题