۱۱ تکنیک طلایی برای بهینه‌سازی پایگاه داده: افزایش عملکرد و کارایی

پایگاه داده معمولاً یکی از بخش‌های کند در عملکرد نرم‌افزارها است. داشتن پایگاه داده‌ای که خوب بهینه شده باشد برای سیستم‌های پرسرعت ضروری است. در ادامه چند تکنیک کاربردی برای بهینه‌سازی پایگاه داده معرفی می‌کنم:

1. Indexing

ایجاد ایندکس‌ها: ایندکس‌ها مثل فهرست هستند و به پایگاه داده کمک می‌کنند که اطلاعات موردنیاز را سریع‌تر پیدا کند. با مرتب کردن داده‌ها، ایندکس‌ها باعث می‌شوند تا پایگاه داده راحت‌تر سطرهایی را که شرط WHERE را دارند پیدا کند و سرعت کوئری‌های SELECT افزایش یابد. البته، ایندکس‌ها ممکن است سرعت عملیات نوشتن (مثل INSERT و UPDATE) را کمی کند کنند، پس باید بین سرعت خواندن و نوشتن تعادل برقرار کنیم.

CREATE INDEX idx_username ON users(username);

2. Using Composite Indexes

ایندکس‌های ترکیبی: ایندکس‌های ترکیبی شامل چند ستون هستند و برای وقتی که در کوئری‌ها باید چند شرط مختلف بررسی یا داده‌ها بر اساس چند ستون مرتب شوند، کاربرد دارند. این کار باعث می‌شود به جای ایندکس‌های جداگانه روی هر ستون، فقط یک ایندکس استفاده کنیم و پایگاه داده سریع‌تر به نتیجه برسد.

CREATE INDEX idx_name_age ON employees(name, age);

3. Normalization and Denormalization

نرمال‌سازی (Normalization): این فرآیند داده‌ها را به گونه‌ای سازمان‌دهی می‌کند که از تکرار و وابستگی‌های غیرضروری کاسته شود و احتمال خطا در داده‌ها کاهش یابد. با تقسیم جداول بزرگ به جداول کوچک و مرتبط، نرمال‌سازی به یکپارچگی داده‌ها کمک می‌کند. البته، ممکن است کوئری‌ها را پیچیده‌تر کند.

مثال کد SQL (Normalization در فرم سوم):

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    address VARCHAR(255)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

دنرمال‌سازی (Denormalization): در حالی که نرمال‌سازی از تکرار داده‌ها جلوگیری می‌کند، دنرمال‌سازی با افزودن تکرار به‌صورت کنترل‌شده، سرعت کوئری‌ها را (به‌ویژه برای عملیات خواندن زیاد) افزایش می‌دهد. این کار ممکن است شامل افزودن ستون‌ها یا جداول تکراری به شکل استراتژیک باشد تا دسترسی به داده‌ها سریع‌تر انجام شود.

CREATE TABLE denormalized_orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),
    order_date DATE
);

4. Query Optimization

بهینه‌سازی کوئری‌ها: کوئری‌هایی که زیاد استفاده می‌شوند را به‌طور مرتب بررسی و بهینه‌سازی کنید. از ابزارهایی مثل EXPLAIN استفاده کنید تا برنامه اجرای کوئری را ببینید و نقاط قابل بهبود را شناسایی کنید.

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

**اجتناب از استفاده از SELECT ***: به‌جای انتخاب همه ستون‌ها، فقط ستون‌های موردنیاز را بازیابی کنید. این کار مقدار داده‌های منتقل‌شده و پردازش‌شده را کاهش می‌دهد و عملکرد کوئری را بهبود می‌بخشد.

SELECT order_id, order_date FROM orders WHERE customer_id = 123;

5. Partitioning

پارتیشن‌بندی جداول: پارتیشن‌بندی به معنای تقسیم جداول بزرگ به قطعات کوچکتر و قابل مدیریت‌تر است. این کار می‌تواند عملکرد کوئری را به‌طور قابل‌توجهی بهبود بخشد، زیرا موتور پایگاه داده می‌تواند بر روی زیرمجموعه‌های کوچکتر داده کار کند و به این ترتیب زمان اجرای کوئری کاهش می‌یابد.

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN (2010),
    PARTITION p3 VALUES LESS THAN (2020),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

پارتیشن‌پروینیگ (Partition Pruning): اطمینان حاصل کنید که برنامه‌ریز کوئری در حین اجرای کوئری، پارتیشن‌های غیرضروری را حذف می‌کند. این کار از اسکن کل مجموعه داده جلوگیری می‌کند و به بهبود عملکرد کمک می‌کند.

SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2023-01-01';

6. Caching

کشینگ کوئری: یک مکانیزم کش برای ذخیره نتایج کوئری‌های پرکاربرد پیاده‌سازی کنید. این کار بار روی پایگاه داده را کاهش می‌دهد و با ارائه نتایج کش‌شده، زمان پاسخگویی را افزایش می‌دهد.

