Indexes na InnoDB su ne B+trees: bishiyoyi masu daidaituwa inda kowace leaf ke zaune a zurfi ɗaya kuma leaves suna haɗe don range scans masu sauri. Bincike O(log n) ne — page reads kaɗan ko a kan manyan tebura.
Composite index yana tsara rows bisa , sannan , sannan . Kana iya amfani da shi kawai ga na columns: , , ko — amma kaɗai ko ba, saboda waɗannan columns ba a tsara su ba har sai an gyara .
(a, b, c)abca(a, b)(a, b, c)b(b, c)aCREATE INDEX idx_abc ON events (user_id, status, created_at);
-- USES the index (leftmost prefix, range on the last column)
SELECT * FROM events
WHERE user_id = 7 AND status = 'open' AND created_at > '2026-01-01';
-- Does NOT use it: skips the leftmost column
SELECT * FROM events WHERE status = 'open';
Tsara columns mafi zaɓi / equality-farko, range column na ƙarshe: da zarar ka buga range (>, BETWEEN), columns bayansa ba za a iya amfani da su ga nema ba.
Idan index ya ƙunshi kowace column da query ke karantawa, InnoDB yana amsawa daga index kaɗai — babu komawa clustered index. EXPLAIN yana nuna Using index.
CREATE INDEX idx_cover ON events (user_id, status, id);
EXPLAIN SELECT id FROM events WHERE user_id = 7 AND status = 'open';
-- Extra: Using index <- covering, no clustered-index lookup
Composite da covering indexes su ne yadda kake mayar da query mai sharuɗɗa da yawa mai jinkiri zuwa index seek ɗaya. Kuskuren tsarin column — ko fatan cewa query a kan column na biyu zai yi amfani da index — shine dalili mafi gama-gari da ya sa index da "ke wanzu" har yanzu ba a amfani da shi.
Ɗakin karatu na tambayoyin hira na IT tare da amsoshi cikakke — daga Junior zuwa Senior.
Ba da Gudummawa