மெதுவான SQL queries ஐ எப்படி optimize செய்வது?

Question

Accepted Answer

மெதுவான queries ஐ optimize செய்வது **execution plan ஐ பகுப்பாய்வு செய்வதன்** (`EXPLAIN`) மூலம் bottleneck ஐக் கண்டறிவதில் தொடங்குகிறது, பின்னர் இலக்கு வைக்கப்பட்ட திருத்தங்களைப் பயன்படுத்துகிறது — பொதுவாக **indexes ஐ சேர்த்தல்**, query ஐ மீண்டும் எழுதுதல், மற்றும் பொதுவான anti-patterns ஐத் தவிர்த்தல். மிகப்பெரிய வெற்றி பொதுவாக full table scans ஐ index lookups ஆக மாற்றுவதுதான்.

## படி 1: EXPLAIN உடன் பகுப்பாய்வு செய்தல்

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;
-- shows the EXECUTION PLAN: scan type (Seq Scan = full scan, Index Scan = using an index),
-- estimated/actual rows, cost, and timing → reveals WHERE the time goes
```

`EXPLAIN` (அல்லது `EXPLAIN ANALYZE`) டேட்டாபேஸ் query ஐ எப்படி இயக்குகிறது என்பதைக் காட்டுகிறது — முக்கியமாக, அது ஒரு **full table scan** (மெதுவானது) செய்கிறதா அல்லது ஒரு **index** (வேகமானது) ஐப் பயன்படுத்துகிறதா என்பதைக் காட்டுகிறது. எப்போதும் optimize செய்வதற்கு முன் profile செய்யுங்கள்.

## மிகப்பெரிய வெற்றி: indexes ஐ சேர்த்தல்

```sql
-- if EXPLAIN shows a Seq Scan on a filtered/joined column → add an index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- turns a full scan into a fast index lookup
```

மிகவும் பொதுவான மற்றும் தாக்கம் மிகுந்த திருத்தம்: `WHERE`, `JOIN`, மற்றும் `ORDER BY` இல் பயன்படுத்தப்படும் columns இல் **indexes ஐ சேர்த்தல்**. இது full table scans ஐ வேகமான lookups ஆக மாற்றுகிறது.

## சரிசெய்ய வேண்டிய பொதுவான anti-patterns

```sql
-- ❌ functions on a column prevent index use
WHERE UPPER(email) = 'X@Y.COM'        -- can't use an index on email
WHERE email = 'x@y.com'               -- ✅ can use the index

-- ❌ leading wildcard LIKE can't use an index
WHERE name LIKE '%smith'              -- ✅ 'smith%' (prefix) can use an index

-- ❌ SELECT * fetches unneeded columns
SELECT * → SELECT only_needed_columns  -- less data, may use a covering index

-- ❌ N+1 queries (querying in a loop) → use a single JOIN instead
```

## பிற optimization நுட்பங்கள்

```text
✓ Fix N+1 queries (one query per row) → use JOINs / batch into one query
✓ Add appropriate indexes (including COMPOSITE for multi-column filters)
✓ Reduce data scanned — filter early, select only needed columns, paginate
✓ Rewrite subqueries as JOINs where more efficient
✓ Use EXISTS instead of IN for existence checks on large subqueries
✓ Keep statistics updated (ANALYZE) so the planner makes good choices
✓ Consider denormalization / materialized views for expensive repeated queries
✓ Watch for missing indexes on FOREIGN KEYS (joins scan without them)
```

## இது ஏன் முக்கியம்

Query optimization டேட்டாபேஸ் செயல்திறனுக்கு முக்கியமான அறிவாகும் — மெதுவான queries மோசமான பயன்பாட்டு செயல்திறனுக்கு ஒரு முன்னணி காரணம், மற்றும் அவற்றைக் கண்டறிந்து சரிசெய்வது எப்படி என்பதைப் புரிந்துகொள்வது அளவில் டேட்டாபேஸ்களுடன் வேலை செய்யும் எவருக்கும் இன்றியமையாத senior-நிலை திறமையாகும்.

அடிப்படை ஒழுங்கு **`EXPLAIN` உடன் பகுப்பாய்வு செய்தல்** (execution plan ஐ ஆராய்ந்து உண்மையான bottleneck ஐக் கண்டறிதல் — முக்கியமாக query ஒரு மெதுவான full table scan செய்கிறதா அல்லது வேகமான index lookup செய்கிறதா என்பதைக் கண்டறிதல்) optimize செய்வதற்கு முன், ஊகிப்பதற்குப் பதிலாக — இந்த அளவீட்டு-உந்துதல் அணுகுமுறைதான் optimization ஐ பயனுள்ளதாக ஆக்குகிறது.

**ஒற்றை மிகப்பெரிய மற்றும் பொதுவான வெற்றி** filtered, joined, மற்றும் sorted columns இல் **indexes ஐ சேர்ப்பது** (full table scans ஐ வேகமான lookups ஆக மாற்றுதல் — பெரும்பாலும் query நேரத்தை அளவு வரிசைகளால் மேம்படுத்துதல்), index அறிவை optimization க்கு மையமாக ஆக்குகிறது.

**பொதுவான anti-patterns** ஐப் புரிந்துகொள்வது அதேபோல் முக்கியம்: columns இல் functions மற்றும் leading-wildcard `LIKE` index பயன்பாட்டைத் தடுக்கின்றன, `SELECT *` தேவையற்ற தரவைப் பெறுகிறது, மற்றும் குறிப்பாக **N+1 queries** (ஒற்றை JOIN க்குப் பதிலாக ஒரு loop இல் query செய்தல் — பயன்பாட்டுக் குறியீட்டில் அடிக்கடி நிகழும், கடுமையான செயல்திறன் கொலையாளி) — இவற்றை அடையாளம் கண்டு சரிசெய்வது மெதுவான queries இன் மிகவும் பொதுவான காரணங்களை நிவர்த்தி செய்கிறது.

பிற நுட்பங்கள் (composite indexes, scan செய்யப்படும் தரவைக் குறைத்தல், subqueries ஐ JOINs ஆக மீண்டும் எழுதுதல், statistics ஐ புதுப்பித்து வைத்தல், விலையுயர்ந்த queries க்கு materialized views) கருவித்தொகுப்பை முழுமைப்படுத்துகின்றன.

Query செயல்திறன் பயன்பாட்டு பதிலளிக்கும் தன்மை மற்றும் scalability ஐ நேரடியாகப் பாதிப்பதால், மற்றும் முறையான அணுகுமுறை (EXPLAIN உடன் பகுப்பாய்வு செய்து, பின்னர் சரிசெய்தல் — பொதுவாக indexes ஐ சேர்த்து N+1 போன்ற anti-patterns ஐ நீக்குதல்) டேட்டாபேஸ்களை சுமையின் கீழ் வேகமாக வைத்திருப்பதால், query optimization ஐ தேர்ச்சி பெறுவது செயல்திறன்மிக்க, scalable டேட்டாபேஸ்-ஆதரவு பயன்பாடுகளை உருவாக்க முடியும் developers ஐ வேறுபடுத்தும் முக்கியமான, அதிக-மதிப்பு senior அறிவாகும் — டேட்டாபேஸ் செயல்திறனுக்கான அடிக்கடி தொடர்புடைய, முக்கியமான திறமை மற்றும் பொதுவான நேர்காணல் தலைப்பு.