Compartilhamento de tecnologia

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

---

1. Infraestrutura: como uma instrução de consulta SQL é executada?

Visão geral

De modo geral, o MySQL pode ser dividido em duas camadas

• Camada de servidor
  Abrange a maioria das principais funções de serviço do MySQL
  • Conector
  • Consultar cache
  • Analisador
  • otimizador
  • Atuador do
  • Todas as funções integradas (como data, hora, funções matemáticas e criptográficas, etc.)
  • Capacidades em mecanismos de armazenamento
    • procedimento armazenado
    • acionar
    • visualizar
    • ……
• camada do mecanismo de armazenamento
  Arquitetura plug-in, responsável pelo armazenamento e recuperação de dados
  • InnoDB-In ...
  • MeuISAM
  • Memória

Conector

mysql -h$ip -P$port -u$user -p
1

O mysql no comando de conexão é uma ferramenta cliente usada para estabelecer uma conexão com o servidor.Depois de completar o handshake TCP clássico, o conector
Está prestes a começar a autenticar sua identidade. Neste momento, serão utilizados o nome de usuário e a senha que você digitou.

• Se o nome de usuário ou a senha estiverem incorretos, você receberá um erro "Acesso negado ao usuário" e, em seguida, o programa cliente
  Finalizar execução.
• Se a autenticação de nome de usuário e senha for aprovada, o conectorTabela de permissões Descubra quais permissões você tem lá.Depois, neste contexto
  A lógica de julgamento de permissões dependerá das permissões lidas neste momento.

Se o cliente ficar inativo por muito tempo, o conector irá desconectá-lo automaticamente. Este tempo é controlado pelo parâmetro wait_timeout, e o valor padrão é 8 horas.

Se o cliente enviar uma solicitação novamente após a conexão ser desconectada, ele receberá um lembrete de erro: Lost connection to MySQL server during query . Se quiser continuar neste momento, você precisará reconectar e executar a solicitação.

No banco de dados, uma conexão longa significa que após a conexão ser bem-sucedida, se o cliente continuar a fazer solicitações, a mesma conexão será sempre utilizada. Uma conexão curta significa que a conexão é desconectada após a execução de algumas consultas e uma nova é restabelecida para a próxima consulta.

O processo de estabelecimento de conexão costuma ser complicado, por isso sugiro que você tente minimizar as ações de estabelecimento de conexão durante o uso, ou seja, tente utilizar conexões longas.

Mas depois de todas as conexões longas serem usadas, você pode descobrir que às vezes a memória ocupada pelo MySQL aumenta muito rapidamente.A memória usada temporariamente pelo MySQL durante a execução é gerenciada no objeto de conexão. . Esses recursos serão liberados quando a conexão for desconectada.Então seO acúmulo de conexões longas pode levar ao uso excessivo de memória., foi eliminado à força pelo sistema (OOM). A julgar pelo fenômeno, o MySQL foi reiniciado de forma anormal.

Como resolver este problema? Você pode considerar as duas opções a seguir.

• Desconecte periodicamente conexões longas . Depois de usá-lo por um período de tempo, ou depois que o programa determinar que uma consulta grande que ocupa memória foi executada, a conexão será desconectada e, em seguida, a consulta será necessária e reconectada.
• Se você estiver usando MySQL 5.7 ou mais recente, você pode executar mysql_reset_connection para reinicializar os recursos de conexão. Este processo não requer reconexão e verificação de permissão, mas restaurará a conexão ao estado em que acabou de ser criada.

Consultar cache

Depois que o MySQL receber uma solicitação de consulta, ele irá primeiro para o cache de consulta para ver se esta instrução foi executada antes. Instruções executadas anteriormente e seus resultados podem ser armazenados em cache diretamente na memória na forma de pares chave-valor. A chave é a instrução da consulta e o valor é o resultado da consulta. Se a sua consulta conseguir encontrar a chave diretamente neste cache, o valor será retornado diretamente ao cliente.

Se a instrução não estiver no cache de consultas, a fase de execução continua. Após a conclusão da execução, os resultados da execução serão armazenados no cache de consulta. Você pode ver que se a consulta atingir o cache, o MySQL pode retornar o resultado diretamente sem realizar operações complexas subsequentes, o que é muito eficiente.

Mas na maioria das vezes eu vouÉ recomendado que você não use cache de consulta ,por que? Porque o cache de consultas geralmente causa mais danos do que benefícios.

