Tipos de índices do Mysql e suas vantagens e desvantagens

ÍndiceÉ um arquivo especial (os índices de tabelas de dados InnoDB são uma parte do espaço de dados), que contém ponteiros de referência para todos os registros da tabela de dados.
Nota:
[1]Os índices não são onipotentes! Os índices podem acelerar as operações de busca de dados, mas tornar as operações de modificação de dados mais lentas. Cada modificação de registro de dados precisa atualizar o índice. Para compensar essa deficiência, muitos comandos SQL têm um item DELAY_KEY_WRITE. A função dessa opção é temporariamente impedir que o MySQL atualize o índice imediatamente após cada inserção de novo registro e cada modificação de registro existente, aguardando que todos os registros sejam inseridos/Após a modificação, faça isso. No caso de precisar inserir muitos novos registros em uma tabela de dados, a opção DELAY_KEY_WRITE será muito significativa.
[2Além disso, os índices ocuparão um espaço considerável no disco. Portanto, deve-se criar índices apenas para as colunas que são mais frequentemente consultadas e ordenadas. Note que, se uma coluna contiver muitos conteúdos repetidos, a criação de um índice não terá grande efeito prático.
Teoricamente, é完全可以为数据表中的每个字段 criar um índice separado, mas o MySQL limita o número total de índices em uma mesma tabela.16un.

1. Índices de tabelas de dados InnoDB

Em comparação com as tabelas de dados MyISAM, o índice é muito mais importante para os dados InnoDB. No caso das tabelas de dados InnoDB, o índice é ainda mais importante. No caso das tabelas de dados InnoDB, o índice não só funciona na busca de registros de dados, mas também é a base e o suporte do mecanismo de bloqueio de nível de linha de dados. "Bloqueio de nível de linha de dados" significa bloquear individualmente os registros que estão sendo processados durante a execução da operação de transação, permitindo que outros usuários não acessem. Este bloqueio afeta (mas não se limita a) os comandos SELECT…LOCK IN SHARE MODE, SELECT…FOR UPDATE e os comandos INSERT, UPDATE e DELETE.
Pela consideração da eficiência, o bloqueio de nível de linha de dados da tabela de dados InnoDB ocorre realmente em seus índices, não na tabela de dados em si. É claro que o mecanismo de bloqueio de nível de linha de dados só pode funcionar eficazmente quando há um índice adequado disponível para bloquear na tabela de dados relevante.

2. Restrição

Se a cláusula WHERE contiver um operador de desigualdade (WHERE coloum != ...), o MySQL não será capaz de usar o índice.
Da mesma forma, se a cláusula WHERE usar funções (WHERE DAY(coluna) = ...), o MySQL também não será capaz de usar o índice.
Da mesma forma, se a cláusula WHERE usar funções (WHERE DAY(coluna) = ...), o MySQL também não será capaz de usar o índice.
Se a cláusula WHERE usar operadores de comparação LIKE e REGEXP, o MySQL pode usar o índice apenas quando o primeiro caractere do padrão de busca não for um caractere curinga. Por exemplo, se a condição de consulta for LIKE 'abc%', o MySQL usará o índice; se a condição de consulta for LIKE '%abc', o MySQL não usará o índice.
No caso da operação ORDER BY, o MySQL usa o índice apenas quando a condição de ordenação não é uma expressão de condição de consulta. (Embora assim seja, em consultas envolvendo várias tabelas de dados, mesmo que haja índices disponíveis, esses índices não têm muita utilidade para acelerar ORDER BY).
Se uma coluna de dados contiver muitos valores repetidos, mesmo que seja criado um índice, não terá um bom efeito. Por exemplo, se uma coluna de dados contiver apenas coisas como '0',/1ou 'Y'/Se o valor 'N' for igual, não há necessidade de criar um índice para ele.　

Índice comum, índice único e índice primário

1Índice comum

A tarefa única do índice comum (definido pela palavra-chave KEY ou INDEX) é acelerar o acesso aos dados. Portanto, deve-se criar índices apenas para as colunas que mais aparecem nas condições de consulta (WHERE coluna = ...) ou nas condições de ordenação (ORDER BY coluna). Sempre que possível, deve-se escolher uma coluna com dados mais organizados e compactos (como uma coluna de tipo inteiro) para criar índices.

2Índice único
O índice comum permite que a coluna indexada contenha valores repetidos. Por exemplo, devido ao fato de as pessoas poderem ter o mesmo nome, o mesmo nome pode aparecer duas ou mais vezes na tabela de dados de 'perfil pessoal de funcionário'.
Se puder ser determinado que uma coluna de dados conterá apenas valores diferentes entre si, ao criar um índice para essa coluna de dados, deve ser definido como índice único usando a palavra-chave UNIQUE. As vantagens disso: uma é simplificar o trabalho de gerenciamento do índice pelo MySQL, tornando-o mais eficiente; a outra é que o MySQL verificará automaticamente se o valor do novo registro naquela coluna já apareceu em algum registro daquela coluna; se sim, o MySQL recusará a inserção daquele novo registro. Isso significa que o índice único pode garantir a unicidade dos registros de dados. De fato, em muitos casos, o objetivo de criar um índice único não é aumentar a velocidade de acesso, mas evitar que os dados se repitam.

3. Índice primário

Já foi enfatizado várias vezes anteriormente: é necessário criar um índice para o campo da chave primária, que é conhecido como 'índice primário'. A única diferença entre o índice primário e o índice único é que o PRIMARY é usado como palavra-chave na definição do primeiro, em vez de UNIQUE.

4. Índice de chave estrangeira

Se uma restrição de chave estrangeira for definida para um campo de chave estrangeira, o MySQL definirá um índice interno para ajudar a gerenciar e usar a restrição de chave estrangeira de forma mais eficiente.

5. Índice composto

Os índices podem cobrir várias colunas de dados, como o índice INDEX(columnA, columnB). A característica desse tipo de índice é que o MySQL pode usar opcionalmente um desses índices. Se a operação de consulta precisar usar apenas um índice na coluna de dados columnA, pode usar o índice composto INDEX(columnA, columnB). No entanto, esse uso só é aplicável às combinações de colunas ordenadas no índice composto. Por exemplo, INDEX(A, B, C) pode ser usado como índice de A ou (A, B), mas não pode ser usado como índice de B, C ou (B, C).

6. Comprimento do índice

Ao definir índices para colunas do tipo CHAR e VARCHAR, pode-se limitar o comprimento do índice a um número de caracteres específico (esse número deve ser menor do que o número máximo de caracteres permitido pelo campo). A vantagem disso é gerar um arquivo de índice de tamanho menor, mas com uma velocidade de busca mais rápida. Em grande parte das aplicações, os dados de string no banco de dados são predominantemente nomes de variadas formas, e configurar o comprimento do índice como10~15caracteres já é suficiente para restringir a busca a apenas algumas poucas gravações de dados.
Ao criar índices para colunas do tipo BLOB e TEXT, é necessário estabelecer um limite para o comprimento do índice; o comprimento máximo permitido para índices pelo MySQL é255caracteres.
Índice completo

　　 Os índices comuns no campo de texto só podem acelerar a busca por strings que aparecem no início do conteúdo do campo (ou seja, os caracteres no início do conteúdo do campo). Se o campo contiver grandes segmentos de texto compostos por várias, até várias palavras, os índices comuns não têm muita utilidade. Essa busca geralmente ocorre na forma LIKE %word%, o que é complexo para o MySQL, e se a quantidade de dados a serem tratados for grande, o tempo de resposta será longo.
Este tipo de situação é exatamente onde o índice de texto completo (full-text index) pode brilhar onde. Ao criar esse tipo de índice, o MySQL criará uma lista de todas as palavras que aparecem no texto, e a operação de consulta usará essa lista para pesquisar os registros de dados relevantes. O índice de texto completo pode ser criado juntamente com a tabela de dados ou usado mais tarde, conforme necessário.

A seguir, adicione este comando:
ALTER TABLE tablename ADD FULLTEXT(column1, column2)

Com o índice de texto completo, é possível usar o comando SELECT para pesquisar registros de dados que contêm uma ou mais palavras dadas. A seguir é a sintaxe básica desses comandos de consulta:
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘word1′, ‘word2′, ‘word3′)

