是否有分析 sqlite 查询的工具? [英] Is there a tool to profile sqlite queries?

问题描述

我正在使用 SQLite 数据库并希望加快我的查询速度，可能是使用索引或完全重组它们.

I am using a SQLite database and would like to speed up my queries, perhaps with indexes or by restructuring them altogether.

是否有分析查询的工具，可以帮助我确定哪些地方速度变慢了?

Is there a tool to profile queries, that might help me decide where things are slowing down?

我知道我可以将查询输入到 SQLite Administrator 之类的工具中来为它们计时，但我正在寻找比这更系统的东西——也许是在后台查看我的所有查询的东西输入一段时间，给出任何瓶颈的细分.

I know I could just enter queries into a tool like SQLite Administrator to time them, but I'm looking for something a bit more systematic than that -- perhaps something that sits in the background and looks at all the queries that I enter over a period, giving a breakdown of any bottle necks.

推荐答案

这里有各种各样的问题.要查看运行哪些查询以及每个查询需要多长时间，您需要修改 sqlite3.dll，如果应用程序链接到它，或者如果它是您自己的应用程序，您可以更轻松地将其写入您的代码(我们这样做并且很长)查询、交易、计时等).

You have a mix of questions in here. To view what queries are run and how long each takes, you'll need to either modify sqlite3.dll if an application is linking to that or if it's your own application you can write it into your code easier (we do this and long all queries, transactions, timings, etc.).

对于单个查询分析，您可以使用EXPLAIN.它不会告诉您查询中各个步骤的时间安排，但会告诉您查询是如何执行的.

For individual query analysis, you can use EXPLAIN. It won't tell you timing of individual steps within a query but it will tel you how the query was executed.

http://www.sqlite.org/lang_explain.html

SQL 语句之前可以使用关键字EXPLAIN"；或通过短语解释查询计划".任何一种修改都会导致 SQL 语句作为查询运行，并返回有关在省略 EXPLAIN 关键字或短语的情况下 SQL 语句将如何操作的信息.

An SQL statement can be preceded by the keyword "EXPLAIN" or by the phrase "EXPLAIN QUERY PLAN". Either modification causes the SQL statement to behave as a query and to return information about how the SQL statement would have operated if the EXPLAIN keyword or phrase had been omitted.

当 EXPLAIN 关键字单独出现时，它会导致语句表现为查询，返回虚拟机指令序列，如果 EXPLAIN 关键字不存在，它将用于执行命令.当 EXPLAIN QUERY PLAN 短语出现时，该语句会返回有关将使用哪些索引的高级信息.

When the EXPLAIN keyword appears by itself it causes the statement to behave as a query that returns the sequence of virtual machine instructions it would have used to execute the command had the EXPLAIN keyword not been present. When the EXPLAIN QUERY PLAN phrase appears, the statement returns high-level information about what indices would have been used.

EXPLAIN 和 EXPLAIN QUERY PLAN 的输出仅用于交互式分析和故障排除.从 SQLite 的一个版本到下一个版本，输出格式的详细信息可能会发生变化.应用程序不应使用 EXPLAIN 或 EXPLAIN QUERY PLAN，因为它们的确切行为未记录、未指定且可变.

The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and troubleshooting only. The details of the output format are subject to change from one release of SQLite to the next. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since their exact behavior is undocumented, unspecified, and variable.

这篇关于是否有分析 sqlite 查询的工具?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！