我感到困惑的是以下两个查询产生相同输出的运行时间.这些查询正在sqlite 3.7.9上运行,大约有450万行的表,每个生成约50行的结果.
以下是查询：
% echo "SELECT DISTINCT acolumn FROM atable ORDER BY acolumn;" | time sqlite3 mydb sqlite3 mydb 8.87s user 15.06s system 99% cpu 23.980 total % echo "SELECT acolumn FROM (SELECT DISTINCT acolumn FROM atable) ORDER BY acolumn;" | time sqlite3 options sqlite3 mydb 1.15s user 0.10s system 98% cpu 1.267 total
两个查询的表现不应该更接近吗？我明白,查询计划程序可能会以不同的顺序执行“排序”和“不同”操作,但如果是,是否需要？还是应该能够弄清楚如何做到最快？
编辑：根据要求,每个查询的“EXPLAIN QUERY PLAN”命令的输出.
对于第一个(单片)查询：
0|0|0|SCAN TABLE atable (~1000000 rows) 0|0|0|USE TEMP B-TREE FOR DISTINCT
1|0|0|SCAN TABLE atable (~1000000 rows) 1|0|0|USE TEMP B-TREE FOR DISTINCT 0|0|0|SCAN SUBQUERY 1 (~1000000 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY
您的第一个查询首先将记录插入到排序的临时表中,然后通过遍历它们并返回仅与前一个不相同的那些来执行DISTINCT.
(这可以在下面显示的EXPLAIN输出中看到; DISTINCT实际上被转换为一个GROUP BY,其行为相同.)
原文链接：https://www.f2er.com/sqlite/197595.html(这可以在下面显示的EXPLAIN输出中看到; DISTINCT实际上被转换为一个GROUP BY,其行为相同.)
您的第二个查询在理论上与第一个查询相同,但是sqlite的查询优化器相当简单,无法证明此转换是安全的(如subquery flattening documentation所述).
因此,通过首先执行DISTINCT,仅将任何非重复项插入到临时表中,然后使用第二个临时表执行ORDER BY来实现.
第二步完全是多余的,因为第一个临时表已经被排序了,但是对于你的数据来说,这恰好是更快的,因为你有这么多重复的东西从来没有存储在任何一个临时表中.
理论上,您的第一个查询可能会更快,因为sqlite已经认识到DISTINCT和ORDER BY子句可以使用相同的排序临时表来实现.
然而,实际上,sqlite它不够聪明,不能记住DISTINCT意味着重复项不需要存储在临时表中.
(这个特殊的优化可能会添加到sqlite,如果你在mailing list很好地问.)
$sqlite3 mydb sqlite> .explain sqlite> explain SELECT DISTINCT acolumn FROM atable ORDER BY acolumn; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 SorterOpen 1 2 0 keyinfo(1,BINARY) 00 2 Integer 0 3 0 00 clear abort flag 3 Integer 0 2 0 00 indicate accumulator empty 4 Null 0 6 6 00 5 Gosub 5 37 0 00 6 Goto 0 40 0 00 7 OpenRead 0 2 0 1 00 atable 8 Rewind 0 14 0 00 9 Column 0 0 8 00 atable.acolumn 10 Sequence 1 9 0 00 11 MakeRecord 8 2 10 00 12 SorterInsert 1 10 0 00 13 Next 0 9 0 01 14 Close 0 0 0 00 15 OpenPseudo 2 10 2 00 16 SorterSort 1 39 0 00 GROUP BY sort 17 SorterData 1 10 0 00 18 Column 2 0 7 20 19 Compare 6 7 1 keyinfo(1,BINARY) 00 20 Jump 21 25 21 00 21 Move 7 6 0 00 22 Gosub 4 32 0 00 output one row 23 IfPos 3 39 0 00 check abort flag 24 Gosub 5 37 0 00 reset accumulator 25 Column 2 0 1 00 26 Integer 1 2 0 00 indicate data in accumulator 27 SorterNext 1 17 0 00 28 Gosub 4 32 0 00 output final row 29 Goto 0 39 0 00 30 Integer 1 3 0 00 set abort flag 31 Return 4 0 0 00 32 IfPos 2 34 0 00 Groupby result generator entry point 33 Return 4 0 0 00 34 Copy 1 11 0 00 35 ResultRow 11 1 0 00 36 Return 4 0 0 00 end groupby result generator 37 Null 0 1 0 00 38 Return 5 0 0 00 39 Halt 0 0 0 00 40 Transaction 0 0 0 00 41 VerifyCookie 0 2 0 00 42 TableLock 0 2 0 atable 00 43 Goto 0 7 0 00
sqlite> explain SELECT acolumn FROM (SELECT DISTINCT acolumn FROM atable) ORDER BY acolumn; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 00 1 Goto 0 39 0 00 2 Goto 0 17 0 00 3 OpenPseudo 0 3 1 01 coroutine for sqlite_subquery_DA7480_ 4 Integer 0 2 0 01 5 OpenEphemeral 2 0 0 keyinfo(1,BINARY) 08 6 OpenRead 1 2 0 1 00 atable 7 Rewind 1 14 0 00 8 Column 1 0 3 00 atable.acolumn 9 Found 2 13 3 1 00 10 MakeRecord 3 1 4 00 11 IdxInsert 2 4 0 00 12 Yield 1 0 0 00 13 Next 1 8 0 01 14 Close 1 0 0 00 15 Integer 1 2 0 00 16 Yield 1 0 0 00 end sqlite_subquery_DA7480_ 17 SorterOpen 3 3 0 keyinfo(1,BINARY) 00 18 Integer 2 1 0 00 19 Yield 1 0 0 00 next row of co-routine sqlite_subquery_DA7480_ 20 If 2 29 0 00 21 Column 0 0 5 00 sqlite_subquery_DA7480_.acolumn 22 MakeRecord 5 1 6 00 23 Column 0 0 7 00 sqlite_subquery_DA7480_.acolumn 24 Sequence 3 8 0 00 25 Move 6 9 0 00 26 MakeRecord 7 3 10 00 27 SorterInsert 3 10 0 00 28 Goto 0 19 0 00 29 OpenPseudo 4 6 1 00 30 OpenPseudo 5 11 3 00 31 SorterSort 3 37 0 00 32 SorterData 3 11 0 00 33 Column 5 2 6 20 34 Column 4 0 5 20 35 ResultRow 5 1 0 00 36 SorterNext 3 32 0 00 37 Close 4 0 0 00 38 Halt 0 0 0 00 39 Transaction 0 0 0 00 40 VerifyCookie 0 2 0 00 41 TableLock 0 2 0 atable 00 42 Goto 0 2 0 00
