sql - 索引列，多列编号的SQL查询速度慢

Question

我有一个由6个数字组成的表作为主键

CREATE TABLE table1 ( num1 decimal, num2 int, num3 int, num4 bigint, num5 bigint, num6 bigint,
PRIMARY KEY (num1, num2, num3, num4, num5, num6))

我需要按排序顺序访问该表，并且经常需要查询该表以依次查找下N个大数及其相关数据。

所以我写的查询是这样的

SELECT * FROM table1 WHERE  
 num1 >? OR (  
 (num1 == ? AND num2 > ?) OR (  
 (num1 == ? AND num2 == ? AND num3 > ?) OR (  
 (num1 == ? AND num2 == ? AND num3 == ? AND num4 > ? OR (  
 (num1 == ? AND num2 == ? AND num3 == ? AND num4 == ? AND num5 > ?) OR (  
 (num1 == ? AND num2 == ? AND num3 == ?  
 AND num4 == ? AND num5 == ? AND num6 > ?)))))) ORDER BY num1, num2, num3, num4, num5, num6  
 LIMIT ?;

这是我看到的查找下一个最大键的最佳方法，并且确实按索引的顺序进行查询。但是....查询需要几秒钟，这是我不喜欢的。

有什么办法可以提高性能？在一千万行的表上执行需要花费几秒钟，而我需要它在100ms的数量上执行更多。

查询计划：

"SEARCH TABLE table1 USING INDEX sqlite_autoindex_table1_1 (num1>?) (~250000 rows)"
"SEARCH TABLE table1 USING INDEX sqlite_autoindex_table1_1 (num1=? AND num2>?) (~2 rows)"
"SEARCH TABLE table1 USING INDEX sqlite_autoindex_table1_1 (num1=? AND num2=? AND num3>?) (~2 rows)"
"SEARCH TABLE table1 USING INDEX sqlite_autoindex_table1_1 (num1=? AND num2=? AND num3=? AND num4>?) (~2 rows)"
"SEARCH TABLE table1 USING INDEX sqlite_autoindex_table1_1 (num1=? AND num2=? AND num3=? AND num4=? AND num5>?) (~1 rows)"
"SEARCH TABLE table1 USING INDEX sqlite_autoindex_table1_1 (num1=? AND num2=? AND num3=? AND num4=? AND num5=? AND num6>?) (~1 rows)"
"USE TEMP B-TREE FOR ORDER BY"

编辑：

为什么这不可能呢？我真的想在INDEXED ORDER中得到东西，由 ORDER BY关键字生成相同的顺序吗？

Answer 1

与其他更复杂的RDBMS相反，sqlite具有基于规则的查询优化器，这意味着执行计划主要取决于查询的编写方式（以及子句的顺序）。它使优化器非常可预测，如果您知道sqlite如何生成执行计划，则可以利用这种可预测性来解决问题。

第一个想法是要注意，诸如（num1>？）或（num1 =？和num2>？）之类的各种子句会产生不相交的结果，并且这些结果自然会在彼此之间进行排序。如果将查询分为多个子查询（每个子查询都处理部分条件）以产生已排序的结果，那么，如果以正确的顺序执行子查询，则所有结果集的连接也将被排序。

例如，考虑以下查询：

select * from table1 where num1=? and num2>? order by num1,num2
select * from table1 where num1>? order by num1,num2

这些查询产生的两个结果集是不相交的，并且第一个结果集的行始终排在第二个结果集的行之前。

第二个想法是了解sqlite如何处理LIMIT子句。实际上，它在查询开始时声明了一个计数器，并在每个选定的行上递减并测试该计数器，因此可以尽早停止查询。

例如，考虑以下查询：

.explain
explain select * from (
   select * from table1 where num1=? and num2>?
   union all
   select * from table1 where num1>?
) limit 10;

sqlite将按查询中指定的顺序评估子查询。如果第一个子查询返回的行数超过10，则第二个子查询甚至都不会执行。
通过显示计划可以很容易地检查它：

addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Trace          0     0     0                    00               
1     Integer        10    1     0                    00               
2     Variable       1     2     2                    00               
3     Goto           0     44    0                    00               
4     OpenRead       3     3     0     keyinfo(6,BINARY,BINARY)  00               
5     SCopy          2     4     0                    00               
6     IsNull         4     23    0                    00               
7     SCopy          3     5     0                    00               
8     IsNull         5     23    0                    00               
9     Affinity       4     2     0     cd             00               
10    SeekGt         3     23    4     2              00               
11    IdxGE          3     23    4     1              01               
12    Column         3     1     6                    00               
13    IsNull         6     22    0                    00               
14    Column         3     0     7                    00               
15    Column         3     1     8                    00               
16    Column         3     2     9                    00               
17    Column         3     3     10                   00               
18    Column         3     4     11                   00               
19    Column         3     5     12                   00               
20    ResultRow      7     6     0                    00               
21    IfZero         1     23    -1                   00               
22    Next           3     11    0                    00               
23    Close          3     0     0                    00               
24    IfZero         1     43    0                    00               
25    Variable       3     13    1                    00               
26    OpenRead       4     3     0     keyinfo(6,BINARY,BINARY)  00               
27    SCopy          13    14    0                    00               
28    IsNull         14    42    0                    00               
29    Affinity       14    1     0     c              00               
30    SeekGt         4     42    14    1              00               
31    Column         4     0     6                    00               
32    IsNull         6     41    0                    00               
33    Column         4     0     7                    00               
34    Column         4     1     8                    00               
35    Column         4     2     9                    00               
36    Column         4     3     10                   00               
37    Column         4     4     11                   00               
38    Column         4     5     12                   00               
39    ResultRow      7     6     0                    00               
40    IfZero         1     42    -1                   00               
41    Next           4     31    0                    00               
42    Close          4     0     0                    00               
43    Halt           0     0     0                    00               
44    Transaction    0     0     0                    00               
45    VerifyCookie   0     3     0                    00               
46    TableLock      0     2     0     table1         00               
47    Goto           0     4     0                    00               

将该计数器声明为步骤1，并在步骤21、24、40进行减量/测试。

通过将这两个说明结合起来，我们可以提出一个查询，它看起来并不漂亮，但是会产生一个有效的执行计划：

SELECT * FROM (
  SELECT * FROM ( SELECT * FROM table1
                  WHERE num1 == ? AND num2 == ? AND num3 == ? AND num4 == ? AND num5 == ? AND num6 > ?
                  ORDER BY num1, num2, num3, num4, num5, num6 )
  UNION ALL
  SELECT * FROM ( SELECT * FROM table1
                  WHERE num1 == ? AND num2 == ? AND num3 == ? AND num4 == ? AND num5 > ?
                  ORDER BY num1, num2, num3, num4, num5, num6 )
  UNION ALL
  SELECT * FROM ( SELECT * FROM table1
                  WHERE num1 == ? AND num2 == ? AND num3 == ? AND num4 > ?
                  ORDER BY num1, num2, num3, num4, num5, num6 )
  UNION ALL
  SELECT * FROM ( SELECT * FROM table1
                  WHERE num1 == ? AND num2 == ? AND num3 > ?
                  ORDER BY num1, num2, num3, num4, num5, num6 )
  UNION ALL
  SELECT * FROM ( SELECT * FROM table1
                  WHERE num1 == ? AND num2 > ?
                  ORDER BY num1, num2, num3, num4, num5, num6 )
  UNION ALL
  SELECT * FROM ( SELECT * FROM table1
                  WHERE num1 > ?
                  ORDER BY num1, num2, num3, num4, num5, num6 )
) LIMIT ?;

注意，由于外部查询中不需要“ order by”子句，因此不需要sqlite执行所有子查询。因此，只要行数正确，它就可以停止。子查询的顺序至关重要。

需要第二层内部子查询，因为不可能在“全部合并”之前使用“ order by”。它们通过sqlite进行了优化，因此这不是问题。

在包含777K行的虚拟表上，初始查询成本为：

strace -c -eread,lseek sqlite3 toto.db < q1.sql
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 63.57    0.001586           0     18556           read
 36.43    0.000909           0     18544           lseek
------ ----------- ----------- --------- --------- ----------------
100.00    0.002495                 37100           total

而我的仅花费：

strace -c -eread,lseek sqlite3 toto.db < q3.sql
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
  -nan    0.000000           0        18           read
  -nan    0.000000           0         8           lseek
------ ----------- ----------- --------- --------- ----------------
100.00    0.000000                    26           total