[MySQL] Proggettazione db

[perseoclub]

Salve a tutti,

lo scorso anno ho realizzato un mio gestionale con php/mysql..
A distanza di un anno vorrei allargare il mio software ad altri reparti della mia struttura, ma abbiamo notato che usando il precedente progetto inserendo molti dati nel corso del tempo il sistema si rallenta parecchio per determinate operazioni.

Vi faccio un esempio:

Ho una tabella `ORDINI` (InnoDB) con alcuni campi, e tra questi il campo `isFinished` (0/1).
Quando gli ordini vengono completati il campo `isFinished` diventa = 1, così il giorno successivo.. quando eseguo le SELECT di tutti gli `ordini_id` ancora in sospeso (`isFinished` = 0) il sistema mi restituisce gli ID su cui si lavora. L'applicazione interroga spesso il DB con select di questo tipo (anche con dei cicli, ecc.).
Nel corso dei mesi.. essendo cresciuta parecchio questa tabella.. le query ci mettono sempre più tempo.

Come posso risolvere?

Ad esempio.. usare una VIEW? usare tabelle MEMORY? oppure MERGE? Insomma.. che consiglio mi potete dare per snellire il processo d'interrogazione sul DB?

Grazie

 

[marino51]

indicizzare le colonne specificate nelle clausole where
creando anche chiavi di ricerca composte (+ colonne nello stesso indice, se necessario) avendo cura, in questo caso, di mettere come primo elemento la colonna + "spezzata" ed a seguire le altre con lo stesso criterio

 

[perseoclub]

Grazie Marino

Ho ridato un'occhiata agli indici.. ma cosa intendi per.. + "spezzata"?

 

[marino51]

supponiamo di avere un elenco di articoli,
gli articoli sono associati ciascuno ad una classe (es. articolo blu, rosso, verde, giallo ....)
gli articoli hanno un indicatore che definisce la disponibilità / indisponibilità

se devo cercare gli articoli disponibili di classe gialla, ed ho un indice così definito, ho buona probabilità di leggere almeno metà dell' insieme articoli, dovendo scorrere tutti i disponibili per trovare i gialli

se invece l'indice è definito con classe e indicatore leggo solo i gialli selezionando i disponibili, scorrendo meno elementi

la classe è più "spezzata" rispetto all'indicatore

 

[perseoclub]

Ho una tabella `prestazioni` (con una decina di campi) che al momento contiene quasi 16.000 rows..

Avevo inizialmente inserito 3 indici, quello primario e altri 2 indici per relazionarla con altre due tabelle.

Il campo `prest_isFinished` non era indicizzato.

Quando eseguivo questo EXPLAIN mi dava questo risultato..

'EXPLAIN
SELECT prest_id
FROM prestazioni
WHERE prestazioni.prest_isFinished = TRUE;'

id + select_type + table ......+ partitions + type + possible_keys + key .+ key_len + ref .+ rows .+ filtered + Extra
1 .+ SIMPLE .....+ prestazioni + NULL ......+ ALL .+ NULL .........+ NULL + NULL ...+ NULL + 15846 + 10.00 ...+ Using where


Inserendo un indice key nel campo `prest_isFinished` ottengo questo nuovo EXPLAIN

id + select_type + table ......+ partitions + type + possible_keys ...+ key .............+ key_len + ref ..+ rows + filtered + Extra
1 .+ SIMPLE .....+ prestazioni + NULL ......+ ref .+ prest_isFinished + prest_isFinished + 1 ......+ const + 7923 + 100.00 ..+ Using index

(...) i Puntini li ho messi per mantenere il layout

Se ho capito bene dovrei inserire un indice su ogni campo delle mie tabelle dove impongo clausole where di questo tipo?
Ho letto che gli indici velocizzano in fase di SELECT ma rallentano i processi di INSERT e UPDATE.. quindi dovrei utilizzarli con prudenza, solo sui campi di query "pesanti"?

Tieni presente che spesso uso query che mettono in relazione fino a 7/8 tabelle con 2/3 clausole where (oltre a quelle relazionali tra le tabelle)..

 

[marino51]

Se ho capito bene dovrei inserire un indice su ogni campo delle mie tabelle dove impongo clausole where di questo tipo?

con l'indice, la select ha trattato poco meno della metà dei record presenti, sicuramente un risparmio anche se poco percettibile,
in ogni caso conviene creare indici combinati in funzione delle clausole where, per esempio

where ColA = 'xyz' and ColB = 'qaz' and ColC = 33

potrebbe essere utile un indice unico che tratti ColA + ColB + ColC

