مطالعه بیشتر

نکات و ترفندهایی برای بهبود عملکرد پرس‌وجو: ایندکس‌ها، viewهای materialized، و بیشتر.

تنظیم عملکرد SQL: تکنیک‌هایی برای پرس‌وجوهای سریع‌تر و ارزان‌تر

نکات و ترفندهایی برای بهبود عملکرد پرس‌وجو: ایندکس‌ها، viewهای materialized، و بیشتر.

تنظیم عملکرد SQL چیست، به هر حال؟

تنظیم عملکرد SQL همه درباره سریع‌تر اجرا کردن پرس‌وجوهای پایگاه داده و استفاده از منابع کمتر است. به آن مثل تنظیم ماشین خود فکر کنید تا راحت‌تر رانندگی کند و مصرف بهتری داشته باشد—جز اینکه اینجا، اطمینان می‌دهید پرس‌وجوهای SQL شما همه چیز را کند نمی‌کنند.

مزیت بهینه‌سازی پرس‌وجوهای شما چیست؟

سرعت بخشیدن به بازیابی داده در پایگاه‌های داده بزرگ
سبک کردن بار روی سرورهای شما تا مسدود نشوند.
راضی نگه داشتن کاربران با عملکرد عالی.
صرفه‌جویی در هزینه با استفاده از قدرت محاسباتی کمتر، به خصوص در ابر.

همانطور که داده شما رشد می‌کند و پرس‌وجوهای شما پیچیده‌تر می‌شوند، بهینه‌سازی پرس‌وجوها می‌تواند تفاوت بزرگی در نحوه عملکرد اپ یا ابزارهای تحلیلی شما ایجاد کند.

نحوه استفاده از EXPLAIN برای دیدن نحوه اجرای یک پرس‌وجو

در SQL، مشخص می‌کنید چه داده‌ای می‌خواهید دریافت کنید، اما نحوه دریافت آن را مشخص نمی‌کنید. هر انبار داده یک query planner دارد که کار آن فهمیدن نحوه اجرای پرس‌وجو است. این query planner بهترین راه اجرای پرس‌وجو را بر اساس زمان اجرا، منابع استفاده شده، و/یا پیش‌تنظیم‌های پایگاه داده می‌فهمد. به عنوان مثال، query planner تصمیم می‌گیرد آیا باید همه ردیف‌ها در جدول را بخواند یا جستجوی خود را به مجموعه خاصی از ردیف‌ها بر اساس یک شرط خاص محدود کند.

برای دیدن نحوه اجرای پرس‌وجو توسط پایگاه داده، می‌توانید پرس‌وجوی خود را با EXPLAIN پیشوند کنید. query planner عملیات‌هایی که انجام می‌دهد، به چه ترتیبی، با استفاده از کدام ایندکس‌ها، و غیره را فهرست می‌کند. همچنین EXPLAIN ANALYZE وجود دارد، که آمار اجرای واقعی پرس‌وجو را به شما می‌دهد. اما برای دریافت آن آمار، پایگاه داده واقعاً پرس‌وجو را اجرا می‌کند، که می‌تواند زمان‌بر باشد یا حتی برای پرس‌وجوهایی که داده را تغییر می‌دهند نامطلوب باشد.

برای دیدن EXPLAIN در عمل، ابتدا برخی داده جعلی در یک پایگاه داده PostgreSQL ایجاد می‌کنیم (پایگاه‌های داده دیگر ممکن است کار کنند، اگرچه ممکن است نیاز به تنظیم کد داشته باشید). عبارت زیر را اجرا کنید (نیاز به مجوزهای نوشتن برای انبار داده دارید).

DROP TABLE IF EXISTS data_table;
CREATE TABLE data_table (
    id SERIAL PRIMARY KEY,
    date_field DATE NOT NULL,
    category TEXT NOT NULL CHECK (category IN ('A', 'B', 'C')),
    numeric_value NUMERIC NOT NULL
);

INSERT INTO data_table (date_field, category, numeric_value)
SELECT
    CURRENT_DATE - (random() * 365)::INT, -- Random date within the last year
    CASE FLOOR(random() * 3)
        WHEN 0 THEN 'A'
        WHEN 1 THEN 'B'
        ELSE 'C'
    END, -- Random category
    (random() * 1000)::NUMERIC -- Random numeric value between 0 and 1000
