آزمون طرح اجرای SQLite؛ اندیس مرکب و پوشاننده برای دادهٔ حسگر

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

۱. قرارداد داده و نتیجه را پیش از طرح اجرا بنویسیم

هر ردیف جدول sample یک نمونهٔ مستقل دارد: شناسهٔ یکتا، شناسهٔ حسگر، زمان صحیح برحسب میلی‌ثانیه، ولتاژ صحیح برحسب میلی‌ولت و برچسب متنی. دادهٔ آموزشی چهار حسگر، دوازده زمان و دو نمونه در هر زمان دارد؛ مجموعاً ۹۶ ردیف. تکرار زمان مجاز است؛ برابر بودن زمان، دو نمونه را به یک رویداد تبدیل نمی‌کند. زمان‌ها فقط اعداد ساختگی‌اند، نه ساعت ثبت یا زمان شبکه.

درخواست اصلی چنین است: از حسگر دوم، نمونه‌های دارای زمان بزرگ‌تر یا مساوی ۱۰۲۰ و کوچک‌تر از ۱۰۴۰ را بردار؛ سپس برحسب زمان و بعد ولتاژ مرتب کن. جواب دستی چهار زوج دارد: ۱۰۲۰ با ۲۰۲۰، ۱۰۲۰ با ۲۰۲۱، ۱۰۳۰ با ۲۰۳۰ و ۱۰۳۰ با ۲۰۳۱. مؤلفهٔ اول میلی‌ثانیه و مؤلفهٔ دوم میلی‌ولت است. زمان ۱۰۴۰ بیرون بازه می‌ماند.

مرتب‌سازی جزئی از قرارداد SQL است. در این داده، زوج زمان و ولتاژ برای هر حسگر یکتاست؛ اگر دادهٔ تازه زوج‌های کاملاً برابر داشته باشد و ترتیب تک‌تک ردیف‌ها مهم شود، به کلید تکمیلی نیاز داریم. این درس صفحه‌بندی یا مدل زمانِ واقعی نمی‌سازد. اندیس باید نتیجهٔ تعریف‌شده را حفظ کند؛ تغییر ترتیب فیزیکی ذخیره‌سازی، مجوز حذف ORDER BY نیست.

۲. اندیس مرکب چگونه به این درخواست مربوط می‌شود؟

اندیس آزمایش، ستون‌های device_id، سپس ts_ms و سپس voltage_mv را دارد. برای درک ترتیب، ابتدا رکوردهای یک حسگر کنار هم قرار می‌گیرند؛ درون آن گروه، زمان و سپس ولتاژ ترتیب را تعیین می‌کنند. توضیح رسمی SQLite دربارهٔ اندیس‌های چندستونی، نقش ستون سمت چپ و شکستن تساوی‌ها را شرح می‌دهد. این ترتیب برای درخواستِ مشخص ما معنی دارد؛ نسخهٔ همگانیِ بهترین اندیس برای هر سامانه نیست.

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

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

۳. کد کامل و ثبتِ مشاهدهٔ واقعی طرح اجرا

کد با کتابخانهٔ استاندارد Python اجرا می‌شود. اتصال :memory: خصوصی است و در پایان بسته می‌شود؛ نه فایل موجودی باز می‌کنیم و نه WAL، چندریسمانی یا آزمون سرعت داریم. طبق مستندات sqlite3 پایتون، مقادیر متغیر با جای‌نگهدار ? به SQL متصل می‌شوند. نام جدول و اندیس در کد ثابت است؛ ساختن عبارت SQL از متن ورودیِ خواننده جزو مثال نیست.

"""Version-recorded SQLite access-plan exercise; no timing or disk database."""
import json
import platform
import sqlite3

QUERY = """SELECT ts_ms, voltage_mv FROM sample
WHERE device_id=? AND ts_ms>=? AND ts_ms<?
ORDER BY ts_ms, voltage_mv"""
LABEL_QUERY = QUERY.replace("ts_ms, voltage_mv FROM", "ts_ms, voltage_mv, label FROM")
INDEX = "sample_device_time_voltage"


def fixture():
    rows = []
    for device in range(1, 5):
        for tick in range(12):
            for replica in (0, 1):
                rows.append((len(rows) + 1, device, 1000 + 10 * tick,
                             1000 * device + 10 * tick + replica,
                             f"d{device}-t{tick}-r{replica}"))
    return rows


def parameters(device_id, lower_ms, upper_ms):
    if any(type(x) is not int for x in (device_id, lower_ms, upper_ms)):
        raise TypeError("exact integer query parameters required")
    if not 1 <= device_id <= 2**31 - 1:
        raise ValueError("device_id outside educational domain")
    if not 0 <= lower_ms <= upper_ms <= 2**63 - 1:
        raise ValueError("ordered nonnegative int64 time bounds required")
    return device_id, lower_ms, upper_ms


