کلید اصلی محاسباتی بر اساس فیلدهای سایر جداول

[m_omrani]

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

فرض کنید یه جدول Product و یک جدول Photo داشته باشیم. کلید اصلی جدول Product فیلدی به نام ProductID از جنس int و identity باشه. هر محصول تعدادی عکس داره. در حالت عادی باید یک کلید خارجی در جدول Photo برای Product درست کنیم. اما این کار رو نمی کنیم. به جای این کار کلید اصلی جدول Photo رو از طریق یه الگوریتم به دست میاریم. الگوریتم رو به صورت یه UDF به صورت زیر پیاده سازی می کنیم و موقع درج عکس جدید برای یه محصول، کُدش رو با استفاده از این UDF به دست میاریم و استفاده می کنیم. جزئیات الگوریتم خیلی مهم نیست. می شه الگوریتم های دیگه ای هم ابداع کرد.


create udf dbo.GetNextPhotoID(@ProductID int) returns int
as
begin
declare @x1 int
declare @x2 int
declare @m int
declare @result int

set @x1 = @ProductID * 100
set @x2 = (@ProductID + 1) * 100

select @m = max(PhotoID) from Photo where PhotoID >= @x1 and PhotoID < @x2
set @m = isnull(@m, 0)

if (@m = 0)
begin
set @result = @x1
break;
end
else if (@m < @x2 - 1)
begin
set @result = @m + 1
break;
end
else
raise error -- the product can not have more photos

return @result
end


بدین ترتیب به ازای کُد محصولی برابر 25 کُد عکس‌های مرتبط با اون برابر 2500، 2501، 2502، ...، 2599 خواهدبود. مشخصه که هر محصول فقط 100 تا عکس مرتبط می تونه داشته باشه که البته به اندازه کافی هم زیاد هست.

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

create proc dbo.GetPhotosOf(@ProductID int) as
begin
declare @x1 int
declare @x2 int

set @x1 = @ProductID * 100
set @x2 = (@ProductID + 1) * 100

select * from Photo where PhotoID >= @x1 and PhotoID < @x2
end

ممکنه بگید حالا این کارا چه مرضیه! ضمن این که خودت باید مسوولیت integrity رکوردهای عکس رو بر عهده بگیری. یه کلید خارجی بگیر خودت رو خلاص کن. ولی این ایده شاید دو تا مزیت داشته باشه:

اول این که تمام عکس های مرتبط با یه محصول از نظر فیزیکی روی دیسک دقیقا پشت سر هم قرار می گیرن. چون رکوردها بر حسب کلید اصلی جدول عکس مرتبط هستن. به همین دلیل بازیابی عکس های یک محصول زودتر انجام می شه (البته به نظرم به طور مشابه، درج عکس برای محصول هم کُندتر می شه).

دوم این که به هر حال جدول Photo یه فیلد کمتر داره. اگه یک میلیارد محصول داشته باشیم (فرض کنید پلیز!)، حداقل 4 گیگا بایت به دلیل همین فیلد ناقابل توی حجم دیتابیس صرفه جویی می شه. البته این استنباط منه. نمی دونم ارزیابی ام چقدر درست باشه. حالا تصور کنید توی چند جدول دیگه هم بتونیم این تکنیک رو به کار ببریم.

anyway!

سوال اینه که به نظر شما اصلا این ایده به دردبخور هست یا ایده‌ی خنده دار و حماقت آمیزیه.

از توجه شما سپاسگزارم.

[محمد سلیم آبادی]

ایده ابتکاری جالبیه. اما نیاز به توسعه ی بیشتری داره.

