Come organizzare un db con tantissimi record?

[Raccoon29]

Ciao a tutti, sono tornato...anche se in realtà sono nuovo in questa sezione: di solito spacco tutto in quello dei css ma vabé.

Sono qui quest'oggi a chiedervi un parere (dal punto di vista prettamente logico per ora) sul come organizzare un database per ospitare ed interrogare una grandissima quantità di record (dove per "grandissima" intendo "ever growing"), nella fattispecie vi vorrei chiedere quanto segue:
mettiamo per esempio il caso di un vocabolario, esso è costituito da una grande quantità di parole e col tempo è probabile che diventi sempre più pesante interrogarne il contenuto. Quindi la domanda è: è malsana l'idea di mirare le ricerche creando una tabella per ogni lettera dell'alfabeto per esempio? Tipo la tabella As che contiene le parole che cominciano per A, poi la Bs quelle che cominciano per B, Cs per C, Ds per D, Es...ecc.

In questo modo il problema non si risolve perché tanto si sovraccaricherà ugualmente, però almeno lo farà più tardi, no?

Ho posto la questione perché mi sorge un dubbio: finché è un vocabolario ok, a limite sono 26 tabelle divise e passa la paura, ma mettiate che sia per esempio invece una lista di chiamate telefoniche categorizzate per numero chiamante (tipo Telecom come fa??), è malsano pensare che ci sia una tabella per ogni numero telefonico anche se magari di numeri telefonici ce ne possono essere decine,centinaia, o addirittura migliaia?

Ecco questo è il dubbio che mi attanaglia: dichiaro qui aperto il dibattito

[deleted_29]

Originariamente inviato da Raccoon29

Ecco questo è il dubbio che mi attanaglia: dichiaro qui aperto il dibattito

dichiaro chiuso il dibattito
---
Sono situazioni totalmente diverse

1) ricerche di testo
2) ricerche full text

Le ricerche di testo (con "=") si effettuano banalmente con indici, tipicamente di tipo btree, con un partizionamento logico (se possibile), shard (se necessario).
In generale con "=" non ci sono grandi problemi. Puoi gestire tutte le telefonate effettuate in Italia dall'invenzione del telefono ad oggi con un normalissimo database opensource e un PC adatto a una segretaria.
Miliardi di righe senza troppo problemi.
E i tempi di accesso sono praticamente nulli, o quasi, perchè se esiste una funzione di ordinamento (e, in generale, esiste) gli indici consentono di accedere con meno di 10 accessi a quantità enormi di dati [qui si entra nel dettaglio, ma non mi sembra il caso di complicare il discorso con l'altezza effettiva]
---
Le ricerche full text sono tutt'altra cosa (sono le ricerche "alla google"), e si entra in un ambito totalmente diverso.

[Raccoon29]

Ti estirperei l'account msn/facebook per stare ore intere a parlare di questo giacché mi interesserebbe moltissimo approfondire nel dettaglio il discorso (dato che ti vedo ferratisssimo in materia!), ma mi rendo conto che non credo sia proponibile (non è che conosci qualche fonte: sito,libro,coupon,stele di rosetta...?)

In effetti un accesso "=" ad una tabella anche immensa richiede poco tempo, ma ho notato che un discorso di join introduce tempi apocalittici; tipo l'esempio di prima dell'archivio telefonico, metti che su una tabella ho la lista dei numeri associati a delle persone e io volessi recuperare le chiamate che magari una persona ha fatto in un lasso di tempo, quindi verrebbe una cosa del tipo:

SELECT * FROM chiamate,numeri 
WHERE chiamate.numero_chiamante=numeri.numero_del_tizio 
AND numeri.tizio=<ID> AND chiamate.data BETWEEN 
'<yyyy-mm-dd (DATA INIZIO)>' AND '<yyyy-mm-dd (DATA FINE)>'

Ora può darsi pure che mi sono tagliato con gli indici (non sono affatto ferrato in materia di indici, anche a scuola se n'è parlato pochissimo ), ma l'uso di una query di questo tipo allunga i tempi di eoni, per questo chiedevo se magari uno spezzettamento delle tabelle può aiutare (per mese e anno se non per numero, non aiuterebbe?)

Vorrei riaprire il dibattito ma rischio che mi meni

[optime]

a parte la forma obsoleta della join postata, è ovvio che NON avere indici sulle chiavi di join porta ad avere tempi di risposta inaccettabili

[Raccoon29]

Forma obsoleta?!? Ma è da sempre che faccio così
Two questions:

1- optime tu come l'avresti fatta quella query?
2- qualcuno ha un link collaudato dove poter far luce sul meraviglioso mondo degli indici?

PS: grazie intanto a tutti per i preziosi interventi!

[optime]

il fatto che tu faccia da sempre così NON vuol dire che sia la forma corretta

join: http://dev.mysql.com/doc/refman/5.0/en/join.html
index: http://dev.mysql.com/doc/refman/5.0/en/join.html

[deleted_29]

Originariamente inviato da Raccoon29
Ti estirperei l'account msn/facebook (...)

Questa non l'avevo mai sentita msn? facebook?

In effetti un accesso "=" ad una tabella anche immensa richiede poco tempo, ma ho notato che un discorso di join introduce tempi apocalittici (...)

Andiamo per ordine.
Join introduce SEMPRE rallentamenti, ciò accade per il modello relazionale che richiede accessi costosi, molto costosi.
L'argomento, da solo, richiederebbe approfondimenti realmente seri.
La versione superbreve è che l'uso di indici velocizza ANCHE i join (che restano, comunque, lenti).
Questo è uno dei motivi per i quali, spesso, non si utilizzano proprio (i join) a favore di tabelle denormalizzate, nel caso siano di grandi dimensioni ed i tempi di risposta importanti.

SELECT * FROM chiamate,numeri 
WHERE chiamate.numero_chiamante=numeri.numero_del_tizio 
AND numeri.tizio=<ID> AND chiamate.data BETWEEN 
'<yyyy-mm-dd (DATA INIZIO)>' AND '<yyyy-mm-dd (DATA FINE)>'

La formulazione del join va benissimo, non c'è nessuna "vera" necessità di utilizzare le forme esplicite SE non nel caso in cui ti serve effettivamente qualcosa di diverso (LEFT join, ad esempio).
Nella tua query gli elementi migliorabili sono

1) non usare mai select * (e vabbè)
2) usare le parentesi per rendere più semplice all'ottimizzatore delle query stabilire un piano (a=b) and (c=d) and (e=blablabla)
3) between non serve essenzialmente a nulla, a favore di >= e <=
4) gli indici sono buoni e giusti SE hanno selettività elevata

