sql - 为什么列上的函数会阻止使用索引？

Question

关于 this question我前几天问过，我得到了以下评论。

In almost any database, almost any function on a column prevents the use of indexes. There are exceptions here and there, but in general, functions prevent the use of indexes

我四处搜索，发现更多提及同样的行为，但我很难找到比评论已经告诉我的更深入的东西。

有人可以详细说明为什么会发生这种情况，以及避免这种情况的策略吗？

Answer 1

最基本形式的索引只是经过排序的列数据，可以很容易地按某个值进行查找。例如，一本教科书可以按某种顺序排列页面，但在后面有所有术语的索引。如您所见，数据已预先计算/排序并存储在单独的区域中。

当您对列应用函数并尝试根据输出进行匹配/过滤时，索引不再有用。让我们再看看书上的例子，说我们正在应用的函数是术语的反转(所以 reverse('integral') 变成 'largetni').您不会在索引中找到这个值，因此您必须获取所有项，将它们放入函数中，然后才进行比较。全部在查询时。本来我们可以跳过搜索 i，然后是 in，然后是 int 等等，这样可以很容易地找到这个词，所以函数做了一切慢得多。

如果你经常使用这个函数查询，你可以提前用reverse(term)建立一个索引来加快查找速度。但如果不明确地这样做，它总是会很慢。