DEV Community

Eduardo Wanderley
Eduardo Wanderley

Posted on • Updated on

Índices Únicos e Compostos: Segredos para Consultas SQL de Alta Performance

Este post é o segundo de uma série que tem como objetivo aprofundar um pouco mais sobre a performance de consultas em bancos de dados. Especificamente, este busca explorar um pouco mais sobre como identificar e evitar problemas de lentidão em consultas utilizando índices. Iremos discutir como diferentes operadores afetam o uso do índice e como podemos garantir que o índice que criamos será utilizado em tantas consultas quanto possível.

A maior parte do conteúdo deste post foi inspirada no livro SQL performance explained do autor Markus Winand.

Se você caiu de paraquedas aqui e nem ao menos sabe o que é um índice, recomendo que leia o post anterior onde explico de forma mais detalhada sobre o que é, e como funciona a busca por índice em um banco de dados.

Para dar continuidade, irei presumir que você já leu o post anterior e já possui um conhecimento prévio em bancos de dados. Vamos lá!

Cláusula WHERE:

A cláusula WHERE define as condições de busca em uma consulta SQL. Escrever uma condição de busca de forma descuidada pode fazer com que o banco de dados tenha que ler uma grande parte do índice desnecessariamente, ou até mesmo não utiliza-lo na busca.

Operador de igualdade:

Quando falamos em consultas sql, esse é o mais frequentemente usado dos operadores. Ele é usado para comparar valores em uma condição WHERE em uma consulta.

Por exemplo, se você quiser selecionar todos os registros de uma tabela clientes onde o valor da coluna "nome" é igual a "Eduardo", você usaria o operador de igualdade da seguinte forma:

SELECT * FROM clientes WHERE nome = 'Eduardo';
Enter fullscreen mode Exit fullscreen mode

Essa consulta retornaria todos os registros da tabela onde o valor na coluna "nome" é "Eduardo". Mas isso você já sabia.

Para deixar os exemplos um pouco mais robustos, criarei um banco de testes para me auxiliar a explicar melhor os conceitos apresentados aqui. Deixarei os scripts abaixo, fique a vontade para testar por aí também! 🙂

Para o meu exemplo, utilizarei um simples relacionamento entre clientes e compras utilizando o banco de dados MYSQL, mas sinta-se a vontade para usar o banco relacional de sua preferência.

Criação das tabelas :

CREATE TABLE clientes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    cidade VARCHAR(255) NOT NULL
);

CREATE TABLE compras (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_cliente INT NOT NULL,
    data_compra DATE NOT NULL,
    valor DECIMAL(10, 2) NOT NULL,
    categoria VARCHAR(255) NOT NULL,
    FOREIGN KEY (id_cliente) REFERENCES clientes (id)
);
Enter fullscreen mode Exit fullscreen mode

Populando a tabela :

INSERT INTO clientes (nome, email, cidade) VALUES 
('João Silva', 'joao.silva@example.com', 'São Paulo'),
('Maria Oliveira', 'maria.oliveira@example.com', 'Rio de Janeiro'),
('Carlos Souza', 'carlos.souza@example.com', 'Belo Horizonte'),
('Ana Costa', 'ana.costa@example.com', 'São Paulo'),
('Lucas Santos', 'lucas.santos@example.com', 'Curitiba'),
('Patricia Rocha', 'patricia.rocha@example.com', 'Porto Alegre'),
('Roberto Lima', 'roberto.lima@example.com', 'Manaus'),
('Fernanda Correia', 'fernanda.correia@example.com', 'Salvador');

