主页 / dba / 问题 / 60480
Accepted
Jérôme Verstrynge
Asked: 2014-03-09 07:59:07 +0800 CST

我应该为非键列创建索引吗？

  • 772

我在 PostGreSql 数据库中有一个表，定义如下：

CREATE TABLE public."MATCH"(
    "ITEM_A_ID" bigint DEFAULT 0,
    "ITEM_B_ID" bigint DEFAULT 0,
    "OWNER_A_ID" bigint DEFAULT 0,
    "OWNER_B_ID" bigint DEFAULT 0,
    "OTHER_DATA" varchar(100) NOT NULL DEFAULT ''
    CONSTRAINT "MATCH_PK" PRIMARY KEY ("ITEM_A_ID","ITEM_B_ID")
);

它将包含很多行。将在此表上执行许多类似以下的查询：

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id;
SELECT * FROM "MATCH" WHERE "OWNER_B_ID" = owner_b_id;

我正在考虑在OWNER_A_IDand上创建索引OWNER_B_ID，因为这些列不是键。这是一个好主意，如果是，我应该如何创建这些？我应该用两列创建一个索引吗？我应该创建两个索引吗？我应该包括其他列吗？

postgresql index

2 个回答

  1. 请更具体：“它将包含很多行”。多少？数百万，数千或数十亿。“这是不是一个好主意？” 这取决于。

    如果您的查询与您提到的查询类似，则应创建两个 b-tree 索引，每个字段一个。说明在这里：http ://www.postgresql.org/docs/9.1/static/sql-createindex.html

    您应该只为这两个字段创建一个索引，只有当您的所有查询都像：

    SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id AND "OWNER_B_ID"=owner_b_id;

    该索引也适用于以下查询：

    SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id;

    但不是为了

    SELECT * FROM "MATCH" WHERE "OWNER_B_ID" = owner_b_id;
    • 6
  2. Best Answer

    选择足够多的索引通常很困难。在您的情况下，创建两个索引应该很有用。

    如果您的查询始终包含第一列作为条件，您应该只创建一个包含两列的索引：

    SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id [AND "OWNER_B_ID" = owner_b_id]

    整个 B-Tree 是建立在索引中列的顺序之上的！您不能在以下查询中完全使用 a、b 上的多列索引：

    SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id OR "OWNER_B_ID" = owner_b_id
SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = "OWNER_B_ID"

    如果您只使用相等检查，您可能会考虑使用哈希索引。但是 postgresql 有一些缺点，你应该先检查一下。

    在其他 dbms 上，您应该考虑在索引中添加其他列作为数据。如果您查询这些特定列而不是 * 这将很有用，因为 dbms 在使用索引后不需要从表中提供数据。

    一个重要因素：索引会随着时间的推移而碎片化（除非您没有在表上执行任何插入/更新/删除）。请检查您的dba是否安装了一些优化操作。

    请检查文档以获取其他选项，例如 FILLFACTOR 或部分索引：http ://www.postgresql.org/docs/9.3/static/sql-createindex.html

    • 5

相关问题