Repository 32bit  Forum
Repository 64bit  Wiki

mysql: indici su tabelle grandi

Forum dedicato alla programmazione.

Moderatore: Staff

Regole del forum
1) Citare in modo preciso il linguaggio di programmazione usato.
2) Se possibile portare un esempio del risultato atteso.
3) Leggere attentamente le risposte ricevute.
4) Scrivere i messaggi con il colore di default, evitare altri colori.
5) Scrivere in Italiano o in Inglese, se possibile grammaticalmente corretto, evitate stili di scrittura poco chiari, quindi nessuna abbreviazione tipo telegramma o scrittura stile SMS o CHAT.
6) Appena registrati è consigliato presentarsi nel forum dedicato.

La non osservanza delle regole porta a provvedimenti di vari tipo da parte dello staff, in particolare la non osservanza della regola 5 porta alla cancellazione del post e alla segnalazione dell'utente. In caso di recidività l'utente rischia il ban temporaneo.

mysql: indici su tabelle grandi

Messaggioda ZeroUno » sab mar 20, 2010 0:26

Salve.

Ho una tabella di grandi dimensioni, nell'ordine delle centinaia di MB e dei milioni di record.

la tabella ha 7 colonne, di cui una è la primary key (id autoincrement), una è il puntamento ad un'altra tabella (id) legata tramite relazione di integrità referenziale, una colonna data/ora, una colonna numerica (popolata ma non utilizzata) e le ultime due quelle che fanno volume. Sono di tipo varchar(255); al momento la prima contiene stringhe della lunghezza massima di 150 caratteri, mentre l'altro campo, sempre varchar(255) ha, al momento, una lunghezza massima di c.a.80 caratteri. Entrambi questi campi possono contenere duplicati.

La mia query fa una ricerca sul quest'ultimo campo, così l'ho indicizzato.

L'indice ha una dimensione di altissime, a mio parere:
record: 2859187
dati: 287 M
indice: 177 M

è normale una dimensione così alta?
ma soprattutto... di quanto mi si alzano le performance?

poi la cardinalità... non so precisamente quanto debba essere, ma mi equivale esattamente al numero di record. E' normale.

Ciao
01
Packages finder: slakfinder.org | Slackpkg+, per aggiungere repository a slackpkg
Codice: Seleziona tutto
1011010 1100101 1110010 1101111 - 0100000 - 1010101 1101110 1101111
Avatar utente
ZeroUno
Staff
Staff
 
Messaggi: 4398
Iscritto il: ven giu 02, 2006 13:52
Località: Roma / Castelli
Nome Cognome: Matteo Rossini
Slackware: current
Kernel: slack-current
Desktop: ktown-latest
Distribuzione: 01000000-current

Re: mysql: indici su tabelle grandi

Messaggioda tgmx » sab mar 20, 2010 10:54

Sinceramente non ho mai guardato la dimensione degli indici ma posso dirti che in tabelle come la tua, prima e dopo aver indicizzato il campo su cui faccio interrogazioni i tempi si sono ridotti di 1/10 o forse più.
Tempo fa però avevo letto (forse sul sito mysql) che se possibile era preferibile indicizzare campi char e non varchar per incrementare le prestazioni e ridurre l'utilizzo di memoria... ma non ho mai verificato.
Avatar utente
tgmx
Linux 3.x
Linux 3.x
 
Messaggi: 1330
Iscritto il: ven apr 28, 2006 13:40
Località: Ancona
Slackware: 13.37
Desktop: KDE 4

Re: mysql: indici su tabelle grandi

Messaggioda ZeroUno » dom mar 21, 2010 10:37

Ora sono in fase di qualche cambio sul programma quindi l'applicativo non è usabile, ma appena funzionerà proverò a vedere che succede senza indice (in dimensione e velocità).
Per quanto riguarda il char sono daccordo che probabilmente accellera l'accesso ai dati, e forse riduce la dimensione degli indici, ma credo che aumenti in modo pauroso la dimensione della tabella. Io infatti ho molte (la maggioranza) stringhe corte, ma alcune sono lunghe.