INSERT INTO compras (id_cliente, data_compra, valor, categoria) VALUES 
(1, '2023-01-10', 150.00, 'Eletrônicos'),
(1, '2023-01-12', 200.00, 'Móveis'),
(2, '2023-01-15', 50.00, 'Roupas'),
(3, '2023-01-18', 80.00, 'Eletrônicos'),
(2, '2023-01-20', 100.00, 'Eletrônicos'),
(4, '2023-01-25', 300.00, 'Eletrodomésticos'),
(1, '2023-02-01', 450.00, 'Informática'),
(5, '2023-02-05', 700.00, 'Viagens'),
(6, '2023-02-08', 250.00, 'Móveis'),
(7, '2023-02-12', 100.00, 'Roupas'),
(8, '2023-02-15', 90.00, 'Roupas'),
(4, '2023-02-17', 140.00, 'Eletrônicos'),
(5, '2023-02-20', 60.00, 'Roupas'),
(7, '2023-02-22', 200.00, 'Eletrônicos'),
(8, '2023-02-25', 300.00, 'Eletrodomésticos'),
(3, '2023-02-28', 400.00, 'Viagens'),
(2, '2023-03-05', 150.00, 'Informática'),
(1, '2023-03-10', 50.00, 'Roupas'),
(6, '2023-03-15', 250.00, 'Eletrônicos'),
(7, '2023-03-20', 90.00, 'Roupas');
Enter fullscreen mode Exit fullscreen mode

Vamos executar o comando abaixo para verificar os índices criados para a tabela compras :

SHOW INDEX FROM compras;
Enter fullscreen mode Exit fullscreen mode

Esse foi o resultado apresentado no console (em um formato json com algumas colunas omitidas para facilitar a visualização) :

{
"show index from compras": [
    {
        "Table" : "compras",
        "Non_unique" : 0,
        "Key_name" : "PRIMARY",
        "Seq_in_index" : 1,
        "Column_name" : "id",
        "Index_type" : "BTREE",
    },
    {
        "Table" : "compras",
        "Non_unique" : 1,
        "Key_name" : "id_cliente",
        "Seq_in_index" : 1,
        "Column_name" : "id_cliente",
        "Index_type" : "BTREE",
    }
]}
Enter fullscreen mode Exit fullscreen mode

Podemos observar que foram criados dois índices para a tabela compras, mas não adicionamos nenhum comando explícito para criação desses índices, certo? 🤔

Isso acontece porque o banco de dados se encarrega de criar de maneira automática o índice para as chaves primárias e estrangeiras da tabela. Dessa forma, os desenvolvedores não precisam se preocupar em escrever scripts para cada chave da tabela, eliminando também a possibilidade de esquecimento por parte do mesmo ou a inclusão incorreta de índices.

Mas então isso quer dizer que, ao buscarmos todas as compras de um determinado cliente, o banco de dados fará o uso de um índice? Vamos descobrir.

Executando o comando :

SELECT * FROM compras WHERE id_cliente = 1;
Enter fullscreen mode Exit fullscreen mode

Devemos obter todas as compras realizadas pelo cliente de id 1 que foram registradas na tabela compras, mas isso você já sabia.

Como saber então se a busca foi feita utilizando algum índice ou não? Bom, isso pode variar de banco de dados para banco de dados, mas no geral, todos possuem um comando que te possibilita ver detalhes coletados pelo SGBD (Sistema gerenciador de banco de dados) sobre o plano de execução escolhido para performar a query.

No caso do mysql, o comando é o EXPLAIN.

Vamos executar o comando e analisar a saída do mesmo :

Comando :

EXPLAIN SELECT * FROM compras WHERE id_cliente = 1;
Enter fullscreen mode Exit fullscreen mode

Saída :

{
"EXPLAIN select * from compras where id_cliente = 1": [
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "c",
        "partitions" : null,
        "type" : "ref",
        "possible_keys" : "id_cliente",
        "key" : "id_cliente",
        "key_len" : "4",
        "ref" : "const",
        "rows" : 4,
        "filtered" : 100.0,
        "Extra" : null
    }
]}
Enter fullscreen mode Exit fullscreen mode

Abordaremos mais adiante, em outro post, de forma mais detalhada sobre a saída do comando EXPLAIN. Por enquanto, vamos focar apenas no atributo key, que nos diz qual índice foi utilizado para a consulta, e no atributo possible_keys, que nos diz quais índices poderiam ser utilizados para realizar a consulta.

Através da coluna key, podemos observar que foi utilizado o índice id_cliente, que foi criado automaticamente pelo banco de dados durante a criação da chave estrangeira que faz referência ao id do cliente na tabela de compras.

Mas e se, ao invés de ter apenas uma condição em nossa busca, houvessem duas ou mais? Por exemplo, se precisássemos buscar todas as compras de um determinado cliente em uma determinada categoria. Esse índice ainda seria útil?