FROM generate_series(1, 50000000);

این پرس‌وجو ممکن است مدتی طول بکشد. برای بررسی اینکه آیا کار کرد، اجرا کنید:

SELECT
  *
FROM
  data_table
LIMIT
  10;

چیزی شبیه این دریافت خواهید کرد:

| id  | date_field        | category | numeric_value |
| --- | ----------------- | -------- | ------------- |
| 1   | August 2, 2024    | A        | 874.17        |
| 2   | October 31, 2024  | A        | 762.03        |
| 3   | August 23, 2024   | A        | 718.73        |
| 4   | February 6, 2025  | C        | 334.45        |
| 5   | August 28, 2024   | A        | 59.4          |
| 6   | August 8, 2024    | A        | 972.74        |
| 7   | October 18, 2024  | B        | 296.99        |
| 8   | November 27, 2024 | B        | 858.26        |
| 9   | September 5, 2024 | C        | 137.84        |
| 10  | February 24, 2025 | C        | 701.68        |

بیایید یک مجموع ساده از numeric_value بر اساس category انجام دهیم و ببینیم چقدر طول می‌کشد. در اینجا پرس‌وجو:

SELECT
  category AS "Category",
  SUM(numeric_value) AS "Sum"
FROM
  data_table
GROUP BY
  category
ORDER BY
  category ASC

چیزی شبیه این دریافت خواهید کرد:

| Category | Sum              |
| -------- | ---------------- |
| A        | 8,336,275,140.07 |
| B        | 8,330,139,598.5  |
| C        | 8,334,188,258.65 |

اجرای آن پرس‌وجو روی PostgreSQL 17 ما با 2 هسته CPU و 2GB RAM حدود 8 ثانیه طول کشید — زمان خیلی طولانی.

حالا بیایید EXPLAIN را روی پرس‌وجو اجرا کنیم. EXPLAIN plan پرس‌وجو، همراه با هزینه‌ها و داده دیگر درباره پرس‌وجو را خروجی می‌دهد. فقط پرس‌وجو را با EXPLAIN پیشوند کنید:

EXPLAIN
    SELECT
        category AS "Category",
        SUM(numeric_value) AS "Sum"
    FROM
        data_table
    GROUP BY
        category
    ORDER BY
        category ASC;

خروجی زیر را خواهید دید (که بسته به استقرار فعلی شما می‌تواند متفاوت باشد):

QUERY PLAN
Finalize GroupAggregate  (cost=631972.08..631972.87 rows=3 width=34)
  Group Key: category
  ->  Gather Merge  (cost=631972.08..631972.78 rows=6 width=34)
        Workers Planned: 2
        ->  Sort  (cost=630972.06..630972.07 rows=3 width=34)
              Sort Key: category
              ->  Partial HashAggregate  (cost=630972.00..630972.04 rows=3 width=34)
                    Group Key: category
                    ->  Parallel Seq Scan on data_table  (cost=0.00..526805.33 rows=20833333 width=13)
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true

خروجی EXPLAIN باید از پایین به بالا خوانده شود. این query plan می‌گوید:

پایگاه داده ابتدا همه رکوردها در جدول را به صورت متوالی (Seq Scan) به صورت موازی روی دو هسته موجود (Workers Planned: 2) اسکن می‌کند.
سپس، پایگاه داده داده را تجمیع می‌کند (HashAggregate)، و آن را روی هر worker به صورت جداگانه (Partial) انجام می‌دهد. پایگاه داده باید تجمیع کند چون یک بند GROUP BY و تجمیع SUM شامل کردیم. تجمیع می‌تواند به صورت موازی انجام شود چون جمع کردن عملیاتی است که می‌تواند موازی شود: جمع کردن مجموع کل روی دو partition همان جمع کردن روی همه داده است.
سپس، پایگاه داده نتایج تجمیع شده را روی هر worker به صورت جداگانه (Sort) مرتب می‌کند. Sort آنجا است، به طور شگفت‌انگیز، نه چون یک بند ORDER BY شامل کردیم — اگر بند ORDER BY را حذف کنید، مرحله Sort هنوز آنجا خواهد بود. پایگاه داده نتایج تجمیع را مرتب می‌کند چون نیاز به ادغام نتایج از دو worker دارد، و وقتی نتایج مرتب هستند کارآمدتر است. اگر به جای ORDER BY category ORDER BY "Sum" داشتید، دو مرتب‌سازی در query plan وجود داشت — با یک مرتب‌سازی اضافی پس از GroupAggregate.
بعد، پایگاه داده نتایج از دو worker را ادغام می‌کند (Gather Merge). در این نقطه، پایگاه داده هنوز مجموع‌ها را برای هر دسته اضافه نکرده است. می‌دانیم نکرده چون پس از تجمیع مقدار rows 3 است: هر worker سه ردیف دارد، یکی برای هر دسته (A، B، و C)، اما پس از Gather Merge 6 rows داریم، پس پایگاه داده هنوز باید مجموع‌ها را از هر worker ترکیب کند.
در نهایت، پایگاه داده نتایج از دو worker را گروه‌بندی و تجمیع می‌کند (GroupAggregate).

