Como usar índices no MySQL

O autor selecionou o programa Write for DOnations.

Introdução

Os bancos de dados relacionais podem ser usados para trabalhar com dados de qualquer tamanho, incluindo grandes bancos de dados contendo milhões de linhas. A Linguagem de Consulta Estruturada (SQL) fornece uma maneira concisa e direta de localizar linhas específicas em tabelas de banco de dados com base em critérios específicos. À medida que os bancos de dados se tornam maiores, localizar linhas específicas dentro deles torna-se cada vez mais difícil, semelhante a procurar uma agulha no palheiro.

A capacidade dos bancos de dados de aceitar uma ampla variedade de condições de consulta torna um desafio para o mecanismo de banco de dados prever quais consultas serão mais comuns. O mecanismo deve estar preparado para localizar linhas de forma eficiente nas tabelas do banco de dados, independentemente de seu tamanho. No entanto, à medida que os dados aumentam, o desempenho da pesquisa pode ser prejudicado. Quanto maior o conjunto de dados, mais difícil é para o mecanismo de banco de dados localizar rapidamente os documentos que correspondem à consulta.

Os administradores de banco de dados podem usar índices para auxiliar o mecanismo de banco de dados e melhorar seu desempenho.

Neste tutorial, você aprenderá o que são índices, como criá-los e se eles são usados para consultar o banco de dados.

Pré-requisitos

Para seguir este guia, você precisará de um computador executando um sistema de gerenciamento de banco de dados relacional (RDBMS) baseado em SQL. As instruções e exemplos neste guia foram validados usando o seguinte ambiente:

• Um servidor executando o Ubuntu 20.04, com um usuário não root com privilégios administrativos e um firewall configurado com UFW, conforme descrito em nosso guia inicial de configuração do servidor para Ubuntu 20.04.
• MySQL instalado e protegido no servidor, conforme descrito na Etapa 3.
• Familiaridade básica com a execução de consultas SELECT para recuperar dados do banco de dados, conforme descrito em nosso guia Como selecionar linhas de tabelas em SQL.

Observação: observe que muitos RDBMSs usam suas próprias implementações exclusivas de SQL. Embora os comandos descritos neste tutorial funcionem na maioria dos RDBMSs, os índices não fazem parte da sintaxe SQL padrão e, portanto, a sintaxe exata ou a saída podem diferir se você testá-los em um sistema diferente do MySQL.

Você também precisará de um banco de dados com algumas tabelas carregadas com dados de exemplo para praticar o uso de índices. Recomendamos que você consulte a seção Conectando ao MySQL e configurando um banco de dados de exemplo para obter detalhes sobre como conectar a um servidor MySQL e criar o banco de dados de teste usado nos exemplos deste guia.

Conectando-se ao MySQL e configurando um banco de dados de amostra

Nesta seção, você se conectará a um servidor MySQL e criará um banco de dados de exemplo para poder seguir os exemplos deste guia.

Se o seu sistema de banco de dados SQL for executado em um servidor remoto, faça SSH em seu servidor a partir de sua máquina local:

ssh sammy@your_server_ip

Em seguida, abra o prompt do servidor MySQL, substituindo sammy pelo nome da sua conta de usuário do MySQL:

mysql -u sammy -p

Crie um banco de dados chamado indexes:

CREATE DATABASE indexes;

Se o banco de dados foi criado com sucesso, você receberá uma saída como esta:

Output
Query OK, 1 row affected (0.01 sec)

Para selecionar o banco de dados indexes, execute a seguinte instrução USE:

USE indexes;

Você receberá a seguinte saída:

Output
Database changed

Depois de selecionar o banco de dados, você pode criar uma tabela de amostra dentro dele. Para este guia, você usará um banco de dados de funcionários imaginário para armazenar detalhes sobre os funcionários atuais e seus dispositivos de trabalho.

A tabela funcionários conterá dados simplificados sobre funcionários no banco de dados. Ele conterá as seguintes colunas:

• employee_id: Esta coluna contém o identificador do funcionário, representado pelo tipo de dados int. Essa coluna se tornará a chave primária da tabela, com cada valor se tornando um identificador exclusivo para sua respectiva linha.
• first_name: Esta coluna contém o primeiro nome de cada funcionário, expresso usando o tipo de dados varchar com no máximo 50 caracteres.< /li>
• last_name: Esta coluna contém o sobrenome de cada funcionário, expresso usando o tipo de dados varchar com no máximo 50 caracteres.< /li>
• device_serial: Esta coluna contém o número de série do computador atribuído ao funcionário, expresso usando o tipo de dados varchar com um máximo de 15 personagens.
• salário: esta coluna contém o salário de cada funcionário, expresso usando o tipo de dados int que armazena dados numéricos.

