אופטימיזציה של אינדקסים ושאילתות

1) מטרות אינדקס ואופטימיזציה

הפחתת P50/P95/P99.
צמיחת QPS ללא סדרי גודל.
חיזוי: תוכניות יציבות ובלי ”קפיצות” בזמן תגובה.
חיסכון: פחות IO/CPU, פחות חשבון ענן.
אמינות: הפחתת מנעולים ומבוי סתום עקב גישה נכונה.

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

2) מבני אינדקס בסיסיים ומתי ליישם אותם

2. 1 B-Tree (ברירת מחדל)

שווה/טווחים, סוג, 'סדר אחר'.
טוב עבור רוב מסנני הזמן/זיהוי/מצב.

2. 2 חשיש

שוויוניות טהורות ('='), זולות יותר בזיכרון אך מקולקלות (PG: אילוצים שהוסרו אך עדיין בחירת נישה).

2. 3 GIN/GIST (PostgreSQL)

GIN: מערכים/מפתחות JSONB, טקסט מלא (tsvector), בלימה ('@>').
גיאו, רכסים, kNN.

2. 4 BRIN (PostgreSQL)

אינדקס סופר-זול על ידי שולחנות ”ממוינים באופן טבעי” (affend-by time). טוב לסדרת זמן עם שולחנות גדולים.

2. 5 Bitmap (MySQL/InnoDB: אין ילידים; DW-DBMS/OLAP)

יעיל לקרדינליות נמוכה והיבטים, לעתים קרובות יותר באחסון עמודות.

2. מדדי עמודה 6 (בית ClickHouse)

מפתח ראשוני + דילוג נתונים (minmax), second 'skip indexes' (פריחה, סט).
שאילתות OLAP עם צבירה וטווחים.

2. 7 אינדקסים הפוכים (Elasticsearch/OpenSearch)

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

2. 8 MongodB

יחיד, תרכובת, רב-מפתח (מערכים), חלקי, TTL, טקסט, חשיש (לשרידת מפתח אחידה).

3) מפתח ועיצוב אינדקס מרוכב

3. חוק הקידומת השמאלית 1

סדר השדות באינדקס קובע את היעילות.

שאלה 'איפה tenant_id =? ו created_at> =? בפקודת DESC "= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

3. 2 שובר שיוויון

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

3. 3 מדדים חלקיים/מסוננים

sql
CREATE INDEX idx_orders_paid_recent
ON orders (created_at DESC, id DESC)
WHERE status = 'paid' AND created_at > now() - interval '90 days';

3. 4 מדדים מכסים

sql
CREATE INDEX idx_user_lastseen_inc ON users (tenant_id, last_seen DESC) INCLUDE (email, plan);

3. 5 פונקציונלי/מחושב

sql
CREATE INDEX idx_norm_email ON users (lower(email));

4) מחיצות ומחלקות

4. 1 חלוקה (PG ירושה מקומית/שולחן; MySQL RANGE/LIST)

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

sql
CREATE TABLE events (
tenant_id bigint,
ts timestamptz,
...
) PARTITION BY RANGE (ts);

4. 2 מפתח מחיצה

ב- OLTP - by 'tenant _ id' (לוקליזציה של טעינה).
בסדרת הזמן/OLAP - by 'ts' (שאילתות טווח).
היברידי: '(tenant_id, ts)' + תת-צדדים.

4. 3 שירטוט

חשיש עקבי/חתך טווח על ידי "דייר _ id' או על ידי זמן.
שאילתה חוצה שברים * פיזור איסוף ומיזוג k-way; תחזיק את הסמן לכל חור.

5) סטטיסטיקות, חשאיות ותוכניות

5. סטטיסטיקות מעודכנות 1

הפעל אנליזה אוטומטית (”autovacuum/autoanalyze”), הגדל את ”ברירת המחדל _ סטטיסטיקה _ מטרה” עבור התפלגויות מלוכלכות.

5. 2 סטטיסטיקה מתקדמת (PG)

sql
CREATE STATISTICS stat_user_country_city (dependencies) ON country, city FROM users;
ANALYZE users;

5. 3 תוכנית הוצאה להורג

• "שורות", "לולאות", "זמן בפועל", "קריאה/להיט משותף", "לבדוק מחדש Cond'.
• הצטרפות: Nest Loop, Hash Join, Merge Join.
• סריקת סק נגד סריקת אינדקס/סריקה בלבד/סריקת ערימה.

