blocks|key|650203|text|编辑:如果你谈论的是星号通配符，就像在Select+*+From+...中一样，那么可以看看其他回复……|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|650204|如果您正在讨论谓词子句中的通配符，或者使用Like运算符、(_+,+%25+)的其他查询表达式，如下所述：|650205|这与使用通配符是否会影响SQL是否可"SARG-ABLE“有关。SARGABLE，(+search+-ARGument-able)表示查询的搜索或排序参数是否可以用作现有索引的条目参数。如果在参数的开头加上通配符|650206|+Where+Name+Like+'%25ing'|code-block|syntax|javascript|650207|那么就没有办法遍历name字段上的索引来找到在‘’中以ing结尾的节点。|BOLD|650208|如果您将通配符附加到末尾，|650209|Where+Name+like+'Donald%25'+|650210|然后，优化器仍然可以在name列上使用索引，并且查询仍然是SARG-able的|650211|entityMap^0|J|H|0|T|8|0|0|0|O|C|0|0|D|0|0|0|13|0^^$0|@$1|2|3|4|5|6|7|Z|8|@$9|10|A|11|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|15|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|16|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|17|8|@$9|18|A|19|B|Q]]|D|@]|E|$]]|$1|R|3|S|5|6|7|1A|8|@$9|1B|A|1C|B|Q]]|D|@]|E|$]]|$1|T|3|U|5|L|7|1D|8|@]|D|@]|E|$M|N]]|$1|V|3|W|5|6|7|1E|8|@$9|1F|A|1G|B|Q]]|D|@]|E|$]]|$1|X|3|-4|5|6|7|1H|8|@]|D|@]|E|$]]]|Y|$]]

EDIT: If you are talking about the asterisk wild card as in <code>Select * From ...</code> then see other responses...

If you are talking about wildcards in predicate clauses, or other query expressions using Like operator, <code>(_ , % )</code> 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

<pre><code> Where Name Like '%ing'
</code></pre>

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, 

<pre><code>Where Name like 'Donald%' 
</code></pre>

then the optimizer can still use an index on the name column, and the query is still SARG-able

blocks|key|1893966|text|你是说用select+col1,+col2,+col3+from+...代替select+*+from+...吗？|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1893967|我认为命名列并检索最少量的信息总是更好的方法，因为|1893968|1893969|您的代码将独立于数据库中列的物理顺序工作。列顺序应该不会影响您的应用程序，但如果您使用*，就会受到影响。在数据库迁移等情况下，这可能是危险的。|unordered-list-item|1893970|如果您命名列，数据库管理系统可以进一步优化执行。例如，如果有一个索引包含您感兴趣的所有数据，则根本不会访问该表。|1893971|1893972|如果你用“通配符”指的是其他东西，那就忽略我的回答...|1893973|entityMap^0|4|W|12|H|0|0|0|17|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|X|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]|$1|I|3|J|5|K|7|Z|8|@$9|10|A|11|B|C]]|D|@]|E|$]]|$1|L|3|M|5|K|7|12|8|@]|D|@]|E|$]]|$1|N|3|-4|5|6|7|13|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|14|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|15|8|@]|D|@]|E|$]]]|R|$]]

Do you mean <code>select * from ...</code> instead of <code>select col1, col2, col3 from ...</code>?

I think it's always better to name the column and retrieve the minimal amount of information, because

<ul>
<li>your code will work independently of the physical order of the columns in the db. The column order should not impact your application, but it will be the case if you use <code>*</code>. It can be dangerous in case of db migration, etc.</li>
<li>if you name the columns, the DBMS can optimize further the execution. For instance, if there is an index that contains all the data your are interested in, the table will not be accessed at all.</li>
</ul>

If you mean something else with "wildcard", just ignore my answer...