Ho letto che gli indici velocizzano in fase di SELECT ma rallentano i processi di INSERT e UPDATE.. quindi dovrei utilizzarli con prudenza, solo sui campi di query "pesanti"?

dipende da quanti record vengono trattati contemporaneamente,
se inserisco o se faccio update di pochi record, non è percettibile il tempo
se aggiorno tutte le chiavi di ricerca di una catena per migliaia di record o se inserisco migliaia di record in sequenza, qualche problemino potrei averlo se la macchina "server" non è ben attrezzata

Tieni presente che spesso uso query che mettono in relazione fino a 7/8 tabelle con 2/3 clausole where (oltre a quelle relazionali tra le tabelle)..

meglio creare una view, con tutti i relativi indici, lavorando con una query semplice, il "sistema" è già preparato alle specifiche ricerche

ps, i dati statici, "da archivio", andrebbero tolti periodicamente dalle tabelle operative in modo da non intralciare le operazioni di insert/update, ma inclusi nelle ricerche select con "view" che includono tabelle operative + tabelle d'archivio, ovviamente in base alle quantità di record

andrebbe fatta un'attenta ricognizione sull'applicazione in modo da evidenziare tutte le query e organizzare indici adeguati,
ms sql mette a disposizione tools tipo "Database Engine Tuning Advisor" suggerendo le opportune indicizzazioni in funzione delle query, forse qualcosa di simile esiste anche x mysql

 

[perseoclub]

Ecco, questa è una cosa che mi son chiesto spesso ma non ho avuto modo di capire.

Ignorantemente io pensavo che le VIEW fossero delle query "preconfezionate" con alcune funzionalità (tipo il controllo di sicurezza dei permessi).. ma avessero la stessa velocità delle query.. E visto che non mi servono controlli di sicurezza in quanto gestisco gli accessi da php.. avevo escluso il loro utilizzo.
Se ho capito quindi le VIEW potrebbero essere ottimizzate rispetto alle "semplici" query, ad esempio con un campo che potrei indicizzare solo a livello di VIEW (ma non nella tabella "originale")? Se così fosse, potrei crearmi tutte le VIEW indicizzate nello specifico, senza dover andare ad indicizzare il campo "originale" della tabella. Giusto?

Divago un attimo: forse sarebbe indicato rivedere anche il concetto di sicurezza e relativi accessi a DB direttamente su Mysql anziché da php?

Inoltre mi consigli di separare la parte logistica da quella di archivio di una tabella.. e di riunirle all'occorrenza tramite un'apposita VIEW?
Prendendo per esempio una tabella utenti: dovrei splittarla in 2/3 parti:
1) `utenti` → `ut_id`*, `ut_nome`, `ut_cognome`, `ut_codiceFiscale`
2) `utentiDati` → `ud_id`*, `ut_id`^, `ud_indirizzo`, `ud_telefono`, `ud_mail`, ecc..
3) `utentiInfo` → `ui_id`*, `ut_id`^, `ui_dataNascita`, `ui_luogoNascita`, `ui_sesso`, `ui_altezza`

* chiave primaria.
^ chiave relazionale.

..oppure dovrei usare lo stesso `ut_id` (e togliere `ud_id` e `ui_id`)?

Se fosse così, quando faccio una SELECT, un INSERT, un UPDATE o un DELETE.. dovrei usare una VIEW..
Se per esempio voglio fare parecchie ricerche sugli utenti alti tot cm.. dovrei creare un indice sul campo `ui_altezza`.. mentre se volessi ricercare tutti i Maschi/Femmine per residenza.. dovrei creare un indice multiplo dei due campi `ud_indirizzo` & `ui_sesso`?

 

[marino51]

Se ho capito quindi le VIEW potrebbero essere ottimizzate rispetto alle "semplici" query,

si

sicurezza e relativi accessi a DB direttamente su Mysql anziché da php?

si, creando l'utente specifico (che si accredita per l'applicazione) in mysql ed utilizzando lo stesso per la connessione
(non conosco mysql ma penso che si possa, come per altri)

logistica da quella di archivio di una tabella.. e di riunirle all'occorrenza tramite un'apposita VIEW?

si, ho dei db con qualche milionata di record che lavorano con questa tecnica, con operazioni sia online che batch, le prestazioni sono molto buone
in ms sql è facile muovere record tra tabelle

DELETE FROM tabella
OUTPUT DELETED.*
INTO archivio
WHERE condizioni

Prendendo per esempio una tabella utenti: dovrei splittarla in 2/3 parti:

dipende dalla quantità dei dati anche se penso che un'anagrafica dovrebbe avere dati abbastanza statici, ma questo dipende dall'applicazione