def selected(connection, device, lower, upper, *, labels=False):
    args = parameters(device, lower, upper)
    return connection.execute(LABEL_QUERY if labels else QUERY, args).fetchall()


def reference(rows, device, lower, upper, *, labels=False):
    # A full Python scan and sort, independently of SQLite's index or plan.
    chosen = [r for r in rows if r[1] == device and lower <= r[2] < upper]
    chosen.sort(key=lambda r: (r[2], r[3]))
    return [(r[2], r[3], r[4]) if labels else (r[2], r[3]) for r in chosen]


def plan(connection, query, args):
    return connection.execute("EXPLAIN QUERY PLAN " + query, args).fetchall()


def rejects(function, args, expected):
    try:
        function(*args)
    except expected:
        return True
    return False


def run_example():
    checks = {}
    def check(name, condition):
        checks[name] = bool(condition)
        if not checks[name]:
            raise AssertionError(name)

    data = fixture()
    connection = sqlite3.connect(":memory:", autocommit=True)
    try:
        connection.execute("""CREATE TABLE sample(
          id INTEGER PRIMARY KEY, device_id INTEGER NOT NULL,
          ts_ms INTEGER NOT NULL, voltage_mv INTEGER NOT NULL,
          label TEXT NOT NULL)""")
        # Automatic indexes are irrelevant to this single-table fixture and disabled.
        connection.execute("PRAGMA automatic_index=OFF")
        connection.executemany("INSERT INTO sample VALUES(?,?,?,?,?)", data)
        args = (2, 1020, 1040)
        hand = [(1020, 2020), (1020, 2021), (1030, 2030), (1030, 2031)]
        before = selected(connection, *args)
        before_plan = plan(connection, QUERY, args)
        details_before = [r[3] for r in before_plan]
        check("hand_result_before_index", before == hand)
        check("observed_before_scan_current_runtime", any(d.startswith("SCAN sample") for d in details_before))
        check("observed_before_temporary_order_current_runtime", any("TEMP B-TREE FOR ORDER BY" in d for d in details_before))
        connection.execute(f"CREATE INDEX {INDEX} ON sample(device_id,ts_ms,voltage_mv)")
        after = selected(connection, *args)
        after_plan = plan(connection, QUERY, args)
        label_plan = plan(connection, LABEL_QUERY, args)
        details_after = [r[3] for r in after_plan]
        details_label = [r[3] for r in label_plan]
        check("hand_result_after_index_same", after == hand == before)
        check("observed_composite_covering_search_current_runtime", any(d.startswith("SEARCH sample") and f"COVERING INDEX {INDEX}" in d for d in details_after))
        check("observed_no_temporary_order_after_current_runtime", not any("TEMP B-TREE" in d for d in details_after))
        check("observed_label_projection_noncovering_current_runtime", any(f"USING INDEX {INDEX}" in d and "COVERING" not in d for d in details_label))
        check("label_projection_independent_reference", selected(connection, *args, labels=True) == reference(data, *args, labels=True))
        check("lower_inclusive_upper_exclusive", selected(connection, 1, 1000, 1010) == [(1000, 1000), (1000, 1001)])
        check("equal_bounds_empty", selected(connection, 1, 1010, 1010) == [])
        check("unknown_device_empty", selected(connection, 99, 0, 2000) == [])
        cases = 0
        for device in (1, 2, 4):
            for lower in (999, 1020, 1100):
                for upper in (1100, 1110, 1200):
                    actual = selected(connection, device, lower, upper)
                    if actual != reference(data, device, lower, upper):
                        raise AssertionError("independent scan result")
                    cases += 1
        check("independent_scan_sort_27_cases_one_group", cases == 27)
        check("index_column_order_recorded", [r[2] for r in connection.execute(f"PRAGMA index_info({INDEX})")] == ["device_id", "ts_ms", "voltage_mv"])
        check("duplicate_timestamps_preserved_and_second_key_ordered", len(after) == 4 and [r[1] for r in after if r[0] == 1020] == [2020, 2021])
        invalid_types = [(True, 0, 1), (1, 0.0, 1), (1, 0, "1"), (1, float("nan"), 2), (1, 0, float("inf"))]
        check("invalid_parameter_types_rejected", all(rejects(parameters, a, TypeError) for a in invalid_types))
        invalid_bounds = [(0, 0, 1), (2**31, 0, 1), (1, -1, 1), (1, 2, 1), (1, 0, 2**63)]
        check("invalid_parameter_ranges_rejected", all(rejects(parameters, a, ValueError) for a in invalid_bounds))
        check("select_and_explain_preserve_fixture", connection.execute("SELECT * FROM sample ORDER BY id").fetchall() == data)
        check("raw_plan_four_fields_and_in_memory_scope", all(len(r) == 4 and type(r[3]) is str for r in before_plan + after_plan + label_plan) and connection.execute("PRAGMA database_list").fetchone()[2] == "")
        return {
            "python_version": platform.python_version(), "sqlite_version": sqlite3.sqlite_version,
            "scope": "single-thread sequential private :memory: database; no WAL, network, existing files, ANALYZE, INDEXED BY or timing benchmark",
            "fixture_rows": len(data), "query_parameters": list(args),
            "units": {"ts_ms": "ms synthetic integer", "voltage_mv": "mV synthetic integer"},
            "hand_result": [list(r) for r in hand],
            "before_index_eqp": [list(r) for r in before_plan],
            "after_index_eqp": [list(r) for r in after_plan],
            "label_projection_eqp": [list(r) for r in label_plan],
            "reference_cases_in_one_group": cases,
            "plan_assertion_scope": "Observed structural properties on recorded runtime only. EQP format and planner choice are not portable application contracts or performance evidence.",
            "checks": checks,
        }
    finally:
        connection.close()


