Ik zit met het volgende en ik weet niet wat ik het beste kan doen.
Beschouw de volgende tabel (die groot wordt):
id PK | giver_id FK | ontvanger_id FK | datum
Ik gebruik InnoDB en voor zover ik het begrijp, creëert het automatisch indexen voor de twee refererende sleutelkolommen. Ik zal echter ook veel zoekopdrachten uitvoeren waarbij ik een bepaalde combinatie moet vinden van:
SELECT...WHERE giver_id = x AND recipient_id = t.
Elke dergelijke combinatie is uniek in de tabel.
Heeft het enig voordeel om een index met twee kolommen toe te voegen aan deze kolommen, of zouden de twee individuele indexen in theorie voldoende / hetzelfde zijn?
Antwoord 1, autoriteit 100%
Als u twee indexen met één kolom heeft, wordt er in uw voorbeeld slechts één van gebruikt.
Als u een index met twee kolommen heeft, is de zoekopdracht mogelijk sneller (u moet meten). Een index met twee kolommen kan ook worden gebruikt als een index met één kolom, maar alleen voor de kolom die als eerste wordt vermeld.
Soms kan het handig zijn om een index op (A,B) en een andere index op (B) te hebben. Dit maakt zoekopdrachten met een of beide kolommen snel, maar verbruikt natuurlijk ook meer schijfruimte.
Bij het kiezen van de indexen moet u ook rekening houden met het effect op het invoegen, verwijderen en bijwerken. Meer indexen = langzamere updates.
Antwoord 2, autoriteit 26%
Een dekkende index zoals:
ALTER TABLE your_table ADD INDEX (giver_id, recipient_id);
…zou betekenen dat de index kan worden gebruikt als een zoekopdracht verwijst naar giver_id, of een combinatie van giver_iden recipient_id. Houd er rekening mee dat indexcriteria uiterst links zijn – een zoekopdracht die alleen verwijst naar recipient_idzou de dekkingsindex niet kunnen gebruiken in de verklaring die ik heb verstrekt.
Houd er rekening mee dat sommige oudere MySQL-versies slechts één index per SELECT kunnen gebruiken, dus een dekkende index zou de beste manier zijn om uw zoekopdrachten te optimaliseren.
Antwoord 3, autoriteit 2%
Als een van de refererende-sleutelindexen al erg selectief is, moet de database-engine die gebruiken voor de door u opgegeven query. De meeste database-engines gebruiken een soort heuristiek om in die situatie de optimale index te kunnen kiezen. Als geen van beide indexen op zichzelf zeer selectief is, is het waarschijnlijk zinvol om de index toe te voegen die op beide sleutels is gebouwd, aangezien u zegt dat u dat type zoekopdracht veel zult gebruiken.
Een ander ding om te overwegen is of u het PK-veld in deze tabel kunt verwijderen en de primaire sleutelindex kunt definiëren op de giver_iden recipient_idvelden. U zei dat de combinatie uniek is, dus dat zou mogelijk kunnen werken (gezien een heleboel andere voorwaarden die alleen u kunt beantwoorden). Doorgaans denk ik echter dat de toegevoegde complexiteit niet de moeite waard is.
Antwoord 4, autoriteit 2%
Een ander ding om te overwegen is dat de prestatiekenmerken van beide benaderingen gebaseerd zullen zijn op de grootte en kardinaliteit van de dataset. Het kan zijn dat de 2-kolomsindex alleen maar beter presteert bij een bepaalde drempelwaarde voor de gegevenssetgrootte, of precies het tegenovergestelde. Niets kan prestatiestatistieken voor uw exacte scenario vervangen.