oppure dovrei usare lo stesso `ut_id` (e togliere `ud_id` e `ui_id`)?

ho sempre evitato gli "id" perchè quasi mai necessari e forieri di problemi, potremmo definire "CodiceCliente" ed é la chiave per tutte le informazioni connesse (se per caso è un autoincrement, non importa è il codice cliente !)

Se fosse così, quando faccio una SELECT, un INSERT, un UPDATE o un DELETE.. dovrei usare una VIEW.. Se per esempio voglio fare parecchie ricerche sugli utenti alti tot cm.. dovrei creare un indice sul campo `ui_altezza`.. mentre se volessi ricercare tutti i Maschi/Femmine per residenza.. dovrei creare un indice multiplo dei due campi `ud_indirizzo` & `ui_sesso`?

si

 

[perseoclub]

Leggo solo ora..

Grazie mille Marino!

Mi trovo alle prese con un altro problema rognoso, sempre di progettazione.

Ho questa situazione. Facciamo finta che sia un ristorante, e un cliente può ordinare questo:

1) Antipasto (7€)
2) Risotto (8€)
3) Cotoletta (10€)
4) Antipasto & Risotto (12€) (non è la somma di 1+2)
5) Menù completo (20€) (non è la somma di 1+2+3)

I 3 "piatti" possono essere cucinati dal ristorante, ma sia il Risotto che la Cotoletta possono essere cucinati dal catering Tizio o dal catering Sempronio. E ognuno dei catering applica al ristorante la propria tariffa per portata richiesta (quindi per 2 e per 3), ma inoltre Sempronio fa al ristorante uno sconto per l'accoppiata 2+3.

La situazione come si gestisce?

Alla fine io 'devo' sapere, relativo al cliente X:
1) ogni piatto ordinato,
2) chi ha cucinato ogni piatto,
3) quanto ha speso il ristorante per ogni piatto (il proprio ha un costo di produzione),
4) quanto è stato incassato dal ristorante per ogni piatto,
5) il conto totale del cliente X.

Complicando le cose: come gestisco la situazione in cui il ristorante pensava di cucinare il risotto e si è accorta che non avendo ingredienti ha usufruito del catering? E se c'era anche la cotoletta nella comanda.. può usufruire dell'offerta di Sempronio 2+3 anziché pagarli distintamente?

Il mio "ristorante" offre oltre 1000 "piatti" diversi.. e in alcuni casi devo utilizzare dei servizi esterni. Quindi la situazione proposta è amplificata su questo ordine di richiesta.

 

[marino51]

I 3 "piatti" possono essere cucinati dal ristorante, ma sia il Risotto che la Cotoletta possono essere cucinati dal catering Tizio o dal catering Sempronio.

per semplificare la gestione, potremmo considerare 3(+) fornitori ciascuno con il proprio listino dei costi (listino acquisti) per piatto
ed un unico affidatario che affida la preparazione dei piatti al fornitore "migliore" in quel momento ("Assegnazione al fornitore")
(esempio, esaurimento capacità ristorante piuttosto che miglior guadagno = ricavo - costo, o altra condizione)

lo stesso affidatario gestisce listini dei ricavi (listino vendita) che applica al cliente specifico che ordina le portate.

i listini dei costi e dei ricavi sono modificabili nel tempo con delle date di validità

il listino dei ricavi può avere condizioni differenti per clienti specifici (ssn, fasi, fasdac, assicurazione....)

E ognuno dei catering applica al ristorante la propria tariffa per portata richiesta (quindi per 2 e per 3),
ma inoltre Sempronio fa al ristorante uno sconto per l'accoppiata 2+3.

tema da approfondire, come vengono applicati gli sconti ?
occorre determinare bene le regole per poterle applicare correttamente

"Controllo fatture" dei fornitori, come viene fatto ?
con questo passaggio si potrebbero legare i costi reali a ciascuna prestazione oltre alla verifica delle fatture ricevute
in alternativa i costi (teorici) vengono ricavati dai listini fornitori presenti in archivio

Alla fine io 'devo' sapere, relativo al cliente X:
1) ogni piatto ordinato dal cliente
2) il conto totale del cliente

1-2) elenco dal dettaglio ordine dei clienti e somma rispetto al listino dei ricavi applicato

3) ogni piatto ordinato,
4) chi ha cucinato ogni piatto,
5) quanto ha speso il ristorante per ogni piatto (il proprio ha un costo di produzione),
6) quanto è stato incassato dal ristorante per ogni piatto