-- Pseudocode
DECLARE @cacheKey NVARCHAR(255) = 'query_cache_key';
DECLARE @cachedResult NVARCHAR(MAX);

SET @cachedResult = REDIS.GET(@cacheKey);

IF @cachedResult IS NULL
BEGIN
    -- Execute the query and store the result in the cache
    SET @cachedResult = EXECUTE_QUERY('SELECT * FROM large_table');
    REDIS.SET(@cacheKey, @cachedResult, EXPIRY_TIME);
END

-- Use @cachedResult for further processing

کشینگ شیء (Object Caching): اشیاء یا داده‌های پرکاربرد را در لایه اپلیکیشن کش کنید تا تعداد کوئری‌ها به پایگاه داده کاهش یابد. این کار می‌تواند با استفاده از کتابخانه‌ها یا فریم‌ورک‌های کشینگ در حافظه انجام شود.

from django.core.cache import cache

def get_user_data(user_id):
    # Try to fetch user data from cache
    user_data = cache.get(f'user_{user_id}')

    if user_data is None:
        # If not in cache, fetch from the database
        user_data = User.objects.get(id=user_id)

        # Store the data in cache for future requests
        cache.set(f'user_{user_id}', user_data, TIMEOUT)

    return user_data

7. Regular Maintenance

به‌روزرسانی آمار: به‌روز نگه‌داشتن آمار برای برنامه‌ریز کوئری بسیار مهم است تا تصمیمات بهتری درباره برنامه‌های اجرایی بگیرد. به‌طور منظم آمار را به‌روزرسانی کنید تا از بهینه‌سازی دقیق و کارآمد کوئری‌ها اطمینان حاصل شود.

-- Update statistics for a table
UPDATE STATISTICS table_name;

8. Data Archiving

بایگانی داده‌ها: داده‌های قدیمی که دیگر به آن‌ها نیازی نیست را بایگانی یا حذف کنید. این کار می‌تواند عملکرد کوئری‌ها را بهبود بخشد و نیاز به فضای ذخیره‌سازی را کاهش دهد، به‌ویژه در سیستم‌هایی که دارای مجموعه‌داده‌های تاریخی بزرگ هستند.

-- Archive data older than a certain date
DELETE FROM historical_data WHERE date < '2020-01-01';

9. Hardware Optimization

بهینه‌سازی پیکربندی سرور: تنظیمات و پیکربندی‌های سرور پایگاه داده را بر اساس بار کاری و قابلیت‌های سخت‌افزاری تنظیم کنید. این شامل پارامترهایی مانند اندازه بافرها، تنظیمات کش و محدودیت‌های اتصال می‌شود.

-- Example: Increase the size of the query cache
SET GLOBAL query_cache_size = 256M;

استفاده از SSDها: از درایوهای حالت جامد (SSDs) برای ذخیره‌سازی استفاده کنید. SSDها به‌طور قابل توجهی نسبت به درایوهای سخت (HDDها) دسترسی سریع‌تری به داده‌ها ارائه می‌دهند که منجر به بهبود عملکرد کلی پایگاه داده می‌شود.

10. Concurrency Control

سطوح ایزولاسیون: سطوح ایزولاسیون را بر اساس نیازهای اپلیکیشن خود تنظیم کنید. سطوح ایزولاسیون کنترل می‌کنند که تغییرات ایجاد شده توسط یک تراکنش برای سایر تراکنش‌ها چقدر قابل مشاهده باشد. انتخاب سطح ایزولاسیون مناسب برای برقراری تعادل بین سازگاری و عملکرد بسیار مهم است.

-- Set isolation level to READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

11. Using Connection Pooling

Using Connection Pooling: از اتصال‌های پایگاه داده استفاده مجدد کنید تا از بار اضافی ایجاد اتصال‌های جدید برای هر درخواست جلوگیری شود. اتصال‌های استخر به بهینه‌سازی مدیریت و استفاده مجدد از اتصالات پایگاه داده کمک می‌کنند و از این رو عملکرد کلی اپلیکیشن را بهبود می‌بخشند.

// Example using Node.js with mysql2
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'my_database',
    connectionLimit: 10
});

// Using a connection from the pool
pool.getConnection((err, connection) => {
    if (err) throw err;
    // Use the connection
    connection.query('SELECT * FROM users', (error, results) => {
        // Release the connection back to the pool
        connection.release();
    });
});

نتیجه‌گیری

بهینه‌سازی پایگاه داده یک فرآیند مستمر است که شامل ارزیابی و بهبود مداوم است. با پیاده‌سازی این تکنیک‌ها می‌توانید عملکرد پایگاه داده را بهبود بخشید و تجربه کاربری بهتری برای کاربران خود فراهم کنید. توجه داشته باشید که انتخاب تکنیک‌ها باید بر اساس نیازهای خاص اپلیکیشن شما و نوع بار کاری انجام شود.

منبع: وبسایت میموک