blocks|key|1891953|text|SELECT+*+FROM...|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1891954|和|unstyled|1891955|SELECT+every,+column,+list,+...+FROM...|1891956|将执行相同的操作，因为两者都是未优化的扫描|1891957|区别在于：|1891958|当表模式changes|1891959|inability创建覆盖索引时，|unordered-list-item|1891960|在sys.columns中的额外查找来解决*|1891961|合同/签名的变化。实际上，根本没有调优选项，如果非schemabound|1891962|can未使用*|1891963|...and+other+stuff|1891964|索引或模式绑定视图，则|1891965|
|1891966|必须刷新所需视图|1891967|1891968|关于同一主题的其他SO问题...|1891969|1891970|What+is+the+reason+not+to+use+select+*+?|offset|length|1891971|Is+there+a+difference+betweeen+Select+*+and+Select+list+each+col|1891972|SQL+Query+Question+-+Select+*+from+view+or+Select+col1,col2…from+view|1891973|“select+*+from+table”+vs+“select+colA,colB,etc+from+table”+interesting+behaviour+in+SqlServer2005|1891974|1891975|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select|1|https://stackoverflow.com/questions/208925/is-there-a-difference-betweeen-select-and-select-list-each-col|2|https://stackoverflow.com/questions/128412/sql-query-question-select-from-view-or-select-col1-col2-from-view|3|https://stackoverflow.com/questions/321468/select-from-table-vs-select-cola-colb-etc-from-table-interesting-behaviour^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|14|0|0|0|1S|1|0|0|1X|2|0|0|2P|3|0|0^^$0|@$1|2|3|4|5|6|7|1Y|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|1Z|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|20|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|21|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|22|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|23|8|@]|9|@]|A|$]]|$1|O|3|P|5|Q|7|24|8|@]|9|@]|A|$]]|$1|R|3|S|5|Q|7|25|8|@]|9|@]|A|$]]|$1|T|3|U|5|Q|7|26|8|@]|9|@]|A|$]]|$1|V|3|W|5|Q|7|27|8|@]|9|@]|A|$]]|$1|X|3|Y|5|Q|7|28|8|@]|9|@]|A|$]]|$1|Z|3|10|5|F|7|29|8|@]|9|@]|A|$]]|$1|11|3|12|5|F|7|2A|8|@]|9|@]|A|$]]|$1|13|3|14|5|Q|7|2B|8|@]|9|@]|A|$]]|$1|15|3|-4|5|F|7|2C|8|@]|9|@]|A|$]]|$1|16|3|17|5|F|7|2D|8|@]|9|@]|A|$]]|$1|18|3|-4|5|F|7|2E|8|@]|9|@]|A|$]]|$1|19|3|1A|5|Q|7|2F|8|@]|9|@$1B|2G|1C|2H|1|2I]]|A|$]]|$1|1D|3|1E|5|Q|7|2J|8|@]|9|@$1B|2K|1C|2L|1|2M]]|A|$]]|$1|1F|3|1G|5|Q|7|2N|8|@]|9|@$1B|2O|1C|2P|1|2Q]]|A|$]]|$1|1H|3|1I|5|Q|7|2R|8|@]|9|@$1B|2S|1C|2T|1|2U]]|A|$]]|$1|1J|3|-4|5|F|7|2V|8|@]|9|@]|A|$]]|$1|1K|3|-4|5|F|7|2W|8|@]|9|@]|A|$]]]|1L|$1M|$5|1N|1O|1P|A|$1Q|1R]]|1S|$5|1N|1O|1P|A|$1Q|1T]]|1U|$5|1N|1O|1P|A|$1Q|1V]]|1W|$5|1N|1O|1P|A|$1Q|1X]]]]

<pre><code>SELECT * FROM...
</code></pre>

and

<pre><code>SELECT every, column, list, ... FROM...
</code></pre>

will perform the same because both are an unoptimised scan

The difference is:

<ul>
<li>the extra lookup in sys.columns to resolve *</li>
<li>the contract/signature change when the table schema changes</li>
<li>inability to create a covering index. In fact, no tuning options at all, really</li>
<li>have to refresh views needed if non schemabound</li>
<li>can not index or schemabind a view using *</li>
<li>...and other stuff</li>
</ul>

Other SO questions on the same subject...

<ul>
<li><a href="https://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select">What is the reason not to use select * ?</a></li>
<li><a href="https://stackoverflow.com/questions/208925/is-there-a-difference-betweeen-select-and-select-list-each-col">Is there a difference betweeen Select * and Select list each col</a></li>
<li><a href="https://stackoverflow.com/questions/128412/sql-query-question-select-from-view-or-select-col1-col2-from-view">SQL Query Question - Select * from view or Select col1,col2…from view</a></li>
<li><a href="https://stackoverflow.com/questions/321468/select-from-table-vs-select-cola-colb-etc-from-table-interesting-behaviour">“select * from table” vs “select colA,colB,etc from table” interesting behaviour in SqlServer2005</a></li>
</ul>

blocks|key|1891990|text|如果您称之为SQL+wild是*。它本身并不意味着性能开销。但是，如果对表进行了扩展，您可能会发现自己正在检索不需要搜索的字段。一般来说，在搜索或插入的字段中不明确是一个坏习惯。考虑一下|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1891991|insert+into+mytable+values(1,2)|code-block|syntax|javascript|1891992|如果将表扩展到三个字段，会发生什么情况？|1891993|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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 

<pre><code>insert into mytable values(1,2)
</code></pre>

What happen if the table is extended to three fields?

blocks|key|2058359|text|从执行计划的角度来看，这可能不是更多的工作。但是，如果您正在获取实际上并不需要的列，则需要在数据库和应用程序之间使用额外的网络带宽。另外，如果您使用的是对返回的数据执行某些工作的高级客户端API+(例如，Perl的selectall_hashref)，那么这些额外的列将对客户端造成性能损失。多少钱？视情况而定。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2058360|entityMap^0|2Z|H|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

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 <code>selectall_hashref</code>) then those extra columns will impose performance cost on the client side. How much? Depends.

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:

<pre><code>SELECT *
</code></pre>

vs.

<pre><code>SELECT item1, item2, etc.
</code></pre>

Sql wildcard: performance overhead?

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

我在谷歌上搜索了这个问题，似乎找不到一致的观点，也找不到许多基于可靠数据的观点。我只是想知道在SQL SELECT语句中使用通配符是否比单独调用每一项产生额外的开销。我在几个不同的测试查询中比较了两者的执行计划，看起来估计总是相同的。是否有可能在其他地方产生一些开销，或者它们是否真的以相同的方式处理？我具体指的是：SE...

问Sql通配符:性能开销？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Sql通配符:性能开销？EN