O cache de consulta é invalidado com muita frequência. Enquanto houver uma atualização em uma tabela, todos os caches de consulta nesta tabela serão limpos. Portanto, é possível que você tenha se dado ao trabalho de salvar os resultados e, antes mesmo de usá-los, eles tenham sido apagados por uma atualização. Para bancos de dados com forte pressão de atualização, a taxa de acertos do cache de consulta será muito baixa. A menos que sua empresa tenha uma tabela estática que só será atualizada de vez em quando. Por exemplo, se for uma tabela de configuração do sistema, a consulta nesta tabela será adequada para o cache de consulta.

Felizmente, o MySQL também fornece esse método de “uso sob demanda”. É possível configurar o parâmetro query_cache_type como DEMAND para que o cache de consulta não seja usado para as instruções SQL padrão. Para instruções que você tem certeza de que deseja usar o cache de consulta, você pode usar SQL_CACHE para especificá-lo explicitamente, como na seguinte instrução:

select SQL_CACHE * from T where ID=10；
1

tem que estar ciente é,A versão MySQL 8.0 exclui diretamente toda a função de cache de consulta, o que significa que esta função não estará mais disponível a partir da versão 8.0.

Analisador

Se o cache de consulta não for atingido, a execução real da instrução será iniciada. Primeiro, o MySQL precisa saber o que você deseja fazer, por isso precisa analisar a instrução SQL.

otimizador

Atuador do

2. Sistema de registro: como uma instrução de atualização SQL é executada?

refazer log

Não sei se você ainda se lembra do artigo “Kong Yiji”. O gerente do hotel possui um quadro rosa especialmente usado para registrar os registros de crédito dos hóspedes. Se não houver muitas pessoas que paguem a crédito, ele poderá escrever o nome e a conta do cliente no quadro. Mas se houver muitas pessoas com contas de crédito, sempre haverá momentos em que o conselho de fãs não conseguirá acompanhá-las. Nesse momento, o lojista deve ter um livro-razão específico para registrar contas de crédito.

Se alguém quiser quitar um crédito ou quitar uma dívida, o lojista geralmente tem duas opções:

• Uma maneira é abrir diretamente o razão e adicionar ou subtrair a conta de crédito;
• Outra abordagem éPrimeiro anote as contas desta vez no quadro rosa e, em seguida, retire os livros contábeis após o horário de fechamento e calcule-os.。

Quando os negócios estão crescendo e o balcão está ocupado, o lojista com certeza escolheráesta última , porque a operação anterior é muito problemática. Primeiro, você precisa encontrar o registro do crédito total da conta dessa pessoa. Pense bem, são dezenas de páginas densamente compactadas. Para encontrar o nome, o lojista pode ter que colocar óculos de leitura e pesquisar lentamente. Depois de encontrá-lo, ele retirará o ábaco para calcular e, finalmente, escreverá o resultado novamente. o livro razão.

Todo esse processo é difícil de pensar. Por outro lado, é mais fácil anotar primeiro no quadro rosa. Pense bem, se o lojista não tiver a ajuda do quadro rosa, ele terá que virar o livro toda vez que registrar as contas. A eficiência não é insuportavelmente baixa?

Da mesma forma, esse problema também existe no MySQL. Se cada operação de atualização precisar ser gravada no disco, e o disco também precisar encontrar o registro correspondente antes da atualização, o custo de IO e o custo de pesquisa de todo o processo serão muito altos. Para resolver esse problema, os projetistas do MySQL usaram uma ideia semelhante ao quadro rosa do lojista do hotel para melhorar a eficiência da atualização.

Todo o processo de cooperação entre o quadro rosa e o livro-razão é, na verdade, o que é frequentemente mencionado no MySQL. WAL tecnologia,WAL O nome completo éWrite-Ahead Logging, o ponto chave éEscreva o log primeiro e depois grave no disco, isto é, escreva primeiro o quadro rosa e depois escreva o livro de contas quando não estiver ocupado.

Especificamente, quando um registro precisa ser atualizado, o mecanismo InnoDB primeiro grava o registro no redo log (quadro rosa) e atualiza a memória. Ao mesmo tempo, o mecanismo InnoDB atualizará o registro da operação no disco no momento apropriado, e essa atualização geralmente é feita quando o sistema está relativamente ocioso, assim como o lojista faz após fechar.

Se hoje não houver muitas contas de crédito, o lojista pode esperar até a hora de fechar para separar os itens. Mas o que devemos fazer se houver muitas contas de crédito em um determinado dia e o quadro rosa estiver cheio? Nesse momento, o lojista não teve escolha a não ser abandonar seu trabalho, atualizar alguns dos registros de crédito do quadro rosa no livro-razão e, em seguida, apagar esses registros do quadro rosa para abrir espaço para novas contas.

