对于一个研究项目，我创建了一个用于存储新闻文章的sqlite数据库。目前数据库有272GB大，存储在2TB的云卷上。我的云计算机有32核和128GB内存，并连接到这个卷。
我正在运行以下查询:"select * from articles where year={} and source in {}"其中，我将"{}"替换为年份和大约6个来源。
运行这个查询大约需要1小时，结果数据库产生了大约45万行(总共9000万行)。在执行此操作时，CPU使用率实际上为0%。
表是这样创建的:"create table if not exists articles(source_id TEXT, source TEXT, day INTEGER, month INTEGER, year INTEGER, program_name TEXT, transcript TEXT, parliament INTEGER, top1_topic INTEGER, top1_acc REAL, top2_topic INTEGER, top2_acc REAL, top3_topic INTEGER, top3_acc REAL, emotionality_nrc REAL, emotionality_liwc REAL, subject_codes TEXT, PRIMARY KEY (source_id, day, month, year, program_name));"我已经分别索引了出处和年份。
查询说明为:QUERY PLAN`--SEARCH articles USING INDEX idx_articles_on_year_source (year=? AND source=?)
我在存储数据库的目录下运行了一个iping测试，得到:
--- . (ext4 /dev/vdb) ioping statistics ---
99 requests completed in 31.1 ms, 396 KiB read, 3.18 k iops, 12.4 MiB/s
generated 100 requests in 1.65 min, 400 KiB, 1 iops, 4.04 KiB/s
min/avg/max/mdev = 157.4 us / 314.5 us / 477.6 us / 76.8 us
和以下fio测试fio --randrepeat=1 --ioengine=libaio --direct=1 --gtod_reduce=1 --name=fiotest --filename=testfio --bs=4k --iodepth=64 --size=8G --readwrite=randrw --rwmixread=75给出了这样的结果:
read: IOPS=10.8k, BW=42.3MiB/s (44.4MB/s)
write: IOPS=3619, BW=14.1MiB/s (14.8MB/s)
我还尝试了"PRAGMA synchronous=OFF"和不同的日志，如memory和WAL。
我有点迷失了为什么数据库这么慢，我应该做些什么来提高速度。是我在设置上犯了一个愚蠢的错误，还是基础设施不好?我应该切换到像amazon redshift这样的数据仓库解决方案吗?
PS:我正在通过python的sqlite3库连接到db，并使用以下代码
def select_articles_by_year_and_sources(self, year, sources=None):
cur = self.conn.cursor()
rows = cur.execute(select_articles_by_year_and_sources_query.format(year, sources))
return iter(ResultIterator(rows))
conn = db.NewsDb(path_db) # connect to database
articles = list(conn.select_articles_by_year_and_sources(year, sources))
conn.close()
我刚试着从附加卷复制一个8GB的文件到我的VM。使用bash cp命令耗时2秒30秒。我想这意味着连接卷的带宽很慢吧?
您的查询计划显示WHERE子句中两个列(year和source)上的索引正在被使用，因此您可能无法加速它。但是，根据数据的分布，有可能在articles(year, source)上创建索引，而不是在articles(source, year)上创建索引，通过更快地修剪更多行可能会更好。
您可以尝试添加新索引，然后在数据库上运行ANALYZE以生成关于索引的统计信息，SQLite使用这些索引来选择它认为哪个可能的索引更好。检查之后的EXPLAIN QUERY PLAN输出，看看它是在使用新索引还是仍然在旧索引上，然后删除没有使用的索引(或者如果在实际使用新索引时速度较慢，则删除该索引)。
另一个选项是使用sqlite3命令行程序的.expert命令，它为查询生成索引建议，看看在这种情况下它会产生什么。