نمایه سازی JSON در MySQL

MySQL با انتشار MySQL 5. 7. 8 نوع داده JSON را در اواسط سال 2015 به ما داد. از آن زمان ، از آن به عنوان راهی برای فرار از تعاریف ستون سفت و سخت و ذخیره اسناد JSON از همه شکل ها و اندازه ها استفاده شده است: سیاهههای مربوط به حسابرسی ، تنظیمات پیکربندی ، بارهای شخص ثالث ، زمینه های تعریف شده توسط کاربر و موارد دیگر.

اگرچه MySQL برای خواندن و نوشتن داده های JSON به ما کارکردهایی می دهد ، اما به سرعت چیزی را کشف خواهید کرد که به طور آشکار از دست رفته است: امکان فهرست مستقیم ستون های JSON خود.

در سایر بانکهای اطلاعاتی ، بهترین راه برای فهرست بندی مستقیم یک ستون JSON معمولاً از طریق نوع شاخصی است که به عنوان یک شاخص معکوس تعمیم یافته یا GIN به طور خلاصه شناخته می شود. از آنجا که MySQL شاخص های جین را ارائه نمی دهد ، ما نمی توانیم به طور مستقیم یک سند JSON ذخیره شده را به طور مستقیم فهرست بندی کنیم. همه از بین نمی روند ، زیرا MySQL راهی به ما می دهد تا به طور غیرمستقیم بخش هایی از اسناد JSON ذخیره شده ما را فهرست بندی کنیم.

بسته به نسخه MySQL که از آن استفاده می کنید ، دو گزینه برای نمایه سازی JSON دارید. در MySQL 5. 7 باید یک ستون تولید شده متوسط ایجاد کنید ، اما با شروع از MySQL 8. 0. 13 ، می توانید مستقیماً یک شاخص کاربردی ایجاد کنید.

بیایید با یک جدول مثال که برای ورود به سیستم اقدامات مختلفی که در یک برنامه انجام شده است استفاده کنیم.

در آن جدول ، اسناد JSON را که دارای این شکل هستند وارد می کنیم:

به عنوان مثال ، ما کلید ایمیل را در داخل شیء درخواست می کنیم. این به کاربران (داستانی) ما اجازه می دهد تا فرم های ارسال شده توسط افراد خاص را به سرعت پیدا کنند.

بیایید نگاهی به اولین گزینه ما برای فهرست بندی بیندازیم: ستون های تولید شده.

فهرست بندی JSON از طریق یک ستون تولید شده #

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

از آنجا که ما در حال تلاش برای فهرست بندی قسمت درخواست هستیم. در ستون Properties ، ستون تولید شده ما از اپراتور استخراج UNQUOTING JSON برای جمع آوری مقدار استفاده می کند.

برای تأیید اینکه بیان خود را به درستی شکل داده ایم ، ابتدا یک جمله انتخابی را اجرا خواهیم کرد و نتایج را بازرسی می کنیم.

The ->>اپراتور یک اپراتور استخراج بی نظیر و بی نظیر است و آن را معادل JSON_UNQUOTE (JSON_EXTRACT (ستون ، مسیر)) می کند. ما می توانستیم عبارت SELECT قبلی را با استفاده از Longhand بنویسیم و همان نتیجه را بدست آوریم.

کدام روش را انتخاب می کنید موضوع اولویت شخصی است!

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

The first part of the ALTER statement should look very familiar, we’re adding a column named email and defining it as a VARCHAR(255) . In the latter half of the statement we declare that the column is generated and that it should always be equal to the result of the expression properties->>"$ . request. email".

ما می توانیم تأیید کنیم که ستون ما با انتخاب آن به عنوان هر ستون دیگر اضافه شده است.

خواهید دید که MySQL اکنون این ستون را برای ما حفظ می کند. اگر بخواهیم مقدار JSON را به روز کنیم ، مقدار ستون تولید شده نیز تغییر می کند.

اکنون که ستون تولید شده خود را در جای خود قرار داده ایم ، می توانیم مانند هر ستون دیگری ، یک شاخص به آن اضافه کنیم.

خودشه! اکنون کلید درخواست را در ستون ویژگی های JSON خود فهرست کرده اید. بیایید تأیید کنیم که MySQL از این فهرست برای سرعت بخشیدن به نمایش داده هایی که در ایمیل فیلتر می شوند استفاده می کند.

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

شاخص های ستون تولید شده و بهینه ساز #

بهینه ساز MySQL یک موجود قدرتمند و مرموز است. وقتی ما به MySQL دستور می دهیم ، ما به آن می گوییم که چه می خواهیم ، نه چگونه آن را بدست آوریم. اغلب اوقات MySQL پرس و جو ما را می گیرد و آن را کمی بازنویسی می کند ، که این یک چیز خوب است! ده ها هزار ساعت در طول ده ها سال به کارآیی و کارآمد بهینه سازی رسیده است.

هنگامی که صحبت از شاخص ها در ستون های تولید شده است ، بهینه ساز می تواند از طریق الگوهای دسترسی مختلف "از طریق" ببیند تا از شاخص زیرین استفاده شود.

We defined an index on email , which is a generated column based on the expression properties->>"$ . request. email". ما قبلاً ثابت کرده ایم که این شاخص هنگام پرس و جو در مورد ستون ایمیل استفاده می شود. جالب تر اینکه این بهینه ساز به اندازه کافی هوشمند است که در صورت فراموش کردن پرس و جو در برابر ستون ایمیل نامگذاری شده ، به ما کمک می کند!

در پرس و جو زیر ، ما با نام به ستون تولید شده دسترسی نداریم ، بلکه در عوض از اپراتور استخراج Shorthand JSON استفاده می کنیم.(برخی از ردیف های حذف شده از بیانیه توضیح برای کوتاه بودن.)

حتی اگر ما به صراحت به ستون با نام خود خطاب نکردیم ، بهینه ساز می فهمد که یک فهرست در یک ستون تولید شده بر اساس آن عبارت وجود دارد و از استفاده از این فهرست استفاده می کند. با تشکر بهینه ساز!

ما می توانیم تأیید کنیم که این مورد برای Longhand نیز وجود دارد.

باز هم ، بهینه ساز "بیان" ما را "می خواند" و از فهرست ایمیل استفاده می کند.

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

اگر از نزدیک نگاه کنید ، خواهید دید که Optimizer پرس و جو ما را بازنویسی کرده و مقایسه برابری را با مرجع ستون فهرست بندی شده تغییر داده است. این امر به ویژه در صورتی مفید است که نتوانید الگوی دسترسی را کنترل کنید زیرا پرس و جو از یک بسته شخص ثالث در پایگاه کد خود صادر می شود ، یا به دلایلی دیگر قادر به تغییر این بخش از کد خود نیستید.

اگر بیان زیرین خیلی نزدیک مطابقت نداشته باشد ، بهینه ساز قادر به استفاده از شاخص نخواهد بود ، بنابراین هنگام ایجاد ستون تولید شده خود حتماً مراقب باشید. مستندات MySQL استفاده بهینه ساز از شاخص های ستون تولید شده را با جزئیات بیشتر توضیح می دهد.

فهرست های عملکردی #

با شروع MySQL 8. 0. 13 ، شما می توانید مرحله میانی ایجاد یک ستون تولید شده را پرش کنید و آنچه را "شاخص کاربردی" می نامند ، ایجاد کنید. مستندات MySQL این قطعات کلیدی کاربردی را صدا می کند.

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

چند GOTCHA با شاخص های عملکردی وجود دارد ، به خصوص وقتی استفاده از آنها برای JSON است.

خوب است که شاخص JSON ما را مانند این ایجاد کنیم:

اما اگر آن را امتحان کنید ، یک خطای نامطبوع به دست می آورید:

پس اینجا چه خبر است؟در مثالهای قبلی ما ، ما مسئول ایجاد ستون تولید شده بودیم و آن را به عنوان Varchar (255) اعلام کردیم که توسط MySQL به راحتی قابل توصیف است.

با این حال ، هنگامی که ما از یک شاخص کاربردی استفاده می کنیم ، MySQL قصد دارد آن ستون را برای ما بر اساس نوع داده ای که از آن استفاده می کند ، ایجاد کند. JSON_UNQUOTE یک مقدار LongText را برمی گرداند ، که قادر به فهرست بندی نیست.

خوشبختانه ، پیام خطا ما را در جهت درست نشان می دهد: ما باید مقدار خود را به نوع دیگری برسانیم که از نظر طولانی نباشد. ریخته گری با استفاده از عملکرد Char به MySQL می گوید که نوع داده Varchar را استنباط کند.

اکنون که شاخص را اضافه کردیم ، خواهیم دید که آیا با اجرای توضیح کار می کند.

متأسفانه ، شاخص ما به هیچ وجه مورد توجه قرار نمی گیرد ، بنابراین ما هنوز از جنگل خارج نیستیم.

مگر در مواردی که مشخص نشده باشد ، ریختن یک مقدار به یک رشته ، جمع آوری را به UTF8MB4_0900_AI_CI تنظیم می کند. از طرف دیگر توابع استخراج JSON ، رشته ای را با یک جمع UTF8MB4_BIN بازگرداند. در آنجا مشکل ما نهفته است! از آنجا که جمع آوری بین بیان پرس و جو و شاخص ذخیره شده ناسازگار است ، از شاخص عملکردی جدید ما استفاده نمی شود.

مرحله آخر این است که صریحاً جمع آوری بازیگران را به UTF8MB4_BIN تنظیم کنید.

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

به وضوح شاخص های کاربردی با چند مشکل وجود دارد که برخی از آنها صریح و آسان برای اشکال زدایی هستند و برخی از آنها که نیاز به کمی بیشتر در اسناد دارند.

به یاد داشته باشید که شاخص های عملکردی از ستون های تولید شده پنهان در زیر کاپوت استفاده می کنند. اگر ترجیح می دهید خود ستون تولید شده را کنترل کنید (حتی در MySQL 8. 0. 13 و بعد) این یک رویکرد کاملاً منطقی است!

در حالی که نمایه سازی مستقیم JSON ممکن است در MySQL در دسترس نباشد ، نمایه سازی غیرمستقیم کلیدهای خاص می تواند اکثر موارد استفاده را پوشش دهد.

فقط با JSON متوقف نشوید! می توانید از ستون های تولید شده و شاخص های عملکردی در انواع الگوهای مشترک و سخت برای نمایه سازی استفاده کنید.