blocks|key|516994|text|这个答案假设您询问的是以下两个变体之间的区别：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|516995|ORDER+BY+totalRevenue
ORDER+BY+totalRevenue+LIMIT+10|code-block|syntax|javascript|516996|在许多数据库中，如果存在涉及totalRevenue的适当索引，则LIMIT查询可以在找到前10条记录后停止排序。|offset|length|style|CODE|516997|在没有任何索引的情况下，正如您所指出的，这两个版本都必须执行完整的排序，因此应该执行相同的排序。|516998|此外，如果表很大，两者之间可能存在很大的性能差异。在LIMIT版本中，BigQuery只需要跨10个记录发送，而在非LIMIT版本中，可能需要发送更多的数据。|516999|entityMap^0|0|0|E|C|X|5|0|0|Q|5|1M|5|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]|$I|X|J|Y|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@$I|11|J|12|K|L]|$I|13|J|14|K|L]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|R|$]]

This answer assumes you are asking about the difference between the following two variants:

<pre><code>ORDER BY totalRevenue
ORDER BY totalRevenue LIMIT 10
</code></pre>

In many databases, if a suitable index existed involving <code>totalRevenue</code>, the <code>LIMIT</code> query could stop sorting after finding the top 10 records.

In the absence of any index, as you pointed out, both versions would have to do a full sort, and therefore should perform the same.

Also, there is a potentially major performance difference between the two, if the table be large. In the <code>LIMIT</code> version, BigQuery only has to send across 10 records, while in the non <code>LIMIT</code> version, potentially much more data has to be sent.

blocks|key|2156158|text|没有性能上的提升。bigQuery仍在检查表上的所有记录。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2156159|您可以对数据进行分区，以减少bigQuery必须读取的记录数量。这将提高业绩。您可以在这里阅读更多信息：https://cloud.google.com/bigquery/docs/partitioned-tables|offset|length|2156160|entityMap|0|LINK|mutability|MUTABLE|url|https://cloud.google.com/bigquery/docs/partitioned-tables^0|0|1G|1L|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

There is no performance gain. bigQuery still has go through all the records on the table.

You can partition your data in order to cut the amount of records that bigQuery has to read. That will increase performance. You can read more information here:
<a href="https://cloud.google.com/bigquery/docs/partitioned-tables" rel="nofollow noreferrer">https://cloud.google.com/bigquery/docs/partitioned-tables</a>

blocks|key|2149347|text|请参阅以下两个查询在bigQuery+UI中的统计差异|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2149348|SELECT+*+FROM+`bigquery-public-data.hacker_news.comments`+LIMIT+1000|code-block|syntax|javascript|2149349|​|2149350|📷|atomic|offset|length|2149351|2149352|SELECT+*+FROM+`bigquery-public-data.hacker_news.comments`+LIMIT+10000|2149353|2149354|2149355|2149356|如您所见，在达到限制标准后，BQ将立即返回UI，从而提高性能，减少网络流量。|2149357|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/dae443e8cd7d7a11f2b64c14055018a5.png|imageAlt|1|https://developer.qcloudimg.com/http-save/yehe-900000/00780c016aa522635bf69f2d94d8654d.png^0|0|0|0|0|1|0|0|0|0|0|0|1|1|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|18|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|19|8|@]|9|@$L|1A|M|1B|1|1C]]|A|$]]|$1|N|3|H|5|6|7|1D|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|1E|8|@]|9|@]|A|$E|F]]|$1|Q|3|H|5|6|7|1F|8|@]|9|@]|A|$]]|$1|R|3|J|5|K|7|1G|8|@]|9|@$L|1H|M|1I|1|1J]]|A|$]]|$1|S|3|H|5|6|7|1K|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|1L|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]]|W|$X|$5|Y|Z|10|A|$11|12|13|-4]]|14|$5|Y|Z|10|A|$11|15|13|-4]]]]

See the statistical difference in bigQuery UI between the below 2 queries

<pre><code>SELECT * FROM `bigquery-public-data.hacker_news.comments` LIMIT 1000
</code></pre>

