Condivisione della tecnologia

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Sommario

Applicazione dell'indice

Quali indici ha MySQL?

Qual è la differenza tra un indice normale e un indice univoco? Quale ha prestazioni di aggiornamento migliori?

Come impostare l'indice della chiave primaria dell'indice cluster Domanda: cosa succederà se non lo imposti?

Che tipo di campi scegliamo solitamente per costruire gli indici?

Più indici sono migliori?

Come ottimizzare l'indice? (copertura dell'ottimizzazione dell'indice, prevenzione degli errori dell'indice, incremento della chiave primaria, ottimizzazione dell'indice del prefisso)

Se viene creato un indice, verrà utilizzato durante l'esecuzione delle query (errore dell'indice, l'ottimizzatore seleziona un piano di esecuzione in base al costo)

Se definisco un campo data di tipo varchar e uno dei dati è "20230922" e se è presente un indice su questo campo data, se la condizione where della mia query è where time=20230922 senza virgolette singole, il risultato sarà l'indice viene ancora colpito? Perché?

L'ultima versione di MySQL ha risolto eventuali casi di errore dell'indice? (Indice della funzione: il valore calcolato dalla funzione può anche essere indicizzato, meccanismo di scansione del salto dell'indice (prefisso più a sinistra))

Qual è il principio di corrispondenza più a sinistra?

A cosa dovrei prestare attenzione quando stabilisco un indice congiunto? (Posiziona quelli più differenziati all'estrema sinistra, il principio di corrispondenza più a sinistra, e non utilizzare l'indice dopo la query sull'intervallo)

Ordine di query del principio corrispondente più a sinistra

Che cos'è il pushdown dell'indice? Aggiunto in MySQL5.6 per ottimizzare le query di dati

Come creare un indice in cui a>1 e b=2 e c <3?

(A,B,C) indice congiunto seleziona * da tbn dove a=? e b in (?,?) e c> Verrà indicizzato?

Come creare un indice congiunto in cui a>100 e b=100 e c=123 ordinati per d?

seleziona id, nome da XX dove età > 10 e nome come "xx%", c'è un indice congiunto (nome, età), parliamo del processo di query

---

Applicazione dell'indice

Il mio SQLQuali indici ci sono?

Ho imparato che MySQL hachiave primariaIndice, indice univoco, indice ordinario, indice prefisso,Indice dell'UnioneQuesti tipi di indici.

Il motore Innodb richiede che ogni tabella del database abbia un filechiave primariaindice，I valori delle colonne dell'indice non sono consentitivalore nullo。Ad esempio, il campo id nella tabella è l'indice della chiave primaria

Indice univoco: Garantire l'unicità di ogni riga di dati nella colonna dati, ma consentire valori null.

PoiPer i campi che vengono interrogati frequentemente, possiamo creare un indice normale per questo campo.，Se sono presenti più campi, puoi prendere in considerazione la creazioneIndice dell'Unione,utilizzoCopertura dell'indiceLe funzionalità migliorano l'efficienza delle query.

Per testi lunghi, stringhe e altri tipi di campi, come titoli di articoli, nomi di prodotti, ecc., possiamo indicizzare solo la parte del prefisso di questi campi, cioèCrea un indice di prefisso per ridurre lo spazio di archiviazione dell'indice.

Qual è la differenza tra un indice normale e un indice univoco? Quale ha prestazioni di aggiornamento migliori? 、

• Un indice univoco potrebbe essere leggermente più veloce quando si esegue una query su un singolo valore perché può terminare la ricerca dopo aver trovato la prima corrispondenza.

• Per le operazioni di inserimento e aggiornamento, un indice normale potrebbe essere leggermente più veloce perché non richiede controlli di unicità.

1. I valori delle colonne dell'indice ordinario possono essere ripetuti, ma i valori delle colonne dell'indice univoco devono essere univoci Quando inseriamo un valore ripetuto in un indice univoco, verrà segnalato un errore a causa del vincolo di unicità.

2. PensoLe prestazioni di aggiornamento dell'indice ordinario saranno migliori, perché quando l'indice ordinario viene aggiornato, se la pagina dei dati aggiornata non lo èMemoria In tal caso, è possibile memorizzare direttamente nella cache l'operazione di aggiornamento nel buffer delle modifiche e l'operazione di aggiornamento verrà completata. (non è richiesto alcun controllo di unicità)

3. Ma,L'indice univoco deve avere vincoli univoci Se la pagina dei dati aggiornata non è nel fileMemoriaIn tal caso, è necessario leggere la pagina di dati corrispondente dal disco alla memoria per determinare se esiste un conflitto. Ciò comporterà la randomizzazione del disco.Io sonoAccesso.

4. Poiché gli indici ordinari possono utilizzare la funzionalità del buffer di modifica, l'aggiornamento degli indici ordinari è più veloce di quello degli indici univoci.Accesso casuale al disco ridotto, quindi le prestazioni di aggiornamento sono migliori

indice clusterDichiave primariaCome impostare l'indice? Domanda: cosa succede se non lo imposti?

Quando InnoDB crea un indice cluster, selezionerà diverse colonne come indici in base a diversi scenari:

1. Se è presente una chiave primaria, per impostazione predefinita la chiave primaria verrà utilizzata come chiave dell'indice cluster.

2. Se non è presente alcuna chiave primaria, selezionaIl primo non contiene Valore NULLL'unica colonna di è comeindice clusterchiave dell'indice

3. In assenza di quanto sopra, InnoDB genererà automaticamente una colonna rowid implicita con incremento automatico come chiave dell'indice cluster.

Che tipo di campi scegliamo solitamente per costruire gli indici?

Scenari in cui è applicabile l'indicizzazione:

1. I campi hanno restrizioni di unicità, ad esempio il codice prodotto

2. Campi utilizzati frequentemente nelle condizioni della query WHERE, che può migliorare la velocità di query dell'intera tabella. Se la condizione della query non è un campo, è possibile stabilire un indice congiunto

3. Campi spesso utilizzati in GROUPBY e ORDER BY, in modo che non sia necessario ordinarli nuovamente durante la ricerca, poiché i record nel B+ Tree vengono tutti ordinati dopo aver stabilito l'indice.

Scenari non adatti all'indicizzazione

1. Campi non utilizzati nelle condizioni WHERE, GROUP BY, ORDER BY, il valore dell'indice è di posizionamento rapido. Se il campo non può essere posizionato, solitamente non è necessario creare un indice, poiché l'indice occuperà spazio fisico.

2. Campi poco distinti , non è necessario creare un indice, ad esempio, il campo sesso contiene solo uomini e donne. Se i record di uomini e donne sono distribuiti equamente nella tabella del database, indipendentemente dal valore cercato, metà dei dati potrebbero essere. essere ottenuto.In questi casi è meglio non indicizzare perché Il mio SQLCe n'è ancora unoottimizzatore di query, quando Query Optimizer rileva che un determinato valore appare in un'alta percentuale di righe di dati nella tabella, generalmente ignorerà l'indice ed eseguiràScansione completa della tabella。

3. Campi aggiornati frequentemente, ad esempio, non indicizzare il saldo utente dei progetti di e-commerce perché i campi dell'indice vengono modificati frequentemente.mantenere B+Alberoordine, è necessaria una ricostruzione frequente dell'indice e questo processo influirà sulle prestazioni del database.

4. Non è consigliabile utilizzare valori non ordinati(come carta d'identità, UUID) come indice, quando la chiave primaria è incerta, causerà una frequente suddivisione dei nodi foglia e la frammentazione dell'archiviazione su disco.

• La tabella dati è più piccola: Quando la quantità di dati in una tabella è piccola o quando una query richiede la scansione di una grande parte dei dati nella tabella, l'ottimizzatore del database può scegliere una scansione completa della tabella invece di utilizzare un indice. In questo caso, il costo di mantenimento dell’indice potrebbe essere maggiore del guadagno in termini di performance.

Più indici sono migliori?

No, sebbene gli indici possano migliorare l'efficienza delle query, la creazione di un indice in più significa che verrà generato un nuovo indice dell'albero B+, che occuperà spazio di archiviazione. Soprattutto quando la quantità di dati della tabella è molto grande, l'indice occuperà più spazio.

Più indici ci sono, le prestazioni di scrittura del database diminuiranno, perché ogni volta che aggiungi, elimini o modifichi la tabella, devi mantenere l'ordine di ciascun indice dell'albero B+.

Come ottimizzare l'indice? (indice di coperturaOttimizzare e prevenire gli errori dell'indice,chiave primariaIncrementale, ottimizzazione dell'indice del prefisso)

Ho usato questi metodi di ottimizzazione

1. Per SQL che necessita di interrogare dati in diversi campi, possiamo creareIndice dell'Unione, quindi il metodo di query diventaindice di copertura, evitando il backup delle tabelle e riducendo un numero elevato di operazioni di I/O.

2. Nostrochiave primariaGli indici sono preferibilmente valori crescenti, poiché il nostro indice memorizza i dati in ordine, se il valore della chiave primaria è un valore casuale, potrebbe causare la suddivisione della pagina che causerà un gran numero di frammenti di memoria, quindi la struttura dell'indice non sarà compatta, il che lo farà influiscono sull'efficienza delle query.

3. vogliamoEvitare di scrivere gli errori di indice SQL Le istruzioni, ad esempio, non eseguono la corrispondenza fuzzy sinistra o sinistra sulle colonne dell'indice, non eseguono calcoli, funzioni e operazioni di conversione del tipo sugli indici. Per utilizzare correttamente gli indici congiunti, è necessario seguire il principio di corrispondenza più a sinistra, ecc.Nella clausola WHERE, se la colonna della condizione prima dell'OR è una colonna dell'indice e la colonna della condizione dopo l'OR non è una colonna dell'indice, l'indice avrà esito negativo.

• Utilizzare diverso da (<>) o operatore NOT: questi operatori in genere invalidano l'indice perché scansionano l'intera tabella.

• Operatore OR: se nella condizione della query viene utilizzato OR e le condizioni su entrambi i lati dell'OR coinvolgono indici diversi, questi indici potrebbero non essere utilizzati.

  • utilizzo OR operatore, seOR Le condizioni su entrambi i lati implicano indici diversi e nella maggior parte dei casi il motore del database non può utilizzare più indici contemporaneamente per ottimizzare la query.Questo èPerché OR L'operatore deve solo soddisfare le condizioni di entrambi i lati, il che aumenta la complessità dell'ottimizzazione delle query.

1. Indice per una stringa di grandi dimensioni, possiamo considerare l'utilizzoindice del prefissoSolo la parte del prefisso della colonna dell'indice viene indicizzata per risparmiare spazio di archiviazione dell'indice e migliorare le prestazioni delle query.

2. È meglio impostare l'indice su NOT NULLO : Per utilizzare al meglio l'indice, la colonna dell'indice deve essere impostata sul vincolo NOT NULL. Ci sono due ragioni:

   1. La presenza di NULL nelle colonne dell'indice renderà più complicata la selezione dell'indice da parte dell'ottimizzatore, rendendo più difficile l'ottimizzazione di operazioni come il conteggio.

   2. Il valore NULL è un valore privo di significato, ma occuperà spazio fisico. È presente una colonna con valore nullo.Verrà utilizzato almeno 1 byte di spazio per memorizzare NULL elenco di valori

Se viene creato un indice, verrà utilizzato durante l'esecuzione delle query (errore dell'indice,ottimizzatoreSeleziona il piano di esecuzione in base al costo)

NO.

1. ho studiatoAnche se la query utilizza un indice, potrebbe non utilizzare l'indice.

   1. Ad esempio: quando la nostra istruzione di query esegue operazioni di corrispondenza fuzzy sinistra, calcolo di espressioni, funzioni e conversione di tipo implicito sul campo dell'indice, l'istruzione di query non può passare attraverso l'indice e il metodo di query diventa una scansione completa della tabella.

   2. E usiamoIndice dell'UnioneDurante l'esecuzione di una query, se non viene seguito il principio di corrispondenza più a sinistra, si verificherà anche un errore di indice.。

2. L'ottimizzatore èScegli un metodo di query in base a considerazioni sui costi, quando si utilizza l'indice secondario per la query, l'ottimizzatore calcolerà il costo della restituzione della tabella e il costo della scansione completa della tabella. Se il costo della restituzione della tabella è troppo elevato, l'ottimizzatore sceglierà di non utilizzare l'indice, ma di utilizzare il scansione completa della tabella.

Se definisco un campo data di tipo varchar e uno dei dati è "20230922" e se è presente un indice su questo campo data, se la condizione where della mia query è where time=20230922 senza virgolette singole, il risultato sarà l'indice viene ancora colpito? Perché?

Non colpirà l'indice.

Perché mysql sta incontrandoConfronto tra stringhe e numeriaccadrà quandoconversione implicita del tipo, VolereConverti oggetto stringa in numero, questo processo di conversione comporta effettivamentefunzione . Nella query menzionata, il campo data è una stringa, quindi quando si verifica la conversione implicita del tipo, verrà applicata al campo dell'indice della data. Se il calcolo della funzione viene eseguito sull'indice, l'indice non sarà più valido.

Per le colonne dell'indice di tipo intero, ad esempioid Colonna il cui valore viene archiviato direttamente nell'indice senza che venga eseguito il calcolo della funzione.Ciò significa utilizzare nella queryidQuando si abbina, non è necessarioidEsegui calcoli funzionali o conversioni e confronta semplicemente i valori interi.

Il mio SQLL'ultima versione ha risolto eventuali casi di errore dell'indice (Indice funzione:calcolo delle funzioniIl valore dopo può anche essere indicizzato e il meccanismo di scansione dell'indice salta (prefisso più a sinistra))

Ho imparato che MySQL8.0 può aggiungere campiindice di funzione, questa nuova funzionalità può risolvere il problema dell'errore dell'indice quando si utilizzano funzioni sull'indice.

Un'altra nuova funzionalità èscansione dell'indice salta, Prima della versione 5.7, quando si utilizzava un indice congiunto, se il principio di corrispondenza più a sinistra non veniva soddisfatto, si verificava un errore dell'indice. Tuttavia, dopo l'introduzione della funzione di salto dell'indice nella versione 8.0, gli indici congiunti possono ancora essere utilizzati anche se il principio di corrispondenza più a sinistra. non è seguito.

Qual è il principio di corrispondenza più a sinistra?

Supponiamo che esista un indice congiunto (a, b, c). Il suo ordine di archiviazione è di ordinare prima in base a, quindi ordinare in base a b quando a è lo stesso e quindi ordinare in base a c quando b è lo stesso. A causa di questa caratteristica, quando si utilizzano indici congiunti, esiste un principio di corrispondenza più a sinistra. Le regole specifiche sono:

1. L'indice federato di MySQL inizierà daLa colonna dell'indice più a sinistra inizia a corrispondere alle condizioni della query, quindi corrisponde in sequenza da sinistra a destra. Se le condizioni della query non utilizzano una colonna, tutte le colonne a destra della colonna non possono essere indicizzate.

2. Quando una colonna viene utilizzata nella condizione della query,Tuttavia, il valore di questa colonna contiene una query di intervallo ed è possibile utilizzare i campi della query di intervalloIndice dell'Unione, ma l'indice congiunto non può essere utilizzato nei campi dietro il campo di query dell'intervallo.

Pertanto, quando utilizziamo gli indici congiunti, dobbiamo rispettare il principio di corrispondenza più a sinistra, altrimenti alcuni campi dell'indice potrebbero non essere indicizzati.

StabilireIndice dell'UnioneC'è qualcosa a cui dobbiamo prestare attenzione (quelli più differenziati sono posizionati all'estrema sinistra, il principio di corrispondenza più a sinistra, e l'indice non viene utilizzato dopo la query sull'intervallo)

1. maggior parteInserisci i campi con maggiore distinzioneIndice dell'Unioneestrema sinistra, utileMigliora l'effetto di filtraggio dell'indice, campi come UUID sono più adatti per l'indicizzazione o il posizionamento nella parte superiore della colonna dell'indice congiunto.

2. Se un campo con bassa discriminazione viene posizionato sul lato più a sinistra dell'indice congiunto, è possibile che Query Optimizer scelga una scansione completa della tabella anziché utilizzare l'indice.

3. Il principio di corrispondenza più a sinistra dell'indice congiunto, inQuando si incontra una query di intervallo (come >, <), la corrispondenza verrà interrotta, ovvero i campi della query di intervallo possono utilizzare l'indice congiunto, ma i campi dietro il campo di query di intervallo non possono utilizzare l'indice congiunto.Tuttavia, per le quattro query di intervallo >=, <=, BETWEEN e la corrispondenza del prefisso simile, la corrispondenza non verrà interrotta.

   1. In MySQL, BETWEEN contiene valori limite valore1 e valore2, simili a >= e =<.

   2. Link di riferimento https://zhuanlan.zhihu.com/p/573138586

Ordine di query del principio corrispondente più a sinistra

select * from T where c=1 and a=2 and b=3;

abc può essere indicizzato perché L'ordine in cui si trovano i campi delle condizioni della query non influisce, l'ottimizzatore MySQL ci aiuterà a regolare l'ordine delle query dei campi, in modo che sia conforme anche al principio di corrispondenza più a sinistra.

sotto indicespingere Che cos'è? Aggiunto in MySQL5.6 per ottimizzare le query di dati

La spinta verso il basso dell'indice può ridursiindice secondarioL'operazione di restituzione della tabella durante la query migliora l'efficienza della query perché lo farà Il livello server è responsabile di alcune delle cose gestite dal livello del motore di archiviazione.Sono andato a occuparmene.

• Quando viene utilizzata l'ottimizzazione pushdown senza condizioni dell'indice, il motore di archiviazione recupera i dati tramite l'indice e quindi li restituisce a MySQL Server.Server MySQL Esprimere giudizi sulle condizioni del filtro.

• Quando si utilizza l'ottimizzazione push-down delle condizioni dell'indice, se sono presenti determinate condizioni di valutazione per le colonne indicizzate, MySQL Server trasferirà questa parte delle condizioni di valutazione al motore di archiviazione, quindi il motore di archiviazione giudicherà se l'indice soddisfa le condizioni passate da MySQL Server Solo quando l'indice soddisfa le condizioni, i dati verranno recuperati e restituiti al server MySQL.

L'ottimizzazione del pushdown delle condizioni dell'indice può ridurre il numero di volte in cui il motore di archiviazione esegue query sulla tabella sottostante e può anche ridurlo Il mio SQL Il numero di volte in cui il server ha ricevuto dati dal motore di archiviazione.

select * from t_user where age > 20 and reward = 100000;

Come creare un indice in cui a>1 e b=2 e c <3?

1. Crea un indice congiunto (abc), (acb), (ab), (ac), solo un indice può

2. Creare indici congiunti (cab), (cba), (ca), (cb), solo c può indicizzare

3. Crea (ba) indice congiunto, sia b che a possono essere indicizzati

4. Crea (bc) indice congiunto, sia b che c possono essere indicizzati

5. creare(bac) Indice dell'Unione, b e a possono essere entrambi indicizzati, ma sono più lenti di (sedere) L'indice congiunto ha un ulteriore vantaggio, il campo c puòspinta verso il basso dell'indice, ridurrà il numero di ritorni di tabella;

6. creare(non è vero) Indice dell'Unione, sia b che c possono essere indicizzati, ma ha un vantaggio in più rispetto all'indice congiunto (bc), il campo a puòspinta verso il basso dell'indice, ridurrà il numero di ritorni di tabella;

(A,B,C) indice congiunto select * from tbn where a=? and b in (?,?) and c>? Verrà indicizzato?

Questa query utilizzerà l'indice congiunto (A,B,C), perché la condizione è basata sulla colonna dell'indice A、B、C Arriva l'ordine, che è lo scenario di utilizzo ideale.

1. per A=?: Questa condizione corrisponde esattamente. MySQL utilizzerà l'indice per individuare la condizione che la soddisfa. A=? registrazione di.

2. per B IN (?, ?): Questa condizione specifica B La colonna può assumere due possibili valori. MySQL utilizzerà l'indice per trovare tutte le corrispondenzeA=? EB La colonna è un record con uno di questi due valori.

3. per C>? : questa condizione è una query di intervallo.già basato suA EB In base al filtro, MySQL continuerà a utilizzare l'indice per trovareC Record con valori di colonna maggiori del valore specificato.

dove a>100 e b=100 e c=123 ordina per d come creareIndice dell'Unione?

PensoStabilire abcda al fineIndice dell'UnioneMeglio, in questo momento è possibile indicizzare sia i campi b che c, ed può utilizzare l'ordinamento dell'indice per evitare l'ordinamento dei file (ordinamento extra), sebbene l'ultimo campo a non possa essere indicizzato (a è fuori ordine), può essere spostato verso il basso utilizzando l'indice per ridurre il numero di risultati della tabella.

seleziona id, nome da XX dove età > 10 e nome tipo 'xx%', sìIndice dell'Unione(nome, età), parla del processo di query

L'ordine dell'indice congiunto è prima il nome, poi l'età. Strutturalmente, viene ordinato prima per nome e poi per età se i nomi sono uguali.Pertanto, l'ottimizzatore deve prima corrispondere al nome. Nome è una query fuzzy corretta in questo momento e non si verificherà un errore di indice, quindi questo SQL può utilizzare l'indicizzazione congiunta.

Nello specifico, solo il nome può essere indicizzato Questo perchéDopo la query fuzzy name right, i valori del campo età non sono in ordine, quindi l'età non può essere indicizzata, ma l'età può essere indicizzata.spinta verso il basso dell'indice。

Gli ultimi campi interrogati sono id e nome. Questi due campi possono essere trovati nell'indice congiunto, quindi non è necessario restituire la tabella. Si tratta di una query di copertura dell'indice.

La query fuzzy relativa al nome corretto è una query di intervallo e i seguenti campi non possono essere indicizzati