blocks|key|149739|text|索引与SQL服务器上的数据本身分开存储。因此，当您执行查询时，应该引用以提供速度的B树索引将不再被引用，因为每个列上都有一个操作(函数)，因此查询优化器将选择不再使用该索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|149740|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The indexes are stored separately from the data itself on the SQL server. So when you do a query the B-tree index that ought to be referenced to provide the speed can no longer be referenced because there is an operation(the function) on each of the column so the query optimiser will opt not to use the index any more.

blocks|key|149400|text|这里很好地解释了为什么会发生这种情况(这是一篇特定于Server的文章，但可能适用于其他systems系统)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|149401|https://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/|offset|length|149402|文章中真正突出的一行是“原因是，必须对每一行数据进行函数值的计算，以确定其是否与您的标准相匹配”。|149403|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|2Z|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

Here is a good explanation of why this occurs (this is a SQL Server specific article, but probably applies to other SQL RDBMS systems):

<a href="https://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/" rel="nofollow">https://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/</a>

The line from the article that really stands out is "The reason for this is is that the function value has to be evaluated for each row of data to determine it matches your criteria."

blocks|key|149412|text|让我们考虑一个极端的例子。假设您正在使用加密散列函数(如HASH(email_address)+=+0x123456+)查找一行。数据库有一个建立在email_address上的索引，但是现在您要求它在HASH(email_address)上查找它没有的数据。它仍然可以使用索引，但是它将不得不查看email_address的每个索引条目，并查看HASH(email_address)是否匹配。如果它需要扫描完整的索引，那么它最好只是扫描完整的表，这样它就不必来回地获取单个行的位置了。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|149413|entityMap^0|S|U|22|D|2T|J|45|D|4T|J|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]|$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|G|$]]

Let's consider an extreme example. Let's say that you're looking up a row using a cryptographic hash function, like <code>HASH(email_address) = 0x123456</code>. The database has an index built on <code>email_address</code>, but now you're asking it to look up data on <code>HASH(email_address)</code> which it doesn't have. It could still use the index, but it would end up having to look at every single index entry for <code>email_address</code> and see if <code>HASH(email_address)</code> matches. If it's going to have to scan the full index, it may as well just scan the full table instead so it doesn't have to bounce back and forth fetching individual row locations.

blocks|key|3519215|text|索引最基本的形式就是排序的列数据，这样就很容易通过一些值来查找。例如，教科书可以按一定顺序排列页面，但在后面为所有术语设置索引。如您所见，数据是预先计算/排序的，并存储在单独的区域中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3519216|当您将函数应用到列并尝试根据输出匹配/筛选时，索引将不再有用。让我们再看一看我们的书中的示例，并说我们正在应用的函数与术语相反(因此reverse('integral')变成了'largetni')。您将在索引中找不到这个值，所以您必须接受所有的术语，将它们放在函数中，然后再进行比较。都是在查询时间。最初，我们可以跳过对i的搜索，然后跳过in，然后跳过int等等，这样就很容易找到这个词，因此函数使一切变得更慢。|offset|length|style|CODE|3519217|如果经常使用此函数进行查询，则可以提前使用reverse(term)创建索引，以加快查找速度。但如果不明确地这样做，它将永远是缓慢的。|3519218|entityMap^0|0|1U|J|2G|A|4H|1|4Q|2|4X|3|0|L|D|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]|$D|P|E|Q|F|G]|$D|R|E|S|F|G]|$D|T|E|U|F|G]|$D|V|E|W|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@$D|Y|E|Z|F|G]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|K|$]]

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.

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 <code>reverse('integral')</code> becomes <code>'largetni'</code>). 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 <code>i</code>, then <code>in</code>, then <code>int</code> and so on, making it easy to find the term so the function made everything much slower.

If you query using this function often, you could make an index with <code>reverse(term)</code> ahead of time to speed up look ups. But without doing so explicitly, it will always be slow.

On <a href="https://stackoverflow.com/questions/37898534/do-you-always-need-to-use-coalesce-in-a-where-if-there-can-be-null-empty-values">this question</a> that I asked the other day I got the following comment.

<blockquote>
 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
</blockquote>

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?

Why do functions on columns prevent the use of indexes?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

在我前几天问的上，我得到了以下评论。在几乎任何数据库中，列上的几乎任何函数都会阻止索引的使用。这里和那里都有例外，但总的来说，函数阻止了索引的使用。我搜索了一下，发现了更多关于这种行为的信息，但是我很难找到比评论告诉我更深入的东西。有人能详细说明为什么会发生这种情况吗?也许是避免这种情况的策略？

问为什么列上的函数阻止索引的使用？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么列上的函数阻止索引的使用？EN