پرس و جو از راه دور کارآمد در mysql

در یکی از برنامه های کاربردی من در محل کار ، ما عرض جغرافیایی و طول عمر تقریباً 6 میلیون خواص را در ایالت تگزاس ذخیره می کنیم. اغلب اوقات کاربران ما نیاز به جستجوی خواصی در نزدیکی یکدیگر دارند.

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

ما می توانیم از عملکرد ST_DISTANCE_SPHERE MYSQL استفاده کنیم تا این محاسبه آسان شود ، اما چند کار اضافی وجود دارد که می توانیم انجام دهیم تا این نوع جستجو بسیار سریعتر انجام شود.

#محاسبه فاصله در mysql

یک پست وبلاگ عالی در وب سایت محکم وجود دارد که مفهوم فرمول های Haversine و عملکرد ST_DISTANCE_SPHERE را معرفی می کند. اگر قبلاً با این مفهوم آشنا نیستید ، این یک خواندن عالی است.

tl ؛ dr این است: MySQL 5. 7 ST_DISTANCE_SPHERE را معرفی کرد ، یک عملکرد بومی برای محاسبه فاصله بین دو نقطه روی زمین.

برای به دست آوردن فاصله بین دو نقطه ، شما عملکرد را با دو نقطه به عنوان آرگومان می نامید:

این مسافت را در متر به شما می دهد ، که می توانید در صورت لزوم به مایل تبدیل شوید:

#فیلتر عرفانی از راه دور

مسلح با این عملکرد ، می توانید از آن برای فیلتر کردن نتایج بیش از مثال استفاده کنید. 1 مایل از یک نقطه هدف:

بر اساس "نقطه مورد علاقه" ما (-97. 745363 ، 30. 324014) ، می توانیم تمام سوابق دیگر را که در شعاع 1 مایل قرار دارند ، بفهمیم.

این یک شروع عالی است ، زیرا به شما نتیجه می دهد که 100 ٪ صحیح هستند ، که بسیار مهم است! نکته منفی این روش این است که بسیار کند است. MySQL نمی تواند از این شاخص ها در این پرس و جو استفاده کند ، زیرا ستون ها در یک محاسبه پنهان هستند.

هنگامی که تعداد نسبتاً کمی از سوابق را بدست آورید ، این راه حل دیگر قابل استفاده نخواهد بود. اجرای این در ما~6 میلیون ردیف بیش از 10 ثانیه طول می کشد!

در حال اجرا در مورد این پرس و جو توضیح می دهد که چرا:

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

بیایید ببینیم چگونه می توانیم این کار را برطرف کنیم.

#QQUYING در برابر ثابت

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

اصل در اینجا به حداقل رساندن محاسباتی است که پایگاه داده باید انجام دهد ، در هر کجا که می توانید. متأسفانه این یکی برای ما کار چندانی نخواهد کرد!

#اضافه کردن یک جعبه محدود

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

این چیزی است که ما اضافه می کنیم:

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

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

در اینجا عملکرد PHP وجود دارد که ما برای محاسبه یک جعبه محدود ، با توجه به عرض جغرافیایی ، طول جغرافیایی و فاصله در مایل استفاده خواهیم کرد.(این یک اقتباس از یک کلاس توسط آنتونی مارتین است.)

توجه داشته باشید که نیازی به درک همه ریاضیات در اینجا نیست (خیلی زیاد است!) ، فقط این که این عملکرد یک جعبه محدود را تولید می کند.

با استفاده از این عملکرد یاور ، می توانیم تصویر جعبه محدود را در بالا تولید کنیم و با استفاده از دامنه پرس و جو لاراول ، آن را به پرس و جو خود اضافه کنیم:

با استفاده از این دامنه اکنون SQL زیر را تولید می کند:

ما برای نزدیک شدن از کادر اتصال ناخالص استفاده می کنیم و سپس با استفاده از ST_DISTANCE_SPHERE برای دقیق شدن استفاده می کنیم.

