O índice no MySQL

Uma tela pode ficar lenta porque o índice que atendia às suas buscas deixou de existir no servidor, e o sistema continua funcionando sem nenhuma mensagem de erro. Os índices fazem parte da definição de cada tabela no MySQL. Conferir quais existem, com quais colunas e em qual ordem é o primeiro passo antes de atribuir uma lentidão a qualquer outra causa.

Os tipos de índice

• Chave primária: o índice da chave primária, com o nome PRIMARY, não aceita valores repetidos nem a ausência de valor. No InnoDB, é esse índice que organiza as próprias linhas da tabela.
• Índice de valores únicos: declarado com a palavra UNIQUE, recusa valores repetidos com o erro 1062, de entrada duplicada. Esse índice aceita várias linhas com NULL, porque o NULL não é igual a nenhum valor.
• Índice comum: declarado com a palavra INDEX ou KEY, aceita valores repetidos e serve para acelerar as buscas e as ordens.

Como ver os índices

• SHOW INDEX FROM produtos;: lista os índices da tabela, com uma linha para cada coluna de cada índice;
• SHOW CREATE TABLE produtos;: mostra os índices dentro da definição completa da tabela;
• SELECT TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'loja' ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;: lista os índices de todas as tabelas do banco loja.

No resultado do SHOW INDEX, algumas colunas merecem atenção:

• Key_name: o nome do índice.
• Seq_in_index: a posição da coluna dentro do índice, a partir de 1.
• Column_name: a coluna que ocupa aquela posição.
• Non_unique: o valor 0 indica um índice de valores únicos, e o valor 1 indica um índice que aceita repetições.
• Cardinality: uma estimativa da quantidade de valores diferentes no índice.
• Collation: a ordem da coluna dentro do índice, com a letra A para a ordem crescente. Apesar do nome, essa coluna não tem relação com a collation dos textos.
• Null: a palavra YES indica que a coluna aceita a ausência de valor.
• Index_type: a estrutura do índice, BTREE nos índices comuns.
• Comment: em uma tabela MyISAM, a palavra disabled indica um índice desativado.

Um índice com duas colunas aparece em duas linhas, com o mesmo Key_name e as posições 1 e 2. A ordem dessas posições é a ordem do índice, e é ela que define quais buscas o índice atende.

No InnoDB, a cardinalidade é calculada por amostragem e serve ao otimizador. Ela não é uma contagem exata, e duas tabelas com a mesma definição podem mostrar valores diferentes, conforme os dados de cada uma. No MyISAM, a cardinalidade vem da última análise da tabela, como a do ANALYZE TABLE, e não acompanha as gravações seguintes, e os índices comuns de uma tabela que nunca foi analisada mostram o valor NULL.

Os índices secundários do InnoDB

No InnoDB, as linhas ficam organizadas pela chave primária. Os outros índices, chamados de índices secundários, guardam os valores das próprias colunas e, junto com eles, a chave primária de cada linha. Uma busca por um índice secundário encontra primeiro a chave primária e, depois, a linha completa no índice da chave primária.

Esse arranjo tem dois efeitos práticos:

• um índice na coluna codigo já contém o id de cada linha, e o MySQL consegue usá-lo para a ordem por código e identificador;
• uma consulta que pede apenas colunas presentes no índice dispensa a segunda busca e fica mais rápida.

O índice ausente

Quando o índice que atende a uma busca não existe, o MySQL ainda devolve o resultado correto por outro caminho, com mais leitura e, muitas vezes, com uma etapa extra de ordenação. O sintoma é a lentidão, e não um erro. As causas mais comuns são estas:

• a tabela foi recriada com CREATE TABLE ... SELECT, que, na forma simples, copia as colunas e os dados, mas não cria nenhum índice, nem a chave primária;
• um índice foi apagado ou recriado com outras colunas por uma manutenção feita fora da aplicação;
• a tabela foi restaurada de uma cópia anterior à criação do índice.

O comando CREATE TABLE ... LIKE, ao contrário, copia a estrutura da tabela, com os índices, mas sem os dados.

Criar um índice

A criação de um índice muda a estrutura da tabela:

• CREATE INDEX idx_vendas_caixa_data ON vendas (caixa, data_venda);: cria um índice com as colunas caixa e data_venda, nessa ordem.

Em uma tabela grande, a criação leva tempo e ocupa espaço em disco. A partir do MySQL 5.6, o InnoDB cria a maioria dos índices sem impedir as gravações na tabela, mas a operação consome recursos do servidor durante todo o processo. No MyISAM, em qualquer versão, e no InnoDB das versões anteriores, a criação impede as gravações na tabela durante toda a operação e permite apenas as leituras. Um índice de valores únicos não é criado quando a coluna já tem valores repetidos, e o MySQL recusa a criação com o erro 1062.

Em sistemas que mantêm as ordens de navegação na aplicação, a criação e a exclusão de índices passam pela rotina da aplicação, porque a aplicação precisa conhecer cada índice que usa. Um índice criado por fora fica desconhecido para a aplicação e para as rotinas de manutenção dela.