از نظر عملکرد، این خروجی به ما می‌گوید:

پردازشگر پرس‌وجو از 2 هسته استفاده می‌کند (Workers Planned: 2).
پرهزینه‌ترین عملیات Parallel Seq Scan بود (0.00..526805.33).
عملیات‌های دیگر تأثیر ناچیزی روی زمان کلی پرس‌وجو داشتند (مثلاً، 630972.00..630972.04 برای HashAggregate — تفاوت بین دو عدد، یعنی هزینه، در مقایسه با هزینه برای Parallel Seq Scan خیلی کوچک است.)

پس چگونه می‌توانیم این پرس‌وجو را متوقف کنیم که پایگاه داده را وقتی مشتریان هر صبح داشبورد خود را بررسی می‌کنند نابود کند؟ پایگاه داده باید هر ردیف در جدول را برای جمع کردن آن‌ها بخواند، پس نمی‌توانیم تعداد کل ردیف‌های اسکن شده را کاهش دهیم. بهترین کاری که می‌توانیم انجام دهیم سرعت بخشیدن به اسکن است. گزینه‌های ما شامل:

افزایش تعداد هسته‌های CPU، تا هر هسته نیاز به عبور از ردیف‌های کمتری داشته باشد.
تغییر به یک پایگاه داده با معماری بهینه شده برای اسکن‌های متوالی سریع‌تر ستون‌های واحد (مثل یک پایگاه داده ستونی).

اگر تغییرات زیرساخت خارج از سؤال است، می‌توانید در عوض نتایج تجمیع را از پیش محاسبه کنید — مفید اگر مشتریان پرس‌وجو را اغلب اجرا می‌کنند — و از کش کردن نتایج یا materialize کردن viewها استفاده کنید.

ایجاد viewهای materialized برای joinها، CTEها، و viewهای گران

Viewهای materialized پرس‌وجوهای از پیش محاسبه شده‌ای هستند که می‌توانید در انبار داده خود ذخیره کنید. Viewهای materialized فشار را از پایگاه داده شما با ذخیره نتایج محاسبه شده برای مشتریان برای پرس‌وجو برمی‌دارند، به جای محاسبه ردیف‌ها هر بار که پایگاه داده را پرس‌وجو می‌کنند. می‌توانید viewهای materialized را به صورت دوره‌ای refresh کنید تا نتایج آن‌ها با داده جدید به‌روز بماند.

بیایید یک view materialized با پرس‌وجویی که قبلاً انجام دادیم ایجاد کنیم:

CREATE MATERIALIZED VIEW preprocessed_sum_of_numeric_value_by_category AS
    SELECT
        category AS "Category",
        SUM(numeric_value) AS "Sum"
    FROM
        data_table
    GROUP BY
        category
    ORDER BY
        category ASC

این نتایج پرس‌وجو را ذخیره می‌کند:

-- preprocessed_sum_of_numeric_value_by_category

| Category | Sum              |
| -------- | ---------------- |
| A        | 8,336,275,140.07 |
| B        | 8,330,139,598.5  |
| C        | 8,334,188,258.65 |

حالا همه آنچه برای دریافت مجموع برای هر دسته نیاز داریم انتخاب از این view materialized است:

SELECT
  *