Essa resposta vai depender muito da quantidade de registros que possuímos na tabela compras. Isso ocorre porque o SGBD poderia utilizar o índice para buscar todos os registros de compra para o cliente com o id fornecido, mas ainda teria que percorrer a tabela para buscar os registros que correspondem à categoria informada. Em uma tabela com poucos dados, esse filtro adicional pode não ser tão custoso, mas à medida que os dados crescem, isso pode se tornar um problema.

Nesse sentido, os bancos de dados nos permitem criar índices compostos, que são índices formados por duas ou mais colunas. Vamos entender um pouco melhor a seguir:

Indíces compostos :

Índices compostos funcionam de forma semelhante aos únicos, no entanto, conseguimos configurar mais de uma coluna da tabela na composição do índice.

Vamos voltar ao exemplo anterior e fazer uma busca no nosso banco de testes da seguinte maneira :

SELECT * FROM compras c WHERE id_cliente = 1 AND categoria = 'Informática';
Enter fullscreen mode Exit fullscreen mode

Para essa busca, estamos utilizando um campo que contém índice (id_cliente) e um campo que não possui nenhum índice criado até então (categoria). Nesse caso, será que o banco de dados fará o uso de algum índice? Vamos descobrir.

Executando o comando :

EXPLAIN SELECT * FROM compras c WHERE id_cliente = 1 AND categoria = 'Informática';
Enter fullscreen mode Exit fullscreen mode

Obtemos a seguinte saída :

{
"EXPLAIN SELECT * FROM compras c WHERE id_cliente = 1 AND categoria = 'Informática'": [
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "c",
        "partitions" : null,
        "type" : "ref",
        "possible_keys" : "id_cliente",
        "key" : "id_cliente",
        "key_len" : "4",
        "ref" : "const",
        "rows" : 949,
        "filtered" : 10.0,
        "Extra" : "Using where"
    }
]}
Enter fullscreen mode Exit fullscreen mode

Analisando a saída, conseguimos observar que foi utilizado o índice id_cliente para realizar a busca. Então já que foi utilizado um índice, está garantido que todas as minhas queries irão rodar utilizando o índice e todas serão extremamente performáticas? Não necessariamente.

No nosso exemplo, as tabelas possuem pouquíssimos registros e por isso a busca foi praticamente instantânea, em cenários reais, onde serão armazenados centenas de milhares (ou até mesmo milhões) de registros, provavelmente essa query não iria performar de uma maneira tão adequada.

Uma possível solução seria a criação de um novo índice, dessa vez composto pelas colunas id_cliente e categoria . Dessa forma, o banco de dados conseguiria realizar a consulta de forma muito mais eficiente, já que os dois critérios de busca utilizados fazem parte da construção do índice.

Para exemplificar, vamos criar um índice composto no nosso banco de teste :

CREATE INDEX categoria_id_cliente on compras (categoria, id_cliente);
Enter fullscreen mode Exit fullscreen mode

Agora vamos utilizar o EXPLAIN novamente, como fizemos anteriormente:

EXPLAIN SELECT * FROM compras c WHERE id_cliente = 1 AND categoria = 'Informática';
Enter fullscreen mode Exit fullscreen mode

Essa foi a saída :

{
"EXPLAIN SELECT * FROM compras c where id_cliente = 1 and categoria = 'Informática'": [
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "c",
        "partitions" : null,
        "type" : "ref",
        "possible_keys" : "id_cliente,categoria_id_cliente",
        "key" : "categoria_id_cliente",
        "key_len" : "1026",
        "ref" : "const,const",
        "rows" : 1490,
        "filtered" : 100.0,
        "Extra" : null
    }
]}
Enter fullscreen mode Exit fullscreen mode

Analisando a saída, podemos observar, pelo atributo possible_keys, que o banco de dados tem a opção de utilizar dois índices para otimizar a busca. O primeiro é o índice id_cliente, que é composto exclusivamente pela coluna do ID do cliente, e o segundo é o índice categoria_id_cliente, que, além do ID do cliente, abrange também a categoria da compra.

Olhando para o atributo key, notamos que o banco de dados optou por utilizar o índice categoria_id_cliente para executar a consulta. Essa escolha foi feita possívelmente pois esse índice abrange ambos os critérios de busca presentes na cláusula WHERE. Como mencionado anteriormente, essa abordagem tende a resultar em um desempenho mais eficiente.

