PostgreSQL Antipatterns: סיפור על חידוד איטרטיבי של חיפוש לפי שם, או "אופטימיזציה קדימה ואחורה"

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

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

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

0: מה המשתמש רצה?

[KDPV מכאן]

למה מתכוון משתמש בדרך כלל כשהוא מדבר על חיפוש "מהיר" לפי שם? זה כמעט אף פעם לא מתברר כחיפוש "כנה" של תת מחרוזת כמו ... LIKE '%роза%' - כי אז התוצאה כוללת לא רק 'Розалия' и 'Магазин Роза'אבל 'Гроза' ואפילו 'Дом Деда Мороза'.

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

1: הגבל את המשימה

ועוד יותר מכך, אדם לא ייכנס ספציפית 'роз магаз', כך שתצטרך לחפש כל מילה לפי קידומת. לא, למשתמש הרבה יותר קל להגיב לרמז מהיר עבור המילה האחרונה מאשר "לפרט" בכוונה את הקודמים - תראה איך כל מנוע חיפוש מטפל בזה.

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

מה עושה מפתח אבסטרקטי?

1.0: מנוע חיפוש חיצוני

הו, החיפוש קשה, אני לא רוצה לעשות כלום - בואו ניתן את זה ל-devops! תן להם לפרוס מנוע חיפוש חיצוני למסד הנתונים: Sphinx, ElasticSearch,...

אפשרות עבודה, אם כי עתירת עבודה מבחינת סנכרון ומהירות שינויים. אך לא בענייננו, שכן החיפוש מתבצע עבור כל לקוח רק במסגרת נתוני חשבונו. ולנתונים יש שונות די גבוהה - ואם המנהל נכנס עכשיו לכרטיס 'Магазин Роза', ואז אחרי 5-10 שניות הוא אולי כבר זוכר שהוא שכח לציין שם את המייל שלו ורוצה למצוא אותו ולתקן אותו.

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

1.1: תת מחרוזת "כנה".

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

בואו ננסה לקחת את הצלחת הבאה כדי לפשט את הדגם:

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

אנחנו מעלים שם 7.8 מיליון רשומות של ארגונים אמיתיים ומוספים אותם לאינדקס:

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

בואו נחפש את 10 הרשומות הראשונות לחיפוש בין-לינארי:

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

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

ובכן, כזה... 26ms, 31MB קרא נתונים ויותר מ-1.7K רשומות מסוננות - עבור 10 רשומות שחיפשו. עלויות התקורה גבוהות מדי, אין משהו יותר יעיל?

1.2: לחפש לפי טקסט? זה FTS!

ואכן, PostgreSQL מספק עוצמה מאוד מנוע חיפוש טקסט מלא (חיפוש טקסט מלא), כולל יכולת חיפוש קידומת. אפשרות מצוינת, אתה אפילו לא צריך להתקין הרחבות! בוא ננסה:

CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));

SELECT
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;

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

כאן הקבילה של ביצוע שאילתה עזרה לנו מעט, וקיצצה את הזמן בחצי ל 11 אלפיות השנייה. והיינו צריכים לקרוא פי 1.5 פחות - בסך הכל 20MB. אבל כאן, כמה שפחות, יותר טוב, כי ככל שהנפח שאנו קוראים גדול יותר, כך הסיכויים לקבל פספוס מטמון גבוהים יותר, וכל עמוד נוסף של נתונים שנקרא מהדיסק מהווה "בלמים" פוטנציאליים לבקשה.

1.3: עדיין אוהב?

הבקשה הקודמת טובה לכולם, אבל רק אם תמשכו אותה מאה אלף פעמים ביום, היא תגיע 2TB לקרוא נתונים. במקרה הטוב, מהזיכרון, אבל אם אין לך מזל, אז מהדיסק. אז בואו ננסה להקטין אותו.

בואו נזכור מה המשתמש רוצה לראות הראשון "שמתחילים ב...". אז זה בצורתו הטהורה ביותר חיפוש קידומת באמצעות text_pattern_ops! ורק אם "אין לנו מספיק" עד 10 רשומות שאנו מחפשים, אז נצטרך לסיים לקרוא אותן באמצעות חיפוש FTS:

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

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

ביצועים מצוינים - סה"כ 0.05ms וקצת יותר מ-100KB לקרוא! רק אנחנו שכחנו מיון לפי שםכדי שהמשתמש לא ילך לאיבוד בתוצאות:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

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

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

1.4: "סיים עם קובץ"

אבל יש אינדקס שמאפשר לך לחפש לפי טווח ועדיין להשתמש במיון כרגיל - btree רגיל!

CREATE INDEX ON firms(lower(name));

רק את הבקשה עבורו יהיה "לאסוף באופן ידני":

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

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