Crie a tabela de amostra com o seguinte comando:

CREATE TABLE employees (
    employee_id int,
    first_name varchar(50),
    last_name varchar(50),
    device_serial varchar(15),
    salary int
);

Se a seguinte saída for impressa, a tabela foi criada:

Output
Query OK, 0 rows affected (0.00 sec)

Em seguida, carregue a tabela employees com alguns dados de amostra executando a seguinte operação INSERT INTO:

INSERT INTO employees VALUES
    (1, 'John', 'Smith', 'ABC123', 60000),
    (2, 'Jane', 'Doe', 'DEF456', 65000),
    (3, 'Bob', 'Johnson', 'GHI789', 70000),
    (4, 'Sally', 'Fields', 'JKL012', 75000),
    (5, 'Michael', 'Smith', 'MNO345', 80000),
    (6, 'Emily', 'Jones', 'PQR678', 85000),
    (7, 'David', 'Williams', 'STU901', 90000),
    (8, 'Sarah', 'Johnson', 'VWX234', 95000),
    (9, 'James', 'Brown', 'YZA567', 100000),
    (10, 'Emma', 'Miller', 'BCD890', 105000),
    (11, 'William', 'Davis', 'EFG123', 110000),
    (12, 'Olivia', 'Garcia', 'HIJ456', 115000),
    (13, 'Christopher', 'Rodriguez', 'KLM789', 120000),
    (14, 'Isabella', 'Wilson', 'NOP012', 125000),
    (15, 'Matthew', 'Martinez', 'QRS345', 130000),
    (16, 'Sophia', 'Anderson', 'TUV678', 135000),
    (17, 'Daniel', 'Smith', 'WXY901', 140000),
    (18, 'Mia', 'Thomas', 'ZAB234', 145000),
    (19, 'Joseph', 'Hernandez', 'CDE567', 150000),
    (20, 'Abigail', 'Smith', 'FGH890', 155000);

O banco de dados responderá com a mensagem de sucesso:

Output
Query OK, 20 rows affected (0.010 sec)
Records: 20  Duplicates: 0  Warnings: 0

Observação: o conjunto de dados não é grande o suficiente para ilustrar diretamente o impacto de desempenho dos índices. No entanto, este conjunto de dados demonstrará como o MySQL usa os índices para limitar a quantidade de linhas percorridas para realizar consultas e obter resultados.

Com isso, você está pronto para seguir o restante do guia e começar a usar índices no MySQL.

Introdução aos índices

Normalmente, quando você executa uma consulta no banco de dados MySQL, o banco de dados deve passar por todas as linhas da tabela, uma a uma. Por exemplo, você pode querer pesquisar os sobrenomes dos funcionários correspondentes a Smith ou todos os funcionários com salário superior a $100.000. Cada linha da tabela será examinada uma a uma para verificar se corresponde à condição. Em caso afirmativo, será adicionado à lista de linhas retornadas. Caso contrário, o MySQL irá escanear as linhas subseqüentes até navegar por toda a tabela.

Embora esse método de localização de linhas correspondentes seja eficaz, ele pode se tornar lento e consumir muitos recursos à medida que o tamanho da tabela aumenta. Como resultado, essa abordagem pode não ser adequada para tabelas ou consultas grandes que exigem acesso rápido ou frequente aos dados.

Para resolver os problemas de desempenho com tabelas e consultas grandes, você pode usar índices. Os índices são estruturas de dados exclusivas que armazenam apenas um subconjunto classificado dos dados separadamente das linhas da tabela. Eles permitem que o mecanismo de banco de dados funcione com mais rapidez e eficiência ao pesquisar valores ou ordenar um campo ou conjunto de campos específico.

Usando a tabela employees como exemplo, uma das consultas típicas que você pode executar é encontrar funcionários pelo sobrenome. Sem nenhum índice, o MySQL recuperaria todos os funcionários da tabela e verificaria se o sobrenome corresponde à consulta. Mas ao usar um índice, o MySQL manterá uma lista separada de sobrenomes, contendo apenas ponteiros para as linhas dos funcionários fornecidos na tabela principal. Em seguida, ele usará esse índice para recuperar os resultados sem examinar a tabela inteira.

Você pode pensar nos índices como uma analogia a uma lista telefônica. Para localizar uma pessoa chamada John Smith no livro, você primeiro vira para a página certa onde as pessoas com nomes começando com S estão listadas e, em seguida, procura nas páginas por pessoas com nomes começando com Sm. Seguindo essa lógica, você pode eliminar muitos cadastros rapidamente, sabendo que eles não correspondem à pessoa que você procura. O processo funciona apenas porque os dados na lista telefônica são classificados em ordem alfabética, o que raramente é o caso de dados armazenados diretamente no banco de dados. Um índice no mecanismo de banco de dados serve a um propósito semelhante a um catálogo telefônico, mantendo as referências ordenadas alfabeticamente aos dados e, assim, ajudando o banco de dados a localizar rapidamente as linhas necessárias.

