时间戳

首页 > 解决方案 > 为什么这个索引会使 SELECT GROUP BY 查询变慢？

sql - 为什么这个索引会使 SELECT GROUP BY 查询变慢？

问题描述

假设我们有一个包含 10 万笔交易的表（每一行都是product按customer时间戳购买的dt）。

我注意到查询

SELECT product, COUNT(customer) FROM transactions GROUP BY product

当没有索引时，速度出奇地快：

数据库大小 SELECT查询时间
无索引 1.8 MB 63 毫秒
索引product 3 MB 220 毫秒
索引product+ 索引customer 4.1 MB 292 毫秒

在这种情况下，为什么索引会使查询变慢？（而且数据库更大，所以这是一个很大的不！）

可重现的代码：

import sqlite3, time, random, string
db = sqlite3.connect('test.db')
db.executescript("""CREATE TABLE transactions(id INTEGER PRIMARY KEY, dt INTEGER, product TEXT, customer TEXT);
                    CREATE INDEX product_idx ON transactions(product);
                    CREATE INDEX customer_idx ON transactions(customer);""")
for i in range(100*1000):
    t = random.randint(1600000000, 1600010000)                       # random datetime
    product = ''.join(random.choices(string.ascii_uppercase, k=2))   # random product among 676 products
    customer = ''.join(random.choices(string.ascii_uppercase, k=2))  # random customer among 676 customers
    db.execute("INSERT INTO transactions(dt, product, customer) VALUES (?, ?, ?)", (t, product, customer))

t0 = time.time()
for _ in db.execute("SELECT product, COUNT(customer) FROM transactions GROUP BY product"):
    pass
print (time.time()-t0)
db.commit()

标签： sqlsqliteindexinggroup-by

解决方案

Sqlite 在查询中为每个表使用一个索引。在像您这样有多个可能索引的情况下，它会猜测使用哪个索引。您可以使用EXPLAIN QUERY PLAN来查看选择了哪个，PRAGMA optimize或者ANALYZE在填充的表上生成统计信息，从而为它提供更好的信息来进行猜测。它还可以决定不使用任何现有索引，并且可能使用一个AUTOMATIC索引，该索引是为查询构建的临时索引，然后在完成返回行时删除（这自然比使用现有索引需要更多时间，因此只会发生当 sqlite 认为它仍然会更快时）。

有一张空桌子：

sqlite> EXPLAIN QUERY PLAN SELECT product, COUNT(customer) FROM transactions GROUP BY product;
QUERY PLAN
`--SCAN TABLE transactions USING INDEX product_idx
sqlite> DROP INDEX product_idx;
sqlite> EXPLAIN QUERY PLAN SELECT product, COUNT(customer) FROM transactions GROUP BY product;
QUERY PLAN
|--SCAN TABLE transactions
`--USE TEMP B-TREE FOR GROUP BY

在这种情况下，由于您对product列进行分组，因此使用该索引。但是它仍然必须读取每组的每一行才能得到 的计数customer，从而导致大量的磁盘查找。没有任何索引，它将顺序读取表，使用临时数据结构来构建结果。这最终会更快（磁盘读取速度很慢）。

您可以在查询计划文档中阅读有关 sqlite 如何使用索引的更多信息。

正如您所发现的，这里最好的方法是使用多列覆盖索引，该索引将所有需要的信息存储在索引本身中，因此不必查询正确的表：

CREATE INDEX product_customer_idx ON transactions(product, customer);

推荐阅读