به یاد بیاورید که شاخص مکانی یکی از سه ویژگی اصلی یک بانک اطلاعاتی فضایی است. ایندکس ها با استفاده از یک پایگاه داده فضایی برای مجموعه داده های بزرگ امکان پذیر هستند. بدون نمایه سازی ، هرگونه جستجو برای یک ویژگی به "اسکن متوالی" از هر رکورد در پایگاه داده نیاز دارد. نمایه سازی با سازماندهی داده ها در یک درخت جستجو که می تواند به سرعت در حال گذر از یک رکورد خاص باشد ، جستجو می کند.
شاخص های مکانی یکی از بزرگترین دارایی های Postgis است. در مثال قبلی ساختمان های فضایی نیاز به مقایسه جداول کامل با یکدیگر دارند. این می تواند بسیار پرهزینه باشد: پیوستن به دو جدول از 10،000 رکورد هر یک بدون شاخص به 100،000،000 مقایسه نیاز دارد. با شاخص ها ، هزینه می تواند به میزان 20،000 مقایسه باشد.
پرونده بار داده ما قبلاً شامل شاخص های مکانی برای همه جداول است ، بنابراین برای نشان دادن اثربخشی شاخص ها ، ابتدا باید آنها را حذف کنیم.
بیایید بدون شاخص مکانی ما یک پرس و جو در NYC_CENSUS_BLOCKS اجرا کنیم.
اولین قدم ما حذف فهرست است.
بیانیه Drop Index یک فهرست موجود را از سیستم پایگاه داده رها می کند. برای اطلاعات بیشتر ، به مستندات PostgreSQL مراجعه کنید.
اکنون ، کنتور "زمان بندی" را در گوشه سمت راست پایین پنجره پرس و جو pgadmin تماشا کنید و موارد زیر را اجرا کنید. جستجوی پرس و جو ما از طریق هر بلوک سرشماری به منظور شناسایی بلوک هایی که حاوی توقف های مترو است که با "B" شروع می شود ، جستجو می کند.
جدول NYC_CENSUS_BLOCKS بسیار کوچک است (فقط چند هزار رکورد) بنابراین حتی بدون یک فهرست ، پرس و جو فقط 300 میلی ثانیه در رایانه تست من طول می کشد.
حالا شاخص مکانی را به داخل اضافه کنید و دوباره پرس و جو را اجرا کنید.
بند استفاده از GIST به PostgreSQL می گوید از ساختار شاخص عمومی (GIST) هنگام ساخت شاخص استفاده می کند. اگر خطایی را دریافت می کنید که به نظر می رسد خطا: Row Index به 11340 بایت نیاز دارد ، حداکثر اندازه 8191 هنگام ایجاد شاخص خود است ، احتمالاً از اضافه کردن بند GIST غفلت کرده اید.
در رایانه تست من زمان به 50 میلی ثانیه کاهش می یابد. هرچه جدول شما بزرگتر باشد ، بهبود سرعت نسبی یک پرس و جو شاخص بیشتر خواهد بود.
15. 1شاخص های مکانی چگونه کار می کنند
شاخص های استاندارد پایگاه داده بر اساس مقادیر ستون که در آن فهرست بندی می شوند ، یک درخت سلسله مراتبی ایجاد می کنند. شاخص های مکانی کمی متفاوت هستند - آنها قادر به نمایه کردن ویژگی های هندسی نیستند و در عوض جعبه های محدود کننده ویژگی ها را فهرست بندی می کنند.
در شکل بالا ، تعداد خطوطی که از ستاره زرد عبور می کنند یکی ، خط قرمز است. اما جعبه های محدود از ویژگی هایی که از جعبه زرد استفاده می کنند ، دو مورد قرمز و آبی است.
روشی که پایگاه داده به طور مؤثر به این سؤال پاسخ می دهد "چه خطوطی از ستاره زرد عبور می کنند" این است که ابتدا به این سؤال پاسخ دهید "چه جعبه هایی از جعبه زرد استفاده می کنند" با استفاده از شاخص (که بسیار سریع است) و سپس محاسبه دقیقی از "چه خطوط تقاطع می کندستاره زرد "فقط برای آن ویژگی هایی که توسط اولین تست برگشته اند.
برای یک جدول بزرگ ، این سیستم "دو پاس" برای ارزیابی شاخص تقریبی ابتدا ، سپس انجام یک آزمایش دقیق می تواند میزان محاسبات لازم برای پاسخ به یک پرس و جو را کاهش دهد.
هر دو Postgis و Oracle فضایی همان ساختار شاخص فضایی "R-Tree" را دارند. درختان R داده ها را در مستطیل ها ، و زیر مجموعه ها ، و مستطیل های فرعی و غیره تجزیه می کنند. این یک ساختار شاخص خود تعیین کننده است که به طور خودکار چگالی داده های متغیر ، مقادیر مختلف همپوشانی جسم و اندازه شی را کنترل می کند.
15. 2توابع فهرست بندی شده فضایی ¶
در صورت وجود ، فقط یک زیر مجموعه از توابع به طور خودکار از یک شاخص مکانی استفاده می کنند.
چهار مورد اول مواردی هستند که معمولاً در نمایش داده شد ، و ST_DWOWTIN برای انجام "در یک فاصله" یا "در یک شعاع" بسیار مهم است در حالی که هنوز هم عملکردی را از این شاخص دریافت می کند.
به منظور افزودن شتاب شاخص به سایر توابع موجود در این لیست (معمولاً ST_RELATE) یک بند فقط شاخص را اضافه کنید که در زیر آن قرار دارد.
15. 3فقط نمایش داده شد
بسیاری از توابع متداول در Postgis (ST_CONTINS ، ST_INTERSECTS ، ST_DWWWWIN و غیره) شامل یک فیلتر شاخص به طور خودکار است. اما برخی از کارکردها (به عنوان مثال ، ST_RELATE) شامل یک فیلتر شاخص نیستند.
برای انجام یک جستجوی جعبه محدود با استفاده از شاخص (و بدون فیلتر) ، از اپراتور && استفاده کنید. برای هندسه ها ، اپراتور && به معنای "جعبه های محدود کننده همپوشانی یا لمس" به همان روشی است که برای اعداد = عملگر به معنی "مقادیر یکسان" است.
بیایید یک پرس و جو فقط شاخص را برای جمعیت "دهکده غربی" با یک پرس و جو دقیق تر مقایسه کنیم. با استفاده از پرس و جو فقط فهرست ما مانند موارد زیر به نظر می رسد:
اکنون بیایید با استفاده از عملکرد دقیق تر ST_INTERSECTS ، همان پرس و جو را انجام دهیم.
یک جواب بسیار پایین تر! اولین پرس و جو خلاصه ای از هر بلوکی که جعبه محدودش در جعبه محدودیت محله در آن قرار دارد. پرس و جو دوم فقط آن دسته از بلوک هایی را که از خود محله عبور می کنند خلاصه کرد.
15. 4تجزیه و تحلیل
برنامه ریز پرس و جو PostgreSQL به طور هوشمندانه انتخاب می کند که چه زمانی از شاخص ها برای ارزیابی یک پرس و جو استفاده کند یا نه. بر خلاف شهود، انجام جستجوی شاخص همیشه سریعتر نیست: اگر قرار باشد جستجو همه رکوردهای جدول را برگرداند، پیمایش درخت شاخص برای به دست آوردن هر رکورد در واقع کندتر از خواندن متوالی کل جدول از ابتدا خواهد بود..
دانستن اندازه مستطیل پرس و جو برای تعیین اینکه آیا یک پرس و جو تعداد زیادی یا تعداد کم رکورد را برمی گرداند کافی نیست. در زیر، مربع قرمز کوچک است، اما رکوردهای بسیار بیشتری نسبت به مربع آبی باز خواهد کرد.
برای اینکه بفهمد با چه وضعیتی سر و کار دارد (خواندن بخش کوچکی از جدول در مقابل خواندن بخش بزرگی از جدول)، PostgreSQL آماری در مورد توزیع داده ها در هر ستون جدول نمایه شده نگه می دارد. به طور پیش فرض، PostgreSQL آمار را به طور منظم جمع آوری می کند. اما اگر در مدت زمان کوتاهی محتویات جدول خود را به شدت تغییر دهید، آمار به روز نخواهد بود.
برای اطمینان از مطابقت آماری با محتوای جدول، عاقلانه است که پس از بارگیری و حذف انبوه داده ها در جداول، دستور ANALYZE را اجرا کنید. این سیستم آمار را مجبور می کند که داده ها را برای تمام ستون های نمایه شده شما جمع آوری کند.
دستور ANALYZE از PostgreSQL می خواهد تا جدول را طی کند و آمار داخلی خود را که برای تخمین طرح پرس و جو استفاده می شود به روز کند (تحلیل طرح پرس و جو بعداً مورد بحث قرار خواهد گرفت).
15. 5. جارو برقی ¶
شایان ذکر است که فقط ایجاد یک شاخص برای اجازه دادن به PostgreSQL برای استفاده مؤثر از آن کافی نیست. جاروبرقی باید هر زمان که تعداد زیادی UPDATE، INSERT یا DELETE بر روی یک جدول صادر شود، انجام شود. دستور VACUUM از PostgreSQL می‌خواهد تا هر فضای استفاده‌نشده در صفحات جدول را که از به‌روزرسانی‌ها یا حذف‌ها در رکوردها باقی مانده است، بازیابی کند.
جاروبرقی برای اجرای کارآمد پایگاه داده به قدری حیاتی است که PostgreSQL به طور پیش‌فرض یک تسهیلات “autovacuum” را فراهم می‌کند.
هم جاروبرقی (فضا را بازیابی می کند) و هم آنالیز (آمار را به روز می کند) روی جداول خود در فواصل معقولی که بر اساس سطح فعالیت تعیین می شود، جارو برقی کنید. در حالی که این برای پایگاه‌های داده بسیار تراکنشی ضروری است، توصیه نمی‌شود که پس از افزودن شاخص‌ها یا داده‌های بارگذاری انبوه منتظر اجرای خلاء خودکار بمانید. هر زمان که یک بروزرسانی دسته ای بزرگ انجام می شود، باید VACUUM را به صورت دستی اجرا کنید.
جاروبرقی و تجزیه و تحلیل پایگاه داده می تواند به طور جداگانه در صورت نیاز انجام شود. صدور دستور VACUUM آمار پایگاه داده را به روز نمی کند. به همین ترتیب، صدور دستور ANALYZE ردیف های جدول استفاده نشده را بازیابی نمی کند. هر دو دستور را می توان در برابر کل پایگاه داده، یک جدول یا یک ستون واحد اجرا کرد.
15. 6. لیست توابع¶
geometry_a && geometry_b: اگر کادر مرزی A با B همپوشانی داشته باشد، TRUE را برمی‌گرداند.
geometry_a = geometry_b: اگر کادر محدود A با B برابر باشد، TRUE را برمی‌گرداند.
ST_Intersects(geometry_a، geometry_b): اگر هندسه/جغرافیا "به صورت فضایی متقاطع شوند" - (هر قسمتی از فضا را به اشتراک بگذارند) TRUE را برمی‌گرداند و اگر اینطور نباشد FALSE (از هم گسسته هستند).
