[Explained] Como criar um índice de banco de dados em SQL

Twittar

Quer acelerar as consultas ao banco de dados? Aprenda a criar um índice de banco de dados usando SQL e otimizar o desempenho da consulta — e acelerar a recuperação de dados.

Ao recuperar dados de uma tabela de banco de dados, você terá que filtrar com base em colunas específicas com mais frequência.

Suponha que você escreva uma consulta SQL para recuperar os dados com base em condições específicas. Por padrão, a execução da consulta executa uma verificação completa da tabela até que todos os registros que satisfaçam a condição sejam encontrados e, em seguida, retorna os resultados.

Isso pode ser extremamente ineficiente quando você precisa consultar uma grande tabela de banco de dados com vários milhões de linhas. Você pode acelerar essas consultas criando um índice de banco de dados.

últimas postagens

O que é um índice de banco de dados?

Quando quiser encontrar um termo específico em um livro, você fará uma varredura completa do livro – uma página após a outra – procurando o termo específico? Bem, você não.

Em vez disso, você procurará o índice para descobrir quais páginas fazem referência ao termo e pulará direto para essas páginas. Um índice em um banco de dados funciona muito como os índices em um livro.

Um índice de banco de dados é um conjunto de ponteiros ou referências aos dados reais, mas classificados de forma a tornar a recuperação de dados mais rápida. Internamente, um índice de banco de dados pode ser implementado usando estruturas de dados como árvores B+ e tabelas hash. Portanto, um índice de banco de dados melhora a velocidade e a eficiência das operações de recuperação de dados.

Como alterar o URL de administração do WordPress para evitar ataques de força bruta?

Criando um índice de banco de dados em SQL

Agora que sabemos o que é um índice de banco de dados e como ele pode acelerar a recuperação de dados, vamos aprender como criar um índice de banco de dados em SQL.

Ao executar operações de filtragem — especificando a condição de recuperação usando uma cláusula WHERE — talvez você queira consultar uma determinada coluna com mais frequência do que outras.

CREATE INDEX index_name ON table (column)

Aqui,

index_name é o nome do índice a ser criado
tabela refere-se à tabela no banco de dados relacional
coluna refere-se ao nome da coluna na tabela do banco de dados na qual precisamos criar o índice.

Você também pode criar índices em várias colunas — um índice de várias colunas — dependendo dos requisitos. Aqui está a sintaxe para fazer isso:

CREATE INDEX index_name ON table (column_1, column_2,...,column_k)

Agora vamos passar para um exemplo prático.

Compreendendo os ganhos de desempenho do índice de banco de dados

Para entender a vantagem de criar um índice, precisamos criar uma tabela de banco de dados com um grande número de registros. Os exemplos de código são para SQLite. Mas você também pode usar outros RDBMS de sua preferência, como PostgreSQL e MySQL.

Preenchendo uma tabela de banco de dados com registros

Você também pode usar o módulo aleatório interno do Python para criar e inserir registros no banco de dados. No entanto, vamos usar falsificador para preencher a tabela do banco de dados com um milhão de linhas.

O seguinte script Python:

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

Você também pode encontrar o código 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 começar a consultar.

Como encontrar sites antigos que não existem mais

Criando um índice na coluna Cidade

Suponha que você queira obter as informações do cliente filtrando com base na coluna da cidade. Sua consulta SELECT ficará assim:

SELECT column(s) FROM customers
WHERE condition;

Então vamos criar o city_idx na coluna city na tabela de clientes:

CREATE INDEX city_idx ON customers (city);

⚠ A criação de um índice leva um tempo considerável e é uma operação única. Mas os benefícios de desempenho quando você precisa de um grande número de consultas — filtrando na coluna da cidade — serão significativos.

Excluindo um índice de banco de dados

Para excluir um índice, você pode usar a instrução DROP INDEX da seguinte forma:

DROP INDEX index_name;

Comparando tempos de consulta com e sem índice

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

Como alternativa, você pode executar as consultas usando o cliente de linha de comando sqlite3. Para trabalhar com customer_db.db usando o 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 a funcionalidade .timer incorporada ao sqlite3 da seguinte forma:

sqlite3 > .timer on
        > <query here>

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

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

QueryTime sem IndexTime com IndexSELECT * FROM clientes
ONDE cidade LIKE ‘Novo%’
LIMITE 10;0,100 s0,001 sSELECT *FROM clientes
WHERE cidade=’Novo Wesley’;0.148 s0.001 sSELECT * FROM clientes
ONDE cidade IN (‘Novo Wesley’, ‘Novo Steven’, ‘Nova Carmenmouth’);0,247 s0,003 s

Como vincular sua conta Riot e Xbox Game Pass

Vemos que os tempos de recuperação com índice são vários pedidos mais rápidos do que aqueles sem índice na coluna da cidade.

Práticas recomendadas para criar e usar índices de banco de dados

Você deve sempre verificar se os ganhos de desempenho são maiores do que a sobrecarga de criar um índice de banco de dados. Aqui estão algumas práticas recomendadas a serem lembradas:

Escolha as colunas certas para criar um índice. Evite criar muitos índices por causa da sobrecarga substancial.
Sempre que uma coluna indexada é atualizada, o índice correspondente também deve ser atualizado. Portanto, criar um índice de banco de dados (embora acelere a recuperação) diminui significativamente as inserções e as operações de atualização. Portanto, você deve criar índices em colunas que são consultadas com frequência, mas raramente atualizadas.

Quando você não deve criar um índice?

Até agora você deve ter uma ideia de quando e como criar um índice. Mas vamos também indicar quando o índice do banco de dados pode não ser necessário:

Quando a tabela do banco de dados é pequena e não contém um grande número de linhas, a varredura completa da tabela para recuperar dados não é tão cara.
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 criar e manter um índice supera os ganhos de desempenho.

Resumindo

Vamos revisar o que aprendemos:

Ao consultar um banco de dados para recuperar dados, pode ser necessário filtrar com base em determinadas colunas com mais frequência. Um índice de banco de dados nessas colunas consultadas com frequência pode melhorar o desempenho.
Para criar um índice em uma única coluna, use a sintaxe: CREATE INDEX nome_índice ON tabela (coluna). Se você deseja criar um índice de múltiplas colunas, use: CREATE INDEX index_name ON table (column_1, column_2,…,column_k)
Sempre que uma coluna indexada for modificada, o índice correspondente também deverá ser atualizado. Portanto, escolha as colunas certas – consultadas com frequência e atualizadas com muito menos frequência – para criar um índice.
Se a tabela do banco de dados for relativamente menor, o custo de criação, manutenção e atualização de um índice será maior do que os ganhos de desempenho.

Na maioria dos sistemas de gerenciamento de banco de dados modernos, há um otimizador de consulta que verifica se um índice em uma coluna específica fará com que a consulta seja executada mais rapidamente. A seguir, vamos aprender as práticas recomendadas para design de banco de dados.