MySQL Indexi | Ghid Complet de Optimizare

📚 Introducere în Indexi MySQL

Un index în MySQL este o structură de date care îmbunătățește dramatic viteza operațiilor de căutare într-un tabel. Similar cu indexul unei cărți, permite MySQL să găsească rândurile fără a scana întregul tabel.

📖 Ce este un Index?

Un index este o structură de date separată care stochează valorile unei coloane împreună cu pointeri către rândurile corespunzătoare. MySQL folosește predominant structura B-Tree pentru indexi.

Comparație Performanță

❌ Fără Index 2.45s

Full Table Scan - Scanează 1,000,000 rânduri

✅ Cu Index 0.001s

Index Lookup - Citește doar 1 rând

Structura B-Tree

Structura B-Tree Index

↙ ↘

25 | 35

75 | 90

↓ ↓ ↓ ↓ ↓ ↓

10, 20

27, 30

40, 45

60, 70

80, 85

95, 99

Pentru a găsi valoarea 27: Root (50) → Stânga (25|35) → Mijloc → Leaf (27)
Doar 3 operații în loc de scanarea tuturor rândurilor!

Când să folosești indexi?

Coloane în WHERE - folosite frecvent pentru filtrare
Coloane în JOIN - foreign keys și coloane de legătură
Coloane în ORDER BY - pentru sortare rapidă
Coloane în GROUP BY - pentru agregări eficiente
Coloane cu selectivitate mare - valori unice sau aproape unice

⚠️ Trade-offs

Indexii încetinesc operațiile de scriere (INSERT, UPDATE, DELETE) și consumă spațiu pe disk. Nu indexa totul - analizează query-urile frecvente!

🗂️ Tipuri de Indexi

🔑

PRIMARY KEY

Index unic și clustered. Determină ordinea fizică a datelor pe disk.

Folosit pentru: ID-uri, chei naturale

⭐

UNIQUE INDEX

Garantează unicitatea valorilor. Permite un singur NULL.

Folosit pentru: email, username

📋

INDEX (Standard)

Index care permite duplicate. Cel mai comun pentru optimizare.

Folosit pentru: status, category_id

📝

FULLTEXT INDEX

Pentru căutări text în coloane TEXT/VARCHAR.

Folosit pentru: descrieri, articole

🗺️

SPATIAL INDEX

Pentru date geografice. Folosește R-Tree.

Folosit pentru: coordonate, locații

🔗

COMPOSITE INDEX

Index pe multiple coloane. Ordinea contează!

Folosit pentru: (user_id, created_at)

Comparație B-Tree vs Hash

Caracteristică	B-Tree (Default)	Hash
Egalitate (=)	✅ Da	✅ Da (mai rapid)
Range (<, >, BETWEEN)	✅ Da	❌ Nu
ORDER BY	✅ Da	❌ Nu
LIKE 'prefix%'	✅ Da	❌ Nu
Complexitate	O(log n)	O(1)

🔨 Crearea Indexilor

Sintaxă de Bază

SQL - Creare Indexi

-- Index simplu
CREATE INDEX idx_users_email 
ON users(email);

-- Index unic
CREATE UNIQUE INDEX idx_users_username 
ON users(username);

-- Index compus (ordinea contează!)
CREATE INDEX idx_orders_user_date 
ON orders(user_id, created_at);

-- Index cu prefix pentru text lung
CREATE INDEX idx_posts_title 
ON posts(title(50));

-- FULLTEXT index
CREATE FULLTEXT INDEX idx_posts_content 
ON posts(title, body);

În CREATE TABLE

SQL - Tabel cu Indexi

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    status ENUM('pending', 'paid', 'shipped'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (id),
    INDEX idx_user (user_id),
    INDEX idx_user_status_date (user_id, status, created_at)
) ENGINE=InnoDB;

Administrare

SQL - Administrare

-- Vizualizează indexii
SHOW INDEX FROM orders;

-- Șterge un index
DROP INDEX idx_users_email ON users;

-- Adaugă cu ALTER TABLE
ALTER TABLE users ADD INDEX idx_email (email);

🔍 Analiza cu EXPLAIN

EXPLAIN este instrumentul esențial pentru înțelegerea execuției query-urilor.

SQL - EXPLAIN

-- EXPLAIN simplu
EXPLAIN SELECT * FROM orders 
WHERE user_id = 123;

-- EXPLAIN ANALYZE (MySQL 8.0.18+)
EXPLAIN ANALYZE SELECT * FROM orders 
WHERE user_id = 123;

-- Format JSON
EXPLAIN FORMAT=JSON SELECT * FROM orders 
WHERE user_id = 123;

Interpretarea Output-ului

Coloană	Descriere	Ce să cauți
`type`	Tipul de acces	🟢 const, eq_ref, ref 🔴 ALL (full scan)
`key`	Indexul folosit	NULL = nu folosește index!
`rows`	Rânduri estimate	Mai puține = mai bine
`Extra`	Info suplimentare	🟢 Using index 🔴 Using filesort

