Aplicativo de índice MySQL

O mecanismo Innodb exige que cada tabela de banco de dados tenha umchave primáriaíndice，Valores de coluna de índice não são permitidosvalor nulo。Por exemplo, o campo id na tabela é o índice de chave primária

Índice único: Garanta a exclusividade de cada linha de dados na coluna de dados, mas permita valores nulos.

EntãoPara campos que são consultados com frequência, podemos criar um índice normal para este campo.，Se houver vários campos, considere criarÍndice da União,usarCobertura do índiceOs recursos melhoram a eficiência da consulta.

Para campos de texto longo, string e outros tipos, como títulos de artigos, nomes de produtos, etc., só podemos indexar a parte do prefixo desses campos, ou sejaCrie um índice de prefixo para reduzir o espaço de armazenamento do índice.

Qual é a diferença entre um índice normal e um índice único? Qual deles tem melhor desempenho de atualização? 、

Um índice exclusivo pode ser um pouco mais rápido ao consultar um único valor porque pode encerrar a pesquisa após encontrar a primeira correspondência.
Para operações de inserção e atualização, um índice normal pode ser um pouco mais rápido porque não requer verificações de exclusividade.

Os valores das colunas de índice comuns podem ser repetidos, mas os valores das colunas de índice exclusivos devem ser únicos. Quando inserimos um valor repetido em um índice exclusivo, um erro será relatado devido à restrição de exclusividade.
Eu pensoO desempenho de atualização do índice comum será melhor, porque quando o índice comum for atualizado, se a página de dados atualizada não forMemória Nesse caso, você pode armazenar em cache diretamente a operação de atualização no buffer de alteração e a operação de atualização será concluída. (não é necessária verificação de exclusividade)
mas,O índice exclusivo precisa ter restrições exclusivas se a página de dados atualizada não estiver no arquivo.MemóriaNesse caso, você precisa ler a página de dados correspondente do disco para a memória para determinar se há um conflito. Isso envolverá a randomização do disco.IOAcesso.
Como os índices comuns podem usar o recurso de buffer de alteração, a atualização dos índices comuns é mais rápida do que a dos índices exclusivos.Acesso aleatório ao disco reduzido, então o desempenho da atualização é melhor

índice clusterizadodechave primáriaComo definir o índice? Pergunta: O que acontecerá se você não defini-lo?

Quando o InnoDB cria um índice clusterizado, ele seleciona diferentes colunas como índices de acordo com diferentes cenários:

Se houver uma chave primária, a chave primária será usada como chave de índice do índice clusterizado por padrão.
Se não houver chave primária, selecioneO primeiro não contém Valor NULOA única coluna de é comoíndice clusterizadochave de índice
Na ausência de qualquer um dos itens acima, o InnoDB gerará automaticamente uma coluna rowid de incremento automático implícita como a chave do índice clusterizado.

Que tipo de campos costumamos escolher para construir índices?

Cenários onde a indexação é aplicável:

Os campos têm restrições de exclusividade, como código do produto
Campos frequentemente usados em condições de consulta WHERE, o que pode melhorar a velocidade de consulta de toda a tabela. Se a condição de consulta não for um campo, um índice conjunto poderá ser estabelecido.
Campos frequentemente usados em GROUPBY e ORDER BY, para que não haja necessidade de ordenar novamente durante a pesquisa, pois os registros na Árvore B+ são todos ordenados após o estabelecimento do índice.

Cenários não adequados para indexação

Campos não usados nas condições WHERE, GROUP BY, ORDER BY, o valor do índice é de posicionamento rápido. Se o campo não puder ser posicionado, geralmente não há necessidade de criar um índice, pois o índice ocupará espaço físico.
Campos pouco distinguíveis , não há necessidade de criar um índice, por exemplo, o campo gênero só possui homens e mulheres. Se os registros de homens e mulheres estiverem distribuídos uniformemente na tabela do banco de dados, então, independentemente do valor pesquisado, metade dos dados poderá. ser obtido.Nestes casos, é melhor não indexar porque MySQLAinda há umotimizador de consulta, quando o otimizador de consulta descobre que um determinado valor aparece em uma alta porcentagem de linhas de dados na tabela, ele geralmente ignora o índice e executaVerificação completa da tabela。
Campos atualizados com frequência, por exemplo, não indexam o saldo do usuário de projetos de comércio eletrônico porque os campos de índice são modificados com frequência.manter B+Árvoreordem, será necessária a reconstrução frequente do índice e esse processo afetará o desempenho do banco de dados.
Não é recomendado usar valores não ordenados(como cartão de identificação, UUID) como índice, quando a chave primária é incerta, causará divisão frequente de nós folha e fragmentação do armazenamento em disco.

