PostgreSQL یا MySQL برای JSON؛ نوع ایندکس نتیجه را دو برابر می‌کند

برای جست‌وجوی مقدار در مسیرهای گوناگون سند JSON، PostgreSQL با ستون jsonb و ایندکس GIN معمولاً نقطهٔ شروع منعطف‌تری است. اگر پرس‌وجوها بیشتر روی چند فیلد شناخته‌شده متمرکزند، MySQL با ستون تولیدشده و ایندکس همان فیلدها انتخاب قابل دفاعی است. در هر دو حالت، نتیجه به عملگر پرس‌وجو، تعداد فیلدهای ایندکس‌شده و سهم نوشتن از بار کاری بستگی دارد.

در آزمون JSON منتشرشدهٔ StaticBlock روی یک میلیون رکورد با PostgreSQL 16.1 و MySQL 8.3.0، نرخ پیکربندی GIN برابر ۴٬۵۲۳ و نرخ پیکربندی ستون مجازی برابر ۲٬۱۳۴ پرس‌وجو در ثانیه گزارش شد؛ یعنی کمی بیش از دو برابر. این عدد نتیجهٔ دو پیکربندی کامل در همان آزمون است، نه اندازه‌گیری اثر مستقل GIN در برابر ستون مجازی. بنابراین تصمیم دربارهٔ پایگاه داده باید از شکل واقعی جست‌وجو و به‌روزرسانی آغاز شود.

مسیرهای متغیر: GIN چه چیزی را جست‌وجو می‌کند؟

اگر برنامه گاهی category، گاهی وضعیت موجودی و گاهی کلیدهای دیگری را در metadata فیلتر می‌کند، ایندکس GIN روی کل ستون jsonb می‌تواند پرس‌وجوی شامل‌بودن کلید و مقدار را در مسیرهای مختلف پوشش دهد. در مثال فرضیِ جدول products، تعریف ایندکس چنین است: CREATE INDEX idx_metadata ON products USING GIN (metadata jsonb_path_ops). پرس‌وجوی SELECT * FROM products WHERE metadata @> '{"category":"electronics"}' ردیف‌هایی را می‌خواهد که این جفت کلید و مقدار را در سند خود دارند.

مستندات jsonb در PostgreSQL میان دو کلاس GIN فرق می‌گذارد: jsonb_ops پیش‌فرض علاوه بر @> از عملگر وجود کلید ? نیز پشتیبانی می‌کند، ولی jsonb_path_ops برای عملگرهای محدودتری طراحی شده و معمولاً ایندکس کوچک‌تر و جست‌وجوی مشخص‌تری برای آن‌ها فراهم می‌کند. اگر شرط اصلی برنامه وجود کلید باشد، همان ایندکس jsonb_path_ops پاسخ مناسبی به آن شرط نیست. در عوض، برای جست‌وجوی شامل‌بودن مقدار در مسیر مشخص، تفاوت ساختار این دو کلاس می‌تواند اندازهٔ ایندکس و کار خواندن را تغییر دهد.

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

فیلد ثابت: ستون تولیدشده در MySQL

برای فیلتری که همیشه روی category اعمال می‌شود، MySQL می‌تواند مقدار اسکالر را از JSON بیرون بکشد و همان خروجی را ایندکس کند. در همان مثال فرضی، می‌توان نوشت: ALTER TABLE products ADD COLUMN category VARCHAR(64) COLLATE utf8mb4_bin GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.category'))) VIRTUAL, ADD INDEX idx_category (category). سپس SELECT * FROM products WHERE category = 'electronics' ایندکس فیلد استخراج‌شده را هدف می‌گیرد. فرض این مثال آن است که مقدار category رشته‌ای کوتاه و بدون فاصلهٔ انتهایی است؛ نوع ستون و قواعد مقایسه باید با دادهٔ واقعی هماهنگ باشند.

راهنمای ایندکس ستون تولیدشدهٔ MySQL می‌گوید مقدار ستون مجازیِ ایندکس‌شده داخل ایندکس ثانویه نگهداری می‌شود و محاسبهٔ آن هنگام INSERT و UPDATE هزینه دارد. اگر ایندکس ساخته نشود، خواندن ستون مجازی مستلزم محاسبهٔ مقدار هنگام بررسی ردیف است. بنابراین انتخاب میان ایندکس کل سند و ایندکس چند فیلد ثابت، انتخاب میان انعطاف جست‌وجو و دامنهٔ داده‌ای است که باید برای هر تغییر نگهداری شود.

تفاوت مهمی میان شرط WHERE category = 'electronics' و شرط JSON_CONTAINS(metadata, '{"category":"electronics"}') وجود دارد: یکسان‌بودن پاسخ این دو در دادهٔ نمونه به معنی یکسان‌بودن مسیر دسترسی نیست. ایندکس روی ستون تولیدشده زمانی مفید است که پرس‌وجو همان ستون یا عبارت سازگار با آن را هدف بگیرد. اگر برنامه شرطش را به شکل دیگری بنویسد، صرفِ وجود ایندکس در طرح جدول ثابت نمی‌کند که موتور آن را در اجرای همان پرس‌وجو به کار برده است.

عضویت در آرایه: ایندکس اسکالر کافی نیست

