Otimização de Índices SQLite

Introdução

Neste laboratório, você aprenderá como otimizar o desempenho de um banco de dados SQLite usando índices. Você criará índices de coluna única para melhorar a velocidade das consultas, com foco na aplicação prática e análise. Você também aprenderá a analisar planos de consulta e remover índices redundantes.

Criar um Banco de Dados e Tabela

Nesta etapa, você criará um banco de dados SQLite e uma tabela employees. Em seguida, você inserirá alguns dados de exemplo na tabela.

Primeiro, abra seu terminal na VM do LabEx. Seu caminho padrão é /home/labex/project.

Para criar um banco de dados SQLite chamado my_database.db, execute o seguinte comando:

sqlite3 my_database.db

Este comando cria um novo arquivo de banco de dados SQLite chamado my_database.db em seu diretório de projeto e abre o shell SQLite.

Em seguida, crie a tabela employees com a seguinte estrutura:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    department TEXT
);

Esta instrução SQL cria uma tabela chamada employees com cinco colunas: id, first_name, last_name, email e department. A coluna id é definida como a chave primária (primary key), o que significa que ela deve conter valores únicos.

Agora, insira alguns dados de exemplo na tabela employees:

INSERT INTO employees (first_name, last_name, email, department) VALUES
('John', 'Doe', 'john.doe@example.com', 'Sales'),
('Jane', 'Smith', 'jane.smith@example.com', 'Marketing'),
('Robert', 'Jones', 'robert.jones@example.com', 'Engineering'),
('Emily', 'Brown', 'emily.brown@example.com', 'Sales'),
('Michael', 'Davis', 'michael.davis@example.com', 'Marketing');

Isso inserirá cinco linhas de dados na tabela employees.

Para verificar se os dados foram inseridos corretamente, execute o seguinte comando:

SELECT * FROM employees;

Você deve ver a seguinte saída:

1|John|Doe|john.doe@example.com|Sales
2|Jane|Smith|jane.smith@example.com|Marketing
3|Robert|Jones|robert.jones@example.com|Engineering
4|Emily|Brown|emily.brown@example.com|Sales
5|Michael|Davis|michael.davis@example.com|Marketing

Criar um Índice

Nesta etapa, você criará um índice na coluna last_name da tabela employees.

Índices são tabelas de pesquisa especiais que o mecanismo de busca do banco de dados pode usar para acelerar a recuperação de dados.

Para criar um índice chamado idx_lastname na coluna last_name, execute o seguinte comando:

CREATE INDEX idx_lastname ON employees (last_name);

Esta instrução SQL cria um índice chamado idx_lastname na coluna last_name da tabela employees.

Para verificar se o índice foi criado, você pode usar o seguinte comando:

PRAGMA index_list(employees);

Este comando exibirá uma lista de índices na tabela employees, incluindo o índice idx_lastname que você acabou de criar. Você deve ver uma saída semelhante a esta:

0|idx_lastname|0|c|0

Esta saída confirma que o índice idx_lastname existe na tabela employees.

Analisar Consultas com EXPLAIN QUERY PLAN

Nesta etapa, você aprenderá como usar o comando EXPLAIN QUERY PLAN para analisar como o SQLite executa uma consulta. Esta é uma ferramenta poderosa para entender o desempenho da consulta e identificar possíveis gargalos.

Para analisar uma consulta, preceda-a com EXPLAIN QUERY PLAN. Por exemplo, para analisar a seguinte consulta:

SELECT * FROM employees WHERE last_name = 'Smith';

Execute o seguinte comando:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE last_name = 'Smith';

A saída será semelhante a esta:

QUERY PLAN
`--SEARCH employees USING INDEX idx_lastname (last_name=?)

Esta saída informa que o SQLite está usando o índice idx_lastname para encontrar os funcionários com o sobrenome 'Smith'. A palavra-chave SEARCH indica que o SQLite está usando um índice para realizar a busca.

Se o índice não fosse usado, a saída seria diferente. Por exemplo, se você consultar funcionários com o primeiro nome 'John' (e você não criou um índice na coluna first_name), a saída seria:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE first_name = 'John';

A saída será semelhante a esta:

QUERY PLAN
`--SCAN employees

