SQL
Տվյալների բազային տուր ճիշտ հարցեր՝ join-ներ, aggregate-ներ, window ֆունկցիաներ, ինդեքսներ և տրանզակցիաներ։
30 վայրկյանում
SQL-ը ռելյացիոն տվյալների բազաներին հարցեր տալու և դրանց տվյալները փոխելու լեզուն է։ Դու նկարագրում ես, թե ինչ ես ուզում (այս սյունակները, այս աղյուսակներից, այս կանոններին համապատասխանող), իսկ տվյալների բազան ինքն է որոշում, թե ինչպես դա ստանալ։ SQL-ը լավ իմանալը, այդ թվում ինդեքսներն ու տրանզակցիաները, հաճախ այն տարբերությունն է, որ էջը բեռնվում է 50 միլիվայրկյանում, թե timeout է տալիս։
Ինչու եմ օգտագործում
- SQL օգտագործում եմ ամեն օր. իմ կառուցած գրեթե յուրաքանչյուր պրոդուկտ իր հիմնական տվյալները պահում է MySQL-ում, MariaDB-ում կամ PostgreSQL-ում, իսկ ORM-ները միայն թաքցնում են SQL-ը, չեն փոխարինում այն։
- Հաշվետվություններն ու անալիտիկան գրում եմ անմիջապես SQL-ով (վաճառքներ, cohort-ներ, պահեստի գնահատում), որտեղ aggregate-ները և window ֆունկցիաները մեկ query-ով անում են այն, ինչը հավելվածի կոդում հարյուրավոր տողեր կպահանջեր։
- Կարդում եմ query plan-ները, որովհետև վեբ հավելվածների արտադրողականության խնդիրները ամենից հաճախ տվյալների բազայի խնդիրներ են՝ բացակայող ինդեքս կամ N+1 ցիկլ։
- Տրանզակցիաների վրա հույս եմ դնում, որպեսզի գումարը, պահեստը և loyalty հաշվեկշիռները մնան ճիշտ, երբ շատ հարցումներ դիպչում են նույն տողերին։
Ինչպես է աշխատում
01Հիմքը. SELECT, WHERE, ORDER BY և տրամաբանական հերթականությունը
Ռելյացիոն տվյալների բազան տվյալները պահում է աղյուսակներում. տողերը գրառումներ են, սյունակները՝ դաշտեր, իսկ primary key-ն եզակիորեն նույնականացնում է յուրաքանչյուր տող։ SELECT query-ն կարդում է տվյալներ։ Օգտակար է իմանալ, թե ինչ տրամաբանական հերթականությամբ են գնահատվում մասերը, որովհետև դա բացատրում է շատ սխալներ. նախ FROM և JOIN (որ տողերն են գոյություն ունենում), հետո WHERE (տողերի ֆիլտրում), GROUP BY (խմբերի ձևավորում), HAVING (խմբերի ֆիլտրում), SELECT (սյունակների հաշվարկ), ORDER BY (սորտավորում) և վերջում LIMIT։ Ահա թե ինչու չես կարող SELECT-ի alias-ը օգտագործել WHERE-ի ներսում։ NULL նշանակում է «անհայտ», ոչ թե զրո կամ դատարկ. համեմատիր IS NULL-ով, որովհետև NULL = NULL-ը true չէ։ Օգտատիրոջ մուտքը միշտ փոխանցիր որպես պարամետրեր (placeholder-ներ)՝ տողեր միացնելու փոխարեն, ինչը կանխում է SQL injection-ը։
-- Ten most recent paid orders above 50.00
SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'paid'
AND total > 50.00
AND cancelled_at IS NULL
ORDER BY created_at DESC
LIMIT 10;
-- From application code, always use parameters:
-- SELECT * FROM users WHERE email = ? -- (MySQL)
-- SELECT * FROM users WHERE email = $1 -- (PostgreSQL)
02JOIN-ներ, GROUP BY և subquery-ներ
Տվյալները բաժանված են աղյուսակների միջև՝ կրկնությունից խուսափելու համար, և JOIN-ը դրանք նորից միավորում է։ INNER JOIN-ը պահում է միայն այն տողերը, որոնք համընկնում են երկու կողմում. LEFT JOIN-ը պահում է ձախ աղյուսակի բոլոր տողերը և բացակայող համընկնումները լրացնում NULL-ով, և հենց այդպես ես գտնում «առանց պատվերների հաճախորդներին»։ GROUP BY-ը տողերը խմբավորում է, որպեսզի aggregate ֆունկցիաները (COUNT, SUM, AVG, MIN, MAX) աշխատեն յուրաքանչյուր խմբի համար. ընտրված յուրաքանչյուր սյունակ պետք է կամ խմբավորված լինի, կամ aggregate-ված։ HAVING-ը ֆիլտրում է խմբավորումից հետո, WHERE-ը՝ առաջ։ Subquery-ն query է մեկ այլ query-ի ներսում. EXISTS-ը պարզ միջոց է հարցնելու «կա՞ գոնե մեկ կապված տող»։ Common table expression-ները (WITH name AS (...)) subquery-ներին տալիս են ընթեռնելի անուններ և երկար հաշվետվությունների review-ն շատ ավելի հեշտ են դարձնում։
-- Revenue per customer this year, only customers above 1,000
WITH paid AS (
SELECT customer_id, total
FROM orders
WHERE status = 'paid' AND created_at >= '2026-01-01'
)
SELECT c.id, c.name, COUNT(*) AS orders, SUM(p.total) AS revenue
FROM customers c
JOIN paid p ON p.customer_id = c.id
GROUP BY c.id, c.name
HAVING SUM(p.total) > 1000
ORDER BY revenue DESC;
-- Customers who never ordered
SELECT c.id, c.email FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
03Window ֆունկցիաներ
Window ֆունկցիաները հաշվարկում են կապված տողերի բազմության վրա՝ առանց դրանք միավորելու, ի տարբերություն GROUP BY-ի։ Պահպանում ես պատվերի յուրաքանչյուր տող, և դրա կողքին կարող ես ցույց տալ հաճախորդի կուտակային գումարը, պատվերի rank-ը կամ նախորդ պատվերի ամսաթիվը։ OVER-ը սահմանում է window-ը. PARTITION BY-ը տողերը բաժանում է խմբերի (ըստ հաճախորդի), ORDER BY-ը սահմանում է հերթականությունը յուրաքանչյուր խմբի ներսում։ Տարածված ֆունկցիաներն են ROW_NUMBER-ը (եզակի հաջորդականություն, իդեալական «յուրաքանչյուր խմբի վերջին տողի» համար), RANK-ը և DENSE_RANK-ը, LAG-ը և LEAD-ը (նախորդ կամ հաջորդ տող) և կուտակային SUM-ը կամ AVG-ը։ Դրանք աջակցվում են PostgreSQL-ում, MySQL 8+-ում, MariaDB 10.2+-ում և SQLite-ում և փոխարինում են հավելվածի կոդում շատ դանդաղ self-join-ների և ցիկլերի։
-- Each customer's latest order, plus days since their previous one
SELECT customer_id, id, total, created_at, days_since_previous
FROM (
SELECT
o.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn,
created_at - LAG(created_at) OVER (
PARTITION BY customer_id ORDER BY created_at
) AS days_since_previous
FROM orders o
WHERE status = 'paid'
) ranked
WHERE rn = 1;
-- (date arithmetic shown in PostgreSQL syntax)
04Ինդեքսներ և ինչպես է աշխատում B-tree-ն
Առանց ինդեքսի տվյալների բազան պետք է կարդա յուրաքանչյուր տող՝ համընկնումներ գտնելու համար (full table scan), ինչպես առանց բովանդակության էջի գրքում որոնելը։ B-tree ինդեքսը բանալիների արժեքների սորտավորված, հավասարակշռված ծառ է՝ տողերի ցուցիչներով։ Քանի որ այն սորտավորված է և ծանծաղ (սովորաբար երեք-չորս մակարդակ նույնիսկ միլիոնավոր տողերի համար), տվյալների բազան կարող է մի քանի էջի ընթերցմամբ թռչել արժեքին կամ միջակայքի սկզբին և հետո շարժվել առաջ։ Դա օգնում է նաև ORDER BY-ին և LIMIT-ին։ (customer_id, created_at) composite ինդեքսը սորտավորված է նախ առաջին սյունակով, հետո երկրորդով, ուստի այն սպասարկում է query-ներ, որոնք ֆիլտրում են customer_id-ով կամ customer_id-ով և created_at-ով, բայց ոչ միայն created_at-ով. սա leftmost-prefix կանոնն է։ Հավասարության սյունակները դիր առաջ, իսկ միջակայքի կամ սորտավորման սյունակները՝ վերջում։ Ինդեքսներն անվճար չեն. յուրաքանչյուր INSERT և UPDATE պետք է դրանք թարմացնի, ուստի ինդեքսավորիր քո իրական query-ների համար, ոչ թե ամեն սյունակ։
-- Serves: WHERE customer_id = ? ORDER BY created_at DESC LIMIT 20
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);
-- Unique index also enforces a business rule
CREATE UNIQUE INDEX idx_users_email ON users (email);
-- Not helped by the composite index above (no leftmost column):
-- SELECT * FROM orders WHERE created_at > '2026-10-01';
-- Wrapping the column in a function also prevents index use:
-- WHERE DATE(created_at) = '2026-11-05' -- slow
-- WHERE created_at >= '2026-11-05'
-- AND created_at < '2026-11-06' -- uses the index
05EXPLAIN-ի ընթերցում և N+1 query-ների ուղղում
EXPLAIN-ը ցույց է տալիս տվյալների բազայի ընտրած plan-ը. որ աղյուսակներն է կարդում, ինչ հերթականությամբ և արդյոք օգտագործում է ինդեքս, թե scan է անում ամեն ինչ։ EXPLAIN ANALYZE-ը (PostgreSQL և MySQL 8.0.18+) իրականում աշխատեցնում է query-ն և ցույց տալիս իրական ժամանակներն ու տողերի քանակը։ Փնտրիր full scan-եր մեծ աղյուսակների վրա (PostgreSQL-ում Seq Scan, MySQL-ում type ALL), գնահատված և իրական տողերի մեծ տարբերություններ և թանկ սորտավորումներ։ Սակայն վեբ հավելվածներում արտադրողականության ամենատարածված բագը ոչ թե մեկ դանդաղ query-ն է, այլ N+1-ը. մեկ query-ով բեռնում ես 50 պատվեր, հետո ցիկլով յուրաքանչյուր պատվերի համար ևս մեկ query ես աշխատեցնում՝ հաճախորդին բերելու համար, և 2-ի փոխարեն ստացվում է 51 query։ ORM-ները դա պատահաբար գրելը հեշտ են դարձնում։ Ուղղումը eager loading-ն է (Laravel-ի with(), Prisma-ի include) կամ մեկ JOIN, իսկ development-ում կամ թեստերում query counter-ը բռնում է regression-ները։
EXPLAIN ANALYZE
SELECT id, total FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
-- Good: Index Scan using idx_orders_customer_created
-- Bad: Seq Scan on orders + Sort (missing or unusable index)
-- N+1 in an ORM (pseudo-code):
-- orders = Order.limit(50) -- 1 query
-- for o in orders: o.customer.name -- +50 queries
-- Fix: load customers in one go
SELECT o.id, o.total, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
ORDER BY o.created_at DESC LIMIT 50;
06Տրանզակցիաներ և isolation մակարդակներ
Տրանզակցիան հրամանները խմբավորում է մեկ «ամբողջը կամ ոչինչ» միավորի մեջ. կամ բոլոր փոփոխությունները պահպանվում են (COMMIT), կամ ոչ մեկը (ROLLBACK)։ Սա ACID-ի A-ն է (atomicity, consistency, isolation, durability)։ Isolation-ը որոշում է, թե զուգահեռ տրանզակցիաները ինչ կարող են տեսնել միմյանցից։ READ COMMITTED-ը (PostgreSQL-ի լռելյայնը) տեսնում է միայն commit արված տվյալները, բայց տողը նորից կարդալիս կարող է ավելի նոր արժեք վերադարձնել։ REPEATABLE READ-ը (MySQL InnoDB-ի լռելյայնը) տալիս է կայուն snapshot ամբողջ տրանզակցիայի համար։ SERIALIZABLE-ը վարվում է այնպես, կարծես տրանզակցիաները աշխատում են մեկ առ մեկ, բայց կարող է որոշները չեղարկել, ուստի հավելվածը պետք է կրկնի։ Counter-ների, պահեստի և հաշվեկշիռների համար խուսափիր read-modify-write race-ից. կամ թարմացրու ատոմար (SET stock = stock - 1 WHERE stock >= 1), կամ lock արա տողը SELECT ... FOR UPDATE-ով։ Տրանզակցիաները պահիր կարճ և երբեք մի սպասիր արտաքին HTTP կանչի՝ lock-եր պահելով։
BEGIN;
-- Lock the wallet row so concurrent requests wait their turn
SELECT balance FROM wallets WHERE customer_id = 42 FOR UPDATE;
-- Atomic, guarded update: fails safely if funds are insufficient
UPDATE wallets
SET balance = balance - 1500
WHERE customer_id = 42 AND balance >= 1500;
INSERT INTO wallet_transactions (customer_id, amount, reason)
VALUES (42, -1500, 'order_1001');
COMMIT; -- or ROLLBACK if the UPDATE affected 0 rows
Օգտագործեք, երբ
- Կապերով և կանոններով ցանկացած տվյալ՝ օգտատերեր, պատվերներ, ապրանքներ, վճարումներ, ամրագրումներ։
- Հաշվետվություններ և անալիտիկա, որտեղ aggregate-ները և window ֆունկցիաները հարցերին պատասխանում են մեկ query-ով։
- Ամեն ինչ, ինչին պետք է ճշգրտություն զուգահեռության պայմաններում՝ հաշվեկշիռներ, պահեստի մնացորդներ, unique constraint-ներ։
- Դանդաղ էջերի debug, որտեղ EXPLAIN-ը և query log-ը ցույց են տալիս, թե իրականում ինչ է անում ORM-ը։
Մի՛ օգտագործեք, երբ
- Շատ մեծ ծավալի, պարզ key-value որոնումներ կամ caching, որտեղ Redis-ն ավելի արագ է և պարզ։
- Full-text search սխալների հանդուրժողականությամբ և relevance ranking-ով մեծ մասշտաբով, որտեղ search engine-ը (Meilisearch, Algolia, Elasticsearch) ավելի հարմար է։
- Հսկայական անալիտիկ ծանրաբեռնվածություն միլիարդավոր event-ների վրա, որտեղ ճիշտ գործիքը columnar warehouse-ն է (ClickHouse, BigQuery)։
Այլընտրանքներ
- ORMs and query builders
Eloquent-ը, Prisma-ն, Drizzle-ը կամ Doctrine-ը SQL են գեներացնում ամենօրյա CRUD-ի համար. միևնույն է, սովորիր SQL՝ հասկանալու և tune անելու համար այն, ինչ նրանք արտադրում են։
- Document databases (MongoDB, Firestore)
Ճկուն JSON փաստաթղթեր և հեշտ scaling, բայց join-ներն ու բազմափաստաթղթային տրանզակցիաներն ավելի բարդ են։
- Columnar warehouses (ClickHouse, BigQuery)
Ստեղծված են հսկայական dataset-ների վրա անալիտիկայի համար. դրանք նույնպես SQL են «խոսում»։
Լավագույն պրակտիկաներ
- Միշտ օգտագործիր պարամետրավորված query-ներ. երբեք օգտատիրոջ մուտքը մի միացրու SQL-ին։
- Ինդեքսավորիր իրական query pattern-ների համար՝ հավասարության սյունակներն առաջ, միջակայքի կամ սորտավորման սյունակները վերջում, և ստուգիր EXPLAIN-ով։
- Ընտրիր միայն քեզ պետք սյունակները և մեծ ցուցակները paginate արա (խորը էջերի համար՝ keyset pagination)։
- Բազմաքայլ փոփոխությունները փաթաթիր տրանզակցիաներում, պահիր դրանք կարճ և հաշվեկշիռների ու պահեստի համար օգտագործիր ատոմար update-ներ կամ FOR UPDATE։
- Development-ում հետևիր մեկ հարցման query-ների քանակին՝ N+1 խնդիրները վաղ բռնելու համար։
- Գումարը պահիր որպես integer minor unit-ներով կամ DECIMAL, երբեք FLOAT։
- Կանոնները պարտադրիր նաև տվյալների բազայում՝ NOT NULL, foreign key-եր և unique constraint-ներ։
Տարածված սխալներ
- N+1 query-ներ՝ թաքնված ORM ցիկլի հետևում։
- Ինդեքսավորված սյունակները ֆունկցիաների մեջ փաթաթելը (DATE(created_at), LOWER(email)), ինչի պատճառով ինդեքսը չի կարող օգտագործվել։
- Խորը pagination-ի համար OFFSET 100000 օգտագործելը, որը միևնույն է կարդում և դեն է նետում բաց թողնված բոլոր տողերը։
- Հաշվեկշիռների կամ պահեստի read-then-write թարմացումներ առանց lock-ի կամ ատոմար UPDATE-ի, ինչը բեռի տակ հանգեցնում է կորած update-ների։
- = NULL-ով համեմատելը IS NULL-ի փոխարեն և մոռանալը, որ NULL-ը NOT IN-ին ստիպում է անսպասելի վարվել։
Խորը վերլուծություն
07MVCC. ինչպես են տվյալների բազաները թույլ տալիս կարդացողներին և գրողներին աշխատել միաժամանակ
Ե՛վ PostgreSQL-ը, և՛ MySQL InnoDB-ն օգտագործում են multi-version concurrency control (MVCC). կարդացողներին գրողներին սպասեցնելու փոխարեն տվյալների բազան պահում է տողի մի քանի տարբերակ և յուրաքանչյուր transaction-ին ցույց է տալիս այն տարբերակը, որը համապատասխանում է նրա snapshot-ին։ Երկու շարժիչները հին տարբերակները տարբեր կերպ են պահում, և դա որոշում է, թե ինչպես են դրանք վարվում production-ում։ PostgreSQL-ը յուրաքանչյուր UPDATE-ի ժամանակ գրում է տողի նոր տարբերակ, իսկ հինը թողնում է աղյուսակում որպես dead tuple. autovacuum-ը հետագայում ազատում է տեղը։ Եթե autovacuum-ը հետ է մնում, աղյուսակներն ու ինդեքսները «ուռչում» են, և query-ները դանդաղում։ InnoDB-ն տողերը թարմացնում է տեղում, իսկ հին տարբերակները պահում է undo log-ում. երկար աշխատող transaction-ը ստիպում է նրան պահել այդ snapshot-ից ի վեր բոլոր տարբերակները՝ մեծացնելով history list-ը։ Երկու շարժիչներում էլ դասը նույնն է. երկար transaction-ները թանկ են, նույնիսկ եթե միայն կարդում են, որովհետև դրանք «կապում» են հին տարբերակները։ Գտիր դրանք pg_stat_activity-ով կամ information_schema.innodb_trx-ով և ֆոնային job-երին տուր statement կամ idle-in-transaction timeout։
-- PostgreSQL: long or idle transactions holding old snapshots
SELECT pid, state, now() - xact_start AS age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY age DESC
LIMIT 10;
-- PostgreSQL: tables with many dead tuples (vacuum falling behind)
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
-- MySQL InnoDB: open transactions and their age
SELECT trx_id, trx_started, trx_rows_locked
FROM information_schema.innodb_trx ORDER BY trx_started;
08Առաջադեմ ինդեքսավորում՝ covering, partial, expression և skip scan
Covering ինդեքսը պարունակում է query-ին անհրաժեշտ բոլոր սյունակները, այնպես որ տվյալների բազան կարող է պատասխանել միայն ինդեքսից՝ առանց աղյուսակ այցելելու. PostgreSQL-ում դա index-only scan է, MySQL-ի EXPLAIN-ում՝ «Using index»։ PostgreSQL-ի INCLUDE կետը ավելացնում է լրացուցիչ payload սյունակներ՝ առանց դրանք sort key-ի մաս դարձնելու։ Partial ինդեքսը (PostgreSQL) ինդեքսավորում է միայն պայմանին համապատասխանող տողերը, օրինակ՝ չառաքված պատվերները, այնպես որ այն մնում է փոքր և արագ, երբ տողերի մեծ մասն անկարևոր է։ Expression ինդեքսը ինդեքսավորում է ֆունկցիայի արդյունքը, ինչը լուծում է «WHERE LOWER(email) = ?» խնդիրը. MySQL-ը functional key part-եր աջակցում է 8.0.13-ից։ Leftmost-prefix կանոնն էլ ունի ժամանակակից նրբություն. MySQL 8.0-ը և PostgreSQL 18-ը կարող են composite ինդեքսի վրա skip scan անել, երբ առաջատար սյունակն ունի քիչ տարբեր արժեքներ՝ այն դիտարկելով որպես մի քանի փոքր range scan։ Սա օգնում է, բայց պահեստային տարբերակ է, ոչ թե պատճառ ինդեքսները սխալ հերթականությամբ նախագծելու։
-- Covering index (PostgreSQL): answer the list query from the index
CREATE INDEX idx_orders_cust_created_inc
ON orders (customer_id, created_at DESC) INCLUDE (total, status);
-- Partial index: only the rows the dashboard actually reads
CREATE INDEX idx_orders_unshipped
ON orders (created_at) WHERE shipped_at IS NULL;
-- Expression index for case-insensitive lookups
CREATE UNIQUE INDEX idx_users_email_lower ON users (lower(email));
-- MySQL 8.0.13+: CREATE INDEX idx_email_lower ON users ((lower(email)));
-- Query must match the expression to use it
SELECT id FROM users WHERE lower(email) = lower($1);
09Keyset pagination և մեծ արդյունքների հավաքածուներ
OFFSET pagination-ը պարզ է թվում, բայց ամեն էջի հետ դանդաղում է, որովհետև տվյալների բազան պետք է ստեղծի և դեն նետի բոլոր բաց թողնված տողերը. 50 տողանոց ցուցակի 5,000-րդ էջը կարդում է 250,000 տող։ Այն նաև անկայուն է. եթե օգտատիրոջ էջ առ էջ անցնելու ընթացքում նոր տող է ավելացվում, տարրերը տեղաշարժվում են և հայտնվում երկու անգամ կամ ընդհանրապես չեն հայտնվում։ Keyset (cursor) pagination-ը հիշում է օգտատիրոջ տեսած վերջին տողը և խնդրում է դրանից հետո եկող տողերը՝ ինդեքսի միջոցով ուղիղ այնտեղ ցատկելով, այնպես որ յուրաքանչյուր էջ նույնքան արժե։ Սորտավորիր ըստ եզակի համադրության, սովորաբար timestamp գումարած primary key որպես tiebreaker, և համեմատիր row value-ով՝ (created_at, id) < (?, ?), որը աջակցում են և՛ PostgreSQL-ը, և՛ MySQL-ը։ Փոխզիջումն այն է, որ չես կարող ցատկել կամայական էջի համարի, ինչը սովորաբար նորմալ է feed-երի, API-ների և infinite scroll-ի համար։ Միլիոնավոր տողերի export-ների դեպքում stream արա keyset batch-երով՝ ամեն ինչ հիշողության մեջ բեռնելու փոխարեն, և աշխատեցրու դրանք read replica-ի վրա, եթե այն կա։
-- First page
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- Next page: pass the last row's (created_at, id) as the cursor
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
AND (created_at, id) < ('2026-10-31 18:20:00', 98123)
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- Supporting index
CREATE INDEX idx_orders_cust_keyset ON orders (customer_id, created_at, id);
10Lock-եր, deadlock-ներ և schema-ի փոփոխություններ առանց downtime-ի
Յուրաքանչյուր UPDATE վերցնում է row lock-եր մինչև transaction-ի ավարտը։ Երբ երկու transaction-ներից յուրաքանչյուրը պահում է lock, որն անհրաժեշտ է մյուսին, ոչ մեկը չի կարող շարունակել. սա deadlock է։ Տվյալների բազաները հայտնաբերում են դա և դրանցից մեկը չեղարկում են սխալով (PostgreSQL-ը ստուգում է deadlock_timeout-ից հետո, լռելյայն մեկ վայրկյան, InnoDB-ն հայտնաբերում է անմիջապես), ուստի հավելվածը պետք է պատրաստ լինի ամբողջ transaction-ը կրկնելու։ Deadlock-ների մեծ մասը կանխիր՝ տողերին դիպչելով հետևողական հերթականությամբ, օրինակ՝ account-ները միշտ lock անելով ըստ աճող id-ի, և transaction-ները կարճ պահելով։ REPEATABLE READ-ի դեպքում InnoDB-ն նաև gap lock-եր է վերցնում միջակայքերի վրա, որոնք կարող են անսպասելիորեն արգելափակել insert-ները։ Schema-ի փոփոխությունները lock-երի ևս մեկ աղբյուր են։ PostgreSQL-ում CREATE INDEX CONCURRENTLY-ն ինդեքս է կառուցում՝ առանց գրառումներն արգելափակելու, իսկ nullable սյունակի կամ հաստատուն default-ով սյունակի ավելացումն ակնթարթային է։ MySQL-ում շատ փոփոխություններ աջակցում են ALGORITHM=INSTANT կամ INPLACE՝ LOCK=NONE-ով։ Միգրացիաների համար միշտ սահմանիր lock_timeout, որպեսզի արգելափակված ALTER-ը արագ ձախողվի, այլ ոչ թե իր հետևում հերթի կանգնեցնի բոլոր query-ները։
-- PostgreSQL: safe migration settings
SET lock_timeout = '5s';
SET statement_timeout = '15min';
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);
-- (cannot run inside a transaction block; re-run if it fails: drop the INVALID index first)
-- Add a NOT NULL constraint without a long lock
ALTER TABLE orders ADD CONSTRAINT orders_currency_nn
CHECK (currency IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_currency_nn;
-- MySQL 8: request an online change, fail if impossible
ALTER TABLE orders ADD COLUMN source VARCHAR(20) NULL,
ALGORITHM=INSTANT;
Իրական իրավիճակներ
Երկու հաճախորդ գնեցին պահեստում մնացած վերջին ապրանքը
Խնդիր
Flash sale-ն ավարտվեց պահեստում եղած միավորներից ավելի շատ պատվերներով։ Կոդը կարդում էր պաշարը, ստուգում, որ այն զրոյից մեծ է, հետո գրում պաշարը հանած մեկ, և ծանրաբեռնվածության տակ մի քանի request կարդացին նույն արժեքը։
Ինչպես լուծել
Read-then-write-ը փոխարինիր մեկ ատոմար, պաշտպանված statement-ով պատվերի transaction-ի ներսում. UPDATE products SET stock = stock - 1 WHERE id = ? AND stock >= 1։ Եթե այն ազդում է զրո տողի վրա, ապրանքը սպառված է, ուստի rollback արա և տեղեկացրու հաճախորդին։ Մի քանի ապրանքով զամբյուղների համար ապրանքների տողերը lock արա SELECT ... FOR UPDATE-ով՝ ըստ աճող id-ի, deadlock-ներից խուսափելու համար, ստուգիր բոլոր քանակները, հետո թարմացրու։ Ավելացրու CHECK (stock >= 0) constraint, որպեսզի տվյալների բազան մերժի բացասական պաշարը, նույնիսկ եթե ապագա որևէ կոդային ճյուղ մոռանա պաշտպանությունը։ Deadlock-ի կամ serialization սխալների դեպքում մի քանի անգամ կրկնիր՝ կարճ backoff-ով։
Հաշվետվության query-ն ծնկի է բերում production տվյալների բազան
Խնդիր
Վաճառքների ամսական հաշվետվությունը join է անում orders, order items և products աղյուսակները երկու տարվա տվյալների վրա։ Երբ ֆինանսական թիմը այն աշխատեցնում է, տվյալների բազայի CPU-ն հասնում է 100%-ի, և checkout-ը բոլորի համար դանդաղում է։
Ինչպես լուծել
Նախ բաժանիր workload-ները. հաշվետվություններն աշխատեցրու read replica-ի վրա, որպեսզի analytics-ը չկարողանա «սովամահ անել» transactional query-ները։ Հետո օպտիմալացրու query-ն. աշխատեցրու EXPLAIN ANALYZE, ավելացրու composite ինդեքս, որը համապատասխանում է ամսաթվի ֆիլտրին և join key-երին, և ընտրիր միայն անհրաժեշտ սյունակները։ Փակված ժամանակահատվածների հաշվետվությունների համար նախապես հաշվարկիր. գիշերային job-ը յուրաքանչյուր ապրանքի օրական գումարները գրում է summary աղյուսակում, և հաշվետվությունը ագրեգացնում է մի քանի հազար summary տող՝ միլիոնավոր տարրերի փոխարեն։ Հաշվետվությունների օգտատիրոջ համար սահմանիր statement_timeout։ Եթե հաշվետվությունների կարիքները շարունակում են աճել, դրանք տեղափոխիր columnar պահոց, օրինակ՝ ClickHouse, որը սնվում է հիմնական տվյալների բազայից։
Ինդեքսի ավելացում 200 միլիոն տողանոց աղյուսակին՝ առանց downtime-ի
Խնդիր
Դանդաղ query-ին նոր ինդեքս է պետք շատ մեծ և ծանրաբեռնված orders աղյուսակի վրա։ Նախորդ փորձը սովորական CREATE INDEX-ով քսան րոպե արգելափակեց գրառումները և outage առաջացրեց։
Ինչպես լուծել
PostgreSQL-ում օգտագործիր CREATE INDEX CONCURRENTLY, որը կառուցում է ինդեքսը՝ թույլ տալով կարդալ և գրել. այն ավելի երկար է տևում և չի կարող աշխատել transaction-ի ներսում, ուստի աշխատեցրու այն միգրացիոն framework-ի transaction wrapper-ից դուրս։ Սահմանիր lock_timeout, որպեսզի սկզբի և վերջի կարճ lock-երը արագ ձախողվեն, այլ ոչ թե տրաֆիկը հերթի կանգնեցնեն։ Եթե այն ձախողվի, կմնա INVALID ինդեքս. ջնջիր այն և նորից փորձիր։ MySQL 8-ում օգտագործիր ALGORITHM=INPLACE, LOCK=NONE կամ շատ մեծ աղյուսակների համար online schema change գործիք, օրինակ՝ gh-ost։ Պլանավորիր ցածր տրաֆիկի ժամին, replica-ների վրա հետևիր replication lag-ին և հետո EXPLAIN-ով հաստատիր, որ planner-ն իրականում օգտագործում է նոր ինդեքսը։
Արագ ուղեցույց
- Իրական plan՝ ժամանակներով և buffer-ներով (PostgreSQL)
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;- Իրական plan՝ ծառի տեսքով (MySQL 8)
EXPLAIN ANALYZE SELECT ...; -- or EXPLAIN FORMAT=TREE- Ամենածանր query-ները ըստ ընդհանուր ժամանակի (PostgreSQL)
SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;- Ինդեքսի կառուցում՝ առանց գրառումներն արգելափակելու
CREATE INDEX CONCURRENTLY idx_name ON tbl (col);- Արագ ձախողվել՝ lock-երի հետևում հերթ կանգնելու փոխարեն
SET lock_timeout = '5s';- Keyset-ի հաջորդ էջ
WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50- Ատոմար պաշտպանված նվազեցում
UPDATE products SET stock = stock - 1 WHERE id = $1 AND stock >= 1;- Insert կամ update մեկ statement-ով (PostgreSQL)
INSERT ... ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price;- Insert կամ update մեկ statement-ով (MySQL)
INSERT ... AS new ON DUPLICATE KEY UPDATE price = new.price;- Միացնել MySQL slow query log-ը
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 0.5;
Իմ իրական նախագծերում
- Աշխատում եմ MySQL-ի և MariaDB-ի հետ Laravel հարթակներում և PostgreSQL-ի հետ (Prisma-ի միջոցով) multi-tenant POS SaaS-ում, որի schema-ն ունի 181 մոդել։
- Այդ SaaS-ում յուրաքանչյուր query կրում է բացահայտ tenant id, և Prisma extension-ը exception է նետում, եթե tenant scope-ը բացակայում է, այնպես որ տվյալները չեն կարող արտահոսել ընկերությունների միջև։
- Ռեստորանային պատվերների հարթակում կառուցեցի մոտ 30 հաշվետվության controller և մոտ 40 Excel export՝ վաճառքներից և ակցիաներից մինչև պատվերների ժամանակի heatmap։
- Հարթակները, որոնց վրա աշխատում եմ, հենվում են aggregate հաշվետվությունների վրա. ռեստորանային հարթակում ամեն օր թարմացվող հաճախորդների սեգմենտներ, իսկ կենդանիների ապրանքների հարթակում՝ reorder, պահեստի գնահատման և dead-stock հաշվետվություններ։
Հարցեր և պատասխաններ
Q1Ո՞րն է WHERE-ի և HAVING-ի տարբերությունը։
Դրանք ֆիլտրում են տարբեր փուլերում։ WHERE-ն աշխատում է խմբավորումից առաջ և ֆիլտրում է առանձին տողեր, ուստի չի կարող aggregate ֆունկցիաներ օգտագործել։ HAVING-ն աշխատում է GROUP BY-ից հետո և ֆիլտրում է ամբողջական խմբեր, ուստի կարող է օգտագործել COUNT, SUM և նմանատիպեր։ Օրինակ՝ հինգից ավելի վճարված պատվեր ունեցող հաճախորդներին գտնելու համար WHERE-ում ֆիլտրում ես status = 'paid', խմբավորում ես ըստ հաճախորդի, հետո պահում ես խմբերը HAVING COUNT(*) > 5-ով։ Տողային պայմանները WHERE-ում, ոչ թե HAVING-ում դնելը նաև ավելի արագ է, որովհետև տվյալների բազան դեն է նետում տողերը մինչև թանկ խմբավորումը։ Query-ի տրամաբանական հերթականությունն իմանալը (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY) պատասխանում է նման հարցերի մեծ մասին։
Q2Ինչպե՞ս է ինդեքսը արագացնում query-ն, և ի՞նչ արժե այն։
Տիպիկ ինդեքսը B-tree է՝ բանալիների արժեքների սորտավորված, հավասարակշռված ծառ, որը ցույց է տալիս տողերը։ Յուրաքանչյուր տող scan անելու փոխարեն տվյալների բազան իջնում է ծառի մի քանի մակարդակ՝ արժեքը կամ միջակայքի սկիզբը գտնելու համար, հետո կարդում է առաջ՝ ըստ հերթականության, ինչը օգնում է նաև ORDER BY-ին և LIMIT-ին։ Composite ինդեքսի համար գործում է leftmost-prefix կանոնը. (customer_id, created_at) ինդեքսն օգնում է customer_id-ով ֆիլտրերին, բայց ոչ միայն created_at-ով։ Գինը սկավառակի տարածքն ու ավելի դանդաղ գրառումներն են, որովհետև յուրաքանչյուր INSERT, UPDATE և DELETE պետք է թարմացնի յուրաքանչյուր ինդեքս։ Ուստի ինդեքսավորում եմ իրական query pattern-ների համար՝ EXPLAIN-ով ստուգված, ոչ թե ամեն սյունակ։
Q3Ի՞նչ է N+1 query խնդիրը և ինչպե՞ս ես այն ուղղում։
Այն առաջանում է, երբ մեկ query ես աշխատեցնում ցուցակը բեռնելու համար, հետո ևս մեկ query յուրաքանչյուր տարրի համար՝ կապված տվյալները բեռնելու համար. 1 query 50 պատվերի համար, գումարած 50 query դրանց հաճախորդների համար։ Յուրաքանչյուր query արագ է, ուստի development-ում ամեն ինչ նորմալ է թվում, բայց production-ում round-trip-ները գումարվում են, և տվյալների բազան ծանրաբեռնվում է։ ORM-ները դա հեշտացնում են, որովհետև order.customer-ին դիմելը լուռ query է գործարկում։ Ուղղումը eager loading-ն է, օրինակ Laravel-ի with('customer')-ը կամ Prisma-ի include-ը, որը բոլոր կապված տողերը բեռնում է մեկ կամ երկու query-ով, կամ բացահայտ JOIN։ Development-ում նաև միացնում եմ lazy-loading զգուշացումներ կամ query counter, որպեսզի regression-ները վաղ բռնվեն։
Q4Բացատրիր տրանզակցիաների isolation մակարդակները, և ե՞րբ կփոխես լռելյայնը։
Isolation-ը որոշում է, թե մեկ տրանզակցիան ինչ է տեսնում միաժամանակ աշխատող մյուսներից։ READ COMMITTED-ը՝ PostgreSQL-ի լռելյայնը, տեսնում է միայն commit արված տվյալները, բայց երկրորդ ընթերցումը կարող է ավելի նոր արժեք վերադարձնել։ REPEATABLE READ-ը՝ MySQL InnoDB-ի լռելյայնը, տալիս է հետևողական snapshot ամբողջ տրանզակցիայի համար։ SERIALIZABLE-ը երաշխավորում է, որ արդյունքը նույնն է, ինչ տրանզակցիաները մեկը մյուսի հետևից աշխատեցնելիս, որոշները չեղարկելու գնով, որոնք հավելվածը պետք է կրկնի։ Լռելյայնը գլոբալ հազվադեպ եմ փոխում։ Փոխարենը կոնկրետ կրիտիկական հատվածները, ինչպես wallet-ի հաշվեկշիռները կամ պահեստը, պաշտպանում եմ ատոմար պայմանական update-ներով կամ SELECT ... FOR UPDATE-ով, իսկ SERIALIZABLE-ը retry ցիկլով օգտագործում եմ միայն բարդ invariant-ների համար։
Q5Ե՞րբ կօգտագործես window ֆունկցիա GROUP BY-ի փոխարեն։
Երբ պետք է aggregate կամ ranking, բայց դեռ ուզում ես պահել յուրաքանչյուր տող։ GROUP BY-ը միավորում է տողերը. ստանում ես մեկ տող յուրաքանչյուր հաճախորդի համար՝ իր ընդհանուր գումարով։ Window ֆունկցիան ավելացնում է հաշվարկված սյունակ՝ պահպանելով մանրամասները. յուրաքանչյուր պատվեր՝ հաճախորդի կուտակային գումարի, իր rank-ի կամ նախորդ պատվերի ամսաթվի կողքին։ Տիպիկ կիրառություններն են «յուրաքանչյուր խմբի վերջին գրառումը» ROW_NUMBER-ով, top-N ըստ կատեգորիայի, կուտակային գումարներ, ժամանակաշրջանների համեմատություն LAG-ով և ընդհանուրի տոկոսը։ Մինչև window ֆունկցիաները սրանց համար պետք էին self-join-ներ կամ correlated subquery-ներ, որոնք ավելի դժվար էին կարդացվում և հաճախ ավելի դանդաղ էին։ Այժմ բոլոր հիմնական տվյալների բազաներն աջակցում են դրանք։
Q6Էջը դանդաղ է, և կասկածում ես տվյալների բազային։ Ինչպե՞ս ես հետազոտում։
Նախ չափիր՝ գուշակելու փոխարեն. միացրու query log-ը կամ օգտագործիր debug գործիք՝ տեսնելու հարցման աշխատեցրած յուրաքանչյուր query՝ ժամանակներով և ընդհանուր քանակով։ Եթե կան տասնյակ կամ հարյուրավոր նմանատիպ query-ներ, դա N+1 է, որն ուղղվում է eager loading-ով։ Եթե մեկ query-ն է գերիշխում, աշխատեցրու դրա վրա EXPLAIN ANALYZE և փնտրիր full table scan-եր մեծ աղյուսակների վրա, մեծ բազմությունների սորտավորումներ և տողերի վատ գնահատումներ։ Սովորական ուղղումներն են համապատասխան composite ինդեքսը, պայմանը վերագրելը, որպեսզի այն կարողանա օգտագործել ինդեքսը, ավելի քիչ սյունակներ ընտրելը կամ pagination-ը։ Հետո նորից չափիր և production-ում slow-query logging-ը միացված պահիր՝ հաջորդը բռնելու համար։
Սենիոր մակարդակի հարցեր
S1Ինչպե՞ս ես մասշտաբավորում ռելյացիոն տվյալների բազան, երբ մեկ սերվերն այլևս բավարար չէ։
Քայլերով՝ ամենաէժանից դեպի ամենաբարդը։ Նախ յուրաքանչյուր query դարձրու արդյունավետ՝ ինդեքսներ, ավելի քիչ round-trip-ներ, առանց N+1-ի, connection pooling PgBouncer-ով կամ ProxySQL-ով։ Հետո «տաք», հազվադեպ փոխվող կարդալու գործողությունները cache արա Redis-ում։ Հաջորդը՝ ավելացրու read replica-ներ և հաշվետվությունները ու կարդալու-ծանր endpoint-ները ուղարկիր այնտեղ՝ ընդունելով replication lag-ը. այն կարդալու գործողությունները, որոնք պետք է տեսնեն օգտատիրոջ սեփական գրառումը, մնում են primary-ի վրա։ Ուղղահայաց մասշտաբավորումը՝ ավելի մեծ մեքենա, թերագնահատված է և հաճախ տարիներ է շահում։ Մեծ աղյուսակների partitioning-ը ըստ ժամանակի ինդեքսները փոքր է պահում և արխիվացումը էժան դարձնում։ Միայն այս ամենից հետո եմ դիտարկում sharding-ը ըստ tenant-ի կամ հաճախորդի, որովհետև այն ահռելիորեն բարդացնում է transaction-ները, join-երը, միգրացիաները և շահագործումը։ Multi-tenant դիզայնը՝ յուրաքանչյուր տողում tenant id-ով, այդ տարբերակը բաց է պահում՝ առանց դրա համար վաղաժամ վճարելու։
S2UUID-ներ, թե auto-increment ամբողջ թվեր որպես primary key. որո՞նք են փոխզիջումները։
Auto-increment ամբողջ թվերը փոքր են, արագ են համեմատվում և insert են լինում հերթականությամբ, ինչը B-tree ինդեքսները կոմպակտ է պահում. բայց դրանք բացահայտում են գրառումների քանակը, URL-ներում գուշակելի են և կոորդինացիա են պահանջում, երբ մի քանի համակարգ է id գեներացնում։ Պատահական UUID-ները (v4) կարելի է ստեղծել ցանկացած տեղ, ներառյալ offline client-ներում, և դրանք չեն արտահոսեցնում բիզնեսի ծավալը, բայց 16 բայթ են և insert են լինում պատահական դիրքերում՝ առաջացնելով page split-եր և վատ cache locality InnoDB-ի նման clustered ինդեքսներում։ Ժամանակով դասավորված UUIDv7-ը լուծում է դրա մեծ մասը. գլոբալ եզակի է, գեներացվում է ցանկացած տեղ և մոտավորապես հաջորդական է, այնպես որ insert-ները մնում են ինդեքսի վերջում։ PostgreSQL 18-ը ունի uuidv7() ֆունկցիա։ Իմ սովորական ընտրությունը ներքին integer կամ UUIDv7 primary key-ն է՝ առանձին անթափանց public id-ով այնտեղ, որտեղ URL-ները չպետք է գուշակելի լինեն։
S3Ինչպե՞ս ես պահպանում տվյալների հետևողականությունը տվյալների բազայի և այլ համակարգերի միջև, օրինակ՝ queue-ի կամ վճարային պրովայդերի։
Արտաքին համակարգը չես կարող դնել տվյալների բազայի transaction-ի ներսում, ուստի նախագծում ես մասնակի ձախողման համար։ Event-ների համար օգտագործում եմ transactional outbox. բիզնես փոփոխությունը և outbox տողը գրվում են նույն transaction-ում, իսկ առանձին worker-ը outbox տողերը հրապարակում է queue-ում և նշում որպես ուղարկված։ Consumer-ները պետք է idempotent լինեն, որովհետև at-least-once առաքումը նշանակում է կրկնօրինակներ։ Վճարումների համար նախ գրանցում եմ մտադրությունը եզակի idempotency key-ով, պրովայդերին կանչում եմ այդ key-ով և համաձայնեցնում եմ webhook-ների և պարբերական job-ի միջոցով, որը ստուգում է սպասող գրառումները, այնպես որ timeout-ը երբեք գումարը անհայտ վիճակում չի թողնում։ Արտաքին id-ների վրա unique constraint-ները կրկնվող webhook-ները անվնաս են դարձնում։ Two-phase commit-ի նման բաշխված transaction-ներ գոյություն ունեն, բայց web համակարգերում դրանք հազվադեպ են արժանի իրենց շահագործման ծախսին։
Բառարան
- MVCC
- Multi-version concurrency control. տողի մի քանի տարբերակի պահպանում, որպեսզի կարդացողները տեսնեն հետևողական snapshot՝ առանց գրողներին արգելափակելու։
- Dead tuple
- PostgreSQL տողի հին տարբերակ, որն այլևս ոչ մի transaction-ի պետք չէ և սպասում է, որ vacuum-ը ազատի դրա տեղը։
- Covering index
- Ինդեքս, որը պարունակում է query-ին անհրաժեշտ բոլոր սյունակները, այնպես որ աղյուսակն ինքը երբեք չի կարդացվում։
- Partial index
- Ինդեքս միայն պայմանին համապատասխանող տողերի վրա, որը փոքր է մնում selective query-ների համար։
- Selectivity
- Թե որքան քիչ տողի է համապատասխանում պայմանը. բարձր selective պայմաններն ամենաշատն են շահում ինդեքսներից։
- Keyset pagination
- Էջավորում՝ տեսած վերջին տողը հիշելով և դրանից հետո եկող տողերը վերցնելով՝ OFFSET-ի փոխարեն։
- Deadlock
- Երկու transaction, որոնցից յուրաքանչյուրը սպասում է մյուսի պահած lock-ին. տվյալների բազան չեղարկում է մեկը՝ ցիկլը կոտրելու համար։
- Gap lock
- InnoDB lock ինդեքսային գրառումների միջև ընկած միջակայքի վրա, որը կանխում է insert-ները այդ միջակայքում։
- Read replica
- Տվյալների բազայի պատճեն, որը փոփոխություններ է ստանում primary-ից և սպասարկում է միայն կարդալու query-ներ։
- Planner statistics
- Արժեքների բաշխման մասին հավաքված տվյալներ, որոնք optimiser-ն օգտագործում է տողերի քանակը գնահատելու և plan ընտրելու համար։
Շարունակեք սովորել
Պրոդուկտի գաղափար ունե՞ք։Եկեք ստեղծենք այն։
Պատմեք ձեր գաղափարի, պրոդուկտի կամ թիմի մասին։ Սովորաբար պատասխանում եմ մեկ աշխատանքային օրվա ընթացքում։