FROM
  preprocessed_sum_of_numeric_value_by_category;

پرس‌وجوی view materialized می‌تواند زمان را از ثانیه‌ها به میلی‌ثانیه‌ها کاهش دهد.

بسته به پایگاه داده شما، viewهای materialized ممکن است یا ممکن نیست وقتی داده زیربنایی تغییر می‌کند به‌روزرسانی شوند. به عنوان مثال، اگر از PostgreSQL استفاده می‌کنید، نیاز به به‌روزرسانی view materialized خود دارید. برای محاسبه مجدد نتایج، نیاز به یک اسکریپت برای اجرای REFRESH MATERIALIZED VIEW preprocessed_sum_of_numeric_value_by_category در یک cadence خاص دارید. چقدر اغلب نتایج را refresh می‌کنید بستگی به چقدر اغلب داده زیربنایی تغییر می‌کند و چقدر اغلب مشتریان نتایج را بررسی می‌کنند دارد.

در اینجا برخی موقعیت‌ها وجود دارد که باید materialize کردن viewها را در نظر بگیرید:

Joinها

Joinهای جدول یکی از زیباترین چیزها در دنیای پایگاه داده رابطه‌ای هستند (یا حداقل ما فکر می‌کنیم). Joinها به شما امکان سازماندهی کسب‌وکار خود در بخش‌های ماژولار که می‌توانند فقط با یک کلمه کلیدی ساده کنار هم قرار گیرند را می‌دهند. اما joinها همچنین می‌توانند خیلی خطرناک باشند: یک join در برابر یک جدول عظیم می‌تواند واقعاً انبار داده شما را مسدود کند. اگر نیاز به join کردن بیش از دو یا سه جدول هر بار که یک پرس‌وجو می‌کنید دارید، materialize کردن پرس‌وجوی پایه با joinها را در یک view materialized در نظر بگیرید.

CTEها

CTEها برای ماژولار کردن کد شما عالی هستند. اما انبار داده شما باید این CTEها را هر بار که این پرس‌وجوها را اجرا می‌کنید محاسبه کند. اگر خود را در حال اجرای همان پرس‌وجوهای پر از CTE هر روز می‌بینید، زمان آن است که داده خود را به گونه‌ای مدل کنید که پرس‌وجوهای شما فقط یک SELECT * FROM table ساده باشد جایی که table یک view materialized یا جدولی است که با نتایج این CTEها ایجاد کرده‌اید.

Viewها (نه viewهای materialized)

به جای نیاز به نوشتن صدها خط SQL هر بار که یک پرس‌وجو می‌کنید، به سادگی ردیف‌ها را از view ذخیره شده خود انتخاب می‌کنید و voilá: همان نتایج را دریافت می‌کنید. Viewها فقط انتزاع هستند؛ هیچ مزیت عملکردی نمی‌دهند. از viewها وقتی می‌خواهید پیچیدگی پرس‌وجوها را کاهش دهید استفاده کنید. اگر اغلب از نتایج استفاده می‌کنید، materialize کردن view را در نظر بگیرید.

نحوه سرعت بخشیدن به پرس‌وجوها با ایندکس کردن ستون‌های مکرراً پرس‌وجو شده

ممکن است در برخی موارد درباره ایندکس‌ها شنیده باشید. پرس‌وجوهای شما کند هستند، یک فرد با دانش پایگاه داده به شما می‌گوید یک ایندکس ایجاد کنید، و boom: پرس‌وجوها اکنون سریع‌تر هستند. یک ایندکس مثل یک جدول lookup است که به پایگاه داده شما کمک می‌کند به سرعت ردیف‌های خاص را بدون نیاز به اسکن از طریق کل جدول پیدا کند. درست مثل نحوه کمک ایندکس یک کتاب برای پیدا کردن موضوعات بدون خواندن هر صفحه، یک ایندکس پایگاه داده مستقیماً به جایی که داده ذخیره شده است اشاره می‌کند. آن‌ها وقتی نیاز به جستجوی برخی از داده دارید، اما نه همه داده، به خوبی کار می‌کنند.

وقتی یک ایندکس به یک یا چند ستون اضافه می‌کنید، پایگاه داده یک کپی از آن ستون‌ها که برای جستجوی مقادیر خاص بهینه شده است ایجاد می‌کند، و همچنین شامل یک اشاره‌گر برای دریافت مقادیر از ستون‌های دیگر (غیر ایندکس شده) است.

