Codurile prefix și indicele de selectivitate
Uneori doriți să indice un foarte lung coloane de caractere, ca urmare a ceea ce indicii sunt mari și lente. O strategie este emularea unui indice hash, așa cum am arătat mai sus. Dar, uneori, acest lucru nu este suficient. Ce altceva se poate face?
Puteți salva de multe ori spațiu și pentru a obține o performanță bună, care sunt indexate de primele caractere, dar nu toate diferenta. Apoi, indicele va ocupa mai puțin spațiu, dar va deveni mai puțin selectivă. index Selectivitatea - raportul dintre numărul de valori diferite sunt indexate (cardinality) la numărul total de rânduri în tabel (#T). Intervalul de valori posibile ale selectivității 1 / # T la 1. Index cu selectivitate ridicată este bun, deoarece permite MySQL pentru a căuta relevante filtru mai multe rânduri. Un index unic are o selectivitate egală cu unu.
Prefixul coloanei este adesea foarte selectiv, pentru a asigura o bună performanță. Dacă se indexează un coloane BLOB sau TEXT, sau coloane foarte lungi, cum ar fi un VARCHAR, atunci obligația de a determina codurile prefix, deoarece MySQL nu permite o astfel de coloane indexate de lungimea lor.
Dificultatea constă în alegerea lungimii de prefix, care ar trebui să fie suficient de mare pentru a asigura o bună selectivitate, dar nu prea mare pentru a economisi spațiu. Prefixul este ales atât timp cât să beneficieze de utilizarea sa a fost aproape la fel ca și utilizarea indicelui coloană completă. Cu alte cuvinte, prefixul cardinalitatea ar trebui să fie aproape la fel ca și întreaga coloană cardinalitatea.
Pentru a determina lungimea corespunzătoare a prefixului, găsi valori comune și lista de comparare a acestora cu lista prefixele cele mai frecvent utilizate. Baza de date de testare Sakila 1 nici un exemplu adecvat pentru a demonstra datele, astfel încât vom crea un tabel bazat pe orașul mesei, astfel încât am avut suficiente date:
CREATE TABLE sakila.city_demo (oraș VARCHAR (50) NOT NULL);
INSERT INTO sakila.city_demo (oraș) oraș SELECT FROM sakila.city;
-- Se repetă următoarea comandă de cinci ori:
INSERT INTO sakila.city_demo (oraș) oraș SELECT FROM sakila.city_demo;
-- Randomizing distribuția (ineficiente, dar convenabil):
city SET = (SELECT oras FROM COMANDA sakila.city BY RAND () LIMIT 1);
Acum avem un set de valori de test. Distribuția rezultatelor este departe de a fi realist, asa ca am folosit functia RAND (). Din acest motiv, veți observa rezultate diferite de ale noastre, dar pentru acest exemplu nu este esențial. În primul rând, vom găsi cele mai frecvente ale orașului:
mysql> SELECT COUNT (*) AS CNT, oraș
-> DE LA GROUP sakila.city_demo DE ORDIN oraș prin CNT DESC LIMIT 10;
Rețineți că fiecare valoare are loc între 45 și 65 de ori. Acum vom găsi comune prefixele numelor orașelor, începând cu trei litere:
mysql> SELECT COUNT (*) AS cnt, LEFT (oraș, 3) AS pref
-> DE LA GROUP sakila.city_demo DE COMANDA pref PRIN cnt DESC LIMIT 10;
Numărul de apariții ale fiecărui prefix este mult mai mare, astfel încât prefixele unic este mult mai mică decât valoarea totală nume unice orașului. Ideea este de a crește lungimea prefixului, atâta timp cât nu va fi aproape la fel ca și selectiv pe întreaga lungime a coloanei. Mai multe experimente au permis să afle că șapte caractere este suficient:
mysql> SELECT COUNT (*) AS cnt, LEFT (oraș, 7) AS pref
-> DE LA GROUP sakila.city_demo DE COMANDA pref PRIN cnt DESC LIMIT 10;
Un alt mod de a determina lungimea prefixului corespunzătoare este de a calcula coloana completă selectivitate și să încerce să ridica lungimea prefixului, care prevede selectivitate aproape. Iată cum să găsiți selectivitatea coloanei completă:
mysql> SELECT COUNT (oraș DISTINCT) / COUNT (*) DIN sakila.city_demo;
În medie, prefixul va fi la fel de bun, în cazul în care selectivitatea sa este de aproximativ 0.031. Puteți evalua un număr de diferite lungimi ale prefixele într-o singură cerere, care este utilă pentru tabele foarte mari. Iată cum să găsiți selectivitatea pentru mai multe lungimi de prefix în aceeași interogare:
mysql> SELECT COUNT (STÂNGA DISTINCT (oraș, 3)) / COUNT (*) AS sel3,
-> COUNT (DISTINCT STÂNGA (oraș, 4)) / COUNT (*) AS sel4,
-> COUNT (DISTINCT STÂNGA (oraș, 5)) / COUNT (*) AS sel5,
-> COUNT (DISTINCT STÂNGA (oraș, 6)) / COUNT (*) AS sel6,
-> COUNT (stânga DISTINCT (oraș, 7)) / COUNT (*) AS sel7
-> DE LA sakila.city_demo;
| sel3 | sel4 | sel5 | sel6 | sel7
| 0.0239 | 0,0293 | 0,0305 | 0.0309 | 0.0310
Cererea indică faptul că o lungime consistentă creștere prefixului dă o ușoară îmbunătățire a selectivității până la șapte caractere.
Nu este suficient să acorde o atenție numai selectivitatea ridicată. Ar trebui să gândească, de asemenea, despre selectivitatea, în cel mai rău caz. Bazat pe o selectivitate medie pe care se poate ajunge la concluzia că prefixul în patru sau cinci caractere suficient de mult timp, dar în cazul în care datele sunt distribuite foarte inegal, te poate duce într-o capcană. Privind la numărul de apariții ale celor mai comune prefixele patru litere de nume de orașe, veți vedea în mod clar inegale:
mysql> SELECT COUNT (*) AS cnt, STÂNGA (oraș, 4) AS pref -> din grupul sakila.city_demo DE COMANDA PRIN pref cnt DESC LIMIT 5;
| 205 | san | 200 | Sant | 135 | Sout | 104 | chan | 91 | Toul + ----- + ------ +
Cu o lungime de patru caractere, cele mai frecvente prefixele sunt mult mai frecvente decât cea mai mare valoare completă comune. Aceasta este, selectivitatea acestor valori sunt mai mici decât selectivitatea medie. Dacă aveți un set mai realist de date decât ca eșantion aleatoriu generat, este probabil ca acest efect poate fi mult mai pronunțată. De exemplu, construirea unui prefix de patru cifre al indicelui numelor reale ale orașelor din lume, va da o selectivitate foarte mică pentru orașe care încep cu «San» și «noi», care este foarte mult.
Acum, găsirea unui prefix potrivit pentru datele noastre de testare, de a crea un index pe un prefix al unei coloane:
mysql> ALTER TABLE sakila.city_demo ADD KEY (oraș (7));
Indicii Prefixul poate fi o modalitate buna de a reduce dimensiunea și de a îmbunătăți performanța indicelui, dar au și dezavantaje
ki: MySQL nu poate folosi codurile prefix pentru orice întrebări cu ORDER BY clauza GROUP BY și, sau ca un indice de acoperire.