ضعف الگوریتم:
فرض کنید 95 تصویر برای یک محصول در جدول ذخیره کردین حالا رئیس تصمیم میگیره عکس های ذخیره شده رو بازنگری کنه و اونهایی رو که نمی پسنده حذف کنه. خب طبیعی که ممکن تصاویر از ردیف های میانی حذف بشن در نتیجه یک فضای هرز (gap) یا شکاف بین داده ها بوجود میاد. حالا اگر جناب رئیس 5 تصویر رو در خوش بینانه ترین حالت حذف کنه الگوریتم شما با اینکه به اندازه 10 تصویر فضا وجود داره تنها اجازه درج 5 تصویر رو میده (در وضعیتی که کدهای حذف شده از میانه بودن نه از آخر). در نتیجه شما هنگامی که میخواهید تصویر جدید برای یک محصول رو درج کنید بجای اینکه Max را بدست بیارین باید اولین gap در بازه مورد نظر بدست بیاد.

عدم وجود جامعیت داده ها در این مدل:
و همینطور نیاز به trigger دارید تا صحت داده ها رو تضمین کنید (چه هنگام درج و چه هنگام ویرایش). الان در جدول تصاویر به سادگی داده هایی درج میشن که خارج از range مورد نظر شماست. مثلا میتونید مقدار 545 رو در جدول ثبت کنید بدون اینکه در جدول محصولات کدمحصولی با مقدار 5 وجود داشته باشه.

وضع مالی رئیس خیلی خوبه!
شما به یک میلیارد سطر در جدول تصاویر اشاره کردین. خب فرض کنید میانگین حجم هر تصویری 100 KB هست. یک میلیارد ضرب در 100 کیلوبایت چند گیگا بایت میشه؟ چیزی حدود 100000 گیگا بایت. اگر server دارای hard disk هایی به حجم 1 ترابایت باشه نیاز به 100 هارد دیسک داره. پس برای همچین شرکتی 4 گیگابایت ناقابل جای صحبتی نداره.

سربار (overhead)
برای حفظ یکپارچگی داده ها همانطور که اشاره شد نیاز به trigger دارید که خود دارای سربار هست.
برای افزودن و ویرایش اطلاعات نیاز به محاسبات گوناگونی دارید که این نیز دارای سربار هست.
برای حذف یک محصولی که میخواهید به تبع آن تصاویرش هم پاک بشه نمیشه از خاصیت cascading استفاده نمود.

نتیجه گیری
لطفا با اضافه کردن یک ستون به نام productID به جدول تصاویر و ایجاد یک شاخص روی آن در کنار یک قید کلید خارجی، به تمام پریشانی ها و سردرگمی ها پایان دهید.

[m_omrani]

خیلی ممنون.

اگه بخوایم به جای تریگر خودمون از تراکنش استفاده کنیم چطور؟ یعنی دسترسی عملیات CRUD روی جداول رو از یوزر دیتابیس بگیریم و فقط اجازه‌ی SELECT جداول رو بهش بدیم. بعد برای دستکاری اطلاعات، استورپراسیجر بنویسیم و اجازه‌ی EXECUTE اونها رو به یوزر دیتابیس بدیم.

برای رفع مشکل گپ افتادن بین کُدهای عکس‌ها هم شاید بشه الگوریتم رو تغییر داد یا از راه‌های ابتکاری دیگه استفاده کرد. بستگی به این داره که هر محصول چند تا عکس داره و آیا حذف عکس به ندرت پیش بیاد یا نه. با وجودی که برای هر محصول می‌شه 100 تا عکس ثبت کرد، اما اگه به طور مینانگین هر محصول فقط 20 تا عکس داشته باشه که سالی یه بار هم حذف نمی‌شن به نظرم باز هم می‌شه از این الگوریتم استفاده کرد.

در نهایت جدول محصول و عکس تنها یک نمونه است. می‌شه هر دو موجودیت دیگه‌ای رو در نظر گرفت. لذا بحث حجم عکس و 1000 هارد 1 ترا بایتی پیش نمیاد. فرض کنید در یک شبکه اجتماعی برای جدول کاربر و مثلا سابقه تحصیل کاربر از این روش استفاده کنیم. به جای ضریب 100 هم از صریب 10 استفاده کنیم. یعنی هر نفر حداکثر 10 سابقه تحصیل می تونه داشته باشه.

