# فصل ۰۲ — `EXPLAIN QUERY PLAN`: ذره‌بینِ دوره

<div dir="rtl">

# فصل ۰۲ — `EXPLAIN QUERY PLAN`: ذره‌بینِ دوره

## در این فصل چه یاد می‌گیری

فصلِ قبل یک پرسش را زمان گرفت و جواب داد «سی‌وچند میلی‌ثانیه». این فصل همان پرسش را می‌پرسد و جوابِ کاملاً دیگری می‌گیرد: **`SCAN events`** — یعنی پایگاه داده هر ۴۰۰٬۰۰۰ سطر را خواند تا ۱۱٬۴۲۱ سطر را پیدا کند.

بعد یک آزمایشِ کوچک: **همان یک کتاب را دو جور صدا می‌زنیم و همان عنوان برمی‌گردد**، ولی یکی از دو پرسش `SEARCH` می‌گیرد و دیگری `SCAN` — و اختلافِ زمانشان ده‌ها برابر است.

![ذره‌بینی که روی نقشهٔ یک ساختمان گرفته شده و دو مسیرِ متفاوت را روی همان نقشه نشان می‌دهد](/lesson-assets/t5-s02-explain/prompt/01-image-plan-under-glass.png)

آخر این فصل می‌توانی:

- نقشهٔ اجرای هر پرسشی را چاپ کنی، بدونِ اینکه پرسش اجرا شود
- `SCAN` را از `SEARCH` تشخیص بدهی و بگویی هر کدام یعنی چه
- `USING INDEX` و `USING COVERING INDEX` را از هم جدا کنی
- بفهمی `USE TEMP B-TREE` کجا از مرتب‌سازی و گروه‌بندی می‌آید
- نقشه را به‌شکلِ درخت بخوانی، نه فهرست

## قبل از شروع

**از فصلِ ۱:** `timed`، میانهٔ چند اجرا، و پهنای نوسان. **از ترمِ ۴:** `PRIMARY KEY` و `UNIQUE`.

**دانه‌بندی:** یک سطر از `events` یک رویداد است. یک سطر از **نقشهٔ اجرا** یک **مرحله از کارِ پایگاه داده** است — و این فصل دربارهٔ خواندنِ همان جدولِ دوم است.

> 📓 **نوت‌بوک:** [نوت‌بوک این فصل را در Colab باز کن](/notebooks/avand/term-5-performance/session-02-explain.ipynb) — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

</div>

| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
| `events` | ۴۰۰٬۰۰۰ | چند ثانیه |
| `books` | ۴۰٬۰۰۰ | کمتر از یک ثانیه |
| `members` | ۱۲٬۰۰۰ | آنی |
| `branches` | ۸ | آنی |

<div dir="rtl">

## ۱. سه کلمه که پرسش را اجرا نمی‌کنند

`EXPLAIN QUERY PLAN` را جلوی هر `SELECT`ی بگذاری، `SQLite` به‌جای اجرای پرسش، **نقشهٔ کاری را که قرار بود بکند** برمی‌گرداند. هیچ سطری خوانده نمی‌شود و هیچ عددی حساب نمی‌شود.

تابعِ `plan` در سلولِ راه‌اندازی دقیقاً همین پیشوند را به متنِ پرسش می‌چسباند، و `show_plan` نتیجه‌اش را کامل چاپ می‌کند. **`show_plan` فقط در این ترم وجود دارد** — چون فقط اینجا ستونِ `detail` آن‌قدر بلند می‌شود که `pandas` می‌بُردش؛ ترم‌های دیگر همان `plan(...)` را مستقیم چاپ می‌کنند.

**و اینجا یک استثنای مهم است که باید صریح گفته شود:** ما داریم متن به یک پرسش **می‌چسبانیم**، در حالی که کلِ این دوره می‌گوید هرگز چیزی به متنِ پرسش نچسبان. تفاوت این است که آنچه می‌چسبانیم **مقدار نیست** — یک کلیدواژهٔ ثابتِ `SQL` است که ما در کد نوشته‌ایم و از هیچ ورودی‌ای نمی‌آید. **مقدارها همچنان با `?` می‌روند**، و در پرسشِ زیر دقیقاً همین‌طور است.

</div>

