索引总和列 [英] Index a sum column

问题描述

为正在求和的列创建索引是否比没有索引快?

Is creating an index for a column that is being summed is faster than no index?

推荐答案

抱歉，不清楚您在问什么.

Sorry, it is not clear what you are asking.

您是在问，它会加快诸如

Are you asking, would it speed up a query such as

SELECT product, sum(quantity) FROM receipts 
GROUP BY product

如果您添加了数量索引?

if you added an index on quantity?

如果这是问题，那么答案是否定的.一般来说，当您需要在众多行中查找几行时，索引会很有帮助；在这里你需要所有的行，所以索引没有帮助.

If that is the question, then the answer is no. Generally speaking, indexes are helpful when you need to find just a few rows among many; here you need all rows, so an index does not help.

有一个晦涩的异常(很少适用，大多数数据库优化器可能不会费心实现这个技巧).如果您的查询恰好是

There is an obscure exception (which applies so rarely most DB optimizers probably don't bother implementing this trick). If your query happens to be

SELECT sum(foo) FROM bar

，在 foo 上有一个索引，bar 是一个有很多列的表，可以读取完整索引，比读取基础表产生的命中要小，直接从index - 根本不必触摸真实"表！然而，这是一种相当罕见的情况，您需要测试一下您的优化器是否知道这样做，然后再过分依赖它.

, where there is an index on foo, and bar is a table with many columns, it is possible to read in the full index, incurring a smaller hit than if you read the underlying table, and get the answer directly from the index -- never having to touch the "real" table at all! This is a fairly rare case, however, and you will want to test that your optimizer knows to do this before relying on this too much.

这篇关于索引总和列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！