ProHoster > بلوق > إدارة > وصفات لاستعلامات SQL للمرضى

وصفات لاستعلامات SQL للمرضى

قبل عدة أشهر أعلنا شرح - عام خدمة لتحليل وتصور خطط الاستعلام إلى PostgreSQL.

لقد استخدمته أكثر من 6000 مرة منذ ذلك الحين ، ولكن ربما تكون إحدى الميزات المفيدة دون أن يلاحظها أحد القرائن الهيكلية، والتي تبدو كالتالي:

وصفات لاستعلامات SQL للمرضى

استمع إليهم وستصبح طلباتك "سلسة كالحرير". 🙂

ولكن بجدية ، هناك العديد من المواقف التي تجعل الطلب بطيئًا و "شرهًا" من حيث الموارد ، نموذجية ويمكن التعرف عليها من خلال هيكل وبيانات الخطة.

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

وصفات لاستعلامات SQL للمرضى

دعونا نلقي نظرة فاحصة على هذه الحالات - كيف يتم تعريفها والتوصيات التي تؤدي إليها.

للحصول على انغماس أفضل في الموضوع ، يمكنك أولاً الاستماع إلى المجموعة المقابلة من تقريري في PGConf.Russia 2020، وبعد ذلك فقط انتقل إلى تحليل مفصل لكل مثال:

رقم 1: فهرس "الفرز السفلي"

متى يحدث

أظهر آخر فاتورة للعميل "LLC Kolokolchik".

كيفية التعرف

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

توصيات

الفهرس مستخدم توسيع مع حقول الفرز.

على سبيل المثال:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 1 -- отбор по конкретной связи
ORDER BY
  pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;

وصفات لاستعلامات SQL للمرضى
[انظر إلىشرح.tensor.ru]

يمكنك أن تلاحظ على الفور أنه تم طرح أكثر من 100 سجل بواسطة الفهرس ، ثم تم فرزها جميعًا ، ثم تم ترك السجل الوحيد.

نصلح:

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

وصفات لاستعلامات SQL للمرضى
[انظر إلىشرح.tensor.ru]

حتى في مثل هذه العينة البدائية - 8.5x أسرع و 33 ضعفًا في القراءة. سيكون التأثير أكثر وضوحًا ، وكلما زادت "الحقائق" التي تمتلكها لكل قيمة. fk.

ألاحظ أن مثل هذا الفهرس سيعمل كفهرس "بادئة" ليس أسوأ من الفهرس السابق لطلبات البحث الأخرى fk، حيث الفرز pk لم يكن ولم يكن كذلك (يمكنك قراءة المزيد حول هذا الموضوع في مقالتي حول العثور على فهارس غير فعالة). على وجه الخصوص ، سوف تقدم بشكل طبيعي دعم صريح للمفتاح الخارجي من هذا المجال.

رقم 2: تقاطع الفهرس (BitmapAnd)

متى يحدث

إظهار جميع العقود المبرمة للعميل "LLC Kolokolchik" نيابة عن "NJSC Lyutik".

كيفية التعرف

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

توصيات

خلق المؤشر المركب حسب الحقول من كلا المصدر أو قم بتوسيع أحد الحقول الموجودة من الثانية.