<a href="https://i.stack.imgur.com/jYFvk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jYFvk.png" alt="Limit 1000"></a>

<pre><code>SELECT * FROM `bigquery-public-data.hacker_news.comments` LIMIT 10000
</code></pre>

<a href="https://i.stack.imgur.com/Uiyb7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Uiyb7.png" alt="Limit 10000"></a>

As you can see BQ will return immediately to UI after the limit criteria is reached this result in better performance and less traffic on the network

blocks|key|517236|text|以下适用于BigQuery|type|unstyled|depth|inlineStyleRanges|entityRanges|data|517237|不一定100%25的技术正确，但足够接近，所以我希望下面能给你一个为什么限制N是非常重要的考虑在BigQuery|517238|假设您有1,000,000行数据和8个处理查询的工作人员，如下所示|517239|SELECT+*+FROM+table_with_1000000_rows+ORDER+BY+some_field|code-block|syntax|javascript|517240|第1轮:要对此数据进行排序，每个工作人员将得到125,000行-因此，现在您有8组排序集，每组为125,000行。|517241|第2轮:+Worker+#1将其排序的数据(125,000行)发送给工作人员#2，#3发送到#4，依此类推。现在我们有4名工人，每个产品都有250,000行。|517242|第3轮:上面的逻辑重复了，现在我们只有两个工人，每个人都有500,000行的有序列表。|517243|第4轮:最后，只有一个工作人员生成1,000,000行的最终排序集|517244|当然，根据行数和可用工作人员数，轮数可能与上述示例中的不同。|517245|摘要:我们这里有：|517246|我们有相当多的数据在工人之间传输--这可能是性能下降的一个相当大的因素。|517247|我们有机会让其中一名工人无法处理分配给相应工人的大量数据。这种情况可能发生得早或晚，通常表现为“资源超过…”错误类型|517248|所以，如果您有限制作为查询的一部分，如下所示|517249|SELECT+*+FROM+table_with_1000000_rows+ORDER+BY+some_field+LIMIT+10+|517250|所以，现在-第一轮将是一样的。但是从第2轮开始，只有前10行将被发送给另一位工人，因此在第一轮之后的每一轮中，只有20行将被处理，只有前10行将被发送到进一步处理，希望您看到这两个进程在工人之间发送的数据的数量以及每个工人需要应用多少工作来对各自的数据进行排序时有多么不同。|517251|概括地说：|517252|无限制10：|517253|初始行移动(第1轮)：1+000+000行；|517254|最初排序行(第1轮)：1+000+000行；|517255|中间行移动(第2-4轮)：1+500+000行|517256|合并后的总体有序行(第2-4轮)：1,500,000；|517257|最终结果:1+000+000行|517258|限制为10：|517259|初始行移动(第1轮)：1+000+000行；|517260|最初排序行(第1轮)：1+000+000行；|517261|中间行移动(第2-4轮)：70行|517262|总体合并有序行(第2-4轮)：140行；|517263|最终结果:+10行|517264|以上数字清楚地显示了使用极限N所获得的性能差异，在某些情况下甚至可以在没有“资源超过.”的情况下成功地运行查询。错误|517265|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|20|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|21|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|22|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|23|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|24|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|25|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|26|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|27|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|28|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|29|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|2A|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|2B|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|2C|8|@]|9|@]|A|$]]|$1|12|3|13|5|H|7|2D|8|@]|9|@]|A|$I|J]]|$1|14|3|15|5|6|7|2E|8|@]|9|@]|A|$]]|$1|16|3|17|5|6|7|2F|8|@]|9|@]|A|$]]|$1|18|3|19|5|6|7|2G|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|2H|8|@]|9|@]|A|$]]|$1|1C|3|1D|5|6|7|2I|8|@]|9|@]|A|$]]|$1|1E|3|1F|5|6|7|2J|8|@]|9|@]|A|$]]|$1|1G|3|1H|5|6|7|2K|8|@]|9|@]|A|$]]|$1|1I|3|1J|5|6|7|2L|8|@]|9|@]|A|$]]|$1|1K|3|1L|5|6|7|2M|8|@]|9|@]|A|$]]|$1|1M|3|1N|5|6|7|2N|8|@]|9|@]|A|$]]|$1|1O|3|1P|5|6|7|2O|8|@]|9|@]|A|$]]|$1|1Q|3|1R|5|6|7|2P|8|@]|9|@]|A|$]]|$1|1S|3|1T|5|6|7|2Q|8|@]|9|@]|A|$]]|$1|1U|3|1V|5|6|7|2R|8|@]|9|@]|A|$]]|$1|1W|3|1X|5|6|7|2S|8|@]|9|@]|A|$]]|$1|1Y|3|-4|5|6|7|2T|8|@]|9|@]|A|$]]]|1Z|$]]