جعبه محدودیت برخی از موارد مثبت کاذب را به ما می دهد (در تصویر زیر) ، اما ST_DISTANCE_SPHERE گران قیمت آن را پس از MySQL مجموعه داده های بسیار کوچکتر را تمیز می کند.

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

بیایید بعد از افزودن آن فهرست ، به خروجی توضیح نگاهی بیندازیم:

ما در حال حاضر در وضعیت بسیار بهتری قرار داریم و احتمالاً می توانیم اینجا متوقف شود و خوب باشد. ما می بینیم که نوع دسترسی از همه به محدوده تغییر کرده است ، و MySQL قادر به استفاده از GEO_DATA_LATITUDE_INDEX جدید است که اضافه کرده ایم. متوجه خواهید شد که شماره ای که MySQL فکر می کند ممکن است بازرسی کند از چند میلیون به آن کاهش یافته است~110،000

یک پیروزی در اطراف!

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

اگر این به اندازه کافی سریع برای نیازهای شما باشد ، مطمئناً می توانید در اینجا متوقف شوید! اما اگر اینطور نیست ، یک چیز دیگر وجود دارد که می توانیم امتحان کنیم.

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

ما می خواهیم سعی کنیم راه خود را در مورد مسئله MySQL انجام دهیم که فقط قادر به استفاده مؤثر از یک قسمت از شاخص مرکب هستیم.

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

اولین کاری که ما انجام خواهیم داد اضافه کردن یک ستون تولید شده ذخیره شده است. ستونهای تولید شده ذخیره شده ستون هایی هستند که در واقع روی دیسک وجود دارند و می توانند فهرست بندی شوند ، اما توسط خود MySQL به روز نگه داشته می شوند. می توانید آنها را به عنوان شبیه به ویژگی های محاسبه شده فکر کنید.

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

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

1000 یک عامل کاملاً دلخواه است. اگر کاربران شما در جستجوی شعاع بسیار گسترده هستند ، باید عامل خود را کاهش دهید. کاربران ما در درجه اول از 1 تا 3 مایل خواص را جستجو می کنند.

این ستون جدید عرض جغرافیایی را می گیرد ، آن را 1000 ضرب می کند و آن را طبقه می کند. می توان آن را به عنوان یک عدد صحیح متوسط ذخیره کرد.

اکنون ما می خواهیم به جای بررسی محدوده ، از این پرس و جو خود استفاده کنیم تا از این پرس و جو استفاده کنیم.

ما می خواهیم جعبه محدودیت خود را مانند گذشته محاسبه کنیم ، اما پس از آن ، عرض جغرافیایی را برای مطابقت با ستون جدید LAT_1000_FLOOR تغییر خواهیم داد.

متغیر جعبه $ ما اکنون چیزی شبیه به این است:

این بسیار مفید نیست ، زیرا به نظر می رسد که ما هنوز می خواهیم با استفاده از یک اپراتور ، Minlat و MaxLat را اعمال کنیم. این جایی است که فریب وارد می شود!

از آنجا که این یک محدوده محدود ، محدود و نسبتاً کوچک است ، ما می توانیم به صورت دستی دامنه را پر کنیم و به جای بین استفاده کنیم.

MySQL در (1 ، 2 ، 3) نمایش داده می شود بسیار متفاوت از 1 تا 3. برخی از بانکهای اطلاعاتی در () به عنوان یک کوتاه برای چندین بیانیه رفتار می کنند ، اما MySQL مقادیر را مرتب می کند و قادر به استفاده از یک جستجوی باینری سریع است.

با این دانش می توانیم خودمان دامنه را پر کنیم:

این دامنه امکانات را پر می کند:

اکنون تنها کاری که ما باقی مانده است این است که آن را به پرس و جو متصل کنیم!

دامنه اکنون SQL زیر را تولید می کند:

بیایید نگاهی به توضیحات بیندازیم و ببینیم که آیا آنچه را که به آن امیدوار هستیم دریافت می کنیم:

می بینید که این حتی بهتر از نتیجه قبلی ما است! پیش از این MySQL فکر می کرد که باید بررسی کند~110،000 ردیف برای به دست آوردن نتیجه ، اکنون فکر می کند که فقط باید 6،600 را بررسی کند! به یاد داشته باشید که در ابتدا ، بدون فهرست ، MySQL حدود 5 میلیون حدس می زد ، اکنون ما در 6،600 با یک شاخص واحد ، یک ستون محاسبه شده و برخی از کار در سمت برنامه هستیم.

اما آیا در واقع ما در مقابل A تفاوت ایجاد می کند؟بیایید با استفاده از بین برای دیدن توضیح دهیم:

نتیجه زیر را می گیریم:

می بینید که MySQL قادر به استفاده کامل از شاخص نیست و تعداد ردیف های تخمین زده شده برای بازرسی از آن به عقب می رود~100000

شما باید برنامه خود را ارزیابی کنید که آیا 1000 در هنگام ساخت ترانشه های خود عامل مناسبی است یا خیر. برای کاربرد ما ، بیشتر افراد معمولاً در شعاع 1 تا 3 مایل جستجو می کنند ، بنابراین ترانشه های باریک اهداف ما را به خوبی انجام می دهند. اگر کاربران شما به عنوان مثال جستجو می کنند. شعاع 20 مایل ، پس از آن ممکن است ترانس های بسیار گسترده تری را در نظر بگیرید ، به عنوان مثال ، یک عامل 250 یا حتی 100.

همانطور که در مورد بانک اطلاعاتی همه چیز ، شما باید این روش ها را با داده ها و پیکربندی های خود تنظیم کنید ، اما نکته بسیار مهمی که باید در نظر داشته باشید این است که می توانید از فهرست ها استفاده کنید تا به سرعت حقیقت را تقریبی کنید.

دانستن کمی در مورد نحوه کار پایگاه داده شما می تواند هنگام طراحی پرس و جوهای پیچیده تر ، یک پا بزرگ را به شما بدهد!

#addendum: شاخص های مکانی

بعد از ارسال این مطلب ، چند سؤال بسیار خوب در امتداد "فهرست های مکانی؟" دریافت کردم؟

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

اگر واقعاً نیازهای مکانی زیادی دارید ، حتی ممکن است به جای MySQL Postgres را در نظر بگیرید! من نمی توانم خیلی با آن صحبت کنم زیرا من اغلب از پس از آن استفاده نمی کنم.

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

اولین کاری که من کردم اضافه کردن یک ستون نقطه به جدول:

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

با حرکت به پرس و جو خود ، ما دوباره از روش BoundingBox استفاده خواهیم کرد تا جعبه خود را بدست آوریم و سپس آن را به چند عملکرد ویژه MySQL منتقل کنیم.

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

ما هنوز باید از ST_DISTANCE_SPHERE نهایی استفاده کنیم تا دقت مورد نظر خود را به ما ارائه دهیم.

اجرای این کار در 6 میلیون ردیف ما در حدود 50-60ms به دست می آوریم. خیلی خوب!

این حدود 20-30 متر کندتر از نسخه فوق است.

با نگاهی به توضیح می توانیم ببینیم که از فهرست جدید استفاده می شود.

می بینید که MySQL حدس می زند که باید بازرسی کند~36000 ردیف ، که بسیار خوب است ، اما به اندازه نسخه Tranches + Range از قبل خوب نیست. برای مرجع ، در اینجا توضیحات از نسخه Tranches است:

شاخص Tranches + Range ما قادر به محدود کردن ردیف ها به~7000 به جای~36000همچنین توجه داشته باشید که شاخص مرکب ما دارای key_len 10 ، در مقابل شاخص مکانی key_len از 34 است ، به این معنی که شاخص مرکب ما بسیار فشرده تر است.

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

با تشکر از خواندن! نام من هارون است و من یک مربی توسعه دهنده در Planetscale هستم - یک شرکت پایگاه داده MySQL بدون سرور.

تمرکز پروژه اصلی من Hammerstone است ، جایی که ما برای برنامه های Laravel و Rails شما مؤلفه هایی می سازیم. من و شریک تجاری من یک پادکست انجام می دهیم که در مورد ساخت یک شرکت نرم افزاری صحبت می کنیم.