SQL:中断查询 [英] SQL: Interrupting a query

问题描述

我已经在使用专有的非SQL DB的项目中进行了工作，在该项目中查询可能会被中断，并且在代码库中有很多地方使用了该功能并且具有很强的意义(例如，停止长时间运行的查询，被用户取消，或者发生了最近的查询并使先前的查询过时等)，我意识到我以前从未真正看到过这种中断的查询"，并认为它可以提出一个很好的SO问题(几个问题，但它们都与完全相同的事物有关):

I've worked on a project using a proprietary non-SQL DB where queries could be interrupted and in the codebase there were quite some spots where that functionnality was used and made perfect sense (for example to stop a long running query that gets cancelled by the user, or when a more recent query takes place and renders the previous query obsolete, etc.) and I realized I never really saw that kind of "interrupted queries" previously and thought it could make a good SO question (several questions, but they're all related to exactly the same thing):

• SQL查询可以中断吗?

• can SQL queries be interrupted?

这是SQL标准的一部分吗?

is this part of the SQL standard?

如果它不是SQL标准的一部分，哪些SQL DB允许查询被打断(最欢迎的例子)?

if it's not part of the SQL standard, which SQL DBs allow queries to be interrupted (any example most welcome)?

中断数据库查询(是否使用SQL)是否很常见，您会知道该查询不再关心结果了? (在我研究过的代码库中，它肯定有助于减轻服务器的负载)

is it common to interrupt a DB query (SQL or not) which you'll know you won't care about the result anymore? (in the codebase I've worked on, it sure helps lighten the server's load)

推荐答案

以"killed"或"terminated"代替被打扰"的爱莫.中断的概念可能会让人感到困惑，因为人们可能会假设它会在以后恢复查询.

Imho "interrupted" should be replaced by 'killed' or 'terminated'. The concept of interrupting can be confusing as one might presume it would allow the query to be resumed later.

SQL标准没有提供中断或终止正在运行的查询的方法，但是我知道的每个DBMS都实现了KILL命令或类似命令.例如，在MySQL中，用户可以使用SHOW [FULL] PROCESSLIST来查看所有正在运行的查询(及其状态，查询ID等).具有KILL特权的用户然后可以终止查询.

The SQL standard does not supply a way to interrupt or terminate a running query, but every DBMS I know does implement a KILL-command or similar. For example, in MySQL a user can use the SHOW [FULL] PROCESSLIST to view all running queries (and their states, query IDs, etc). Users with the KILL privilege can then terminate a query.

大多数KILL的发生是因为查询有运行时间过长或阻塞其他查询的风险，例如.表缺少索引或磁盘已满.如果您不关心结果(例如，用户取消了网站导航)，则通常Web服务器本身将中止该过程，从而中止其自身的查询(无需手动或程序员交互)

Most KILLs happen because a query risks running too long or is blocking other queries, eg. the table is missing an index or the disk is full. When you don't care about the result (eg. user cancelled site navigation), often the webserver itself will abort the process and hence the query in itself (no manual or programmer interaction necessary)

这篇关于SQL:中断查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！