به عنوان مثال، بگویید یک جدول با داده مشتری دارید، شامل IDهای مشتری، نام‌ها، تاریخ‌های ثبت‌نام و غیره. اگر می‌خواستید یک مشتری با یک نام خاص پیدا کنید، پایگاه داده باید هر رکورد واحد را اسکن کند و نام را با آنچه درخواست کردید مقایسه کند، تا زمانی که یک تطابق پیدا کند. اما اگر یک ایندکس روی ستون نام قرار دهید، پایگاه داده ابزارهای دیگری برای انجام این کار خواهد داشت. پیاده‌سازی‌های دقیق ایندکس‌ها می‌تواند متفاوت باشد، اما تقریباً، می‌توانید به ایندکس به عنوان یک فهرست مرتب شده از همه نام‌ها فکر کنید، ذخیره شده با یک اشاره‌گر به رکورد دقیق با آن نام در پایگاه داده. چون فهرست نام‌ها در ایندکس مرتب شده است، پایگاه داده زمان بسیار کمتری برای پیدا کردن نامی که پرسیدید نیاز دارد. پس از پیدا شدن نام، از اشاره‌گر ذخیره شده در ایندکس برای بازیابی بقیه داده برای آن مشتری استفاده می‌کند.

پایگاه داده شما همیشه یک ایندکس روی کلید اصلی خواهد داشت (به همین دلیل است که جستجوی یک رکورد با ID معمولاً سریع‌تر از، به عنوان مثال، نام و نام خانوادگی است).

بیایید به مثال قبلی برگردیم: SUM(numeric_value) روی همه دسته‌ها در جدول. آیا یک ایندکس آن سؤال را سریع‌تر می‌کند؟ قطعاً نه، چون آن سؤال هنوز باید روی کل جدول برود تا نتیجه را محاسبه کند.

حالا، بیایید تصور کنیم کسی می‌خواهد یک پرس‌وجو انجام دهد که فقط بخشی از یک جدول را جستجو می‌کند: مجموع مقدار عددی بر اساس دسته، اما فقط برای 60 روز گذشته. بیایید آن را امتحان کنیم:

SELECT
  category AS "Category",
  SUM(numeric_value) AS "Sum"
FROM
  data_table
WHERE
  date_field <= CURRENT_DATE + INTERVAL '1' DAY
  AND date_field >= CURRENT_DATE - INTERVAL '60' DAY
GROUP BY
  category
ORDER BY
  category ASC

که مدتی طول کشید! بیایید EXPLAIN را اجرا کنیم تا ببینیم اینجا چه اتفاقی می‌افتد:

QUERY PLAN
Finalize GroupAggregate  (cost=857409.42..857410.21 rows=3 width=34)
  Group Key: category
  ->  Gather Merge  (cost=857409.42..857410.12 rows=6 width=34)
        Workers Planned: 2
        ->  Sort  (cost=856409.40..856409.41 rows=3 width=34)
              Sort Key: category
              ->  Partial HashAggregate  (cost=856409.34..856409.38 rows=3 width=34)
                    Group Key: category
                    ->  Parallel Seq Scan on data_table  (cost=0.00..839305.33 rows=3420801 width=13)
                          Filter: ((date_field <= (CURRENT_DATE + '1 day'::interval day)) AND (date_field >= (CURRENT_DATE - '60 days'::interval day)))
JIT:
  Functions: 9
  Options: Inlining true, Optimization true, Expressions true, Deforming true

یک بار دیگر، عملیات اسکن متوالی پرهزینه را می‌بینیم. آیا یک ایندکس روی date_field کمک می‌کند؟

ایجاد یک ایندکس

بیایید یک ایندکس روی ستون date_field ایجاد کنیم:

CREATE INDEX idx_data_table_date
ON data_table (date_field);

پایگاه داده نیاز به زمانی برای ایجاد یک ایندکس دارد، و جدول را از هر عملیات نوشتن تا زمانی که ایندکس را تمام کند قفل می‌کند.

می‌توانید ایندکس را با یک پرس‌وجو بررسی کنید:

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE tablename = 'data_table'

