راهنمایی در مورد یک دیتابیس حجیم

[adelx61]

با سلام به شما .چند سوال در مورد یک دیتابیس حجیم داشتم :
ما در حال انجام پروزه ای هستیم که تولیدات روزانه یک شرکت بزرگ که در سطح کشور نمایندگی دارد را از طریق برنامه پی اچ پی و بانک mysql مدیریت میکند : در این بانک 4 جدول داریم که داده های انها به هم مرتبط است و برای گزارش گیری نیاز به join کردن این 4 جدول داریم . در هر یک از این جداول روزانه به طور متوسط 3300 رکورد درج می شود . یعنی در هر کدام ماهانه حدود صدهزار و سالانه حدود یک میلیون رکورد درج خواهد شد . حالا سوالات من این است :
1 - ایا بانک mysql اصلا برای این کار مناسب است ؟ با ذکر این نکته که این سیستم فقط برای ده سال اینده است و بعد از ان سیستم عوض خواهد شد . یعنی حداکثر تعداد رکوردهای هر یک از این جداول در 10 سال اینده به ده میلیون میرسد
2 – فیلدی که اکثر جستجوها بر اساس ان صورت میگیرد فیلد تاریخ است . ایا ایندکس گذاری این فیلد برای بالابردن سرعت کافی است ؟
3 – در سالهای اینده که تعداد رکورد هر جدول بالای میلیون است چه کار باید کرد که هنگام join این 4 جدول سرعت پایین نیاید . البته گزارشها روزانه،ماهانه و سالانه هستند و در واقع همه رکورد ها در join شرکت ندارند و حداکثر رکوردهای یک بازه زمانی یکساله که تعداد انها یک میلیون است از هرجدول در join می اید .
4 – برنامه این سیستم به صورت سالی نوشته میشود . یعنی کاربر در برنامه بعد از یک سال مثل سیستمهای حسابداری سال سیستم را عوض میکند و همه قسمتهای برنامه فقط اطلاعات و گزارشهای این سال را نمایش میدهد و ضمنا اطلاعات سال قبل با سال بعد اصلا مرتبط نیست ، ایا اصلا میتوان این 4 جدول را در هر سال جدید باز تعریف کرد تا اصلا با مشکل سرعت روبرو نشویم . یعنی برای هر سال 4 جدول جداگانه در نظر بگیریم .
تشکر

[مهرداد سیف زاده]

برای گزارش گیری نیاز به join کردن این 4 جدول داری

اولین چیزی که به چشم میاد بحث join‌ هست. دیتابیس mysql شدیدا روی join افت سرعت داره. البته نه تنها mysql بلکه تمامشون این طور هستن. اکثر طراحان دیتابیس اولین چیزی که در هر پروژه ای لحاظ میکنن، پیاده سازی با روشهایی بدون join هست. کمی تامل کنید و با تکیه بر تجارب، بدون join هم میشه




1 - ایا بانک mysql اصلا برای این کار مناسب است ؟ با ذکر این نکته که این سیستم فقط برای ده سال اینده است و بعد از ان سیستم عوض خواهد شد . یعنی حداکثر تعداد رکوردهای هر یک از این جداول در 10 سال اینده به ده میلیون میرسد

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



2 – فیلدی که اکثر جستجوها بر اساس ان صورت میگیرد فیلد تاریخ است . ایا ایندکس گذاری این فیلد برای بالابردن سرعت کافی است ؟

تاریخ رو unix time stamp‌ذخیره کنید. میتونه ایندکس باشه.



3 – در سالهای اینده که تعداد رکورد هر جدول بالای میلیون است چه کار باید کرد که هنگام join این 4 جدول سرعت پایین نیاید . البته گزارشها روزانه،ماهانه و سالانه هستند و در واقع همه رکورد ها در join شرکت ندارند و حداکثر رکوردهای یک بازه زمانی یکساله که تعداد انها یک میلیون است از هرجدول در join می اید .

این مورد شما پارتیشن بندی میتونه مفید باشه. البته مکانیزم mysql شاید به پای oracle نرسه توی partition. چون در اوراکل میتونید بگید در این تیبل داده‌ها از تاریخ ۳ ماه، ۳ ماه جدا بشه و partition بشه. بعد در زمان جستجو سرعت فوق العاده بالا میره. یعنی موقعی که شما روی یه تاریخی دارید جستجو میکنید، نمیره کل چند میلیون رکورد رو نمیچرخه، بلکه فورا بر حسب تاریخ میره روی پارتیشن تاریخ و فقط توی تعداد کمی رکورد جستجو رو انجام میده. همین مفهوم در mysql پیاده سازی شده. منابع زیر رو مطالعه کنید
https://www.mysql.com/why-mysql/presentations/getting-best-mysql-performance-part-4-partitioning/
http://stackoverflow.com/questions/16721772/mysql-performance-multiple-tables-vs-index-on-single-table-and-partitions



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