على سبيل المثال:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  (fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре

وصفات لاستعلامات SQL للمرضى
[انظر إلىشرح.tensor.ru]

نصلح:

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

وصفات لاستعلامات SQL للمرضى
[انظر إلىشرح.tensor.ru]

هنا يكون الكسب أقل ، نظرًا لأن Bitmap Heap Scan فعال تمامًا من تلقاء نفسه. لكن على اي حال 7x أسرع و 2.5 ضعفًا في القراءة.

رقم 3: دمج الفهارس (BitmapOr)

متى يحدث

اعرض أول 20 طلبًا "خاصًا" أو طلبًا غير مخصص للمعالجة ، مع إعطاء الأولوية للطلب.

كيفية التعرف

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

توصيات

استخدم الاتحاد [الكل] لدمج الاستعلامات الفرعية لكل من كتل الشرط أو.

على سبيل المثال:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own;

CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой

SELECT
  *
FROM
  tbl
WHERE
  fk_own = 1 OR -- свои
  fk_own IS NULL -- ... или "ничьи"
ORDER BY
  pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;

وصفات لاستعلامات SQL للمرضى
[انظر إلىشرح.tensor.ru]

نصلح:

(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own = 1 -- сначала "свои" 20
  ORDER BY
    pk
  LIMIT 20
)
UNION ALL
(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own IS NULL -- потом "ничьи" 20
  ORDER BY
    pk
  LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо

وصفات لاستعلامات SQL للمرضى
[انظر إلىشرح.tensor.ru]

لقد استفدنا من حقيقة أنه تم استلام جميع السجلات العشرين الضرورية على الفور في المجموعة الأولى ، لذلك لم يتم تنفيذ السجل الثاني ، الذي يحتوي على أكثر "تكلفة" لمسح كومة الصور النقطية - كنتيجة لذلك 22x أسرع ، قراءات أقل 44x!

قصة أكثر تفصيلاً حول طريقة التحسين هذه على أمثلة محددة يمكن قراءتها في المقالات Antipatterns PostgreSQL: المداخلات والعوامل المؤذية и PostgreSQL Antipatterns: قصة التنقيح المتكرر للبحث بالاسم ، أو "التحسين ذهابًا وإيابًا".

نسخة معممة أمر الاختيار بعدة مفاتيح (وليس فقط لزوج من const / NULL) تمت مناقشته في المقالة SQL HowTo: اكتب حلقة while مباشرة في الاستعلام ، أو "ثلاثة اتجاهات أولية".

# 4: نحن نقرأ كثيرا

متى يحدث

كقاعدة عامة ، يحدث ذلك عندما تريد "إرفاق مرشح آخر" بطلب موجود.

"وليس لديك نفس الشيء ، ولكن مع أزرار اللؤلؤ؟ » فيلم "Diamond Hand"

على سبيل المثال ، عند تعديل المهمة أعلاه ، اعرض أول 20 طلبًا "حرجًا" للمعالجة ، بغض النظر عن الغرض منها.

كيفية التعرف

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

توصيات

خلق [أكثر] تخصصا الفهرس مع عبارة WHERE أو قم بتضمين حقول إضافية في الفهرس.

إذا كان شرط التصفية "ثابت" لمهامك - هذا هو لا يشمل التوسع قائمة القيم في المستقبل - من الأفضل استخدام فهرس WHERE. تتناسب حالات منطقية / تعداد مختلفة بشكل جيد مع هذه الفئة.

إذا كانت حالة الترشيح يمكن أن تتخذ على قيم مختلفة، من الأفضل توسيع الفهرس بهذه الحقول - كما هو الحال مع Bitmap وما فوق.

على سبيل المثال:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"

CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);

SELECT
  *
FROM
  tbl
WHERE
  critical
ORDER BY
  pk
LIMIT 20;

وصفات لاستعلامات SQL للمرضى
[انظر إلىشرح.tensor.ru]

نصلح:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

وصفات لاستعلامات SQL للمرضى
[انظر إلىشرح.tensor.ru]

كما ترى ، اختفت التصفية من الخطة تمامًا وأصبح الطلب 5 مرة أسرع.

رقم 5: طاولة متفرقة

متى يحدث

محاولات مختلفة لإنشاء قائمة انتظار معالجة المهام الخاصة بك ، عندما يؤدي عدد كبير من التحديثات / عمليات حذف السجلات على الجدول إلى حالة عدد كبير من السجلات "الميتة".

كيفية التعرف

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

توصيات

القيام يدويا بانتظام فراغ [كامل] أو تحقيق معالجة متكررة بشكل كافٍ فراغ تلقائي من خلال ضبط المعلمات ، بما في ذلك لجدول محدد.

في معظم الحالات ، تحدث هذه المشكلات بسبب ضعف تخطيط الاستعلام عند استدعائها من منطق الأعمال ، مثل تلك التي تمت مناقشتها في Antipatterns PostgreSQL: قتال جحافل "الموتى".

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

# 6: القراءة من "منتصف" الفهرس

متى يحدث

يبدو أنهم قرأوا قليلاً ، وتمت فهرسة كل شيء ، ولم يقوموا بتصفية أي شخص إضافي - ولكن مع ذلك ، تمت قراءة عدد أكبر من الصفحات مما نود.

كيفية التعرف

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

توصيات

ألق نظرة فاحصة على بنية الفهرس المستخدم والحقول الرئيسية المحددة في الاستعلام - على الأرجح ، لم يتم تعيين جزء الفهرس. ستحتاج على الأرجح إلى إنشاء فهرس مشابه ، ولكن بدون حقول بادئة ، أو تعلم لتكرار قيمهم.

على سبيل المثال:

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;

وصفات لاستعلامات SQL للمرضى
[انظر إلىشرح.tensor.ru]

يبدو أن كل شيء على ما يرام ، حتى من حيث الفهرس ، ولكن مريبًا إلى حد ما - بالنسبة لكل سجل من السجلات العشرين التي تمت قراءتها ، كان لا بد من طرح 20 صفحات من البيانات ، 4 كيلوبايت لكل سجل - أليس هذا عريضًا؟ نعم واسم الفهرس tbl_fk_org_fk_cli_idx يقود إلى الفكر.

نصلح:

CREATE INDEX ON tbl(fk_cli);

وصفات لاستعلامات SQL للمرضى
[انظر إلىشرح.tensor.ru]

فجأة - 10 مرات أسرع و 4 مرات أقل للقراءة!

لمزيد من الأمثلة على الاستخدام غير الفعال للفهارس ، راجع المقالة DBA: البحث عن فهارس عديمة الفائدة.

رقم 7: CTE × CTE

متى يحدث

في الطلب سجل "الدهون" CTE من جداول مختلفة ، ثم قررت أن تفعل فيما بينها JOIN.

الحالة مناسبة للإصدارات أدناه الإصدار 12 أو الطلبات ذات WITH MATERIALIZED.

كيفية التعرف

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

توصيات

حلل الطلب بعناية هي الحاجة إلى CTE هنا على الإطلاق؟ إذا كانت الإجابة بنعم ، إذن تطبيق "القاموس" في hstore / json وفقًا للنموذج الموضح في Antipatterns PostgreSQL: ضرب قاموس الانضمام الثقيلة.

رقم 8: التبديل إلى القرص (مكتوب مؤقتًا)

متى يحدث

المعالجة لمرة واحدة (الفرز أو التفرد) لعدد كبير من السجلات لا تتناسب مع الذاكرة المخصصة لذلك.

كيفية التعرف

-> *
   && temp written > 0

توصيات

إذا كان حجم الذاكرة المستخدمة من قبل العملية لا يتجاوز بشكل كبير القيمة المحددة للمعلمة Work_mem، يجب تصحيحه. يمكنك على الفور في التكوين للجميع ، أو يمكنك من خلال SET [LOCAL] لطلب / معاملة محددة.

على سبيل المثال:

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

وصفات لاستعلامات SQL للمرضى
[انظر إلىشرح.tensor.ru]

نصلح:

SET work_mem = '128MB'; -- перед выполнением запроса

وصفات لاستعلامات SQL للمرضى
[انظر إلىشرح.tensor.ru]

لأسباب واضحة ، إذا تم استخدام الذاكرة فقط ، وليس القرص ، فسيتم تنفيذ الاستعلام بشكل أسرع. في نفس الوقت ، يتم أيضًا إزالة جزء من الحمل من محرك الأقراص الثابتة.

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

رقم 9: إحصائيات غير ذات صلة

متى يحدث

تم سكب الكثير في القاعدة دفعة واحدة ، لكن لم يكن لديهم الوقت لطردها بعيدًا ANALYZE.

كيفية التعرف

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

توصيات

تنفق نفس الشيء ANALYZE.

تم وصف هذا الموقف بمزيد من التفصيل في Antipatterns PostgreSQL: الإحصائيات هي رأس كل شيء.

# 10: "حدث خطأ ما"

متى يحدث

كان هناك قفل ينتظر طلبًا منافسًا ، أو لم يكن هناك ما يكفي من موارد أجهزة CPU / Hypervisor.

كيفية التعرف

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

توصيات

استخدام خارجي نظام مراقبة خادم للحظر أو الاستهلاك غير الطبيعي للموارد. لقد تحدثنا بالفعل عن نسختنا الخاصة بتنظيم هذه العملية لمئات من الخوادم. هنا и هنا.

وصفات لاستعلامات SQL للمرضى
وصفات لاستعلامات SQL للمرضى

المصدر: www.habr.com

إضافة تعليق