Question Se joint aux index mysql spatiaux


J'ai deux tables: une avec des points, l'autre avec des polys.

CREATE TABLE `points` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `point` point NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

CREATE TABLE `ranges` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `poly` polygon NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `poly` (`poly`)
) ENGINE=MyISAM;

Je veux joindre des plages aux points sur les points à l'intérieur des polys. Les requêtes semblent simples:

SELECT * 
  FROM points 
    LEFT JOIN ranges 
      ON MBRCONTAINS(poly, point) 
  WHERE points.id = 2;

Cette requête fonctionne rapidement et utilise des index, une partie de l'explication:

table | type | possibles_keys | clé | key_len
gammes gamme | poly | poly | 34

Mais quand j'essaie de joindre plusieurs lignes de la table points:

SELECT * 
  FROM points 
   LEFT JOIN ranges 
    ON MBRCONTAINS(poly, point) 
  WHERE points.id IN (1,2,3);

tout se décompose:

+ ---- + ------------- + ------------ + ------- + --------- ------ + --------- + --------- + ------ + -------- + ------- ------ +
| id | select_type | table | type | possibles_keys | clé | key_len | ref | lignes | Extra |
+ ---- + ------------- + ------------ + ------- + --------- ------ + --------- + --------- + ------ + -------- + ------- ------ +
| 1 | SIMPLE | points | gamme | PRIMAIRE | PRIMAIRE | 4 | NULL | 3 | En utilisant où |
| 1 | SIMPLE | gammes TOUT | poly | NULL | NULL | NULL | 155183 | |
+ ---- + ------------- + ------------ + ------- + --------- ------ + --------- + --------- + ------ + -------- + ------- ------ +

Ajouter FORCE INDEX (poly) n'aide pas.

Exemple de données pour tester les requêtes (désolé, seule la version php, je ne suis pas commun avec les procédures SQL):

//points
for($i=0;$i<=500;$i++) {
    $point = mt_rand();
    mysql_query('INSERT INTO points (point) VALUES (POINTFROMWKB(POINT('.$point.', 0)))');
}

$qty = 20000;
$max = mt_getrandmax();
$add = $max / $qty
$end = 0;

//polys
while($end < $max) {
    $start = $end;
    $end = mt_rand($start, $start + $add);
    mysql_query('INSERT INTO ranges (poly) VALUES (
        GEOMFROMWKB(POLYGON(LINESTRING(
            POINT('.$start.', -1),
            POINT('.$end.',   -1),
            POINT('.$end.',    1),
            POINT('.$start.',  1),
            POINT('.$start.', -1)
          )))
    )');
}

11
2017-11-23 15:16


origine


Réponses:


Je pense que c'est parce que MySQL ne supporte pas la fusion des index spatiaux. Je ne sais pas si c'est toujours vrai mais je l'ai lu quelque part dans le passé. Si vous avez une instruction OR, les index spatiaux ne sont pas utilisés

Dans votre cas, où faites-vous points.id = 1, c'est un choix direct avec un résultat renvoyé qui est utilisé dans les contenus. Cela utilise l'index.

Lorsque vous ajoutez des points.in (1,2,3), cela retourne 3 résultats et chacun doit être mappé à la table des plages, donc ne fonctionne pas

résultat

id  select_type     table   type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   SIMPLE  points  range   PRIMARY     PRIMARY     4   NULL    3   100.00  Using where
1   SIMPLE  ranges  ALL     poly    NULL    NULL    NULL    6467418     100.00   

Vous pouvez simplifier votre test sans la table de point en procédant comme suit: SELECT * FROM gammes dans lesquelles mbrcontains (poly, GEOMFROMWKB (POINT (0, 0)))

id  select_type     table   type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   SIMPLE  ranges  range   poly    poly    34  NULL    1   100.00  Using where

Et maintenant ceci; SELECT * FROM gammes dans lesquelles mbrcontains (poly, GEOMFROMWKB (POINT (0, 0))) OU mbrcontains (poly, GEOMFROMWKB (POINT (10, 10)))

résultat

id  select_type     table   type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   SIMPLE  ranges  ALL     poly    NULL    NULL    NULL    6467418     100.00  Using where

Voir que dans le deuxième cas, vous n'utilisez pas d'index et que vous numérisez.

Vous pouvez forcer la requête à utiliser l'index en créant UNION pour chaque point spécifique, mais je ne suis pas sûr que ce soit plus rapide. J'ai fait des tests localement et c'était un peu plus lent que votre première requête.

EXPLAIN EXTENDED 
SELECT *
FROM points
FORCE INDEX (PRIMARY )
LEFT JOIN ranges
FORCE INDEX ( poly ) ON mbrcontains( poly, point )
WHERE points.id = 1
UNION DISTINCT
SELECT *
FROM points
FORCE INDEX (PRIMARY )
LEFT JOIN ranges
FORCE INDEX ( poly ) ON mbrcontains( poly, point )
WHERE points.id = 2
UNION DISTINCT
SELECT *
FROM points
FORCE INDEX (PRIMARY )
LEFT JOIN ranges
FORCE INDEX ( poly ) ON mbrcontains( poly, point )
WHERE points.id = 3

résultat

id  select_type     table   type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   PRIMARY     points  const   PRIMARY     PRIMARY     4   const   1   100.00   
1   PRIMARY     ranges  range   poly    poly    34  NULL    1   100.00  Using where
2   UNION   points  const   PRIMARY     PRIMARY     4   const   1   100.00   
2   UNION   ranges  range   poly    poly    34  NULL    1   100.00  Using where
3   UNION   points  const   PRIMARY     PRIMARY     4   const   1   100.00   
3   UNION   ranges  range   poly    poly    34  NULL    1   100.00  Using where
NULL    UNION RESULT    <union1,2,3>    ALL     NULL    NULL    NULL    NULL    NULL    NULL     

6
2017-11-29 19:26



J'ai utilisé avec succès des requêtes similaires, avec une seule différence dans le modèle de données: une clé spatiale dans la base de données de points. Dans mon cas:

CREATE TABLE geopoints (
  pid int(11) NOT NULL AUTO_INCREMENT,
  description varchar(255) NOT NULL DEFAULT '',
  geopoint point NOT NULL,
  PRIMARY KEY (pid),
  SPATIAL KEY geopoint (geopoint) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Et tout s'est bien passé dans des requêtes comme celle-ci:

SELECT pt.pid, x(geopoint), Y(geopoint), pl.pid, AsText(geopolygon) 
  FROM geopoints pt INNER JOIN geopolygons pl ON MBRCONTAINS(geopolygon, geopoint)
 WHERE pt.pid IN (1,2,4,5) AND pl.pid BETWEEN 1 AND 5;

mes deux centimes,


3
2017-07-28 21:23



Si tout ce que vous avez à faire est des carrés, je me contenterais de 4 chiffres dans votre table qui peuvent être indexés représentant Haut, Gauche, Hauteur, Largeur, puis exécutez votre requête dont la coordonnée "X" entre Gauche, Gauche + Largeur et coordonnée "Y" entre Haut, Haut + Hauteur.


0
2017-11-30 12:27