minhas informações de contato
Correspondência[email protected]
2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
O conteúdo dos logs do MySQL é muito importante e é frequentemente questionado durante entrevistas. Ao mesmo tempo, dominar o conhecimento relacionado ao log também nos ajudará a compreender os princípios subjacentes do MySQL e a solucionar problemas quando necessário.
Os tipos de log comuns no MySQL incluem principalmente as seguintes categorias (para o mecanismo de armazenamento InnoDB):
O log binário (binlog) e o log de transações (redo log e undo log) são mais importantes e exigem nosso foco.
O log de consulta lenta registra todas as instruções de consulta cujo tempo de execução excede long_query_time (o padrão é 10s, geralmente definido como 1s). É frequentemente usado ao resolver problemas de consulta lenta de SQL (o tempo de execução de SQL é muito longo).
Encontrar SQL lento é o primeiro passo para otimizar o desempenho das instruções SQL. Em seguida, use o comando EXPLAIN para analisar o SQL lento e obter informações sobre o plano de execução.
Você pode usar as variáveis show como o comando "slow_query_log" para verificar se o log de consulta lenta está ativado por padrão.
Pode ser ativado por SET GLOBAL slow_query_log=ON
O parâmetro long_query_time define quanto tempo uma consulta leva antes que ela possa ser definida como uma consulta lenta. O padrão é 10s. Você pode visualizá-lo através do comando SHOW VARIABLES LIKE'%long_query_time%';
Também pode ser modificado: set global long_query_time = 12
Em projetos reais, o log de consulta lenta pode ser relativamente grande e é inconveniente analisá-lo diretamente. Podemos usar a ferramenta oficial de análise e ajuste de consulta lenta do MySQL. mysqldumps lento . Meu blog também está simplesmente conectado à ferramenta mysqldumpslow:
[MySQL] ferramenta mysqldumpslow - resumindo arquivos de log de consulta lenta-CSDN Blog
Existe uma variável no MySQL que registra o número atual de instruções de consulta lenta. Você pode usar show global status como'%slo.
comando w_queries%';
MySQL nos forneceEXPLICARcomando para obter informações sobre o plano de execução.
O plano de execução refere-se ao método de execução específico de uma instrução SQL após ser otimizada pelo otimizador de consulta MySQL. Os planos de execução geralmente são usados em cenários como análise e otimização de desempenho SQL. Através dos resultados do EXPLAIN, você pode aprender informações como a sequência de consulta da tabela de dados, o tipo de operação da operação de consulta de dados, quais índices podem ser atingidos, quais índices serão realmente atingidos, quantas linhas de registros em cada dados tabela são consultadas e outras informações. Especificamente, podemos otimizar o SQL por meio de alguns métodos comuns abaixo:
1. Evite usar SELECT *
SELECT * consome mais CPU.
SELECT *Campos inúteis aumentam o consumo de recursos de largura de banda da rede e o tempo de transmissão de dados, especialmente campos grandes (como varchar,
bolha, texto)。
SELECT * não pode usar o otimizador MySQL para cobrir a otimização do índice (a estratégia de "índice de cobertura" baseada no otimizador MySQL é extremamente rápida e eficiente e é um método de otimização de consulta altamente recomendado na indústria)
SELECT <lista de campos> pode reduzir o impacto das alterações na estrutura da tabela.
2. Otimização da paginação
A paginação comum leva um tempo relativamente curto quando a quantidade de dados é pequena.
Se a quantidade de dados aumentar, atingindo milhões ou até dezenas de milhões, a paginação comum demorará muito.
Como otimizar? Você pode modificar a instrução SQL acima em uma subconsulta.
Primeiro consultamos o valor da chave primária correspondente ao primeiro parâmetro de limite e, em seguida, filtramos e limitamos com base nesse valor da chave primária, para que a eficiência seja mais rápida. Porém, este método só funciona se os ids estiverem em ordem positiva.
No entanto, o resultado da subconsulta gerará uma nova tabela, o que afetará o desempenho. Deve-se evitar o uso extensivo de subconsultas. Além disso, este método só é aplicável quando o ID está em sequência positiva. Em cenários de paginação complexos, muitas vezes é necessário filtrar os IDs que atendem às condições por meio de condições de filtragem. Neste momento, os IDs são discretos e descontínuos.
3. Faça menos junções
Manual de desenvolvimento do Alibaba:
Você pode ler a discussão em Zhihu:
https://www.zhihu.com/question/68258877
https://www.zhihu.com/question/682588774. Recomenda-se não usar chaves estrangeiras e cascatas
Manual de desenvolvimento Java do Alibaba:
5. Escolha o tipo de campo apropriado
6. Tente usar UNION ALL em vez de UNION
UNION colocará todos os dados dos dois conjuntos de resultados em uma tabela temporária e, em seguida, executará a operação de desduplicação, que é mais demorada e consome mais recursos da CPU.
UNION ALL não desduplicará mais o conjunto de resultados e os dados obtidos contêm itens duplicados.
No entanto, se dados duplicados não forem permitidos no cenário comercial real, UNION ainda poderá ser usado.
7. Operações em lote
Para atualizações de dados no banco de dados, se operações em lote puderem ser usadas, use-as tanto quanto possível para reduzir o número de solicitações de banco de dados e melhorar o desempenho.
8. Use índices corretamente
Há muito conteúdo nesta seção, que será apresentado em um blog separado posteriormente.
binlog (log binário é um arquivo de log binário) registra principalmente todas as operações que foram alteradas no banco de dados MSQL (todas as instruções DDL e DML executadas pelo banco de dados), incluindo alterações na estrutura da tabela (CREATE, ALTER, DROP TABLE.), dados da tabela modificações ( INSERT.UPDATE, DELETE..), mas não inclui SELECT, SHOW e outras operações que não causem alterações no banco de dados.
Você pode usar o comando show binary logs para visualizar uma lista de todos os logs binários:
Existem 3 tipos de métodos de gravação binária:
Comparado ao modo Row, o arquivo de log no modo de instrução é menor, a pressão de E/S do disco também é menor e o desempenho é melhor. No entanto, sua precisão é pior que o modo Row.
Antes do MySQL 5.1.5, o formato do binlog era apenas STATEMENT 5.1.5 começou a suportar binlog no formato ROW A partir da versão 5.1.8, o MySQL começou a suportar binlog no formato MIXED. Antes do MySQL 5.7.7, o modo Statement era usado por padrão. MySQL5.7.7 usa o modo Row por padrão.
Você pode usar variáveis de exibição como '%binlog format%' para visualizar o formato usado pelo binlog;
O principal cenário de aplicação do binlog é a replicação mestre-escravo, mestre-mestre e mestre-escravo, todos inseparáveis do binlog, que precisam ser confiáveis para sincronizar os dados e garantir a consistência dos dados.
O princípio da replicação mestre-escravo é mostrado na figura abaixo:
1. A biblioteca principal grava as alterações nos dados do banco de dados no log binário
2. Conecte a biblioteca escrava à biblioteca principal
3. A biblioteca escrava criará um thread I0 para solicitar o log binário atualizado da biblioteca principal.
4. A biblioteca principal criará um encadeamento de despejo de log binário para enviar o log binário, e o encadeamento de E/0 na biblioteca escrava será responsável pelo recebimento. 5. O encadeamento de E/0 da biblioteca escrava gravará o log binário recebido no relé. registro.
6. Leia o log de retransmissão do thread SQL da biblioteca e sincronize os dados localmente (ou seja, execute o SQL novamente)
Para o mecanismo de armazenamento InnoDB, durante a execução de uma transação, o log será primeiro gravado no binlogcache. Somente quando a transação for enviada, o log no binlogcache será persistido no arquivo binlog no disco. Gravar na memória é mais rápido e isso também é feito por razões de eficiência.
Como o log binário de uma transação não pode ser dividido, não importa o tamanho da transação, ela deve ser gravada uma vez, para que o sistema aloque um bloco de memória para cada thread como cache do log binário. Podemos controlar o tamanho do binlogcache de um único thread através do parâmetro binlog_cache_size. Se o conteúdo do armazenamento exceder este parâmetro, ele deverá ser armazenado temporariamente no disco (swap).
Então, quando o binlog é liberado para o disco? Você pode controlar o tempo de liberação do biglog por meio do parâmetro sync_binlog. O intervalo de valores é 0-N e o padrão é 0:
·0: Não há requisito obrigatório, o sistema decidirá quando escrever no disco.
·1: Cada vez que uma transação é submetida, o binlog deve ser gravado no disco:
·N: O binlog será gravado no disco a cada N transações.Risco de perda
Antes do MySQL5.7, o valor padrão de sync_binlog era 0. Após o MySQL5.7, o valor padrão de sync_binlog é 1. Geralmente, não é recomendado definir o valor de sync_binlog como 0. Se os requisitos de desempenho forem relativamente altos ou ocorrer um gargalo de E/S de disco, o valor de sync_binlog poderá ser aumentado adequadamente. No entanto, isso aumentará o risco de perda de dados.
Ao encontrar as três situações a seguir, o MySQL irá gerar novamente um novo arquivo de log e o número de série do arquivo será incrementado.
Sabemos que o mecanismo de armazenamento InnoD8 gerencia o espaço de armazenamento em unidades de páginas. Os dados que inserimos no MySQL, em última análise, existem na página. Para reduzir a sobrecarga de E/S do disco, existe também uma área chamada Buffer Pool, que existe na memória. Quando a página correspondente aos nossos dados não existe no Buffer Pool, o MSQL primeiro armazenará em cache a página no disco no Buffer Pool, para que mais tarde operemos diretamente a página no Buffer Pool, o que melhora muito o desempenho de leitura e gravação .
Depois que uma transação é confirmada, nossas modificações na página correspondente no Buffer Pool podem não ser persistidas no disco. Neste momento, se o MySQL travar repentinamente, as alterações nesta transação desaparecerão diretamente?
Obviamente que não, se assim for, violaria obviamente a durabilidade da transação.
O mecanismo MySQLInnoDB usa redo log para garantir a durabilidade da transação. A principal coisa que o redo log faz é registrar as modificações da página, como quantos bytes foram modificados em um determinado deslocamento em uma determinada página e qual é o conteúdo modificado específico. Cada registro no redo log contém o número do espaço de tabela, número da página de dados, deslocamento, dados modificados específicos e pode até registrar o comprimento dos dados modificados (dependendo do tipo de redo log).
Quando a transação for confirmada, iremos liberar o redo log para o disco de acordo com a estratégia de liberação. Dessa forma, mesmo se o MySQL travar, os dados que não foram gravados no disco podem ser recuperados após a reinicialização, garantindo assim a durabilidade. da transação. Em outras palavras, o redo log oferece recursos de recuperação de falhas do MySQL.
O Redo Log registra todas as operações de modificação no banco de dados. Quando o banco de dados realiza operações de gravação (INSERT, UPDATE, DELETE), essas operações serão primeiro registradas no Redo Log e depois aplicadas ao arquivo de dados. Dessa forma, mesmo que o sistema falhe antes que a operação de modificação de dados seja completamente gravada no disco, o Redo Log pode garantir que os dados não sejam perdidos. Durante a recuperação, o banco de dados refaz essas operações de modificação inacabadas do Redo Log para garantir a consistência dos dados.
O Redo Log ajuda o banco de dados a se recuperar para um estado consistente após uma falha do sistema ou uma queda de energia inesperada. Durante o processo de recuperação, o banco de dados verificará os registros no Redo Log e reaplicará todas as modificações de dados enviadas, mas não persistentes, aos arquivos de dados para recuperar os dados.
Para melhorar o desempenho das operações de gravação, os bancos de dados geralmente usam mecanismos de cache (como buffer pools) para armazenar temporariamente as operações de modificação na memória, em vez de gravá-las imediatamente no disco. A existência do Redo Log torna esse mecanismo de cache possível, pois desde que o Redo Log seja persistido, não há risco de perda de dados, mesmo que os dados no cache não tenham sido gravados no disco.
Ao confirmar uma transação, o redo log no buffer de log será liberado para o disco. Você pode usar innodb_flush_log_at.
confirmar controle de parâmetro. Devemos prestar atenção à configuração da política de liberação correta innodb_flush_log_at_trx_commit Dependendo da estratégia de liberação configurada no MySQL, pode haver pequenos problemas de perda de dados após o MySQL cair.
innodb_flush_log_at_trx_commit É um parâmetro de configuração importante no mecanismo de armazenamento MySQL InnoDB. Ele determina as estratégias de liberação (liberação) e gravação (gravação) do log quando uma transação é enviada, afetando assim a durabilidade e o desempenho dos dados. Possui três valores, nomeadamente 0, 1 e 2. Cada valor representa uma estratégia de escovagem diferente.
innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_trx_commit = 2
Resumo da estratégia de pincel
1. O redo log é gravado no buffer de log, mas ainda não foi gravado no cache da página. Neste momento, o banco de dados trava e ocorre perda de dados (essa perda de dados pode ocorrer quando o valor da política de liberação innodb_flush log_at trx_commit é. 0);
2. O redo log foi gravado no cache da página, mas ainda não foi gravado no disco. O sistema operacional travará e poderá ocorrer perda de dados (essa perda de dados pode ocorrer quando o valor da política de liberação innodb2 flush log_at trx_commit for. 2).
Undo Log (log de reversão) é um log usado no sistema de banco de dados para registrar operações de modificação de dados. Ele registra as operações reversas (ou seja, operações de desfazer) de todas as operações de modificação em dados durante a execução da transação. O Undo Log desempenha um papel fundamental na reversão da transação. Por meio do Undo Log, os dados podem ser restaurados ao estado anterior ao início da transação, garantindo assim a atomicidade da transação.
A atomicidade de uma transação significa que todas as operações da transação são todas executadas ou nenhuma é executada. O Undo Log garante a atomicidade das transações através dos seguintes mecanismos:
Gravar operações de desfazer Durante a execução da transação, qualquer operação de modificação nos dados registrará a operação de desfazer correspondente no Log de Desfazer antes da modificação real. Por exemplo, se uma transação atualizar o valor de uma linha de registros, o valor antigo será registrado no Undo Log antes da atualização.
Transação de reversão Se a transação falhar por algum motivo (como um erro ou reversão explícita), o sistema de banco de dados lê o log de desfazer e restaura os dados ao estado anterior ao início da transação, de acordo com a operação de desfazer registrada. Dessa forma, você pode garantir que a transação com falha não terá impacto no banco de dados, garantindo assim a atomicidade da transação.
Citar:
Explicação detalhada da separação leitura-gravação e subbanco de dados e tabela JavaGuide |
Ele se dedica à pesquisa de tecnologia há mais de 30 anos e é proficiente em diversas linguagens como java, linux, javascript, php, css, etc. estação de documentação do desenvolvedor para compartilhar alguns problemas no desenvolvimento de tecnologia para referência futura.
Correspondência[email protected]