این مورد هم بازم میتونه partition بندی بشه. یا کلا اگر با اون دیتابیس دیگه کار ندارید، برای هر سال دیتابیس رو جدا کنید. البته اگر در زمان گزارش گیری با دیتای سال گذشته کار ندارید. یا حتی میتونید گزارش‌های مفید سال قبل رو بگیرید و ذخیره کنید و بعد برای سال جدید دیتابیس جدید بسازید. در کل در ساختار برنامه باید طوری طراحی کنید که اگر نیاز بود برای هر سال دیتابیس جدید بسازید و کاربر با انتخاب سال بتونه بین دیتابیس‌ها جا به جا بشه. البته باز هم تکرار میکنم این مورد جداسازی در زمانی مفید هست که با دیتای سال گذشته کار نداشته باشید. مثلا فروردین ماه برای تغییرات محصول نیاز به تغییرات محصول اسفند گذشته نباشه.

[adelx61]

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

[مهرداد سیف زاده]

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

[adelx61]

سپاس از راهنمایی شما استاد

[charcharkh]

بدرد ما هم خورد :تشویق:

[Unique]

آقا مهرداد پاسخ شما را دادن اما من کمی متفاوت فکر میکنم :


1 - ایا بانک mysql اصلا برای این کار مناسب است ؟ با ذکر این نکته که این سیستم فقط برای ده سال اینده است و بعد از ان سیستم عوض خواهد شد . یعنی حداکثر تعداد رکوردهای هر یک از این جداول در 10 سال اینده به ده میلیون میرسد
دوست عزیز شما MySQL را خیلی دست کم میگیرین !!! اصلا ۱۰ یا ۲۰ یا ۵۰ میلیون رکورد جزو Big Data نیست و تا جایی که من میدونم ۵۰۰ میلیون و ۱ بیلیون و این حرفا را Big Data میدونن که MySQL در صورت داشتن شرایط زیر به راحتی از پسش بر میاد :

۱- اگه شما بخواین با قهرمان اتوموبیل رانی توی فرمول ۱ شرکت کنید ولی بخواهین بهش پراید بدین قطعا سر خورده میشین !!! وقتی بحث از حجم بالای اطلاعات باشه باید سخت افزار مناسب هم در اختیار باشه. مثلا هارد های SSD ، رم زیاد و CPU خوب. شرکت های بزرگ برای تامین چنین سخت افزاری هیچ مشکلی ندارن !

۲- اگه توی همون مسابقه فرمول ۱ شما مک لارن هم بدین به راننده اما مکانیک های خبره اون را براتون بهینه نکنن باز هم قطعا شکست میخورین ! MySQL بسته به Proccess ی که باید انجام بده باید Config بشه ، در واقع شما باید تنظیمات را بر اساس سخت افزار و نوع نیاز ها بهینه کنین و این کار را فقط متخصص های شبکه و پایگاه داده میتونن انجام بدن و نیاز به آدم کار آمدش داره


2 – فیلدی که اکثر جستجوها بر اساس ان صورت میگیرد فیلد تاریخ است . ایا ایندکس گذاری این فیلد برای بالابردن سرعت کافی است ؟
Index گذاری ، نوع انتخاب Data Type ها و معماری پایگاه خیلی مهم هستند ، یک طراحی بد هم قطعا براتون دردسر ساز میشه.


در سالهای اینده که تعداد رکورد هر جدول بالای میلیون است چه کار باید کرد که هنگام join این 4 جدول سرعت پایین نیاید . البته گزارشها روزانه،ماهانه و سالانه هستند و در واقع همه رکورد ها در join شرکت ندارند و حداکثر رکوردهای یک بازه زمانی یکساله که تعداد انها یک میلیون است از هرجدول در join می اید .
ببینین ، با رعایت موارد زیر دو تا تصمیم میشه گرفت :
شما نرمال سازی و معماری پایگاه را درست انجام میدین ، نوع داده های مناسب انتخاب میکنید ، ایندکس گذاری را به درستی انجام میدین ،‌query ها را با دقت و وسواس مینویسین حالا :