if __name__ == "__main__":
    print(json.dumps(run_example(), ensure_ascii=False, indent=2))

تابع parameters فقط int دقیق را قبول می‌کند؛ bool، رشته، float، NaN و بی‌نهایت رد می‌شوند. شناسهٔ حسگر بین یک و حداکثر صحیح علامت‌دار ۳۲بیتی و کران‌های زمان نامنفی و حداکثر صحیح علامت‌دار ۶۴بیتی‌اند. کران پایین نباید از بالا بیشتر باشد. بازهٔ خالی با دو کران برابر معتبر است و نتیجهٔ خالی دارد؛ حسگرِ ناشناختهٔ معتبر هم الزاماً خطای ورودی نیست.

SELECT و EXPLAIN QUERY PLAN در هر وضعیت جدا اجرا و ردیف‌های خامِ چهارستونیِ طرح ذخیره می‌شوند. شماره‌های گره یا ستون سوم را معیار پذیرش تجاری نمی‌گذاریم. اندیس‌های خودکار برای این تمرین خاموش‌اند و ANALYZE یا INDEXED BY اجرا نشده است. بنابراین نام اندیسِ مشاهده‌شده، انتخاب طبیعیِ همین موتور و همین داده است؛ تغییر نسخه، آمار یا داده می‌تواند مشاهده را تغییر دهد.

۴. نتیجهٔ مستقل را از گزارش تشخیصی جدا بسنجیم

در اجرای Python 3.14.5 با SQLite 3.50.4، پیش از اندیس، SCAN sample و مرتب‌سازی موقت دیده شد. پس از اندیس، SEARCH با COVERING INDEX گزارش شد و همان مرتب‌سازی موقت در طرح دیده نشد. درخواستِ شامل label، عبارت USING INDEX داشت و پوشاننده نبود. این‌ها گزارش مسیرند؛ هیچ نسبت افزایش سرعت، زمان انتظار یا تضمین حافظه از آن‌ها محاسبه نکرده‌ایم.

{
  "python_version": "3.14.5",
  "sqlite_version": "3.50.4",
  "scope": "single-thread sequential private :memory: database; no WAL, network, existing files, ANALYZE, INDEXED BY or timing benchmark",
  "fixture_rows": 96,
  "query_parameters": [
    2,
    1020,
    1040
  ],
  "units": {
    "ts_ms": "ms synthetic integer",
    "voltage_mv": "mV synthetic integer"
  },
  "hand_result": [
    [
      1020,
      2020
    ],
    [
      1020,
      2021
    ],
    [
      1030,
      2030
    ],
    [
      1030,
      2031
    ]
  ],
  "before_index_eqp": [
    [
      3,
      0,
      216,
      "SCAN sample"
    ],
    [
      16,
      0,
      0,
      "USE TEMP B-TREE FOR ORDER BY"
    ]
  ],
  "after_index_eqp": [
    [
      3,
      0,
      46,
      "SEARCH sample USING COVERING INDEX sample_device_time_voltage (device_id=? AND ts_ms>? AND ts_ms<?)"
    ]
  ],
  "label_projection_eqp": [
    [
      4,
      0,
      49,
      "SEARCH sample USING INDEX sample_device_time_voltage (device_id=? AND ts_ms>? AND ts_ms<?)"
    ]
  ],
  "reference_cases_in_one_group": 27,
  "plan_assertion_scope": "Observed structural properties on recorded runtime only. EQP format and planner choice are not portable application contracts or performance evidence.",
  "checks": {
    "hand_result_before_index": true,
    "observed_before_scan_current_runtime": true,
    "observed_before_temporary_order_current_runtime": true,
    "hand_result_after_index_same": true,
    "observed_composite_covering_search_current_runtime": true,
    "observed_no_temporary_order_after_current_runtime": true,
    "observed_label_projection_noncovering_current_runtime": true,
    "label_projection_independent_reference": true,
    "lower_inclusive_upper_exclusive": true,
    "equal_bounds_empty": true,
    "unknown_device_empty": true,
    "independent_scan_sort_27_cases_one_group": true,
    "index_column_order_recorded": true,
    "duplicate_timestamps_preserved_and_second_key_ordered": true,
    "invalid_parameter_types_rejected": true,
    "invalid_parameter_ranges_rejected": true,
    "select_and_explain_preserve_fixture": true,
    "raw_plan_four_fields_and_in_memory_scope": true
  }
}