3) vedi punto 1 sommato per piatto nel periodo analizzato
4) dalla "Assegnazione al fornitore"
6) dal listino dei ricavi applicato
5) dal listino dei costi applicato
7) primo margine di vendita per ogni piatto, dalla differenza dei due precedenti (magari x fornitore per valutare la convenienza)

Complicando le cose: come gestisco la situazione in cui il ristorante pensava di cucinare il risotto e si è accorta che non avendo ingredienti ha usufruito del catering? E se c'era anche la cotoletta nella comanda.. può usufruire dell'offerta di Sempronio 2+3 anziché pagarli distintamente?

riapplicando a variazione la "Assegnazione al fornitore" ( non avendo ingredienti = capacità produttiva, come introdurla ? potrebbe interessare anche il "catering" ? )

certo è una bella sfida,
conviene identificare bene tutte le fasi dei processi di "vendita" e di "acquisto", ragionandoci per applicare le strategie migliori

 

[perseoclub]

Perdonami Marino, ho dimenticato un dettaglio importantissimo. A volte capita che un piatto richiesto sia scorporato dai fornitori in diversi sottopiatti.
Esempio: Arrosto misto (11€) → il fornitore mi fa pagare i seguenti piatti (Maiale 2€, Vitello 2,5€, Salsiccia 1,5€), per un totale di 6€.

Ho usato impropriamente la parola 'sconto'.. in realtà sono pacchetti già definiti. Cioè `2+3` ha un suo costo, come `2` e come `3`. Può essere che `2+3` costi meno di `2`+`3`.

Quando scrivi affidatario.. intendi la tabella intermedia della relazione molti a molti (tra la domanda e la produzione)? Oppure è un passaggio ulteriore?

Al momento sono a questo punto (e non so se sia corretto), con dei passaggi più o meno lineari:

1) Tabella Offerte (menù)
Off_id (int, PRIMARY)
Off_nome_offerta
Off_prezzo_offerta

2) Tabella Piatti
Pia_id (int, PRIMARY)
Pia_nome_piatto

3) Tabella OffertePiatti
OffPia_id (int, PRIMARY)
Off_id (int, KEY)
OffPia_id (int, KEY)

4) Tabella ChiCucina
Cc_id (int, PRIMARY)
Cc_nome
Cc_telefono
...

5) Tabella Produzione (servizio per piatti scomposti)
Prod_id (int, PRIMARY)
OffPia_id (int, KEY)
Prod_chi_cucina (int KEY)
Prod_nome_piatto
Prod_costo_piatto

6) Tabella Ordinazione
Ord_id (int, PRIMARY)
Prod_id (int, KEY)
Ord_prezzoApplicato*

[*] Non è molto logica la cosa.. ma imponendo immediatamente il costoApplicato mi evito di dover ricontrollare tramite le date quale era il costo di listino, il giorno che venne fatta l'ordinazione. In effetti è un metodo ortodosso, però mi evita di eseguire altre operazioni e controlli in funzione delle date (datatype che trovo spesso estremamente rognoso). Magari in seguito, quando avrò ben presente lo schema generale, questo passaggio potrei 'rivederlo'.


Andando a fare una simulazione:

Tabella Offerte:
1, Antipasto, 7€
2, Risotto, 8€
3, Cotoletta, 10€
4, Contorno, 4€
5, Cotoletta+Contorno, 12€
6, ArrostoMisto, 11€

Tabella Piatti:
1, Antipasto
2, Risotto
3, Cotoletta
4, Contorno
5, ArrostoMisto
6, Porzione maiale di ArrostoMisto
7, Porzione manzo di ArrostoMisto
8, Porzione salsiccia di ArrostoMisto

Tabella OffertePiatti:
1, 1, 1 (→ antipasto, di antipasto)
2, 2, 2 (→ risotto, di risotto)
3, 3, 3 (→ cotoletta, di cotoletta)
4, 4, 4 (→ contorno, di contorno)
5, 5, 3 (→ cotoletta di cotoletta+contorno)
6, 5, 4 (→ contorno di cotoletta+contorno)
7, 6, 5 (→ arrosto misto)
8, 6, 6 (→ porzione maiale, di arrosto misto)
9, 6, 7 (→ porzione manzo, di arrosto misto)
10, 6, 8 (→ porzione salsiccia, di arrosto misto)

Tabella ChiCucina:
1, Ristorante, ...
2, CateringTizio, ...
3, CateringSempronio, ...