نوشتن یک پرس‌وجو که از یک ایندکس استفاده می‌کند

پس از آماده شدن ایندکس، بیایید پرس‌وجو را دوباره اجرا کنیم تا ببینیم آیا از ایندکس استفاده می‌کند و زمان لازم برای برگرداندن نتایج را کاهش می‌دهد.

اگر دوباره EXPLAIN را اجرا کنید، می‌بینید که ایندکس حتی استفاده نشد، و هنوز اسکن متوالی موازی دریافت می‌کنید (و پرس‌وجو هنوز کند است).

بیایید ببینیم آیا می‌توانیم پایگاه داده را با تاریخ‌های خاص، نه نسبی، به استفاده از ایندکس وادار کنیم.

EXPLAIN
SELECT
  category AS "Category",
  SUM(numeric_value) AS "Sum"
FROM
  data_table
WHERE
  date_field BETWEEN DATE '2025-01-01' AND DATE '2025-03-01'
GROUP BY
  category
ORDER BY
  category ASC;

که به ما می‌دهد:

QUERY PLAN
Finalize GroupAggregate  (cost=649104.46..649105.24 rows=3 width=34)
  Group Key: category
  ->  Gather Merge  (cost=649104.46..649105.16 rows=6 width=34)
        Workers Planned: 2
        ->  Sort  (cost=648104.44..648104.44 rows=3 width=34)
              Sort Key: category
              ->  Partial HashAggregate  (cost=648104.38..648104.41 rows=3 width=34)
                    Group Key: category
                    ->  Parallel Seq Scan on data_table  (cost=0.00..630972.00 rows=3426475 width=13)
                          Filter: ((date_field >= '2025-01-01'::date) AND (date_field <= '2025-03-01'::date))
JIT:
  Functions: 9
  Options: Inlining true, Optimization true, Expressions true, Deforming true

استفاده از تاریخ‌های خاص هزینه را از cost=0.00..839305.33 به cost=0.00..630972.00 کاهش داد.

اما آیا موتور هنوز یک اسکن کامل جدول انجام می‌دهد؟ بله، انجام می‌دهد (Parallel Seq Scan on data_table). برای وادار کردن پایگاه داده به استفاده از ایندکس، نیاز به محدود کردن بازه زمانی داریم. بیایید شرط BETWEEN را برای محدود کردن بازه زمانی به دو هفته تغییر دهیم، بگویید 2025-03-01 تا 2025-03-15. دوباره EXPLAIN را اجرا کنید. چیزی شبیه این خواهید دید:

QUERY PLAN
Finalize GroupAggregate  (cost=633296.94..633297.72 rows=3 width=34)
  Group Key: category
  ->  Gather Merge  (cost=633296.94..633297.64 rows=6 width=34)
        Workers Planned: 2
        ->  Sort  (cost=632296.92..632296.92 rows=3 width=34)
              Sort Key: category
              ->  Partial HashAggregate  (cost=632296.86..632296.89 rows=3 width=34)
                    Group Key: category
                    ->  Parallel Bitmap Heap Scan on data_table  (cost=27900.15..628034.34 rows=852503 width=13)
                          Recheck Cond: ((date_field >= '2025-03-01'::date) AND (date_field <= '2025-03-15'::date))
                          ->  Bitmap Index Scan on idx_data_table_date  (cost=0.00..27388.65 rows=2046008 width=0)
                                Index Cond: ((date_field >= '2025-03-01'::date) AND (date_field <= '2025-03-15'::date))
JIT:
  Functions: 9
  Options: Inlining true, Optimization true, Expressions true, Deforming true

Index Cond را می‌بینید؟ حالا از ایندکسی که ایجاد کردیم استفاده می‌کنیم. آنچه قبلاً یک اسکن متوالی بود اکنون یک اسکن ایندکس است.

جادویی به نظر می‌رسد، درست است؟ خوب... نه خیلی. چون همانطور که قبلاً دیده‌اید، اگر برای یک بازه زمانی به اندازه کافی طولانی پرس‌وجو کنید، هنوز یک اسکن کامل جدول را trigger می‌کنید. و دلیلی برای آن وجود دارد.

