代码之家 › 专栏 › 技术社区 › pouya

SQLite慢速选择查询

sqlite3 sqlite sql

0

pouya · 技术社区 · 7 年前

我正在运行以下选择查询:

SELECT "entry"."id" AS "entry_id",
"entry"."input" AS "entry_input",
"entry"."output" AS "entry_output",
"entry"."numOfWords" AS "entry_numOfWords",
"entry"."times_seen" AS "entry_times_seen",
"word_class"."value" AS "word_class_value",
"dominant_noun"."noun" AS "dominant_noun_noun",
"dominant_noun"."article" AS "dominant_noun_article",
"dominant_noun"."isPluaral" AS "dominant_noun_isPluaral",
"subject"."subjectIndex" AS "subject_subjectIndex",
"last_time_visited"."value" AS "last_time_visited_value"
FROM "entry" "entry"
LEFT JOIN "word_class" "word_class" ON "word_class"."entryId"="entry"."id"
LEFT JOIN "dominant_noun" "dominant_noun" ON "dominant_noun"."entryId"="entry"."id"
LEFT JOIN "subject_entries_entry" "subject_entry" ON "subject_entry"."entryId"="entry"."id"
LEFT JOIN "subject" "subject" ON "subject"."id"="subject_entry"."subjectId"
LEFT JOIN "last_time_visited" "last_time_visited" ON "last_time_visited"."entryId"="entry"."id"
WHERE "entry"."inputLang" = 31
AND ("entry"."input" like '% hilfe %' OR "entry"."input" like 'hilfe %' OR "entry"."input" like '% hilfe')
ORDER BY "word_class"."value" DESC, "entry"."numOfWords" ASC;

时间结果:

real    0m15.100s
user    0m14.072s
sys     0m1.024s

根据此数据库架构:

CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE IF NOT EXISTS "subject" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "subjectIndex" tinyint NOT NULL);
CREATE TABLE IF NOT EXISTS "entry" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "inputLang" tinyint NOT NULL, "outputLang" tinyint NOT NULL, "input"
varchar NOT NULL, "output" varchar NOT NULL, "numOfWords" tinyint NOT NULL, "times_seen" integer NOT NULL DEFAULT (0));
CREATE TABLE IF NOT EXISTS "abbr" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "value" varchar NOT NULL, "entryId" integer, CONSTRAINT "REL_ca935aaf7
66cba1e7bfbe90275" UNIQUE ("entryId"), CONSTRAINT "FK_ca935aaf766cba1e7bfbe902757" FOREIGN KEY ("entryId") REFERENCES "entry" ("id"));
CREATE TABLE IF NOT EXISTS "word_class" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "value" integer NOT NULL, "entryId" integer, CONSTRAINT "REL_941
45442deb2b2209bd943a787" UNIQUE ("entryId"), CONSTRAINT "FK_94145442deb2b2209bd943a7874" FOREIGN KEY ("entryId") REFERENCES "entry" ("id"));
CREATE TABLE IF NOT EXISTS "dominant_noun" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "noun" varchar NOT NULL, "article" tinyint NOT NULL, "isPluar
al" boolean NOT NULL, "entryId" integer, CONSTRAINT "REL_f493eeedea653d8a89f595c82c" UNIQUE ("entryId"), CONSTRAINT "FK_f493eeedea653d8a89f595c82c4" FOREI
GN KEY ("entryId") REFERENCES "entry" ("id"));
CREATE TABLE IF NOT EXISTS "last_time_visited" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "value" datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP), "e
ntryId" integer, CONSTRAINT "REL_e631a6f55d59214f8e6aaa6447" UNIQUE ("entryId"), CONSTRAINT "FK_e631a6f55d59214f8e6aaa64478" FOREIGN KEY ("entryId") REFER
ENCES "entry" ("id"));
CREATE TABLE IF NOT EXISTS "subject_entries_entry" ("subjectId" integer NOT NULL, "entryId" integer NOT NULL, CONSTRAINT "FK_d2eaa7a84a7963ed94e472cef0b"FOREIGN KEY ("subjectId") REFERENCES "subject" ("id") ON DELETE CASCADE, CONSTRAINT "FK_5f940450dd4c681a9fecf0b14b2" FOREIGN KEY ("entryId") REFERENCES "entry" ("id") ON DELETE CASCADE, PRIMARY KEY ("subjectId", "entryId"));
CREATE INDEX "IDX_3091789786b922bee00bbb44b1" ON "entry" ("inputLang") ;
CREATE INDEX "IDX_36ab3550b9e3ef647d1230affc" ON "entry" ("outputLang") ;
CREATE INDEX "IDX_1b0f6266dffb9a7e6343e7faa4" ON "entry" ("input") ;
CREATE INDEX "IDX_a77c7936ea412ec1958007154a" ON "entry" ("numOfWords") ;
CREATE INDEX "IDX_b32699a03d36223ff9bad94ea6" ON "entry" ("times_seen") ;