راهنمای EXPLAIN QUERY PLAN صریحاً قالب خروجی را تشخیصی و قابل تغییر معرفی می‌کند. هجده گروه این تمرین، ویژگی‌های مشاهده‌شدهٔ نسخهٔ ثبت‌شده را هم بررسی می‌کنند؛ آزمون آن رشته‌ها قرارداد قابل‌انتقال برای برنامهٔ تولید نیست. حتی نمایش ts_ms>? در شرح طرح، جای شرط SQL با >= را نمی‌گیرد؛ پذیرشِ زمانِ مرزی را از خودِ نتیجه می‌آزماییم.

مرجع مستقل، فهرست پایتونیِ رکوردها را کامل پیمایش، فیلتر و مرتب می‌کند؛ از اندیس یا متن طرح کمک نمی‌گیرد. بیست‌وهفت ترکیبِ حسگر و بازه، یک گروه از هجده گروه‌اند. جواب دستی، مرز بازه، تکرار زمان، ستون برچسب و ثابت ماندن جدول هم بررسی می‌شوند. این کار با آزمون تعداد ردیف در JOIN و قرارداد صفحه‌بندی مرکب مسئلهٔ متفاوتی را می‌سنجد.

منابع در ۲۵ مهر ۱۴۰۵ خوانده شدند؛ آخرین بازنگریِ صفحهٔ EQP مه ۲۰۲۵ و صفحهٔ برنامه‌ریز مارس ۲۰۲۶ است. مستندات Python نسخهٔ 3.14.8 از اجرای 3.14.5 جداست. SQLite 3.50.4 قدیمیِ محیط، توصیهٔ استقرار نیست؛ آزمایشِ حافظه‌ای و ترتیبیِ حاضر، مشکل هم‌زمانیِ WAL یا آمادگی عملیاتی موتور را آزمایش و رفع نمی‌کند.

۵. چهار تمرین با پاسخ کامل

پرسش اول: چرا در بازهٔ ۱۰۲۰ تا ۱۰۴۰ چهار ردیف داریم؟ پاسخ: دو زمان ۱۰۲۰ و ۱۰۳۰ داخل بازه‌اند و در هرکدام دو نمونه داریم. زمانِ برابر با کران بالایی، ۱۰۴۰، به‌علت شرط کوچک‌تر کنار گذاشته می‌شود.

پرسش دوم: با افزودن label به SELECT چه اتفاقی افتاد؟ پاسخ: نتیجه به مرجعِ دارای برچسب تطبیق داده شد؛ موتور هنوز اندیس را استفاده کرد، اما ستون label در آن نبود و طرح، پوشانندگی را گزارش نکرد.

پرسش سوم: نبودن مرتب‌سازی موقت یعنی حذف ORDER BY مجاز است؟ پاسخ: خیر؛ ترتیبِ درخواست همچنان با ORDER BY تعریف می‌شود. برنامه‌ریز در این مشاهده آن را از ترتیب اندیس تأمین کرده است؛ حذف بند، قرارداد پرس‌وجو را عوض می‌کند.

پرسش چهارم: موفقیت هجده گروه ثابت می‌کند برنامه سریع‌تر شده؟ پاسخ: خیر؛ نتیجهٔ fixture و طرحِ موتور ثبت‌شده بررسی شده‌اند. سنجش کارایی به داده، بار نوشتن، محیط و روش اندازه‌گیری جدا نیاز دارد؛ این تمرین زمان‌سنج ندارد.

منابع و مطالعهٔ بیشتر

• SQLite؛ EXPLAIN QUERY PLAN، پیمایش و مرتب‌سازی موقت
• SQLite؛ اندیس چندستونی، پوشاننده و ترتیب خروجی
• Python 3.14؛ sqlite3 و اتصال حافظه‌ای و اتصال پارامترها

بررسی منابع: ۲۵ مهر ۱۴۰۵. تحلیل‌ها و پیشنهادهای اجرایی، جمع‌بندی نویسنده‌اند.