```python
SQL_MONTH = """
    SELECT COUNT(*) AS n
    FROM events
    WHERE happened_at >= ? AND happened_at < ?
"""
MONTH = ("2022-03-01", "2022-04-01")

print("پاسخ:", int(q(SQL_MONTH, MONTH).iloc[0, 0]), "رویداد از",
      int(q("SELECT COUNT(*) AS n FROM events").iloc[0, 0]))
```

```text
پاسخ: 11421 رویداد از 400000
```

```python
show_plan(SQL_MONTH, MONTH)
```

```text-approx
 id  parent      detail
  3       0 SCAN events
```

<div dir="rtl">

**یک کلمه، و کلِ معما حل شد: `SCAN events`.**

`SCAN` یعنی «از سطرِ اول تا سطرِ آخرِ این جدول را می‌خوانم». پایگاه داده هیچ راهِ میان‌بری به تاریخ ندارد، پس هر ۴۰۰٬۰۰۰ سطر را برداشت، شرط را روی هرکدام سنجید، و ۱۱٬۴۲۱ تا را نگه داشت. **آن سی‌وچند میلی‌ثانیهٔ فصلِ قبل، هزینهٔ خواندنِ ۳۸۸٬۵۷۹ سطری بود که به کارمان نیامدند.**

## ۲. `SCAN` در برابرِ `SEARCH`

حالا همان کتاب را دو جور صدا می‌زنیم. هر دو پرسش **دقیقاً یک عنوان** برمی‌گردانند:

</div>

```python
BY_KEY = "SELECT title FROM books WHERE id = ?"
BY_MATH = "SELECT title FROM books WHERE id + 0 = ?"

print("با کلید :", q(BY_KEY, (17,)).iloc[0, 0])
print("با محاسبه:", q(BY_MATH, (17,)).iloc[0, 0])
```

```text
با کلید : سنگ پشت ستاره
با محاسبه: سنگ پشت ستاره
```

```python
show_plan(BY_KEY, (17,))
print()
show_plan(BY_MATH, (17,))
print()
print("با کلید  :", timed(BY_KEY, (17,)), "میلی‌ثانیه")
print("با محاسبه:", timed(BY_MATH, (17,)), "میلی‌ثانیه")
```

```text-approx
 id  parent                                           detail
  2       0 SEARCH books USING INTEGER PRIMARY KEY (rowid=?)

 id  parent     detail
  2       0 SCAN books

با کلید  : 0.06 میلی‌ثانیه
با محاسبه: 2.83 میلی‌ثانیه
```

<div dir="rtl">

**همان جواب، همان جدول، همان یک سطر — و ده‌ها برابر اختلاف.**

- **`SEARCH`** یعنی پایگاه داده مستقیم رفت سرِ جای درست. `INTEGER PRIMARY KEY` در `SQLite` همان شناسهٔ داخلیِ سطر است، پس رسیدن به سطرِ شمارهٔ ۱۷ یعنی چند پرش در یک درخت.
- **`SCAN`** یعنی همان کارِ بخشِ ۱: هر ۴۰٬۰۰۰ سطر خوانده شد.

و **دلیلِ `SCAN` شدنِ پرسشِ دوم را همین‌جا نگو، فقط ثبتش کن**: به‌محضِ اینکه ستونِ فیلتر داخلِ یک محاسبه برود، پایگاه داده دیگر نمی‌تواند از ترتیبِ ذخیره‌شده استفاده کند. فصلِ ۴ کلِ این خانواده از تله‌ها را باز می‌کند.

> ✅ **چک کن:** دو پرسشِ بالا باید **دقیقاً** یک عنوان بدهند. اگر ندادند، شمارهٔ کتاب را در یکی از دو پرسش عوض کرده‌ای — و آن‌وقت داری دو کارِ متفاوت را با هم می‌سنجی، نه دو راهِ رسیدن به یک کار.

## ۳. `USING INDEX`: چیزی که از قبل آنجاست

جدولِ `members` هیچ `index`ی که ما ساخته باشیم ندارد. ولی ستونِ `national_id` قیدِ `UNIQUE` دارد، و **`SQLite` برای اجرای هر قیدِ `UNIQUE` خودش یک `index` می‌سازد**:

</div>

```python
print(q("PRAGMA index_list(members)")[["name", "unique", "origin"]])
print()
print(q("PRAGMA index_info(sqlite_autoindex_members_1)"))
```

```text
                         name  unique origin
0  sqlite_autoindex_members_1       1      u

   seqno  cid         name
0      0    3  national_id
```

<div dir="rtl">

