Is de volgorde van velden van de index met meerdere kolommen in MySQL van belang

Ik weet hoe belangrijk indexen zijn en hoe de volgorde van samenvoegingen de prestaties kan veranderen. Ik heb veel gelezen over indexen met meerdere kolommen en heb het antwoord op mijn vraag niet gevonden.

Ik ben benieuwd of ik een index met meerdere kolommen maak, of de volgorde waarin ze zijn gespecificeerd er toe doet. Mijn gok is dat dat niet het geval zou zijn, en dat de motor ze als een groep zou behandelen, waarbij bestellen er niet toe doet. Maar ik wil het verifiëren.

Bijvoorbeeld van de website van mysql (http://dev.mysql. com/doc/refman/5.0/en/multiple-column-indexes.html)

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

Zou er enig voordeel zijn in gevallen waarin het volgende beter zou zijn, of gelijkwaardig?

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (first_name,last_name)
);

Specifiek:

INDEX name (last_name,first_name)

vs

INDEX name (first_name,last_name)

Antwoord 1, autoriteit 100%

Bij het bespreken van indexen met meerdere kolommen, gebruik ik een analogie met een telefoonboek. Een telefoonboek is in feite een index op achternaam, dan voornaam. Dus de sorteervolgorde wordt bepaald door welke “kolom” eerst is. Zoekopdrachten vallen in een paar categorieën:

1. Als je mensen opzoekt wiens achternaam Smith is, kun je ze gemakkelijk vinden omdat het boek op achternaam is gesorteerd.

2. Als je mensen opzoekt wiens voornaam John is, helpt het telefoonboek niet omdat de Johns door het hele boek verspreid zijn. Je moet het hele telefoonboek scannen om ze allemaal te vinden.

3. Als je mensen opzoekt met een specifieke achternaam Smith en een specifieke voornaam John, helpt het boek omdat je de Smiths bij elkaar gesorteerd vindt, en binnen die groep Smiths worden de Johns ook in gesorteerde volgorde gevonden .

Als u een telefoonboek had gesorteerd op voornaam en vervolgens op achternaam, zou het sorteren van het boek u helpen in de bovenstaande gevallen #2 en #3, maar niet in geval #1.

Dat verklaart gevallen voor het opzoeken van exacte waarden, maar wat als u zoekt op waardenbereiken? Stel dat u alle mensen wilt vinden wiens voornaam John is en wiens achternaam begint met ‘S’ (Smith, Saunders, Staunton, Sherman, enz.). De Johns zijn gesorteerd onder ‘J’ binnen elke achternaam, maar als je alle Johns wilt voor alle achternamen die beginnen met ‘S’, worden de Johns niet gegroepeerd. Ze zijn weer verspreid, dus je moet alle namen doorzoeken waarvan de achternaam begint met ‘S’. Terwijl als het telefoonboek op voornaam en vervolgens op achternaam zou zijn gerangschikt, je alle Johns bij elkaar zou vinden, en binnen de Johns zouden alle ‘S’-achternamen bij elkaar worden gegroepeerd.

Dus de volgorde van kolommen in een index met meerdere kolommen is zeker van belang. Voor één type query kan een bepaalde kolomvolgorde voor de index nodig zijn. Als je verschillende soorten zoekopdrachten hebt, heb je misschien meerdere indexen nodig om ze te helpen, met kolommen in verschillende volgorde.

Je kunt mijn presentatie lezen Hoe indexen te ontwerpen, echtvoor meer informatie.

Antwoord 2, autoriteit 17%

De twee indexen zijn verschillend. Dit geldt in MySQL en in andere databases. MySQL legt de verschillende aspecten goed uit in de documentatie .

Beschouw de twee indexen:

create index idx_lf on name(last_name, first_name);
create index idx_fl on name(first_name, last_name);

Beide zouden even goed moeten werken op:

where last_name = XXX and first_name = YYY

idx_lf is optimaal voor de volgende omstandigheden:

where last_name = XXX
where last_name like 'X%'
where last_name = XXX and first_name like 'Y%'
where last_name = XXX order by first_name

idx_fl is optimaal voor het volgende:

where first_name = YYY
where first_name like 'Y%'
where first_name = YYY and last_name like 'X%'
where first_name = XXX order by last_name

Voor veel van deze gevallen kunnen beide indexen mogelijkworden gebruikt, maar één is optimaal. Overweeg bijvoorbeeld idx_lf met de vraag:

where first_name = XXX order by last_name

MySQL kan de hele tabel lezen met idx_lf en vervolgens filteren na de order by. Ik denk niet dat dit in de praktijk een optimalisatie-optie is (voor MySQL), maar dat kan in andere databases gebeuren.

Antwoord 3, autoriteit 8%

De algemene regel is dat u in een index met meerdere kolommende meest selectieve — dat wil zeggen degene die u de minste resultaten geeft — eerst wilt plaatsen. Dus als u een index met meerdere kolommen maakt voor een tabel met een status-kolom van bijvoorbeeld 10 mogelijke waarden, en ook een dateAdded-kolom, en u schrijft doorgaans query’s leuk vinden

SELECT * FROM myTable WHERE status='active' and dateAdded='2010-10-01'

…dan zou je eerst dateAddedwillen hebben, omdat dat de scan zou beperken tot slechts een paar rijen in plaats van 10% (of welk deel dan ook ‘actief’) van je rijen.

Dit vergt behoorlijk wat denkwerk en afstemming; je zou het boek van Lahdenmaki en Leach eens moeten lezen.