۷۰۰ میلیون رکورد MySQL را به راحتی آب خوردن با PHP پردازش کنید

در جدیدترین پروژم باید تمام رکوردهای یک جدول دیتابیس MySQL رو پردازش میکردم که حدود ۷۰۰ میلیون رکورد داشت.

اولین نکته ای که باید بدونید این هست که وقتی یک کوئری رو اجرا میکنید سرور MySQL باید کامل کوئری رو اجرا و نتایج رو آماده کنه که این رکوردها در حافظه قرار میگیرن و بعد نتایج رو به سمت سرور PHP برگردونه حالا اگه سرور MySQL حافظه کم داشته باشه و یا نتایج خیلی زیاد باشه مثل اینجا عملا این کوئری نتیجه ای برنمیگردونه و یا خیلی خیلی طول میکشه برای حل مشکل چند تا راه حل وجود داره

  1.  استفاده از limit و offset
  2. استفاده از یک ستون که دارای ایندکس هست برای صفحه بندی

برای مورد اول مشکل این هست که هر موقع MySQL بخواد با استفاده از  limit offset صفحه بندی رو انجام بده باید کل رکوردها رو تا اون offset اسکن کنه به همین دلیل اگه این مقدار زیاد بشه عملا کوئری خیلی خیلی طول خواهد کشید

بهترین راه حل صفحه بندی با استفاده از ستونی که ایندکس داشته باشه هست مثل ستون Id، نمونه کد زیر رو ببینید:

یعد از اجرای اولین دور ، مقدار Id آخرین رکورد به عنوان Id شروع تکرار بعدی قرار داده میشه و به این صورت کل جدول پردازش میشه

توجه داشته باشید که مقدار هر صفحه رو ۱۰۰۰ گذاشتم ولی ممکنه حجم رکوردهای شما خیلی بزرگ باشه که عملا این کوئری خیلی خیلی طول بکشه نتیجه برگردونه و یا حجم رکوردهاتون خیلی کوچیک باشه بنابراین این عدد رو باید خودتون پیدا کنید تا مقداری داشته باشه که نه خیلی کم باشه و نه خیلی زیاد

تا اینجا راه حل دستتون اومد حالا یه موضوع جالب دیگه رو میخوام توضیح بدم که مطمئنم به دردتون میخوره

تفاوت fetch با fetchAll

کد زیر  رو در نظر بگیرید

وقتی کد به تابع execute میرسه رم پر میشه و دیگه به حلقه While نمیرسه که نتایج رو پردازش کنه، مشکل به این خاطر هست که PDO به صورت پیش فرض از Buffered Queries استفاده میکنه بزارید Buffered Queries و Unbuffered Queries  رو توضیح بدم.

تو Buffered Queries وقتی تابع execute اجرا میشه MySQLنتایج رو به سرور PHP میفرسته و کل رکوردهای حاصل در حافظه سرور php نگه داری میشه که تو مرحله بعد میشه با fetch یا fetchAll اونها رو پردازش کرد.

حالا اگه از Unbuffered Queries استفاده کنیم (که میگم چجوری فعالش کنید) وقتی تابع execute اجرا بشه MySQLیک Resource به PHP برمیگردونه و نه کل نتایج رو، بعد شما با استفاده از تابع fetch میتونید دونه دونه رکوردها رو پردازش کنید و یا با تابع fetchAll کلشون رو بارگذاری کنید.

اگه تعداد رکوردهایی که میگیرید زیاد هست و باعث پرشدن رم سرور PHP میشه باید از fetch استفاده کنید تا رکوردها یکجا بارگذاری نشن

برای اینکه Buffered Queries رو غیر فعال کنید باید به کد زیر رو بزنید

هر کدوم از این روش ها مزایا و معایب خودش رو داره

  1. در Buffered Queries میتونید از توابعی مثل rowCount استفاده کنید چون کل نتایج مشخص شده هست ولی تو unbuffered queries همچین چیزی وجود نداره چون تعداد نتایج مشخص نیست چون همشون بارگزاری نشدن و از معایبش هم همین که چون کل نتایج تو حافظه قرار میگیرن باعث میشه استفاده زیادی از رم بشه.
  2. Unbuffered Queries هم مزیتش بهینه سازی حافظه هست و از معایبش هم اینکه دو تا کوئری رو همزمان نمیتونید اجرا کنید به این خاطر که وقتی کوئری اول اجرا میشه و  MySQL یک ریسورس برمیگردونه حالا اگه یک کوئری دیگه اجرا کنید MySQL نمیتونه هندل کنه مگه اینکه با تابع closeCursor قبلی رو ببندید مثل کد زیر

۳. نکته آخر اینکه ممکن هست درایور MySQL سیستمتون با توجه به نسخش از این قابلیت پشتبانی نکنه که اگه اینجوری باشه خطایی میده کاملن مشخص هست.