מתכונים לשאילתות SQL חולות

לפני מספר חודשים הכרזנו explain.tensor.ru - ציבורי שירות לניתוח והצגה של תוכניות שאילתות ל PostgreSQL.

השתמשת בו יותר מ-6000 פעמים מאז, אבל אחת התכונות השימושיות אולי נעלמו מעיניהם היא רמזים מבניים, שנראים בערך כך:

הקשיבו להם והבקשות שלכם "יהפכו חלקות כמשי". 🙂

אבל ברצינות, מצבים רבים שהופכים בקשה לאטית ו"גרגרנית" מבחינת משאבים, אופייניים וניתן לזהותם לפי המבנה והנתונים של התוכנית.

במקרה זה, כל מפתח אינדיבידואלי לא יצטרך לחפש בעצמו אפשרות אופטימיזציה, בהסתמך רק על הניסיון שלו - נוכל לספר לו מה קורה כאן, מה יכולה להיות הסיבה, וכן איך להמציא פתרון. וזה מה שעשינו.

בואו נסתכל מקרוב על המקרים הללו – כיצד הם מוגדרים ולאילו המלצות הם מובילים.

כדי להתעמק טוב יותר בנושא, אתה יכול קודם להאזין לבלוק המתאים מ הדיווח שלי ב-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;

[הסתכל ב-explain.tensor.ru]

אתה יכול מיד לשים לב שיותר מ-100 רשומות הופחתו על ידי האינדקס, שסודרו אז כולן, ואז נשארה היחידה.

אנחנו מתקנים:

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

[הסתכל ב-explain.tensor.ru]

אפילו על מדגם פרימיטיבי כזה - מהיר פי 8.5 ופי 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); -- отбор по конкретной паре

[הסתכל ב-explain.tensor.ru]

אנחנו מתקנים:

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

[הסתכל ב-explain.tensor.ru]

כאן הרווח קטן יותר, שכן Bitmap Heap Scan יעיל למדי בפני עצמו. אבל בכל מקרה מהיר פי 7 ופי 2.5 פחות קריאות.

מס' 3: שילוב אינדקסים (BitmapOr)

מתי עולה

הצג את 20 הבקשות הראשונות "בעצמך" או שלא הוקצו לעיבוד, עם הבקשות הבקשות העדיפות ביותר.

איך לזהות

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

המלצות

להשתמש UNION [כל] כדי לשלב שאילתות משנה עבור כל אחד מבלוק התנאי OR.

לדוגמה:

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;

