بهینه‌سازی عملکرد MySQL: از کوئری‌های کند تا دیتابیسی برق‌آسا

عملکرد دیتابیس اغلب گلوگاه اپلیکیشن‌های وب است. این راهنما تکنیک‌های جامع بهینه‌سازی MySQL را پوشش می‌دهد؛ از بهبودهای سطح کوئری گرفته تا تنظیم پیکربندی سرور.

درک نحوهٔ اجرای کوئری

پیش از بهینه‌سازی، با کمک EXPLAIN بفهمید MySQL کوئری‌ها را چگونه اجرا می‌کند:

EXPLAIN SELECT 
    o.id,
    o.total,
    u.name,
    COUNT(oi.id) as item_count
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'completed'
    AND o.created_at > '2024-01-01'
GROUP BY o.id
ORDER BY o.created_at DESC
LIMIT 20;

<Callout type="info"> ستون‌های کلیدی EXPLAIN که باید زیر نظر بگیرید: type (به دنبال ref یا بهتر از آن باشید)، rows (هرچه کمتر، بهتر) و Extra (از "Using filesort" و "Using temporary" پرهیز کنید). </Callout>

تحلیل خروجی EXPLAIN

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
|  1 | SIMPLE      | o     | range  | idx_status    | idx_... | 4       | NULL             | 5000 | Using where |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY | 4       | mydb.o.user_id   |    1 | NULL        |
|  1 | SIMPLE      | oi    | ref    | idx_order     | idx_... | 4       | mydb.o.id        |    3 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+

استراتژی‌های ایندکس‌گذاری

طراحی ایندکس ترکیبی (Composite)

ایندکس‌ها را بر اساس الگوهای کوئری طراحی کنید:

-- Query pattern: Filter by status, date range, sort by date
SELECT * FROM orders 
WHERE status = 'pending' 
AND created_at > '2024-01-01'
ORDER BY created_at DESC;

-- Optimal composite index (leftmost prefix rule)
CREATE INDEX idx_orders_status_created 
ON orders(status, created_at);

-- For queries with multiple equality conditions
SELECT * FROM products
WHERE category_id = 5
AND brand_id = 10
AND is_active = 1;

-- Index with most selective column first
CREATE INDEX idx_products_brand_cat_active
ON products(brand_id, category_id, is_active);

ایندکس‌های پوششی (Covering Index)

با ایندکس‌های پوششی از مراجعهٔ دوباره به خود جدول بی‌نیاز شوید:

-- Query only needs specific columns
SELECT id, name, price FROM products
WHERE category_id = 5
ORDER BY price;

-- Covering index includes all needed columns
CREATE INDEX idx_products_covering
ON products(category_id, price, id, name);

-- MySQL can satisfy query entirely from index
-- EXPLAIN shows "Using index" in Extra column

ایندکس برای عملیات JOIN

-- Ensure foreign keys are indexed
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- For complex joins, index the join columns
SELECT p.name, SUM(oi.quantity) as total_sold
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.created_at > '2024-01-01'
GROUP BY p.id
ORDER BY total_sold DESC;

-- Indexes needed:
-- orders(created_at) - for WHERE filter
-- order_items(order_id) - for JOIN
-- order_items(product_id) - for JOIN

<Note title="نکتهٔ ایندکس"> در ایندکس‌گذاری زیاده‌روی نکنید! هر ایندکس، عملیات INSERT/UPDATE را کندتر می‌کند. ایندکس‌های بی‌استفاده را با sys.schema_unused_indexes زیر نظر بگیرید. </Note>

تکنیک‌های بهینه‌سازی کوئری

پرهیز از اسکن کامل جدول (Full Table Scan)

-- Bad: Function on indexed column prevents index use
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- Good: Range query uses index
SELECT * FROM users 
WHERE created_at >= '2024-01-01' 
AND created_at < '2025-01-01';

-- Bad: Leading wildcard prevents index use
SELECT * FROM products WHERE name LIKE '%phone%';

-- Good: Trailing wildcard can use index
SELECT * FROM products WHERE name LIKE 'phone%';

-- For full-text search, use FULLTEXT index
ALTER TABLE products ADD FULLTEXT INDEX ft_name (name);
SELECT * FROM products WHERE MATCH(name) AGAINST('phone');

بهینه‌سازی سابکوئری‌ها

-- Bad: Correlated subquery runs for each row
SELECT * FROM products p
WHERE price > (
    SELECT AVG(price) FROM products 
    WHERE category_id = p.category_id
);

-- Good: JOIN with derived table
SELECT p.* FROM products p
JOIN (
    SELECT category_id, AVG(price) as avg_price
    FROM products
    GROUP BY category_id
) cat_avg ON p.category_id = cat_avg.category_id
WHERE p.price > cat_avg.avg_price;

