پاک کردن دیتای زیاد از جدول

پاک کردن رکوردهای زیاد از جداول لاگ

به عنوان یک DBA، ما به‌طور مرتب با وظایف مربوط به مدیریت پایگاه داده سروکار داریم. یکی از این وظایف، حذف داده‌های قدیمی از جداول است. این موضوع به‌ویژه برای جداولی که داده‌های لاگ را ذخیره می‌کنند، بسیار مهم است. در بسیاری از موارد، چندین برنامه ممکن است به‌طور همزمان به یک جدول لاگ‌ بنویسند و این باعث می‌شود که حجم داده‌ها به سرعت افزایش پیدا کند.

تصور کنید جدولی برای ذخیره‌سازی لاگ داریم که یا اخیراً پاک‌سازی نشده یا شاید هیچ‌وقت این فرآیند برای آن انجام نشده است. حالا ما باید تمامی داده‌هایی که بیش از ۹۰ روز از آن‌ها گذشته را حذف کنیم. بعد از بررسی، متوجه می‌شویم که حدود ۶۰ میلیون رکورد نیاز به حذف دارد. بهترین روش برای انجام این کار چیست؟ یکی از گزینه‌ها این است که یک دستور DELETE به شکل زیر اجرا شود:

DELETE FROM Table1
 WHERE MyDate < getdate() – 90;

ین دستور عملیات حذف را انجام می‌دهد، اما چالش‌های مهمی به همراه دارد:

اولین مشکل این است که حذف داده‌ها زمان‌بر است. SQL Server تمامی این فرآیند را در قالب یک تراکنش مدیریت می‌کند تا در صورت بروز خطا، امکان بازگشت (rollback) وجود داشته باشد. این بدان معناست که همزمان با حذف رکوردها، حجم زیادی از اطلاعات به لاگ تراکنش‌ها نیز اضافه می‌شود که می‌تواند فضای دیسک را پر کرده و حتی منجر به بروز خطا شود. همچنین، اگر خطایی رخ دهد و SQL Server نیاز به بازگردانی داشته باشد، این فرآیند می‌تواند بیشتر از زمان حذف اولیه طول بکشد. برای مثال، اگر خطایی پس از ۴۵ دقیقه از شروع عملیات رخ دهد، بازگشت ممکن است تا ۹۰ دقیقه زمان ببرد، بسته به تنظیمات سیستم.

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

علاوه بر این، حذف‌های حجیم منابع زیادی مانند I/O مصرف می‌کنند که می‌تواند کارایی سیستم را به شدت کاهش داده و باعث ایجاد تأخیر در اجرای برنامه‌ها شود، چیزی که در بسیاری از موارد قابل قبول نیست.

پس، برای حذف تعداد زیادی رکورد چه باید کرد؟ بهترین رویکرد این است که داده‌ها را به صورت تدریجی و در بخش‌های کوچک حذف کنیم.

در ابتدا، به ستونی که در شرط WHERE استفاده می‌شود (مثلاً ستون CreatedDate) دقت کنید. آیا این ستون ایندکس شده است؟ اگر نه، احتمالاً عملیات حذف منجر به اسکن‌های زیادی روی جدول می‌شود که می‌خواهیم از آن اجتناب کنیم. اگر بتوانید ایندکسی برای این ستون ایجاد کنید، ایده‌آل خواهد بود؛ اما در بسیاری از مواقع به دلایل مختلف این امکان وجود ندارد. بهترین کار این است که یک عملیات حذف کوچک را بر اساس این ستون اجرا کنید و برنامه اجرایی (execution plan) را بررسی کنید تا بفهمید چه اتفاقی در حال رخ دادن است و این عملیات چقدر زمان می‌برد.

Delete Top (10000)
From TableA
Where CreatedDate < GetDate() – 90;

با اجرای این دستور متوجه می‌شویم که اسکن جدول انجام می‌شود و زمان اجرای آن حدود ۴۵ ثانیه طول می‌کشد، که این زمان برای ما مناسب نیست. بنابراین، به دنبال بهینه‌سازی می‌رویم. اولین قدم، پیدا کردن کلید اصلی جدول است. فرض کنیم که کلید اصلی جدول ستون ID باشد. در این مرحله می‌توانیم از کلید اصلی به همراه ستون تاریخ (مثل CreatedDate) برای بهبود عملکرد دستور استفاده کنیم.

ایده این است که به جای اجرای یک دستور DELETE بزرگ، داده‌ها را به صورت تدریجی و در بخش‌های کوچک‌تر با ترکیب ID و CreatedDate حذف کنیم. با استفاده از کلید اصلی، می‌توانیم رکوردها را به صورت محدود شده و در محدوده‌های مشخصی از ID حذف کنیم. این روش باعث می‌شود که هم قفل‌های جدول کاهش یابد و هم کارایی عملیات بهبود پیدا کند، در حالی که به طور همزمان حجم داده‌های حذف شده را مدیریت می‌کنیم تا تأثیر منفی روی سیستم به حداقل برسد.

Delete
From TableA
Where ID IN (
    Select Top 10000 ID
    From TableA 
    Where CreatedDate < GetDate() – 90
);

این روش به شما امکان می‌دهد که از کلید اصلی برای حذف داده‌ها استفاده کنید که به مراتب کارآمدتر است. اجرای آن ۱۸ ثانیه زمان می‌برد که در مقایسه با ۴۵ ثانیه قبلی، بسیار بهتر است؛ اما هنوز هم برای حذف ۱۰,۰۰۰ رکورد زمان زیادی است.

برای بهینه‌سازی بیشتر، تست‌های بیشتری با تعداد ردیف‌های مختلف انجام می‌دهیم:

حذف ۲۰,۰۰۰ ردیف: ۳۵ ثانیه طول می‌کشد.
حذف ۱۰,۰۰۰ ردیف: ۱۸ ثانیه زمان می‌برد.
حذف ۵,۰۰۰ ردیف: تنها ۴ ثانیه طول می‌کشد.
به نظر می‌رسد که ۵,۰۰۰ ردیف نقطه بهینه باشد. برای مثال، اگر ۱۰,۰۰۰ ردیف را در یک مرحله حذف کنیم، ۱۸ ثانیه طول می‌کشد؛ اما اگر این تعداد را به دو مرحله ۵,۰۰۰ تایی تقسیم کنیم، کل فرآیند فقط ۸ ثانیه زمان می‌برد. بنابراین، حذف‌های کوچک‌تر و مکرر کارآمدتر از حذف‌های بزرگ‌تر هستند.

با این روش، حذف ۶۰ میلیون رکورد حدود ۶.۶ ساعت طول خواهد کشید. این زمان اگرچه عالی نیست، اما قابل قبول است. شما می‌توانید یک حلقه بنویسید که به طور پیوسته رکوردهای ۵,۰۰۰ تایی را حذف کند تا کل عملیات به پایان برسد. البته استفاده از کرسر (cursor) هم امکان‌پذیر است، اما از آنجایی که معمولاً کرسرها پیچیده و پرهزینه هستند، پیشنهاد می‌شود از آن‌ها اجتناب شود.

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