[הסתכל ב-explain.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, больше и не надо

[הסתכל ב-explain.tensor.ru]

ניצלנו את העובדה שכל 20 הרשומות הדרושות התקבלו מיד בבלוק הראשון, כך שהשנייה, עם סריקת ערימת Bitmap "היקרה" יותר, אפילו לא בוצעה - כתוצאה מכך פי 22 מהר יותר, פי 44 פחות קריאות!

סיפור מפורט יותר על שיטת האופטימיזציה הזו על דוגמאות קונקרטיות ניתן לקרוא במאמרים PostgreSQL Antipatterns: JOINs ו-ORs מזיקים и PostgreSQL Antipatterns: סיפור על חידוד איטרטיבי של חיפוש לפי שם, או "אופטימיזציה קדימה ואחורה".

גרסה כללית בחירה מסומנת על ידי מספר מקשים (ולא רק עבור זוג const / NULL) נדון במאמר SQL HowTo: כתוב לולאת while ישירות בשאילתה, או "אלמנטרי תלת כיווני".

#4: קראנו יותר מדי

מתי עולה

ככלל, זה מתרחש כאשר אתה רוצה "לצרף פילטר נוסף" לבקשה קיימת.

"ואין לך אותו דבר, אבל עם כפתורי פנינים? " הסרט "יד יהלום"

לדוגמה, שינוי המשימה למעלה, הצג את 20 הבקשות ה"קריטיות" הראשונות לעיבוד, ללא קשר למטרה שלהן.

איך לזהות

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

המלצות

צור [עוד] מיוחדים אינדקס עם סעיף WHERE או לכלול שדות נוספים באינדקס.

אם תנאי הסינון הוא "סטטי" למשימות שלך - כלומר לא כולל הרחבה רשימת ערכים בעתיד - עדיף להשתמש באינדקס WHERE. סטטוסים שונים של בוליאן/נמצא מתאימים היטב לקטגוריה זו.

אם מצב הסינון יכול לקבל ערכים שונים, עדיף להרחיב את האינדקס עם השדות האלה - כמו במצב של BitmapAnd למעלה.

לדוגמה:

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;

[הסתכל ב-explain.tensor.ru]

אנחנו מתקנים:

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

[הסתכל ב-explain.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

המלצות

בצע באופן ידני באופן קבוע ואקום [מלא] או להשיג עיבוד תכוף מספיק ואקום אוטומטי על ידי כוונון עדין של הפרמטרים שלו, כולל עבור טבלה ספציפית.

ברוב המקרים, בעיות אלו נגרמות על ידי פריסת שאילתה לקויה כאשר נקראות מהיגיון עסקי, כגון אלו שנדונו ב PostgreSQL Antipatterns: נלחמים בהמוני "מתים".

אבל עלינו להבין שאפילו ואקום מלא לא תמיד יכול לעזור. במקרים כאלה, כדאי להכיר את האלגוריתם מהמאמר. DBA: כאשר ואקום עובר, אנו מנקים את השולחן באופן ידני.

#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;

[הסתכל ב-explain.tensor.ru]

נראה שהכל בסדר, אפילו מבחינת האינדקס, אבל איכשהו חשוד - עבור כל אחת מ-20 הרשומות שנקראו, היה צריך להפחית 4 עמודי נתונים, 32KB לרשומה - זה לא מודגש? כן ושם אינדקס tbl_fk_org_fk_cli_idx מוביל למחשבה.

אנחנו מתקנים:

CREATE INDEX ON tbl(fk_cli);

[הסתכל ב-explain.tensor.ru]

פתאום - פי 10 מהר יותר ופי 4 פחות לקריאה!

לדוגמאות נוספות לשימוש לא יעיל באינדקסים, עיין במאמר DBA: מצא אינדקסים חסרי תועלת.

#7: CTE × CTE

מתי עולה

לפי בקשה קלע CTE "שמן". מטבלאות שונות, ואז החליט לעשות ביניהם JOIN.

המקרה רלוונטי לגרסאות מתחת ל-v12 או בקשות עם WITH MATERIALIZED.

איך לזהות

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

המלצות

נתח את הבקשה בקפידה האם יש צורך ב-CTE כאן בכלל? אם כן, אז החל "מילון" ב-hstore/json על פי המודל המתואר ב PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN.

#8: החלפה לדיסק (כתובת זמנית)

מתי עולה

עיבוד חד פעמי (מיון או ייחוד) של מספר רב של רשומות אינו מתאים לזיכרון שהוקצה לכך.

איך לזהות

-> *
   && temp written > 0

המלצות

אם כמות הזיכרון המשמשת את הפעולה אינה עולה בהרבה על הערך המוגדר של הפרמטר work_mem, יש לתקן את זה. אתה יכול מיד בתצורה לכולם, או שאתה יכול לעבור SET [LOCAL] לבקשה/עסקה ספציפית.

לדוגמה:

SHOW work_mem;
-- "16MB"

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

[הסתכל ב-explain.tensor.ru]

אנחנו מתקנים:

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

[הסתכל ב-explain.tensor.ru]

מסיבות ברורות, אם משתמשים רק בזיכרון ולא בדיסק, השאילתה תהיה הרבה יותר מהירה. במקביל, חלק מהעומס מוסר גם מהדיסק הקשיח.

אבל אנחנו חייבים להבין שלא תמיד יעבוד גם להקצות הרבה זיכרון - זה נדוש לא מספיק לכולם.

#9: סטטיסטיקה לא רלוונטית

מתי עולה

הרבה נשפך לבסיס בבת אחת, אבל לא היה להם זמן לגרש אותו ANALYZE.

איך לזהות

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

המלצות

תוציא אותו דבר ANALYZE.

מצב זה מתואר ביתר פירוט ב PostgreSQL Antipatterns: הסטטיסטיקה היא הראש של הכל.

#10: "משהו השתבש"

מתי עולה

הייתה נעילה שהמתינה לבקשה מתחרה, או שלא היו מספיק משאבי חומרה של CPU/Hypervisor.

איך לזהות

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

המלצות

השתמש חיצוני מערכת ניטור שרת לחסימה או צריכת משאבים חריגה. כבר דיברנו על הגרסה שלנו לארגון התהליך הזה עבור מאות שרתים. כאן и כאן.

מקור: www.habr.com