Otimização de Desempenho e Índices MySQL

Introdução

Neste laboratório, você aprenderá sobre índices MySQL e técnicas de otimização de desempenho. O laboratório foca na criação e gerenciamento de índices para melhorar o desempenho de consultas ao banco de dados.

Você começará criando uma tabela users e inserindo dados de exemplo. Em seguida, criará um índice de coluna única na coluna username e aprenderá como verificar sua criação. O laboratório também abordará a análise de planos de consulta usando EXPLAIN, a adição de índices compostos para consultas com várias colunas e a remoção de índices não utilizados para manter a eficiência do banco de dados.

Criar um Índice de Coluna Única em uma Tabela

Nesta etapa, você aprenderá como criar um índice de coluna única no MySQL. Índices são cruciais para melhorar o desempenho de consultas ao banco de dados, especialmente ao lidar com tabelas grandes. Um índice em uma coluna permite que o banco de dados localize rapidamente linhas que correspondem a um valor específico nessa coluna, sem ter que escanear a tabela inteira.

Entendendo Índices

Pense em um índice como o índice de um livro. Em vez de ler o livro inteiro para encontrar um tópico específico, você pode usar o índice para localizar rapidamente as páginas relevantes. Da mesma forma, um índice de banco de dados ajuda o motor do banco de dados a encontrar linhas específicas rapidamente.

Criando uma Tabela

Primeiro, vamos criar uma tabela simples chamada users para demonstrar a criação de um índice. Abra um terminal na VM LabEx. Você pode usar o atalho Xfce Terminal na área de trabalho.

Conecte-se ao servidor MySQL como usuário root:

sudo mysql -u root

Primeiro, crie um banco de dados para este laboratório e selecione-o:

CREATE DATABASE lab_db;
USE lab_db;

Agora, crie a tabela users:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Esta instrução SQL cria uma tabela chamada users com colunas para id, username, email e created_at. A coluna id é definida como chave primária e auto-incrementa.

Vamos inserir alguns dados de exemplo na tabela users:

INSERT INTO users (username, email) VALUES
('john_doe', 'john.doe@example.com'),
('jane_smith', 'jane.smith@example.com'),
('peter_jones', 'peter.jones@example.com');

Criando um Índice de Coluna Única

Agora, vamos criar um índice na coluna username. Isso ajudará a acelerar consultas que buscam usuários por seu nome de usuário.

CREATE INDEX idx_username ON users (username);

Esta instrução cria um índice chamado idx_username na coluna username da tabela users.

Verificando o Índice

Você pode verificar se o índice foi criado usando o comando SHOW INDEXES:

SHOW INDEXES FROM users;

A saída mostrará os detalhes dos índices na tabela users, incluindo o índice idx_username que você acabou de criar. Você deverá ver uma linha onde Key_name é idx_username e Column_name é username.

+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users |          0 | PRIMARY      |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_username |            1 | username    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Você agora criou com sucesso um índice de coluna única em uma tabela. Isso melhorará o desempenho de consultas que filtram dados com base na coluna indexada.

Analisar um Plano de Consulta Usando EXPLAIN

Nesta etapa, você aprenderá a usar a instrução EXPLAIN no MySQL para analisar o plano de execução de consultas. Compreender o plano de consulta é essencial para identificar gargalos de desempenho e otimizar suas consultas.

O que é um Plano de Consulta?

Um plano de consulta é um roteiro que o motor do banco de dados usa para executar uma consulta. Ele descreve a ordem em que as tabelas são acessadas, os índices que são usados e os algoritmos que são aplicados para recuperar os dados. Ao analisar o plano de consulta, você pode entender como o banco de dados está executando sua consulta e identificar áreas para melhoria.

Usando a Instrução EXPLAIN

A instrução EXPLAIN fornece informações sobre como o MySQL executa uma consulta. Ela mostra as tabelas envolvidas, os índices usados, a ordem de junção (join) e outros detalhes que podem ajudá-lo a entender o desempenho da consulta.

Agora, vamos analisar uma consulta simples usando EXPLAIN.

EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

A saída da instrução EXPLAIN será uma tabela com várias colunas. Aqui está uma descrição de algumas das colunas mais importantes:

id: O ID da instrução SELECT.
select_type: O tipo de consulta SELECT (por exemplo, SIMPLE, PRIMARY, SUBQUERY).
table: A tabela que está sendo acessada.
type: O tipo de junção (join type). Esta é uma das colunas mais importantes. Valores comuns incluem:
- system: A tabela tem apenas uma linha.
- const: A tabela tem no máximo uma linha correspondente, que é lida no início da consulta.
- eq_ref: Uma linha é lida desta tabela para cada combinação de linhas das tabelas anteriores. Isso é usado ao fazer junção em uma coluna indexada.
- ref: Todas as linhas correspondentes são lidas desta tabela para cada combinação de linhas das tabelas anteriores. Isso é usado ao fazer junção em uma coluna indexada.
- range: Apenas linhas dentro de um determinado intervalo são recuperadas, usando um índice.
- index: Uma varredura completa do índice é realizada.
- ALL: Uma varredura completa da tabela é realizada. Este é o tipo menos eficiente.
possible_keys: Os índices que o MySQL poderia usar para encontrar as linhas na tabela.
key: O índice que o MySQL realmente usou.
key_len: O comprimento da chave que o MySQL usou.
ref: As colunas ou constantes que são comparadas ao índice.
rows: O número de linhas que o MySQL estima que precisará examinar para executar a consulta.
Extra: Informações adicionais sobre como o MySQL está executando a consulta. Valores comuns incluem:
- Using index: A consulta pode ser satisfeita usando apenas o índice.
- Using where: O MySQL precisa filtrar as linhas após acessar a tabela.
- Using temporary: O MySQL precisa criar uma tabela temporária para executar a consulta.
- Using filesort: O MySQL precisa classificar as linhas após acessar a tabela.

Interpretando a Saída do EXPLAIN

Para a consulta SELECT * FROM users WHERE username = 'john_doe', a saída do EXPLAIN deve ser algo como isto:

+------+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
| id   | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+------+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | users | ref  | idx_username  | idx_username | 767     | const | 1    | Using index condition |
+------+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+

Neste exemplo:

type é ref, o que significa que o MySQL está usando um índice para encontrar a linha correspondente.
possible_keys e key mostram idx_username, o que significa que o MySQL está usando o índice idx_username que criamos na etapa anterior.
rows é 1, o que significa que o MySQL estima que precisará examinar apenas uma linha para executar a consulta.

Analisando uma Consulta Sem Índice

Agora, vamos analisar uma consulta que não usa um índice. Primeiro, vamos adicionar uma nova coluna à tabela users chamada city:

ALTER TABLE users ADD COLUMN city VARCHAR(255);

Agora, vamos executar um EXPLAIN em uma consulta que busca por city. Como ainda não adicionamos nenhum dado à coluna city, vamos atualizar uma das linhas:

UPDATE users SET city = 'New York' WHERE username = 'john_doe';

Agora, execute a instrução EXPLAIN novamente:

EXPLAIN SELECT * FROM users WHERE city = 'New York';

A saída pode ser algo como isto:

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

Neste exemplo:

type é ALL, o que significa que o MySQL está realizando uma varredura completa da tabela.
possible_keys e key são ambos NULL, o que significa que o MySQL não está usando nenhum índice.
rows é 3, o que significa que o MySQL estima que precisará examinar todas as 3 linhas da tabela para executar a consulta.
Extra mostra Using where, o que significa que o MySQL precisa filtrar as linhas após acessar a tabela.

Isso indica que a consulta não está otimizada e poderia se beneficiar de um índice na coluna city.

Saída do plano de consulta MySQL EXPLAIN

Adicionar um Índice Composto para Consultas Multi-Coluna

Nesta etapa, você aprenderá como criar um índice composto no MySQL. Um índice composto é um índice em duas ou mais colunas de uma tabela. Ele pode melhorar significativamente o desempenho de consultas que filtram dados com base em várias colunas.

O que é um Índice Composto?

Um índice composto é um índice que abrange várias colunas. Ele é útil quando as consultas usam frequentemente várias colunas na cláusula WHERE. A ordem das colunas no índice composto é importante. O índice é mais eficaz quando as colunas são especificadas na mesma ordem na cláusula WHERE da consulta.

Vamos adicionar mais alguns dados à tabela users, incluindo cidades diferentes:

INSERT INTO users (username, email, city) VALUES
('alice_brown', 'alice.brown@example.com', 'Los Angeles'),
('bob_davis', 'bob.davis@example.com', 'Chicago'),
('charlie_wilson', 'charlie.wilson@example.com', 'New York'),
('david_garcia', 'david.garcia@example.com', 'Los Angeles');

Criando um Índice Composto

Suponha que você execute frequentemente consultas que filtram usuários por city e username. Nesse caso, você pode criar um índice composto nas colunas city e username.

CREATE INDEX idx_city_username ON users (city, username);

Esta instrução cria um índice chamado idx_city_username nas colunas city e username da tabela users.

Verificando o Índice

Você pode verificar se o índice foi criado usando o comando SHOW INDEXES:

SHOW INDEXES FROM users;

A saída mostrará os detalhes dos índices na tabela users, incluindo o índice idx_city_username que você acabou de criar. Você deverá ver duas linhas para idx_city_username, uma para a coluna city e outra para a coluna username.

