Migliorare performance query/database milioni record

di
John23
il
7 risposte
J
John23
Pochi posts
Iscritto da
lug, 2016
Messaggi:
12

Migliorare performance query/database milioni record

Salve a tutti, ho una applicazione che interagendo con uno script php fa delle query a un database.

Il Database è cosi formato:

Tabella recordtable:

Name (varchar)(Primary key)
Record(int/bigint)(Index)


Esempio:
...
Name | Record
Luca | 256
Giulio | 3
Alessio| 12
...


Ora da script php , avendo un determinato nomeidx e il recordx, avrei bisogno di recuperare la sua posizione nel database, per ora eseguo una query di questo tipo :

php script: 

...

//in $record ho il record dell utente x
//in $name ho il nomeid dell utente x
//in $misqly ho la connessione al database


$query= mysqli_query($mysqli,"SELECT COUNT(Name) as rank  FROM recordtable Where Record > '$record' ");
$data=mysqli_fetch_assoc($query);
echo $data['rank'];

...
Ho preferito questo a un ORDER_BY perchè più veloce, ma il problema sta sempre nelle performance, ovvero con la configurazione del database sopra citata impiego :

10 secondi per fare una query di questo tipo su un database con 10 milioni di record nella tabella recordtable.


Vorrei migliorare le performance, considerando milioni di utenti, agendo o sullo script o sul database, consigli?

Grazie.