ستونِ `origin` مقدارِ `u` دارد، یعنی «از یک قیدِ `UNIQUE` آمده» — نه از دستِ ما. حالا ببینیم در نقشه چطور دیده می‌شود:

</div>

```python
BY_NATIONAL = "SELECT name, city FROM members WHERE national_id = ?"
print(q(BY_NATIONAL, ("1000079190",)))
```

```text
          name city
0  شیرین توکلی   قم
```

```python
show_plan(BY_NATIONAL, ("1000079190",))
print()
print("زمان:", timed(BY_NATIONAL, ("1000079190",)), "میلی‌ثانیه")
```

```text-approx
 id  parent                                                                detail
  3       0 SEARCH members USING INDEX sqlite_autoindex_members_1 (national_id=?)

زمان: 0.06 میلی‌ثانیه
```

<div dir="rtl">

**`SEARCH ... USING INDEX <نام>`** یعنی پایگاه داده از یک ساختارِ جانبی استفاده کرد تا مستقیم به سطر برسد. فصلِ بعد نشان می‌دهد چطور خودت چنین چیزی بسازی؛ اینجا فقط داریم یاد می‌گیریم که در نقشه چه شکلی است.

## ۴. `USING COVERING INDEX`: وقتی اصلاً به جدول سر نمی‌زند

یک کلمهٔ اضافه در نقشه هست که ارزشِ توجه دارد. این دو پرسش را با هم بسنج:

</div>

```python
NEED_ROW = "SELECT name, city FROM members WHERE national_id < ?"
NEED_NOTHING = "SELECT COUNT(*) AS n FROM members WHERE national_id < ?"
HALF = ("1050000000",)

print("سطرهایی که در بازه‌اند:", int(q(NEED_NOTHING, HALF).iloc[0, 0]))
print("کل اعضا              :", int(q("SELECT COUNT(*) AS n FROM members").iloc[0, 0]))
```

```text
سطرهایی که در بازه‌اند: 6313
کل اعضا              : 12000
```

```python
show_plan(NEED_NOTHING, HALF)
```

```text-approx
 id  parent                                                                         detail
  3       0 SEARCH members USING COVERING INDEX sqlite_autoindex_members_1 (national_id<?)
```

<div dir="rtl">

**`COVERING` یعنی هر چیزی که این پرسش لازم داشت، داخلِ خودِ `index` بود.** پرسش فقط می‌خواست بشمارد، و آن `index` تمامِ مقدارهای `national_id` را دارد — پس پایگاه داده اصلاً به جدولِ `members` سر نزد.

پرسشِ `NEED_ROW` ولی `name` و `city` می‌خواهد که در `index` نیستند، پس مجبور است برای هر سطرِ پیداشده به جدول هم مراجعه کند. **همین یک کلمه در نقشه، تفاوتِ یک مراجعه با دو مراجعه به ازای هر سطر است**، و فصلِ بعد اندازه‌اش می‌گیرد.

## ۵. `USE TEMP B-TREE`: کاری که در نقشه پیدا می‌شود ولی در پرسش نوشته نشده

مرتب‌سازی و گروه‌بندی هزینه دارند، و نقشه صریح می‌گویدشان:

</div>

```python
BY_CATEGORY = """
    SELECT category, COUNT(*) AS n
    FROM books
    GROUP BY category
    ORDER BY category
"""
first_five = "SELECT id, happened_at FROM events ORDER BY happened_at LIMIT 5"

rows = q(BY_CATEGORY)
print(rows)
print("جمع هشت دسته:", int(rows["n"].sum()))
print()
print(q(first_five))
```

```text
     category     n
0       تاریخ  4949
1        رمان  4943
2  زندگی‌نامه  5004
3         شعر  5127
4         علم  4967
5       فلسفه  4879
6         هنر  5006
7        کودک  5125
جمع هشت دسته: 40000

   id          happened_at
0   1  2022-01-01 00:02:34
1   2  2022-01-01 00:04:37
2   3  2022-01-01 00:04:44
3   4  2022-01-01 00:04:56
4   5  2022-01-01 00:06:25
```

```python
show_plan(BY_CATEGORY)
print()
show_plan(first_five)
print()
print("گروه‌بندی :", timed(BY_CATEGORY), "میلی‌ثانیه")
print("پنج سطر اول:", timed(first_five), "میلی‌ثانیه")
```