Tabella Produzione:
1, 1, 1, Antipasto, 2€
2, 2, 1, Risotto, 3€
3, 2, 2, RisottoTizio, 4.5€
4, 2, 3, RisottoSempronio, 5€
5, 3, 1, Cotoletta, 4€
6, 3, 2, CotolettaTizio, 6€
7, 3, 3, CotolettaSempronio 5.5€
8, 4, 1, Contorno, 1.5€
9, 4, 3, ContornoSempronio, 2.5€
10, 5, 1, Cotoletta (di Cotoletta+Contorno), 4€
11, 5, 3, CotolettaSempronio (di Cotoletta+Contorno), 5€
12, 6, 1, Contorno(di Cotoletta+Contorno), 2.5€
13, 6, 3, ContornoSempronio (di Cotoletta+Contorno), 2€
14, 7, 1, ArrostoMisto, 5€
15, 8, 2 ,Porzione maiale (di arrosto misto) Tizio, 2€
16, 9, 2, Porzione manzo (di arrosto misto) Tizio, 2,5€
17, 10, 2, Porzione salsiccia (di arrosto misto) Tizio, 1,5€
18, 7, 3, ArrostoMistoSempronio, 7€

Tabella Ordinazioni:
.....


Il problema di questa mia progettazione è che alcuni piatti vengono ripetuti, ma in realtà sono gli stessi (cambia solo il prezzo). Vedi la cotoletta o il contorno di Sempronio.. avrebbero 2 id differenti, mentre invece sono la stessa produzione, ma con prezzo variato. Io vorrei mantenere sempre lo stesso id per ogni piatto, e gestire i prezzi separatamente.
Per questo ti dicevo, come gestire il caso in cui il ristorante prende un ordine (ad esempio l'arrosto misto) a cui applica il corrispettivo id, ma si accorge che manca la carne per cucinarlo e deve girare l'ordine a Sempronio (lui lo scorpora in 3 sottopiatti, quindi non più un solo id, ma 3 differenti id). Quindi nella tabella ordinazioni non avrò più una sola riga ma 3.
O viceversa, il ristorante sceglie in primis il catering Sempronio, che poi respinge l'ordine perché ha finito la carne, quindi deve prepararsela di per sè.. da 3 rows passiamo ad 1.

Credo che il problema sia molto complesso, e non trovo una strada valida per strutturarlo.

Puoi darmi qualche consiglio?

 

[marino51]

impegnato all'estero, tornato oggi, mi ero portato il tuo ultimo post, ma non ho avuto spazi, in ogni caso come hai scritto,

Credo che il problema sia molto complesso

si abbastanza, ed è difficile fare proposte con dei semplici post senza una discussione di confronto,
proverei prima a definire il processo
identificando gli attori, cosa portano e cosa chiedono in termini di documenti e informazioni
perché non mi sembra solo un elenco di piatti e la loro cucina con dei legami tra tabelle

 

[perseoclub]

Ciao Marino.

Nel frattempo mi sono scervellato a cercare un'accettabile soluzione e alla fine ho optato per il concreto (a discapito della relazionalità).

Venendo meno alla buona prassi, sto valutando di procedere in tal senso:

MENU
1) creo una tabella[1] con le richieste ("gruppi di piatti" e "piatti singoli") contenente il campo del "prezzo al cliente"

2) creo una tabella[2] con tutti i "piatti singoli"

3) creo la tabella[3] per la relazione N-N tra la tabella[1] e la tabella[2]

4) creo una tabella[4] con "piatti singoli x chi li cucina", in relazione 1-N con la tabella[2], contenente il campo del "costo di produzione"


ORDINAZIONI
5) creo la tabella[5] ordini, in relazione 1-N con la [1] (e riscrivo il campi del "prezzo applicato" → in modo tale che se anche in futuro aggiornassi i prezzi della tabella[1] ..mantengo traccia di quanto pagò il cliente con il tariffario dell'epoca.

6) Attraverso questa relazione in tabella[5], posso estrarre i singoli piatti della tabella[2] dalla tabella[3].. e ognuno lo inserisco nella tabella[6] lavorazioni, in relazione 1-N con [5] e con [4] (e anche qui riscrivo quanto è il "prezzo di produzione" in modo che se aggiornassi i costi della tabella[4] ..mantengo traccia di quanto costò all'epoca.

È uno schema spartano ma dovrebbe funzionare, in relazione del fatto che le tabelle vengo aggiornate raramente (quindi se dovesse servire al limite potrei crearmi in futuro uno script php per aggiornare i campi 'manualmente').

Che ne pensi? Ti tengo aggiornato..

 

[marino51]

con un po' di ritardo,
continuo a dirti che è difficile per me dare un giudizio,
mi sembra che stai seguendo il concetto della "distinta base" che trovo più idoneo e semplice
al contempo se tu definissi i passi di un processo, le tabelle diventano la naturale congiunzione di un passo con il successivo rendendo più semplice la progettazione