Otimização do sistema de front-end e back-end em banco de dados MySQL

Dicas:

1. Como detectar afirmações ineficientes?

No MySQL, definindo --log-slow-queries=[nome do arquivo] nos parâmetros de inicialização, você pode registrar instruções SQL cujo tempo de execução excede long_query_time (o padrão é 10 segundos) no arquivo de log especificado. Você também pode modificar o tempo de consulta longo no arquivo de configuração de inicialização, como:

# Defina o tempo de consulta longo para 8 segundos

long_query_time=8

2. Como consultar o índice de uma tabela?

Você pode usar a instrução SHOW INDEX, como:

SHOW INDEX FROM [nome da tabela]

3. Como consultar o uso do índice de uma determinada instrução?

Você pode usar a instrução EXPLAIN para ver o uso do índice de uma determinada instrução SELECT. Se for uma instrução UPDATE ou DELETE, ela precisará primeiro ser convertida em uma instrução SELECT.

4. Como exportar o conteúdo do mecanismo INNODB para o arquivo de log de erros.

Podemos usar o comando SHOW INNODB STATUS para visualizar muitas informações úteis sobre o mecanismo INNODB, como o processo atual, transações, erros de chave estrangeira, impasse

problemas e outras estatísticas. Como permitir que essas informações sejam registradas no arquivo de log? Contanto que você crie a tabela innodb_monitor usando a seguinte instrução, o MySQL gravará o sistema no arquivo de log de erros a cada 15 segundos:

Se você não precisar mais exportar para o arquivo de log de erros

;

, basta excluir Esta tabela pode ser:

DROP TABLE innodb_monitor

5. Como excluir arquivos de log enormes regularmente?

Basta definir o tempo de expiração do log no arquivo de configuração de inicialização:

expire_logs_days=10

Notas:

1. Concentre-se no índice

. A tabela TSK_TASK é utilizada para salvar tarefas de monitoramento do sistema. Os campos e índices relevantes são os seguintes:

ID: chave primária

MON_TIME: tempo de monitoramento construído

;

Nota: O MySQL criará automaticamente índices para chaves estrangeiras Durante este processo de otimização, descobriu-se que esses índices de chaves estrangeiras criados automaticamente causarão interferência desnecessária na eficiência das instruções SQL.

Primeiro, descobrimos no arquivo de log que a execução da seguinte instrução foi relativamente lenta, mais de 10 segundos:

# Query_time: 18 Lock_time: 0 Rows_sent: 295 Rows_examined: 88143

select * from TSK_TASK WHERE STATUS_ID = 1064 and MON_TIME >= ' 2007-11-22' e MON_TIME < '2007-11-23'

Acontece que é necessário encontrar 295 registros que atendam às condições entre 88.143 registros, o que é obviamente lento. Use rapidamente a instrução EXPLAIN para verificar o uso do índice:

+----+-------------+----------+------+- ---------

| id | select_type | tipo_chaves | chave_len | linhas extras

| ----------+------+----------

| 1 | TSK_TASK | FK_task_status_id_TO_SYS_HIER_INFO,TSK_TASK_KEY_MON_TIME

| +-------------+----------+------+--------- --Pode

-se ver que há há dois índices disponíveis: FK_task_status_id_TO_SYS_HIER_INFO, TSK_TASK_KEY_MON_TIME, e o índice de chave estrangeira em STATUS_ID é usado quando a instrução é finalmente executada.

Vamos dar outra olhada no índice da tabela TSK_TASK:

+----------+-------------------------- -- --------

|

Nome_coluna |

-- ---------------

| TSK_TASK | PRIMÁRIO |

999149

|

No Oracle ou em outros bancos de dados relacionais, condições WHERE A ordem dos campos no índice desempenha um papel importante na seleção de o índice. Vamos ajustar a ordem dos campos, colocar STATUS_ID no final e EXPLAIN novamente:

EXPLAIN select * from TSK_TASK WHERE MON_TIME >= '2007-11-22' e MON_TIME < '2007-11-23' e STATUS_ID = 1064

; sem efeito, o MySQL ainda usa o índice de chave estrangeira STATUS_ID criado pelo sistema.

Após uma análise cuidadosa, parece que o atributo Cardinalidade (ou seja, o número de valores únicos no índice) desempenha um papel extremamente importante na seleção do índice. O MySQL seleciona o índice com o menor número de valores únicos. no índice como o índice de toda a instrução.

Para esta instrução, se você usar FK_task_status_id_TO_SYS_HIER_INFO como índice e a tabela TSK_TASK armazenar dados por muitos dias, o número de registros verificados será grande e a velocidade será lenta. Existem várias soluções de otimização disponíveis:

Se não houver muitas tarefas em um dia, excluímos o índice FK_task_status_id_TO_SYS_HIER_INFO, então o MySQL usará o índice TSK_TASK_KEY_MON_TIME, e então verificará os registros com STATUS_ID 1064 nos dados daquele dia, o que não é lento ;

Se houver muitas tarefas em um dia, precisamos excluir os índices FK_task_status_id_TO_SYS_HIER_INFO e TSK_TASK_KEY_MON_TIME e, em seguida, criar um índice conjunto de STATUS_ID, MON_TIME, que certamente será muito eficiente.

Portanto, recomenda-se não utilizar chaves estrangeiras para tabelas com grande número de registros para evitar séria redução na eficiência do desempenho.

2. Tente controlar o número de registros em cada tabela.

Quando o número de registros em uma tabela for grande, o gerenciamento e a manutenção serão muito problemáticos. Por exemplo, a manutenção do índice levará muito tempo, o que causará grandes interrupções no sistema. interferência normal do sistema.

Para tabelas cujo volume de dados continua a crescer ao longo do tempo, podemos distinguir dados em tempo real e dados históricos com base no tempo. Podemos usar o programa de serviço em segundo plano para mover regularmente os dados da tabela em tempo real para a tabela histórica, controlando assim. o número de registros na tabela em tempo real e melhorando o desempenho da consulta e a eficiência operacional. Mas observe que o tempo de cada movimento deve ser curto o suficiente para não afetar a escrita dos dados do programa normal. Se demorar muito, poderá causar um problema de impasse.

3. Estratégia de hashing (partição) de dados:

Quando o número de clientes atinge uma determinada escala, um único banco de dados não será capaz de suportar maior acesso simultâneo. Neste momento, você pode considerar o hash (particionamento) dos dados do cliente em vários bancos de dados. compartilhe a carga. Melhore o desempenho geral e a eficiência do sistema.