```text-approx
 id  parent                       detail
  7       0                   SCAN books
  9       0 USE TEMP B-TREE FOR GROUP BY

 id  parent                       detail
  4       0                  SCAN events
 16       0 USE TEMP B-TREE FOR ORDER BY

گروه‌بندی : 13.23 میلی‌ثانیه
پنج سطر اول: 39.82 میلی‌ثانیه
```

<div dir="rtl">

**پرسشِ دوم فقط پنج سطر می‌خواهد و گران‌تر از پرسشی است که هشت گروه می‌سازد.**

`USE TEMP B-TREE FOR ORDER BY` می‌گوید چرا: برای اینکه بدانی کدام پنج سطر **اولین‌اند**، باید هر ۴۰۰٬۰۰۰ سطر را دیده باشی. `LIMIT 5` این خواندن را کم نمی‌کند؛ فقط به موتور اجازه می‌دهد به‌جای مرتب کردنِ همه، پنج‌تای برتر را نگه دارد. **کارِ خواندن همچنان انجام شده است.**

این جملهٔ کوتاه را نگه دار، چون در فصلِ ۶ دوباره سرِ راهمان سبز می‌شود: **`LIMIT` یک ابزارِ سرعت نیست.**

## ۶. نقشه یک درخت است، نه فهرست

دو ستونِ اولِ نقشه تا حالا بی‌کار بوده‌اند. `id` شمارهٔ هر مرحله است و `parent` می‌گوید هر مرحله زیرمجموعهٔ کدام مرحله است. **مرحله‌ای که `parent`ش صفر نیست، داخلِ مرحلهٔ دیگری اجرا می‌شود.**

</div>

```python
DONORS = "SELECT COUNT(*) AS n FROM members WHERE id IN (SELECT donated_by FROM books)"
print("اعضایی که دستِ‌کم یک کتاب اهدا کرده‌اند:", int(q(DONORS).iloc[0, 0]))
print("کتاب‌هایی که اهداکننده دارند         :",
      int(q("SELECT COUNT(donated_by) AS n FROM books").iloc[0, 0]))
```

```text
اعضایی که دستِ‌کم یک کتاب اهدا کرده‌اند: 2840
کتاب‌هایی که اهداکننده دارند         : 3251
```

```python
show_plan(DONORS)
```

```text-approx
 id  parent                                             detail
  3       0 SEARCH members USING INTEGER PRIMARY KEY (rowid=?)
  7       0                                    LIST SUBQUERY 1
  9       7                                         SCAN books
```

<div dir="rtl">

مرحلهٔ ۹ زیرِ مرحلهٔ ۷ است. یعنی: **اول زیرپرسش اجرا می‌شود و فهرستِ اهداکننده‌ها ساخته می‌شود (`SCAN books`)، بعد برای هر مقدارِ آن فهرست یک `SEARCH` روی `members` انجام می‌شود.**

و توجه کن که ۳٬۲۵۱ کتابِ اهدایی از ۲٬۸۴۰ عضو آمده‌اند — **بعضی اعضا بیش از یک کتاب داده‌اند**، و همین است که فهرستِ زیرپرسش کوتاه‌تر از تعدادِ کتاب‌هاست.

> 🔧 **اگر کار نکرد:** پرتکرارترین خطای این فصل وقتی است که متنی را که از قبل پیشوند دارد دوباره به `plan` بدهی:

</div>

```python
try:
    con.execute("EXPLAIN QUERY PLAN EXPLAIN QUERY PLAN SELECT 1").fetchall()
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
sqlite3.OperationalError
near "EXPLAIN": syntax error
```

<div dir="rtl">

**رفعش این است که متغیرهای پرسش را همیشه بدونِ پیشوند نگه داری** و پیشوند را فقط از راهِ `plan` اضافه کنی. اگر یک بار پیشوند را داخلِ متنِ ذخیره‌شدهٔ پرسش بگذاری، آن متن دیگر نه اجرا می‌شود و نه نقشه می‌دهد.

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در جدول‌های این فصل دو دانه‌بندی داشتیم: یک سطر از نتیجه (یک دسته، یک عضو) و یک سطر از **نقشه**، که یک مرحله از کارِ پایگاه داده است. **با کدام شمارشِ دوم سنجیدی؟** با سه‌تا: دو پرسشِ بخشِ ۲ همان یک عنوان را دادند؛ جمعِ هشت دستهٔ بخشِ ۵ دقیقاً ۴۰٬۰۰۰ شد؛ و ۲٬۸۴۰ عضو در برابرِ ۳٬۲۵۱ کتابِ اهدایی با هم سازگارند چون یک عضو می‌تواند چند کتاب داده باشد. **چه کسی از قلم افتاد؟** ۳۶٬۷۴۹ کتابی که `donated_by` ندارند و در نقشهٔ بخشِ ۶ اصلاً وارد فهرست نمی‌شوند — و فصلِ ۶ نشان می‌دهد همین `NULL`ها چطور می‌توانند جوابِ یک پرسشِ کاملاً معقول را صفر کنند.