O uso de índices no MySQL tem vários benefícios. Os mais comuns são acelerar as cláusulas ORDER BY mais rapidamente e impor a exclusividade do valor.

No entanto, o uso de índices pode degradar o desempenho máximo do banco de dados em algumas circunstâncias. Os índices são projetados para acelerar a recuperação de dados e são implementados usando estruturas de dados adicionais que são armazenadas juntamente com os dados da tabela. Essas estruturas devem ser mantidas atualizadas com cada alteração no banco de dados, o que pode diminuir o desempenho das consultas SELECT às vezes pode ser compensado pelo desempenho visivelmente mais lento das consultas que gravam dados no banco de dados.

É recomendável criar índices somente quando houver uma clara necessidade deles, como quando o desempenho de um aplicativo começa a diminuir. Ao escolher quais índices criar, considere as consultas que são executadas com mais frequência e levam mais tempo e crie índices com base nas condições de consulta que mais se beneficiarão delas.

Nota: Este guia visa apresentar o tópico de índices de banco de dados no MySQL, ilustrando as aplicações comuns e tipos de índice. O mecanismo de banco de dados suporta muitos cenários mais complexos para usar índices para aumentar o desempenho do banco de dados, que está fora do escopo deste guia. Incentivamos você a consultar a documentação oficial do MySQL sobre índices para uma descrição mais completa dos recursos do banco de dados.

Nas etapas a seguir, você criará índices de diferentes tipos para diversos cenários. Você aprenderá como verificar se os índices são usados em uma consulta. Por fim, você aprenderá a remover índices, se necessário.

Usando índices de coluna única

Um índice de coluna única é o tipo de índice mais comum e direto que você pode usar para otimizar o desempenho da consulta. Esse tipo de índice ajuda o banco de dados a acelerar as consultas que filtram o conjunto de dados com base nos valores de uma única coluna. Os índices criados em uma única coluna podem acelerar muitas consultas condicionais, incluindo correspondências exatas usando o operador = e comparações com os operadores > ou <.

No banco de dados de exemplo que você criou em uma etapa anterior, não há índices. Antes de criar um índice, você primeiro testará como o banco de dados lida com consultas SELECT na tabela employees quando a cláusula WHERE é usada apenas para solicitar um subconjunto de dados da tabela.

Suponha que você queira encontrar funcionários com um salário de exatamente $100.000. Execute a seguinte consulta:

SELECT * FROM employees WHERE salary = 100000;

A cláusula WHERE solicita uma correspondência exata de funcionários com o salário correspondente ao valor solicitado. Neste exemplo, o banco de dados responderá da seguinte forma:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           9 | James      | Brown     | YZA567        | 100000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Observação: conforme mostrado na saída acima, o banco de dados respondeu quase instantaneamente à consulta emitida. Com apenas algumas linhas de amostra no banco de dados, o uso de índices não afetará visivelmente o desempenho da consulta. No entanto, com grandes conjuntos de dados, você observará alterações significativas no tempo de execução da consulta relatado pelo banco de dados após a execução da consulta.

A julgar pela saída da consulta, você não pode saber como o mecanismo de banco de dados abordou o problema de localizar as linhas correspondentes na tabela. No entanto, o MySQL fornece uma maneira de obter informações sobre o plano de consulta, que é como o mecanismo executa a consulta: instruções EXPLAIN.

Para acessar o plano de consulta para a consulta SELECT, execute o seguinte:

EXPLAIN SELECT * FROM employees WHERE salary = 100000;

O comando EXPLAIN diz ao MySQL para executar a consulta SELECT, mas em vez de retornar os resultados, ele mostrará informações sobre como o mecanismo de banco de dados executou a consulta internamente.

O plano de execução será semelhante ao seguinte (sua tabela pode diferir um pouco):

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Nesta saída de tabela, as colunas descrevem muitos aspectos da execução da consulta. Dependendo da sua versão do MySQL, sua saída pode conter colunas adicionais, mas para este tutorial, aqui estão as informações mais importantes:

• possible_keys lista os índices que o MySQL considerou para uso. Neste caso, não há nenhum (NULL).
• chave descreve o índice que o MySQL decidiu usar ao executar a consulta. Nesse caso, nenhum índice foi usado (NULL).
• rows mostra o número de linhas que o MySQL teve que analisar individualmente antes de retornar os resultados. Aqui, é 20, que corresponde ao número de todas as linhas possíveis na tabela. Isso significa que o MySQL teve que verificar cada linha na tabela employees para encontrar a única retornada.
• Extra mostra informações descritivas adicionais sobre o plano de consulta. Neste exemplo, a anotação Using where significa que o banco de dados filtrou os resultados diretamente da tabela usando a instrução WHERE.

Sem índices em vigor, o banco de dados teve que varrer 20 linhas para recuperar uma única. Se a tabela contivesse milhões de linhas, o MySQL teria que percorrê-las uma a uma, resultando em baixo desempenho de consulta.

Nota: Versões mais recentes do MySQL, ao usar EXPLAIN, mostram 1 linha no conjunto, 1 warning na saída, enquanto versões mais antigas do MySQL e MySQL- bancos de dados compatíveis geralmente simplesmente mostram 1 linha no conjunto. O aviso não é um sinal de um problema. O MySQL usa seu mecanismo de avisos para fornecer mais informações estendidas sobre o plano de consulta. O uso dessas informações adicionais está fora do escopo deste tutorial. Você pode aprender mais sobre esse comportamento na página Extended EXPLAIN Output Format na documentação do MySQL.

A consulta SELECT que você acabou de executar usou a condição de consulta exata, WHERE salary=100000. Em seguida, vamos verificar se o banco de dados se comportará de maneira semelhante a uma condição de comparação. Tente recuperar funcionários com salário inferior a 70000:

SELECT * FROM employees WHERE salary < 70000;

Desta vez, o banco de dados retornou duas linhas para John Smith e Jane Doe:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|           2 | Jane       | Doe       | DEF456        |  65000 |
+-------------+------------+-----------+---------------+--------+
8 rows in set (0.000 sec)

No entanto, quando você usa EXPLAIN para entender a execução da consulta da seguinte forma:

EXPLAIN SELECT * FROM employees WHERE salary < 70000;

Você notará que a tabela é quase idêntica à consulta anterior:

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Assim como na consulta anterior, o MySQL escaneou todas as 20 linhas na tabela para encontrar as que você solicitou através da cláusula WHERE na consulta. Embora o número de linhas retornadas seja pequeno em comparação com o número de todas as linhas da tabela, o mecanismo de banco de dados precisa realizar muito trabalho para localizá-las.

Para remediar isso, você pode criar um índice para a coluna salary, que dirá ao MySQL para manter uma estrutura de dados adicional e altamente otimizada, especialmente para os dados salary do < tabelafuncionários. Para isso, execute a seguinte consulta:

CREATE INDEX salary ON employees(salary);

A sintaxe da instrução CREATE INDEX requer:

• O nome do índice, que neste caso é salário. O nome deve ser único dentro de uma única tabela, mas pode ser repetido em diferentes tabelas no mesmo banco de dados.
• O nome da tabela para a qual o índice foi criado. Neste caso, são funcionários.
• A lista de colunas para as quais o índice é criado. Aqui, você está usando uma única coluna chamada salário para criar o índice.

Nota: Dependendo de suas permissões de usuário MySQL, você pode receber um erro ao executar o comando CREATE INDEX: ERROR 1142 (42000): INDEX command negado ao usuário user@host for table Employees. Para conceder permissões INDEX ao seu usuário, efetue login no MySQL como root e execute os seguintes comandos, substituindo o nome de usuário e host do MySQL conforme necessário:

GRANT INDEX on *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;

Depois de atualizar as permissões do usuário, efetue logout como root e efetue login novamente como usuário e execute novamente a instrução CREATE INDEX.

O banco de dados confirmará que o índice foi criado com sucesso:

Output
Query OK, 0 rows affected (0.024 sec)
Records: 0  Duplicates: 0  Warnings: 0

Com o índice definido, tente repetir as consultas anteriores para verificar se algo mudou. Comece recuperando o único funcionário com o salário de exatamente 100000:

SELECT * FROM employees WHERE salary = 100000;

O resultado será o mesmo de antes, com apenas James Brown retornado:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           9 | James      | Brown     | YZA567        | 100000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

No entanto, pedir ao MySQL para explicar como ele abordou a consulta mostrará algumas diferenças de antes. Execute a consulta EXPLAIN da seguinte forma:

EXPLAIN SELECT * FROM employees WHERE salary = 100000;

Desta vez, a saída será impressa assim:

Output
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | salary        | salary | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

