Skip to main content
 首页 » 编程设计

python之为什么 SQLite 中的多列索引会降低查询的性能,除非索引所有列

2024年11月24日11cloudgamer

我正在尝试通过使用索引来优化对 SQLite 数据库的简单查询的性能。例如,该表有 5M 行,5 列; SELECT 语句用于选取所有列,而 WHERE 语句仅检查 2 列。但是,除非我拥有多列索引中的所有列,否则查询的性能比没有任何索引更差。

我是否错误地为该列建立了索引,或者在选择所有列时,我是否应该将所有列都包含在索引中以提高性能?

每个案例下面的# 是我在硬盘中创建SQLite 数据库时得到的结果。然而,出于某种原因,使用 ':memory:' 模式使所有索引情况比没有索引时更快。

import sqlite3 
import datetime 
import pandas as pd 
import numpy as np 
import os 
import time 
 
# Simulate the data 
size = 5000000 
apps = [f'{i:010}' for i in range(size)] 
dates = np.random.choice(pd.date_range('2016-01-01', '2019-01-01').to_pydatetime().tolist(), size) 
prod_cd = np.random.choice([f'PROD_{i}' for i in range(30)], size) 
models = np.random.choice([f'MODEL{i}' for i in range(15)], size) 
categories = np.random.choice([f'GROUP{i}' for i in range(10)], size) 
 
# create a db in memory 
conn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES) 
c = conn.cursor() 
# Create table and insert data 
c.execute("DROP TABLE IF EXISTS experiment") 
c.execute("CREATE TABLE experiment (appId TEXT, dtenter TIMESTAMP, prod_cd TEXT, model TEXT, category TEXT)") 
c.executemany("INSERT INTO experiment VALUES (?, ?, ?, ?, ?)", zip(apps, dates, prod_cd, models, categories)) 
 
# helper functions 
def time_it(func): 
    def wrapper(*args, **kwargs): 
        start = time.time() 
        result = func(*args, **kwargs) 
        print("time for {} function is {}".format(func.__name__, time.time() - start)) 
        return result 
    return wrapper 
 
@time_it 
def read_db(query): 
    df = pd.read_sql_query(query, conn) 
    return df 
 
@time_it 
def run_query(query): 
    output = c.execute(query).fetchall() 
    print(output) 
 
# The main query 
query = "SELECT * FROM experiment WHERE prod_cd IN ('PROD_1', 'PROD_5', 'PROD_10') AND dtenter >= '2018-01-01'" 
 
# CASE #1: WITHOUT ANY INDEX 
run_query("EXPLAIN QUERY PLAN " + query) 
df = read_db(query) 
>>> time for read_db function is 2.4783718585968018 
 
# CASE #2: WITH INDEX FOR COLUMNS IN WHERE STATEMENT 
run_query("DROP INDEX IF EXISTs idx") 
run_query("CREATE INDEX idx ON experiment(prod_cd, dtenter)") 
run_query("EXPLAIN QUERY PLAN " + query) 
df = read_db(query) 
>>> time for read_db function is 3.221407890319824 
# CASE #3: WITH INDEX FOR MORE THEN WHAT IN WHERE STATEMENT, BUT NOT ALL COLUMNS  
run_query("DROP INDEX IF EXISTs idx") 
run_query("CREATE INDEX idx ON experiment(prod_cd, dtenter, appId, category)") 
run_query("EXPLAIN QUERY PLAN " + query) 
df = read_db(query) 
>>>time for read_db function is 3.176532745361328 
 
# CASE #4: WITH INDEX FOR ALL COLUMNS  
run_query("DROP INDEX IF EXISTs idx") 
run_query("CREATE INDEX idx ON experiment(prod_cd, dtenter, appId, category, model)") 
run_query("EXPLAIN QUERY PLAN " + query) 
df = read_db(query) 
>>> time for read_db function is 0.8257918357849121 

请您参考如下方法:

The SQLite Query Optimizer Overview说:

When doing an indexed lookup of a row, the usual procedure is to do a binary search on the index to find the index entry, then extract the rowid from the index and use that rowid to do a binary search on the original table. Thus a typical indexed lookup involves two binary searches.

索引条目的顺序与表条目的顺序不同,因此如果查询从表的大部分页面返回数据,那么所有这些随机访问查找都比仅扫描所有表行要慢。

只有当您的 WHERE 条件过滤掉比返回的行多得多的行时,索引查找才比表扫描更有效。

SQLite 假定对索引列的查找具有高选择性。您可以通过运行 ANALYZE 获得更好的估计填写表格后。
但是,如果您所有的查询都采用索引无济于事的形式,那么最好根本不使用索引。


当您为查询中使用的所有列创建索引时,不再需要额外的表访问:

If, however, all columns that were to be fetched from the table are already available in the index itself, SQLite will use the values contained in the index and will never look up the original table row. This saves one binary search for each row and can make many queries run twice as fast.

When an index contains all of the data needed for a query and when the original table never needs to be consulted, we call that index a "covering index".