Ordem das colunas em índices compostos:

Você deve ter percebido (ou não), que ao criar o índice composto eu coloquei primeiro a coluna categoria e depois a coluna id_cliente, certo? Mas se eu tivesse colocado o inverso, teria alguma diferença? Na verdade sim, e muita!

Tratando-se de índices compostos, a ordem de criação (ordem na qual você dispõe as colunas que farão parte do índice) possui muito impacto na usabilidade do índice. Isso porquê o primeiro critério de ordenação define a ordem principal e o segundo critério depende da ordem definida pelo primeiro critério. Ou seja, a segunda coluna só definirá a ordem se houver duas ou mais entradas com a primeira coluna igual.

Dessa forma, colocando em primeiro lugar a coluna categoria, conseguimos reaproveitar o índice categoria_id_cliente em buscas onde temos a categoria como critério de busca. Vamos ver na prática :

EXPLAIN SELECT * FROM compras where categoria = 'Informática';
Enter fullscreen mode Exit fullscreen mode

Saída :

{
"EXPLAIN SELECT * FROM compras where categoria = 'Informática'": [
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "c",
        "partitions" : null,
        "type" : "ref",
        "possible_keys" : "categoria_id_cliente",
        "key" : "categoria_id_cliente",
        "key_len" : "1022",
        "ref" : "const",
        "rows" : 5299,
        "filtered" : 100.0,
        "Extra" : null
    }
]}
Enter fullscreen mode Exit fullscreen mode

Analisando a saída, podemos ver que realmente foi utilizado o índice categoria_id_cliente para realizar a busca.

E se o índice tivesse sido criado de outra maneira, com a coluna id_cliente antes da coluna categoria ? Vamos fazer esse teste :

Deletando o índice categoria_id_cliente :

DROP INDEX categoria_id_cliente ON compras;
Enter fullscreen mode Exit fullscreen mode

Criando um novo índice invertendo a posição das colunas :

CREATE INDEX id_cliente_categoria on compras (id_cliente, categoria);
Enter fullscreen mode Exit fullscreen mode

Utilizando o EXPLAIN novamente para a mesma busca :

EXPLAIN SELECT * FROM compras c where categoria = 'Informática';
Enter fullscreen mode Exit fullscreen mode

Essa foi a saída :

{
"EXPLAIN SELECT * FROM compras c where categoria = 'Informática'": [
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "c",
        "partitions" : null,
        "type" : "ALL",
        "possible_keys" : null,
        "key" : null,
        "key_len" : null,
        "ref" : null,
        "rows" : 11960,
        "filtered" : 10.0,
        "Extra" : "Using where"
    }
]}
Enter fullscreen mode Exit fullscreen mode

Analisando a saída, conseguimos perceber através do atributo key que não foi utilizado nenhum índice para a busca.

Dado isso, é importante escolhermos de maneira cuidadosa quais colunas serão responsáveis pela ordenação, afim de que o índice seja aproveitado o máximo possível.

O mesmo se aplica para índices com mais de duas colunas. Por exemplo, um índice com três colunas pode ser usado quando a busca é pela primeira coluna, pelas duas primeiras colunas, ou por todas as colunas juntas, seguindo a mesma lógica do índice de duas colunas.

No entanto, é importante dizer que, apesar de tanto o índice composto quanto o índice único entregarem ótima performance no select, na maioria das vezes o índice único é preferível. Além de poupar espaço em disco, índices compostos podem causar sobrecargas de manutenção. Quanto menos índices uma tabela tem, melhor a performance do update, delete e insert.

Índices Compostos em Buscas por Range

Ao realizar buscas com operadores de intervalo, como < (menor que), > (maior que), e BETWEEN (entre), tambem é possível utilizar índices para melhorar a performance. A eficácia dos índices em tais consultas depende da maneira como o banco de dados percorre os nós de folha do índice. Por isso, é importante minimizar o intervalo de busca para otimizar a consulta. Vamos explorar isso de forma mais prática.

Imagine que a tabela compras tenha muitos registros e que buscas combinando categoria e um intervalo de datas sejam frequentes. Nesse contexto, seria vantajoso criar um índice composto para as colunas data_compra e categoria:

CREATE INDEX data_compra_categoria ON compras (data_compra, categoria);
Enter fullscreen mode Exit fullscreen mode

Agora vamos executar o comando EXPLAIN para a seguinte query:

EXPLAIN SELECT * FROM compras c where categoria = 'informatica' AND data_compra > DATE('2020-02-25');
Enter fullscreen mode Exit fullscreen mode

Saída:

{
"EXPLAIN SELECT * FROM compras c where categoria = 'informatica' AND data_compra > DATE('2020-02-25')": [
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "c",
        "partitions" : null,
        "type" : "ALL",
        "possible_keys" : "idx_data_compra,data_compra_categoria",
        "key" : null,
        "key_len" : null,
        "ref" : null,
        "rows" : 20,
        "filtered" : 10.0,
        "Extra" : "Using where"
    }
]}
Enter fullscreen mode Exit fullscreen mode

Ao analisar a saída do comandoEXPLAIN, notamos que o índice composto data_compra_categoria é considerado como uma chave possível, mas o SGBD optou por não utiliza-lo, isso pode ocorrer devido à ordem das colunas no índice composto. Vamos entender:

Ordem das Colunas:

A ordem das colunas em um índice composto é crucial. Por exemplo, se o índice começa com a coluna data_compra, a coluna categoria só será usada como critério de ordenação quando houver datas de compra idênticas. Por outro lado, se o índice começar com categoria, o primeiro critério de ordenação será a categoria, o que pode reduzir significativamente o intervalo de busca.

Considerando um cenário com 1000 registros na tabela compras, dos quais 100 têm data maior que '2023-02-25' e apenas 5 pertencem à categoria informática, qual estratégia de ordenação faz mais sentido? Ordenar primeiro por categoria, certo? Fazendo isso, o banco de dados primeiro usaria o índice para encontrar as compras de informática (5 registros), e depois filtraria esses registros pela data de compra.

Por outro lado, se o índice começar pela data de compra, o banco de dados teria que filtrar entre 100 registros para encontrar aqueles que também atendem ao critério de categoria. Isso significa uma varredura muito maior.

Dessa forma, para tabelas com grandes volumes de dados, a escolha acertada da ordem das colunas em índices compostos é essencial. Geralmente, é preferível começar com critérios de igualdade e, em seguida, usar critérios de intervalo.

Limitando o range de busca:

Uma maneira de incentivar o plano de execução a utilizar o índice de data no exemplo mencionado é restringir ao máximo o intervalo da data. Isso torna o uso do índice mais benéfico do que realizar uma varredura completa da tabela, mesmo em um banco de dados de teste com uma quantidade limitada de dados.

Vamos executar o comando EXPLAIN para a mesma query, mas agora limitando mais o espaço de tempo entre as compras.

EXPLAIN SELECT * FROM compras c where data_compra BETWEEN DATE('2023-01-01') AND DATE('2023-01-15');
Enter fullscreen mode Exit fullscreen mode

Saída:

{
"EXPLAIN SELECT * FROM compras c where data_compra BETWEEN DATE('2023-01-01') AND DATE('2023-01-15')": [
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "c",
        "partitions" : null,
        "type" : "range",
        "possible_keys" : "data_compra_categoria",
        "key" : "data_compra_categoria",
        "key_len" : "3",
        "ref" : null,
        "rows" : 3,
        "filtered" : 100.0,
        "Extra" : "Using index condition"
    }
]}
Enter fullscreen mode Exit fullscreen mode

Ao analisar a saída do comando EXPLAIN, percebemos que o banco de dados dessa vez optou por utilizar o índice data_compra_categoria, demostrando a eficácia da estratégia de limitação do intervalo de datas.

Conclusão

Chegamos ao fim deste post, onde exploramos o uso de índices em bancos de dados e como eles influenciam o desempenho das consultas SQL. Vimos como índices únicos e compostos operam e o impacto significativo que a ordem das colunas em índices compostos pode ter na performance das queries que fazemos.

Em posts futuros, vamos explorar ainda mais outros aspectos que não cobrimos hoje. Até lá, experimente os conceitos discutidos aqui e veja como eles podem melhorar a performance das suas consultas em situações reais.

Até a próxima!

Top comments (0)