A tabela de dados é menor: Quando a quantidade de dados em uma tabela é pequena ou quando uma consulta requer a verificação de uma grande parte dos dados da tabela, o otimizador de banco de dados pode escolher uma verificação completa da tabela em vez de usar um índice. Neste caso, o custo de manutenção do índice pode ser maior que o ganho de desempenho.

Mais índices são melhores?

Não, embora os índices possam melhorar a eficiência da consulta, criar mais um índice significa que será gerado um novo índice de árvore B+, que ocupará espaço de armazenamento. Especialmente quando a quantidade de dados da tabela for muito grande, o índice ocupará mais espaço.

Quanto mais índices houver, o desempenho de gravação do banco de dados diminuirá, pois cada vez que você adiciona, exclui ou modifica a tabela, é necessário manter a ordem de cada índice da árvore B+.

Como otimizar o índice (índice de coberturaOtimize e evite falhas de índice,chave primáriaOtimização incremental de índice de prefixo)

Eu usei esses métodos de otimização

Para SQL que precisa consultar dados em diversos campos, podemos criarÍndice da União, então o método de consulta se tornaíndice de cobertura, evitando o backup de tabelas e reduzindo um grande número de operações de E/S.
nossochave primáriaOs índices são preferencialmente valores crescentes, porque nosso índice armazena dados em ordem, se o valor da chave primária for um valor aleatório, isso pode causar a divisão da página. A divisão da página causará um grande número de fragmentos de memória, de modo que a estrutura do índice não será compacta, o que causará um grande número de fragmentos de memória. afetar a eficiência da consulta.
nós queremosEvite escrever falha no índice SQL Instruções, como não realizar correspondência difusa à esquerda ou à esquerda em colunas de índice, não realizar cálculos, funções e operações de conversão de tipo em índices. Para usar índices conjuntos corretamente, você deve seguir o princípio de correspondência mais à esquerda, etc.Na cláusula WHERE, se a coluna de condição antes de OR for uma coluna de índice e a coluna de condição após OR não for uma coluna de índice, o índice falhará.

Use diferente de (<>) ou operador NOT: Esses operadores geralmente invalidam o índice porque verificam a tabela inteira.

Operador OR: Se OR for usado na condição de consulta e as condições em ambos os lados do OR envolverem índices diferentes, esses índices não poderão ser usados.

usar OR operador, seOR As condições de ambos os lados envolvem índices diferentes e, na maioria dos casos, o mecanismo de banco de dados não pode usar vários índices ao mesmo tempo para otimizar a consulta.Isso éporque OR O operador só precisa atender às condições de ambos os lados, o que aumenta a complexidade da otimização da consulta.

Índice para alguma string grande, podemos considerar usaríndice de prefixoSomente a parte do prefixo da coluna do índice é indexada para economizar espaço de armazenamento do índice e melhorar o desempenho da consulta.
O índice é melhor definido como NOT NULO : Para utilizar melhor o índice, a coluna do índice deve ser definida como a restrição NOT NULL. Existem dois motivos:
1. A presença de NULL nas colunas de índice tornará a seleção do índice do otimizador mais complicada, dificultando a otimização de operações como contagem.
2. O valor NULL é um valor sem sentido, mas ocupará espaço físico. Existe uma coluna de valor nulo.Pelo menos 1 byte de espaço será usado para armazenar NULL lista de valores

Se um índice for criado, ele será usado na consulta (falha no índice,otimizadorSelecione o plano de execução com base no custo)

não.

aprendiMesmo que a consulta utilize um índice, ela não poderá utilizar o índice.
1. Por exemplo: quando nossa instrução de consulta executa correspondência difusa à esquerda, cálculo de expressão, função e operações implícitas de conversão de tipo no campo de índice, a instrução de consulta não pode passar pelo índice e o método de consulta se torna uma varredura completa da tabela.
2. E nós usamosÍndice da UniãoAo consultar, se o princípio de correspondência mais à esquerda não for seguido, também ocorrerá falha no índice.。
O otimizador éEscolha um método de consulta com base em considerações de custo, ao usar o índice secundário para consulta, o otimizador calculará o custo do retorno da tabela e o custo da varredura completa da tabela. Se o custo do retorno da tabela for muito alto, o otimizador escolherá não usar o índice, mas usar o índice. verificação completa da tabela.