Este comando adicionará column1e coluna2a coluna contém word1、word2e word3todos os registros de dados

Notas：As tabelas de dados InnoDB não suportam índice de texto completo.

Otimização de consulta e índice

　　　　　 Apenas quando há dados suficientes de teste no banco de dados, os resultados do teste de desempenho têm valor de referência real. Se apenas alguns centenas de registros de dados forem encontrados no banco de dados de teste, eles geralmente são carregados em toda a memória após a execução do primeiro comando de consulta, o que tornará a execução dos comandos de consulta subsequentes muito rápida - independentemente de usar ou não índices. Apenas quando os registros no banco de dados excedem10Apenas quando o número de registros de dados é suficiente e a quantidade total de dados também excede a quantidade total de memória do servidor MySQL, os resultados do teste de desempenho do banco de dados têm significado.

　　　　　 Quando não está claro em quais colunas de dados deve ser criado um índice, as pessoas muitas vezes podem obter alguma ajuda do comando EXPLAIN SELECT. Isso é apenas adicionar uma palavra-chave EXPLAIN como prefixo a um comando SELECT comum. Com essa palavra-chave, o MySQL não executará o comando SELECT, mas sim analisá-lo. O MySQL listará o processo de execução da consulta e as informações de índice (se houver) em forma de tabela.
No resultado da saída do comando EXPLAIN, o1A coluna é o nome da tabela de dados lida do banco de dados, organizadas na ordem de leitura. A coluna type especifica a relação de associação (JOIN) entre esta tabela de dados e outras tabelas de dados. Entre vários tipos de relações de associação, a mais eficiente é system, seguida por const, eq_ref, ref, range, index e All (All significa: para cada registro na tabela de dados superior, todos os registros nesta tabela de dados precisam ser lidos novamente - neste caso, geralmente pode ser evitado por um índice).

