多列索引是否适用于单列选择？ [英] Does a multi-column index work for single column selects too?

问题描述

我有（例如）索引：

CREATE INDEX someIndex ON orders (customer, date);

此索引是否仅加速使用 customer和date 的查询它会加速像这样的单列查询吗？

Does this index only accelerate queries where customer and date are used or does it accelerate queries for a single-column like this too?

SELECT * FROM orders WHERE customer > 33;

我正在使用SQLite。

I'm using SQLite.

如果答案是肯定的，为什么每个表可以创建多个索引？

If the answer is yes, why is it possible to create more than one index per table?

还有一个问题：当你在查询中使用两个列时，组合索引与两个分离索引相比要快多少？

Yet another question: How much faster is a combined index compared with two separat indexes when you use both columns in a query?

推荐答案

marc_s对你的第一个问题有正确的答案。多键索引中的第一个键可以像单个键索引一样工作，但任何后续键都不会。

marc_s has the correct answer to your first question. The first key in a multi key index can work just like a single key index but any subsequent keys will not.

至于复合索引的速度有多快取决于你的数据以及如何构建索引和查询，但它通常很重要。索引基本上允许Sqlite对字段进行二进制搜索。

As for how much faster the composite index is depends on your data and how you structure your index and query, but it is usually significant. The indexes essentially allow Sqlite to do a binary search on the fields.

使用您运行查询时给出的示例：

Using the example you gave if you ran the query:

SELECT * from orders where customer > 33 && date > 99

Sqlite首先会在客户> 33的整个表上使用二进制搜索获得所有结果。然后它只会对那些寻找日期> 99的结果进行二元搜索。

Sqlite would first get all results using a binary search on the entire table where customer > 33. Then it would do a binary search on only those results looking for date > 99.

如果你在客户和日期使用两个单独的索引进行相同的查询，Sqlite将不得不二进制搜索整个表两次，首先是客户，再次搜索日期。

If you did the same query with two separate indexes on customer and date, Sqlite would have to binary search the whole table twice, first for the customer and again for the date.

因此，您将看到多少速度增加取决于您如何构建索引关于你的查询。理想情况下，索引中的第一个字段和查询应该是消除最可能匹配的字段，因为这会通过大大减少第二次搜索必须完成的工作量来提高速度。

So how much of a speed increase you will see depends on how you structure your index with regard to your query. Ideally, the first field in your index and your query should be the one that eliminates the most possible matches as that will give the greatest speed increase by greatly reducing the amount of work the second search has to do.

有关详细信息，请参阅：
http：//www.sqlite .org / optoverview.html

For more information see this: http://www.sqlite.org/optoverview.html

这篇关于多列索引是否适用于单列选择？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！