بیایید سناریوی زیر را تصور کنیم: یک کتاب با هزار صفحه دارید. یک خواننده مشتاق هستید که صد صفحه در روز می‌خوانید. حالا بگویید می‌خواهید خطوط خاص را در یک کتاب پیدا کنید. نمی‌توانید از یک ایندکس برای پیدا کردن آن خطوط استفاده کنید؛ نیاز به اسکن کل کتاب دارید. حالا بیایید تصور کنیم که، به جای پیدا کردن خطوط خاص، نیاز به پیدا کردن فصلی که یک موضوع را پوشش می‌دهد دارید: این موردی است که یک ایندکس به کار می‌آید.

صبر کنید، اما این‌ها رایانه‌ها هستند، درست است؟ مثل، خوانندگان فوق‌العاده سریع؟ نمی‌توانم فقط برای هر ستون یک ایندکس بسازم تا همه موارد ممکن را پوشش دهد؟ خوب، قطعاً می‌توانید، اما همه آن ایندکس‌ها ظرفیت نوشتن جدول را مختل می‌کنند: هر بار که یک ردیف جدید وجود دارد، موتور باید همه ایندکس‌ها را به‌روزرسانی کند. به علاوه، پایگاه داده نیاز به فضای بیشتر و بیشتر برای ذخیره آن ایندکس‌ها خواهد داشت.

پس ایندکس‌ها می‌توانند کمک کنند (خیلی)، اما محدودیت‌هایی دارند. در اینجا برخی راه‌های دیگر برای سرعت بخشیدن به پرس‌وجوها وجود دارد.

تکنیک‌های دیگر برای بهبود عملکرد پرس‌وجوی SQL

فقط داده مورد نیاز خود را درخواست کنید

ردیف‌های بیشتر یعنی پرس‌وجوهای کندتر (بیشتر اوقات). مشابه یک کتاب درسی، هرچه کتاب صفحات بیشتری داشته باشد، زمان بیشتری برای خواندن آن می‌گیرید. پایگاه‌های داده به همان شکل کار می‌کنند. پس فقط داده مورد نیاز خود را درخواست کنید. یک راه رایج برای کاهش ردیف‌ها فیلتر برای بازه‌های تاریخ کوچکتر است.

کش کردن نتایج

کش کردن به سادگی ذخیره نتایج پرس‌وجو است تا بتوانید نتایج را در آینده بازیابی کنید. تصمیم می‌گیرید چقدر می‌خواهید نتایج معتبر بمانند قبل از نیاز به refresh با فراخوانی دیگر به پایگاه داده. گزینه‌های کش ابزار BI خود را بررسی کنید.

داده خود را برای برآورده کردن نیازهای فعلی مدل کنید، نه برای همیشه

با مدل‌سازی داده اینجا فقط منظورمان نحوه سازماندهی داده خود به جداول، و نحوه ارتباط آن جداول با یکدیگر است. نیازهای تحلیلی شما از نیازهای عملیاتی شما متفاوت خواهد بود (مدل داده اپلیکیشن شما احتمالاً بهترین مدل برای پرس‌وجوهای تحلیلی نیست). داده به خوبی مدل شده می‌تواند واقعاً عملکرد را بهبود بخشد، اما مدل کامل وجود ندارد. یک مدل داده خوب مدلی است که مشکلاتی که امروز دارید (و برای چند ماه آینده) را حل می‌کند. شرکت‌هایی که زنده می‌مانند با گذشت زمان رشد و تکامل می‌یابند، پس انتخاب‌های طراحی که در گذشته انجام دادید ممکن است دیگر در آینده مناسب شما نباشد.

ویژگی‌های مدیریت workload پایگاه داده خود را بررسی کنید

پایگاه‌های داده برای اجرای پرس‌وجوها برای چندین کلاینت به طور همزمان طراحی شده‌اند. یک نکته وجود دارد، اگرچه: برای یک موتور پایگاه داده، یک پرس‌وجوی عظیم و یک پرس‌وجوی سریع اولویت یکسانی دارند، پس می‌توانید موقعیتی داشته باشید که پرس‌وجوی عظیم همه منابع سرور را مصرف می‌کند در حالی که پرس‌وجوی سریع صبورانه منتظر آزاد شدن منابع می‌ماند.

