אופטימיזציה של שאילתות באמצעות עמודות וירטואליות לביטויים

בדף הזה מוסבר איך להשתמש בעמודות וירטואליות לביטויים במנוע מבוסס-עמודות כדי להאיץ את השאילתות.

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

הנה כמה תרחישי שימוש בעמודות וירטואליות לביטויים:

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

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

ביטויים נתמכים בעמודות וירטואליות

‫AlloyDB תומך באופרטורים -> ו-->> בעמודות JSON ו-JSONB בגרסת (Preview).

לפני שמתחילים

  1. מפעילים את מנוע מבוסס-העמודות באשכול AlloyDB. מידע נוסף זמין במאמר בנושא הגדרת מנוע מבוסס-עמודות.

  2. אפשר לזהות ביטויים שנמצאים בשימוש תדיר בשאילתות AlloyDB באמצעות תובנות לגבי שאילתות.

  3. הוספה של עמודות הבסיס של ביטויים למנוע מבוסס-עמודות. מידע נוסף זמין במאמר בנושא ניהול ידני של תוכן במאגר עמודות.

הפעלת עמודות וירטואליות לביטויים

אפשר להפעיל תמיכה בעמודות וירטואליות לביטויים במופע באמצעות הפקודה gcloud beta alloydb instances update.

כדי להשתמש ב-CLI של gcloud, אפשר להתקין ולהפעיל את Google Cloud CLI, או להשתמש ב-Cloud Shell.

    gcloud beta alloydb instances update INSTANCE_ID \
        --region=REGION_ID \
        --cluster=CLUSTER_ID \
        --project=PROJECT_ID \
        --update-mode=INPLACE \
        --add-database-flags="google_columnar_engine.enable_virtual_columns_scan=on"

מחליפים את מה שכתוב בשדות הבאים:

  • ‫INSTANCE_ID: מזהה מופע AlloyDB.
  • ‫REGION_ID: האזור של מופע AlloyDB.
  • ‫CLUSTER_ID: מזהה האשכול של מופע AlloyDB.
  • ‫PROJECT_ID: מזהה הפרויקט ב- Google Cloud .

כדי להגדיר את הדגל הזה ברמת הסשן, מריצים את הפקודה הבאה:

SET google_columnar_engine.enable_virtual_columns_scan=on;

הוספת עמודות וירטואליות לביטויים

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

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

    SELECT google_columnar_engine_add(
    relation => 'DB.SCHEMA.TABLE_NAME',
    columns => 'COLUMN_NAME, COLUMN_NAME',
    expressions => 'EXP1, EXP2, EXP3'
    );

מחליפים את מה שכתוב בשדות הבאים:

  • ‫DB.SCHEMA: סכימת מסד הנתונים שבה הטבלה מאוחסנת.
  • ‫TABLE_NAME: שם הטבלה שבה העמודה מאוחסנת.
  • ‫COLUMN_NAME: שם העמודות שכוללות את הביטויים.

  • ‫EXP1, EXP2, EXP3 עם רשימה מופרדת בפסיקים של ביטויים. הביטויים הנתמכים ב-JSON Extract הם -> ו-->>.

    לדוגמה, כדי להוסיף את הביטויים user ->> 'email' ו-user ->> 'name' לטבלה employee בסכימה public, משתמשים בשאילתה הבאה:

        SELECT google_columnar_engine_add(
    relation => 'postgres.public.employee',
    expressions => '"user ->> ''email''", "user ->> ''name''"'
    );

    תחביר של ביטוי:

    • מקיפים את כל ערך הביטוי במירכאות בודדות – לדוגמה, expressions => 'EXP1,EXP2,EXP3'.
    • אם יש כמה ביטויים, צריך להפריד ביניהם בפסיקים.
    • מקיפים כל ביטוי בודד במירכאות כפולות.
    • כדי לסמן בתו בריחה (escape) גרש בודד בביטוי, צריך להוסיף גרש בודד נוסף.

    לדוגמה, כדי להוסיף את הביטויים col -> 'level1' ו-col -> 'level1' ->> 'level2', צריך להשתמש בפורמט הבא:

        expressions => '"col -> ''level1''", "col -> ''level1'' ->> ''level2''"'

דוגמה לעמודות וירטואליות לביטויים

הדוגמה הזו מדגימה איך להשתמש בתכונה 'עמודות וירטואליות לביטויים'. יוצרים טבלה users עם עמודת JSONB‏ profile ומאכלסים אותה בנתונים לדוגמה. לאחר מכן, על סמך ניתוח השאילתה, מוסיפים את הביטוי profile ->> 'email' שבו נעשה שימוש לעתים קרובות למנוע העמודות באמצעות הפונקציה google_columnar_engine_add. לאחר מכן, מנוע מבוסס-עמודות משתמש בביטוי הנפוץ הזה כדי לבצע אופטימיזציה של שאילתות עתידיות.

כדי להוסיף את הביטוי הרגולרי לדוגמה למנוע מבוסס-עמודות, פועלים לפי השלבים הבאים:

  1. נכנסים לדף Clusters במסוף Google Cloud .

    כניסה לדף Clusters

  2. לוחצים על שם האשכול בעמודה שם המשאב.

  3. בחלונית הניווט, לוחצים על AlloyDB Studio.

  4. כדי ליצור את הטבלה users עם עמודת JSONB‏ profile, מריצים את הפקודה הבאה:

    CREATE TABLE users (
id int,
username TEXT,
profile JSONB
);

  5. כדי לאכלס את הטבלה users בנתונים לדוגמה, מריצים את הפקודה הבאה:

    INSERT INTO users (id, username, profile)
SELECT
    i,
    'user' || i,
    jsonb_build_object(
        'name', 'User ' || i,
        'email', 'user' || i || '@example.com',
        'active', (i % 2 = 0)
    )
FROM generate_series(1, 100000) AS i;

  6. כדי לשפר את הביצועים של השאילתה שכוללת את הביטוי הנפוץ, מוסיפים את הביטוי profile ->> 'email' למנוע מבוסס-עמודות:

    SELECT google_columnar_engine_add(
    relation => 'users',
    columns => 'username, profile',
    expressions => '"profile ->> ''email''"'
);

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

    SELECT username
FROM users
WHERE profile->>'email' = 'user50000@example.com';

  8. הפעלת התכונה של עמודות וירטואליות לביטויים.

    SET google_columnar_engine.enable_virtual_columns_scan=on;

  9. מריצים מחדש את השאילתה שמשתמשת בביטוי התדיר ומתעדים את הזמן שנדרש להשלמת השאילתה.

    SELECT username
FROM users
WHERE profile->>'email' = 'user50000@example.com';

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

הצגת עמודות וירטואליות של ביטויים

כדי למצוא את כל הביטויים שנוספו לטבלה ספציפית, מריצים שאילתה בתצוגה g_columnar_virtual_columns:

    SELECT * FROM g_columnar_virtual_columns;

הפלט אמור להיראות כך, כאשר השדה description מציג את כל הביטויים שנוספו לטבלה (relation).

SELECT * FROM g_columnar_virtual_columns;
category           | expression
database_name      | testdb
schemas            | {public}
relations          | {users}
description        | profile->>'email'
column_data_type   | text
status             | Usable
last_accessed_time | 2026-02-04 06:25:32.499601+00
num_times_accessed | 1

הסרת עמודות וירטואליות של ביטויים

כדי להסיר ביטוי, קוראים לפונקציה google_columnar_engine_drop():

    SELECT google_columnar_engine_drop(
      relation => 'DB.SCHEMA.TABLE_NAME',
      expressions => 'EXP1, EXP2, EXP3'
    );

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