O MySQL declara que a partir de uma possível chave mostrada em possible_keys, decidiu usar a chave chamada salary, que é o índice que você criou. A coluna rows agora mostra 1 em vez de 20. Como usava o índice, o banco de dados evitava varrer todas as linhas do banco de dados e podia retornar a única linha solicitada imediatamente. A coluna Extra agora não menciona Using WHERE, porque iterar na tabela principal e verificar cada linha em relação à condição de consulta não era necessário para executar a consulta.

Com um pequeno conjunto de dados de amostra, o impacto do uso do índice não é muito perceptível. Mas o banco de dados deu muito menos trabalho para recuperar o resultado e o efeito dessa alteração seria significativo em um conjunto de dados maior.

Tente executar novamente a segunda consulta, recuperando os funcionários com salário inferior a 70000, para verificar se o índice será usado lá também.

Execute a seguinte consulta:

SELECT * FROM employees WHERE salary < 70000;

As mesmas duas linhas para John Smith e Jane Doe serão retornadas:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|           2 | Jane       | Doe       | DEF456        |  65000 |
+-------------+------------+-----------+---------------+--------+
8 rows in set (0.000 sec)

No entanto, quando você usa EXPLAIN da seguinte forma:

EXPLAIN SELECT * FROM employees WHERE salary < 70000;

A tabela será diferente da execução anterior da mesma consulta:

Output
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | salary        | salary | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

A coluna key informa que o MySQL usou o índice para executar a consulta. Em rows, apenas duas linhas foram analisadas para retornar o resultado. Desta vez, a coluna Extra diz Using index condition, o que significa que neste caso particular, o MySQL filtrou usando o índice e então usou a tabela principal apenas para recuperar o já linhas correspondentes.

Nota: Às vezes, mesmo que um índice esteja presente e possa ser usado, o MySQL decidirá contra isso. Por exemplo, se você executar:

EXPLAIN SELECT * FROM employees WHERE salary < 140000;

O plano de execução terá a seguinte redação:

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | salary        | NULL | NULL    | NULL |   20 |    80.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Mesmo que salary esteja listado em possible_keys, a coluna key vazia (lendo NULL) significa que o MySQL decidiu não use o índice, que pode ser confirmado pelas 20 linhas verificadas. O planejador de consulta de banco de dados analisa cada consulta em relação a índices possíveis para determinar o caminho de execução mais rápido. Se o custo de acessar o índice superar o benefício de usá-lo (por exemplo, se a consulta retornar uma parte significativa dos dados da tabela original), o banco de dados poderá decidir que é realmente mais rápido fazer uma verificação completa da tabela.

Da mesma forma, as anotações na coluna Extra, como Using index condition ou Using where, descrevem como o mecanismo de banco de dados executou a consulta com mais detalhes . Dependendo do contexto, o banco de dados pode escolher outra forma de executar a consulta e você pode ter saídas com a anotação Using index condition ausente ou outra anotação presente. Isso não significa que o índice não seja usado corretamente, mas apenas que o banco de dados decidiu que uma maneira diferente de acessar as linhas terá mais desempenho.

Nesta seção, você criou e usou índices de coluna única para melhorar o desempenho de consultas SELECT baseadas na filtragem de uma única coluna. Na próxima seção, você explorará como os índices podem ser usados para garantir a exclusividade entre os valores em uma determinada coluna.

Usando índices exclusivos para evitar a duplicação de dados

Como você explorou na última seção, um uso comum de índices é recuperar dados com mais eficiência, ajudando o mecanismo de banco de dados a fazer menos trabalho para obter o mesmo resultado. Outra finalidade é garantir que os dados na parte da tabela em que o índice está definido não sejam repetidos. Isso é o que um índice exclusivo faz.

Muitas vezes, evitar valores duplicados é necessário para garantir a integridade dos dados, seja do ponto de vista lógico ou técnico. Por exemplo, não deve haver duas pessoas diferentes usando o mesmo CPF ou um sistema online não deve permitir o registro de vários usuários com o mesmo nome de usuário ou endereço de e-mail.

No caso do exemplo da tabela employees neste guia, o número de série do dispositivo atribuído é um campo que não deve conter duplicatas. Se tivesse, isso significaria que dois funcionários receberam o mesmo computador. Neste ponto, no entanto, você pode facilmente inserir novos funcionários com números de série repetidos.

Tente inserir outro funcionário com um número de série de dispositivo que já esteja em uso:

INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);

O banco de dados irá obrigar e inserir a linha, notificando você do sucesso:

Output
Query OK, 1 row affected (0.009 sec)

No entanto, se você agora consultar o banco de dados em busca de funcionários usando o computador ABCD123 assim:

SELECT * FROM employees WHERE device_serial = 'ABC123';

Você terá duas pessoas diferentes como resultado:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
|          21 | Sammy      | Smith     | ABC123        |  65000 |
+-------------+------------+-----------+---------------+--------+
2 rows in set (0.000 sec)

