重排数据库替代ORDER BY提升查询性能是否可行？

• 数据库规模：约12万条静态记录（数据无变更），用于自动补全功能的前缀匹配查询（WHERE ... LIKE 'test%'）
• 性能问题：添加ORDER BY relevance DESC后，查询耗时翻倍（原平均100ms，延迟明显）
• 疑问：能否通过按relevance重排整个数据库，移除ORDER BY来提升性能？能否保证SQL查询始终按数据插入顺序遍历表？
• 当前查询语句：

select *
from hao2_dict
where definitions like 'ba%'
   or searchable_pinyin like 'ba%'
ORDER BY relevance DESC
LIMIT 100

• 补充信息：
  • 数据库结构：包含id、word、searchable_pinyin、definitions、relevance等字段
  • 性能测试结果：
    • 使用relevance DESC索引时，搜索b%耗时50ms（比无索引快）；搜索banana%耗时超1700ms（远慢于无索引）
  • 执行计划（EXPLAIN结果）：
    • 搜索b%的执行计划：

0   Init    0   27  0       0
1   Noop    1   11  0       0
2   Integer 100 1   0       0
3   OpenRead    0   5   0   9   0
4   OpenRead    2   4223    0   k(2,-,) 0
5   Rewind  2   26  2   0   0
6   DeferredSeek    2   0   0       0
7   Column  0   6   4       0
8   Function    1   3   2   like(2) 0
9   If  2   13  0       0
10  Column  0   4   6       0
11  Function    1   5   2   like(2) 0
12  IfNot   2   25  1       0
13  IdxRowid    2   7   0       0
14  Column  0   1   8       0
15  Column  0   2   9       0
16  Column  0   3   10      0
17  Column  0   4   11      0
18  Column  0   5   12      0
19  Column  0   6   13      0
20  Column  0   7   14      0
21  Column  2   0   15      0
22  RealAffinity    15  0   0       0
23  ResultRow   7   9   0       0
24  DecrJumpZero    1   26  0       0
25  Next    2   6   0       1
26  Halt    0   0   0       0
27  Transaction 0   0   10  0   1
28  String8 0   3   0   b%  0
29  String8 0   5   0   b%  0
30  Goto    0   1   0       0

- 搜索`banana%`的执行计划：

0   Init    0   27  0       0
1   Noop    1   11  0       0
2   Integer 100 1   0       0
3   OpenRead    0   5   0   9   0
4   OpenRead    2   4223    0   k(2,-,) 0
5   Rewind  2   26  2   0   0
6   DeferredSeek    2   0   0       0
7   Column  0   6   4       0
8   Function    1   3   2   like(2) 0
9   If  2   13  0       0
10  Column  0   4   6       0
11  Function    1   5   2   like(2) 0
12  IfNot   2   25  1       0
13  IdxRowid    2   7   0       0
14  Column  0   1   8       0
15  Column  0   2   9       0
16  Column  0   3   10      0
17  Column  0   4   11      0
18  Column  0   5   12      0
19  Column  0   6   13      0
20  Column  0   7   14      0
21  Column  2   0   15      0
22  RealAffinity    15  0   0       0
23  ResultRow   7   9   0       0
24  DecrJumpZero    1   26  0       0
25  Next    2   6   0       1
26  Halt    0   0   0       0
27  Transaction 0   0   10  0   1
28  String8 0   3   0   banana% 0
29  String8 0   5   0   banana% 0
30  Goto    0   1   0       0

关于重排数据库移除ORDER BY的可行性 #

1. 能否依赖插入顺序返回结果？
   绝大多数关系型数据库（从执行计划判断你用的是SQLite）不保证默认查询结果的顺序与插入顺序一致，除非显式指定ORDER BY。哪怕你按relevance降序重新插入数据，存储引擎可能因为页分裂、索引维护等操作打乱物理顺序，后续查询无法确保按该顺序返回，所以这种方式不可靠。

2. 静态数据库的替代方案
   既然数据不会变更，可以直接生成一个按relevance DESC预排序的新表，再在新表上创建前缀索引适配查询：

   -- 创建预排序新表
   CREATE TABLE hao2_dict_sorted AS
   SELECT * FROM hao2_dict ORDER BY relevance DESC;
   
   -- 创建前缀索引
   CREATE INDEX idx_def_prefix ON hao2_dict_sorted(definitions);
   CREATE INDEX idx_pinyin_prefix ON hao2_dict_sorted(searchable_pinyin);
   

   之后查询语句改为：

   SELECT *
   FROM hao2_dict_sorted
   WHERE definitions LIKE 'ba%' OR searchable_pinyin LIKE 'ba%'
   LIMIT 100;
   

   这种方式不需要额外排序，直接取符合条件的前100条即可。

针对现有索引性能问题的优化 #

从测试结果看，relevance DESC索引在宽泛前缀（如b%）时高效，但精准前缀（如banana%）时低效，原因是数据库选择遍历整个relevance索引再过滤条件，匹配结果少的时候这种方式开销极大。可以通过以下方式优化：

1. 拆分查询并强制使用前缀索引
   给definitions和searchable_pinyin单独创建前缀索引，用UNION ALL拆分两个查询条件，分别使用对应索引后再合并排序：

   -- 创建前缀索引
   CREATE INDEX idx_definitions_prefix ON hao2_dict(definitions);
   CREATE INDEX idx_pinyin_prefix ON hao2_dict(searchable_pinyin);
   
   -- 拆分查询
   SELECT *
   FROM hao2_dict INDEXED BY idx_definitions_prefix
   WHERE definitions LIKE 'ba%'
   UNION ALL
   SELECT *
   FROM hao2_dict INDEXED BY idx_pinyin_prefix
   WHERE searchable_pinyin LIKE 'ba%' AND definitions NOT LIKE 'ba%'
   ORDER BY relevance DESC
   LIMIT 100;
   

   这种方式减少了需要过滤的行数，提升精准前缀查询的性能。

2. 创建复合索引
   构建(字段名, relevance DESC)的复合索引，让数据库匹配前缀后直接按relevance降序返回结果，避免全局排序：

   CREATE INDEX idx_def_relevance ON hao2_dict(definitions, relevance DESC);
   CREATE INDEX idx_pinyin_relevance ON hao2_dict(searchable_pinyin, relevance DESC);
   

   复合索引能让数据库找到匹配前缀的记录后直接按目标顺序返回，不管是宽泛还是精准前缀，性能都会更稳定。

内容的提问来源于stack exchange，提问作者Basti