A coluna possible_keys fornece todas as índices disponíveis para o MySQL ao procurar registros de dados. A coluna key é o índice realmente usado pelo MySQL, a comprimento em bytes deste índice é fornecido na coluna key_len. Por exemplo, para um índice de coluna INTEGER, o comprimento em bytes será4Se for usado um índice composto, você pode ver quais partes específicas do MySQL estão usando na coluna key_len. Seguindo a regra geral, o valor da coluna key_len deve ser o menor possível (o que significa mais rápido).
A coluna ref fornece o nome da coluna de outro tabela na relação de associação. A coluna row é o número de linhas que o MySQL espera ler da tabela ao executar esta consulta. O produto de todos os números na coluna row nos dá uma ideia de quantas combinações esta consulta precisa processar.
Por último, a coluna extra fornece mais informações relacionadas à operação JOIN, por exemplo, se o MySQL deve criar uma tabela temporária ao executar esta consulta, você verá a palavra using temporary na coluna extra.

Isso é tudo o que há no artigo, espero que ajude no seu aprendizado e que você apóie o tutorial Yell.

Declaração: o conteúdo deste artigo é extraído da Internet, pertence ao autor original, foi contribuído e carregado voluntariamente pelos usuários da Internet, este site não possui direitos de propriedade, não foi editado manualmente e não assume responsabilidades legais relacionadas. Se você encontrar conteúdo suspeito de violação de direitos autorais, por favor, envie um e-mail para: notice#oldtoolbag.com (ao enviar e-mail, substitua # por @ para denunciar e forneça provas relevantes. Se confirmado, o site deletará imediatamente o conteúdo suspeito de violação de direitos autorais.)