Database Indexing: Sababta Query-gaagu u Qaato 2 ilbiriqsi halkii uu ka noqon lahaa 20ms
🧠 Engineering Insights #1
- Database Indexing.
Marka application-kaagu bilaabo inuu yeesho kumanaan ama malaayiin records, waxa ugu horreeya ee isticmaaluhu dareemo waa gaabis. Boggii oo si tartiib ah u furmaya, search-ga oo dib u dhacaya, ama API-ga oo qaadanaya ilbiriqsiyo badan.
Inta badan dhibaatadu ma aha database-ka laftiisa. Dhibaatadu waa in database-ku uusan haysan hab degdeg ah oo uu ku helo xogta aad weydiisanayso.
Halkaas ayay ka bilaabataa Database Indexing.
Waa maxay Database Index?
Database Index waa qaab xogeed (data structure) oo database-ku isticmaalo si uu si degdeg ah ugu helo records-ka, halkii uu saf kasta mid mid uga baari lahaa.
Waxaad u malayn kartaa inuu la mid yahay index-ka buug.
Haddii buug uu leeyahay 1,000 bog oo aad rabto cutubka "Networking", ma bilaabaysid bogga 1 ilaa bogga 1000. Waxaad marka hore eegaysaa index-ka buugga, kadibna waxaad toos ugu boodaysaa bogga saxda ah.
Database index wuxuu qabtaa isla shaqadaas.
Maxaa dhacaya haddii Index uusan jirin?
Bal qiyaas inaad leedahay jadwalkan:
users
id
name
email
country
Waxaad qortay query:
SELECT * FROM users WHERE email = 'user@example.com';
Haddii database-ku uusan lahayn index ku yaal "email", wuxuu samaynayaa waxa loo yaqaan:
Full Table Scan
Taasi waxay ka dhigan tahay:
- Safka 1 ayuu eegayaa
- Safka 2 ayuu eegayaa
- Safka 3 ayuu eegayaa
- ...
- Ilaa uu ka helo record-ka
Haddii ay jiraan 5 milyan oo users, waxaa suuragal ah inuu akhriyo malaayiin saf kahor inta uusan helin hal record.
Taasi waa sababta query-ga qaar ay u gaabiyaan.
Sidee ayuu Index-ku u shaqeeyaa?
Markaad samayso index:
CREATE INDEX idx_users_email ON users(email);
Database-ku wuxuu dhisaa qaab gaar ah oo u sahlaya inuu si degdeg ah u helo email kasta.
Marka query-ga dambe yimaado:
SELECT * FROM users WHERE email='user@example.com';
Uma baahna inuu akhriyo dhammaan table-ka.
Wuxuu si toos ah ugu boodaa meesha xogtu taallo.
Sababta Performance-ku u kordho
Aan tusaale qaadanno.
Database:
10,000,000 users
Search:
WHERE email = ?
Without Index
Scan ↓
1 2 3 4 5 ... 10,000,000
With Index
Index -> email -> Record
Waxaa dhici karta:
Without Index
2.4 seconds
With Index
15 milliseconds
Farqigu waa boqolaal jeer.
Index walba wuxuu leeyahay qiimo.
Markaad samaynayso:
INSERT
UPDATE
DELETE
Database-ku waa inuu sidoo kale cusboonaysiiyaa index-ka.
Sidaas darteed:
Readsway degdegaan.Writeswax yar ayay gaabiyaan.
Taasi waa trade-off-ka ugu weyn.
Goorma ayay tahay inaad samayso Index?
Badanaa columns-kan ayaa mudan.
✅ Primary Keys
id
✅ Foreign Keys
user_id
community_id
post_id
✅ Search Columns
email
username
slug
✅ Frequently Filtered Columns
status
country
category
✅ Sorting
created_at
published_at
Goormaan Index loo samayn?
Ha samayn index haddii:
- Table-ku aad u yar yahay.
- Column-ka si dhif ah loo isticmaalo.
- Column-ku leeyahay values aad isugu eg.
Tusaale:
gender
male
female
Index-kan faa'iido badan ma yeelan karo.
Composite Index
Mararka qaar query-gu wuxuu isticmaalaa laba ama saddex column.
WHERE community_id = 5 AND created_at DESC
Index fiican wuxuu noqon karaa:
CREATE INDEX idx_posts ON posts(community_id, created_at);
Halka laba index oo kala duwan mararka qaar aysan u shaqayn sida composite index.
Order-ka columns-ku waa muhiim.
(community_id, created_at)
wuxuu ka duwan yahay
(created_at, community_id)
Over Indexing
Developers badan waxay sameeyaan qaladkan.
Waxay index u sameeyaan column kasta.
Taasi waxay keentaa:
- Storage badan
- INSERT gaabis
- UPDATE gaabis
- DELETE gaabis
Index badan mar walba ma fiicna.
Index sax ah ayaa fiican.
Sidee lagu ogaadaa in Query-gu gaabis yahay?
Database-yada casriga ahi waxay leeyihiin qalab lagu arko execution plan.
PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE email='user@example.com';
Waxaad arki kartaa:
Seq Scan
ama
Index Scan
Haddii aad aragto Seq Scan table weyn, waxaa laga yaabaa inaad u baahan tahay index.
Tusaale Dhab ah
Bal qiyaas e-commerce platform.
Waxaad leedahay table:
products
Mar kasta oo user-ku furo category gaar ah, application-ku wuxuu soo bandhigayaa products-ka category-gaas, isagoo kuwa ugu dambeeyay marka hore tusaya.
SELECT *
FROM products
WHERE category_id = 25
ORDER BY created_at DESC;
Haddii index uusan jirin:
Database-ku wuxuu baari karaa malaayiin posts.
Laakiin haddii aad leedahay:
CREATE INDEX idx_posts_community_created ON posts(community_id, created_at DESC);
Community-ga wuxuu ku furmi karaa si aad uga dhaqso badan.
Khaladaadka ugu badan
❌ Index u samaynta column kasta.
❌ In aan la isticmaalin "EXPLAIN ANALYZE".
❌ In la illoobo in writes-ku qaali noqdaan.
❌ Composite index oo order khaldan leh.
❌ In la maleeyo in index walba uu hagaajinayo performance.
Best Practices
- Samee index-ka columns-ka ugu badan ee lagu search-gareeyo.
- Cabbir performance ka hor iyo kadib.
- Isticmaal "EXPLAIN ANALYZE".
- Ka saar index-yada aan la isticmaalin.
- Dib u eeg indexes marka application-ku koro.
Gunaanad
Database Indexing waa mid ka mid ah farsamooyinka ugu saameynta badan ee lagu hagaajiyo performance-ka applications.
Mararka qaar hal index oo si sax ah loo sameeyo ayaa ka qiimo badan in server-ka RAM ama CPU loo kordhiyo.
Software Engineering ma aha oo keliya in code-ku shaqeeyo; waa inuu si hufan, degdeg ah, oo la miisaami karo (scalable) u shaqeeyo. Indexing waa tallaabada ugu horreysa ee lagu gaaro yoolkaas.
💬 Dood
Ma la kulantay query si lama filaan ah u gaabis noqday? Maxaad samaysay si aad u xallisay—index ma ku filnaaday, mise xal kale ayaad adeegsatay?
