Melhores práticas para obter a solução dos problemas do servidor do Banco de Dados do Azure para MySQL – Servidor Flexível
Use as seções a seguir para manter seus bancos de dados do Banco de Dados do Azure para MySQL – Servidor Flexível funcionando sem problemas e use essas informações como princípios orientadores para garantir que os esquemas sejam projetados de forma ideal e forneçam o melhor desempenho para seus aplicativos.
Verificar o número de índices
Em um ambiente de banco de dados ocupado, você pode observar o alto uso de E/S, o que pode ser um indicador de padrões de acesso a dados insatisfatórios. Índices não utilizados podem ter um impacto negativo no desempenho, pois consomem espaço em disco e cache e reduzem as operações de gravação (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, colete 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 seja executada apenas trimestralmente ou anualmente. Além disso, considere se um índice é usado para impor exclusividade ou ordenação.
Observação
Lembre-se de examinar índices periodicamente e executar as atualizações necessárias com base em quaisquer modificações nos dados da tabela.
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 movimentados 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 cada índice e os números de latência para leituras, bem como operações de gravação individuais, o que pode indicar que um ajuste adicional é necessário 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
Examinar o design da chave primária
O Banco de Dados do Azure para MySQL – Servidor Flexível usa o mecanismo de armazenamento InnoDB para todas as tabelas não temporárias. Com o InnoDB, os dados são armazenados em um índice clusterizado usando uma estrutura de Árvore B. A tabela é fisicamente organizada com base em valores de chave primária, o que significa que as linhas são armazenadas na ordem de chave primária.
Cada entrada de chave secundária em uma tabela InnoDB contém um ponteiro para o valor da chave primária no qual os dados são armazenados. Em outras palavras, uma entrada de índice secundário contém uma cópia do valor da chave primária para o qual a entrada está apontando. Portanto, as principais opções de chave têm um efeito direto sobre a quantidade de sobrecarga de armazenamento em suas tabelas.
Se uma chave for derivada de dados reais (por exemplo, nome de usuário, email, SSN etc.), ela será chamada de chave natural. Se uma chave for artificial e não derivada de dados (por exemplo, um inteiro incrementado automaticamente), ela será conhecida como chave sintética ou chave substituta.
Geralmente, é recomendável 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 a fragmentação de página quando as linhas são inseridas ou atualizadas. Para evitar esses problemas, use chaves alternativas que aumentam monotonicamente em vez de chaves naturais. Uma coluna íntegra (grande) de incremento automático é um bom exemplo de uma chave substituta que aumenta monotonicamente. Se precisar de uma determinada combinação de colunas, seja exclusivo e declare essas colunas como uma chave secundária exclusiva.
Durante os estágios iniciais da criação de um aplicativo, talvez você não pense antecipadamente em imaginar um momento em que sua tabela começa a se aproximar com 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). Verifique todas as chaves primárias da tabela e alterne para usar colunas de inteiros de 8 bytes (BIGINT) para acomodar o potencial de um alto volume ou crescimento.
Observação
Para obter mais informações sobre tipos de dados e seus valores máximos, no Manual de referência do MySQL, consulte Tipos de dados.
Usar índices de cobertura
A seção anterior explica como os índices no MySQL são organizados como Árvores B e em um índice clusterizado, os nós folha contêm as páginas de dados da tabela subjacente. Índices secundários têm a mesma estrutura de árvore B que índices clusterizados e você pode defini-los em uma tabela ou exibição com um índice clusterizado ou um heap. Cada linha do índice secundário contém o valor da chave não clusterizada e um localizador de linha. Esse localizador aponta para a linha de dados no índice clusterizado ou heap que possui o valor da chave. Como resultado, qualquer pesquisa que envolva um índice secundário deve navegar desde o nó raiz 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 pelos nós de branch até o nó folha correto) para obter a linha de dados.
Para evitar que essa E/S aleatória extra seja lida 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 associadas a E/S e cargas de trabalho armazenadas em cache. Portanto, como melhor prática, use a cobertura de índices porque eles se ajustam à memória e são menores e mais eficientes para ler do que verificar todas as linhas.
Considere, por exemplo, uma tabela que você esteja usando para tentar encontrar todos os funcionários que ingressaram 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 em que apenas uma cláusula está sendo usada para filtrar os registros do funcionário.
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 cubra a coluna na cláusula Where, juntamente com as colunas projetadas, você 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)
Observação
É importante escolher a ordem correta das colunas no índice de cobertura para atender à consulta corretamente. A regra geral é escolher as colunas para filtragem primeiro (cláusula WHERE), depois classificação/agrupamento (ORDER BY e GROUP BY) e, por fim, a projeção de dados (SELECT).
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.