使用Python在SQLite中优化嵌套查询

我在用Python写一个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'

我不确定是否可以组合一些查询，或从其他角度提取数据。我还考虑过在数据库中构建一个包含计数的辅助表，并在原始表更新时更新它。我已经盯着这个问题一整天了，所以明天我会重新开始，希望从专家那里得到一些见解;)

解决方案

1. 使用GROUP BY子句

首先，你可以使用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子句的存在。这应该可以减少查询的数量，我认为这是提高性能的第一步。

2. 创建索引

其次，根据搜索列创建索引，在本例中是sender、day和hour。

CREATE INDEX messages_sender_by_day ON messages (sender, day);

（你可能不需要在其中包括“hour”。）