🍠 ⤵️ 🐿️ جعل عدد POSTGRESQL أسرع (*) 🧛 🌸 🙍🏼

غالبًا ما يتم الشكوى من أن العد (*) في PostgreSQL بطيء جدًا.

في هذه المقالة ، أريد استكشاف الخيارات حتى تحصل على النتيجة في أسرع وقت ممكن.

لماذا العد (*) بطيء جدا؟

يفهم معظم الأشخاص دون مشاكل أن الطلب التالي سيتم تنفيذه ببطء:

SELECT count(*)
FROM /*   */;

هذا ، بعد كل شيء ، استعلام معقد ، ويجب أن يحسب PostgreSQL النتيجة قبل أن يعرف عدد الصفوف التي سيحتويها.

لكن الكثير من الناس يشعرون بالصدمة عندما يكتشفون أن الاستعلام التالي بطيء:

SELECT count(*) FROM large_table;

ومع ذلك ، إذا فكرت مرة أخرى ، فإن كل ما سبق صحيح: يجب أن تحسب PostgreSQL مجموعة النتائج قبل أن تتمكن من حسابها. نظرًا لعدم تخزين "عداد الصفوف السحرية" في الجدول (كما هو الحال في MyISAM MySQL) ، فإن الطريقة الوحيدة لحساب الصفوف هي النظر إليها.

لذلك ، يُجري العد (*) عادةً عمليات مسح جدول تسلسلي ، والتي يمكن أن تكون مكلفة للغاية.

هل يمثل "" في العدد () مشكلة؟

ينطبق "*" في SELECT * FROM ... على كافة الأعمدة. لذلك ، يجد العديد من الأشخاص أن استخدام count (*) غير فعال ، وبدلاً من ذلك يستخدم count (id) أو count (1) بدلاً من ذلك.

لكن "*" في العد (*) مختلفة تمامًا ، فهي تعني ببساطة "سلسلة" ولا يتمدد على الإطلاق (في الواقع ، إنها "مجمعة بدون وسيطة"). عدد الرموز (1) أو count (id) أبطأ في الواقع من count (*) ، لأنه يجب التحقق مما إذا كانت الوسيطة NULL أم لا (count ، مثل معظم المجاميع ، تتجاهل الوسائط NULL).

لذلك لن تحقق أي شيء عن طريق تجنب "*".

باستخدام الفهرس فقط المسح

من المغري مسح فهرس صغير ، وليس الجدول بأكمله ، لحساب عدد الصفوف. ومع ذلك ، هذا ليس بهذه البساطة في PostgreSQL بسبب إستراتيجية إدارة التزامن متعددة الإصدارات. تحتوي كل نسخة من الصف ("tuple") على معلومات حول لقطة قاعدة البيانات التي تكون مرئية لها . لكن هذه المعلومات (الزائدة) لا يتم تخزينها في الفهارس. لذلك ، لا يكفي عادةً حساب الإدخالات في الفهرس ، حيث يجب أن ينظر PostgreSQL إلى إدخال الجدول ("heap tuple") للتأكد من أن إدخال الفهرس مرئي.

للتخفيف من هذه المشكلة ، قدمت PostgreSQL خريطة رؤية وهيكل بيانات يخزن معلومات حول ما إذا كانت جميع الصفوف في كتلة الجدول مرئية للجميع أم لا.
إذا كانت معظم الكتل في الجدول مرئية بالكامل ، فإن عمليات المسح الضوئي للفهرس لا تتطلب زيارات متكررة لمجموعة من الصفوف لتحديد الرؤية. يُطلق على مسح الفهرس هذا "فحص الفهرس فقط" ، وغالبًا ما يكون أسرع لفحص الفهرس لحساب الصفوف.

الآن هو VACUUM الذي يدعم خريطة الرؤية ، لذا تأكد من إجراء الفراغ التلقائي بشكل كافٍ إذا كنت تريد استخدام فهرس صغير لتسريع العد (*).

باستخدام الجدول المحوري

كتبت أعلاه أن PostgreSQL لا تخزن عدد الصفوف في جدول.

يعد الحفاظ على عدد الصفوف أمرًا كبيرًا ، حيث يحدث هذا الحدث مع كل تعديل للبيانات ولا يؤتي ثماره. هذه ستكون صفقة سيئة. علاوة على ذلك ، نظرًا لأن الطلبات المختلفة يمكن أن ترى إصدارات مختلفة من السلاسل ، يجب أيضًا إصدار العداد.

