SQLite查询的执行时间:单位

本文介绍了SQLite查询的执行时间:单位的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如 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