Se eu definir um campo de data do tipo varchar, e um dos dados for '20230922', e se houver um índice neste campo de data, então se a condição where da minha consulta for where time=20230922 sem aspas simples, o índice ainda será atingido?

Não atingirá o índice.

Porque o mysql está encontrandoComparação de strings e númerosacontecerá quandoconversão implícita de tipo, vaiConverter objeto string em número, esse processo de conversão na verdade envolvefunção . Na consulta que você mencionou, o campo de data é uma string, portanto, quando ocorrer uma conversão implícita de tipo, ela será aplicada ao campo de índice de data. Se o cálculo da função for executado no índice, o índice se tornará inválido.

Para colunas de índice do tipo inteiro, por exemploid Coluna cujo valor é armazenado diretamente no índice sem que ocorra o cálculo da função.Isso significa usar na consultaidAo combinar, não é necessárioidExecute quaisquer cálculos funcionais ou conversões e simplesmente compare valores inteiros.

MySQLA versão mais recente resolveu algum caso de falha de índice (Índice de função:cálculo de funçãoO valor depois também pode ser indexado e mecanismo de varredura de salto de índice (prefixo mais à esquerda))

Aprendi que o MySQL8.0 pode adicionar camposíndice de função, esse novo recurso pode resolver o problema de falha do índice ao usar funções no índice.

Outra novidade évarredura de salto de índice, Antes da versão 5.7, ao usar um índice conjunto, se o princípio de correspondência mais à esquerda não for atendido, ocorrerá uma falha no índice. No entanto, após o recurso de varredura de salto de índice ser introduzido na versão 8.0, os índices conjuntos ainda poderão ser usados, mesmo se o princípio de correspondência mais à esquerda. não é seguido.

Qual é o princípio de correspondência mais à esquerda?

Suponha que haja um índice conjunto (a, b, c). Sua ordem de armazenamento é classificar primeiro por a, depois classificar por b quando a for o mesmo e, em seguida, classificar por c quando b for o mesmo. Devido a esse recurso, ao usar índices conjuntos, existe um princípio de correspondência mais à esquerda. As regras específicas são:

O índice federado do MySQL começará emA coluna de índice mais à esquerda começa a corresponder às condições de consulta e, em seguida, corresponde em sequência da esquerda para a direita. Se as condições de consulta não usarem uma coluna, todas as colunas à direita da coluna não poderão ser indexadas.
Quando uma coluna é usada na condição de consulta,No entanto, o valor desta coluna contém uma consulta de intervalo e os campos da consulta de intervalo podem ser usadosÍndice da União, mas o índice conjunto não pode ser usado nos campos atrás do campo de consulta de intervalo.

Portanto, quando usamos índices conjuntos, devemos respeitar o princípio de correspondência mais à esquerda, caso contrário, alguns campos de índice podem não ser indexados.

EstabelecerÍndice da UniãoHá algo que precisamos prestar atenção (os mais diferenciados são colocados na extrema esquerda, o princípio de correspondência mais à esquerda, e o índice não é usado após a consulta de intervalo)

maioriaColoque os campos com maior distinção emÍndice da Uniãoesquerda longínqua, útilMelhorar o efeito de filtragem de índice, campos como UUID são mais adequados para indexação ou classificação no topo da coluna do índice conjunto.
Se um campo com baixa discriminação for colocado no lado esquerdo do índice conjunto, isso poderá fazer com que o otimizador de consulta escolha uma varredura completa da tabela em vez de usar o índice.
O princípio de correspondência mais à esquerda do índice conjunto, emAo encontrar uma consulta de intervalo (como >, <), a correspondência será interrompida, ou seja, os campos da consulta de intervalo podem usar o índice conjunto, mas os campos atrás do campo de consulta de intervalo não podem usar o índice conjunto.No entanto, para as quatro consultas de intervalo de >=, <=, BETWEEN e correspondência de prefixo semelhante, a correspondência não será interrompida.
1. No MySQL, BETWEEN contém valores limites valor1 e valor2, semelhantes a >= e =<.
2. Link de referência https://zhuanlan.zhihu.com/p/573138586

Ordem de consulta do princípio de correspondência mais à esquerda

select * from T where c=1 and a=2 and b=3;

abc pode ser indexado porque A ordem em que os campos de condição de consulta não afetam, o otimizador MySQL nos ajudará a ajustar a ordem de consulta dos campos, de modo que também esteja em conformidade com o princípio de correspondência mais à esquerda.

