Otimize consultas SQL: Crie índices de banco de dados e acelere suas buscas!

últimas postagens

Deseja otimizar a velocidade das suas consultas ao banco de dados? Descubra como criar um índice de banco de dados usando SQL para impulsionar o desempenho das suas consultas e agilizar a recuperação de dados.

Ao realizar a busca por informações em uma tabela de banco de dados, frequentemente você necessitará aplicar filtros com base em colunas específicas.

Imagine que você elabore uma consulta SQL para obter dados com base em critérios definidos. Por padrão, a execução da consulta realiza uma varredura completa da tabela até identificar todos os registros que satisfazem a condição, retornando em seguida os resultados.

Tal procedimento pode ser extremamente ineficiente quando se trata de consultar uma tabela de banco de dados extensa, com milhões de registros. A solução para acelerar essas consultas é criar um índice de banco de dados.

O que é um Índice de Banco de Dados?

Quando você busca um termo específico em um livro, você percorre todas as páginas, uma a uma, até encontrar o que procura? Certamente que não.

Ao invés disso, você consulta o índice para identificar as páginas que mencionam o termo desejado, indo diretamente a elas. Um índice em um banco de dados funciona de forma semelhante aos índices de um livro.

Um índice de banco de dados consiste em um conjunto de referências ou ponteiros para os dados propriamente ditos, organizados de maneira a otimizar a velocidade de recuperação. Internamente, um índice de banco de dados pode ser implementado utilizando estruturas de dados como árvores B+ e tabelas hash. Assim, um índice de banco de dados aprimora a velocidade e a eficiência das operações de recuperação de dados.

Como Criar um Índice de Banco de Dados em SQL

Agora que entendemos o conceito de um índice de banco de dados e seu papel em acelerar a recuperação de dados, vamos aprender a criar um índice de banco de dados em SQL.

Ao executar operações de filtragem — definindo a condição de recuperação através de uma cláusula WHERE — é comum que você queira consultar uma determinada coluna com maior frequência em relação a outras.

CREATE INDEX nome_do_indice ON tabela (coluna)

Onde:

`nome_do_indice` é o nome do índice a ser criado.
`tabela` se refere à tabela no banco de dados relacional.
`coluna` representa o nome da coluna da tabela na qual o índice deve ser criado.

Também é possível criar índices em várias colunas — um índice de múltiplas colunas — de acordo com suas necessidades. Veja a sintaxe para realizar essa ação:

CREATE INDEX nome_do_indice ON tabela (coluna_1, coluna_2,...,coluna_k)

Agora, vamos examinar um exemplo prático.

Entendendo os Benefícios de Desempenho de um Índice de Banco de Dados

Para compreender a vantagem de criar um índice, é necessário construir uma tabela de banco de dados com um grande número de registros. Os exemplos de código a seguir são para o SQLite, mas você também pode utilizar outros SGBDs de sua preferência, como PostgreSQL e MySQL.

Preenchendo uma Tabela de Banco de Dados com Registros

Você pode usar o módulo `random` do Python para gerar e inserir registros no banco de dados. No entanto, vamos utilizar o pacote Faker para preencher a tabela do banco de dados com um milhão de linhas.

O script Python abaixo:

Cria e se conecta ao banco de dados `customer_db`.
Cria uma tabela `customers` com os campos: `first_name`, `last_name`, `city` e `num_orders`.
Gera dados sintéticos e insere os dados — um milhão de registros — na tabela `customers`.

O código também está disponível no GitHub.

# main.py
# imports
import sqlite3
from faker import Faker
import random

# connect to the db
db_conn = sqlite3.connect('customer_db.db')
db_cursor = db_conn.cursor()

# create table
db_cursor.execute('''CREATE TABLE customers (
                  id INTEGER PRIMARY KEY,
                  first_name TEXT,
                  last_name TEXT,
                  city TEXT,
                  num_orders INTEGER)''')

# create a Faker object
fake = Faker()
Faker.seed(27)

# create and insert 1 million records
num_records = 1_000_000

for _ in range(num_records):
    first_name = fake.first_name()
    last_name = fake.last_name()
    city = fake.city()
    num_orders = random.randint(0,100)
    db_cursor.execute('INSERT INTO customers (first_name, last_name, city, num_orders) VALUES (?,?,?,?)', (first_name, last_name, city, num_orders))

# commit the transaction and close the cursor and connection
db_conn.commit()
db_cursor.close()
db_conn.close()

Agora, podemos dar início às consultas.

Criando um Índice na Coluna Cidade

Suponha que você precise obter informações do cliente filtrando pela coluna da cidade. Sua consulta SELECT seria:

SELECT coluna(s) FROM clientes
WHERE condição;

Vamos criar o índice `city_idx` na coluna `city` da tabela `customers`:

CREATE INDEX city_idx ON customers (city);

⚠ A criação de um índice leva um tempo considerável e é uma ação única. No entanto, os benefícios de desempenho serão significativos quando houver um grande número de consultas que filtrem pela coluna da cidade.

Excluindo um Índice de Banco de Dados

Para eliminar um índice, utilize o comando DROP INDEX da seguinte maneira:

DROP INDEX nome_do_indice;

Comparando os Tempos de Consulta com e sem Índice

Se você pretende executar consultas em um script Python, use o cronômetro padrão para obter os tempos de execução das consultas.

Em alternativa, você pode executar as consultas utilizando o cliente de linha de comando sqlite3. Para interagir com `customer_db.db` por meio do cliente de linha de comando, execute o seguinte comando no terminal:

$ sqlite3 customer_db.db;

Para obter os tempos de execução aproximados, você pode usar o recurso `.timer` integrado ao sqlite3, como demonstrado:

sqlite3 > .timer on
        > <consulta aqui>

Dado que criamos um índice na coluna da cidade, as consultas que envolvem filtragem pela coluna da cidade na cláusula WHERE serão muito mais rápidas.

Primeiro, execute as consultas. Em seguida, crie o índice e execute as consultas novamente. Anote os tempos de execução em cada caso. Aqui estão alguns exemplos:

Consulta	Tempo sem Índice	Tempo com Índice
SELECT * FROM clientes WHERE cidade LIKE ‘Novo%’ LIMIT 10;	0,100 s	0,001 s
SELECT * FROM clientes WHERE cidade=’Novo Wesley’;	0.148 s	0.001 s
SELECT * FROM clientes WHERE cidade IN (‘Novo Wesley’, ‘Novo Steven’, ‘Nova Carmenmouth’);	0,247 s	0,003 s

É evidente que os tempos de recuperação com índice são ordens de grandeza mais rápidos do que aqueles sem o índice na coluna da cidade.

Melhores Práticas para Criar e Utilizar Índices de Banco de Dados

É sempre importante avaliar se os ganhos de desempenho superam a sobrecarga de criação de um índice de banco de dados. Considere as seguintes práticas recomendadas:

Escolha as colunas corretas para indexar. Evite criar muitos índices devido à sobrecarga que isso gera.
Sempre que uma coluna indexada é modificada, o índice correspondente também deve ser atualizado. Por isso, criar um índice de banco de dados (embora acelere a recuperação) diminui significativamente as operações de inserção e atualização. Consequentemente, crie índices em colunas que são consultadas com frequência, mas raramente atualizadas.

Quando Não Criar um Índice?

Agora você já deve ter uma ideia de quando e como criar um índice. No entanto, é importante ressaltar quando um índice de banco de dados pode não ser necessário:

Quando a tabela do banco de dados é pequena e não contém um número muito grande de linhas, a varredura completa da tabela para buscar informações não é tão dispendiosa.
Não crie índices em colunas que raramente são usadas para recuperação. Quando você cria índices em colunas que não são consultadas com frequência, o custo de criação e manutenção do índice acaba sendo maior que os ganhos de desempenho.

Resumindo

Vamos revisar o que aprendemos:

Ao consultar um banco de dados para obter informações, é comum que seja preciso filtrar com base em colunas específicas com maior frequência. Um índice de banco de dados nessas colunas que são frequentemente consultadas pode otimizar o desempenho.
Para criar um índice em uma única coluna, use a seguinte sintaxe: `CREATE INDEX nome_do_indice ON tabela (coluna)`. Se você deseja criar um índice em múltiplas colunas, utilize: `CREATE INDEX nome_do_indice ON tabela (coluna_1, coluna_2,…,coluna_k)`.
Sempre que uma coluna indexada for modificada, o índice correspondente deve ser atualizado. Portanto, selecione as colunas de forma adequada – aquelas que são consultadas com frequência e atualizadas com menos frequência – para a criação de um índice.
Se a tabela do banco de dados for relativamente pequena, o custo de criar, manter e atualizar um índice será maior do que os ganhos de desempenho.

Na maioria dos sistemas modernos de gerenciamento de banco de dados, há um otimizador de consultas que verifica se um índice em uma coluna específica fará com que a consulta seja executada mais rapidamente. Na sequência, vamos abordar as melhores práticas para design de banco de dados.