解释结果:

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     109   0                    00  Start at 109
1     SorterOpen     6     14    0     k(2,-B,B)      00
2     OpenRead       0     12    0     7              00  root=12 iDb=0; entry
3     OpenRead       1     2     0     3              00  root=2 iDb=0; word_class
4     OpenRead       7     3     0     k(2,,)         02  root=3 iDb=0; sqlite_autoindex_word_class_1
5     OpenRead       2     5     0     5              00  root=5 iDb=0; dominant_noun
6     OpenRead       8     6     0     k(2,,)         02  root=6 iDb=0; sqlite_autoindex_dominant_noun_1
7     OpenRead       3     10    0     2              00  root=10 iDb=0; subject_entries_entry
8     OpenRead       4     9     0     2              00  root=9 iDb=0; subject
9     OpenRead       5     7     0     3              00  root=7 iDb=0; last_time_visited
10    OpenRead       9     8     0     k(2,,)         02  root=8 iDb=0; sqlite_autoindex_last_time_visited_1
11    Rewind         0     92    0                    00
12      Column         0     1     1                    00  r[1]=entry.inputLang
13      Ne             2     91    1     (BINARY)       54  if r[1]!=r[2] goto 91
14      Column         0     3     4                    00  r[4]=entry.input
15      Function0      1     3     1     like(2)        02  r[1]=func(r[3..4])
16      If             1     23    0                    00
17      Column         0     3     6                    00  r[6]=entry.input
18      Function0      1     5     1     like(2)        02  r[1]=func(r[5..6])
19      If             1     23    0                    00
20      Column         0     3     8                    00  r[8]=entry.input
21      Function0      1     7     1     like(2)        02  r[1]=func(r[7..8])
22      IfNot          1     91    1                    00
23      Integer        0     9     0                    00  r[9]=0; init LEFT JOIN no-match flag
24      Rowid          0     10    0                    00  r[10]=rowid
25      SeekGE         7     87    10    1              00  key=r[10]
26      IdxGT          7     87    10    1              00  key=r[10]
27      DeferredSeek   7     0     1                    00  Move 1 to 7.rowid if needed
28      Integer        1     9     0                    00  r[9]=1; record LEFT JOIN hit
29      Integer        0     11    0                    00  r[11]=0; init LEFT JOIN no-match flag
30      Rowid          0     12    0                    00  r[12]=rowid
31      SeekGE         8     83    12    1              00  key=r[12]
32      IdxGT          8     83    12    1              00  key=r[12]
33      DeferredSeek   8     0     2                    00  Move 2 to 8.rowid if needed
34      Integer        1     11    0                    00  r[11]=1; record LEFT JOIN hit
35      Once           0     44    0                    00
36      OpenAutoindex  10    3     0     k(3,B,,)       00  nColumn=3; for subject_entries_entry
37      Rewind         3     44    0                    00
38        Column         3     1     13                   00  r[13]=subject_entries_entry.entryId
39        Column         3     0     14                   00  r[14]=subject_entries_entry.subjectId
40        Rowid          3     15    0                    00  r[15]=rowid
41        MakeRecord     13    3     1                    00  r[1]=mkrec(r[13..15])
42        IdxInsert      10    1     0                    10  key=r[1]
43      Next           3     38    0                    03
44      Integer        0     16    0                    00  r[16]=0; init LEFT JOIN no-match flag
45      Rowid          0     17    0                    00  r[17]=rowid
46      SeekGE         10    80    17    1              00  key=r[17]
47        IdxGT          10    80    17    1              00  key=r[17]
48        Integer        1     16    0                    00  r[16]=1; record LEFT JOIN hit
49        Integer        0     18    0                    00  r[18]=0; init LEFT JOIN no-match flag
50        Column         10    1     19                   00  r[19]=subject_entries_entry.subjectId
51        SeekRowid      4     76    19                   00  intkey=r[19]
52        Integer        1     18    0                    00  r[18]=1; record LEFT JOIN hit
53        Integer        0     20    0                    00  r[20]=0; init LEFT JOIN no-match flag
54        Rowid          0     21    0                    00  r[21]=rowid
55        SeekGE         9     72    21    1              00  key=r[21]
56        IdxGT          9     72    21    1              00  key=r[21]
57        DeferredSeek   9     0     5                    00  Move 5 to 9.rowid if needed
58        Integer        1     20    0                    00  r[20]=1; record LEFT JOIN hit
59        Rowid          0     24    0                    00  r[24]=rowid
60        Column         0     3     25                   00  r[25]=entry.input
61        Column         0     4     26                   00  r[26]=entry.output
62        Column         0     6     27    0              00  r[27]=entry.times_seen
63        Column         2     1     28                   00  r[28]=dominant_noun.noun
64        Column         2     2     29                   00  r[29]=dominant_noun.article
65        Column         2     3     30                   00  r[30]=dominant_noun.isPluaral
66        Column         4     1     31                   00  r[31]=subject.subjectIndex
67        Column         5     1     32                   00  r[32]=last_time_visited.value
68        Column         1     1     22                   00  r[22]=word_class.value
69        Column         0     5     23                   00  r[23]=entry.numOfWords
70        MakeRecord     22    11    35                   00  r[35]=mkrec(r[22..32])
71        SorterInsert   6     35    22    11             00  key=r[35]
72        IfPos          20    76    0                    00  if r[20]>0 then r[20]-=0, goto 76
73        NullRow        5     0     0                    00
74        NullRow        9     0     0                    00
75        Goto           0     58    0                    00
76        IfPos          18    79    0                    00  if r[18]>0 then r[18]-=0, goto 79
77        NullRow        4     0     0                    00
78        Goto           0     52    0                    00
79      Next           10    47    0                    00
80      IfPos          16    83    0                    00  if r[16]>0 then r[16]-=0, goto 83
81      NullRow        10    0     0                    00
82      Goto           0     48    0                    00
83      IfPos          11    87    0                    00  if r[11]>0 then r[11]-=0, goto 87
84      NullRow        2     0     0                    00
85      NullRow        8     0     0                    00
86      Goto           0     34    0                    00
87      IfPos          9     91    0                    00  if r[9]>0 then r[9]-=0, goto 91
88      NullRow        1     0     0                    00
89      NullRow        7     0     0                    00
90      Goto           0     28    0                    00
91    Next           0     12    0                    01
92    OpenPseudo     11    36    14                   00  14 columns in r[36]
93    SorterSort     6     108   0                    00
94      SorterData     6     36    11                   00  r[36]=data
95      Column         11    10    34                   00  r[34]=last_time_visited_value
96      Column         11    9     33                   00  r[33]=subject_subjectIndex
97      Column         11    8     32                   00  r[32]=dominant_noun_isPluaral
98      Column         11    7     31                   00  r[31]=dominant_noun_article
99      Column         11    6     30                   00  r[30]=dominant_noun_noun
100     Column         11    0     29                   00  r[29]=word_class_value
101     Column         11    5     28                   00  r[28]=entry_times_seen
102     Column         11    1     27                   00  r[27]=entry_numOfWords
103     Column         11    4     26                   00  r[26]=entry_output
104     Column         11    3     25                   00  r[25]=entry_input
105     Column         11    2     24                   00  r[24]=entry_id
106     ResultRow      24    11    0                    00  output=r[24..34]
107   SorterNext     6     94    0                    00
108   Halt           0     0     0                    00
109   Transaction    0     0     348   0              01  usesStmtJournal=0
110   Integer        31    2     0                    00  r[2]=31
111   String8        0     3     0     % hilfe %      00  r[3]='% hilfe %'
112   String8        0     5     0     hilfe %        00  r[5]='hilfe %'
113   String8        0     7     0     % hilfe        00  r[7]='% hilfe'
114   Goto           0     1     0                    00