الگوریتم استفاده از ضریب 100 هم یه نمونه است. شاید بشه با ابداع یک الگوریتم دیگه روی این ایده باز هم کار کرد. مهم اینه که آیا مزایای این همه دردسر واقعا می صرفه ازش استفاده کنیم یا نه.

[محمد سلیم آبادی]

بله. اگر شما مدیرت permission ها را به خوبی انجام بدین میتونید به جای trigger از stored Procedure نیز استفاده کنید و فقط اعمال مذکور را از طریق اجرای SP برای کاربران مهیا کنید. در این حالت دیگه کاربر نمیتونه مستقیما داده ها را بروزرسانی (delete/update/insert) کنه تا نیاز باشه برای اعمال صحت داده ها trigger نوشت.

الگوریتم مورد نظر اگر بتونه مشکل گپ رو حل کنه (که کار خاصی نداره) باز یک مشکل دیگه ای داره. اگر شما قرار باشه بصورت دسته ای داده ها را در جدول درج کنید آنگاه باید بتونید برای یک گروه از داده ها کد تولید کنید. به عبارتی فرض کنید 20 تصویر با یک عمل insert قرار هست درج بشه. البته این موضوع بستگی داره به اینکه آیا شما در رویه ی ذخیره شده خود از پارامتر های ورودی از نوع TVP استفاده می کنید یا خیر.

[m_omrani]

خیلی متوجه نشدم. مگه درج یک گروه از داده‌ها رو نمی‌شه با یه حلقه انجام داد؟

فرض کنید یه پراسیجر به اسم dbo.AddSinglePhoto داشته باشیم که یه عکس رو با استفاده از تابع dbo.GetNextPhotoID درج می‌کنه. سپس یه پراسیجر دیگه بنویسیم به اسم dbo.AddMultiplePhotos که اطلاعات یک گروه از عکس‌ها رو به صورت جدول می گیره و در یک حلقه، هر عکس رو با adbo.AddSinglePhoto به دیتابیس اضافه می کنه. کل عملیات رو هم به صورت تراکنش انجام می‌دیم.

من فکر می‌کنم مشکل یا نقطه ضعف جدی ایده‌ی من به تغییرات شدید کلید اصلی جدول Photo مربوط می شه. چون برای این که دیتابیس همیشه بتونه ترتیب فیزیکی عکس‌ها رو حفظ کنه، مرتب باید اونها رو جا به جا کنه. به همین دلیل در مقیاس میلیارد رکورد، این جا به جا کردن علاوه بر کُند کردن عمل درج، باعث قطعه قطعه شدن دیتابیس و دیسک می شه و کارایی کل دیتابیس رو پایین میاره. نمی‌دونم استنباطم چقدر درسته.

[محمد سلیم آبادی]