Ciao
01
Packages finder: slakfinder.org | Slackpkg+, per aggiungere repository a slackpkg
Codice: Seleziona tutto
1011010 1100101 1110010 1101111 - 0100000 - 1010101 1101110 1101111
Avatar utente
ZeroUno
Staff
Staff
 
Messaggi: 4398
Iscritto il: ven giu 02, 2006 13:52
Località: Roma / Castelli
Nome Cognome: Matteo Rossini
Slackware: current
Kernel: slack-current
Desktop: ktown-latest
Distribuzione: 01000000-current

Re: mysql: indici su tabelle grandi

Messaggioda ZeroUno » lun mar 29, 2010 21:46

Ho provato a togliere l'indice.
La tabella è passata da 550 M a 380M, ma effettivamente i tempi di ricerca sono saliti alle stelle, si, anche 10/1
la tabella ha 3 milioni di record.

adesso sto ricostruendo l'indice (considerando che la sola eliminazione ha portato 147sec, quanto ci metterà a fare la creazione? popolando la tabella e le altre correlate da zero impiego 15/20 min, ora dovrei metterci di meno)

dopo trasformo in char.
Packages finder: slakfinder.org | Slackpkg+, per aggiungere repository a slackpkg
Codice: Seleziona tutto
1011010 1100101 1110010 1101111 - 0100000 - 1010101 1101110 1101111
Avatar utente
ZeroUno
Staff
Staff
 
Messaggi: 4398
Iscritto il: ven giu 02, 2006 13:52
Località: Roma / Castelli
Nome Cognome: Matteo Rossini
Slackware: current
Kernel: slack-current
Desktop: ktown-latest
Distribuzione: 01000000-current

Re: mysql: indici su tabelle grandi

Messaggioda ZeroUno » lun mar 29, 2010 22:08

Un indice è stato aggiunto in filename ( La query ha impiegato 1258.7071 sec )



Però io non ho capito la cardinalità:
La prima volta che creo la tabella è pari al numero di record. Man mano che tolgo record e ne metto di nuovi la cardinalità scende. Ora, dopo la ricostruzione dell'indice, è a c.a. il 50% nel numero dei record. n.b.: è plausibile che nella tabella ci siano il 50% di duplicati di quel campo (forse più che meno), ma se la cardinalità è bassa per questo.. perchè non lo è da subito?

Questi sono i dati statistici della tabella:

Spazio utilizzato Tipo Utilizzo
Dati 315,8 MiB
Indice 229,6 MiB
In eccesso 450,0 MiB
Effettivo 95,4 MiB
Totale 545,4 MiB

che significano "in eccesso" e "effettivo"?

Grazie
01
Packages finder: slakfinder.org | Slackpkg+, per aggiungere repository a slackpkg
Codice: Seleziona tutto
1011010 1100101 1110010 1101111 - 0100000 - 1010101 1101110 1101111
Avatar utente
ZeroUno
Staff
Staff
 
Messaggi: 4398
Iscritto il: ven giu 02, 2006 13:52
Località: Roma / Castelli
Nome Cognome: Matteo Rossini
Slackware: current
Kernel: slack-current
Desktop: ktown-latest
Distribuzione: 01000000-current

Re: mysql: indici su tabelle grandi

Messaggioda ZeroUno » mar mar 30, 2010 6:09

con il char è improponibile.

A parte che ho fatto l'errore di optare per la conversione anzichè la creazione da zero (1h 45m di conversione), 900M di dati per 3.000.000 di record e 1G di indice, e alto peggiornamento dei tempi di ricerca.

Ciao
01
Packages finder: slakfinder.org | Slackpkg+, per aggiungere repository a slackpkg
Codice: Seleziona tutto
1011010 1100101 1110010 1101111 - 0100000 - 1010101 1101110 1101111
Avatar utente
ZeroUno
Staff
Staff
 
Messaggi: 4398
Iscritto il: ven giu 02, 2006 13:52
Località: Roma / Castelli
Nome Cognome: Matteo Rossini
Slackware: current
Kernel: slack-current
Desktop: ktown-latest
Distribuzione: 01000000-current


Torna a Programmazione

Chi c’è in linea

Visitano il forum: Nessuno e 1 ospite