لكن لا شيء يمنعك من تنفيذ مثل هذا الخط في مواجهة نفسك.
افترض أنك تريد تتبع عدد الصفوف في mytable. يمكنك القيام بذلك على النحو التالي:

START TRANSACTION;
 
CREATE TABLE mytable_count(c bigint);
 
CREATE FUNCTION mytable_count() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF TG_OP = 'INSERT' THEN
      UPDATE mytable_count SET c = c + 1;
 
      RETURN NEW;
   ELSIF TG_OP = 'DELETE' THEN
      UPDATE mytable_count SET c = c - 1;
 
      RETURN OLD;
   ELSE
      UPDATE mytable_count SET c = 0;
 
      RETURN NULL;
   END IF;
END;$$;
 
CREATE CONSTRAINT TRIGGER mytable_count_mod
   AFTER INSERT OR DELETE ON mytable
   DEFERRABLE INITIALLY DEFERRED
   FOR EACH ROW EXECUTE PROCEDURE mytable_count();
 
-- TRUNCATE triggers must be FOR EACH STATEMENT
CREATE TRIGGER mytable_count_trunc AFTER TRUNCATE ON mytable
   FOR EACH STATEMENT EXECUTE PROCEDURE mytable_count();
 
-- initialize the counter table
INSERT INTO mytable_count
   SELECT count(*) FROM mytable;
 
COMMIT;

نحن نفعل كل شيء في معاملة واحدة حتى لا يتم "فقدان" أي تغييرات في البيانات على المعاملات المتزامنة بسبب حالة الحلقة.
يتم ضمان ذلك من خلال الأمر CREATE TRIGGER الذي يغلق الجدول في وضع SHARE ROW EXCLUSIVE ، والذي يمنع جميع التغييرات المتزامنة.
الجانب السلبي هو أن جميع تعديلات البيانات المتوازية يجب أن تنتظر حتى يتم تنفيذ عدد SELECT (*).

هذا يعطينا بديلاً سريعًا جدًا للعد (*) ، ولكن على حساب إبطاء جميع تغييرات البيانات في الجدول. يضمن استخدام مشغل قيد مؤجل أن يكون قفل الصف في mytable_count قصيرًا قدر الإمكان لتحسين التزامن.

على الرغم من حقيقة أن طاولة العداد هذه يمكن أن تتلقى العديد من التحديثات ، فلا يوجد خطرلا يوجد "تضخم في الجدول" ، لأن كل هذه ستكون تحديثات "ساخنة" (تحديثات ساخنة).

أنت فعلا بحاجة إلى العد (*)

في بعض الأحيان يكون الحل الأفضل هو البحث عن بديل.

غالبًا ما يكون التقريب جيدًا بما فيه الكفاية ولا تحتاج إلى المبلغ المحدد. في هذه الحالة ، يمكنك استخدام الدرجة التي يستخدمها PostgreSQL لجدولة الاستعلامات:

SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'mytable';

يتم تحديث هذه القيمة من خلال الفراغ التلقائي والتحليل التلقائي ، لذلك يجب ألا تتجاوز أبدًا 10٪. يمكنك تقليل autovacuum_analyze_scale_factor لهذا الجدول بحيث يعمل التحليل التلقائي هناك في كثير من الأحيان.

تقدير عدد نتائج الاستعلام

حتى الآن ، نحن نستكشف كيفية تسريع عملية عد صفوف الطاولة.

ولكن في بعض الأحيان تحتاج إلى معرفة عدد الصفوف التي سترجعها عبارة SELECT دون تنفيذ الاستعلام فعليًا.

من الواضح أن الطريقة الوحيدة للحصول على إجابة دقيقة لهذا السؤال هي إكمال الطلب. ولكن إذا كانت الدرجة جيدة بما يكفي ، يمكنك استخدام مُحسِّن PostgreSQL للحصول عليها.

تستخدم الدالة البسيطة التالية SQL و EXPLAIN الديناميكيين لتمرير خطة تنفيذ الاستعلام كوسيطة ، وإرجاع تقدير لعدد الصفوف:

CREATE FUNCTION row_estimator(query text) RETURNS bigint
   LANGUAGE plpgsql AS
$$DECLARE
   plan jsonb;
BEGIN
   EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;
 
   RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;$$;

لا تستخدم هذه الوظيفة لمعالجة عبارات SQL غير الموثوق بها ، لأنها عرضة بطبيعتها لحقن SQL.

جعل عدد POSTGRESQL أسرع (*)