Da mesma forma, o redo log do InnoDB tem um tamanho fixo. Por exemplo, ele pode ser configurado como um conjunto de 4 arquivos, cada arquivo tem 1 GB de tamanho. Então, esta "placa rosa" pode registrar um total de 4 GB de operações. Comece a escrever desde o início e depois volte ao início para escrever em loop, como mostra a imagem abaixo.

write pos é a posição do registro atual. Ele retrocede durante a gravação. Depois de escrever no final do arquivo nº 3, ele retorna ao início do arquivo nº 0. O ponto de verificação é a posição atual a ser apagada e também avança e faz loop. Antes de apagar o registro, o registro deve ser atualizado no arquivo de dados.

O espaço entre a posição de gravação e o ponto de verificação é a parte vazia do “quadro rosa” que pode ser usada para registrar novas operações. Se a posição de gravação alcançar o ponto de verificação, significa que o "quadro rosa" está cheio e nenhuma nova atualização pode ser realizada neste momento. Você deve parar e apagar alguns registros primeiro para avançar no ponto de verificação.

Com o redo log, o InnoDB pode garantir que, mesmo que o banco de dados seja reiniciado de forma anormal, os registros enviados anteriormente não serão perdidos.crash-safe。

Para entender o conceito de segurança contra colisões, pense em nosso exemplo anterior de registro de crédito. Desde que o registro de crédito esteja registrado no quadro rosa ou escrito no livro-razão, mesmo que o lojista o esqueça mais tarde, como suspender repentinamente o negócio por alguns dias, ele ainda pode esclarecer a conta de crédito por meio dos dados do livro-razão e placa rosa após retomar os negócios.

log binário

Como mencionamos antes, o MySQL como um todo tem na verdade duas partes: uma é a camada do servidor, que faz principalmente as coisas no nível funcional do MySQL, a outra é a camada do mecanismo, que é responsável por assuntos específicos relacionados ao armazenamento;O quadro rosa de que falamos acimaredo log é um log exclusivo do mecanismo InnoDB,e A camada Servidor também possui seu próprio log, chamado binlog (log de arquivo)。

Acho que você vai perguntar: por que existem dois logs?

Porque não havia mecanismo InnoDB no MySQL no início. O próprio mecanismo do MySQL é o MyISAM, mas o MyISAM não possui recursos à prova de falhas e os logs do binlog só podem ser usados ​​para arquivamento. O InnoDB foi introduzido no MySQL na forma de um plug-in por outra empresa. Como depender apenas do binlog não possui recursos de segurança contra falhas, o InnoDB usa outro sistema de log, ou seja, redo log, para obter recursos de segurança contra falhas.

Esses dois logs têm as três diferenças a seguir.

1. O redo log é exclusivo do mecanismo InnoDB; o binlog é implementado pela camada do servidor do MySQL e pode ser usado por todos os mecanismos.
2. redo log é um log físico, registra “quais modificações foram feitas em determinada página de dados”;binlog é um log lógico, o que fica registrado é a lógica original desta instrução, como "adicione 1 ao campo c da linha com ID=2".
3. redo log é escrito em um loop, o espaço será esgotado;binlog pode ser escrito adicionalmente . "Anexar gravação" significa que depois que o arquivo binlog atingir um determinado tamanho, ele mudará para o próximo e não substituirá o log anterior.

Com uma compreensão conceitual desses dois logs, vamos dar uma olhada nos processos internos do executor e do mecanismo InnoDB ao executar esta simples instrução de atualização.

1. O executor primeiro procura o mecanismo para obter a linha ID=2. ID é a chave primária e o mecanismo usa diretamente a pesquisa em árvore para encontrar essa linha. Se a página de dados onde está localizada a linha com ID=2 já estiver na memória, ela será retornada diretamente ao executor, caso contrário, ela precisará ser lida primeiro na memória do disco e depois retornada;
2. O executor obtém os dados da linha fornecidos pelo mecanismo, adiciona 1 a esse valor, por exemplo, costumava ser N, mas agora é N+1, obtém uma nova linha de dados e então chama a interface do mecanismo para escrever isso nova linha de dados.
3. O mecanismo atualiza esta nova linha de dados na memória e registra a operação de atualização no redo log neste momento. refazer log empreparar estado. Em seguida, informe ao executor que a execução foi concluída e a transação pode ser enviada a qualquer momento.
4. O executor gera um binlog desta operação e coloca binlog gravado no disco。
5. O executor chama a interface de transação de commit do mecanismo e o mecanismo grava o refazer log Alterar para enviar (comprometer-se), a atualização será concluída.

Aqui eu forneço o fluxograma de execução desta instrução de atualização. A caixa clara na figura indica que ela é executada dentro do InnoDB, e a caixa escura indica que ela é executada no executor.

