آموزش کاهش حجم دیتابیس MySQL

افزایش حجم دیتابیس می‌تواند به‌مرور فضای دیسک سرور را اشغال کند. جدول‌های Log، اطلاعات قدیمی، داده‌های حذف‌شده، Indexها و Binary Logها از مواردی هستند که می‌توانند باعث افزایش مصرف Storage شوند.
در این آموزش، کاهش حجم دیتابیس را در محیط MariaDB بررسی می‌کنیم. دستورات با Client سازگار mysql نوشته شده‌اند؛ بنابراین روی سروری مانند MariaDB 10.6 نیز می‌توانید از همان دستور mysql استفاده کنید.
نکته مهم این است که حذف رکوردها الزاماً بلافاصله باعث کوچک شدن فایل دیتابیس روی Disk نمی‌شود. برای آزادسازی واقعی فضا باید ابتدا عامل مصرف را شناسایی کنیم و سپس در صورت نیاز Table را Rebuild یا Optimize کنیم. MariaDB نیز OPTIMIZE TABLE را برای Defragment کردن و بازیابی فضای بلااستفاده Tableها ارائه می‌کند.

قبل از شروع Backup تهیه کنید

عملیاتی مانند:

DELETE
DROP
OPTIMIZE TABLE

می‌توانند حجم زیادی از داده را تغییر دهند. قبل از انجام Cleanup روی Database اصلی، حتماً Backup معتبر داشته باشید؛ مخصوصاً اگر قرار است داده‌های قدیمی یا Tableهای بزرگ حذف شوند. همچنین در Serverهای Production بهتر است عملیات سنگین در زمان کم‌ترافیک انجام شود.

مرحله اول: بررسی فضای Disk سرور

ابتدا وضعیت کلی Storage را بررسی کنید:

df -h

برای مثال:

Filesystem      Size  Used Avail Use%
/dev/sda2       100G   87G   13G  88%

این دستور مشخص می‌کند آیا واقعاً Disk در حال پر شدن است یا خیر.

مرحله دوم: پیدا کردن مسیر Data Directory

وارد MariaDB شوید:

mysql -u root -p

سپس اجرا کنید:

SHOW VARIABLES LIKE 'datadir';

خروجی می‌تواند مشابه زیر باشد:

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

با SHOW VARIABLES می‌توان مقادیر System Variableهای MariaDB را مشاهده کرد. حالا از Linux حجم این Directory را بررسی کنید:

du -sh /var/lib/mysql

مثلاً:

32G    /var/lib/mysql

توجه کنید که حجم datadir فقط شامل یک Database نیست و ممکن است System Databaseها، Binary Logها و سایر فایل‌های MariaDB نیز در آن قرار داشته باشند.

مرحله سوم: پیدا کردن بزرگ‌ترین دیتابیس‌ها

برای مشاهده حجم Databaseها دستور زیر را اجرا کنید:

SELECT
    table_schema AS `Database`,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Size_MB`
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

خروجی می‌تواند مشابه زیر باشد:

+--------------------+----------+
| Database           | Size_MB  |
+--------------------+----------+
| wordpress_db       | 8420.35  |
| shop_db            | 2150.72  |
| mysql              |   12.45  |
+--------------------+----------+

MariaDB اطلاعات مربوط به حجم Data و Index هر Table را از طریق information_schema.TABLES ارائه می‌کند.

مرحله چهارم: پیدا کردن بزرگ‌ترین Tableها

بعد از شناسایی Database بزرگ، باید مشخص کنیم کدام Table بیشترین فضا را مصرف می‌کند. فرض کنیم Database ما:

wordpress_db

است. دستور زیر را اجرا کنید:

SELECT
    table_name AS `Table`,
    engine AS `Engine`,
    table_rows AS `Rows`,
    ROUND(data_length / 1024 / 1024, 2) AS `Data_MB`,
    ROUND(index_length / 1024 / 1024, 2) AS `Index_MB`,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS `Total_MB`,
    ROUND(data_free / 1024 / 1024, 2) AS `Free_MB`
FROM information_schema.tables
WHERE table_schema = 'wordpress_db'
ORDER BY (data_length + index_length) DESC;

این Query علاوه بر حجم Table، مقدار Data، Index و فضای آزاد گزارش‌شده را نیز نشان می‌دهد. در information_schema.TABLES ستون‌های DATA_LENGTH، INDEX_LENGTH و DATA_FREE برای بررسی مصرف Storage در دسترس هستند. در مورد InnoDB، تعداد Rowها نیز ممکن است یک مقدار تخمینی باشد.

مرحله پنجم: دلیل بزرگ شدن Table را مشخص کنید

صرفاً بزرگ بودن یک Table به معنی غیرضروری بودن داده‌های آن نیست. ابتدا مشخص کنید Table چه اطلاعاتی نگهداری می‌کند. برای مثال Tableهای بزرگ ممکن است شامل:

Logs
Sessions
Statistics
History
Audit Data
Queue Data
Temporary Application Data

باشند. در سیستم‌هایی مانند WordPress نیز بعضی Pluginها ممکن است Log، Queue یا داده‌های تاریخی زیادی داخل Database ذخیره کنند. قبل از هر DELETE مشخص کنید داده موردنظر واقعاً قابل حذف است.

مرحله ششم: تعداد رکوردها را بررسی کنید

برای Table مشخص می‌توانید از:

SELECT COUNT(*)
FROM table_name;

استفاده کنید. برای مثال:

SELECT COUNT(*)
FROM application_logs;

اگر Table بسیار بزرگ باشد، اجرای COUNT(*) نیز ممکن است زمان‌بر باشد؛ بنابراین روی Databaseهای Production ابتدا وضعیت Load سرور را بررسی کنید. برای مشاهده Queryهای فعال می‌توانید به مقاله پیدا کردن Query های در حال اجرا در MySQL مراجعه کنید.

مرحله هفتم: داده‌های قدیمی را قبل از حذف بررسی کنید

هیچ‌وقت مستقیماً دستور:

DELETE FROM application_logs;

را روی یک Table ناشناخته اجرا نکنید. ابتدا رکوردهایی را که قرار است حذف شوند با SELECT بررسی کنید. برای مثال :

SELECT *
FROM application_logs
WHERE created_at < '2025-01-01'
LIMIT 100;

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

DELETE FROM application_logs
WHERE created_at < '2025-01-01';

تاریخ و Table بالا فقط مثال هستند و باید متناسب با Application واقعی تغییر داده شوند.

چرا بعد از DELETE حجم فایل کم نمی‌شود؟

این یکی از مهم‌ترین نکات مقاله است. فرض کنید یک Table:

10 GB

حجم دارد و 6 GB از داده‌های آن را حذف می‌کنید. ممکن است بعد از DELETE فایل Table همچنان تقریباً همان 10 GB باقی بماند. دلیل این است که InnoDB می‌تواند فضای آزادشده را برای داده‌های جدید همان Table دوباره استفاده کند؛ اما این فضا الزاماً بلافاصله به File System بازگردانده نمی‌شود.
MariaDB برای Tableهایی که حجم زیادی از داده آن‌ها حذف شده است، استفاده از OPTIMIZE TABLE را برای Defragment و Reclaim کردن فضای بلااستفاده پشتیبانی می‌کند.

مرحله هشتم: بررسی innodb_file_per_table

قبل از Optimize بهتر است وضعیت این تنظیم را بررسی کنید:

SHOW VARIABLES LIKE 'innodb_file_per_table';

اگر خروجی:

ON

باشد، هر InnoDB Table معمولاً Tablespace جداگانه با پسوند:

.ibd

دارد. MariaDB توضیح می‌دهد که با innodb_file_per_table=ON هر InnoDB Table در Tablespace اختصاصی ذخیره می‌شود و این ساختار بازیابی فضای Disk را ساده‌تر می‌کند.

مرحله نهم: Optimize کردن Table

بعد از حذف مقدار قابل‌توجهی داده می‌توانید Table موردنظر را Optimize کنید:

OPTIMIZE TABLE wordpress_db.application_logs;

خروجی ممکن است شامل پیامی مشابه این باشد:

Table does not support optimize,
doing recreate + analyze instead

و در نهایت:

status    OK

برای InnoDB، OPTIMIZE TABLE معمولاً می‌تواند Table و Indexهای آن را Rebuild کند. MariaDB این عملیات را برای آزادسازی فضای بلااستفاده و Defragment کردن Table پشتیبانی می‌کند.

جمع‌بندی

برای کاهش حجم دیتابیس ابتدا با Query روی:

information_schema.tables

مشخص کنید کدام Database و Table بیشترین فضا را مصرف می‌کند. پس از شناسایی داده‌های غیرضروری، آن‌ها را با شرط مناسب حذف یا Archive کنید. اگر مقدار زیادی از داده یک InnoDB Table حذف شده باشد، می‌توان در شرایط مناسب از:

OPTIMIZE TABLE database_name.table_name;

برای Rebuild و بازیابی فضای بلااستفاده استفاده کرد.