Práticas recomendadas para solucionar problemas do Banco de Dados do Azure para MySQL - Servidor Flexível

APLICA-SE A: Banco de Dados do Azure para MySQL - Servidor Único Banco de Dados do Azure para MySQL - Servidor Flexível

Use as seções a seguir para manter seus bancos de dados de servidor flexíveis do Banco de Dados do Azure para MySQL funcionando sem problemas e use essas informações como princípios orientadores para garantir que os esquemas sejam projetados de forma otimizada e forneçam o melhor desempenho para seus aplicativos.

Verifique o número de índices

Em um ambiente de banco de dados ocupado, você pode observar alto uso de E/S, o que pode ser um indicador de padrões de acesso a dados insatisfatórios. Os índices não utilizados podem ter um impacto negativo no desempenho, uma vez que consomem espaço em disco e cache, e tornam mais lentas as operações de escrita (INSERT / DELETE / UPDATE). Os índices não utilizados consomem desnecessariamente mais espaço de armazenamento e aumentam o tamanho do backup.

Antes de remover qualquer índice, certifique-se de reunir informações suficientes para verificar se ele não está mais em uso. Essa verificação pode ajudá-lo a evitar a remoção inadvertida de um índice que é crítico para uma consulta que é executada apenas trimestralmente ou anualmente. Além disso, certifique-se de considerar se um índice é usado para impor exclusividade ou ordenação.

SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name;

(ou)

use information_schema; select tables.table_name, statistics.index_name, statistics.cardinality, tables.table_rows from tables join statistics on (statistics.table_name = tables.table_name and statistics.table_schema = '<YOUR DATABASE NAME HERE>' and ((tables.table_rows / statistics.cardinality) > 1000));

Listar os índices mais ocupados no servidor

A saída da consulta a seguir fornece informações sobre os índices mais usados em todas as tabelas e esquemas no servidor de banco de dados. Essas informações são úteis para identificar a proporção de gravações para leituras em relação a cada índice e os números de latência para leituras, bem como operações de gravação individuais, o que pode indicar que é necessário um ajuste adicional em relação à tabela subjacente e às consultas dependentes.

SELECT 
object_schema AS table_schema, 
object_name AS table_name, 
index_name, count_star AS all_accesses, 
count_read, 
count_write, 
Concat(Truncate(count_read / count_star * 100, 0), ':', 
Truncate(count_write / count_star * 100, 0)) AS read_write_ratio, 
 count_fetch AS rows_selected , 
 count_insert AS rows_inserted, 
 count_update AS rows_updated, 
 count_delete AS rows_deleted, 
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency , 
 Concat(Round(sum_timer_fetch / 1000000000000, 2), ' s') AS select_latency, 
 Concat(Round(sum_timer_insert / 1000000000000, 2), ' s') AS insert_latency, 
Concat(Round(sum_timer_update / 1000000000000, 2), ' s') AS update_latency, 
 Concat(Round(sum_timer_delete / 1000000000000, 2), ' s') AS  delete_latency 
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE index_name IS NOT NULL AND count_star > 0 
ORDER BY sum_timer_wait DESC 

Revisar o design da chave primária

O servidor flexível do Banco de Dados do Azure para MySQL usa o mecanismo de armazenamento InnoDB para todas as tabelas não temporárias. Com o InnoDB, os dados são armazenados dentro de um índice clusterizado usando uma estrutura B-Tree. A tabela é organizada fisicamente com base em valores de chave primária, o que significa que as linhas são armazenadas na ordem da chave primária.

Cada entrada de chave secundária em uma tabela InnoDB contém um ponteiro para o valor da chave primária na qual os dados são armazenados. Em outras palavras, uma entrada de índice secundária contém uma cópia do valor da chave primária para o qual a entrada está apontando. Portanto, as opções de chave primária têm um efeito direto na quantidade de sobrecarga de armazenamento em suas tabelas.

Se uma chave é derivada de dados reais (por exemplo, nome de usuário, e-mail, SSN, etc.), ela é chamada de chave natural. Se uma chave for artificial e não derivada de dados (por exemplo, um inteiro incrementado automaticamente), ela será chamada de chave sintética ou chave substituta.