מצוין - המיון עובד, וצריכת המשאבים נשארת "מיקרוסקופית", יעיל פי אלפי מ-FTS "טהור".! כל מה שנותר הוא לחבר אותו לבקשה אחת:

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'роза' AND
    lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
  ORDER BY
     lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
    lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
  ORDER BY
    lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
  , lower(name)
  LIMIT 10
)
LIMIT 10;

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

אז כן, יש לנו עכשיו גם btree וגם ג'ין על השולחן, אבל סטטיסטית מסתבר שכן פחות מ-10% מהבקשות מגיעות לביצוע החסימה השנייה. כלומר, עם מגבלות אופייניות כאלה ידועות מראש למשימה, הצלחנו להפחית את הצריכה הכוללת של משאבי השרת בכמעט פי אלף!

1.5*: אנחנו יכולים להסתדר בלי קובץ

מעל LIKE נמנע מאיתנו להשתמש במיון שגוי. אבל ניתן "להגדיר אותו בדרך הנכונה" על ידי ציון האופרטור USING:

כברירת מחדל היא מונחת ASC. בנוסף, ניתן לציין את השם של אופרטור מיון ספציפי בסעיף USING. אופרטור המיון חייב להיות חבר קטן מ- או גדול ממשפחת אופרטורים של עץ B. ASC בדרך כלל שווה ערך USING < и DESC בדרך כלל שווה ערך USING >.

במקרה שלנו, "פחות" זה ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

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

2: איך בקשות מחמצות

כעת אנו משאירים את בקשתנו "להתבעבע" למשך שישה חודשים או שנה, ואנו מופתעים למצוא אותה שוב "בפסגה" עם אינדיקטורים של ה"שאיבה" היומית הכוללת של הזיכרון (מאגרים משותפים) ב 5.5TB - כלומר, אפילו יותר ממה שהיה במקור.

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

2.1: הולדת ההחלפה

בשלב מסוים, צוות פיתוח אחר רצה לאפשר "לקפוץ" מחיפוש מנוי מהיר לרישום עם תוצאות זהות, אך מורחבות. מהו רישום ללא ניווט בדפים? בואו נדפק את זה!

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

כעת ניתן היה להציג את הרישום של תוצאות החיפוש עם טעינת "עמוד אחר עמוד" ללא כל לחץ עבור המפתח.

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

2.2: אני רוצה משהו אקזוטי

בשלב מסוים היזם רצה לגוון את המדגם המתקבל עם נתונים מטבלה אחרת, שלגביה נשלחה כל הבקשה הקודמת ל-CTE:

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

ולמרות זאת, זה לא רע, שכן שאילתת המשנה מוערכת רק עבור 10 רשומות שהוחזרו, אם לא ...

2.3: DISTINCT הוא חסר היגיון וחסר רחמים

איפשהו בתהליך של אבולוציה כזו משאילתת המשנה השנייה אָבֵד NOT LIKE מצב. ברור שאחרי זה UNION ALL התחילו לחזור כמה ערכים פעמיים - נמצא תחילה בתחילת השורה, ואחר כך שוב - בתחילת המילה הראשונה של שורה זו. במגבלה, כל הרשומות של שאילתת המשנה השנייה יכולות להתאים לרשומות של הראשונה.

מה עושה מפתח במקום לחפש את הסיבה?.. אין שאלה!

• להכפיל את הגודל דוגמאות מקוריות
• להחיל DISTINCTכדי לקבל רק מופעים בודדים של כל שורה

WITH q AS (
  ( ... LIMIT <2 * N> + 10)
  UNION ALL
  ( ... LIMIT <2 * N> + 10)
  LIMIT <2 * N> + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET <N>;

כלומר, ברור שהתוצאה, בסופו של דבר, זהה לחלוטין, אבל הסיכוי "לעוף" לתוך תת-השאילתה של CTE 2 הפך להרבה יותר גבוה, וגם בלי זה, ברור יותר קריא.

אבל זה לא הדבר הכי עצוב. מאז המפתח ביקש לבחור DISTINCT לא עבור אלה ספציפיים, אלא עבור כל השדות בבת אחת רשומות, ואז השדה sub_query - התוצאה של שאילתת המשנה - נכלל שם באופן אוטומטי. עכשיו, לביצוע DISTINCT, מסד הנתונים היה צריך להפעיל כבר לא 10 שאילתות משנה, אלא כולן <2 * N> + 10!

2.4: שיתוף פעולה מעל הכל!

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

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

באופן כללי, בדגימה שנתפסה N הגיע לערכים של כמעט 17K, ובתוך יום אחד בלבד לפחות 4K מבקשות כאלה בוצעו "לאורך השרשרת". האחרונים שבהם נסרקו באומץ על ידי 1GB זיכרון לכל איטרציה...

בסך הכל

מקור: www.habr.com