sob índiceempurrar O que é? Adicionado no MySQL5.6 para otimizar consultas de dados

O pushdown do índice pode reduziríndice secundárioA operação de retorno de tabela durante a consulta melhora a eficiência da consulta porque A camada do servidor é responsável por algumas das coisas que são tratadas pela camada do mecanismo de armazenamento.Fui lidar com isso.

Quando a otimização push-down sem condições de índice é usada, o mecanismo de armazenamento recupera os dados por meio do índice e os retorna ao MySQL Server.Servidor MySQL Faça julgamentos sobre as condições do filtro.
Ao usar a otimização push-down da condição de índice, se houver certas condições de julgamento para colunas indexadas, o MySQL Server enviará esta parte das condições de julgamento para o mecanismo de armazenamento e, em seguida, o mecanismo de armazenamento julgará se o índice atende às condições passadas por Servidor MySQL. Somente quando o índice atender às condições, os dados serão recuperados e retornados ao servidor MySQL.

A otimização do empilhamento da condição do índice pode reduzir o número de vezes que o mecanismo de armazenamento consulta a tabela subjacente e também pode reduzir MySQL O número de vezes que o servidor recebeu dados do mecanismo de armazenamento.

select * from t_user where age > 20 and reward = 100000;

Como criar um índice onde a>1 e b=2 ec <3?

Criar índice conjunto (abc), (acb), (ab), (ac), apenas um índice de lata
Crie índices conjuntos (cab), (cba), (ca), (cb), somente c pode indexar
Crie (ba) um índice conjunto, tanto b quanto a podem ser indexados
Crie um índice conjunto (bc), tanto b quanto c podem ser indexados
criar (bac) Índice da União, b e a podem ser indexados, mas são mais lentos que (BA) o índice conjunto tem mais um benefício, o campo c podepushdown de índice, reduzirá o número de retornos de tabela;
criar(bca) Índice da União, tanto b quanto c podem ser indexados, mas tem mais uma vantagem que o índice conjunto (bc), o campo a podepushdown de índice, reduzirá o número de retornos de tabela;

(A,B,C) índice conjunto `select * from tbn where a=? and b in (?,?) and c>?` Será indexado?

Esta consulta usará o índice conjunto (A,B,C), porque a condição é baseada na coluna de índice A、B、C Chega o pedido, que é o cenário ideal de uso.

para A=?: Esta condição é uma correspondência exata. O MySQL usará o índice para localizar a condição que satisfaz a condição. A=? registro de.
para B IN (?, ?): Esta condição especifica B A coluna pode assumir dois valores possíveis. MySQL usará o índice para encontrar todas as correspondênciasA=? eB Coluna é um registro com qualquer um desses dois valores.
para C>? : esta condição é uma consulta de intervalo.já baseado emA eB Com base no filtro, o MySQL continuará a usar o índice para encontrarC Registros com valores de coluna maiores que o valor especificado.

onde a> 100 e b = 100 e c = 123 ordene por d como criarÍndice da União?

Eu pensoEstabelecer bcda em ordemÍndice da UniãoMelhorar, neste momento ambos os campos b e c podem ser indexados, ed pode usar a ordenação do índice para evitar a classificação de arquivos (classificação extra), embora o último campo a não possa ser indexado (a está fora de ordem), ele pode ser empurrado para baixo usando o índice para reduzir o número de retornos da tabela.

selecione id, nome de XX onde idade > 10 e nome como 'xx%', OKÍndice da União(nome, idade), fale sobre o processo de consulta

A ordem do índice conjunto é primeiro o nome e depois a idade. Estruturalmente, ele é classificado primeiro por nome e depois por idade se os nomes forem iguais.Portanto, o otimizador precisa corresponder primeiro ao nome. O nome é uma consulta difusa correta neste momento e a falha do índice não ocorrerá, portanto, este SQL pode usar a indexação conjunta.

Especificamente, apenas o nome pode ser indexado.Após a consulta difusa do nome à direita, os valores do campo idade não estão em ordem, portanto, a idade não pode ser indexada, mas a idade pode ser indexada.pushdown de índice。

Os últimos campos consultados são id e nome. Esses dois campos podem ser encontrados no índice conjunto, portanto não há necessidade de retornar a tabela.

A consulta difusa à direita do nome é uma consulta de intervalo e os campos a seguir não podem ser indexados

Compartilhamento de tecnologia