**Índice** é uma estrutura de dados especial no banco de dados usada para ajudar o sistema de gerenciamento de banco de dados (SGBD) a acessar rapidamente informações específicas na tabela de dados. Um índice é semelhante ao índice de um livro e pode acelerar a recuperação de dados.
2. O papel do índice
Melhore a eficiência da consulta: Por meio de índices, o sistema de banco de dados pode localizar rapidamente os dados na tabela, reduzindo a necessidade de varreduras completas da tabela.
Otimize a classificação e agrupamento de dados: nas operações de classificação e agrupamento, os índices podem reduzir significativamente o número de comparações e movimentações de dados.
Garanta a exclusividade dos dados: ao criar um índice exclusivo ou um índice de chave primária, você pode garantir a exclusividade dos dados na tabela.
Suporta junções de tabelas eficientes: em consultas que envolvem múltiplas tabelas, os índices podem acelerar o processo de junção entre tabelas.
3. Classificação dos índices
Os índices SQL podem ser classificados de acordo com diferentes padrões. Os métodos de classificação comuns incluem:
Classificado por estrutura de armazenamento：
Índice de árvore B: Incluindo B-Tree e B+Tree, etc., que são os tipos de índice mais comumente usados em bancos de dados.
Índice de hash: Calcule o valor hash da coluna de índice por meio da função hash para localizar rapidamente os dados.
Índice de texto completo: usado para pesquisar conteúdo de texto e oferece suporte a sintaxe de consulta complexa.
índice espacial: Usado para armazenar dados geoespaciais, como dados de coordenadas em sistemas de informações geográficas (GIS).
Classificado por função：
Índice comum: O tipo de índice mais básico sem quaisquer restrições.
índice único: requer que o valor da coluna do índice seja exclusivo.
índice de chave primária: um índice exclusivo especial usado para identificar exclusivamente cada linha de dados na tabela.
índice composto: um índice que contém várias colunas, usado para melhorar o desempenho de consultas com várias colunas.
4. Vantagens e Desvantagens da Indexação
vantagem：
Melhore a eficiência da consulta.
Otimize a classificação e agrupamento de dados.
Suporta junções de tabelas eficientes.
Garanta a exclusividade dos dados.
deficiência：
Aumento dos custos de E/S: Os arquivos de índice ocupam espaço adicional em disco e podem aumentar o número de operações de E/S em disco.
Desempenho reduzido das operações de gravação: Ao inserir, atualizar ou excluir dados, o índice também precisa ser atualizado, o que pode reduzir o desempenho das operações de gravação.
Muitos índices podem levar à degradação do desempenho: Índices inadequados ou muitos índices podem reduzir o desempenho geral do banco de dados.
5. Cenários de uso do índice
As decisões sobre a utilização de índices devem basear-se nas necessidades específicas do negócio e nas características dos dados. Aqui estão alguns cenários de uso comuns:
Colunas que aparecem frequentemente nas condições de consulta.
Colunas que participam de operações de classificação ou agrupamento.
Colunas que frequentemente participam de junções de tabelas.
Colunas com altos requisitos de exclusividade, como ID do usuário, endereço de e-mail etc.
6. Criação e manutenção de índices
Criar índice：
pode usarCREATE INDEX instrução cria um índice no banco de dados. Por exemplo:
CREATEINDEX idx_name ON table_name (column_name);
1
Excluir índice：
Quando o índice não for mais necessário, você poderá usarDROP INDEX declaração para excluí-lo. Por exemplo:
DROPINDEX idx_name ON table_name;
1
Manter índice：
Reconstrua o índice regularmente: à medida que os dados são adicionados e modificados, o índice pode ficar fragmentado e a reconstrução periódica do índice pode restaurar seu desempenho.
Monitore o uso do índice: monitore o uso de índices por meio de ferramentas de gerenciamento de banco de dados ou instruções SQL e descubra e resolva prontamente problemas relacionados ao índice.
7. Melhores práticas para indexação
Crie índices apenas para colunas obrigatórias: evite criar índices para colunas usadas com pouca frequência ou que tenham altas taxas de repetição.
Considere o uso de índices compostos: quando as condições de consulta envolvem diversas colunas, considere criar um índice composto para melhorar o desempenho da consulta.
Evite muitos índices: Embora os índices possam melhorar o desempenho da consulta, muitos índices reduzirão o desempenho das operações de gravação e aumentarão o consumo de espaço de armazenamento.
Usando varreduras de cobertura de índice: Tente fazer com que a consulta obtenha os dados necessários apenas através do índice e evite consultar novamente a tabela.
Princípios básicos de indexação
estrutura de dados : Os índices geralmente usam uma estrutura de dados para armazenar dados, como árvore B (a mais comum é a árvore B+), tabela hash, etc. Essas estruturas de dados permitem que os sistemas de banco de dados encontrem, insiram, excluam e atualizem dados mais rapidamente do que varreduras completas de tabelas.
Classificar e armazenar : Ao criar um índice, o banco de dados classifica os dados com base nos valores das colunas do índice e armazena esses valores na estrutura do índice. Para estruturas de árvore balanceadas, como árvores B, esse método de classificação e armazenamento hierárquico pode garantir a eficiência da consulta.
par de valores-chave : os índices são normalmente armazenados como pares de valores-chave, onde a chave é o valor da coluna do índice e o valor é um ponteiro ou número de linha para a linha correspondente na tabela. Dessa forma, quando uma consulta utiliza a coluna de índice, o banco de dados pode localizar rapidamente a localização física dos dados.
O papel do índice
Acelere a recuperação de dados : A principal função do índice é acelerar a recuperação de dados. Por meio de índices, o banco de dados pode localizar rapidamente o local de armazenamento dos dados sem verificar a tabela inteira. Isto é especialmente importante para tabelas com grandes volumes de dados.
Reduza os custos de E/S : o gargalo de desempenho das operações de banco de dados (especialmente operações de consulta) geralmente está na E/S do disco. Os índices podem reduzir significativamente a quantidade de dados que precisam ser lidos durante as consultas, reduzindo assim os custos de E/S.
Suporta classificação e agrupamento: com índices, o banco de dados pode realizar operações de classificação e agrupamento com mais eficiência porque o próprio índice já classifica os dados.
Obtenha exclusividade de dados: um índice exclusivo garante que o valor da coluna indexada seja exclusivo na tabela, o que ajuda a manter a integridade e a consistência dos dados.
Otimizar consultas de junção: em consultas de junção envolvendo múltiplas tabelas, os índices podem reduzir significativamente o número de linhas que precisam ser comparadas e correspondidas durante o processo de junção, melhorando assim a eficiência da consulta.
Precauções
Embora os índices possam melhorar significativamente o desempenho da consulta, eles têm suas limitações:
Custos de manutenção do índice: O próprio índice precisa ocupar espaço de armazenamento adicional e, quando os dados são inseridos, atualizados e excluídos, o índice também precisa ser atualizado adequadamente, o que aumentará os custos adicionais de manutenção.
otimizador de consulta : o otimizador de consulta de banco de dados escolherá automaticamente se deseja usar um índice e qual índice usar com base em fatores como condições de consulta e estrutura da tabela. Portanto, nem todas as consultas utilizarão o índice.
seleção de índice : ao projetar um índice, você precisa selecionar cuidadosamente as colunas e os tipos de índice para equilibrar o desempenho da consulta e os custos de manutenção. Muitos índices podem reduzir o desempenho das atualizações de dados e aumentar os custos de armazenamento.
Diferentes tipos de índices possuem características próprias e cenários aplicáveis no banco de dados. A seguir está uma análise detalhada das características e cenários aplicáveis de vários tipos de índices comuns:
1. Índice comum
Características：
O tipo de índice mais básico sem restrições de exclusividade.
Valores NULL são permitidos.
Não pode ser referenciado como chave estrangeira.
Uma tabela pode ter vários índices comuns.
Cena aplicável：
É usado para acelerar o acesso e recuperação de dados na tabela, especialmente ao criar índices comuns em colunas com grandes quantidades de dados e consultadas com frequência, o que pode melhorar significativamente a eficiência da consulta.
Índices comuns podem ser usados quando os requisitos de exclusividade não estão envolvidos nas condições de consulta.
2. Índice único
Características：
As colunas de dados não permitem duplicatas, mas valores NULL são permitidos (mas só pode haver um valor NULL em uma tabela, porque NULL é considerado um valor especial em comparações de exclusividade).
Não pode ser referenciado como chave estrangeira.
Uma tabela permite que índices exclusivos sejam criados em múltiplas colunas.
Cena aplicável：
Usado para garantir a exclusividade dos dados e evitar duplicação de dados.
Em cenários onde é necessário garantir que o valor de uma determinada coluna ou combinação de colunas seja único, o uso de um índice único pode garantir a precisão dos dados.
Quando requisitos de exclusividade estão envolvidos nas condições de consulta, o uso de um índice exclusivo pode acelerar a consulta.
3. Índice de chave primária
Características：
O índice de chave primária é um índice exclusivo especial, que não apenas exige que o valor da coluna de dados seja exclusivo, mas também não permite NULL.
Só pode haver um índice de chave primária por tabela.
Os índices de chave primária podem ser referenciados como chaves estrangeiras.
Cena aplicável：
Usado para identificar exclusivamente cada linha de dados na tabela para garantir a integridade e consistência dos dados.
Em cenários em que você precisa acessar rapidamente linhas específicas em uma tabela, o uso de índices de chave primária pode melhorar significativamente a eficiência da consulta.
O índice de chave primária é um aspecto muito importante no design de tabelas e na otimização do banco de dados. Ele tem um impacto importante no desempenho e na capacidade de manutenção do banco de dados.
4. Índice combinado
Características：
Use várias colunas para formar um índice.
Você pode melhorar a eficiência das consultas que incluem essas colunas, especialmente se essas colunas aparecerem frequentemente juntas nos critérios de consulta.
A ordem na qual os índices são combinados é importante porque afeta a seleção e o uso de índices pelo otimizador de consulta.
Cena aplicável：
Quando as condições de consulta envolvem múltiplas colunas, o uso de um índice combinado pode reduzir significativamente a quantidade de dados que precisam ser verificados e melhorar a eficiência da consulta.
Em cenários em que são necessárias operações conjuntas de consulta, classificação ou filtragem em múltiplas colunas, o uso de um índice combinado pode otimizar o desempenho da consulta.
5. Outros tipos de índice (como índice de texto completo)
Características：
Um índice de texto completo é um tipo especial de índice usado para pesquisar palavras-chave em dados de texto.
É diferente dos índices regulares na estrutura e no uso de dados e geralmente é usado em cenários de pesquisa de texto completo.
Cena aplicável：
Adequado para pesquisa difusa por palavra-chave de dados massivos, como função de pesquisa de texto em mecanismos de pesquisa.
A indexação de texto completo pode fornecer uma solução mais eficiente quando você precisa realizar pesquisas complexas em campos de texto (como pesquisas envolvendo curingas, pesquisas de sinônimos, etc.).
Aqui estão as etapas detalhadas sobre como criar, visualizar e excluir índices:
Criar índice
Existem muitas maneiras de criar um índice, mas elas podem ser resumidas da seguinte forma:
Especifique o índice ao criar a tabela：
Em usoCREATE TABLE Ao criar uma tabela usando uma instrução, você pode especificar o índice diretamente após a definição da coluna. Isso inclui índices de chave primária, índices exclusivos, etc.
Exemplo (supondo que você crie um arquivo chamadostudentsmesa, e emidCrie um índice de chave primária na coluna):
CREATETABLE students (
id INTAUTO_INCREMENTPRIMARYKEY,
name VARCHAR(100),
age INT,INDEX idx_name (name)-- 在name列上创建普通索引);
1
2
3
4
5
6
Adicione um índice usando a instrução ALTER TABLE：
Se a tabela já existir, você pode usarALTER TABLEinstrução para adicionar um índice à tabela.
Exemplo (porstudentsmesaageAdicione um índice normal à coluna):
ALTERTABLE students ADDINDEX idx_age (age);
1
Crie um índice usando a instrução CREATE INDEX：
Outra maneira de criar um índice em uma tabela existente é usarCREATE INDEXdeclaração.
Exemplo (porstudentsmesanameeageCombinação de colunas para criar índice):
CREATEINDEX idx_name_age ON students (name, age);
1
Ver índice
Os métodos de visualização de índices variam de sistema de banco de dados para sistema de banco de dados, mas a maioria dos bancos de dados fornece comandos ou métodos de consulta correspondentes para visualizar informações de índice.
Usando o comando SHOW INDEX (MySQL)：
Para banco de dados MySQL, você pode usarSHOW INDEXComando para visualizar as informações do índice da tabela.
Exemplo:
SHOWINDEXFROM students;
1
Isto irá listarstudentsTodas as informações de índice da tabela, incluindo nome do índice, nome da coluna, tipo de índice, etc.
Consulte a tabela INFORMATION_SCHEMA.STATISTICS (MySQL)：
Outra maneira de ver o índice é consultarINFORMATION_SCHEMA.STATISTICS superfície. Esta tabela armazena informações estatísticas de todas as tabelas do banco de dados, incluindo informações de índice.
Usando visualizações ou funções do sistema (outros sistemas de banco de dados)：
Para outros sistemas de banco de dados (como SQL Server, Oracle, etc.), pode ser necessário usar visualizações do sistema ou funções específicas para visualizar informações de índice. Para métodos específicos, consulte a documentação oficial do banco de dados correspondente.
Excluir índice
A exclusão de um índice também requer métodos diferentes dependendo do sistema de banco de dados.
Use o comando DROP INDEX：
A maioria dos sistemas de banco de dados suporta o usoDROP INDEXcomando para excluir o índice.
Exemplo (MySQL):
DROPINDEX idx_name ON students;
1
Nota: Em alguns sistemas de banco de dados (como SQL Server),DROP INDEXA sintaxe do comando pode ser um pouco diferente, exigindo que o nome da tabela e o nome do índice sejam especificados, mas não usandoONPalavras-chave.
Use a instrução ALTER TABLE：
Outra maneira de excluir um índice é usarALTER TABLEdeclaração.
Exemplo (MySQL):
ALTERTABLE students DROPINDEX idx_age;
1
Use ferramentas de gerenciamento de banco de dados：
Além de usar comandos SQL, você também pode usar ferramentas de gerenciamento de banco de dados (como MySQL Workbench, SQL Server Management Studio, etc.) para criar, visualizar e excluir índices graficamente. Essas ferramentas geralmente fornecem interfaces de operação mais intuitivas e opções funcionais ricas.
Precauções
Ao criar, visualizar e eliminar índices, certifique-se de ter conhecimento suficiente do sistema de banco de dados para evitar erros desnecessários e perda de dados.
Embora os índices possam melhorar a eficiência da consulta, eles também ocupam espaço de armazenamento adicional e podem aumentar a sobrecarga das operações de inserção, atualização e exclusão de dados. Portanto, ao criar um índice, é necessário fazer concessões e escolhas com base na situação real.
Antes de excluir um índice, certifique-se de que ele não esteja mais em uso ou que um esquema de indexação alternativo esteja disponível. Caso contrário, eliminar o índice poderá diminuir o desempenho da consulta.
Os índices têm um impacto significativo no desempenho do banco de dados, tanto positivo quanto potencialmente negativo. A seguir está uma análise detalhada:
Influência positiva
Acelere a recuperação de dados：
Os índices podem acelerar significativamente a recuperação de dados. Através do índice, o sistema de banco de dados pode localizar diretamente a localização dos dados de destino sem verificar a tabela inteira, uma por uma. Isto é especialmente importante em conjuntos de dados de grande escala e pode melhorar significativamente a eficiência da consulta.
Os índices também reduzem os custos de E/S, reduzindo o número de linhas que um banco de dados verifica, porque o sistema de banco de dados pode encontrar os dados necessários com mais rapidez, sem precisar ler uma tabela inteira ou um grande número de linhas de dados irrelevantes.
Melhore o desempenho do banco de dados：
O uso de índices pode reduzir o número de operações de E/S de disco no sistema de banco de dados porque os índices geralmente são armazenados na memória e podem ser acessados rapidamente. Isto ajuda a melhorar o desempenho de todo o sistema de banco de dados, especialmente ao lidar com consultas complexas e grandes quantidades de dados.
Os índices também podem otimizar os planos de consulta, permitindo que o sistema de banco de dados execute operações de consulta com mais eficiência. O otimizador de consulta pode usar índices para gerar planos de consulta mais eficientes, melhorando assim o tempo de resposta e o rendimento da consulta.
Garanta a exclusividade dos dados：
Índices exclusivos e índices de chave primária garantem a exclusividade dos dados no banco de dados. Isso ajuda a manter a integridade e a consistência dos dados, evitando erros e duplicação de dados.
potenciais impactos negativos
Aumente os requisitos de espaço de armazenamento：
Os índices requerem espaço de armazenamento adicional. Para bancos de dados grandes, o espaço de armazenamento ocupado pelo índice pode ser bastante grande, o que aumenta o custo de armazenamento do banco de dados.
À medida que o número de índices aumenta, os requisitos gerais de armazenamento do banco de dados aumentam proporcionalmente. Portanto, o impacto no espaço de armazenamento precisa ser ponderado ao criar um índice.
Aumente os custos de escrita：
Cada vez que os dados são inseridos, atualizados ou excluídos, o sistema de banco de dados não só precisa modificar os próprios dados, mas também atualizar os índices correspondentes. Isso aumenta a sobrecarga das operações de gravação e reduz o desempenho de gravação.
Em cenários de operações de gravação altamente simultâneas, as atualizações frequentes do índice podem se tornar um gargalo de desempenho. Portanto, o impacto no desempenho de gravação precisa ser considerado ao projetar índices.
Reduza a eficiência do otimizador de consulta：
Em alguns casos, os índices podem fazer com que o otimizador de consulta escolha um plano de consulta abaixo do ideal. Isso pode ocorrer porque a presença do índice engana o otimizador de consulta na estimativa dos custos da consulta.
Portanto, ao criar um índice, você precisa considerar cuidadosamente se ele realmente ajudará a otimizar o desempenho da consulta e evitará a criação de índices redundantes ou desnecessários.
Custos de manutenção do índice：
Os índices requerem manutenção regular para manter seu desempenho. Isso inclui operações como reconstrução ou reorganização de índices, atualização de estatísticas de índice e assim por diante. Essas operações consomem tempo e recursos adicionais e podem ter um impacto de curto prazo no desempenho do banco de dados.
Os administradores de banco de dados precisam ter um certo nível técnico para gerenciar índices de maneira eficaz, incluindo a seleção de estratégias de indexação apropriadas, a otimização do desempenho da consulta e a manutenção regular dos índices.
Quando uma coluna tem um índice, mas alguns dados são excluídos, a atualização do índice geralmente é feita automaticamente pelo sistema de gerenciamento de banco de dados (SGBD) sem intervenção manual do usuário. O objetivo dos índices de banco de dados é acelerar a recuperação de dados. Eles estão intimamente relacionados aos dados da tabela, mas o índice em si não armazena dados, mas armazena ponteiros ou informações de localização que apontam para os dados da tabela.
Aqui estão instruções detalhadas sobre como o índice é atualizado automaticamente para refletir exclusões de dados:
Mecanismo de atualização automática de índice
Operação de exclusão de dados：
Quando um usuário executa uma operação de exclusão de dados (como usarDELETEinstrução), o DBMS primeiro removerá a linha de dados especificada da tabela.
Esta operação de exclusão não afeta apenas os dados da tabela, mas também os índices associados aos dados.
Atualizações de índice：
Quando uma linha de dados é excluída de uma tabela, o SGBD atualiza automaticamente todos os índices afetados.
Para cada linha de dados excluída, o SGBD removerá a entrada de índice correspondente (ou seja, o ponteiro ou informações de localização apontando para a linha de dados) do índice.
Esta atualização é imediata, o que significa que quando uma linha é excluída da tabela, a entrada do índice correspondente é removida imediatamente.
Considerações de desempenho：
Embora a atualização automática do índice garanta a consistência dos dados do índice e da tabela, ela também pode ter um certo impacto no desempenho.
Especialmente ao executar um grande número de operações de exclusão, a atualização do índice pode se tornar um gargalo de desempenho.
Portanto, antes de executar um grande número de operações de exclusão, às vezes pode ser necessário considerar outras estratégias, como exclusão em lote, reconstrução de índice, etc., para otimizar o desempenho.
Reconstrução de índice
Se a operação de exclusão causar fragmentação grave do índice e afetar o desempenho da consulta, considere reconstruir o índice.
A reconstrução do índice é uma operação para reconstruir o índice, que pode eliminar a fragmentação do índice e melhorar o desempenho da consulta.
No MySQL, você pode usarALTER TABLECorrespondência de declaraçãoDROP INDEXeADD INDEX opção para reconstruir o índice. No entanto, deve-se notar que este processo pode bloquear temporariamente a tabela e afetar outras operações de consulta.
A atualização manual de índices para refletir as operações de exclusão de dados geralmente não é necessária na maioria dos sistemas de gerenciamento de banco de dados (como MySQL, PostgreSQL, SQL Server, etc.) porque o banco de dados mantém automaticamente a consistência do índice. Quando você exclui dados de uma tabela, o banco de dados remove automaticamente a entrada de índice correspondente do índice.
No entanto, em alguns casos, o desempenho da consulta poderá ser afetado se um índice ficar fragmentado devido a modificações frequentes de dados, incluindo inserções, atualizações e exclusões. Neste ponto, você pode querer intervir manualmente na manutenção do índice, incluindo a reconstrução ou otimização do índice. Embora isso não "atualize" diretamente o índice para refletir uma única operação de exclusão, pode melhorar o desempenho geral do índice.
A seguir estão alguns métodos manuais de otimização de índice que podem refletir indiretamente o impacto das operações de exclusão de dados no índice:
Reconstruir índice：
usarALTER TABLE a instrução descarta e recria o índice. Isso remove a fragmentação do índice e o torna mais compacto e eficiente.
Por exemplo, no MySQL, você pode usar o seguinte comando para reconstruir o índice (assumindo que o índice seja denominadoidx_name, o nome da tabela éusers）：
Nota: A reconstrução do índice pode bloquear temporariamente a tabela e afetar outras operações de consulta, por isso é recomendável realizá-la fora dos horários de pico.
Otimizar tabela：
usarOPTIMIZE TABLE instrução para reorganizar o armazenamento físico da tabela, incluindo índices. Isto pode ajudar a reduzir a fragmentação da tabela e potencialmente melhorar o desempenho da consulta.
Por exemplo, no MySQL, você pode usar o seguinte comando para otimizar uma tabela (assumindo que a tabela seja chamadausers）：
OPTIMIZETABLE users;
1
Nota: O processo de otimização da tabela pode levar algum tempo e a tabela também pode ficar temporariamente bloqueada.
Tabela de Análise：
usarANALYZE TABLE instrução para atualizar as estatísticas da tabela, incluindo o uso do índice. Isso ajuda o otimizador de banco de dados a gerar planos de consulta mais eficientes.
Por exemplo, no MySQL, você pode usar o seguinte comando para analisar uma tabela (assumindo que a tabela seja chamadausers）：
ANALYZETABLE users;
1
A análise de tabelas não afeta diretamente a estrutura física do índice, mas pode ajudar o banco de dados a fazer melhor uso do índice.
Manter o índice regularmente：
Verifique periodicamente a fragmentação do índice e otimize ou reconstrua conforme necessário.
Considere o uso de ferramentas ou scripts de gerenciamento de banco de dados para automatizar o processo de manutenção do índice.
É importante observar que a atualização manual do índice para refletir uma única operação de exclusão de dados geralmente não é necessária porque o banco de dados trata essa situação automaticamente. No entanto, é importante manter o seu índice regularmente para garantir que permanece em condições ideais, o que pode ser conseguido através dos métodos descritos acima.
Além disso, se você precisar intervir manualmente no índice para refletir alterações específicas nos dados (embora isso seja raro), talvez seja necessário obter uma compreensão mais profunda do funcionamento interno do seu sistema de gerenciamento de banco de dados e considerar o uso do comando de manutenção de banco de dados de nível inferior ou ferramenta. Entretanto, na maioria dos casos, basta confiar nos recursos automáticos de manutenção de índice do banco de dados.
