MySQL և MariaDB

01InnoDB. ինչպես է MySQL-ը պահում տվյալները

InnoDB-ն լռելյայն storage engine-ն է և միակը, որը պետք է օգտագործել հավելվածի տվյալների համար։ Այն յուրաքանչյուր աղյուսակ պահում է որպես clustered index. տողերը ֆիզիկապես դասավորված են ըստ primary key-ի B-tree-ի ներսում։ Secondary ինդեքսները պահում են ինդեքսավորված սյունակները գումարած primary key-ը և օգտագործում են այն ամբողջական տողը գտնելու համար։ Սրանից երկու հետևանք կա։ Կարճ, անընդհատ աճող primary key-ը (bigint) բոլոր ինդեքսները փոքր է պահում, իսկ insert-ները՝ էժան, մինչդեռ պատահական UUID-ները առաջացնում են page split-եր և ուռճացում։ Եվ secondary ինդեքսով որոնումն արժե երկու որոնում, եթե ինդեքսը չի ծածկում բոլոր անհրաժեշտ սյունակները։ InnoDB-ն նաև պահում է redo log՝ crash-ից վերականգնման համար, undo log՝ rollback-ների և consistent read-երի համար, և buffer pool, որը page-երը cache է անում հիշողության մեջ։

• innodb_buffer_pool_size. RAM-ի մոտ 50-ից 70%-ը՝ առանձնացված database սերվերի վրա։
• Յուրաքանչյուր աղյուսակի պետք է բացահայտ primary key։
• Ամենուր օգտագործեք utf8mb4. հին utf8-ը չի կարող պահել emoji-ներ և որոշ գրերի նշաններ։

02Ինդեքսներ և EXPLAIN

MySQL-ի ինդեքսները B-tree-եր են, և գործում է leftmost-prefix կանոնը. (status, created_at) ինդեքսն օգնում է WHERE status = 'paid' և WHERE status = 'paid' AND created_at > ... հարցումներին, բայց ոչ միայն created_at-ին։ Covering index-ը պարունակում է հարցմանը պետք եկող բոլոր սյունակները, ուստի MySQL-ը աղյուսակին ընդհանրապես չի դիպչում. EXPLAIN-ը ցույց է տալիս 'Using index'։ Սյունակների վրա ֆունկցիաները, ինչպես WHERE DATE(created_at) = ..., խանգարում են ինդեքսի օգտագործմանը. վերաշարադրեք դրանք որպես միջակայքեր։ Սկզբում wildcard-ը (LIKE '%term') չի կարող օգտագործել B-tree, ուստի օգտագործեք FULLTEXT կամ search engine։ Օգտագործեք EXPLAIN՝ plan-ը տեսնելու համար, և EXPLAIN ANALYZE (MySQL 8)՝ իրական ժամանակները տեսնելու համար։ Հետևեք type սյունակին. const-ը և ref-ը լավ են, ALL-ը նշանակում է full table scan։

1ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
2 
3EXPLAIN ANALYZE
4SELECT id, total_minor
5FROM orders
6WHERE status = 'paid'
7  AND created_at >= '2026-10-01'
8  AND created_at <  '2026-11-01';

03Transaction-ներ և locking

Transaction-ը խմբավորում է հրամանները, որպեսզի դրանք բոլորը հաջողվեն կամ բոլորը ձախողվեն (ACID՝ atomic, consistent, isolated, durable)։ InnoDB-ի լռելյայն isolation level-ը REPEATABLE READ-ն է. transaction-ի ներսում տեսնում եք համահունչ snapshot։ Սովորական SELECT-ները երբեք չեն արգելափակում գրողներին՝ շնորհիվ MVCC-ի (multi-version concurrency control)։ Երբ պետք է անվտանգ կարդալ տողը և հետո թարմացնել այն, օրինակ՝ պահեստի մնացորդը կամ wallet-ի հաշվեկշիռը, lock արեք այն SELECT ... FOR UPDATE-ով կամ թարմացրեք ատոմար՝ մեկ հրամանով և պայմանով։ Transaction-ները կարճ պահեք. երկարները պահում են lock-եր, արգելափակում ուրիշներին և մեծացնում undo log-ը։ Deadlock-ներ լինում են, երբ երկու transaction տողերը lock են անում հակառակ հերթականությամբ. InnoDB-ն հայտնաբերում է դրանք և մեկին rollback անում, ուստի ձեր կոդը պետք է կրկնի։

1START TRANSACTION;
2 
3SELECT stock FROM variants WHERE id = 42 FOR UPDATE;
4 
5UPDATE variants SET stock = stock - 2
6WHERE id = 42 AND stock >= 2;   -- 0 rows affected = out of stock
7 
8INSERT INTO order_items (order_id, variant_id, qty, unit_price_minor)
9VALUES (1001, 42, 2, 2490);
10 
11COMMIT;

04Օգտատերեր և արտոնություններ

MySQL-ի հաշիվները user գումարած host են. 'app'@'localhost'-ը և 'app'@'%'-ը տարբեր հաշիվներ են։ Հետևեք least privilege սկզբունքին։ Հավելվածի օգտատերը ստանում է միայն տվյալների իրավունքներ (SELECT, INSERT, UPDATE, DELETE) իր սեփական տվյալների բազայի վրա։ Առանձին migration օգտատերը ստանում է սխեմայի իրավունքներ (CREATE, ALTER, INDEX, DROP), իսկ backup-ները՝ իրենց սեփական read-only հաշիվը։ Երբեք թույլ մի տվեք հավելվածներին միանալ որպես root։ MySQL 8.4-ից սկսած հին mysql_native_password plugin-ը լռելյայն անջատված է. caching_sha2_password-ը լռելյայն է և աշխատում է ընթացիկ driver-ների հետ։ Սերվերը bind-address-ով կապեք 127.0.0.1-ին կամ private ցանցի հասցեին, և 3306 port-ը փակ պահեք firewall-ում։ Եթե միացումներն անցնում են ցանցով, օգտագործեք TLS։

1CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'use-a-long-random-secret';
2GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'localhost';
3 
4CREATE USER 'shop_migrate'@'localhost' IDENTIFIED BY 'another-long-secret';
5GRANT ALL PRIVILEGES ON shop.* TO 'shop_migrate'@'localhost';
6 
7SHOW GRANTS FOR 'shop_app'@'localhost';

05Slow query log

Slow query log-ը գրանցում է յուրաքանչյուր հրաման, որը long_query_time վայրկյանից ավելի է տևում։ Դա ամենաարագ միջոցն է գտնելու, թե իրականում ինչն է ցավեցնում production-ում, գուշակելու փոխարեն։ Միացրեք այն մոտ կես վայրկյանից մեկ վայրկյան շեմով, ըստ ցանկության staging պատճենի վրա գրանցեք նաև ինդեքս չօգտագործող հարցումները, և հետո ամփոփեք ֆայլը։ mysqldumpslow-ը գալիս է MySQL-ի հետ. Percona Toolkit-ի pt-query-digest-ը տալիս է ավելի լավ հաշվետվություն՝ խմբավորված ըստ query fingerprint-ի, որը ցույց է տալիս, թե որ հարցումներն են ամենաշատ ընդհանուր ժամանակը ծախսում։ Շտկեք առաջին մի քանիսը, սովորաբար ինդեքսով կամ վերաշարադրված հարցումով, հետո կրկնեք։ performance_schema-ն և sys schema-ն նույն պատկերը տալիս են live, օրինակ՝ sys.statements_with_runtimes_in_95th_percentile։

1# /etc/mysql/mysql.conf.d/mysqld.cnf
2[mysqld]
3slow_query_log      = ON
4slow_query_log_file = /var/log/mysql/slow.log
5long_query_time     = 0.5
6 
7# then summarise:
8# sudo pt-query-digest /var/log/mysql/slow.log | less

06Replication-ի հիմունքներ