اگر metadata آرایه‌ای به نام tag_ids داشته باشد، ایندکس category مسئلهٔ جست‌وجوی عضو آن آرایه را حل نمی‌کند. در مثال فرضیِ برچسب عددی 42، PostgreSQL می‌تواند از SELECT * FROM products WHERE metadata @> '{"tag_ids":[42]}' همراه با GIN روی metadata استفاده کند. عبارت متناظر در MySQL به شکل SELECT * FROM products WHERE 42 MEMBER OF (metadata->'$.tag_ids') نوشته می‌شود؛ شرط این است که اعضای آرایه واقعاً عدد باشند، نه رشته‌هایی که شبیه عددند.

راهنمای ایندکس چندمقداری MySQL برای چنین آرایه‌ای ساختاری مانند CREATE INDEX idx_tag_ids ON products ((CAST(metadata->'$.tag_ids' AS UNSIGNED ARRAY))) نشان می‌دهد. این ایندکس برای هر عضو آرایه مدخلی مرتبط با ردیف می‌سازد و موتور می‌تواند آن را برای MEMBER OF، JSON_CONTAINS و JSON_OVERLAPS بررسی کند. پس مقایسهٔ درست برای جست‌وجوی عضو آرایه، GIN با ایندکس چندمقداری است؛ ستون مجازیِ اسکالر نمایندهٔ توان MySQL در این الگو نیست.

هزینهٔ این انتخاب به اندازهٔ آرایه نیز بستگی دارد. ردیفی با اعضای بیشتر، مدخل‌های بیشتری در ایندکس چندمقداری ایجاد می‌کند و تغییر همان آرایه ممکن است کار نگهداری بیشتری داشته باشد. در سوی دیگر، GIN روی کل سند برای مسیرهایی خارج از tag_ids نیز داده نگه می‌دارد. اگر برنامه تنها یک آرایه را جست‌وجو می‌کند، محدودهٔ پوشش ایندکس به اندازهٔ سرعت یک پرس‌وجوی نمونه اهمیت دارد.

تغییر مکرر مقدار: مزیت مشروط به‌روزرسانی جزئی

برای داده‌ای که مرتب تغییر می‌کند، نرخ جست‌وجو به تنهایی کافی نیست. در مثال فرضی، PostgreSQL می‌تواند وضعیت موجود را با UPDATE products SET metadata = jsonb_set(metadata, '{status}', '"done"'::jsonb) WHERE id = 42 تغییر دهد. شکل مشابه در MySQL عبارت است از UPDATE products SET metadata = JSON_SET(metadata, '$.status', 'done') WHERE id = 42. هر دو دستور از دید برنامه یک مسیر را هدف می‌گیرند، اما صرفِ استفاده از تابع JSON به معنی نوشتن فیزیکی فقط همان چند بایت نیست.

مستندات نوع JSON در MySQL می‌گوید به‌روزرسانی جزئی درجا وقتی ممکن است که ستون از نوع JSON باشد، تابعی مانند JSON_SET روی همان ستون عمل کند، مقدار موجود جایگزین شود و مقدار تازه از فضای موجود بزرگ‌تر نباشد؛ فضای آزادمانده از تغییرهای قبلی نیز می‌تواند استثنا ایجاد کند. افزودن کلید تازه یا جایگزینی با مقدار بزرگ‌تر ممکن است این بهینه‌سازی را از دسترس خارج کند. این قابلیت برای تغییرهای کوچک و پرتکرار مهم است، اما هزینهٔ نگهداری ایندکس‌های وابسته به داده همچنان در تصمیم باقی می‌ماند.

عدد آزمون را به کدام انتخاب می‌توان تعمیم داد؟

دو پیکربندی آزمون با تنظیمات حافظه و ابزارهای اتصال متفاوت اجرا شده‌اند. در بخش JSON، شرط PostgreSQL از @> و شرط MySQL از JSON_CONTAINS روی کل metadata استفاده می‌کند، در حالی که جدول نتیجه سمت MySQL را «ستون مجازی» می‌نامد. تعریف آن ستون، DDL ایندکس و برنامهٔ اجرای پرس‌وجو ارائه نشده است؛ از این رو نمی‌توان از جدول نتیجه فهمید که همان شرط MySQL واقعاً از ایندکس ستون مجازی بهره برده یا تمام اختلاف از نوع ایندکس آمده است.

برای انتخاب معماری، سه بار کاری را جداگانه بسنجید: فیلتر در مسیرهای متغیر، فیلتر روی فیلد ثابت و عضویت در آرایه. هر کدام ایندکس متناظر خود را می‌خواهد و هزینهٔ نوشتن متفاوتی دارد. اگر تغییر وضعیت یا قیمت بخش مهمی از بار است، زمان UPDATE و اندازهٔ ایندکس را نیز کنار نرخ خواندن قرار دهید؛ برتری یک پیکربندی در فیلتر category به‌تنهایی پاسخ این بخش از مسئله نیست.

بیشتر بخوانید:

• GPT-6 Sol و Luna آمدند؛ اختلاف قیمت ۲۰ برابری برای دو نوع کار
• Cloudflare R2 یا Amazon S3؛ خروج داده می‌تواند صورت‌حساب را عوض کند
• Paymob سی‌وپنج میلیون دلار گرفت؛ درآمد خلیج فارس هفت‌برابر شد