روشی برای کار با داده های آماری در دیتابیس - شبیه سازی Materialized view در Mariadb/Mysql

مقدمه

چندی پیش در یکی از پروژه هایی که در پذیرش24 کار میکردم نیاز به یک داشبورد آماری شد که باید برای به دست آوردن آمارهای روزانه / ماهانه و سالانه با فیلتر های مختلف یک تعداد کوئری اجرا میکردم و چالش از اونجایی شروع شد که در این کوئری ها باید بین چند جدول join انجام میشد و تعداد رکوردهای اون tableها هم سر به فلک میکشید و در نتیجه زمان پاسخ کوئری حدود 2 دقیقه طول میکشید. یکی دیگه از مشکلات این بود که تعداد کاربران سیستم خیلی زیاد بودن و در زمان لانچ پروژه تعداد زیادی کوئری اجرا میشد که عملا با اون روش کار عاقلانه ای نبود. پس دست به کار شدم و روشی که در همراه‌پی استفاده استفاده کرده بودم را اونجا هم اجرا کردم البته یکم توسعه دادم. به دلیل اهمیت محرمانگی اطلاعات نمیتونم در این مقاله از tableهایی که برای پذیرش24 استفاده کردم را ذکر کنم ولی یک نمونه ساده شده از روش مورد استفاده در همراه‌پی را تشریح میکنم و سعی میکنم به روشی بیان کنم که هم ساده باشه و هم جزییات فنی دقیق بیان بشه.

تشریح مساله و چالش های اون:

ما تو یک نسخه قدیمی از همراه‌پی از دیتابیس Mysql Percona Xtradb cluster استفاده میکردیم یک فورک از Mysql هست و یکی از چالش هایی که داشتیم مربوط به آمار تراکنش های هر کاربر به تفکیک نوع تراکنش بود. البته چالش فقط برای تراکنش نبود برای تمام آمارهای سیستم بود ( مثلا آمار دستگاه هایی که عملیات پرداخت را انجام دادن به تفکیک دسکتاپ و موبایل و نوع سیستم عامل و ...). حالا شاید از خودتون بپرسید این که چالشی نداره با یک کوئری میشه تمام این اطلاعات را به دست آورد. بله درسته ولی اگر تعداد رکوردهای جدول چند میلیون باشه و تعداد کاربران سیستم هم زیاد و تعداد درخواست های آماری به صورت هم زمان زیاد باشه چی ؟ قطعا جواب منفیه و روش فعلی اصلا جوابگوی کار ما نیست.

در Postgresql یک ویژگی به نام Materialized View وجود داره که جواب کار ما هست ولی Mariadb/Mysql چنین ویژگی نداریم. البته یک پروژه تولید شده بود به نام Flexview که یک سری جداول موقت برای نگه داری اطلاعات آماری تولید میکرد و در نهایت Depricate شد.

راه حل چی بود ؟

راه حل این مساله این بود که ما یک سری جداول آماری داشته باشیم و به صورت تدریجی آمارهای اون را به‌روزرسانی کنیم و به جای کوئری روی table های اصلی، کوئری را روی table های آماری اجرا کنیم. به عنوان مثال آمار تعداد تراکنش های هر روز هریک از کاربران. روند کار هم به این شکل هست که ما کوئری های اصلی را یک بار اجرا میکنیم و نتایج اون را در این tableها نگه داری میکنیم و در یک بازه زمانی مشخص اون کوئری ها را اجرا میکنیم تا مجدد بروز رسانی اطلاعات انجام بشه. شاید از خودتون بپرسید این که باز همون کوئری ها را اجرا میکنه و بهینه نیست. در ادامه روش کار را توضیح میدم که چطور این مساله هم حل میشه.

برای درک بهتر یک مثال میزنم:

تعداد کاربر : 50000

میانگین تعداد تراکنش های هر کاربر در روز: 25

انواع تراکنش : واریز – برداشت – عودت وجه

میانگین تعداد رکوردها در روز : 000/50 * 25 = 000/250/1

