La variabile di sistema MAX_SEEKS_FOR_KEY in MySQL controlla una soglia relativa al processo decisionale dell'ottimizzatore quando si sceglie se utilizzare una scansione indice o una tabella completa. L'impostazione di MAX_SEEKS_FOR_KEY è troppo in alto comporta diversi rischi che possono influire sulle prestazioni e sull'efficienza delle query in un database MySQL.
max_seeks_for_key rappresenta il numero massimo presunto di tasto chiede l'ottimizzatore prevede quando si cerca una riga in base a una chiave indice. Per impostazione predefinita, questo valore è estremamente elevato (su un sistema a 64 bit, è in genere impostato su 18.446.744.073.709.551.615). Ciò significa che l'ottimizzatore non si limiterà a presumere quanto possa essere costosa la ricerca chiave da un indice.
Quando MAX_SEEKS_FOR_KEY è impostato molto in alto, l'ottimizzatore tende a preferire le scansioni delle tabelle sulle scansioni dell'indice, specialmente nei casi in cui la cardinalità di un indice (il numero stimato di valori univoci in un indice) è relativamente bassa. Ciò può portare a diverse conseguenze indesiderabili:
1. Piani di query non ottimale:
Il rischio più significativo di un alto Max_Seeks_For_Key è che l'ottimizzatore di query può scegliere scansioni di tabelle complete anziché scansioni di indici più efficienti. Scansioni della tabella completa Leggi ogni riga nella tabella in sequenza, il che può comportare costi I/O più elevati se la tabella è grande e la query necessita solo di un piccolo sottoinsieme di righe. Ciò porta a tempi di esecuzione delle query più lenti rispetto all'utilizzo di una scansione indice appropriata.
2. Aumento dell'utilizzo e latenza delle risorse:
Le scansioni della tabella completa consumano spesso più risorse I/O del disco CPU e del disco perché ogni riga nella tabella deve essere esaminata. Ciò può contribuire al carico complessivo del sistema, aumentare la latenza per le query e ridurre il throughput per il server MySQL, in particolare sotto la concorrenza dell'utente elevato o con set di dati molto grandi.
3. Capestri prestazioni su tavoli grandi:
Poiché le scansioni complete richiedono la lettura dell'intera tabella, l'impostazione di max_seeks_for_key è un impatto troppo elevato per prestazioni negativamente per tabelle di grandi dimensioni con molte righe. Le query che dovrebbero idealmente sfruttare gli indici per filtrare rapidamente le righe invece incorrere in lunghe durate di scansione, degradando la reattività dell'applicazione.
4. Comportamento incoerente tra le domande:
Poiché MAX_SEEKS_FOR_KEY si applica a livello globale, aumentare il suo valore influisce su tutte le query sul server. Mentre alcune domande potrebbero beneficiare di scansioni complete, molti altri ottimizzati per gli indici potrebbero funzionare peggio. Ciò comporta imprevedibilità e prestazioni di query incoerenti, che richiedono una sperimentazione e una messa a punto attenti e in corso specifiche per query.
5. Mancata sfruttamento della cardinalità indice in modo efficace:
La cardinalità indice (la stima di valori distinti nell'indice) è la chiave per le decisioni di MySQL Optimizer. Se max_seeks_for_key è impostato troppo in alto, l'ottimizzatore penalizza le scansioni degli indici supponendo che potrebbero richiedere una chiave più costosa di quanto non facciano effettivamente, anche quando gli indici ad alta cardinalità dovrebbero essere logicamente utilizzati. Di conseguenza, le ottimizzazioni basate sull'indice sono sottoutilizzate o ignorate.
6. Statistiche di ottimizzatore fuorviante:
L'ottimizzatore dipende da statistiche come la cardinalità dell'indice e i costi associati alle ricerche di riga. Quando Max_seeks_for_key maschera il costo effettivo essendo eccessivamente permissivo, può far sì che l'ottimizzatore giudichi erroneamente il costo dei percorsi di accesso, influenzando l'accuratezza del modello di costo complessivo dell'ottimizzatore.
7. Difficoltà a risolvere i problemi di prestazione:
Poiché MAX_SEEKS_FOR_KEY può interagire in modi complessi con statistiche dell'indice e piani di query, averlo impostato troppo in alto può rendere più impegnativa la diagnosi di query lente. Può portare a situazioni in cui gli indici di ottimizzazione o la riscrittura non producono miglioramenti previsti perché le ipotesi di costo fondamentali in ottimizzatore rimangono distorti.
Riepilogo dei rischi: l'impostazione di Max_seeks_for_key troppo in alto fa sì che l'ottimizzatore MySQL favorisca le scansioni della tabella completa anziché sulle scansioni indici, portando a tempi di esecuzione più lenti, aumento della CPU e carico I/O, uso inefficiente degli indici, per le prestazioni degradate su grandi tabelle, comportamenti incollati di query, stime dei costi fuorvianti da parte dell'ottimizzatore e sfida per la sintonizzazione delle prestazioni.
Questi rischi evidenziano il motivo per cui molti DBA scelgono di ridurre max_seeks_for_key a un livello più moderato (come 1000 o simili). La riduzione del valore incoraggia l'ottimizzatore a preferire l'utilizzo dell'indice rispetto alle scansioni complete quando la cardinalità dell'indice è al di sopra di quella soglia, migliorando generalmente la velocità delle query e riducendo il consumo di risorse per i carichi di lavoro tipici.
In conclusione, mentre un max_seeks_for_key molto elevato potrebbe sembrare sicuro in quanto non limita l'ottimizzatore, in pratica rischia di scarsi prestazioni di query, risorse sprecate e una maggiore latenza a causa di scansioni premature della tabella completa, in particolare su set di dati di grandi dimensioni che dovrebbero essere indicizzati in modo efficiente.