+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users |          0 | PRIMARY           |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_username      |            1 | username    | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            1 | city        | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            2 | username    | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Usando o Índice Composto

Para ver o benefício do índice composto, você pode usar o comando EXPLAIN para analisar uma consulta que usa as colunas city e username na cláusula WHERE.

EXPLAIN SELECT * FROM users WHERE city = 'New York' AND username = 'charlie_wilson';

A saída do EXPLAIN mostrará que a consulta está usando o índice idx_city_username, o que significa que o banco de dados pode encontrar rapidamente a linha correspondente sem escanear a tabela inteira. Procure pelas colunas possible_keys e key na saída. Se o índice estiver sendo usado, você verá idx_city_username nessas colunas.

+------+-------------+-------+------+--------------------------------+--------------+---------+-------+------+------------------------------------+
| id   | select_type | table | type | possible_keys                  | key          | key_len | ref   | rows | Extra                              |
+------+-------------+-------+------+--------------------------------+--------------+---------+-------+------+------------------------------------+
|    1 | SIMPLE      | users | ref  | idx_username,idx_city_username | idx_username | 767     | const | 1    | Using index condition; Using where |
+------+-------------+-------+------+--------------------------------+--------------+---------+-------+------+------------------------------------+

Ordem das Colunas no Índice

A ordem das colunas no índice composto é importante. Se você criar um índice em (username, city) em vez de (city, username), o índice será menos eficaz para consultas que filtram por city e depois por username.

Por exemplo, se tivéssemos um índice em (username, city) e executássemos a seguinte consulta:

EXPLAIN SELECT * FROM users WHERE city = 'New York' AND username = 'charlie_wilson';

O MySQL pode não usar o índice, ou pode usá-lo apenas parcialmente, porque a coluna city não é a coluna principal (leading column) no índice.

Remover um Índice Não Utilizado

Nesta etapa, você aprenderá como remover um índice não utilizado no MySQL. Embora os índices possam melhorar significativamente o desempenho das consultas, eles também adicionam sobrecarga às operações de escrita (inserções, atualizações e exclusões). Portanto, é importante identificar e remover índices que não estão mais sendo usados.

Por que Remover Índices Não Utilizados?

Índices não utilizados ocupam espaço em disco e podem desacelerar as operações de escrita. Quando os dados são modificados em uma tabela, o motor do banco de dados também deve atualizar todos os índices dessa tabela. Se um índice não está sendo usado por nenhuma consulta, ele está apenas adicionando sobrecarga desnecessária.

Nas etapas anteriores, criamos um índice chamado idx_username na coluna username. Vamos supor que, após analisar seus padrões de consulta, você determine que este índice não está mais sendo usado.

Removendo o Índice

Para remover o índice idx_username, você pode usar a instrução DROP INDEX:

DROP INDEX idx_username ON users;

Esta instrução remove o índice idx_username da tabela users.

Verificando a Remoção do Índice

Você pode verificar se o índice foi removido usando o comando SHOW INDEXES:

SHOW INDEXES FROM users;

A saída mostrará os detalhes dos índices na tabela users. Você não deverá mais ver o índice idx_username na saída.

+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users |          0 | PRIMARY           |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            1 | city        | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| users |          1 | idx_city_username |            2 | username    | A         |           7 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

Identificando Índices Não Utilizados

Em um cenário do mundo real, identificar índices não utilizados pode ser desafiador. O MySQL fornece várias ferramentas e técnicas para ajudá-lo nesta tarefa:

MySQL Enterprise Audit: Este recurso permite registrar todas as consultas executadas em seu servidor. Você pode então analisar os logs de consulta para identificar quais índices estão sendo usados.
Performance Schema: O Performance Schema fornece informações detalhadas sobre o desempenho do servidor, incluindo o uso de índices.
Ferramentas de Terceiros: Várias ferramentas de terceiros podem ajudá-lo a monitorar o uso de índices e identificar índices não utilizados.

Ao monitorar regularmente o uso de seus índices e remover índices não utilizados, você pode melhorar o desempenho geral do seu banco de dados.

Agora que completamos todas as etapas, vamos sair do console do MySQL:

exit;

Exemplo de remoção de índice da tabela de usuários do MySQL

Resumo

Neste laboratório, você aprendeu como criar um índice de coluna única no MySQL para melhorar o desempenho das consultas. Você também aprendeu a usar a instrução EXPLAIN para analisar planos de execução de consultas e identificar gargalos de desempenho. Além disso, você praticou a criação de índices compostos para consultas com várias colunas e a remoção de índices não utilizados para manter a eficiência do banco de dados. Compreender e utilizar índices de forma eficaz é uma habilidade fundamental para otimizar o desempenho do banco de dados.

Índices MySQL e Otimização de Desempenho