باستخدام المؤشرات ، يمكنك استلام دفعة من قاعدة البيانات ومعالجة كمية كبيرة من البيانات دون إضاعة ذاكرة التطبيق. أنا متأكد من أن كل مطور ويب واجه مهمة مماثلة مرة واحدة على الأقل ، وليس مرة واحدة فقط أمامي. سوف أخبرك في هذه المقالة بالمهام المهمة التي يمكن أن تكون مؤشرات مفيدة بها ، وسأقدم لك تعليمات برمجية جاهزة للعمل معهم من PHP + Doctrine باستخدام مثال PostrgeSQL.
المشكلة
دعونا نتخيل أن لدينا مشروع PHP ، كبير وثقيل. بالتأكيد ، لقد كتب بمساعدة بعض الأطر. على سبيل المثال ، Symfony. كما أنه يستخدم قاعدة بيانات ، على سبيل المثال ، PostgreSQL ، وقاعدة البيانات تحتوي على لوحة تضم 2،000،000 سجل تحتوي على معلومات حول الطلبات. والمشروع نفسه هو واجهة لهذه الطلبات ، والتي يمكن عرضها وتصفيتها. واسمحوا لي أن أقول ، هذا في حالة جيدة.
الآن طُلب منا (لم تتم مطالبتك بعد؟ سيُطلب منهم بالتأكيد) تحميل نتيجة تصفية الطلبات في ملف Excel. دعنا نضغط زرًا مع أيقونة جدول ، والتي سوف تنثر ملفًا مع أوامر للمستخدم.
كيف يتم تحديد ذلك عادة ، ولماذا هو سيء؟
كيف يمكن للمبرمج الذي لم يواجه هذه المهمة حتى الآن؟ إنه يجعل SELECT في قاعدة البيانات ، ويقرأ نتائج الاستعلام ، ويقوم بتحويل الاستجابة إلى ملف Excel ويعطيها لمتصفح المستخدم. المهمة تعمل ، يتم الانتهاء من الاختبار ، ولكن المشاكل تبدأ في الإنتاج.
بالتأكيد ، لقد وضعنا حدًا لذاكرة PHP في بعض المعقول (يمكن القول) 1 غيغابايت لكل عملية ، وبمجرد أن هذه الخطوط 2 مليون لم تعد صالحة في هذا جيجابايت ، فواصل كل شيء. تعطل PHP بسبب خطأ "نفاد الذاكرة" ، ويشكو المستخدمون من عدم تحميل الملف. يحدث هذا لأننا اخترنا طريقة ساذجة إلى حد ما لإلغاء تحميل البيانات من قاعدة البيانات - يتم نقلها جميعًا من ذاكرة قاعدة البيانات (والقرص الموجود تحتها) إلى ذاكرة الوصول العشوائي الخاصة بعملية PHP ، ثم تتم معالجتها وتحميلها إلى المستعرض.
حتى يتم وضع البيانات دائمًا في الذاكرة ، يلزمك أخذها من قاعدة البيانات على شكل أجزاء. على سبيل المثال ، تمت قراءة 10000 سجل ومعالجتها وكتابتها في ملف ، ومرات عديدة.
حسنا ، يعتقد المبرمج الذي التقى مهمتنا لأول مرة. بعد ذلك ، سأقوم بعمل حلقة وفرغ نتائج الاستعلام إلى أجزاء ، مما يشير إلى LIMIT و OFFSET. إنه يعمل ، لكنه عملية مكلفة للغاية لقاعدة البيانات ، وبالتالي فإن تحميل التقرير لا يبدأ في 30 ثانية ، ولكن 30 دقيقة (ليس سيئًا جدًا!). بالمناسبة ، إذا لم يكن هناك شيء في عقل مبرمج ، بغض النظر عن OFFSET في هذه اللحظة ، فلا يزال هناك العديد من الطرق لتحقيق ذلك دون اغتصاب قاعدة البيانات.
في الوقت نفسه ، تتمتع قاعدة البيانات نفسها بقدرة مضمنة على مؤشر ترابط قراءة البيانات منه - المؤشرات.
المؤشرات
المؤشر هو مؤشر إلى سطر في نتائج استعلام يعيش في قاعدة البيانات. عند استخدامها ، لا يمكننا إجراء SELECT في وضع تنزيل البيانات الفوري ، ولكننا نفتح المؤشر باستخدام هذا التحديد. بعد ذلك ، نبدأ في تلقي تدفق البيانات من قاعدة البيانات أثناء تحرك المؤشر للأمام. هذا يعطينا نفس النتيجة: نقرأ البيانات جزئيًا ، لكن قاعدة البيانات لا تقوم بنفس المهمة لإيجاد السطر الذي يجب أن تبدأ به ، كما هو الحال مع OFFSET.
يتم فتح المؤشر فقط داخل المعاملة ويستمر إلى أن تصبح المعاملة حية (هناك استثناء ، انظر WITH HOLD ). هذا يعني أننا إذا قرأنا ببطء الكثير من البيانات من قاعدة البيانات ، فسنحصل على معاملة طويلة. هذا أمر سيء في بعض الأحيان ، تحتاج إلى فهم وقبول هذا الخطر.
المؤشرات في العقيدة
دعونا نحاول تنفيذ العمل مع المؤشرات في العقيدة. أولاً ، كيف يبدو طلب فتح المؤشر؟
BEGIN; DECLARE mycursor1 CURSOR FOR ( SELECT * FROM huge_table );
DECLARE ينشئ ويفتح المؤشر لاستعلام SELECT المحدد. بعد إنشاء المؤشر ، يمكنك البدء في قراءة البيانات منه:
FETCH FORWARD 10000 FROM mycursor1; < 10 000 > FETCH FORWARD 10000 FROM mycursor1; < 10 000 > ...
وهكذا ، إلى أن تعيد FETCH قائمة فارغة. هذا سوف يعني أنهم انتقلوا إلى النهاية.
COMMIT;
نضع الخطوط العريضة لفئة متوافقة مع العقيدة التي ستختتم العمل مع المؤشر. ولحل 80٪ من المشكلة في 20٪ من الوقت ، ستعمل فقط مع استعلامات Native. لذلك دعونا نسميها ، PgSqlNativeQueryCursor.
المصمم:
public function __construct(NativeQuery $query) { $this->query = $query; $this->connection = $query->getEntityManager()->getConnection(); $this->cursorName = uniqid('cursor_'); assert($this->connection->getDriver() instanceof PDOPgSqlDriver); }
أنا هنا توليد اسم للمؤشر في المستقبل.
نظرًا لأن الفصل لديه كود SQL خاص بـ PostgreSQL في الفصل ، فمن الأفضل التحقق من أن سائقنا هو PG.
من الفصل نحتاج إلى ثلاثة أشياء:
- أن تكون قادرة على فتح المؤشر.
- أن تكون قادرة على إرجاع البيانات إلينا.
- أن تكون قادرة على إغلاق المؤشر.
افتح المؤشر:
public function openCursor() { if ($this->connection->getTransactionNestingLevel() === 0) { throw new \BadMethodCallException('Cursor must be used inside a transaction'); } $query = clone $this->query; $query->setSQL(sprintf( 'DECLARE %s CURSOR FOR (%s)', $this->connection->quoteIdentifier($this->cursorName), $this->query->getSQL() )); $query->execute($this->query->getParameters()); $this->isOpen = true; }
كما قلت ، المؤشرات مفتوحة في الصفقة. لذلك ، أتحقق هنا من أننا لم ننسَ إجراء مكالمة لهذه الطريقة داخل معاملة مفتوحة بالفعل. (الحمد لله ، لقد مر الوقت الذي سيتم فيه سحب إجراء معاملة هنا!)
لتبسيط مهمة إنشاء وتهيئة NativeQuery جديدة ، أنا مجرد استنساخ الذي تم تغذيته في المنشئ ولفه في DECLARE ... CURSOR FOR (here_original_query). أنا أحملها.
لنجعل طريقة getFetchQuery. لن يُرجع البيانات ، ولكن طلبًا آخر يمكن استخدامه كما تريد للحصول على البيانات المطلوبة في دفعات معينة. هذا يعطي رمز الدعوة المزيد من الحرية.
public function getFetchQuery(int $count = 1): NativeQuery { $query = clone $this->query; $query->setParameters([]); $query->setSQL(sprintf( 'FETCH FORWARD %d FROM %s', $count, $this->connection->quoteIdentifier($this->cursorName) )); return $query; }
تحتوي الطريقة على معلمة واحدة - هذا هو حجم الحزمة ، والتي ستصبح جزءًا من الطلب الذي يتم إرجاعه بواسطة هذه الطريقة. أطبق نفس الخدعة باستخدام استنساخ الاستعلام ، والكتابة فوق المعلمات فيه واستبدال SQL بـ FETCH ... FROM ...؛
لكي لا تنسَ فتح المؤشر قبل أول مكالمة إلى getFetchQuery () (فجأة لن أحصل على قسط كافٍ من النوم) ، سأقوم بفتح ضمني منه مباشرةً في طريقة getFetchQuery ():
public function getFetchQuery(int $count = 1): NativeQuery { if (!$this->isOpen) { $this->openCursor(); } …
وستكون طريقة openCursor () نفسها خاصة. لا أرى حالات على الإطلاق عندما أحتاج إلى الاتصال بها صراحة.
داخل getFetchQuery () ، قمت بترميز FORWARD الثابت لتحريك المؤشر إلى الأمام لعدد معين من الخطوط. لكن أوضاع استدعاء جلب مختلفة كثيرة. دعنا نضيفهم أيضا؟
const DIRECTION_NEXT = 'NEXT'; const DIRECTION_PRIOR = 'PRIOR'; const DIRECTION_FIRST = 'FIRST'; const DIRECTION_LAST = 'LAST'; const DIRECTION_ABSOLUTE = 'ABSOLUTE';
يقبل نصفهم عدد الأسطر في المعلمة ، بينما لا يقبل النصف الآخر. إليكم ما حصلت عليه:
public function getFetchQuery(int $count = 1, string $direction = self::DIRECTION_FORWARD): NativeQuery { if (!$this->isOpen) { $this->openCursor(); } $query = clone $this->query; $query->setParameters([]); if ( $direction == self::DIRECTION_ABSOLUTE || $direction == self::DIRECTION_RELATIVE || $direction == self::DIRECTION_FORWARD || $direction == self::DIRECTION_BACKWARD ) { $query->setSQL(sprintf( 'FETCH %s %d FROM %s', $direction, $count, $this->connection->quoteIdentifier($this->cursorName) )); } else { $query->setSQL(sprintf( 'FETCH %s FROM %s', $direction, $this->connection->quoteIdentifier($this->cursorName) )); } return $query; }
أغلق المؤشر مع CLOSE ، وليس من الضروري الانتظار حتى تكتمل المعاملة:
public function close() { if (!$this->isOpen) { return; } $this->connection->exec('CLOSE ' . $this->connection->quoteIdentifier($this->cursorName)); $this->isOpen = false; }
المدمر:
public function __destruct() { if ($this->isOpen) { $this->close(); } }
هنا هو الفصل بأكمله . دعونا نحاول في العمل؟
أنا فتح بعض الكاتب الشرطي في بعض XLSX الشرطي.
$writer->openToFile($targetFile);
هنا أحصل على NativeQuery لسحب قائمة الطلبات من قاعدة البيانات.
$query = $this->getOrdersRepository($em) ->getOrdersFiltered($dateFrom, $dateTo, $filters);
بناءً على هذا الاستعلام ، أعلن المؤشر.
$cursor = new PgSqlNativeQueryCursor($query);
وبالنسبة له ، أحصل على طلب لتلقي البيانات على دفعات من 10000 سطر.
$fetchQuery = $cursor->getFetchQuery(10000);
أكرر حتى أحصل على نتيجة فارغة. في كل تكرار ، أقوم بإجراء FETCH ، ومعالجة النتيجة ، والكتابة إلى ملف.
do { $result = $fetchQuery->getArrayResult(); foreach ($result as $row) { $writer->addRow($this->toXlsxRow($row)); } } while ($result);
أغلق المؤشر والكاتب.
$cursor->close(); $writer->close();
أكتب الملف إلى القرص في دليل للملفات المؤقتة ، وبعد اكتمال التسجيل ، أرسله إلى المتصفح لتجنب مرة أخرى التخزين المؤقت في الذاكرة.
تقرير جاهز! استخدمنا كمية ثابتة من الذاكرة من PHP لمعالجة جميع البيانات ولم نعذب قاعدة البيانات مع سلسلة من الاستفسارات الثقيلة. استغرق التفريغ نفسه وقتًا أكثر قليلاً من القاعدة المطلوبة للوفاء بالطلب.
تعرف على ما إذا كانت هناك أماكن في مشاريعك يمكنها تسريع / حفظ الذاكرة باستخدام المؤشر؟