MySQL Índice: Tutorial de Criar, Adicionar e Soltar

Por: Marcus Allen Marcus Allen
Hours Atualização do

⚡ Resumo Inteligente

MySQL O tutorial sobre índices aborda como os índices classificam e localizam dados rapidamente. Um índice é uma estrutura de pesquisa ordenada criada em uma ou mais colunas; o comando CREATE INDEX o adiciona, o comando SHOW INDEXES o inspeciona e o comando DROP INDEX o remove quando as tabelas com alta frequência de escrita superam os benefícios da leitura.

  • 📚 Trate os índices como um dicionário: Eles classificam os valores das colunas para que o mecanismo possa localizar as linhas sem precisar examinar a tabela inteira.
  • 🛠️ Criar na mesa ou posteriormente: Defina um índice diretamente no comando CREATE TABLE ou adicione-o posteriormente com CREATE INDEX em uma tabela ativa.
  • 🔍 Inspecionar com MOSTRAR ÍNDICES: Use o comando SHOW INDEXES FROM nome_da_tabela para listar todos os índices, partes da chave, cardinalidade e indicadores de unicidade.
  • 🧹 Eliminar quando o custo de escrita for muito alto: Índices tornam as operações de INSERT e UPDATE mais lentas — remova os índices não utilizados com DROP INDEX para recuperar a taxa de transferência de escrita.
  • 🤖 Utilize IA para o design de índices: Assistentes de IA leem registros de consultas lentas, sugerem ordens de colunas para índices compostos e explicam planos EXPLAIN linha por linha.
Saiba Mais

MySQL Conceito de índice

O que é uma MySQL Índice?

An índice in MySQL Um índice é uma estrutura de dados que armazena valores de colunas de forma ordenada, permitindo que o mecanismo de busca encontre as linhas rapidamente. Os índices são criados na(s) coluna(s) mais frequentemente usada(s) para filtrar dados. Pense em um índice como uma lista ordenada alfabeticamente: é muito mais rápido encontrar um nome em uma lista ordenada do que em uma lista desordenada.

Os índices apresentam uma desvantagem: cada operação de INSERT ou UPDATE precisa manter o índice, portanto, adicionar muitos índices em uma tabela com alta frequência de escrita pode prejudicar o desempenho geral. Como regra geral, indexe as colunas que aparecem nas cláusulas WHERE, JOIN e ORDER BY em tabelas que são lidas com mais frequência do que escritas.

Por que usar um índice?

Ninguém gosta de sistemas lentos. Alto desempenho é uma preocupação primordial para quase todos os aplicativos baseados em banco de dados. As empresas investem pesado em hardware para manter as consultas rápidas, mas existe um limite para o que o hardware sozinho pode oferecer. Otimizar índices é uma alternativa mais barata e eficaz.

MySQL Conceito de índice

Tempos de resposta lentos geralmente ocorrem porque as linhas são armazenadas em ordem física no disco. Sem um índice, MySQL É necessário examinar cada linha para encontrar aquelas que correspondem a um predicado — uma “varredura completa da tabela”. Os índices permitem MySQL pular diretamente para as linhas correspondentes, o que transforma o plano de consulta de O(n) para aproximadamente O(log n) para pesquisas em árvores B.

Sintaxe: Criar Índice

Um índice pode ser definido em dois lugares:

  1. No momento da criação da tabela.
  2. Após a tabela já existir.

Exemplo: Criar um índice em linha com CREATE TABLE

Para o myflixdb No banco de dados, esperamos muitas buscas na coluna de nome completo. O script abaixo cria uma nova members_indexed tabela com um índice no full_names coluna.

CREATE TABLE `members_indexed` (
    `membership_number` INT(11) NOT NULL AUTO_INCREMENT,
    `full_names`        VARCHAR(150) DEFAULT NULL,
    `gender`            VARCHAR(6)   DEFAULT NULL,
    `date_of_birth`     DATE         DEFAULT NULL,
    `physical_address`  VARCHAR(255) DEFAULT NULL,
    `postal_address`    VARCHAR(255) DEFAULT NULL,
    `contact_number`    VARCHAR(75)  DEFAULT NULL,
    `email`             VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`membership_number`),
    INDEX (`full_names`)
) ENGINE = InnoDB;

Execute o script em MySQL Bancada de trabalho contra o myflixdb base de dados.

tabela members_indexed em MySQL Workbench

revisar myflixdb para ver o novo members_indexed tabela. O full_names A coluna agora aparece abaixo do Índices nó.

Com o aumento do número de membros, as pesquisas sobre members_indexed que usam WHERE e ORDER BY contra full_names são muito mais rápidas do que as mesmas consultas no original. members tabela sem o índice.

ARTIGOS RELACIONADOS

Adicione um índice após a tabela já existir.

Você frequentemente descobrirá que uma tabela existente precisa de um índice — as consultas de pesquisa são lentas e um plano EXPLAIN mostra uma varredura completa da tabela em uma coluna que aparece na cláusula WHERE. CREATE INDEX A instrução adiciona um índice sem recriar a tabela.

CREATE INDEX `id_index` ON `table_name` (`column_name`);

Exemplo concreto — acelerar as buscas no title coluna do movies tabela:

CREATE INDEX `title_index` ON `movies` (`title`);

