Início / dba / Perguntas / 150858
Accepted
chiastic-security
Asked: 2016-09-29 02:55:44 +0800 CST

Por que essa consulta sqlite é muito mais lenta quando eu indexo as colunas?

  • 772

Eu tenho um banco de dados sqlite com duas tabelas, cada uma com 50.000 linhas, contendo nomes de pessoas (falsas). Eu construí uma consulta simples para descobrir quantos nomes existem (nome próprio, inicial do meio, sobrenome) que são comuns a ambas as tabelas:

select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;

Quando não há índices, exceto nas chaves primárias (irrelevantes para esta consulta), ele é executado rapidamente:

[james@marlon Downloads] $ time sqlite3 generic_data_no_indexes.sqlite "select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;"
131

real    0m0.115s
user    0m0.111s
sys     0m0.004s

Mas se eu adicionar índices às três colunas em cada tabela (seis índices no total):

CREATE INDEX `idx_uk_givenname` ON `fakenames_uk` (`givenname` )
//etc.

então corre dolorosamente devagar:

[james@marlon Downloads] $ time sqlite3 generic_data.sqlite "select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;"
131

real    1m43.102s
user    0m52.397s
sys     0m50.696s

Existe alguma rima ou razão para isso?

Aqui está o resultado EXPLAIN QUERY PLANpara a versão sem índices:

0|0|0|SCAN TABLE fakenames_uk
0|1|1|SEARCH TABLE fakenames_usa USING AUTOMATIC COVERING INDEX (middleinitial=? AND surname=? AND givenname=?)

Isso é com índices:

0|0|0|SCAN TABLE fakenames_uk
0|1|1|SEARCH TABLE fakenames_usa USING INDEX idx_us_middleinitial (middleinitial=?)
performance index

1 respostas

  1. Best Answer

    No SQLite, as junções são executadas como junções de loop aninhadas, ou seja, o banco de dados passa por uma tabela e, para cada linha, procura linhas correspondentes na outra tabela.

    Se houver um índice, o banco de dados pode pesquisar qualquer correspondência no índice rapidamente e, em seguida, ir para a linha da tabela correspondente para obter os valores de quaisquer outras colunas necessárias.

    Neste caso, existem três índices possíveis. Sem nenhuma informação estatística (que seria criada executando ANALYZE ), o banco de dados escolhe o menor, para reduzir o I/O. No entanto, o middleinitialíndice é inútil porque não reduz muito o número de linhas da tabela que precisam ser buscadas; e a etapa adicional pelo índice realmente aumenta a E/S necessária porque as linhas da tabela não são mais lidas em ordem, mas aleatoriamente.

    Se não houver índice, a pesquisa de linhas correspondentes exigirá uma verificação completa da segunda tabela para cada linha da primeira tabela. Isso seria tão ruim que o banco de dados estima que vale a pena criar e descartar um índice temporário apenas para essa consulta. Este índice temporário ("AUTOMÁTICO") é criado em todas as colunas utilizadas para a pesquisa. A operação COUNT(*) não precisa de valores de nenhuma outra coluna, então esse índice passa a ser um índice de cobertura , o que significa que não é necessário realmente procurar a linha da tabela correspondente a uma entrada de índice, o que economiza ainda mais I /O.

    Para agilizar esta consulta, crie este índice permanentemente, para que não seja mais necessário construir um temporário:

    CREATE INDEX uk_all_names ON fakenames_uk(surname, givenname, middleinitial);

EXPLAIN QUERY PLAN
SELECT count(*)
FROM fakenames_uk
JOIN fakenames_usa USING (givenname, middleinitial, surname);

0|0|1|SCAN TABLE fakenames_usa
0|1|0|SEARCH TABLE fakenames_uk USING COVERING INDEX uk_all_names (surname=? AND givenname=? AND middleinitial=?)

    O índice surnamenão é mais necessário porque o índice de três colunas pode ser usado para qualquer pesquisa nesta coluna.
    O índice givennamepode ser útil se você fizer pesquisas apenas nesta coluna.
    O índice on middleinitialé sempre inútil: uma consulta que procura um dos 26 valores possíveis é mais rápida se apenas varrer a tabela inteira.

    • 22

relate perguntas