برخی موتورهای پایگاه داده ویژگی‌های مدیریت workload ارائه می‌دهند که به شما امکان اختصاص پرس‌وجوها به صف‌های مختلف را می‌دهند، که می‌تواند تأثیر آن پرس‌وجوهای بزرگ که سرور شما را می‌کوبند را نرم کند.

اجتناب از اجرای پرس‌وجوها روی پایگاه داده تولید اپلیکیشن شما

مگر اینکه یک استارتاپ با فقط چند نفر که از اپلیکیشن شما استفاده می‌کنند باشید، اجرای پرس‌وجوها روی همان پایگاه داده‌ای که اپلیکیشن شما در تولید استفاده می‌کند را توصیه نمی‌کنیم. یک پرس‌وجوی گران ممکن است باعث هرج و مرج در عملیات روزانه شما شود. توصیه می‌کنیم حداقل یک انبار داده جداگانه که فقط خواندنی است (اغلب replica خواندنی نامیده می‌شود، چون یک کپی از پایگاه داده تولید شما است) ایجاد کنید و ابزار تحلیلی خود را به آن متصل کنید.

برای دریاچه‌های داده، از کلیدهای partition استفاده کنید

اگر از یک دریاچه داده استفاده می‌کنید، همان اصول پایه شرح داده شده اینجا اعمال می‌شود. به علاوه، اگر دریاچه داده شما عملکرد partition دارد، باید از کلیدهای partition برای کمک به اجتناب از خواندن‌های عظیم استفاده کنید. برای بیشترین استفاده از موازی‌سازی، می‌خواهید از کلیدهای partition در همه پرس‌وجوهای خود استفاده کنید.

یک انبار داده ستونی را در نظر بگیرید

انبارهای داده رابطه‌ای سنتی مثل PostgreSQL همه‌کاره‌های عالی هستند، و می‌توانند شما را خیلی در مسیر تحلیلی جلو ببرند. اما اگر زمان‌های پرس‌وجو غیرقابل تحمل می‌شوند—و از قبل همه تکنیک‌های این مقاله را امتحان کرده‌اید—انتقال workloadهای تحلیلی خود به یک موتور ستونی را در نظر بگیرید. انبارهای داده ستونی برای workloadهای تحلیلی طراحی شده‌اند، پس می‌توانند یک جهش عملکرد بزرگ به شما بدهند (البته با قیمت).

برای نکات عملکرد برای ذخیره‌سازی ستونی، نیاز به مراجعه به مستندات روی موتور ستونی خاص دارید، چون هر موتور quirks و قیمت‌گذاری خود را دارد.

نمی‌توانید همه پرس‌وجوهای خود را فوق‌العاده سریع کنید

آنچه می‌توانید انجام دهید برقراری تعادل بین:

هزینه‌های زیرساخت: حتی اگر هسته‌ها و حافظه بیشتری روی مشکل بیندازید، سرعت پرس‌وجوی شما به یک plateau می‌رسد.
ایندکس‌هایی که ایجاد می‌کنید: اگر ایندکس‌های زیادی دارید، نوشتن را کند می‌کنید.
کهنگی داده: اگر نیاز به داده به‌روز شده در زمان واقعی ندارید، می‌توانید نتایج را ذخیره و استفاده مجدد کنید.

هوش تجاری

تجزیه و تحلیل تعبیه شده

داشبوردهای تحلیلی

سازنده کوئری

تفکیک داده‌ها

مدل‌ها

تحلیل عملکرد

متابات بزودی

SDK تجزیه و تحلیل تعبیه شده

تحلیل برچسب سفید

حفاری عمقی

ویرایشگر SQL

مجوزها

آپلود CSV

مستندات

یادگیری

شروع کار

راهنماها

وبلاگ

رویدادها

مشتریان

بحث

متخصصان Metabase

داستان‌های جامعه

آخرین پست‌های وبلاگ

داستان پشت تولیدکننده مجموعه داده هوش مصنوعی ما

چگونه داشبوردهای فروش بسازیم که تیم‌های فروش واقعاً از آن استفاده کنند

تجزیه و تحلیل تعبیه شده چیست؟

نظرسنجی پشته داده جامعه متابیس: توسط تیم‌های داده، برای تیم‌های داده

۵ شکست بزرگ داشبورد (و نحوه رفع آن‌ها)