Sql通配符：性能开销？
我在谷歌上搜索了这个问题，似乎找不到一致的观点，或者许多基于可靠数据的观点。我只是想知道在 SQL SELECT 语句中使用通配符是否会比单独调用每个项目产生额外的开销。我在几个不同的测试查询中比较了两者的执行计划，看起来估计值总是相同的。是否有可能在其他地方产生了一些开销，或者它们的处理方式确实相同？
我具体指的是：
SELECT *
vs。
SELECT item1, item2, etc.
I've Googled this question and can't seem to find a consistent opinion, or many opinions that are based on solid data. I simply would like to know if using the wildcard in a SQL SELECT statement incurs additional overhead than calling each item out individually. I have compared the execution plans of both in several different test queries, and it seems that the estimates always read the same. Is it possible that some overhead is incurred elsewhere, or are they truly handled identically?
What I am referring to specifically:
SELECT *
vs.
SELECT item1, item2, etc.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
并将
执行相同的操作，因为两者都是未优化的扫描。
不同之处在于：
关于同一主题的其他 SO 问题...
and
will perform the same because both are an unoptimised scan
The difference is:
Other SO questions on the same subject...
您的意思是
select * from ...而不是select col1, col2, col3 from ...吗？我认为命名列并检索最少量的信息总是更好，因为
*则会出现这种情况。在数据库迁移等情况下，这可能很危险。如果您对“通配符”有其他意思，请忽略我的答案......
Do you mean
select * from ...instead ofselect col1, col2, col3 from ...?I think it's always better to name the column and retrieve the minimal amount of information, because
*. It can be dangerous in case of db migration, etc.If you mean something else with "wildcard", just ignore my answer...
编辑：如果您正在谈论
Select * From ...中的星号通配符，请参阅其他响应...如果您正在谈论谓词子句中的通配符或使用 Like 运算符的其他查询表达式,
(_ , % )如下所述，那么：这与使用通配符是否影响 SQL 是否为“SARG-ABLE”有关。 SARGABLE（Search-ARGument-able）表示查询的搜索或排序参数是否可以用作现有索引的条目参数。如果将通配符添加到参数的开头
，则无法遍历名称字段上的索引来查找以“ing”结尾的节点。
如果您将通配符附加到末尾，
那么优化器仍然可以在名称列上使用索引，并且查询仍然是 SARG 可用的
EDIT: If you are talking about the asterisk wild card as in
Select * From ...then see other responses...If you are talking about wildcards in predicate clauses, or other query expressions using Like operator,
(_ , % )as described below, then:This has to do with whether using the Wildcard affects whether the SQL is "SARG-ABLE" or not. SARGABLE, (Search-ARGument-able)means whether or not the query's search or sort arguments can be used as entry parameters to an existing index. If you prepend the wild card to the beginning of an argument
Then there is no way to traverse an index on the name field to find the nodes that end in 'ing'.
If otoh you append the wildcard to the end,
then the optimizer can still use an index on the name column, and the query is still SARG-able
如果你所说的SQL野车是*。它本身并不意味着性能开销。但是，如果表扩展，您可能会发现自己检索了未搜索的字段。
一般来说，不具体指定您搜索或插入的字段是一个坏习惯。
考虑
如果表扩展到三个字段会发生什么？
If that you call SQL wild car is *. It does not imply performance overhead by it self. However, if the table is extended you could find yourself retrieving fields you doesn't search.
In general not being specific in the fields you search or insert is a bad habit.
Consider
What happen if the table is extended to three fields?
从执行计划的角度来看，这可能并不是更多的工作。但是，如果您要获取实际上并不需要的列，则数据库和应用程序之间会使用额外的网络带宽。此外，如果您使用高级客户端 API 对返回的数据执行一些工作（例如，Perl 的
selectall_hashref），那么这些额外的列将给客户端带来性能成本。多少？视情况而定。It may not be more work from an execution plan standpoint. But if you're fetching columns you don't actually need, that's additional network bandwidth being used between the database and your application. Also if you're using a high-level client API that performs some work on the returned data (for example, Perl's
selectall_hashref) then those extra columns will impose performance cost on the client side. How much? Depends.