criar índice de classificação

O estado de thread creating sort index indica que um thread está processando uma instrução SELECT que requer o uso de uma tabela temporária interna para classificar os dados.

Versões compatíveis do mecanismo

Essas informações sobre estados de thread têm suporte para as seguintes versões:

• Aurora MySQL versão 2 até 2.09.2

Contexto

O estado creating sort index aparece quando uma consulta com uma cláusula ORDER BY ou GROUP BY não pode utilizar um índice existente para realizar a operação. Nesse caso, o MySQL precisa realizar uma operação filesort mais cara. Em geral, essa operação é executada na memória quando o conjunto de resultados não é muito grande. Caso contrário, ela envolve a criação de um arquivo no disco.

Possíveis causas do maior número de esperas

O surgimento de creating sort index por si só não indica um problema. Se a performance for ruim e você vir instâncias frequentes de creating sort index, a causa mais provável são consultas lentas com operadores ORDER BY ou GROUP BY.

Ações

A diretriz geral é encontrar consultas com cláusulas ORDER BY ou GROUP BY associadas aos aumentos no estado creating sort index. Em seguida, verifique se adicionar um índice ou aumentar o tamanho do buffer de classificação resolve o problema.

Tópicos

• Habilite o Performance Schema se ele não estiver habilitado
• Identificar as consultas com problemas
• Examinar os planos de explicação para o uso da classificação de arquivo
• Aumentar o tamanho do buffer de classificação

Habilite o Performance Schema se ele não estiver habilitado

O Performance Insights relata estados de thread somente quando instrumentos do Performance Schema não estão habilitados. Quando os instrumentos do Performance Schema estão habilitados, o Performance Insights relata eventos de espera. Os instrumentos do Performance Schema fornecem insights adicionais e melhores ferramentas quando você investiga possíveis problemas de performance. Portanto, convém habilitar o Performance Schema. Para obter mais informações, consulte Ativar o Performance Schema para o Performance Insights no Aurora MySQL.

Identificar as consultas com problemas

Para identificar consultas atuais que estão causando aumentos no estado creating sort index, execute show processlist e veja se alguma das consultas tem ORDER BY ou GROUP BY. Opcionalmente, execute explain for connection N, em que N é o ID de lista de processos da consulta com filesort.

Para identificar consultas anteriores que estão causando esses aumentos, ative o log de consultas lentas e localize as consultas com ORDER BY. Execute EXPLAIN nas consultas lentas e procure "usando classificação de arquivo". Para obter mais informações, consulte Examinar os planos de explicação para o uso da classificação de arquivo.

Examinar os planos de explicação para o uso da classificação de arquivo

Identifique as instruções com cláusulas ORDER BY ou GROUP BY que resultam no estado creating sort index.

O exemplo a seguir mostra como executar explain em uma consulta. A coluna Extra mostra que essa consulta usa filesort.

mysql> explain select * from mytable order by c1 limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mytable
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2064548
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.01 sec)

O exemplo a seguir mostra o resultado da execução de EXPLAIN na mesma consulta depois que um índice é criado na coluna c1.

mysql> alter table mytable add index (c1);

mysql> explain select * from mytable order by c1 limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mytable
   partitions: NULL
         type: index
possible_keys: NULL
          key: c1
      key_len: 1023
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)

Para obter informações sobre o uso de índices para otimização da ordem de classificação, consulte Otimização ORDER BY, na documentação do MySQL.

Aumentar o tamanho do buffer de classificação

Para ver se uma consulta específica exigia um processo filesort que criou um arquivo no disco, verifique o valor da variável sort_merge_passes após a execução da consulta. Por exemplo:

mysql> show session status like 'sort_merge_passes';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
+-------------------+-------+
1 row in set (0.01 sec)

--- run query
mysql> select * from mytable order by u limit 10; 
--- run status again:

mysql> show session status like 'sort_merge_passes';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
+-------------------+-------+
1 row in set (0.01 sec)

Se o valor de sort_merge_passes for alto, considere aumentar o tamanho do buffer de classificação. Aplique o aumento no nível da sessão, porque elevá-lo globalmente pode aumentar significativamente a quantidade de RAM utilizada pelo MySQL. O exemplo a seguir mostra como alterar o tamanho do buffer de classificação antes de executar uma consulta.

mysql> set session sort_buffer_size=10*1024*1024;
Query OK, 0 rows affected (0.00 sec)
-- run query