۱ - کار را تکمیل و تحویل میدین و توی دوران پشتیبانی چنانچه مشکل سرعت پیش اومد با استفاده از Explain و Show Profile مشکل را پیدا و حل میکنید.
۲ - همین اول کار جداول را با Data های الکی برای ۲۰ یا ۳۰ میلیون رکورد پر میکنید و برنامتون را تست میکنید و اگه مشکلی داشت رفع میکنید.


بله فقط باید یکم برنامه نویسی کنیم و از حلقه های foreach تودرتو استفاده کنیم یعنی به نظر شما بهتره این کار را انجام دهیم به جای join
select زدن روی جدول اول و بعد بر اساس داده‌های جدول، select زدن روی جدول دوم بسیار بهینه تر از join هست
آقا من شکه شدم این را خوندم و آقا مهرداد تایید کردن و چهارچرخ عزیز هم استفاده برده !!!!!!!
آقا مهرداد اگه مثلا جدل اول ۱۰۰۰ تا رکورد بخونن (اون یک میلیون پیش کش) و بعدش بندازن توی حلقه و 1000 تا query بزنن روی جدول دوم از join بهتر و سریعتره !؟!؟!؟؟! فکر کنم من اشتباه حالیم شده ، اینطوری که پدر MySQL علنی در میاد. لطفا حتما پاسخ بدین تا آموزش بد داده نشه. فکر کنم منظورتون چیز دیگه ای بوده.

دوستان من نمیگم Join روی Performance اثر نداره اما نه تا این حد ، نسخه های جدید MySQL مخصوصا MariaDB ۱۰ بسیار بهینه عمل میکنن. اکثر دوستانی که با Join مشکل دارن یا درست ایندکس نمیکنن یا Datatype درستی انتخاب نمیکنن یا Query بد مینویسن. Join در این حد اسمش رو نبر نیست. مگه میشه بی خیال join بشی‌؟ یا ازش دوری کنی ؟! توی پروژه های چند میلیونی که واقعا خنده داره. توی چند بیلیونی هم با پارتیش بندی و ایندکس و Query مناسب و بهره بردن از View ها و خیلی تکنیک های دیگه میشه سرعت مناسب را داشت.

[us1234]

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

این روش خیلی خطرناکه ، هیچ موقع کوئری را داخل loop نیندازید ( همان طور که unique توضیح داد ) . به چند دلیل ، اول اینکه زبان برنامه نویسی mysql سی است که به بهترین نحو بهینه سازی شده و بعد کامپایل شده پس در هر شرایط عملکرد ش از php خیلی بهتره پس بهتره پروسه های سنگین توسط همان mysql انجام شود و فقط از نتایج آن در php استفاده شود ، دلیل بعدی اینکه برای هر ارتباط با mysql نیاز به سوکت زدن است که کل پروسه ارتباط و دریافت اطلاعات سرباره زیادی به سی پی یو تحمیل میکنه ، پس کلا قید این روش را بزنید و سعی کنید شسته رفته ترین نتیجه را در یک کوئری بیارید داخل کد ( بخصوص کدی مثل php که اسکریپتی است )


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

اگر بنا را بگیم بر این فرض که منظور شما از سلکت های چندگانه چیزی مثل sub-query یا چند سلکت محدود باشه و مثل بالا نخواهید پردازش را در php انجام دهید ، بازم با این قطعیت نمیشه گفت که این روش ها سریعتر از join است !
نتیجه تحقیقات من در این مورد این است که هیچ کس نظر قطعی در این خصوص ندارد و بسته به سناریو یکی از روش ها کارآمدتر از دیگری میشود ، همه متخصصان متفق القول میگویند 2 روش ( join و none-join ) را تست کنید ببیند برای کار شما کدام سریعتر است .

البته طراحی بهینه دیتابیس ، ایندکس گذاری ، نرمال بودن دیتابیس ، استفاده کوئری های بهینه ، استفاده کش و ... پارامتر های تاثیر گذار در این معقوله می باشد .

[charcharkh]

آقا من شکه شدم این را خوندم و آقا مهرداد تایید کردن و چهارچرخ عزیز هم استفاده برده !!!!!!!


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

[مهرداد سیف زاده]

آقا من شکه شدم این را خوندم و آقا مهرداد تایید کردن و چهارچرخ عزیز هم استفاده برده !!!!!!!

