Repository navigation
Commit 52b2d92
committed
perf(dedupe): 给定向候选配 (type, TRIM(title)) 表达式索引,并用 EXPLAIN 钉住它
定向候选把「该类型前 100 条」换成 `type = ? AND TRIM(title) = ?`,代价随最大类型的行数
线性长。本机 node:sqlite 实测(同类型 1000 / 5000 / 20000 行):
现口径 0.13 / 0.68 / 4.63 ms,加这条索引后 ~0.00 ms。
TRIM 本身不是瓶颈——同句去掉 TRIM 只差 0.07 ms,缺的是一条能把整个条件当 seek 用的
索引;而这是每存一条都要付一次的写路径,20000 行那一档不该赌「用户到不了」。
索引表达式必须与 SQL 逐字相同,写歪一点(用 title、列序不对)优化器静默不认,而行为
用例全绿——所以补一条 EXPLAIN QUERY PLAN 用例,钉的是「这条查询真的选中这条索引」,
不是「索引存在」。存量库靠 CREATE INDEX IF NOT EXISTS 在打开时补上,与 share 那两条
索引同一写法。1 parent f15190f commit 52b2d92
3 files changed
Lines changed: 48 additions & 0 deletions
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
915 | 915 | | |
916 | 916 | | |
917 | 917 | | |
| 918 | + | |
| 919 | + | |
| 920 | + | |
| 921 | + | |
| 922 | + | |
| 923 | + | |
| 924 | + | |
| 925 | + | |
| 926 | + | |
918 | 927 | | |
919 | 928 | | |
920 | 929 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
915 | 915 | | |
916 | 916 | | |
917 | 917 | | |
| 918 | + | |
| 919 | + | |
| 920 | + | |
| 921 | + | |
| 922 | + | |
| 923 | + | |
| 924 | + | |
| 925 | + | |
| 926 | + | |
918 | 927 | | |
919 | 928 | | |
920 | 929 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
9 | 9 | | |
10 | 10 | | |
11 | 11 | | |
| 12 | + | |
| 13 | + | |
| 14 | + | |
| 15 | + | |
12 | 16 | | |
13 | 17 | | |
14 | 18 | | |
| |||
148 | 152 | | |
149 | 153 | | |
150 | 154 | | |
| 155 | + | |
| 156 | + | |
| 157 | + | |
| 158 | + | |
| 159 | + | |
| 160 | + | |
| 161 | + | |
| 162 | + | |
| 163 | + | |
| 164 | + | |
| 165 | + | |
| 166 | + | |
| 167 | + | |
| 168 | + | |
| 169 | + | |
| 170 | + | |
| 171 | + | |
| 172 | + | |
| 173 | + | |
| 174 | + | |
| 175 | + | |
| 176 | + | |
| 177 | + | |
| 178 | + | |
| 179 | + | |
| 180 | + | |
0 commit comments