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.
