如何分析 Sqlite 查询执行? [英] How can I analyse a Sqlite query execution?
问题描述
我有一个 Sqlite 数据库,我想检查索引是否正确.MS SQL Analyzer 非常擅长分解查询执行和使用的索引.
I have a Sqlite database which I want to check the indexes are correct. MS SQL Analyser is great at breaking down the query execution and utilised indexes.
Sqlite 有没有类似的工具?
Is there a similar tool for Sqlite?
推荐答案
我知道没有漂亮的图形工具,但是您可以通过 EXPLAIN
关键字获得所有信息.
I know of no pretty graphical tools, but all of the information you seek is available from the EXPLAIN
keyword.
考虑这个数据库:
sqlite> create table users (name, email);
sqlite> create index user_names on users (name);
基于 email
的查询不会使用索引:
A query predicated on email
will not use an index:
sqlite> explain select * from users where email='foo';
地址 | 操作码 | p1 | p2 | p3 | p4 | p5 | 评论 |
---|---|---|---|---|---|---|---|
0 | 跟踪 | 0 | 0 | 0 | 00 | ||
1 | String8 | 0 | 1 | 0 | 脚 | 00 | |
2 | 转到 | 0 | 13 | 0 | 00 | ||
3 | 打开读取 | 0 | 2 | 0 | 2 | 00 | |
4 | 倒带 | 0 | 11 | 0 | 00 | ||
5 | 列 | 0 | 1 | 2 | 00 | ||
6 | Ne | 1 | 10 | 2 | collseq(BINARY) | 6a | |
7 | 列 | 0 | 0 | 4 | 00 | ||
8 | 列 | 0 | 1 | 5 | 00 | ||
9 | 结果行 | 4 | 2 | 0 | 00 | ||
10 | 下一个 | 0 | 5 | 0 | 01 | ||
11 | 关闭 | 0 | 0 | 0 | 00 | ||
12 | 暂停 | 0 | 0 | 0 | 00 | ||
13 | 交易 | 0 | 0 | 0 | 00 | ||
14 | 验证Cookie | 0 | 5 | 0 | 00 | ||
15 | 表锁 | 0 | 2 | 0 | 用户 | 00 | |
16 | 转到 | 0 | 3 | 0 | 00 |
而基于名称的查询将使用 user_names
索引:
Whereas a query predicated on name will use the user_names
index:
sqlite> explain select * from users where name='foo';
地址 | 操作码 | p1 | p2 | p3 | p4 | p5 | 评论 |
---|---|---|---|---|---|---|---|
0 | 跟踪 | 0 | 0 | 0 | 00 | ||
1 | String8 | 0 | 1 | 0 | 脚 | 00 | |
2 | 转到 | 0 | 18 | 0 | 00 | ||
3 | 打开读取 | 0 | 2 | 0 | 2 | 00 | |
4 | 打开读取 | 1 | 3 | 0 | keyinfo(1,BINARY) | 00 | |
5 | IsNull | 1 | 15 | 0 | 00 | ||
6 | 亲和力 | 1 | 1 | 0 | bb | 00 | |
7 | SeekGe | 1 | 15 | 1 | 1 | 00 | |
8 | IdxGE | 1 | 15 | 1 | 1 | 01 | |
9 | IdxRowid | 1 | 2 | 0 | 00 | ||
10 | 寻找 | 0 | 2 | 0 | 00 | ||
11 | 列 | 1 | 0 | 3 | 00 | ||
12 | 列 | 0 | 1 | 4 | 00 | ||
13 | 结果行 | 3 | 2 | 0 | 00 | ||
14 | 下一个 | 1 | 8 | 0 | 00 | ||
15 | 关闭 | 0 | 0 | 0 | 00 | ||
16 | 关闭 | 1 | 0 | 0 | 00 | ||
17 | 暂停 | 0 | 0 | 0 | 00 | ||
18 | 交易 | 0 | 0 | 0 | 00 | ||
19 | 验证Cookie | 0 | 5 | 0 | 00 | ||
20 | 表锁 | 0 | 2 | 0 | 用户 | 00 | |
21 | 转到 | 0 | 3 | 0 | 00 |
使用 EXPLAIN
确实需要掌握 SQLite 的虚拟机 VDBE:
Using EXPLAIN
does require coming to grips with SQLite's virtual machine, VDBE:
http://www.sqlite.org/opcode.html
但这并不像看起来那么难,它会为您提供有关查询的完整故事.
But this is not as hard as it looks, and gives you the complete story about your query.
这篇关于如何分析 Sqlite 查询执行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!