唯一索引对于列搜索性能更好吗？（PGSQL和MySQL）

Question

我很好奇是否

CREATE INDEX idx ON tbl (columns);

对比。

CREATE UNIQUE INDEX idx ON tbl (columns);

在 PostgreSQL 或 MySQL 实现中，当扫描索引列时，使用 UNIQUE 关键字是否会对算法性能产生显著影响，或者它只是在索引旁引入唯一约束。

我认为可以说，在某种程度上索引可能会带来较小的好处，因为索引很可能被实现为某种哈希¹结构，并且碰撞处理将导致不是 O(1) 性能。基于这个前提，如果大部分值都相同，那么结构就会退化为线性结构。

因此，对于我的问题，请假设值的分布是相对离散和均匀的。

谢谢！

^{1 对我来说纯属推测，因为我不熟悉 RDBM 的内部机制。}

- Alex Balashov

Answer 1

如果您的数据是唯一的，应该在其上创建一个UNIQUE索引。

这不会增加任何额外的开销，并影响优化器在某些情况下的决策，使它可以选择更好的算法。

例如，在SQL Server和PostgreSQL中，如果您对UNIQUE键进行排序，则优化器会忽略之后使用的ORDER BY子句（因为它们是无关紧要的），也就是说，此查询：

SELECT  *
FROM    mytable
ORDER BY
        col_unique, other_col
LIMIT 10

查询将使用col_unique上的索引，并且不会在other_col上进行排序，因为它是无用的。

此查询：

SELECT  *
FROM    mytable
WHERE   mycol IN
        (
        SELECT  othercol
        FROM    othertable
        )

如果othertable.othercol有一个UNIQUE索引，那么它也会转换为INNER JOIN（而不是SEMI JOIN）。

索引始终包含某种指向行的指针（在PostgreSQL中为ctid，在MyISAM中为行指针，在InnoDB中为主键/唯一标识符），并且叶子节点按这些指针排序，因此实际上每个索引叶子节点在某种程度上都是唯一的（尽管可能不明显）。

有关性能详细信息，请参见我博客中的文章：

• 使索引成为UNIQUE

- Quassnoi

Answer 2

在进行更新/插入操作时，如果设置了唯一约束条件，可能会有一些小的惩罚。它需要在插入/更新操作之前进行搜索，以确保唯一性约束条件不被违反。

- Eric

---

FYI，无论如何都必须扫描B树来找到要放置索引数据的页面，因此这基本上是一个平衡点。 - xzilla

Answer 3

通常情况下，索引使用的是B树而不是哈希（虽然有基于哈希的索引，但最常见的索引（至少在PostgreSQL中）是基于B Tree的）。

就速度而言-唯一索引应该更快-当索引扫描找到具有给定值的行时，它无需搜索是否存在其他具有此值的行，可以立即完成扫描。

- user80168