A palavra-chave SCAN indica que o SQLite está realizando uma varredura completa da tabela, o que significa que ele precisa examinar cada linha na tabela para encontrar os funcionários com o primeiro nome 'John'. Isso é menos eficiente do que usar um índice.

Adicionar Mais Dados e Analisar a Ordenação

Vamos inserir mais dados para tornar a análise do plano de consulta mais significativa. Insira os seguintes dados na tabela employees:

INSERT INTO employees (first_name, last_name, email, department) VALUES
('Alice', 'Johnson', 'alice.johnson@example.com', 'HR'),
('Bob', 'Williams', 'bob.williams@example.com', 'Finance'),
('Charlie', 'Brown', 'charlie.brown@example.com', 'IT'),
('David', 'Miller', 'david.miller@example.com', 'Sales'),
('Eve', 'Wilson', 'eve.wilson@example.com', 'Marketing'),
('John', 'Taylor', 'john.taylor@example.com', 'Engineering'),
('Jane', 'Anderson', 'jane.anderson@example.com', 'HR'),
('Robert', 'Thomas', 'robert.thomas@example.com', 'Finance'),
('Emily', 'Jackson', 'emily.jackson@example.com', 'IT'),
('Michael', 'White', 'michael.white@example.com', 'Sales');

Agora, vamos analisar uma consulta mais complexa que envolve ordenação. Suponha que você queira encontrar todos os funcionários do departamento de 'Vendas' e ordená-los por sobrenome. Você pode usar a seguinte consulta:

SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

Analise o plano de consulta:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

A saída pode ser semelhante a esta:

QUERY PLAN
`--SCAN employees USING INDEX idx_lastname

Neste caso, o SQLite está realizando uma varredura completa da tabela e, em seguida, ordenando os resultados.

Vamos criar um índice na coluna department:

CREATE INDEX idx_department ON employees (department);

Agora, analise o plano de consulta novamente:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

A saída pode mudar para:

QUERY PLAN
|--SEARCH employees USING INDEX idx_department (department=?)
`--USE TEMP B-TREE FOR ORDER BY

Agora, o SQLite está usando o índice idx_department para encontrar os funcionários no departamento de 'Vendas', mas ainda precisa ordenar os resultados.

Remover Índices Redundantes

Nesta etapa, você aprenderá como identificar e remover índices redundantes no SQLite. Índices redundantes podem impactar negativamente o desempenho do banco de dados, aumentando a sobrecarga das operações de escrita sem fornecer nenhum benefício às operações de leitura.

Vamos criar um índice nas colunas department e last_name:

CREATE INDEX idx_department_lastname ON employees (department, last_name);

Agora, liste todos os índices na tabela employees:

PRAGMA index_list(employees);

Você deve ver uma saída semelhante a esta:

0|idx_lastname|0|c|0
1|idx_department|0|c|0
2|idx_department_lastname|0|c|0

Agora, vamos analisar uma consulta que filtra por department e last_name:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' AND last_name = 'Doe';

A saída pode ser semelhante a esta:

QUERY PLAN
`--SEARCH employees USING INDEX idx_department_lastname (department=? AND last_name=?)

Esta saída indica que o SQLite está usando o índice idx_department_lastname para esta consulta.

Agora, vamos analisar uma consulta que filtra apenas por department:

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales';

A saída pode ser semelhante a esta:

QUERY PLAN
`--SEARCH employees USING INDEX idx_department (department=?)

Esta saída indica que o SQLite está usando o índice idx_department para esta consulta.

Neste cenário, o índice idx_department_lastname é redundante porque o índice idx_department pode ser usado para consultas que filtram apenas por department. O índice idx_department_lastname só fornece um benefício para consultas que filtram por department e last_name.

Para remover o índice idx_department redundante, você pode usar o comando DROP INDEX:

DROP INDEX idx_department;

Agora, liste todos os índices na tabela employees novamente:

PRAGMA index_list(employees);

Você deve ver que o índice idx_department não está mais listado.

Resumo

Neste laboratório, você aprendeu como otimizar o desempenho do banco de dados SQLite usando índices. Você criou índices de coluna única para melhorar a velocidade das consultas, analisou planos de consulta usando EXPLAIN QUERY PLAN e removeu índices redundantes. Essas habilidades ajudarão você a construir bancos de dados SQLite mais eficientes e responsivos.