linuxnews - MySQL binario contro non binario per gli hash ID



string binary (2)

Dal manuale :

The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except
that they contain binary strings rather than non-binary strings. That is,
they contain byte strings rather than character strings. This means that
they have no character set, and sorting and comparison are based on the
numeric values of the bytes in the values. 

Poiché CHAR (32) BINARY fa sì che una colonna BINARY (32) venga creata sotto il cofano, il vantaggio è che ci vorrà meno tempo per ordinare da quella colonna, e probabilmente meno tempo per trovare le righe corrispondenti se la colonna è indicizzata.

Supponendo che voglio usare un hash come ID invece di un numerico. Sarebbe un vantaggio in termini di prestazioni archiviarli come BINARY rispetto a quelli non binari?

CREATE TABLE `test`.`foobar` (
  `id` CHAR(32) BINARY CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  PRIMARY KEY (`id`)
)
CHARACTER SET ascii;

Answer #1

Sì. Spesso un hash digest è memorizzato come rappresentazione ASCII di cifre esadecimali, ad esempio MD5 della parola "hash" è:

0800fc577294c34e0b28ad2839435945

Questa è una stringa ASCII di 32 caratteri.

Ma MD5 produce davvero un valore hash binario a 128 bit. Questo dovrebbe richiedere solo 16 byte per essere memorizzati come valori binari invece di cifre esadecimali. Quindi puoi guadagnare un po 'di efficienza spaziale usando le stringhe binarie.

CREATE TABLE test.foobar (
  id BINARY(16) NOT NULL PRIMARY KEY
);

INSERT INTO test.foobar (id) VALUES (UNHEX(MD5('hash')));

Ri. i tuoi commenti che sei più preoccupato per le prestazioni rispetto all'efficienza dello spazio:

Non so per nessuna ragione che il tipo di dati BINARY sia più veloce di CHAR.

Essere mezzo grande può essere un vantaggio per le prestazioni se si utilizzano efficacemente i buffer della cache. Cioè, una data quantità di memoria cache può memorizzare il doppio delle righe di dati BINARY se la stringa è metà della dimensione del CHAR necessario per memorizzare lo stesso valore in hex. Allo stesso modo la memoria cache per l'indice su quella colonna può memorizzare il doppio.

Il risultato è una cache più efficace, perché una query casuale ha maggiori possibilità di colpire i dati o l'indice memorizzati nella cache, invece di richiedere l'accesso al disco. L'efficienza della cache è importante per la maggior parte delle applicazioni di database, perché in genere il collo di bottiglia è l'I / O del disco. Se è possibile utilizzare la memoria cache per ridurre la frequenza di I / O del disco, è molto più difficile per il dollaro rispetto alla scelta tra un tipo di dati o un altro.

Per quanto riguarda la differenza tra una stringa di hash memorizzata in BINARY rispetto a un BIGINT, sceglierei BIGINT. L'efficienza della cache sarà ancora maggiore, e anche per l'aritmetica dei numeri interi dei processori a 64 bit e dei confronti dovrebbe essere molto veloce.

Non ho misurazioni per supportare le affermazioni di cui sopra. Il vantaggio netto di scegliere un tipo di dati rispetto all'altro dipende molto dai modelli di dati e dai tipi di query nel database e nell'applicazione. Per ottenere la risposta più precisa, devi provare entrambe le soluzioni e misurare la differenza.

Ri. Supponendo che il confronto tra stringhe binarie sia più veloce del confronto tra stringhe senza distinzione tra maiuscole e minuscole, ho provato il seguente test:

mysql> SELECT BENCHMARK(100000000, 'foo' = 'FOO');
1 row in set (5.13 sec)

mysql> SELECT BENCHMARK(100000000, 'foo' = BINARY 'FOO');
1 row in set (4.23 sec)

Quindi il confronto tra stringhe binarie è del 17,5% più veloce rispetto al confronto tra stringhe senza distinzione tra maiuscole e minuscole. Ma notate che dopo aver valutato questa espressione 100 milioni di volte, la differenza totale è ancora inferiore a 1 secondo. Mentre possiamo misurare la differenza relativa della velocità, la differenza assoluta nella velocità è davvero insignificante.

Quindi ribadisco:

  • Misura, non indovinare o supporre. Le tue ipotesi formulate saranno sbagliate molto spesso. Misura prima e dopo ogni cambiamento che fai, quindi sai quanto ti ha aiutato.
  • Investire il tuo tempo e attenzione dove si ottiene il più grande successo per il dollaro.
  • Non sudare le piccole cose. Naturalmente, una piccola differenza si somma con iterazioni sufficienti, ma date queste iterazioni è comunque preferibile un miglioramento delle prestazioni con un vantaggio assoluto maggiore.




binary