SQLite查询的执行时间:单位 [英] Execution time of an SQLite Query: Units

问题描述

如 SQLite文档中所述，人们可以使用:

As described in SQLite Documentation one could use:

sqlite> .timer ON

或将相同的命令添加到〜/.sqliterc

or add the same command to ~/.sqliterc

完成此操作后，对于每个执行的查询，SQLite Shell都会使用CPU时间的用户和sys组件进行响应:

When this is done, the SQLite shell responds with user and sys components of CPU Time for every query executed:

user@machine% sqlite3 test.db
-- Loading resources from ~/.sqliterc

SQLite version 3.7.14 2012-09-03 15:42:36
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> select count(*) from my_table;
count(*)
10143270
CPU Time: user 0.199970 sys 1.060838

虽然我找到了此答案，以秒为单位提供了时间单位的证据，但我很难同意用它.当使用秒表计时查询的执行时间时，我发现几乎每个查询所花的时间都比shell所花费的时间长.例如，在上述示例中计时的查询大约实时花费了大约 1分钟54秒.这种差异的原因是什么?

While I found this answer providing evidence for the units of time being in seconds, I am having a hard time agreeing with it. When timing the execution of a query using a stopwatch, I find almost every query to be taking longer than what the shell times it as taking. For example, the query timed in the above example approximately took a little over 1 minute and 54 seconds in real time. What is the reason for this discrepancy?

那么，再次来个单位是什么?什么是用户和 sys 组件?

So once again, what are the units? What are user and sys components?

我正在通过NFS访问的Debian GNU/Linux 6.0.6(挤压)发行版上运行SQLite 3.7.14.

推荐答案

user是CPU在用户空间(即数据库本身)中执行代码所花费的时间； sys用于内核中的代码.

user is the time that the CPU spent executing code in user space (i.e., in the database itself); sys is for code in the kernel.

进行I/O时，CPU大部分时间都在等待.

When doing I/O, the CPU spends most of the time waiting.

足够新的SQLite版本还可以显示经过的实时时间:

Sufficiently recent versions of SQLite also show the elapsed real time:

SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
sqlite> UPDATE ...;
Run Time: real 36.591 user 17.362911 sys 1.809612

这篇关于SQLite查询的执行时间:单位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！