Menene index, kuma ta yaya primary key ya bambanta da secondary index?

Question

IT Interview Practice · Accepted Answer

Index wata daban, tsararriyar tsarin bayanai ce (**B-tree** a InnoDB) da ke sa MySQL ta sami rows ba tare da bincika dukan tebur ba. Ba tare da shi ba, bincike yana karanta kowace row (**full table scan**); da shi kuwa, yana tafiya cikin bishiya a matakai kaɗan.

## Primary da secondary

A InnoDB, tebur da kansa **shi ne** index na primary key — *clustered index*. Ana adana rows a jiki bisa tsarin primary-key, don haka binciken primary key yana sauka kai tsaye a kan cikakkiyar row.

**Secondary index** wata B-tree ce daban da aka gina bisa wasu column(s). Leaf nodes ɗinta suna adana darajar da aka index tare da **primary key**, don haka amfani da secondary index yawanci yana kashe bincike na biyu ("komawa clustered index") don ɗauko sauran row.

```sql
CREATE TABLE users (
  id     INT PRIMARY KEY,          -- clustered: rows stored in id order
  email  VARCHAR(255),
  city   VARCHAR(80)
);

CREATE INDEX idx_city ON users (city);   -- secondary B-tree on city

-- Fast: walks idx_city, then primary key for the rest of each row
SELECT * FROM users WHERE city = 'Hanoi';

-- Slow: no index on email -> full table scan
SELECT * FROM users WHERE email = 'a@b.com';
```

## Cinikin daidaito

Indexes suna hanzarta karatu amma kowane `INSERT`/`UPDATE`/`DELETE` dole ne ya sabunta su, kuma suna cin disk. Yi index ga column ɗin da gaske kake tacewa, join, ko tsarawa a kai — ba kowace column ba.

## Me ya sa yake da muhimmanci

Indexes su ne lever mafi girma a kan saurin query: bambanci tsakanin karanta tree nodes 3 da bincika miliyan rows. Sanin cewa InnoDB yana clustering bisa primary key kuma yana bayyana dalilin da ya sa gajeriyar, monotonic key (kamar `AUTO_INCREMENT`) ke sa inserts da kowace secondary index su zama masu sauƙi.

Primary da secondary

Cinikin daidaito

Me ya sa yake da muhimmanci

Tambayoyi masu alaƙa