解释查询计划输出:

QUERY PLAN
|--SCAN TABLE entry AS entry
|--SEARCH TABLE word_class AS word_class USING INDEX sqlite_autoindex_word_class_1 (entryId=?)
|--SEARCH TABLE dominant_noun AS dominant_noun USING INDEX sqlite_autoindex_dominant_noun_1 (entryId=?)
|--SEARCH TABLE subject_entries_entry AS subject_entry USING AUTOMATIC COVERING INDEX (entryId=?)
|--SEARCH TABLE subject AS subject USING INTEGER PRIMARY KEY (rowid=?)
|--SEARCH TABLE last_time_visited AS last_time_visited USING INDEX sqlite_autoindex_last_time_visited_1 (entryId=?)
`--USE TEMP B-TREE FOR ORDER BY

分析输出:

subject||1437631
entry|IDX_b32699a03d36223ff9bad94ea6|2348382 2348382
entry|IDX_a77c7936ea412ec1958007154a|2348382 67097
entry|IDX_1b0f6266dffb9a7e6343e7faa4|2348382 2
entry|IDX_36ab3550b9e3ef647d1230affc|2348382 1174191
entry|IDX_3091789786b922bee00bbb44b1|2348382 1174191
abbr|sqlite_autoindex_abbr_1|42575 1
dominant_noun|sqlite_autoindex_dominant_noun_1|823071 1
word_class|sqlite_autoindex_word_class_1|2005516 1
subject_entries_entry|sqlite_autoindex_subject_entries_entry_1|1437631 1 1

通常需要10秒以上才能得到结果。虽然这是我第一次使用sqlite,但20秒的回复时间似乎很奇怪。如果我需要提供额外的信息来解决问题,请添加评论?

1 回复 | 直到 7 年前

1

2

Shawn 7 年前

您看到的长查询时间的因素是 subject_entries_entry 表。它是一个标准的连接表,用于关联 entry 表到行 subject 表。表定义使用的主键将主题ID放在第一位,然后是条目ID。( PRIMARY KEY ("subjectId", "entryId") )

另一方面,您的查询首先联接表中的条目ID,然后联接主题ID——与键中的顺序相反。sqlite可以并确实重新排序联接中的表,以尽可能提高效率,但在这种情况下,它不会这样做。去 EXPLAIN QUERY PLAN 输出:

SEARCH TABLE subject_entries_entry AS subject_entry USING AUTOMATIC COVERING INDEX (entryId=?)

这个 SEARCH 意味着它在索引中查找特定的行,而不是查找每个单独的行( SCAN )这是你想要的,但是 AUTOMATIC COVERING INDEX 部分是坏的。 AUTOMATIC 这意味着查询计划器没有找到可以使用的现有索引,但认为使用索引比扫描表要好,因此它会为该查询构建一个临时索引。看起来像 主题条目 表有很多行,因此这可能需要一些时间。

用主键列按照它们在联接中使用的相同顺序重新创建表可以将时间缩短很多(使用更多磁盘空间时,翻转列的单独索引也是如此)。

我对这张桌子的另一个建议是 WITHOUT ROWID 一个。正常的sqlite表使用64位整数主键(称为rowid),而不管表定义使用什么;非- INTEGER PRIMARY KEY 只是正常的 UNIQUE 此类表中的索引。用 WITHOUT ROWID ,主键是表的实际主键,在这种情况下,实际的rowid没有实际用途,可以节省空间。它没有复制每行内容的表和索引,而是只有一个表。不过,这种优化不会影响查询速度,因为它使用的是覆盖已经在索引中包含所有必需信息的索引;实际表在查询中甚至没有像现在这样被查看。

我不确定是否会有进一步的加速——看看查询计划,它对其余的表使用了预先存在的索引,并且join子句都很简单。我有点惊讶它没有使用索引 entry(inputLang) 搜索而不是扫描该表。如果你能用 SQLITE_ENABLE_STAT4 打开,然后 PRAGMA optimize 重建统计表,但这取决于你使用的语言(C或C++中的简单,其他的更难)。

编辑:

其他需要探索的事情:

使用启用多线程排序 PRAGMA threads=N 其中n是正在使用的核心数。
使用启用内存映射数据库 PRAGMA mmap_size=X .