> 🤖 **از دستیارت بپرس:** «تفاوتِ `SCAN` و `SEARCH` در خروجیِ `EXPLAIN QUERY PLAN` چیست؟» بعد این را بپرس: **«اگر نقشه بگوید `SEARCH`، آیا حتماً پرسشم سریع است؟»** — جوابِ درست می‌گوید نه: `SEARCH` فقط می‌گوید راهِ میان‌بری استفاده شد، و اگر آن میان‌بر نصفِ جدول را برگرداند باز هم کند است. اگر جواب گفت «بله، `SEARCH` یعنی سریع»، همین حالا یادداشتش کن؛ فصلِ ۴ با عدد ردش می‌کند.

## واژه‌های تازهٔ این فصل

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| query plan | کوئری پلن | نقشهٔ اجرا؛ فهرستِ کارهایی که پایگاه داده برای یک پرسش می‌کند |
| `SCAN` | اسکن | خواندنِ همهٔ سطرهای یک جدول یا `index` |
| `SEARCH` | سرچ | رفتنِ مستقیم به سطرهای موردِ نظر، بدونِ خواندنِ بقیه |
| covering index | کاورینگ ایندکس | `index`ی که همهٔ ستون‌های لازمِ پرسش را دارد، پس جدول لازم نمی‌شود |
| temp b-tree | تمپ بی‌تری | ساختارِ موقتی که برای `ORDER BY` یا `GROUP BY` ساخته و بعد دور ریخته می‌شود |
| autoindex | اتوایندکس | `index`ی که `SQLite` خودش برای اجرای یک قید ساخته است |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا `LIMIT 5` پرسشِ بخشِ ۵ را ارزان نکرد؟

**تمرینِ اجباری:** نقشهٔ اجرای این چهار پرسش را چاپ کن: شمارشِ کلِ `events`؛ یک کتاب با `id` مشخص؛ کتاب‌های یک دسته؛ و همان پرسشِ دسته‌ها با `ORDER BY n DESC`. **برای هر چهارتا، قبلش حدس بزن `SCAN` می‌بینی یا `SEARCH`، بعد چاپ کن.** حالا جوابِ سؤالِ اصلی را بنویس: **کدام حدست غلط بود و نقشه چه چیزی نشان داد که تو حسابش نکرده بودی؟**

**تمرینِ ستاره‌دار ⭐:** یک پرسش با دو `JOIN` بنویس — `events` به `books` به `branches` — که تعدادِ رویدادهای هر شهر را بدهد. **نقشه‌اش را چاپ کن و هر سطرش را با یک جملهٔ فارسی توضیح بده**، به‌ترتیبی که واقعاً اجرا می‌شوند. بعد سؤالِ سخت را جواب بده: **آیا ترتیبِ سطرهای نقشه همان ترتیبِ نوشتنِ جدول‌ها در پرسش است؟ اگر جای دو جدول را در متنِ پرسش عوض کنی، نقشه عوض می‌شود؟** اول حدس بزن، بعد امتحان کن.

## در فصل بعد

حالا می‌دانی `SCAN` یعنی چه و `SEARCH` یعنی چه، و دیدی که یک `index` — حتی آنکه `SQLite` خودش ساخته بود — می‌تواند `SCAN` را به `SEARCH` تبدیل کند.

فصلِ بعد همان کار را عمداً می‌کند: **یک `CREATE INDEX` روی `events(happened_at)`، و همان پرسشِ ماهِ سومِ فصلِ ۱ که `SCAN events` می‌گرفت، `SEARCH` می‌شود.** دو عددِ زمانی‌اش را کنارِ هم می‌گذاریم، و بعد سراغِ چیزی می‌رویم که کمتر کسی می‌پرسد: **این `index` چقدر فضا گرفت و نوشتن را چقدر کند کرد؟**

</div>

---
Source: https://yaadestan.com/courses/avand/lessons/t5/s02-explain (یادستان, Yaadestan)
