在几乎所有数据库中，列上的几乎所有函数都无法使用索引.到处都有例外，但总的来说，函数会阻止使用索引
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
我在Google上四处搜寻，并发现了更多关于这种行为的提及，但是我很难找到比评论已经告诉我的更深入的内容.
I googled around and found more mentions of this same behavior, but I had trouble finding something more in depth than what the comment already told me.
有人能详细说明为什么会发生这种情况吗，也许是避免这种情况发生的策略?
Could someone elaborate on why this occurs, and perhaps strategies for avoiding it?
推荐答案
最基本形式的索引就是排序的列数据，可以轻松地按某个值查找.例如，一本教科书可以按一定顺序排列页面，但后面的所有术语都有一个索引.如您所见，数据是经过预先计算/排序的，并存储在单独的区域中.
An index in its most basic form is just the sorted column data, making it easy to look up by some value. For example, a textbook can have the pages in some order, but then have an index in the back for all the terms. As you can see, the data is precomputed/sorted and stored in a separate area.
当您将函数应用于列并尝试根据输出进行匹配/过滤时，索引不再有用.让我们再次看一下我们的书籍示例，并说我们正在应用的函数与该术语相反(因此reverse('integral')变为'largetni').您不会在索引中找到此值，因此您必须将所有术语取整，然后将它们放入函数中，然后再进行比较.全部在查询时.最初，我们可以跳过对i，然后依次搜索in和int等的搜索，从而很容易找到该术语，因此该函数使所有操作变慢了.
When you apply a function to the column and try to match/filter based on the output, the index is no longer useful. Let's take a look at our book example again, and say that the function we're applying is the reverse of the term (so reverse('integral') becomes 'largetni'). You won't find this value in the index, so you have to take all the terms, put them through the function, and only then compare. All at query time. Originally we could skip search for i, then in, then int and so on, making it easy to find the term so the function made everything much slower.
如果经常使用此功能进行查询，则可以提前使用reverse(term)进行索引，以加快查找速度.但是，如果没有明确地这样做，它将总是很慢.
If you query using this function often, you could make an index with reverse(term) ahead of time to speed up look ups. But without doing so explicitly, it will always be slow.
这篇关于为什么列上的函数会阻止索引的使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
