mysql - 为什么使用Group By对SQL的索引性能会有很大的影响

Question

为什么使用Group By对SQL的索引性能会有很大的影响？索引是不是能提升group by的性能？

---

还有一点关于SQL的疑问，为什么在使用模糊查询的时候，%name%, 如果使用了前模糊，会使得索引没有了效果，这个怎么理解，虽然模糊的知道可能是这样的，但是找不到官方对此的说法。谢谢~

Answer 1

What does “significant impact on index performance” mean? Indexing takes too long? But this seems to have nothing to do with gruop by.

So I guess your question is "Can indexing improve the performance of group by"? I wish the cause and effect relationship of this problem would be easier to understand. If this is the problem, maybe the following passage can give you some hints:

SQL databases use two entirely different group by algorithms. The
first one, the hash algorithm, aggregates the input records in a
temporary hash table. Once all input records are processed, the hash
table is returned as the result. The second algorithm, the sort/group
algorithm, first sorts the input data by the grouping key so that the
rows of each group follow each other in immediate succession.
Afterwards, the database just needs to aggregate them. In general,
both algorithms need to materialize an intermediate state, so they are
not executed in a pipelined manner. Nevertheless the sort/group
algorithm can use an index to avoid the sort operation, thus enabling
a pipelined group by.

Original source: Indexing Group By