🚀 Tipuri de Access (bun → rău)

const → eq_ref → ref → range → index → ALL (cel mai rău!)

🔗 Indexi Compuși

Indexii compuși sunt puternici, dar ordinea coloanelor este crucială.

📖 Regula "Leftmost Prefix"

Un index (A, B, C) poate fi folosit pentru:
✅ WHERE A = ?
✅ WHERE A = ? AND B = ?
✅ WHERE A = ? AND B = ? AND C = ?
❌ WHERE B = ? (nu poate sări peste A!)

SQL - Index Compus

-- Creăm un index compus
CREATE INDEX idx_user_status_date 
ON orders(user_id, status, created_at);

-- ✅ FOLOSEȘTE indexul complet
SELECT * FROM orders
WHERE user_id = 123
AND status = 'paid'
AND created_at > '2024-01-01';

-- ❌ NU folosește indexul (lipsește user_id)
SELECT * FROM orders
WHERE status = 'paid';

Ordinea Optimă

Reguli pentru Ordinea Coloanelor

Coloane cu egalitate (=) PRIMELE

Coloane pentru range (<, >) la FINAL

Selectivitate mare primele

📦 Covering Index

Un Covering Index conține toate coloanele necesare pentru un query, eliminând accesul la tabel.

Index + Table Lookup ~50ms

Caută în index, apoi accesează tabelul

Covering Index ~5ms

Toate datele sunt în index!

SQL - Covering Index

-- Query
SELECT user_id, status, created_at
FROM orders
WHERE user_id = 123;

-- Index care acoperă toate coloanele
CREATE INDEX idx_covering 
ON orders(user_id, status, created_at);

-- EXPLAIN arată "Using index" în Extra

💡 "Using index" în EXPLAIN

Când vezi Using index în Extra = covering index. Cea mai eficientă execuție!

⚡ Tehnici de Optimizare

Indexi pentru JOIN

SQL - JOIN Optimization

-- Query cu JOIN
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id;

-- Indexi necesari:
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_users_status ON users(status);

Indexi pentru ORDER BY

SQL - ORDER BY

-- ❌ Fără index potrivit - filesort (lent)
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC;

-- ✅ Cu index compus - fără filesort
CREATE INDEX idx_user_date 
ON orders(user_id, created_at);

Indexi Funcționali (MySQL 8.0+)

SQL - Functional Index

-- Index pe expresie
CREATE INDEX idx_year 
ON orders((YEAR(created_at)));

-- Acum folosește indexul:
SELECT * FROM orders
WHERE YEAR(created_at) = 2024;

-- Index pe LOWER
CREATE INDEX idx_email_lower 
ON users((LOWER(email)));

🚫 Anti-Patterns de Evitat

❌ Greșeli Comune

✗

Funcții pe coloane indexate
WHERE YEAR(created_at) = 2024 → NU folosește index!
✅ WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

✗

Conversie implicită de tip
WHERE varchar_col = 123 → pierde index
✅ WHERE varchar_col = '123'

✗

LIKE cu wildcard la început
WHERE name LIKE '%john%' → full scan
✅ FULLTEXT index sau LIKE 'john%'

✗

OR pe coloane diferite
WHERE user_id = 1 OR product_id = 5
✅ Folosește UNION

Refactorizare Query-uri

SQL - Refactorizare

-- ❌ WRONG: Funcție pe coloană
SELECT * FROM orders
WHERE DATE(created_at) = '2024-06-15';

-- ✅ CORRECT: Range query
SELECT * FROM orders
WHERE created_at >= '2024-06-15 00:00:00'
AND created_at < '2024-06-16 00:00:00';

-- ❌ WRONG: OR pe coloane diferite
SELECT * FROM orders
WHERE user_id = 123 OR product_id = 456;

-- ✅ CORRECT: UNION
SELECT * FROM orders WHERE user_id = 123
UNION
SELECT * FROM orders WHERE product_id = 456;

🛠️ Tools și Mentenanță

📊

MySQL Workbench

Visual EXPLAIN, Query Analyzer

mysql.com/products/workbench

🔍

pt-query-digest

Analizează slow query log

percona.com

📈

MySQLTuner

Recomandări de configurare

github.com/major/MySQLTuner-perl

Mentenanță Indexi

SQL - Mentenanță

-- Actualizează statisticile
ANALYZE TABLE orders;

-- Reconstruiește tabelul
OPTIMIZE TABLE orders;

-- Găsește indexii nefolosiți
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE count_star = 0
AND index_name IS NOT NULL;

Checklist Final

✅ Checklist Optimizare

✓

Analizează slow query log

✓

Folosește EXPLAIN pentru query-uri importante

✓

Creează indexi compuși pentru WHERE multiple

✓

Verifică ordinea coloanelor (leftmost prefix)

✓

Consideră covering indexes

✓

Indexează coloanele din JOIN

✓

Evită funcții pe coloane indexate

✓

Rulează ANALYZE TABLE periodic

✓

Șterge indexii nefolosiți

📚 Resurse