عدم متوجه شدن شما. بر میگرده به متفاوت بودن دیدگاه هر یک از ما.
من متصور بودم که تنها با یک عمل درج تمام داده ها در جدول درج بشن. که در این حالت نیاز هست یکجا تمام کدها تولید بشن و بعد از بررسی کدها در صورت صحت همگی یکجا در جدول درج بشن.
در حالی که شما از یک ترفند دیگه برای این منظور استفاده کردین. درج تک تکی و در صورت عدم موفقیت درج یکی از آنها لغو شدن تمام به هنگام سازی های موجود در تراکنش.
قطعه قطعه شدن شاخص خوشه ای شما با کمک بازسازی کردن برطرف میشه.
برمیگردیم به جدول Photos و تکنیکی که شما برای ذخیره سازی اطلاعات استفاده کردین. شما اشاره کردین که روش شما دارای 2 مزیت هست یکی فضا کمتر اشغال میشه و دومی اینکه سرعت جستجو افزایش پیدا میکنه.
در حالی که اگر از کلید طبیعی برای جدول استفاده بشه دقیقا همین اتفاق خواهد افتاد و در نتیجه نیازی به پیچیده تر کردن موضوع نیست. در جدول Photos نباید اجازه درج دو یا چند تصویر یکسان برای یک محصول را بدهیم در نتیجه می بایست ترکیب دو ستون کد محصول و تصویر را منحصربفرد بگیریم. برای این منظور یک کلید اولیه ترکیبی روی این دو ستون ایجاد میکنیم با این کار هم محصولات بصورت مرتب شده در جدول درج میشن و هم اینکه ستون مصنوعی identity ای وجود نداره که بخواد 4 بایت فضا اشغال کنه. اما همانطور که خود اشاره کردین در این حالت ممکن مشکل قطعه قطعه شدن حافظه رخ بده هنگام درج که درمانش هم rebuild کردنش هست.
البته این روش هم ممکنه از لحاظ عملکرد و مباحث دیگه نقاط ضعف و معایبی داشته باشه. لذا تنها، هدفم از مطرح کردن این روش نشان دادن یک شیوه مساوی با شیوه شما بود.
گرچه در روش نامبرده شده دیگه مشکل جامعیت داده وجود نداره. که این خودش یک مزیت محسوب میشه.
نکته ی دیگری که باقی می ماند راجب جامعیت داده ها در مدل شماست. شما علاوه بر اینکه باید کدهای تولید شده را جهت وجود داشتن آن در جدول محصولات بررسی کنید تا تنها برای محصولاتی کد تولید کنید که وجود خارجی داند. باید همچنین بررسی کنید تا برای یک محصول تصاویر تکراری در جدول درج نشه.

[m_omrani]

به نظرم درج دسته جمعی رکورد هم با ایده‌ای که مطرح کردم شدنیه.

کافیه‌ی کُد آخرین عکس رو پیدا کنیم و بر اساس اون این بار به جای تولید یک کُد، چندین کُد به تعداد رکوردهای مورد درج تولید کنیم. مثلاً اگه کُد آخرین عکس برای محصولی با کُد 25 برابر 2510 باشه، برای درج دسته جمعی 10 تا عکس دیگه، می‌تونیم کُدهای اونها رو به ترتیب 2511، 2512، ... و 2520 بگیریم و بعد همه رو یکباره درج کنیم.

در مورد سایر نکات مطرح شده جسارتا فکر کنم یک مشکل کوچکی وجود داره یا من درست متوجه نشدم.

شما گفتید ترکیب کُد محصول و تصویر رو کلید اصلی جدول Photo بگیریم. اما نگفتید کُد عکس رو از کجا آوردید. من که از ابتدای این بحث یادم نمیاد به فیلدی به اسم «کُد عکس» اشاره کرده باشم. اساسا ایده‌ای که مطرح کردم از یک دیدگاه دقیقا برای پرهیز از همین ترکیب کُد محصول (کلید خارجی) و کُد فرضی عکس بود. یعنی از اضافه کردن ستون «کُد محصول» به جدول عکس و افزایش حجم ناشی از اون خودداری کنیم و به جاش، کلید اصلی عکس رو طوری بگیریم که به طور محاسباتی، کُد محصول رو هم داخل خودش داشته باشه.

این که گفتید «با این کار محصولات هم به صورت مرتب شده درج می‌شن» رو متوجه نشدم. ما که با جدول محصول و ستون identity اش مشکلی نداشتیم. مشکلمون جدول عکس بود.

ایده‌ی rebuild کردن ایندکس هم به نظر من در سناریوی چند میلیاردی بسیار هزینه‌بره و اصلا به صرفه نیست. همون طور که گفتم به نظرم مشکل بزرگ این ایده، قطعه قطعه شدن دیسکه که به سرعت رخ می ده و اگه برای حلش، بخوایم ایندکس رو rebuild کنیم باید این کار رو در بازه‌های زمانی کوتاهی انجام بدیم که مقرون به صرفه نیست.

در مورد جلوگیری از درج عکس تکراری به نظرم الگوریتم مزبور خودش این مساله رو تضمین می کنه. چون به سبکی شبیه identity کار می کنه. کُد آخرین عکس رو در میاره، یکی بهش اضافه می کنه.

ارادتمند