7 Risposte

  • M
    migliorabile
    Super Famoso
    Iscritto da
    apr, 2013
    Messaggi:
    6034

    Re: Migliorare performance query/database milioni record

    INDICI, devi costruire GLI INDICI!!!!!

    E gli indici vanno creati in base alla QUERY.

    Poi devi assicurarti di aggiornare le STATISTICHE che servono al DBMS per sapere se deve o meno usare l'indice.

    Comunque, con MILIONI DI UTENTI, entrano in gioco strategie decisamente piu' sofisticate, e carichi di lavoro molto piu' importanti.
    Queste problematiche sono al di fuori delle competenze di uno sviluppatore medio, e richiedono conoscenze piu' raffinate.
  • J
    John23
    Pochi posts
    Iscritto da
    lug, 2016
    Messaggi:
    12

    Re: Migliorare performance query/database milioni record

    migliorabile ha scritto:

    INDICI, devi costruire GLI INDICI!!!!!

    E gli indici vanno creati in base alla QUERY.

    Poi devi assicurarti di aggiornare le STATISTICHE che servono al DBMS per sapere se deve o meno usare l'indice.

    Grazie per la risposta, comunque gli indici li ho già creati in base alla query infatti ho un indice sul campo Record della tabella e una chiave primaria sul campo Name, questi mi velocizzano, già ora, molto la ricerca.

    migliorabile ha scritto:

    Comunque, con MILIONI DI UTENTI, entrano in gioco strategie decisamente piu' sofisticate, e carichi di lavoro molto piu' importanti.
    Queste problematiche sono al di fuori delle competenze di uno sviluppatore medio, e richiedono conoscenze piu' raffinate.

    Non ci sono manuali o articoli da leggere sui quali cercare di apprendere queste conoscenze?

    ( Partizionare il Database non credo convenga perchè dovrei avere delle tabelle partizionate ordinate per Record, altrimenti la ricerca dovrei sempre effettuarla su tutte le partizioni per trovare e contare tutti i record maggiori del record dell utentex )
  • +m+
    +m+
    Molto attivo
    Iscritto da
    gen, 2016
    Messaggi:
    712

    Re: Migliorare performance query/database milioni record

    Vabbè
    1) usi una metodologia di accesso PHP sconsigliatissima giacchè prona a voragini di sicurezza
    2) SELECT COUNT(Name) as rank FROM recordtable Where Record > qualcosa
    è una interrogazione con RANGE, che funziona bene con indici di tipo btree (su record).
    Il count invece se ne frega dell'esistenza di un indice su name (ovviamente).
    Le cose da fare sono
    a- fai un EXPLAIN e vedi cosa ti dice
    b- valuta la SELETTIVITA' dell'indice (cosa fondamentale)
    c- valuta la possibilità di creare una tabella derivata in cui, ad esempio mediante trigger, aggiorni i conteggi. meglio ancora ovviamente da applicazione
    d- caso (c) ma con sharding verticale (questa è per veri esperti) per aumentare la concorrenza
    e- che storage engine utilizzi?
    f- che versione di mysql utilizzi? Meglio sarebbe usare mariadb almeno 10.x, attivare le statistiche slow log. ovviamente con (a) e (b) già puoi sapere cosa accade
    g- puoi anche shardare orizzontalmente, avendo tra l'altro una condizione a range viene pure bene, PERO' impatteresti su tutte le altre query
    h- a seconda di f potresti valutare anche un group by, invece di count, per quella query specifica.
  • J
    John23
    Pochi posts
    Iscritto da
    lug, 2016
    Messaggi:
    12

    Re: Migliorare performance query/database milioni record

    Intanto Grazie per la risposta.

    +m+ ha scritto:

    1) usi una metodologia di accesso PHP sconsigliatissima giacchè prona a voragini di sicurezza
    Che metodologia dovrei/potrei usare?

    +m+ ha scritto:

    2) SELECT COUNT(Name) as rank FROM recordtable Where Record > qualcosa
    è una interrogazione con RANGE, che funziona bene con indici di tipo btree (su record).
    Il count invece se ne frega dell'esistenza di un indice su name (ovviamente).
    Le cose da fare sono
    a- fai un EXPLAIN e vedi cosa ti dice
    b- valuta la SELETTIVITA' dell'indice (cosa fondamentale)

    Ho analizzato la tabella e gli inidici presenti sono entrambi di tipo BTREE e penso funzionino bene poichè senza essi il tempo di esecuzione della query aumenta di molto.

    +m+ ha scritto:

    c- valuta la possibilità di creare una tabella derivata in cui, ad esempio mediante trigger, aggiorni i conteggi. meglio ancora ovviamente da applicazione
    d- caso (c) ma con sharding verticale (questa è per veri esperti) per aumentare la concorrenza
    Sinceramente non sono molto informato sull' argomento, proverò a informarmi, questa tabella derivata deve essere mantenuta aggiornata come una classifica con i record in ordine decrescente? grazie.

    +m+ ha scritto:

    e- che storage engine utilizzi?
    f- che versione di mysql utilizzi? Meglio sarebbe usare mariadb almeno 10.x, attivare le statistiche slow log. ovviamente con (a) e (b) già puoi sapere cosa accade
    Utilizzo InnoDB e versione di mysql 5.6.17

    +m+ ha scritto:

    h- a seconda di f potresti valutare anche un group by, invece di count, per quella query specifica.
    Con un GROUP BY dovrei fare una subquery per potere contare la posizione nel database del record selezionato, ho già provato ma va più lenta la ricerca.


    Mi consigli qualcosa nello specifico per migliorare le prestazioni (anche radicali cambi di struttura della base di dati)?
    Perchè per mantenere una classifica con cosi tanti punteggi utente ordinati, che sia efficiente nella ricerca (sotto i secondi), restituendo la posizione dell utente che ne fa la richiesta, non ho spunti ( a parte quelli che mi avete dato, grazie) sui quali aggrapparmi.
  • +m+
    +m+
    Molto attivo
    Iscritto da
    gen, 2016
    Messaggi:
    712

    Re: Migliorare performance query/database milioni record

    A) e b) sono i due elementi principali

    per 1) PDO
  • +m+
    +m+
    Molto attivo
    Iscritto da
    gen, 2016
    Messaggi:
    712

    Re: Migliorare performance query/database milioni record

    Poi non mi è chiaro se vuoi il rank di TUTTI gli utenti, o solo di 1
    Carica da qualche parte il dump di un pezzetto del db e vediamo
  • +m+
    +m+
    Molto attivo
    Iscritto da
    gen, 2016
    Messaggi:
    712

    Re: Migliorare performance query/database milioni record

    Se ho capito bene vuoi il rank, cioè il numero nella classifica ordinato per Record
    Primo, secondo, terzo... n-esimo

    Da questo esempio...

    Name | Record
    Luca | 256
    Giulio | 3
    Alessio| 12

    io interpreto (certo un po' vaghino, comunque...) che vuoi questo...

    Name | Record |rank
    Luca | 256 |1
    Alessio| 12 |2
    Giulio | 3 |3


    Una tecnica carina è questa 
    SELECT Name,Record,@precedente:=@corrente,@corrente:=Record,@rank:=IF(@precedente=@corrente, @rank, @rank+1) AS rank
FROM recordtable,
(SELECT @corrente:=null,@precedente:=null,@rank:=0) as inutile
ORDER BY Record desc
    e metti pure l'explain

    prima dell'order by puoi mettere un WHERE qualcosa, ovviamente è una selezione sul record, non sul risultato

    Se vuoi il rank di un record ben preciso puoi fare così, con una subquery derivata e piazzando il WHERE fuori
    SELECT Name,Record,rank FROM
(
SELECT Name,Record,@precedente:=@corrente,@corrente:=Record,@rank:=IF(@precedente=@corrente, @rank, @rank+1) AS rank
FROM recordtable,
(SELECT @corrente:=null,@precedente:=null,@rank:=0) as inutile
ORDER BY Record desc
) as inutile2
WHERE Name="pippo"
    in questo caso se metti "Alessio" salta fuori che è il numero 2 nell'elenco


    a me sembra abbastanza chiaro, vedi te se lo è
Devi accedere o registrarti per scrivere nel forum
7 risposte