两列上的唯一索引以及每一列上的单独索引？

问题描述：

我不太了解数据库优化，但我正在试图理解这种情况。两列上的唯一索引以及每一列上的单独索引？

说我下面的表格有：

cities 
=========== 
state_id integer 
name varchar(32) 
slug varchar(32)

现在，说我要这样执行查询：

SELECT * FROM cities WHERE state_id = 123 AND slug = 'some_city' 
SELECT * FROM cities WHERE state_id = 123

如果我想要一个城市的“鼻涕虫”内是唯一的它的特定状态，我会在state_id和slug上添加一个唯一的索引。

那个指标够了吗？或者我还应该在state_id上添加另一个，以便第二个查询得到优化？或者第二个查询是否自动使用唯一索引？

我正在研究PostgreSQL，但我觉得这种情况非常简单，大多数DBMS的工作方式都是相似的。

另外，我知道这对小桌子肯定没有什么不同，但我的例子很简单。想想200k +行的表格。

谢谢！

看来这个主题更复杂，比我第一个念头。谢谢大家的意见。 – Ivan 2009-02-01 02:08:57

答

上（STATE_ID，蛞蝓）单个唯一的索引应该是足够的。可以肯定的，当然，你需要运行EXPLAIN和/或分析（可能的类似http://explain.depesz.com/的帮助），但最终什么指标是合适的，你将要运行什么样的查询非常密切的依赖。请记住，索引使SELECT更快，INSERT，UPDATE和DELETE更慢，所以理想情况下只需要尽可能多的索引。

另外，PostgreSQL有一个聪明的查询优化：它将用于查询小表和巨大的表格完全不同的搜索计划。如果表是小，它只是做一个顺序扫描，而不是甚至任何指标打扰，因为与他们合作的开销不仅仅是蛮力通过表筛选更高。一旦表的大小超过阈值时，再次如果表变得越来越大，如果再你改变你的选择可能会改变，或者，或者这变成一个不同的计划....

总结：你不能信任的结果对数据集进行EXPLAIN和ANALYZE分析的数据集比实际数据小得多或者不同。让它工作，然后让它快点（如果你需要的话）。

答

要进行优化，请使用EXPLAIN http://www.postgresql.org/docs/7.4/static/sql-explain.html并亲自查看。但优化并不是制定这些指标的最重要原因;首先它是抑制数据库不合逻辑的约束。

答

[编辑：误读的问题...希望我的答案是现在比较相关]

在你的情况，我建议在(state_id, slug) 1个指数。如果您只需要搜索slug，请在该列上添加索引。如果你有这些，那么在state_id上添加另一个索引是不必要的，因为第一个索引已经覆盖了它。

无论何时在WHERE子句中使用列的初始段时都可以使用索引。所以例如列A，B和C上的索引将优化包含涉及A，B和C的WHERE子句的查询，仅包含A和B的WHERE子句或仅包含A的WHERE子句。请注意，列出现在索引定义中的顺序非常重要的是 - 这个例子的索引不能用于只涉及B和/或C的WHERE子句。

（当然，这取决于查询优化器是否实际使用了特定索引，但在您的情况下有200k行，可以保证通过state_id或slug或两者一个简单的搜索将使用的指标之一）

答

任何像样的优化将看到三列的索引 - 说：

CREATE INDEX idx_1 ON SomeTable(Col1, Col2, Col3);

和将使用该指数为任何满足下列条件：

WHERE Col1 = ...something... 

WHERE Col1 = ...something... AND Col2 = ...otherthing... 

WHERE Col3 = ....whatnot.... 
    AND Col1 = ...something.... 
    AND Col2 = ...otherthing...

也就是说，如果有施加到索引的列的任何连续领先子集条件下才会使用索引。尽管我使用了平等，但它也适用于范围（开放 - 仅比例如大）或关闭（在两个值之间）。

两列上的唯一索引以及每一列上的单独索引？

相关推荐