Ora può darsi pure che mi sono tagliato con gli indici (non sono affatto ferrato in materia di indici, anche a scuola se n'è parlato pochissimo ), ma l'uso di una query di questo tipo allunga i tempi di eoni, per questo chiedevo se magari uno spezzettamento delle tabelle può aiutare (per mese e anno se non per numero, non aiuterebbe?)

Vorrei riaprire il dibattito ma rischio che mi meni

son tutte cose banali.

"spezzettare" le tabelle (partizionamento o sharding) serve, eccome, in certi casi, in altri no.
---
Andiamo per ordine sugli indici, i quali sono (a livello di spiegazione banale)... un indice. Immagina semplicemente un libro coi titoli dei capitoli negli indici: leggendo quello (l'indice) puoi stabilire molto rapidamente dove si trova (o "circa" dove si trova) il dato che ti interessa.

Esistono numerosi tipi di indici; quelli "normali", ad albero, suddividono le chiavi in pagine ordinate.

Questo rende veloce interrogazioni di queste tipologie
= qualcosa
>= qualcosa
<= qualcosa
minimo(qualcosa)
massimo(qualcosa)
order by qualcosa (indiciato)
group by qualcosa (indiciato)
---
Nel caso del join, per tornare a bomba, avere due indici sui due campi usati per il join consente (*** poi vediamo quando NON consente ***) di avere esecuzioni molto più veloci.

L'effetto negativo degli indici è che sono costosi da generare, nel senso che rallentano le fasi di inserimento ed aggiornamento (* questo non è vero per tutti, ma facciamo finta di sì).

Quindi se crei 1000 indici ti troverai ad avere scritture lente, e magari neppure li userai in fase di lettura (* esistono programmelli che ti danno anche sotto forma di analisi automatica qualche informazione *)
---
Oltre al "costo" di crearli, e mantenerli, gli indici hanno tre grandissimi difetti che, spesso, non vengono trattati (neppure all'università), o solo meramente accennati.

1) selettività. Se un indice "prende dentro" tutte o quasi le righe, è inutile, anzi è dannoso.
Supponiamo di avere un elenco di 1000 indirizzi, 999 dei quali a milano.
Se hai un indice sulla città, e selezioni where citta='milano', in realtà non ottieni miglioramenti, perchè devi "smazzarti" 999 righe (sulle 1000 totali) attraverso un meccanismo indiretto di accesso all'indice.
In altre parole, in questo caso, l'indice è DELETERIO anzichè essere un vantaggio
(* in questo caso uno furbo avrebbe usato il complemento, ovvero selezionare le città che NON sono milano, ossia 1*)

In certi casi la selettività è alta, in altri è bassa, e non c'è niente da fare.
In quest'ultimo caso bisogna riflettere a fondo se e quando usarli

2) ricerche combinate con altri indici, ovvero con selezioni multiple.
Quando hai query che "mischiano" campi diversi, acceduti in modi diversi o contrari, può accadere che gli indici non siano utilizzabili (tutti).
In pratica rimangono esistenti (e quindi costosi), ma inutili

3) - specifico per i testi - inutilità, anzi dannosità, per ricerche del tipo LIKE '%qualcosa%', ovvero una sottostringa.
Gli indici "normali" (btree, ma anche hash), semplicemente, NON consentono di cercare parti di testo.
In questo esempio, potrei usare favorevolmente un indice per cercare la parte iniziale o finale, ma non quella in mezzo
LIKE 'FINI%' è buono
LIKE '%SILVIO' è buono
LIKE '%MASSIMO%' è MALE
FINI GIANFRANCO
BERLUSCONI SILVIO
BERRUTI MASSIMO MARIA

