python - 如何优化 SQLite 中的多个嵌套 SELECT(使用 Python)？

Question

我正在构建一个 CGI 脚本来轮询 SQLite 数据库并构建一个统计表。源数据库表如下所述，相关代码块也是如此。一切正常(功能上)，但 CGI 本身非常慢，因为我有多个嵌套的 SELECT COUNT(id) 调用。我认为我最好的优化方法是询问 SO 社区，因为我在 Google 的时间相对没有什么成果。

表格:

CREATE TABLE messages (
    id TEXT PRIMARY KEY ON CONFLICT REPLACE,
    date TEXT,
    hour INTEGER,
    sender TEXT,
    size INTEGER,
    origin TEXT,
    destination TEXT,
    relay TEXT,
    day TEXT);

(是的，我知道该表未规范化，但它填充了邮件日志中的摘录...我很高兴能够提取和填充工作，更不用说规范化了。我不认为该表结构在这一点上与我的问题有很大关系，但我可能是错的。)

示例行:

476793200A7|Jan 29 06:04:47|6|admin@mydomain.com|4656|web02.mydomain.pvt|user@example.com|mail01.mydomain.pvt|Jan 29

还有，构建我的表的 Python 代码:

#!/usr/bin/python
print 'Content-type: text/html\n\n'

from datetime import date

import re
p = re.compile('(\w+) (\d+)')

d_month = {'Jan':1,'Feb':2,'Mar':3,'Apr':4,'May':5,'Jun':6,'Jul':7,'Aug':8,'Sep':9,'Oct':10,'Nov':11,'Dec':12}
l_wkday = ['Mo','Tu','We','Th','Fr','Sa','Su']

days = []
curs.execute('SELECT DISTINCT(day) FROM messages ORDER BY day')
for day in curs.fetchall():
    m = p.match(day[0]).group(1)
    m = d_month[m]
    d = p.match(day[0]).group(2)
    days.append([day[0],"%s (%s)" % (day[0],l_wkday[date.weekday(date(2010,int(m),int(d)))])])

curs.execute('SELECT DISTINCT(sender) FROM messages')
senders = curs.fetchall()
for sender in senders:
    curs.execute('SELECT COUNT(id) FROM messages WHERE sender=%s',(sender[0]))
    print '  <div id="'+sender[0]+'">'
    print '   <h1>Stats for Sender: '+sender[0]+'</h1>'
    print '   <table><caption>Total messages in database: %d</caption>' % curs.fetchone()[0]
    print '    <tr><td>&nbsp;</td><th colspan=24>Hour of Day</th></tr>'
    print '    <tr><td class="left">Day</td><th>%s</th></tr>' % '</th><th>'.join(map(str,range(24)))
    for day in days:
            print '    <tr><td>%s</td>' % day[1]
            for hour in range(24):
                    sql = 'SELECT COUNT(id) FROM messages WHERE sender="%s" AND day="%s" AND hour="%s"' % (sender[0],day[0],str(hour))
                    curs.execute(sql)
                    d = curs.fetchone()[0]
                    print '    <td>%s</td>' % (d>0 and str(d) or '')
            print '    </tr>'
    print '   </table></div>'

print ' </body>\n</html>\n'

我不确定是否有任何方法可以组合一些查询，或者从不同的角度处理它以提取数据。我还考虑过构建第二张表，其中包含计数，并在更新原始表时更新它。我今天一直盯着这个看太久了，所以明天我要重新开始攻击它，希望能从专家那里得到一些见解；)

---

编辑:使用下面提供的 GROUP BY 答案，我能够在一个查询中从数据库中获取所需的数据。我转而使用 Perl，因为 Python 的嵌套 dict 支持对于我处理此问题所需的方式(以特定方式构建一组 HTML 表)来说效果不是很好。下面是修改后的代码片段:

my %data;
my $rows = $db->selectall_arrayref("SELECT COUNT(id),sender,day,hour FROM messages GROUP BY sender,day,hour ORDER BY sender,day,hour");
for my $row (@$rows) {
    my ($ct, $se, $dy, $hr) = @$row;
    $data{$se}{$dy}{$hr} = $ct;
}
for my $se (keys %data) {
    print "Sender: $se\n";
    for my $dy (keys %{$data{$se}}) {
    print "Day: ",time2str('%a',str2time("$dy 2010"))," $dy\n";
        for my $hr (keys %{$data{$se}{$dy}}) {
            print "Hour: $hr = ".$data{$se}{$dy}{$hr}."\n";
        }
    }
    print "\n";
}

曾经需要 28.024 秒执行的任务现在需要 0.415 秒!

Answer 1

首先你可以使用group by子句:

select count(*), sender from messages group by sender;

这样您就可以为所有发件人执行一个查询，而不是为每个发件人执行一次查询。另一种可能性是:

select count(*), sender, day, hour
    from messages group by sender, day, hour
    order by sender, day, hour;

我没有测试它，但至少现在你知道 group by 子句的存在了。这应该会减少查询的数量，我认为这是提高性能的第一步。

其次，根据搜索列创建索引，在您的案例中是发件人、日期和时间。

如果这还不够，请使用分析工具找出花费时间最多的地方。您还应该考虑使用 fetchmany 而不是 fetchall 来保持低内存消耗。请记住，由于 sqlite 模块是用 C 编码的，因此请尽可能多地使用它。