بنده که تایید نکردم چنین کاری کنن!!! یعنی به صراحت گفته باشم در یک ردیف چند هزارتایی foreach بزنید و select کنید؟ من فقط گفتم طوری طراحی کنید که نیاز به join‌نباشه. یا حداقل در جاهایی که جستجو زیاد داره با مکانیزمهایی این join رو کم کنید. خب معلومه که فقط تعداد select در لحظه زیاد میشه فشار چند برابری به دیتابیس میاد. حالا تحلیل‌ها و روشهای عجیب دیگه از توش در اومده من شرمنده هستم.
ولی تجربه‌ای که خودم داشتم اونم روی mysql 5.5. رکوردهایی که اضافه میشد در حد میلیونی بود. حالا برای insert یا update روزی ۲ تا ۳ بار بود. که اونم هر بار جمعا حدود یک میلیون میرسید. ولی select بصورت لحظه‌ای و چند کاربره
در ابتدا با تمام روشهای موجود اعم از index و partition تست گرفتیم که باز هم کند بود. تصمیم بعدی شک به join بود که توی وب هم حسابی به چالش کشیده بودن. حتی یادمه توی stackoverflow بنچ مارک هم گذاشته بودن. ولی خب اگر در لحظه قرار بود دو تا select بزرگ زده بشه بجای join که باز همون میشد، بلکه کندتر. و اما روشی بهتر. صاحب پروژه از اضافه‌کردن هاردهای ssd دست ما رو باز ذاشت و گفت اصلا همیشه ۱۰ برابر مورد نیاز بهش mount میکنیم. از طرف دیگر update و insert روزی سه بار و هر بار چندین هزار رکورد بود. برنامه ای به زبان c نوشتم که بار insert میومد تعداد رکوردهای جدید رو میگرفت و join‌میزد و حاصل رو در یه دیتابیس بزرگ‌تر ذخیره میکرد. این یعنی join زده شده و از قبل خروجی آماده هست و صرفا باید نشون داده بشه. حجم دیتابیس بالا میرفت ولی سرعت چند برابر بود.
البته اینم بگم این روش به این دلیل قابل استفاده بود که داده‌های مدام در تغییر نبودن. بلکه یک بار در چندین تیبل ذخیره میشد و فقط هم همون‌داده‌های گذشته استفاده میشد. اگر update هم میشد باز هم برنامه من میومد و داده‌های جدید رو بروزرسانی میکرد.
البته جدیدا mariadb ادعای performance در join داره که چون تجربه نکردم و در عمل چیزی ندیدم نمیتونم نظر بدم. حتی از خیلی از همکاران هم پرسیدم حتی برای تست هم کسی ازش استفاده نکرده.

ولی نکته آخر. ببینید نمیدونم چرا تا اسم بزرگی عده‌ای میان و فورا میگن برید روی oracle یا باید از mssql استفاده بشه. فرق oracle اینه در همون استارت اولیه بنای کار big scale هست. و برای همین باید قوانین table space، partition، index، redo و log فایل ها و هزار تا قوانین دیگری در همون ساخت دیتابیس و جدول باید پشت سر بزارید. اونم نمیتونید رعایت نکنید اصلا شما بگو من قراره category که همش ۵ تا ردیف هست رو توش ذخیره کنم. ولی mysql همین دیتابیس و جداول رو به راحتی در اختیار شما میزاره. ولی بنا رو کار بزرگ نمیزاره. کار بزرگ از mysql میخواید. اوکی قوانین طراحی بزرگ رو بهش اضافه کنید. برای indexهای مناسب رو بزارید، table space میتونید تعریف کنید، همچنین با partition داده‌ها رو کلا بر اساس فیلدی از هم جدا کنید و سرعت جستجو رو بالا ببرید
دیگه در مورد cluster، redundant و replication در mysql حرفی نمیزنم تا دوستان برن خودشون جستجو کنن و ببینن با همین mysql چه کارها که نمیشه کرد.

[Unique]

select زدن روی جدول اول و بعد بر اساس داده‌های جدول، select زدن روی جدول دوم بسیار بهینه تر از join هست
من بر مبنای این جمله گفتم چون معنی دو پهلو داره ، اگه منظورتون دو یا سه تا Select پی در پی هست میتونه درست باشه اونم در شرایط بسیار خاص و وقتی اطمینان داریم join کند عمل میکنه اما بلافاصله بعد از پست سوال کننده که حرف از foreach میزنه این را به ذهن میکشونه که query توی loop بندازیم !

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