5. 4 יציבות בתוכניות

פרמטריזציה (הצהרות מוכנות) יכולה ”לדבוק” בתוכנית גרועה. השתמש במעקות בטיחות מטמון תוכנית (PG: "תוכנית _ cache _ mode = force_custom_plan' לשאילתות בעיה) או" העברת "קבועים.

6) אופטימיזציה של מצטרפים ומינים

6. 1 אסטרטגיות

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

6. 2 אינדקסים מצטרפים

עבור AA AU AB on B.a_id = A.id ', # האינדקס ל' B '(a_id).
עבור המסנן לאחר ההצטרפות - האינדקס על העמודות של המסנן של הטבלה הפנימית.

6. מיון 3

הימנע 'בהוראת על ידי ללא אינדקס מתאים; מיון על סטים גדולים הוא יקר על ידי זיכרון/דיסק.

7) שכתוב שאילתות

להיפטר ”פתיתי שלג” של תת שאילתות; להרחיב ב מצטרף.
השתמש ב ־ CTE-inline (PG בין 12 לקווי ברירת המחדל של CTE, אך ”MATERIALIED” יכול לבצע תוצאת ביניים במקרה הצורך).
הסר את רשימת השדות (IO/רשת חיסכון).
העבירו חישובים מ ”איפה” לצורה האינדקס (טורים ממוחשבים מראש).
אגרגציות: טבלאות סיכום ראשוניות/תצוגות ממשיות עם עדכון מצטבר.

8) קשיחות, הגבלות ועבודת אלילים

הכנס עדכון: 500-5000 חבילות במקום אחת אחרי השנייה.
חפש פגאינציה על ידי '(sort_key, id)' במקום 'עמוק OFSET'.
הגבל את החיוג לפני מיון/joyne (לדחוף כלפי מטה ”LIMIT”).

9) מטמון ודנורמליזציה

שאילתות-מטמון ברמת היישום (מפתח = SQL + bind-vars + rights version).
השקפה ממומשת על אגרגטים כבדים; סבב/תוכנית התייחסות.
Denormalization - החנות קוראת לעתים קרובות שדות מחושבים (מחיר כולל הנחה) אך עם משימת הדק/רקע לעקביות.
Redis בתור L2 עבור מפתחות חמים (עם TTL ונכות אירוע).

10) הפרטים של המנועים הפופולריים

10. 1 פוסט ־ GreSQL

B-Tree, HASH, GIN/GIST, BRIN, חלקי, פונקציונלי, כולל.

sql
CREATE INDEX idx_orders_tenant_created_desc
ON orders (tenant_id, created_at DESC, id DESC)
INCLUDE (amount, status);

sql
CREATE INDEX idx_docs_fts ON docs USING GIN (to_tsvector('russian', title          ' '          body));

10. 2 MySQL/InnoDB

sql
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE; -- check risk-free plans

סטטיסטיקות היסטוגרמה ('אנליזה טבלה... עדכון היסטוגרמה 8. 0).

10. 3 ClickHouse

מפתח עיקרי = סוג; 'סדר ידי (tenant_id, ts, id) ".

sql
CREATE TABLE events (
tenant_id UInt64,
ts DateTime64,
id UInt64,
payload String,
INDEX idx_bloom_payload payload TYPE bloom_filter GRANULARITY 4
) ENGINE = MergeTree()
ORDER BY (tenant_id, ts, id);

10. 4 MongodB

js db. orders. createIndex({ tenant_id: 1, created_at: -1, _id: -1 });
db. orders. createIndex({ status: 1 }, { partialFilterExpression: { archived: { $ne: true } } });

השתמש ”רמז ()” לאבחון, לצפות ”שאילתה מכוסה”.

10. 5 Elasticsearch/OpenSearch

מילת מפתח נגד שדות טקסט; doc_values למיון/אגרגטים.
קטעי ערימה: צבירה - כבד; להגביל 'ולהשתמש' composite 'aggregations (קריאה).
אל תכלול מנתחים היכן דרושה השוואה מדויקת.

11) תחרותיות, משולבים ו ־ MVCC