Este não é um comportamento esperado para manter o banco de dados funcionários válido. Vamos reverter essa alteração excluindo a linha recém-criada:

DELETE FROM employees WHERE employee_id = 21;

Você pode confirmar isso executando novamente a consulta SELECT anterior:

SELECT * FROM employees WHERE device_serial = 'ABC123';

Mais uma vez, apenas John Smith usa o dispositivo com o número de série ABC123:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Para proteger o banco de dados contra tais erros, você pode criar um índice exclusivo na coluna device_serial.

Para isso, execute:

CREATE UNIQUE INDEX device_serial ON employees(device_serial);

Adicionar a palavra-chave UNIQUE ao criar o índice instrui o banco de dados a garantir que os valores na coluna device_serial não possam ser repetidos. Com índices exclusivos, todas as novas linhas adicionadas à tabela serão verificadas em relação ao índice para determinar se o valor da coluna atende à restrição.

O banco de dados confirmará a criação do índice:

Output
Query OK, 0 rows affected (0.021 sec)
Records: 0  Duplicates: 0  Warnings: 0

Agora, verifique se ainda é possível adicionar uma entrada duplicada à tabela. Tente executar a consulta INSERT bem-sucedida anteriormente novamente:

INSERT INTO employees VALUES (21, 'Sammy', 'Smith', 'ABC123', 65000);

Desta vez, a mensagem de erro será exibida:

Output
ERROR 1062 (23000): Duplicate entry 'ABC123' for key 'device_serial'

Você pode verificar se a nova linha não foi adicionada à tabela usando a consulta SELECT novamente:

SELECT * FROM employees WHERE device_serial = 'ABC123';

Uma única linha agora é retornada:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Índices exclusivos, além de proteger contra entradas duplicadas, também são índices totalmente funcionais para acelerar as consultas. O mecanismo de banco de dados usará índices exclusivos da mesma maneira que na etapa anterior. Você pode verificar isso executando:

EXPLAIN SELECT * FROM employees WHERE device_serial = 'ABC123';

O plano de execução será semelhante ao seguinte (sua tabela pode diferir um pouco):

Output
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | const | device_serial | device_serial | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

O índice device_serial é mostrado em possible_keys e na coluna key, confirmando que o índice foi usado ao realizar a consulta.

Você usou índices exclusivos para proteger contra dados duplicados no banco de dados. Na próxima seção, você usará índices abrangendo mais de uma coluna.

Usando índices em várias colunas

Até agora, todos os índices que você criou nas seções anteriores foram definidos usando um único nome de coluna, referente aos valores da coluna escolhida. A maioria dos sistemas de banco de dados oferece suporte a índices que abrangem mais de uma coluna. Esses índices, chamados de índices de várias colunas, fornecem uma maneira de armazenar valores para várias colunas em um único índice, permitindo que o mecanismo de banco de dados execute consultas com mais rapidez e eficiência usando o conjunto de colunas juntas.

As consultas usadas com frequência que devem ser otimizadas para desempenho geralmente usam várias condições na cláusula de filtragem WHERE. Um exemplo desse tipo de consulta seria pedir ao banco de dados para localizar uma pessoa tanto pelo nome quanto pelo sobrenome:

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

O primeiro pensamento para otimizar esta consulta com índices pode ser criar dois índices individuais, um na coluna last_name e outro na coluna first_name. No entanto, esta não é a melhor escolha para esta situação.

Se você criasse dois índices separados dessa forma, o MySQL saberia como encontrar todos os funcionários chamados Smith. Ele também saberia como encontrar todos os funcionários chamados John. No entanto, não saberia como encontrar pessoas chamadas John Smith.

Para ilustrar o problema de ter dois índices individuais, imagine ter duas listas telefônicas separadas, uma organizada por sobrenomes e outra por nomes próprios. Ambos os catálogos telefônicos se assemelham a índices criados nas colunas last_name e first_name, respectivamente. Como usuário de um catálogo telefônico, você pode abordar o problema de encontrar John Smith de três maneiras possíveis:

• Use a lista telefônica ordenada por sobrenomes para encontrar todas as pessoas chamadas Smith, ignore a segunda lista telefônica e percorra manualmente todas as pessoas Smith uma a uma até encontrar John Smith.
• Faça o oposto: use a lista telefônica ordenada por nomes para encontrar todas as pessoas chamadas John, ignore a segunda lista telefônica e percorra manualmente todas as pessoas John, uma por um até encontrar John Smith.
• Tente usar os dois catálogos telefônicos juntos: encontre todas as pessoas chamadas John e separadamente todas as pessoas chamadas Smith, anote os resultados provisórios e tente fazer a intersecção manual dos dois subconjuntos de dados que procuram pessoas que estão em ambas as listas individuais.