Geralmente é recomendado evitar o uso de chaves primárias naturais. Essas chaves geralmente são muito amplas e contêm valores longos de uma ou várias colunas. Isso, por sua vez, pode introduzir uma sobrecarga de armazenamento severa com o valor da chave primária sendo copiado em cada entrada de chave secundária. Além disso, as chaves naturais geralmente não seguem uma ordem predeterminada, o que reduz drasticamente o desempenho e provoca fragmentação da página quando as linhas são inseridas ou atualizadas. Para evitar esses problemas, use chaves substitutas monotonicamente crescentes em vez de chaves naturais. Uma coluna inteira (grande) de incremento automático é um bom exemplo de uma chave substituta monotonicamente crescente. Se você precisar de uma determinada combinação de colunas, seja exclusivo, declare essas colunas como uma chave secundária exclusiva.

Durante os estágios iniciais de criação de um aplicativo, você pode não pensar com antecedência para imaginar um momento em que sua tabela começa a se aproximar de ter dois bilhões de linhas. Como resultado, você pode optar por usar um inteiro de 4 bytes assinado para o tipo de dados de uma coluna ID (chave primária). Certifique-se de verificar todas as chaves primárias da tabela e alternar para usar colunas inteiras de 8 bytes (BIGINT) para acomodar o potencial de um alto volume ou crescimento.

Usar índices de cobertura

A seção anterior explica como os índices no MySQL são organizados como B-Trees e em um índice agrupado, os nós de folha contêm as páginas de dados da tabela subjacente. Os índices secundários têm a mesma estrutura de árvore B que os índices clusterizados, e você pode defini-los em uma tabela ou exibição com um índice clusterizado ou um heap. Cada linha de índice no índice secundário contém o valor de chave não clusterizada e um localizador de linha. Esse localizador aponta para a linha de dados no índice clusterizado ou heap com o valor da chave. Como resultado, qualquer pesquisa envolvendo um índice secundário deve navegar a partir do nó raiz através dos nós de ramificação até o nó folha correto para obter o valor da chave primária. Em seguida, o sistema executa uma leitura aleatória de E/S no índice de chave primária (mais uma vez navegando do nó raiz através dos nós de ramificação até o nó folha correto) para obter a linha de dados.

Para evitar essa leitura de E/S aleatória extra no índice de chave primária para obter a linha de dados, use um índice de cobertura, que inclui todos os campos exigidos pela consulta. Geralmente, o uso dessa abordagem é benéfico para cargas de trabalho vinculadas a E/S e cargas de trabalho armazenadas em cache. Portanto, como prática recomendada, use índices de cobertura porque eles cabem na memória e são menores e mais eficientes de ler do que digitalizar todas as linhas.

Considere, por exemplo, uma tabela que você está usando para tentar encontrar todos os funcionários que entraram na empresa após 1º de janeiro de 2000.

mysql> show create table employee\G
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `empid` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(10) DEFAULT NULL,
  `lname` varchar(10) DEFAULT NULL,
  `joindate` datetime DEFAULT NULL,
  `department` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)`

`mysql> select empid, fname, lname from employee where joindate > '2000-01-01';

Se você executar um plano EXPLAIN nessa consulta, observará que atualmente nenhum índice está sendo usado e uma cláusula where sozinha está sendo usada para filtrar os registros de funcionários.

mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

No entanto, se você adicionou um índice que cobre a coluna na cláusula where, juntamente com as colunas projetadas, verá que o índice está sendo usado para localizar as colunas de forma muito mais rápida e eficiente.

mysql> CREATE INDEX cvg_idx_ex ON employee (joindate, empid, fname, lname);

Agora, se você executar o plano EXPLAIN na mesma consulta, o valor "Usando índice" aparecerá no campo "Extra", o que significa que o InnoDB executa a consulta usando o índice que criamos anteriormente, o que confirma isso como um índice de cobertura.

mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: range
possible_keys: cvg_idx_ex
          key: cvg_idx_ex
      key_len: 6
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

No exemplo anterior, vimos que ter um índice de cobertura para uma consulta fornece caminhos de recuperação de registro mais eficientes e otimiza o desempenho em um ambiente de banco de dados altamente simultâneo.

Próximos passos

Para encontrar respostas de colegas para suas perguntas mais importantes, ou para postar ou responder perguntas, visite Stack Overflow.