Ottimizzazione: filesort con piccoli LIMIT

Descrizione dell'ottimizzazione

MySQL 5.6 introduce una ottimizzazione per le query ORDER BY ...LIMIT n. Quando n è sufficientemente piccolo, l'ottimizzazione usa una coda di priorità per l'ordinamento. L'alternativa, detto in soldoni, è ordinare l'intero output e poi prendere solo le prime n righe.

L'ottimizzazione è stata portata in MariaDB 10.0, nella versione 10.0.0. Il server non dava però alcuna indicazione utile a capire se questa ottimizzazione veniva usata. (E' così che questa funzionalità è stata progettata da Oracle. In MySQL 5.6, l'unico modo è esaminare l'optimizer_trace, che non è attualmente supportato da MariaDB).

Visibilità dell'ottimizzazione in MariaDB

Variabile di stato

Il primo modo è controllare la variabile di stato Sort_priority_queue_sorts. Essa mostra quante volte un ordinamento è stato eseguito tramite la coda di priorità. (Il numero totale degli ordinamenti è la somma di Sort_range e Sort_scan).

Slow query log

Il secondo modo è controllare lo slow query log. Quando si utilizzano le statistiche estese nello slow query log e si specifica log_slow_verbosity=query_plan, le voci dello slow query log assomigliano alla seguente:

# Time: 140714 18:30:39
# User@Host: root[root] @ localhost []
# Thread_id: 3  Schema: test  QC_hit: No
# Query_time: 0.053857  Lock_time: 0.000188  Rows_sent: 11  Rows_examined: 100011
# Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
SET timestamp=1405348239;SET timestamp=1405348239;
select * from t1 where col1 between 10 and 20 order by col2 limit 100;

Si noti "Priority_queue: Yes" nell'ultima riga di commento. (pt-query-digest è in grado di interpretare lo slow query log con il campo Priority_queue)

EXPLAIN non fornisce indicazioni se il filesort utilizza la coda di priorità o un quicksort generico e un algoritmo merge. In entrambi i casi apparirà Using filesort, sia su MariaDB sia su MySQL.

Vedi anche

• La pagina LIMIT Optimization nel manuale di MySQL 5.6 (si cerchi "priority queue").
• La voce del WorkLog di MySQL: WL#1393
• MDEV-415, MDEV-6430