میانگین تعداد رکوردها در ماه : 000/500/37

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

ماهیت مساله این هست که تعداد تراکنش روز گذشته هیچ وقت تغییر نمیکنه و پایان هر روز دیگه مشخصه که هر کاربر چقدر تراکنش داشته و تعداد تراکنش روز جاری هم به صورت افزایشی هست و هیچ وقت تعداد تراکنش ثبت شده در جدول تراکنش ها کمترنمیشه پس میشه به این نتیجه رسید که اگر ما آمار تعداد تراکنش روزانه ی هر کاربر را در یک جدول دیگه ذخیره کنیم دیگه نیازی نیست برای روزهای گذشته و حتی روز جاری تا زمان جاری، کوئری آماری را روی جدول اصلی تراکنش ها اجرا کنیم ( تصور کنید که چقدر فشار روی دیتابیس کم میشه و چقدر سرعت افزایش پیدا میکنه ? )

خب از تئوری مساله بیام سراغ مبحث عملی با ذکر مثال :

ما 3 جدول طراحی میکنیم به نام های :

• Transactions : جدول اصلی تراکنش ها
• Transactions_summary : جدول آماری تراکنش ها
• summaries_updates : توی این جدول اطلاعات آخرین بروز رسانی را نگه داری میکنیم تا آپدیت های آمار به صورت تدریجی انجام بشه.

اگر تصاویر کیفیت لازم را ندارند میتونید به کدهای داخل گیت مراجعه کنید

جدول transactions :

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

جدول transactions_summary:

در این جدول خلاصه آمارهای جدولtransactions نگه داری میشود.

نکته ی مهم که در این جدول وجود داره وجود کلید unique هست به نام tr_summary_unq که از ترکیب چند فیلد تشکیل شده.اگر دقت کنم در داده های آماری مساله ی ما برای هر کاربر در هر دوره ی زمانی تنها سه رکورد یکتا وجود داره.به عنوان مثال کاربر 1 در روز فقط میتونه 3 رکورد برای transaction_type های مختلف داشته باشه. در ادامه بیشتر توضیح میدم.

جدول summaries_updates:

در این جدول تاریخ و زمان آخرین آپدیت جدول آماری را نگه داری میکنیم. فرض کنید ما چندین جدول آماری برای موارد مختلف داریم که باید اطلاعات آخرین بروز رسانی هر کدام را نگهداری کنیم.

در ادامه باید یک سری داده ی تصادفی را در جدول transactions اضافه کنیم که بنده برای این مثال از فریم ورک flask استفاده کردم و کد اونو توی گیت هابم قرار میدم، شما هم میتونید از هر ابزاری برای این کار استفاده کنید. حتی میشه از procedure ها هم برای این کار استفاده کرد.

در کد بالا ما میخوایم 80% از رکوردها از نوع incoming

15% از نوع outgoing

5% از نوع refund باشند که با استفاده از متد choices() انجام شد.

بعد از اتمام این مرحله دیگه کامل در mariadb/mysql هستیم و وارد مبحث اصلی میشیم

ما ابتدا از نوشتن اولین function شروع میکنیم

در اصل کار این تابع این هست که از جدول summaries_updates چک میکنه آیا سابقه ای از آپدیت جدول خلاصه آمار مورد نظر ما وجود داره یا خیر. اگر وجود داره که تاریخ و ساعت آخرین آپدیت اون جدول را میده و اگر وجود نداره تاریخ 200 روز قبل را میده و یک رکورد در جدول summaries_updates ثبت میکنه . شاید بپرسید چرا 200 روز ؟ این یه عددی هست که شما میتونید تغییرش بدید.من فرض را بر این گذاشتم که دیتای جدول برای کمتر از 200 روز هست.

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

درمرحله ی بعد ما باید یک Procedure بنویسیم که یک aggregation query اجرا کنه و نتیجه ی اون را در جدول transaction_summary بریزه

3 نکته ی مهم در این Procedure وجود داره :

• اون شرطی هست که هر بار این Procedure اجرا میشه نره کل جدول را اسکن کنه و از تاریخ و ساعت آخرین آپدیتش شروع به بروز رسانی کنه ( خط 17 و 18 ). با این حرکت فقط برای اولین بار کل جدول اسکن میشه و دفعات بعد که این Procedure صدا زده میشه دیگه کل جدول درگیر نمیشه
• خط 26 تا 28 که ما چک میکنیم اگر رکورد تکراری وجود داشت ما داده های آماریمون را update میکنیم. به دلیل این که در ادامه میبینید ما این Procedure را هر 60 ثانیه صدا میزنیم و ممکنه در جدول transactions ما رکورد جدیدی مربوط به اون روز ثبت شده باشه که نیاز است آپدیت انجام بشه. شاید بپرسید داده تکراری چی هست. با ذکر یک مثال ساده توضیح میدم. برای دوره ی زمانی daily هر user_id بیشتر از یک رکورد در روز برای تراکنش هایی از نوع incoming نمیتونه داشته باشه. اگر از قبل این رکورد وجود داشته باشه یعنی باید به داده های آماری اون بروز رسانی بشه تا رکورد اضافه با آمار اشتباه ثبت نشه.
• خط 29 که تاریخ و ساعت آخرین آپدیت را در جدول summaries_updates ثبت میکنه و دفعات بعد از اونجا به بعد را آپدیت میکنه (آپدیت تدریجی)

تا اینجای کار ما function و Procedure مورد نظرمون را پیاده سازی کردیم و باید بریم سراغ مرحله آخر که بروز رسانی جدول است.

در اولین خط ما event_scheduler دیتابیس را روشن میکنیم که هر در دوره های زمانی مورد نظرمون Procedure ما را صدا بزنه. در خط های بعدی هم یک event ایجاد میکنیم که هر 60 ثانیه یک بار اجرا بشه. این عدد بستگی به نوع مساله شما ممکنه تغییر کنه و مشخص کننده بازه ی زمانی بروز رسانی آمارها است.

شاید بپرسید چرا به جای event از Trigger استفاده نکردم ؟ دلیلش این هست که Procedure ما transactional هست به همین دلیل نمیتونیم از trigger استفاده کنیم.

سوال دومی که ممکنه پیش بیاد درمورد duration های weekly/monthly/annual هست. این موارد برای تمرین بیشتر گذاشته شده تا شما updateTransactionSummary را توسعه بدید.

تا اینجای کار با روش کار آشنا شدیم. حالا باید یک تست انجام بدیم که performance کار را نمایش بده. روش تست هم به این شکل هست که ما برای آمار یکسان از دو جدول transactions و transactions_summary یک کوئری میگیریم و زمان پاسخ را باهم مقایسه میکنیم

تست روی جدول اول

و نتیجه ی کوئری

تست روی جدول دوم

و نتیجه ی کوئری

همانطور که مشاهده میکنید نتایج یکسان ولی تفاوت زمان پاسخ با 25 برابر یا به عبارتی 2500% اختلاف اون هم روی فقط 200/000 رکورد. تصور کنید تعداد رکوردهای جدول اصلی چند ده میلیون باشه، اون زمان کارایی اصلی این روش خودشو نشون میده.

از مزایای این روش :

• سرعت بالا
• فشار خیلی کمتر به سرور
• میشه پارتیشن بندی کرد بدون درگیر شدن با محدودیت های پارتیشن بندی در mysql مثل index ها و...
• قابل اجرا بر روی دیتابیس های قدیمی و یا دیتابیس های در حال استفاده که فشار کوئری های آماری زیادی را تحمل میکنن

از معایب این روش :

• تاخیر در آپدیت آمارها ( قابل تنظیم از 1 ثانیه تا روزانه و ... )
• شاید گاهی اوقات scheduler دیتابیس کار نکنه و یکم آمار قدیمی بشه و مبجور بشید یه refresh بزنید.
• شما بگید ?

آدرس گیت پروژه :

https://github.com/alifattahi/mysql-materialized/