Below is applicable for BigQuery 

Not necessarily 100% technically correct - but close enough so I hope below will give you an idea why LIMIT N is extremely important to consider in BigQuery 

Assume you have 1,000,000 rows of data and 8 workers to process query like below 

<pre><code>SELECT * FROM table_with_1000000_rows ORDER BY some_field
</code></pre>

Round 1: To sort this data each worker gets 125,000 rows – so now you have 8 sorted sets of 125,000 rows each 
Round 2: Worker #1 sends its sorted data (125,000 rows) to worker #2, #3 sends to #4 and so on. So now we have 4 workers and each produce ordered set of 250,000 rows 
Round 3: Above logic repeated and now we have just 2 workers each producing ordered list of 500,000 rows 
Round 4: And finally, just one worker producing final ordered set of 1,000,000 rows 

Of course, based on number of rows and number of available workers – number of rounds can be different than in above example

In Summary: what we have here: 
a. We have quite a huge amount of data being transferred between workers – this can be quite a factor for performance going down 
b. And we have chance for one of the workers not being able to process amount of data distributed to respective worker. It can happen earlier or later and is usually manifested with “Resources exceeded …” type of error 

So, now if you have LIMIT as a part of query as below 

<pre><code>SELECT * FROM table_with_1000000_rows ORDER BY some_field LIMIT 10 
</code></pre>

So, now – Round 1 is going to be the same. But starting with Round 2 – ONLY top 10 rows will be sent to another worker – thus in each Round after first one - only 20 rows will processed and only top 10 will be sent for further processing
Hope you see how different these two processes in terms of volume of the data being sent between workers and how much work each worker needs to apply to sort respective data 

To Summarize: 
Without LIMIT 10: 
 • Initial rows moved (Round 1): 1,000,000; 
 • Initial rows ordered (Round 1): 1,000,000; 
 • Intermediate rows moved (Round 2 - 4): 1,500,000 
 • Overall merged ordered rows (Round 2 - 4): 1,500,000; 
 • Final result: 1,000,000 rows 

With LIMIT 10: 
 • Initial rows moved (Round 1): 1,000,000; 
 • Initial rows ordered (Round 1): 1,000,000; 
 • Intermediate rows moved (Round 2 - 4): 70 
 • Overall merged ordered rows (Round 2 - 4): 140; 
 • Final result: 10 rows 

Hope above numbers clearly show the difference in performance you gain using LIMIT N and in some cases even ability to successfully run the query without "Resource exceeded ..." error

for example

<pre><code> SELECT company_ID, totalRevenue 
FROM `BigQuery.BQdataset.companyperformance` 
ORDER BY totalRevenue LIMIT 10
</code></pre>

The only difference I can see between using and not using LIMIT 10 is just the different amount of data used for displaying to user.
The system still orders all the data first before performing a LIMIT.

Is there any performance benefit when we use Limit

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

例如    SELECT company_ID, totalRevenue FROM `BigQuery.BQdataset.companyperformance` ORDER BY totalRevenue LIMIT 10我看到使用限制10和不使用限制10之间唯一的区别是用于向用户显示的不同数据量。在执行限制之前，...

问当我们使用限制时，有什么性能效益吗？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问当我们使用限制时，有什么性能效益吗？EN