Suite

Optimisation de MySQL JOIN basée sur une correspondance Within

Optimisation de MySQL JOIN basée sur une correspondance Within


J'ai une table de points et de limites et j'essaie d'ajouter l'ID de limite dans lequel se trouve un point. Cependant, en utilisant unLEFT JOIN ON À l'intérieur (emplacement, limite)il faut environ 3,5 heures pour correspondre à 450 000 points contre 350 limites. Existe-t-il un moyen d'optimiser cette jointure ?

Plus en détail:

J'ai deux tables dans MySQL 5.6, dont l'une contient des points, chacun stocké en tant queindiqueret l'autre contenant des limites, chacune stockée en tant quegéométrie:

-- Table des emplacements, environ 0,5 million de points CREATE TABLE emplacements ( id INT(11) NOT NULL PRIMARY KEY, longitude float(11,6) DEFAULT NULL, latitude float(10,6) DEFAULT NULL, lonLat point NOT NULL DEFAULT" ,borderId INT(11) DEFAULT NULL ) ENGINE=MyISAM; -- Remplir le champ lonLat UPDATE locations SET lonLat = POINTFROMTEXT(CONCAT('point(',longitude,",latitude,')')) WHERE longitude IS NOT NULL AND la latitude N'EST PAS NULLE ; -- Ajouter un index spatial sur les emplacements lonLat ALTER TABLE ADD SPATIAL INDEX(lonLat); -- Table d'environ 350 limites exactes, certaines limites se chevauchant CREATE TABLE IF NOT EXISTS ( id INT(11) NOT NULL PRIMARY KEY, llgeom geometry NOT NULL ); -- Ajouter un index spatial sur la limite llgeom : ALTER TABLE 'boundaries' ADD SPATIAL('llgeom');

J'ai une requête qui met à jour la table d'emplacement avec l'ID de limite dans lequel se trouve le point de cette ligne :

UPDATE emplacements GAUCHE JOIN frontières ON Within(lonLat, llgeom) SET boundId =borders.id;

Notez que lonLat et llgeom ont tous deux déjà des index spatiaux sur eux.

Avec environ 450 000 points et 350 géométries, fonctionnant sur MySQL 5.6, cela prend environ 3,5 heures. Faire un test limité à seulement 14 lignes prend environ 2,1 secondes.

Si je lance un EXPLAIN, cela montre qu'aucune indexation n'est utilisée :

mysql> expliquer les emplacements de mise à jour LEFT JOIN bounds ON Within(lonLat, llgeom) SET boundId = bounds.id; | identifiant | select_type | tableau | taper | clés_possibles | clé | key_len | réf | rangées | Supplément | | 1 | SIMPLE | emplacements | TOUS | NULL | NULL | NULL | NULL | 451010 | NULL | | 1 | SIMPLE | limites | TOUS | NULL | NULL | NULL | NULL | 353 | En utilisant où ; Utilisation du tampon de jointure (Block Nested Loop) |

Cela montre quetaperestTOUSdans les deux cas, ce qui est "le pire type de jointure et indique généralement le manque d'index appropriés sur la table".

Y a-t-il une amélioration que je peux apporter qui donnera de bien meilleures performances, en utilisant des index ?

NB L'utilisation de la fonction ST_Within (qui donne de vraies limites, plutôt qu'une correspondance simplifiée du cadre de délimitation) pour ces mêmes 14 lignes prend beaucoup plus de temps, 83 secondes :

UPDATE emplacements LEFT JOIN bounds ON ST_Within(lonLat, llgeom) SET boundId = bounds.id;

Cependant, j'ai une routine appelée vraimentWithin qui a le même résultat mais prend environ 2,3 secondes. Mais quelle que soit la fonction utilisée (la bbox Within, la procédure vraimentWithin ou la ST_Within officielle), cela s'avère trop lent pour 450 000 points.


J'ai eu un problème similaire.
J'ai résolu avec une procédure.
Essayer:

COMMENCER À DÉCLARER b, loc_id INT; DÉCLARER loc_point point ; DECLARE cur_1 CURSOR FOR SELECT lonLat, id FROM emplacements ; DÉCLARER CONTINUER LE GESTIONNAIRE POUR L'ENSEMBLE INTROUVABLE b = 1 ; OUVERT cur_1; REPEAT FETCH cur_1 INTO loc_point, loc_id; COMMENCER À DÉCLARER un TEXTE ; DECLARE cur_2 CURSEUR POUR SELECTIONNER l'identifiant DES limites WHERE Within(loc_point, bounders.llgeom); OUVERT cur_2; FETCH cur_2 DANS a; UPDATE emplacements SET boundId = a WHERE id = loc_id; FERMER cur_2; FINIR; JUSQU'À b = 1 FIN DE LA RÉPÉTITION ; FERMER cur_1 ; FINIR

MySQL refuse absolument d'utiliser l'index spatial dans une jointure, à moins que la jointure ne porte sur une seule ligne. Il utilisera l'index pour effectuer une vérification de plage (plage vérifiée pour chaque enregistrement), ce qui est mieux que rien, mais pas aussi rapide qu'il devrait l'être.

J'ajouterais également que la jointure gauche n'est pas nécessaire et ralentira les choses, à moins que vous n'effacez les valeurs précédemment définies dans boundingId.

Et Vince a raison. Vous voulez que la requête commence par toutes les limites, puis se joigne aux points. Une autre raison d'abandonner la jointure gauche.

J'envisageais d'utiliser une procédure adaptée à ma situation. Je vais faire quelques comparaisons et poster les résultats.

J'utilise 5.7.20 avec innodb.