Nenhuma dessas abordagens é ideal, e o MySQL tem opções semelhantes disponíveis ao lidar com vários índices desarticulados e uma consulta solicitando mais de uma condição de filtragem.

Outra abordagem seria usar índices que levam em conta não uma única coluna, mas muitas colunas. Você pode imaginar isso como uma lista telefônica colocada dentro de outra lista telefônica: primeiro você procura o sobrenome Smith, levando você ao segundo catálogo de todas as pessoas chamadas Smith organizadas alfabeticamente pelos primeiros nomes, que você pode usar para encontrar rapidamente John.

Nota: Costuma-se dizer que o MySQL pode usar apenas um índice para cada tabela usada em uma consulta. Nem sempre é verdade, pois o MySQL suporta otimizações de mesclagem de índice para usar vários índices juntos ao executar uma consulta. No entanto, essa limitação é uma boa regra ao criar índices. MySQL pode decidir não usar múltiplos índices; mesmo que isso aconteça, em muitos cenários, eles não servirão ao propósito tão bem quanto um índice dedicado.

No MySQL, para criar um índice de várias colunas para sobrenomes e nomes na tabela employees, execute:

CREATE INDEX names ON employees(last_name, first_name);

Nesse caso, a instrução CREATE INDEX difere um pouco. Agora, nos parênteses após o nome da tabela (employees), duas colunas são listadas: last_name e first_name. Isso cria um índice de várias colunas em ambas as colunas. A ordem em que as colunas são listadas na definição do índice é importante, como você descobrirá em breve.

O banco de dados mostrará a seguinte mensagem confirmando que criou o índice com sucesso:

Output
Query OK, 0 rows affected (0.024 sec)
Records: 0  Duplicates: 0  Warnings: 0

Agora, tente executar a consulta SELECT para localizar linhas com o primeiro nome correspondente a John e o sobrenome correspondente a Smith:

SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

O resultado é uma única linha com um funcionário chamado John Smith:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Agora use a consulta EXPLAIN para verificar se o índice foi usado:

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

O plano de execução será semelhante ao seguinte (sua tabela pode diferir um pouco):

Output
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | names         | names | 406     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

O banco de dados usou o índice names. Uma única linha foi escaneada, então a tabela não foi percorrida mais do que o necessário. A coluna Extra diz Using index condition, o que significa que o MySQL pode concluir a filtragem usando apenas o índice.

Filtrar nomes e sobrenomes usando o índice de várias colunas que abrange essas duas colunas fornece ao banco de dados uma maneira direta e rápida de encontrar os resultados desejados.

Com o índice definido em ambas as colunas, o que acontecerá se você tentar localizar todos os funcionários chamados Smith, mas não filtrar pelo primeiro nome? Execute a consulta modificada:

SELECT * FROM employees WHERE last_name = 'Smith';

A saída retornará o seguinte:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|          20 | Abigail    | Smith     | FGH890        | 155000 |
|          17 | Daniel     | Smith     | WXY901        | 140000 |
|           1 | John       | Smith     | ABC123        |  60000 |
|           5 | Michael    | Smith     | MNO345        |  80000 |
+-------------+------------+-----------+---------------+--------+
4 rows in set (0.000 sec)

Quatro funcionários têm o sobrenome Smith.

Mais uma vez, acesse o plano de execução da consulta:

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

O plano de execução será semelhante ao seguinte (sua tabela pode diferir um pouco):

Output
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | names         | names | 203     | const |    4 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Dessa vez foram retornadas quatro linhas, pois há mais de um funcionário com esse sobrenome. No entanto, a tabela do plano de execução mostra que o banco de dados usou o índice multicoluna names para executar esta consulta, verificando apenas 4 linhas - o número exato retornado.

Nas consultas anteriores, a coluna usada para filtrar os resultados (last_name) foi passada primeiro na instrução CREATE INDEX. Agora você filtrará a tabela employees por first_name, que era a segunda coluna na lista de colunas para esse índice de várias colunas. Execute a seguinte consulta:

SELECT * FROM employees WHERE first_name = 'John';

A saída retornará da seguinte forma:

Output
+-------------+------------+-----------+---------------+--------+
| employee_id | first_name | last_name | device_serial | salary |
+-------------+------------+-----------+---------------+--------+
|           1 | John       | Smith     | ABC123        |  60000 |
+-------------+------------+-----------+---------------+--------+
1 row in set (0.000 sec)

Acesse o plano de execução da consulta:

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

A saída retornará da seguinte forma:

Output
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Mais uma vez, os resultados retornados contêm um único funcionário, mas, desta vez, nenhum índice foi usado. O banco de dados escaneou toda a tabela, ilustrado pela anotação Using where na coluna Extra, bem como 20 linhas escaneadas.

Nesse caso, o banco de dados não usou o índice devido à ordem das colunas passadas para a instrução CREATE INDEX quando o índice foi criado pela primeira vez: last_name, first_name. O banco de dados só pode usar o índice se a consulta usar a primeira coluna ou a primeira e a segunda colunas; ele não pode oferecer suporte a consultas no índice em que a primeira coluna da definição do índice não é usada.

Com um índice criado em várias colunas, o banco de dados pode usar o índice para acelerar as consultas envolvendo todas as colunas indexadas ou um crescente prefixo à esquerda de todas as colunas indexadas. Por exemplo, um índice de várias colunas que inclui as colunas (a, b, c) pode ser usado para acelerar as consultas que envolvem todas as três colunas e as consultas que envolvem apenas as duas primeiras colunas, ou mesmo consultas que envolvem apenas a primeira coluna. Por outro lado, o índice não ajudará em consultas envolvendo apenas a última coluna, c, ou as duas últimas colunas, b e c.

Ao escolher cuidadosamente as colunas incluídas no índice e sua ordem, um único índice de várias colunas pode ser usado para acelerar várias consultas na mesma tabela. Neste exemplo, se assumirmos que a pesquisa de funcionários ocorre tanto pelo nome quanto pelo sobrenome ou apenas pelo sobrenome, a ordem fornecida das colunas no índice names garante que o índice agilizará todas as consultas relevantes.

Nesta seção, você usou índices de várias colunas e aprendeu sobre a ordem das colunas ao especificar tal índice. Na próxima seção, você aprenderá como gerenciar índices existentes.

Listando e removendo índices existentes

Nas seções anteriores, você criou novos índices. Como os índices têm nomes e são definidos em tabelas específicas, você também pode listá-los e manipulá-los quando necessário.

Para listar todos os índices que você criou neste tutorial para a tabela employees, execute a seguinte instrução:

SHOW INDEXES FROM employees;

A saída será semelhante à seguinte:

Output
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees |          0 | device_serial |            1 | device_serial | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | salary        |            1 | salary        | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | names         |            1 | last_name     | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employees |          1 | names         |            2 | first_name    | A         |          20 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-----------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)

Dependendo da sua versão do MySQL, sua saída pode diferir um pouco, mas incluirá todos os índices, incluindo seus nomes, colunas usadas para definir o índice, informações sobre sua exclusividade e outros detalhes extensos da definição do índice.

Para excluir índices existentes, você pode usar a instrução SQL DROP INDEX. Imagine que você não deseja mais impor exclusividade na coluna device_serial. Assim, o índice device_serial não será mais necessário. Execute o seguinte comando:

DROP INDEX device_serial ON employees;

device_serial é o nome do índice e employees é a tabela na qual o índice foi definido. O banco de dados confirmará a exclusão do índice:

Output
Query OK, 0 rows affected (0.018 sec)
Records: 0  Duplicates: 0  Warnings: 0

Às vezes, os padrões de consultas típicas mudam com o tempo ou novos tipos de consulta se tornam proeminentes. Em seguida, pode ser necessário reavaliar os índices usados, criar novos ou excluir os não usados para evitar a degradação do desempenho do banco de dados, mantendo-os atualizados.

Usando os comandos DROP INDEX, você pode gerenciar índices em um banco de dados existente, seguindo as melhores práticas para criar índices quando eles se tornarem necessários e benéficos.

Conclusão

Seguindo este guia, você aprendeu o que são índices e como usar os tipos mais comuns no MySQL para acelerar a recuperação de dados por meio de consultas SELECT condicionais. Você usou índices para manter a exclusividade dos dados da coluna e aprendeu como os índices podem afetar as consultas em que mais de uma coluna é usada na condição de filtragem.

Você pode usar índices para moldar o desempenho do banco de dados de acordo com o tipo de consultas executadas com mais frequência, atingindo o equilíbrio certo entre o desempenho de leitura e gravação para casos de uso comuns. Este tutorial cobriu apenas o básico do uso de índices para essa finalidade. Você pode oferecer suporte a consultas mais complexas por meio de índices, entendendo como o MySQL escolhe quais índices usar e quando usá-los. Para saber mais sobre isso, consulte a documentação do MySQL sobre índices.

Se você quiser aprender mais sobre os diferentes conceitos da linguagem SQL e como trabalhar com ela, recomendamos que verifique os outros guias da série Como usar o SQL.