processo de execução de instrução de atualização

Você deve ter notado que as últimas três etapas parecem um pouco "circulares". A escrita do redo log é dividida em duas etapas: preparar e confirmar. Este é o "commit em duas fases".

commit em duas fases

Por que é necessária a “submissão em duas fases”?Isso é para permitir a diferença entre os dois logslogicamente consistente . Para explicar esse problema, temos que começar com a pergunta do início do artigo: Como restaurar o banco de dados ao estado de qualquer segundo em meio mês?

Como dissemos antes, o binlog registrará todas as operações lógicas e adotará a forma de “escrita de anexos”. Se o seu DBA prometer que pode ser restaurado dentro de meio mês, o sistema de backup definitivamente salvará todos os logs binários do último meio mês e executará backups regulares de todo o banco de dados. O “regular” aqui depende da importância do sistema, que pode ser uma vez por dia ou uma vez por semana.

Quando você precisar restaurar para um segundo especificado, por exemplo, às duas horas da tarde de um dia, descobrir que uma tabela foi excluída acidentalmente ao meio-dia e precisar recuperar os dados, você pode fazer o seguinte:

• Primeiro, encontre o backup completo mais recente. Se tiver sorte, pode ser um backup da noite anterior e restaure esse backup para o banco de dados temporário;
• Em seguida, a partir do ponto no tempo de backup, os logs binários de backup são retirados em sequência e reproduzidos até o horário anterior à exclusão acidental da tabela ao meio-dia.
  Dessa forma, seu banco de dados temporário será igual ao banco de dados online antes de você excluí-lo acidentalmente. Em seguida, você poderá retirar os dados da tabela do banco de dados temporário e restaurá-los no banco de dados online conforme necessário.

Ok, depois de falar sobre o processo de recuperação de dados, vamos voltar e falar sobre por que o log precisa de "confirmação em duas fases". Aqui poderíamos também usar a prova por contradição para explicar.

Como o redo log e o binlog são duas lógicas independentes, se o commit de duas fases não for usado, o redo log deve ser gravado primeiro e depois o binlog deve ser gravado, ou a ordem inversa deve ser adotada. Vamos ver quais problemas existem com esses dois métodos.

Ainda use a instrução de atualização anterior como exemplo. Suponha que o valor do campo c na linha atual com ID = 2 seja 0 e suponha que durante a execução da instrução de atualização, ocorra uma falha após a gravação do primeiro log, mas antes da gravação do segundo log.

• Escreva primeiro o redo log e depois o binlog.
  Suponha que o processo MySQL seja reiniciado de forma anormal quando o redo log for gravado, mas antes do binlog ser gravado. Como dissemos antes, após a gravação do redo log, mesmo que o sistema trave, os dados ainda podem ser restaurados, portanto, o valor de c nesta linha após a recuperação é 1. No entanto, como o log binário travou antes de ser concluído, esta declaração não foi registrada no log binário neste momento. Portanto, quando o backup do log for feito posteriormente, esta instrução não será incluída no log binário salvo. Então você descobrirá que se precisar usar este log binário para restaurar a biblioteca temporária, porque o log binário desta instrução foi perdido, a biblioteca temporária não será atualizada desta vez. O valor de c na linha restaurada é 0, que é. o mesmo que o valor da biblioteca original diferente.
• Escreva o binlog primeiro e depois refaça o log.
  Se houver uma falha após a gravação do binlog, uma vez que o redo log ainda não foi gravado, a transação será inválida após a recuperação da falha, portanto o valor de c nesta linha é 0. Mas o log "Alterar c de 0 para 1" foi registrado no binlog. Portanto, quando o binlog for usado para restaurar posteriormente, mais uma transação será lançada. O valor de c na linha restaurada é 1, que é diferente do valor no banco de dados original.
  Pode-se observar que se o "two-phase commit" não for utilizado, o estado do banco de dados pode ser inconsistente com o estado da biblioteca restaurada usando seu log.

Você pode perguntar: essa probabilidade é muito baixa? Não há situações em que a biblioteca temporária precise ser restaurada a qualquer momento.

Na verdade não, este processo não é necessário apenas para recuperar dados após operação incorreta. Quando você precisar expandir a capacidade, ou seja, quando precisar construir mais bancos de dados standby para aumentar a capacidade de leitura do sistema, a prática comum agora é usar backup completo e aplicar binlog para conseguir isso. uma inconsistência entre os bancos de dados mestre e escravo online.

Simplificando, tanto o redo log quanto o binlog podem ser usados ​​para representar o status de commit de uma transação, eA submissão em duas fases visa manter os dois estados logicamente consistentes.。