In questi casi si adottano approcci diversi, con indici di tipologie diverse e, nel caso specifico di mysql, quasi sempre con motori speciali di supporto (lucene, sphinx)
---
C'è tantissimo altro da dire sugli indici (ma proprio tanto), questa è l'infarinatura supersemplice

[las]

Originariamente inviato da Raccoon29
mettiamo per esempio il caso di un vocabolario, esso è costituito da una grande quantità di parole e col tempo è probabile che diventi sempre più pesante interrogarne il contenuto.

io spenderei 2 parole anche sul concetto di 'grande tabella' ... perchè spesso viene scambiato per grande quello che in realtà è molto piccolo, sia chiaro, a scopo teorico/didattico uno può pure ottimizzarsi alla perfezione una tabella con 2 record.
Però è bene sapere che nella pratica una tabella con 250.000 record (più o meno le parole della lingua italiana) è da considerarsi una tabella piccola, e con questo non voglio assolutamente dire che non va ottimizzata, ci mancherebbe, però definiamola per quello che è: una tabella di piccole dimensioni

[deleted_29]

Originariamente inviato da las
io spenderei 2 parole anche sul concetto di 'grande tabella' ... perchè spesso viene scambiato per grande quello che in realtà è molto piccolo, sia chiaro, a scopo teorico/didattico uno può pure ottimizzarsi alla perfisaezione una tabella con 2 record.
Però è bene sapere che nella pratica una tabella con 250.000 record (più o meno le parole della lingua italiana) è da considerarsi una tabella piccola, e con questo non voglio assolutamente dire che non va ottimizzata, ci mancherebbe, però definiamola per quello che è: una tabella di piccole dimensioni

dipende.
dipende da tanti aspetti.
se la interroghi spesso per fare ricerche tipo forum, su una tabella myisam per avere le ricerche full text, avrai lock sull'intera tabella.
un mix di scritture-letture farà sì che la piccola tabella non sia piccola per nulla.
---
analogamente se la tabella contiene, ad esempio, record contenenti campi UUID (non indiciati) e vuoi un ordinamento, allora è grande.

Se stai scrivendo un'applicazione sqllite per android, è grande in tutti i casi.
Se la usi per fare delle (mysql) where qualcosa IN (select ... from tabella) allora è gigantesca.
Se è una tabella dei CAP (che son circa 85.000, comprese le città suddivise in zone), e vuoi fare una ricerca "alla google+ajax", ossia con "precompilazione" dei risultati, è grande.
---
"grande" e "piccolo" dipende essenzialmente dalla tipologia di problema.
"davvero grande", in generale, è quando le pagine degli indici sono così grandi da non riuscire a risiedere nella RAM del computer.

[Raccoon29]

franzauker ti voglio un gran bene.

Praticamente da tutto ho capito che ho grosse lacune in campo db, e per ovviare a questo un buon libro di amazon sta viaggiando verso di me...spero di riuscire a rimettermi "in pari" (in pari proprio è impossibile, però almeno a riguadagnare terreno: fare progetti mastodontici che magari soffrono di colli di bottiglia da uso superficiale del database sarebbe ridicolo!).

Intanto un grande grazie a chi è intervenuto!

il fatto che tu faccia da sempre così NON vuol dire che sia la forma corretta

Non l'ho mai preteso

join: http://dev.mysql.com/doc/refman/5.0/en/join.html
index: http://dev.mysql.com/doc/refman/5.0/en/join.html

ehm, i link sono identici

Però è bene sapere che nella pratica una tabella con 250.000 record (più o meno le parole della lingua italiana) è da considerarsi una tabella piccola

Sì infatti, questo lo so bene. Quello del vocabolario era più un titolo di esempio (quello che mi veniva in mente mentre scrivevo). L'analogia più veritiera al momento per il mio caso è quella della lista delle telefonate con l'archivio dei numeri di telefono, dove i numeri di telefono sono circa 800, mentre la lista delle chiamate è dell'ordine dei milioni. Ora non so se questa pure può essere considerata piccola, ma i tempi di risposta al momento mi stanno creando non pochi problemi