برنامه نویس وب و موبایل


یکشنبه 22 تیر
MVC

پارتیشن بندی جداول در sql server برای به صفر کردن زمان عملیات حذف و درج

پنجشنبه, 15 خرداد 1399

امروز می خوام در مورد یکی از قابلیتهای جالب و کاربردی sql server براتون مطلب بذارم در شرکت ما یه جدول داریم که حجمش شش ترا بایت هست و دیتای روزانه یکی از بانکها رو براساس سرفصل های حسابداری و شعبه های بانک و یه سری چیزای دیگه به صورت روزانه نگه میداره مشکلی که داشتیم این بود که دیتای پایه ممکنه تغییر کنه و کاربر مجبوره برای اون روزی که دیتاش تغییر کرده تایید مالی بزنه در تایید مالی ما دو اتفاق می افتهاولیش محاسبه دیتای اون روز به اضای شعب و سرفصل و ... دوم حذف دیتای قبلی اون دوره از جدول اصلی و انتقال دیتای محاسبه شده جدید این اتفاق برای  هر روز بیش از 35 دقیقه طول می کشید که فقط حذف اون دوره 15 دقیقه طول می کشید به دلیل حجم زیاد دیتا من این تایید مالی به زیر ده دقیقه رسوندم با کمک همین پارتیشن بندی جدول، چطور ؟ به ازای هر دوره که داشتیم یه پارتیشن درست کردم و دیتای اون دوره داخل پارتیشن خودش گذاشتم باور نکردنی هست هم عملیات درج و هم عملیات حذف اون دوره نزدیک به صفر ثانیه اتفاق می افتاد و این کمک بزرگی به ما کرد در ادمه کدهاش می ذارم امیدوارم این مطلب به درد تون بخوره 

من اول کد ساخت پارتیشن میذارم بعد توضیح میدم

CREATE PARTITION FUNCTION pfIntervalRange(SMALLINT)
AS
RANGE LEFT FOR VALUES (3652,3653)
GO
CREATE PARTITION SCHEME psIntervalRange
AS
PARTITION pfIntervalRange ALL TO([اسم فایل گروپ جدول])
GO

CREATE PARTITION SCHEME psIntervalFVIndexRange 
AS
PARTITION pfIntervalRange ALL TO([اسم فایل گروپ ایندکس])
GO

اول از همه ما باید یک Partition Function درست کنیم که تنظیمات اولیه پارتیشن ما رو شامل میشه کد اسکریپتش می تونید در خط اول مشاهده کنید داخل پرانتز نوع ستونی که می خوایم براساس اون پارتیشن بندی کنیم می ذاریم من اینجا چون دوره هام توی یه جدول دیگه ذخیره کردم و مقدار id اون در جدول دیتا نگه میدارم پس نوع ستونم SMALLINT هست حال برای شما ممکنه هر چیز دیگه ای باشه

در خط بعد میشه لیست پارتیشن ها بهش داد من فعلا دو تا رو بهش دادم وبعدا میشه به این پارتیشن ها اضافه کرد.

گام بعدی پس از ایجاد Partition Function، تعریف Partition Schema است، که به منظور قرار گرفتن هر یک از پارتیشن‌های تعریف شده توسط Function در Filegroup‌های مناسب آن استفاده می‌شود و من در اینجا دو تا Schema ساختم یکی برای دیتا یکی هم برای ایندکس ها حالا نوبت ساخت جداول مربوطه ست من دو تا جدول داشتم که یکی temp بود و مقادیر محاسبه شده نگه میداشت و یکی هم جدول اصلی که بعد از محاسبه دیتای temp به اون انتقال میدادم برای ساخت جدولی که پارتیشن بندی شده باشه کافیه ته اسکریپت ساخت جدول خط زیر اضافه کنیم 

Create Table <name> (…) ON psIntervalRange (نام ستونی که می خواهیم روی اون پارتیشن کنیم)

همچنین این کار برای ساخت ایندکس جدول هم انجام می دهیم

Create NONCLUSTERED INDEX <name> (…) ON psIntervalFVIndexRange (نام ستونی که می خواهیم روی اون پارتیشن کنیم)

جداول ما آماده ست laugh

خوب حالا میریم سراغ استفاده از این پارتیشن بندی

IF NOT EXISTS (SELECT rv.value FROM sys.partitions p JOIN sys.indexes i
ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions f ON f.function_id = ps.function_id
LEFT JOIN sys.partition_range_values rv
ON f.function_id = rv.function_id
AND p.partition_number = rv.boundary_id
WHERE i.index_id < 2 AND OBJECT_NAME(i.object_id)='اسم جدول خودتون' AND rv.VALUE=@IntervalId)
BEGIN
    ALTER PARTITION SCHEME psIntervalRange   NEXT USED [PRIMARY] 
    ALTER PARTITION SCHEME psIntervalFVIndexRange   NEXT USED [ExtendOne]
    ALTER PARTITION FUNCTION [pfIntervalRange]() SPLIT RANGE(@IntervalId)
END

من یه اسکریپت نوشتم که اول چک می کنیم توی پارتیشن مورد نظرمون دوره ای که می خوایم براش دیتا بزنیم وجود داره یا نه اگه وجود نداشت اون دوره به پارتیشن های قبلی اضافه می کنیم با اینکار دیگه نیازی نیست موقع ساخت پارتیشن حتما همه پارتیشن ها اضافه کنیم، در گام بعد سراغ استفاده از این پارتیشن بندی میریم

--حذف اطلاعات جدول اصلی برای این دوره
TRUNCATE TABLE [dbo].[اسم جدول تون]  WITH (PARTITIONS ($PARTITION.pfIntervalRange(@IntervalId)));

--انتقال اطلاعات این دوره از تمپ به جدول اصلی
ALTER TABLE [temp].[اسم جدول تون]
SWITCH PARTITION $PARTITION.pfIntervalRange(@IntervalId) 
TO  [dbo].[اسم جدول تون] PARTITION $PARTITION.pfIntervalRange(@IntervalId);

همون طور که توی کد توضیح دادم خط اول تمامی اطلاعات اون دوره از جدول اصلی در کسری از ثانیه حذف می کنه و در قسمت بعدی اطلاعات جدول موقت برای دوره مورد نظر ما به جدول اصلی انتقال میده بدون اینکه زمانی از شما گرفته باشه و جالبه بدونید این انتقال با حذف دیتا از جدول موقت هم همراه هست و دیگه نیازی به خالی کردن جدول موقت نداریم مقدار IntervalId  هم شناسه دوره مورد نظرمون برای تایید مالی هست.

در پایان اینو بگم که sql server می تونه تا 15 هزار پارتیشن ساپورت کنه یعنی اگه برای تمام روزهای سال دیتا داشته باشیم تا 41 سال دیتا رو می تونیم به این روش پارتیشن بندی کنیم که این فوق العاده ست جهت اطلاعات بیشتر به لینک Partitioned Tables and Indexes مراجعه کنید.



نظرات

Drag to order
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
نظر تایید شده ای برای این مطلب وجود ندارد .