-- Even better: Window function (MySQL 8.0+)
SELECT * FROM (
    SELECT *, AVG(price) OVER (PARTITION BY category_id) as avg_price
    FROM products
) t WHERE price > avg_price;

بهینه‌سازی صفحه‌بندی (Pagination)

-- Bad: OFFSET scans and discards rows
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 100000;

-- Good: Keyset pagination (cursor-based)
SELECT * FROM products 
WHERE id > 100000  -- Last seen ID
ORDER BY id 
LIMIT 10;

-- For complex sorting, use deferred join
SELECT p.* FROM products p
JOIN (
    SELECT id FROM products
    ORDER BY created_at DESC, id DESC
    LIMIT 10 OFFSET 100000
) t ON p.id = t.id;

تنظیم پیکربندی سرور

بافر پول InnoDB

# my.cnf - For dedicated database server with 32GB RAM

[mysqld]
# Buffer pool should be 70-80% of available RAM
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 24

# Log file size affects recovery time vs write performance
innodb_log_file_size = 2G
innodb_log_buffer_size = 64M

# Flush settings (1 = safest, 2 = faster)
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# Thread concurrency
innodb_thread_concurrency = 0
innodb_read_io_threads = 8
innodb_write_io_threads = 8

تنظیمات Query Cache و حافظه

[mysqld]
# Connection handling
max_connections = 500
thread_cache_size = 100

# Memory per connection
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M

# Temporary tables
tmp_table_size = 256M
max_heap_table_size = 256M

# Table cache
table_open_cache = 4000
table_definition_cache = 2000

مانیتورینگ و پروفایلینگ

لاگ کوئری‌های کند (Slow Query Log)

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

کوئری‌های Performance Schema

-- Find top 10 slowest queries
SELECT 
    DIGEST_TEXT,
    COUNT_STAR as exec_count,
    ROUND(SUM_TIMER_WAIT/1000000000000, 2) as total_time_sec,
    ROUND(AVG_TIMER_WAIT/1000000000, 2) as avg_time_ms,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- Find tables with most I/O
SELECT 
    object_schema,
    object_name,
    count_read,
    count_write,
    ROUND(sum_timer_read/1000000000000, 2) as read_time_sec,
    ROUND(sum_timer_write/1000000000000, 2) as write_time_sec
FROM performance_schema.table_io_waits_summary_by_table
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- Find unused indexes
SELECT * FROM sys.schema_unused_indexes;

-- Find redundant indexes
SELECT * FROM sys.schema_redundant_indexes;

مانیتورینگ بلادرنگ

-- Current running queries
SELECT 
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    LEFT(info, 100) as query
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

-- InnoDB status
SHOW ENGINE INNODB STATUS\G

-- Buffer pool hit ratio (should be > 99%)
SELECT 
    (1 - (
        (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') /
        (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests')
    )) * 100 as buffer_pool_hit_ratio;

پارتیشن‌بندی برای جدول‌های بزرگ

-- Range partitioning by date
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT,
    user_id INT NOT NULL,
    total DECIMAL(10,2),
    status VARCHAR(20),
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at),
    INDEX idx_user (user_id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Queries automatically prune partitions
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' 
AND created_at < '2024-07-01';
-- Only scans p2024 partition

Connection Pooling

پولینگ در سطح اپلیکیشن

// Node.js with mysql2
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'app_user',
  password: 'password',
  database: 'myapp',
  waitForConnections: true,
  connectionLimit: 20,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 10000
});

// Use pool for queries
async function getUser(id) {
  const [rows] = await pool.execute(
    'SELECT * FROM users WHERE id = ?',
    [id]
  );
  return rows[0];
}

جمع‌بندی

بهینه‌سازی عملکرد MySQL فرایندی تکرارشونده است. کار را با شناسایی کوئری‌های کند به کمک لاگ کوئری‌های کند شروع کنید، آن‌ها را با EXPLAIN تحلیل کنید، ایندکس‌های مناسب اضافه کنید و نتیجه را زیر نظر بگیرید. پیکربندی سرور را هم باید بر اساس ویژگی‌های بار کاری (workload) و منابع در اختیارتان تنظیم کنید.

نکات کلیدی:

• ایندکس‌ها را بر اساس الگوهای واقعی کوئری‌ها طراحی کنید
• برای درک نحوهٔ اجرای کوئری از EXPLAIN استفاده کنید
• در شرط‌های WHERE از اعمال تابع روی ستون‌های ایندکس‌شده پرهیز کنید
• بافر پول InnoDB را متناسب با شرایط پیکربندی کنید
• با Performance Schema به‌صورت پیوسته مانیتور کنید

این یادداشت ترجمهٔ فارسی نوشتهٔ خودم است — نسخهٔ اصلی (انگلیسی) در dev.to