Boela kwa blog
Ferikgong 27, 2026Dikaelo

Jaanong o ka Nolofatsa Tiro ya MySQL

Tlhaloso e e feletseng bakeng sa go nolofatsa tiro ya database ya MySQL: go rulaganya configuration, go nolofatsa ditaelo, go tsenya di-index, le go lokela.

Jaanong o ka Nolofatsa Tiro ya MySQL

Go nolofatsa tiro ya MySQL go botlhokwa ka botlalo bakeng sa di-application tse di laolang dipalo tse dintsi tsa data kgotsa traffic e e kwa godimo. Tlhaloso eno e kaya ditsela tse di botlhokwa tsa go ntlafatsa tiro ya MySQL, go simolola ka go rulaganya configuration go ya go go nolofatsa ditaelo.

Go Nolofatsa Configuration ya MySQL

Fetola faele ya configuration ya MySQL go nolofatsa tiro go ya ka didiriswa tsa server ya gago:

bash
# Fetola configuration ya MySQL (Ubuntu/Debian)
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Fetola configuration ya MySQL (CentOS/RHEL)
sudo nano /etc/my.cnf

# Tsenya kgotsa fetola ditsamaiso tseno:
[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
max_connections = 200
query_cache_size = 64M
query_cache_type = 1
tmp_table_size = 64M
max_heap_table_size = 64M

# Simolola gape MySQL
sudo systemctl restart mysql  # Ubuntu/Debian
sudo systemctl restart mysqld   # CentOS/RHEL

Go Nolofatsa InnoDB

InnoDB ke engine ya motlha ya go boloka ya MySQL. E nolofatse go nna le tiro e e botoka:

bash
# Mo /etc/mysql/mysql.conf.d/mysqld.cnf kgotsa /etc/my.cnf
[mysqld]
# InnoDB buffer pool (dirisa 70-80% ya RAM e e gona)
innodb_buffer_pool_size = 2G

# Bogolo jwa faele ya log ya InnoDB
innodb_log_file_size = 512M

# Mokgwa wa go tlhatswa wa InnoDB (bakeng sa SSD)
innodb_flush_method = O_DIRECT

# Di-thread tsa I/O tsa InnoDB
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# Tlhatswa log ya InnoDB mo go tiisa transaction
innodb_flush_log_at_trx_commit = 2

Configuration ya Query Cache

Query cache e ka ntlafatsa tiro bakeng sa ditiro tse di nonofileng tsa go bala (Tlhagiso: Query cache e se sa dirisiwa mo MySQL 8.0):

bash
# Bakeng sa MySQL 5.7 le tse di sa tlang
[mysqld]
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

# Sekaseka boemo jwa query cache
mysql> SHOW VARIABLES LIKE 'query_cache%';

# Sekaseka dipalo tsa query cache
mysql> SHOW STATUS LIKE 'Qcache%';

Go Nolofatsa Di-Index

Go tsenya di-index ka tsela e e siameng go botlhokwa ka botlalo bakeng sa tiro ya ditaelo:

bash
# Sekaseka tafole go ntsha dipalo tsa index
ANALYZE TABLE table_name;

# Sekaseka di-index tse di sa dirisiwang
SELECT * FROM sys.schema_unused_indexes;

# Tlhama index
CREATE INDEX idx_column ON table_name(column_name);

# Tlhama index e e kopaneng
CREATE INDEX idx_multi ON table_name(col1, col2, col3);

# Bontsha di-index
SHOW INDEXES FROM table_name;

# Tlosa index e e sa dirisiwang
DROP INDEX idx_name ON table_name;

Go Nolofatsa Ditaelo

Nolofatsa ditaelo tsa gago tsa SQL go nna le tiro e e botoka:

  • Dirisa EXPLAIN go sekaseka di-plan tsa go dira ditaelo: EXPLAIN SELECT * FROM table WHERE column = 'value'
  • Gapela SELECT * - tlhopha fela dikolomo tse di tlhokegang
  • Dirisa LIMIT go thibela ditsetso tsa dipoelo
  • Dirisa di-WHERE clause tse di siameng tse di nang le dikolomo tse di nang le di-index
  • Gapela ditiro mo di-WHERE clause (sekai, WHERE YEAR(date) = 2026 → WHERE date >= '2026-01-01')
  • Dirisa di-JOIN mo boemong jwa di-subquery fa go kgonega
  • Dirisa UNION mo boemong jwa OR bakeng sa maemo a le mantsi fa go siame

Go Lokela Tiro ya MySQL

bash
# Sekaseka boemo jwa MySQL
mysql> SHOW STATUS;

# Sekaseka ditaelo tse di nonofileng
mysql> SHOW VARIABLES LIKE 'slow_query%';

# Nolofatsa log ya ditaelo tse di nonofileng
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

# Sekaseka lenaneo la ditiro
mysql> SHOW PROCESSLIST;

# Sekaseka boemo jwa tafole
mysql> SHOW TABLE STATUS LIKE 'table_name';

# Sekaseka boemo jwa InnoDB
mysql> SHOW ENGINE INNODB STATUS;

Dikgakololo tsa Go Nolofatsa Tiro

  • Ka metlha dira OPTIMIZE TABLE go defragmenta ditafole
  • Lokela log ya ditaelo tse di nonofileng mme o nolofatse ditaelo tse di nonofileng
  • Dirisa connection pooling go fokotsa overhead ya kopano
  • Kgaoganya ditafole tse dikgolo ka letsatsi kgotsa selekanyo bakeng sa tiro e e botoka
  • Dirisa di-replica tsa go bala bakeng sa ditiro tse di nonofileng tsa go bala
  • Boloka MySQL e e ntsha mofuta o o ikanyegang o o sa tlang
  • Lokela I/O ya disk mme o nagane ka go dirisa boloko jwa SSD/NVMe
  • Dirisa EXPLAIN ANALYZE (MySQL 8.0+) bakeng sa sekaseko se se tseneletseng sa ditaelo