עסקאות קצרות; להימנע ”ארוך” קורא תחת ”קריאה חוזרת” ללא צורך.
פעולות אינדקס גם לוקחות מנעולים (צמצום תפוקה בכתב).
תוכנית לאינדקס מקוון: 'צור אינדקס קונקרטלי' (PG), 'אלגוריתם = INSLACE '/' ONLINE' (MYSQL).
מכניס בזנב עבור שעה/זיהוי = ”עמודים חמים” של האינדקס; הפיצו את המפתח (UUIDv7/Salt).

12) יכולת תצפית ו ־ SLO

• 'db _ query _ latency _ ms' (P50/P95/P99) בשם שאילתה.
• 'ros _ בחן', 'שורות _ חזר', 'buffer _ hit _ ratio'.
• 'deadlocks',' lock _ wait _ ms', 'temp _ disk _ usage'.
• שיתוף של תוכניות עם 'סק סריקה' שבו 'סריקת אינדקס הייתה צפויה.
• התראות רגרסיה בעת שינוי גירסה/פרמטרים של DBMS.

• אפשר רישום שאילתה איטי עם סף (לדוגמה, 200 ms).
• קורלציה של שאילתות עם תוחלת (trace_id).
• הסר תוכניות שאילתה בעייתיות ושמור לחפצים לאחסון רטרוספקטיבה.

• קרא '<= 150 ms' עם 'LIMIT <= 50&fospos ודייר חם.
• P95 רשומות '<= 200 ms' עם חבורות עד 1000 שורות.

13) בטיחות וריבוי דירות

יש צורך באינדקסים על שדות בקרת גישה (”tenant _ id',” בעלים _ id').
מדיניות (RLS/ABAC) חייבת להיות מסננת מראש; אחרת, האופטימיזר מתכנן לא נכון.
אל תפתח שדות רגישים בטקסט ברור; השתמש בחשיש/אסימונים.

14) אנטי דפוסים

עמוק 'OFSET' ללא אלטרנטיבה מחפש-סמן.
”אינדקס אחד לכולם” - עומס יתר זיכרון וכתיבה-נתיב.
'בחר' בדרכים קריטיות.
פונקציות מעל העמודה ב- 'WHERE' ללא אינדקס פונקציות.
תוכניות לא יציבות בגלל הסטטיסטיקה הישנה.
חסר סדר על ידי 'בזמן מחכה לסדר יציב.
אינדקסים למען האינדקסים: ROI <0 בשל כתיבה/תמיכה יקרה.

15) רשימת מימושים

1. בקשות טופ N על ידי QPS וזמן # בחר 3-5 מועמדים.
2. הסר תוכניות ”הסבר אנליזה”, בדוק חשבונות נגד בפועל.
3. אינדקסים עיצוביים: סדר שדה, CALL/חלקי/פונקציונלי.
4. יישום החלוקה עבור טבלאות גדולות (מקשי דייר זמניים).
5. שאילתות שכתוב יתר: הסר את 'בחר', CTEs פשוטים, הגבלת סט.
6. אפשר לקשקש ולחפש עבודת אלילים.
7. הגדרת מטמון: L1/L2, נכות על ידי אירועים.
8. הצג ניטור של תוכניות ויומן איטי, התראות לסגירות.
9. בצע בדיקות עומס עם הפצת נתונים אמיתית.
10. עדכון הנחיות פיתוח (רמזים ORM, אינדקס, גבולות).

16) לפני/אחרי דוגמאות

sql
SELECT FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

sql
-- Индекс: (status, created_at DESC, id DESC) INCLUDE (amount, currency)
SELECT id, amount, currency, created_at
FROM orders
WHERE status = 'paid'
AND (created_at, id) < (:last_ts,:last_id)   -- seek
ORDER BY created_at DESC, id DESC
LIMIT 50;

17) פרוטוקולי ORM ו ־ API

הימנע מ ־ N + 1: דגימות חמדניות (”כוללות”, ”הצטרפו ל ־ FETCH”, ”preload”).
תחזיות שדה מפורשות, פגום על ידי סמן.
GRPC/REST: הגבילו את "page _ size", תקנו את "sign', השתמשו באסימונים אטומים.
מטמון תוכנית: השתמש בפרמטריזציה; לא מייצרים SQL ”ייחודי” לכל קריאה.

18) נדידה ופעולות

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

תקציר 19)

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