Toda consulta que filtra por movies.title agora está respaldada pelo novo índice. Consultas que filtram por outras colunas ainda percorrem a tabela, a menos que tenham seu próprio índice.

Observação: Você pode criar um índice composto em várias colunas quando suas consultas sempre filtram ou classificam pela mesma combinação. A ordem importa — a coluna principal determina se o índice pode ser usado.

Listar índices em uma tabela

Uso SHOW INDEXES Para visualizar todos os índices definidos em uma tabela.

SHOW INDEXES FROM `table_name`;

Exemplo — liste os índices do movies tabela:

SHOW INDEXES FROM `movies`;

Execute a instrução em MySQL Workbench contra myflixdb Para visualizar os índices existentes e as colunas que eles abrangem.

Observação: As chaves primárias e estrangeiras são indexadas automaticamente por MySQLCada índice possui um nome único e lista as colunas que abrange.

Sintaxe: Remover Índice

Uso DROP INDEX Remover um índice existente de uma tabela. Isso é útil quando uma tabela com muitas gravações está sendo prejudicada por um índice que não está mais sendo útil nas leituras.

DROP INDEX `index_id` ON `table_name`;

Exemplo concreto — solte o full_names índice de members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

Tipos de MySQL Índices

MySQL Suporta diversos tipos de índice, cada um adequado a uma carga de trabalho diferente.

Formato Propósito
CHAVE PRIMÁRIA Identificador de linha único; agrupado com os dados da tabela no InnoDB.
UNIQUE Garante a singularidade e, ao mesmo tempo, serve como índice.
ÍNDICE (árvore B) Índice secundário padrão usado para consultas de intervalo e pesquisas de igualdade.
TEXTO COMPLETO Otimizado para pesquisa de texto em linguagem natural com CORRESPONDÊNCIA... CONTRA.
ESPACIAL Índice R-tree para tipos de dados GIS, como PONTO e POLÍGONO.
JOGO DA VELHA Consultas de igualdade em tempo constante; usadas pelo mecanismo de armazenamento MEMORY.
Composto (multicoluna) Combina várias colunas em um único índice; respeita a regra do prefixo mais à esquerda.

Melhores Práticas para MySQL Índices

Os hábitos abaixo mantêm os índices úteis e evitam que se tornem um peso morto.

  • Índice para o padrão de consulta, não para o nome da coluna: Adicione índices que correspondam às cláusulas WHERE, JOIN e ORDER BY reais, e não a "todas as colunas que parecem importantes".
  • Ordem de índice composto do Watch: A coluna principal deve aparecer na consulta para que o índice seja utilizado.
  • Evite índices duplicados: Um prefixo inicial de um índice composto já abrange as pesquisas de coluna única nesse prefixo.
  • Inspecionar com EXPLICAR: Confirme se o planejador está realmente selecionando o novo índice.
  • Remover índices não utilizados: usar sys.schema_unused_indexes in MySQL 5.7+ para encontrar índices que nada lê.
  • Tipos de dados correspondentes: Se uma cláusula WHERE comparar uma coluna VARCHAR a um número, o índice não poderá ser usado devido a uma conversão implícita.

Perguntas Frequentes

Uma chave primária identifica exclusivamente cada linha e está sempre indexada. Um índice geral acelera as buscas, mas permite valores duplicados. Toda chave primária é um índice, mas nem todo índice é uma chave primária.

Evite índices em tabelas muito pequenas, em colunas com poucos valores distintos (baixa cardinalidade) e em tabelas que são escritas com muito mais frequência do que lidas. Cada índice extra torna mais lentas todas as operações de INSERT, UPDATE e DELETE.

Um índice composto (multicoluna) abrange mais de uma coluna em um único índice. Ele respeita a regra do prefixo mais à esquerda, portanto, pode atender a consultas que filtram pela primeira coluna, pelas duas primeiras colunas e assim por diante, mas não apenas pela segunda coluna.

Execute EXPLAIN em frente à instrução SELECT. chave A coluna mostra qual índice o otimizador escolheu, enquanto tipo e linhas Informa se o caminho de acesso é eficiente.

Um índice de cobertura contém todas as colunas necessárias para a consulta, portanto, o mecanismo responde à consulta usando apenas o índice, sem precisar ler a tabela. O comando EXPLAIN exibe a mensagem "Usando índice" quando isso acontece.

Os motivos comuns incluem envoltórioping a coluna em uma função (WHERE YEAR(col) = …), conversões de tipo implícitas, cardinalidade muito baixa e estatísticas desatualizadas. Executar ANALYZE TABLE Para atualizar as estatísticas e inspecionar EXPLAIN pelo verdadeiro motivo.

Os assistentes de IA processam logs de consultas lentas, classificam os padrões mais custosos, sugerem índices de coluna única ou compostos e explicam os planos EXPLAIN em linguagem simples. Eles reduzem o tempo de otimização de horas para minutos em cargas de trabalho rotineiras.

Sim. As ferramentas de IA transformam uma solicitação como "acelerar as buscas de clientes por e-mail e data de cadastro" em uma instrução CREATE INDEX funcional, recomendam a ordem das colunas e explicam o impacto esperado na taxa de transferência de leitura e gravação.

Resuma esta postagem com: