optimization - Sqlite subselect 比 distinct + order by 快得多-6ren

optimization - Sqlite subselect 比 distinct + order by 快得多

转载作者：IT王子更新时间：2023-10-29 06:20:54

我对以下两个产生相同输出的查询的运行时间截然不同感到困惑。这些查询在 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 ，其行为相同。)

从理论上讲，您的第二个查询与第一个查询相同，但 SQLite 的查询优化器相当简单，无法证明此转换是安全的(如 subquery flattening documentation 中所述)。因此，它是通过执行 DISTINCT 来实现的。首先，通过仅将任何非重复项插入临时表，然后执行 ORDER BY有第二个临时表。第二步是完全多余的，因为第一个临时表已经排序，但无论如何这对您的数据来说会更快，因为您有太多重复项从未存储在任何一个临时表中。

理论上，您的第一个查询可能会更快，因为 SQLite 已经识别出 DISTINCT和 ORDER BY子句可以用相同排序的临时表来实现。然而，在实践中，SQLite 还不够聪明，无法记住 DISTINCT。暗示不需要将重复项存储在临时表中。(如果你在 mailing list 上很好地询问，这个特殊的优化可能会添加到 SQLite 中。)

$ 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

关于optimization - Sqlite subselect 比 distinct + order by 快得多，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/13533658/

文章推荐： javascript - 在 Windows 8 Javascript 应用程序中使用 SQLite3

文章推荐： ruby - 用多个定界符拆分字符串

文章推荐： c# - 为 x64 c# 获取一个有效的 SpatiaLite + SQLite 系统

css transition in 快，out 快
这看起来很基础，但我想不通。是否有一种简单的 CSS 唯一方法可以使 cssa 真正快速淡入并缓慢淡出。这是为了改变多个 div 的颜色。大约 0.5 秒的缓入和 2 秒的缓出。谢谢! 最佳答案你
Python+C 比纯 C 快(稍微)快
我一直在用各种语言和实现实现相同的代码(在 Blackjack 中发牌而不爆牌的方法的数量)。我注意到的一个奇怪之处是，Python 在 C 中调用分区函数的实现实际上比用 C 编写的整个程序快一点。
XPath:/比//快？
如果我没看错，/ 意味着它右边的节点必须是左边节点的直接子节点，例如/ul/li 返回 li 项，它们是作为文档根的 ul 项的直接子项。 //ul//li 返回 li 项，它们是文档中某处任何 ul
php - mysql随机更新一列(快)
如何随机更新一个表。所以你给一列一个随机值。并且该列(例如“顶部”)是唯一的。如果您在数字 10 到 20 之间进行选择，并且您有 10 行，那么您就不能有未使用的数字。如果你有 Test table
android - 位图创建和绘制速度慢...快
这在一小部分是一个问题(因为我不明白为什么它会有所不同)，在很大程度上是一篇希望能帮助其他一些可怜的程序员的帖子。我有一个代码库，是我大约 5-7 年前第一次开始 Android 编程时编写的，它具
sql - 为什么自连接比 or 快？
我正在尝试过滤关系表以获得满足两个条件的表子集(即:我想要 color_ids 为 1 或 2 的条目的所有 ID)。这是一张结实的 table ，所以我正在尝试尽可能多地进行优化。我想知道是否有人
聊聊PHP中的 === 运算符为什么比 == 快
在上一篇《聊聊PHP中require_once()函数为什么不好用》中给大家介绍了PHP中require_once()为什么不好用的原因，感兴趣的朋友可以去阅读了解一下~ 那么本文将给大家介绍PH
perl - 为什么 FastCGI 快？
很难说出这里问的是什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或言辞激烈，无法以目前的形式合理回答。如需帮助澄清此问题以便可以重新打开，visit the help center . 10年前关
OpenGL 读取像素的速度比 glReadPixels 快
有没有办法提高glReadPixels的速度?目前我做: Gdx.gl.glReadPixels(0, 0, Gdx.graphics.getWidth(), Gdx.graphics.getHeig
r - 为什么:=比`:=`()快？
通常，我以函数形式`:=`()来计算data.table中的多列，认为这是最有效的方法。但是我最近发现它比简单地重复使用:=慢。至少在我的电脑上。我猜想:=的功能形式可能会产生一些开销，但这是它变慢
.net - 为什么三个线程比 10+ 快
我的问题是针对 Windows 环境中多线程的性能问题。在测试我的代码后，我得到的结果是增加线程数不会提高并行计算的性能，并且在经过一些计数后变得更少。到底是怎么回事？是否可以找出最佳线程数的公式:
C: free() 下一个尺寸无效(快)
我看到很少有相同问题的主题，但我仍然无法解决我的问题。这是我的代码 - 使用 XOR 加密的 C 套接字编程当服务器和客户端连接时:- 用户发送消息，例如:你好- 服务器响应，例如:(服务器):你好
c# - 鼠标移动比重绘 wpf 快
我正在定义继承自 Shape 类并实现“几何”属性的形状。这是一个例子: public class Landmark : Shape { public override bool IsInB
Android 执行代码的速度比 PC 快
相同代码在 Android(1Ghz Snapdragon)上的执行速度比我在 3.3 Ghz Core 2 Duo 的 PC(在桌面应用程序中)快 2 倍(PC 的类被复制到 Android 项目)
php - 比 in_array 快？
我需要将一个值与一组数组进行比较。但是，我需要比较 foreach 中的多个值。如果使用 in_array，它可能会很慢，真的很慢。有没有更快的选择？我当前的代码是 foreach($a as $b)
Java 比 C 快
这个问题在这里已经有了答案: How do I write a correct micro-benchmark in Java? (11 个答案) 关闭 9 年前。今天我做了一个简单的测试来比较
Python 实现比 C 快
如果比较不应该以这种方式进行，我深表歉意。我是编程新手，只是很好奇为什么会这样。我有一个包含词嵌入的大型二进制文件 (4.5gb)。每行都有一个单词，后面跟着它的嵌入，它由 300 个浮点值组成。我
c++ - 为什么我的直接四元数乘法比 SSE 快？
我经历了几个不同的四元数乘法实现，但我很惊讶地发现引用实现是迄今为止我最快的实现。这是有问题的实现: inline static quat multiply(const quat& lhs, cons
c++ - 为什么虚函数调用比 dynamic_cast 快？
我写了一个简单的例子，估计调用虚函数的平均时间，使用基类接口(interface)和dynamic_cast和调用非虚函数。这是它: #include #include #include #in
c++ - 比 Stackwalk 快
有没有人知道比“StackWalk”更好/更快的获取调用堆栈的方法？我还认为 stackwalk 在有很多变量的方法上也会变慢......(我想知道商业分析员是做什么的？)我在 Windows 上使用

IT王子

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

optimization - Sqlite subselect 比 distinct + order by 快得多