creazione di indice di ordinamento

Lo stato del thread creating sort index indica che un thread sta elaborando un’istruzione SELECT che richiede l'uso di una tabella temporanea interna per ordinare i dati.

Versioni del motore supportate

Queste informazioni sullo stato del thread sono supportate per le seguenti versioni:

• Aurora MySQL versione 2 fino alla 2.09.2

Context

Lo stato creating sort index appare quando una query con una clausola ORDER BY o GROUP BY non può utilizzare un indice esistente per eseguire l'operazione. In questo caso, MySQL deve eseguire un’operazione filesort più costosa. Questa operazione viene generalmente eseguita in memoria se la serie di risultati non è troppo grande. In caso contrario, comporta la creazione di un file su disco.

Probabili cause di aumento delle attese

La comparsa di creating sort index non indica di per sé un problema. Se le prestazioni sono scadenti e si vedono frequenti istanze di creating sort index, la causa più probabile è query lente con operatori ORDER BY o GROUP BY.

Operazioni

La linea guida generale è trovare query con clausole ORDER BY o GROUP BY associate agli aumenti dello stato creating sort index. Quindi verificare se l'aggiunta di un indice o l'aumento della dimensione del buffer di ordinamento risolve il problema.

Argomenti

• Attiva il Performance Schema se non è attivato
• Identificare le query problematiche
• Esaminare i piani di spiegazione per l'utilizzo di filesort
• Aumenta la dimensione del buffer di ordinamento

Attiva il Performance Schema se non è attivato

Performance Insights segnala gli stati del thread solo se gli strumenti di Performance Schema non sono attivati. Quando gli strumenti di Performance Schema sono attivati, Performance Insights segnala invece gli eventi di attesa. Gli strumenti di Performance Schema forniscono informazioni dettagliate aggiuntive e strumenti migliori quando si esaminano potenziali problemi di prestazione. Pertanto, è consigliabile attivare il Performance Schema. Per ulteriori informazioni, consulta Abilitazione di Performance Schema per Performance Insights su Aurora MySQL.

Identificare le query problematiche

Per identificare le query correnti che causano un aumento dello stato creating sort index, eseguire show processlist e vedi se una qualsiasi delle query ha ORDER BY o GROUP BY. Facoltativamente, eseguire explain for connection N, dove N è l'ID dell'elenco dei processi della query con filesort.

Per identificare le query precedenti che causano questi aumenti, attiva il registro delle query lente e trova le query con ORDER BY. Esegui EXPLAIN sulle query lente e cerca “utilizzo di filesort”. Per ulteriori informazioni, consulta Esaminare i piani di spiegazione per l'utilizzo di filesort.

Esaminare i piani di spiegazione per l'utilizzo di filesort

Identifica le istruzioni con clausole ORDER BY o GROUP BY che si traducono nello stato creating sort index.

Negli esempi seguenti viene illustrato come eseguire explain su una query. La colonna Extra mostra che questa query utilizza 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)

L'esempio seguente mostra il risultato dell'esecuzione di EXPLAIN sulla stessa query dopo la creazione di un indice sulla colonna 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)

Per informazioni sull'utilizzo degli indici per l'ottimizzazione dell'ordinamento, consulta Ottimizzazione ORDER BY nella documentazione di MySQL.

Aumenta la dimensione del buffer di ordinamento

Per verificare se una query specifica richiedeva un processo filesort che ha creato un file su disco, controllare il valore della variabile sort_merge_passes dopo aver eseguito la query. Di seguito viene riportato un esempio.

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 il valore di sort_merge_passes è alto, si consideri di aumentare la dimensione del buffer di ordinamento. Applica l'aumento a livello di sessione, perché l’aumento a livello globale può aumentare significativamente la quantità di RAM utilizzata da MySQL. L'esempio seguente mostra come modificare le dimensioni del buffer di ordinamento prima di eseguire una query.

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