أكثر

تحسين MySQL JOIN بناءً على تطابق داخل


لدي جدول بالنقاط والحدود ، وأحاول إضافة معرف الحدود الذي توجد به نقطة. ومع ذلك ، فإن استخدام ملفLEFT JOIN ON داخل (الموقع ، الحدود)يستغرق الأمر حوالي 3.5 ساعة من المباراة 450.000 نقطة مقابل 350 حدًا. هل هناك طريقة لتحسين هذا الانضمام؟

بتفاصيل اكثر:

لدي جدولين في MySQL 5.6 ، يحتوي أحدهما على نقاط ، يتم تخزين كل منهما كملفهدفوالآخر يحتوي على حدود ، يتم تخزين كل منها على هيئة ملفالهندسة:

- جدول المواقع ، حوالي 0.5 مليون نقطة إنشاء مواقع الجدول (المعرف INT (11) ليس مفتاحًا أساسيًا فارغًا ، تعويم خط الطول (11.6) افتراضي NULL ، تعويم خط العرض (10.6) افتراضي NULL ، نقطة طول غير افتراضية " ، borderId INT (11) DEFAULT NULL) ENGINE = MyISAM؛ - ملء حقل lonLat مواقع التحديث SET lonLat = POINTFROMTEXT (CONCAT ('point ('، longitude، "، latitude، ')')) حيث خط الطول ليس فارغًا و خط العرض ليس فارغًا ؛ - إضافة فهرس مكاني على مواقع lonLat ALTER TABLE إضافة فهرس مكاني (lonLat) ؛ - جدول يضم حوالي 350 حدًا دقيقًا ، وبعضها يتداخل مع إنشاء جدول إذا لم يكن خارج الحدود (المعرف INT (11) NOT NULL PRIMARY KEY ، هندسة llgeom NOT NULL) ؛ - إضافة فهرس مكاني على llgeom الحدود: ALTER TABLE "حدود" ADD SPATIAL ('llgeom') ؛

لدي استعلام يقوم بتحديث جدول الموقع بمعرف الحدود الذي تكون نقطة هذا الصف داخله:

تحديث المواقع LEFT JOIN limits ON within (lonLat، llgeom) SET borderaryId = limits.id؛

لاحظ أن كلا من lonLat و llgeom لهما فهارس مكانية بالفعل.

مع حوالي 450.000 نقطة و 350 هندسة ، تعمل على MySQL 5.6 ، يستغرق هذا حوالي 3.5 ساعة. يستغرق إجراء اختبار يقتصر على 14 صفًا فقط حوالي 2.1 ثانية.

إذا قمت بتشغيل EXPLAIN ، فهذا يدل على عدم استخدام الفهرسة:

mysql> شرح تحديث المواقع لحدود JOIN LEFT ON ضمن (lonLat، llgeom) SET borderaryId = limits.id؛ | معرف | select_type | الجدول | اكتب | الممكن_المفاتيح | مفتاح | key_len | المرجع | صفوف | إضافي | | 1 | بسيط | المواقع | الكل | NULL | NULL | NULL | NULL | 451010 | NULL | | 1 | بسيط | حدود | الكل | NULL | NULL | NULL | NULL | 353 | باستخدام أين ؛ استخدام المخزن المؤقت للانضمام (Block Nested Loop) |

وهذا يبين أناكتبيكونالكلفي كلتا الحالتين ، وهو "أسوأ نوع صلة ويشير عادةً إلى عدم وجود فهارس مناسبة في الجدول."

هل هناك بعض التحسينات التي يمكنني إجراؤها والتي ستعطي أداءً أفضل بكثير باستخدام الفهارس؟

ملحوظة: إن استخدام الدالة ST_Within (التي تعطي حدودًا حقيقية ، بدلاً من مطابقة المربع المحيط المبسط) لتلك الصفوف الـ 14 نفسها يستغرق وقتًا أطول بكثير ، 83 ثانية:

تحديث المواقع حدود الانضمام اليسرى على ST_Within (lonLat ، llgeom) SET borderaryId = الحدود.

ومع ذلك ، لدي روتين يسمى حقًا في غضون والذي له نفس النتيجة ولكنه يستغرق حوالي 2.3 ثانية. ولكن أيًا كانت الوظائف الثلاثة المستخدمة (bbox within ، أو الإجراء في الداخل ، أو ST_Within الرسمي) ، فإن هذا يعمل ببطء شديد عند 450.000 نقطة.


كان لدي مشكلة مماثلة.
لقد حللت مع إجراء.
يحاول:

BEGIN DECLARE b، loc_id INT؛ إعلان نقطة loc_point ؛ إعلان cur_1 CURSOR لتحديد lonLat ، معرف من المواقع ؛ إقرار استمرار المعالج لمجموعة غير موجودة ب = 1 ؛ فتح cur_1 ؛ REPEAT FETCH cur_1 INTO loc_point، loc_id؛ ابدأ بالإعلان عن نص ؛ قم بإعلان cur_2 CURSOR لتحديد معرف من الحدود حيث يوجد داخل (loc_point، limits.llgeom) ؛ فتح cur_2 ؛ إحضار cur_2 إلى ؛ تحديث المواقع SET borderaryId = أين معرف = loc_id ؛ إغلاق cur_2 ؛ نهاية؛ حتى ب = 1 نهاية التكرار ؛ إغلاق cur_1 ؛ نهاية

ترفض MySQL تمامًا استخدام الفهرس المكاني في الصلة ، ما لم تكن الصلة في صف واحد. سيستخدم الفهرس لإجراء فحص النطاق (تم التحقق من النطاق لكل سجل) وهو أفضل من لا شيء ، ولكن ليس بالسرعة التي ينبغي أن يكون عليها.

أود أيضًا أن أضيف أن الصلة اليسرى غير ضرورية وستؤدي إلى إبطاء الأمور ، إلا إذا كنت تمسح القيم المحددة مسبقًا في borderId.

وفينس على حق. تريد أن يبدأ الاستعلام بكل الحدود ثم ينضم إلى النقاط. سبب آخر للتخلي عن الصلة اليسرى.

كنت أفكر في استخدام إجراء لوضعي. سأجري بعض المقارنات وأنشر النتائج.

أنا أستخدم 5.7.20 مع innodb.