Replication-ը փոփոխությունները պատճենում է source սերվերից մեկ կամ մի քանի replica-ների։ Source-ը յուրաքանչյուր փոփոխություն գրում է իր binary log-ում (binlog). յուրաքանչյուր replica քաշում է binlog-ը և նորից կատարում այն։ Օգտագործեք row-based ֆորմատը (լռելյայն) և GTID-ներ (global transaction ids), որոնք failover-ը և replica-ների վերաուղղումը շատ ավելի պարզ են դարձնում։ Replication-ը լռելյայն ասինխրոն է, ուստի replica-ն կարող է հետ մնալ. նրանից կարդացածը կարող է մի փոքր հնացած լինել, ինչը կարևոր է «կարդա քո իսկ գրածը» հոսքերի համար, օրինակ՝ checkout-ից անմիջապես հետո պատվերը ցույց տալիս։ Replica-ները օգտակար են կարդալու մասշտաբավորման, հաշվետվությունների և որպես warm standby։ Replication-ը backup չէ. DROP TABLE-ը կամ սխալ UPDATE-ը ակնթարթորեն replicate է լինում։ MySQL 8.4-ից սկսած տերմիններն են source և replica, SHOW REPLICA STATUS-ի նման հրամաններով։

07Backup-ներ, և MySQL ընդդեմ MariaDB

Backup-ները երկու տեսակի են։ Logical backup-ները, որոնք արվում են mysqldump-ով կամ MySQL Shell-ի dump utility-ներով, export են անում SQL կամ տվյալների ֆայլեր. դրանք portable են տարբերակների միջև և հեշտ է մասնակի վերականգնել, բայց դանդաղ են մեծ տվյալների բազաների համար։ Օգտագործեք --single-transaction՝ առանց lock-ի համահունչ InnoDB snapshot ստանալու համար։ Physical backup-ները պատճենում են հենց տվյալների ֆայլերը՝ MySQL-ի համար Percona XtraBackup-ով, MariaDB-ի համար՝ mariadb-backup-ով. մեծ բազաների դեպքում դրանք շատ ավելի արագ են ստեղծվում և վերականգնվում, և աջակցում են incremental backup-ներ։ Պահեք binlog-երը՝ point-in-time recovery հնարավոր դարձնելու համար, պատճենները պահեք սերվերից դուրս և պարբերաբար ստուգեք վերականգնումը։ MariaDB-ն MySQL-ից fork է արվել 2009-ին։ Ամենօրյա SQL-ի համար դրանք դեռ մոտ են, բայց տարամիտվել են. JSON-ը, նույնականացումը, replication-ը և optimiser-ի հնարավորությունները տարբերվում են, ուստի դրանց վերաբերվեք որպես առանձին պրոդուկտների և ստուգեք նախքան անցում կատարելը։

1# consistent logical backup, compressed, off to another location
2mysqldump --single-transaction --routines --triggers --events \
3  -u backup_user -p shop | gzip > shop-$(date +%F).sql.gz
4 
5# restore into a test database to prove the backup works
6gunzip < shop-2026-11-05.sql.gz | mysql -u root -p shop_restore_test

08Հիշողություն և durability՝ buffer pool, redo log և flush կարգավորումներ

MySQL-ի tuning-ի մեծ մասը հանգում է InnoDB-ի մի քանի կարգավորման։ Buffer pool-ը cache է անում տվյալների և ինդեքսների էջերը․ նվիրված բազայի սերվերի վրա սովորաբար նրան տրվում է RAM-ի 50-ից 75 տոկոսը, և նպատակն այն է, որ «տաք» աշխատանքային տվյալները տեղավորվեն, և ընթերցումները հազվադեպ դիպչեն սկավառակին։ innodb_dedicated_server = ON կարգավորումը buffer pool-ի և redo log-ի չափերը ավտոմատ որոշում է մեքենայի հիշողությունից, ինչը լավ default է այն host-ի վրա, որտեղ աշխատում է միայն MySQL-ը։ Redo log-ը գրանցում է փոփոխությունները մինչև տվյալների էջերի գրվելը․ MySQL 8.0.30-ից սկսած դրա չափը մեկ կարգավորում է՝ innodb_redo_log_capacity, և չափազանց փոքր log-ը գրելու բեռի տակ ստիպում է անընդհատ flush անել։ Durability-ն փոխզիջում է, որը դուք ընտրում եք գիտակցաբար։ innodb_flush_log_at_trx_commit = 1 և sync_binlog = 1 արժեքները flush են անում յուրաքանչյուր commit-ի ժամանակ և դիմանում են հոսանքի անջատմանը՝ առանց commit արված transaction-ների կորստի․ 2 և 0 արժեքներն ավելի արագ են, բայց crash-ի դեպքում կարող են կորցնել մոտ մեկ վայրկյանի commit-ներ։ Փողի հետ կապված ամեն ինչի համար պահեք 1 և 1։

1# /etc/mysql/mysql.conf.d/mysqld.cnf  (dedicated DB host, 16 GB RAM)
2[mysqld]
3innodb_buffer_pool_size        = 10G
4innodb_redo_log_capacity       = 4G
5innodb_flush_log_at_trx_commit = 1     # full durability
6sync_binlog                    = 1
7max_connections                = 300   # each connection also uses memory
8binlog_expire_logs_seconds     = 604800  # keep 7 days for point-in-time recovery
9 
10# check how often reads miss the buffer pool:
11# SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

09Online սխեմայի փոփոխություններ՝ INSTANT, INPLACE և metadata lock-եր

MySQL 8-ում ALTER TABLE-ն ունի երեք ալգորիթմ։ INSTANT-ը փոխում է միայն metadata-ն և ավարտվում է միլիվայրկյաններում՝ անկախ աղյուսակի չափից․ 8.0.29-ից սկսած այն կարող է ավելացնել կամ հեռացնել սյունակներ ցանկացած դիրքում, ինչպես նաև վերանվանել սյունակներ և փոխել default արժեքներ։ INPLACE-ը շատ գործողությունների համար վերակառուցում կամ փոփոխում է աղյուսակը՝ առանց ընթերցումներն ու գրառումները արգելափակելու, օրինակ՝ secondary ինդեքս ավելացնելիս LOCK=NONE-ով, բայց այն դեռ ժամանակ և I/O է պահանջում, իսկ replica-ներում փոփոխությունը վերարտադրվում է հետո՝ առաջացնելով lag։ COPY-ն վերակառուցում է ամբողջ աղյուսակը և արգելափակում գրառումները։ Ալգորիթմը նշեք բացահայտ․ այդ դեպքում MySQL-ը կմերժի, այլ ոչ թե լուռ կանցնի ավելի դանդաղ տարբերակի։ Թաքնված վտանգը metadata lock-ն է։ Ամեն ALTER սկզբում և վերջում կարճ ժամանակով բացառիկ metadata lock է պահանջում։ Եթե աղյուսակի վրա երկար transaction է բաց, ALTER-ը սպասում է, և այդ աղյուսակի ամեն նոր հարցում հերթ է կանգնում ALTER-ի հետևում․ կայքը կարծես սառած լինի։ Սահմանեք կարճ lock_wait_timeout և կրկնեք։ Հսկայական աղյուսակների կամ ռիսկային փոփոխությունների համար gh-ost-ը կամ pt-online-schema-change-ը ֆոնում պատճենում են աղյուսակը՝ սահմանափակված չափաբաժիններով։

1SET SESSION lock_wait_timeout = 5;   -- give up quickly instead of freezing traffic
2 
3ALTER TABLE orders ADD COLUMN gift_note VARCHAR(255) NULL, ALGORITHM=INSTANT;
4 
5ALTER TABLE orders ADD INDEX idx_orders_customer (customer_id, created_at),
6  ALGORITHM=INPLACE, LOCK=NONE;
7 
8-- who is holding or waiting for metadata locks?
9SELECT * FROM sys.schema_table_lock_waits\G

10Lock-երի ներքին աշխատանքը և deadlock-ների վերլուծություն

InnoDB-ն lock է անում ինդեքսի գրառումները, ոչ թե վերացական տողերը, և սա բացատրում է անակնկալների մեծ մասը։ Locking read-ը կամ UPDATE-ը lock է անում իր սկանավորած բոլոր ինդեքսային գրառումները, ոչ միայն փոփոխվողները, ուստի UPDATE-ը, որի WHERE-ը չի կարող ինդեքս օգտագործել, կարող է lock անել աղյուսակի մեծ մասը։ REPEATABLE READ-ի դեպքում InnoDB-ն նաև gap և next-key lock-եր է վերցնում միջակայքերի վրա՝ phantom-ները կանխելու համար, ինչը նշանակում է, որ INSERT-ը կարող է սպասել մի միջակայքի, որը սկանավորել է մեկ այլ transaction։ Գործնական կանոններ․ համոզվեք, որ UPDATE և DELETE հրամանները ֆիլտրում են ինդեքսով, տողերին դիպչեք հետևողական հերթականությամբ, օրինակ՝ դասավորված ըստ primary key-ի, որպեսզի երկու transaction չկարողանան lock անել հակառակ հերթականությամբ, և transaction-ները պահեք կարճ։ Երբ deadlock է լինում, InnoDB-ն rollback է անում ավելի էժան transaction-ը 1213 սխալով, և հավելվածը պարզապես պետք է կրկնի ամբողջ transaction-ը։ Այն հասկանալու համար կարդացեք SHOW ENGINE INNODB STATUS-ի LATEST DETECTED DEADLOCK բաժինը կամ սահմանեք innodb_print_all_deadlocks = ON՝ ամեն deadlock log անելու համար։ Ընթացիկ սպասումները երևում են performance_schema.data_locks-ում և sys.innodb_lock_waits-ում։

1-- who blocks whom right now
2SELECT waiting_pid, waiting_query, blocking_pid, blocking_query, wait_age
3FROM sys.innodb_lock_waits;
4 
5-- last deadlock, with both transactions and the locks they held
6SHOW ENGINE INNODB STATUS\G
7 
8-- log every deadlock to the error log
9SET PERSIST innodb_print_all_deadlocks = ON;

11High availability՝ զուգահեռ replication, semi-sync և cluster-ներ

Սովորական ասինխրոն replication-ը կարող է կորցնել վերջին transaction-ները, եթե source-ը ընկնի, իսկ replica-ն կարող է հետ մնալ ծանր գրելու բեռի տակ։ Մի քանի հնարավորություն լուծում է սա։ Multi-threaded replication-ը replica-ի վրա transaction-ները կիրառում է զուգահեռ․ replica_parallel_workers-ի default-ը 8.0.27-ից 4 է, իսկ replica_preserve_commit_order-ը անվտանգ է պահում commit-ների հերթականությունը։ Semi-synchronous replication-ը ստիպում է source-ին սպասել, մինչև առնվազն մեկ replica ստանա յուրաքանչյուր transaction, նախքան commit-ը հաստատելը՝ փոքր-ինչ latency փոխանակելով failover-ի ժամանակ հաստատված գրառումները չկորցնելու հետ։ Ավտոմատ failover-ի համար MySQL InnoDB Cluster-ը համատեղում է Group Replication-ը՝ երեք կամ ավելի հանգույցների consensus պրոտոկոլը, և MySQL Router-ը, որը կապերն ուղարկում է ընթացիկ primary-ին։ MariaDB-ի օգտատերերը հաճախ փոխարենը ընտրում են Galera Cluster-ը։ Ինչ էլ ընտրեք, մոնիտորինգ արեք lag-ը (SHOW REPLICA STATUS կամ ավելի լավ՝ heartbeat աղյուսակ), խուսափեք հսկայական միակ transaction-ներից, որոնք replica-ները պետք է վերարտադրեն հաջորդաբար, և «կարդա քո իսկ գրածը» հարցումներն ուղղեք primary-ին։

• Մեծ UPDATE կամ DELETE job-երը բաժանեք մի քանի հազար տողանոց չափաբաժինների, որպեսզի replica-ները հետ չմնան։
• Յուրաքանչյուր աղյուսակում primary key-ը պարտադիր է Group Replication-ի համար և լավ է row-based replication-ի համար։
• Փորձարկեք failover-ը․ cluster-ը, որի վրա ոչ ոք failover չի արել, չստուգված ենթադրություն է։