Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Requêtes MySQL Discussion :

Optimisation par table de cache spatiale et temporelle


Sujet :

Requêtes MySQL

  1. #1
    Membre actif
    Profil pro
    Developpeur
    Inscrit en
    juillet 2004
    Messages
    299
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Developpeur

    Informations forums :
    Inscription : juillet 2004
    Messages : 299
    Points : 275
    Points
    275
    Par défaut Optimisation par table de cache spatiale et temporelle
    Hello,

    aujourd’hui, je stocke des points (longitude, latitude) avec un timestamp, sessionID et une mapID.

    J'ai 2 types de requête :
    Une qui récupère dans un intervalle de temps un nombre de session différentes par petits intervalles de temps.
    Ca va me produire un graph temporel ensuite coté front-end.

    Une autre requête qui récupère des ensembles de points (aggrégés par environ 1 mètre) dans un intervalle de temps, et groupé aussi par intervalle de temps.
    Ceci est ensuite affiché sur une carte.

    En gros, je vais vous mettre les requêtes :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
    SELECT ROUND(p.lon * 100000) AS lon_int, ROUND(p.lat * 100000) AS lat_int,
            	 ROUND(UNIX_TIMESTAMP(p.timestamp)/60) AS ts_minute,
            	  COUNT(*) AS position_nb
            FROM `map_sessions` ms
            JOIN positions p
                ON (ms.id=p.`map_session_id`
                )
            WHERE ms.map_id IN (57)
            AND p.timestamp BETWEEN '2019-08-31 12:00:00' AND '2019-09-02 12:00:00'
    		GROUP BY ts_interval, lon_int, lat_int
            ORDER BY ts_interval
    Ici, on a un groupement d'environ 1 mètre pour les lat/lng, et 1 minute pour le temps.
    Mais pour le temps, cela peut varier en fonction de l'intervalle considéré. Exemple, sur1 jour, on aura un intervalle de 24*60*60 / 100 = 864.


    Voici l'autre type de requête qu'on peut avoir :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
    SELECT ROUND(UNIX_TIMESTAMP(p.timestamp) / 60) AS ts_interval,
            COUNT(DISTINCT ms.id) AS session_nb,
            SUM(UNIX_TIMESTAMP(ms.session_stop)-UNIX_TIMESTAMP(ms.session_start)) AS diff
            FROM `map_sessions` ms
            JOIN positions p
                ON (ms.id=p.map_session_id)
     
            WHERE ms.map_id IN (196)
            AND p.timestamp > '2019-08-31 01:00:00' AND p.timestamp <= '2019-09-05 12:00:00'
            GROUP BY ts_interval   
            ORDER BY ts_interval ASC
    En gros, c'est un peu pareil, mais on compte les sessions différentes et on prend toutes les positions possibles.

    J'utilise des double pour stocker les lat/lng. J'utilise un datetime pour le timestamp.

    Et voici le souci :
    quand on a quelques dizaines de milliers de lignes, ça passe bien, mais quand on passe sur des centaines de milliers de lignes, ça commence à prendre quelques secondes (10s environ).

    Je cherche donc à optimiser tout ça.
    Voici plusieurs pistes que je pourrais explorer :
    - faire des tables de cache avec agrégat, pré groupement au mètre, et à la minute (on ne descend jamais en dessous de la minute sur mes requêtes).
    - Passer dans cette même table les lat/lng en entier, le temps en entier de type minute.

    - Avoir plusieurs tables de cache avec différents niveau de groupement de temps... ???

    - Utiliser des tables partitionnées ??? Partitionner par map_id/map_session_id, ou par année ? mois ? jour ?

    Qu'en pensez vous ?

    Merci!
    Хајде Јано коло да играмо

  2. #2
    Modérateur

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 276
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 276
    Points : 11 991
    Points
    11 991
    Par défaut
    bonjour,

    quelles sont les structures des tables et indexes ?

    essayez en posant l'index suivant

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    CREATE INDEX IX_position_session_ts ON positions (map_session_id, timestamp, lon, lat)

  3. #3
    Membre actif
    Profil pro
    Developpeur
    Inscrit en
    juillet 2004
    Messages
    299
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Developpeur

    Informations forums :
    Inscription : juillet 2004
    Messages : 299
    Points : 275
    Points
    275
    Par défaut
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
     
    CREATE TABLE `map_sessions` (
      `id` int(10) UNSIGNED NOT NULL,
      `session_id` int(10) UNSIGNED DEFAULT NULL,
      `map_id` int(10) UNSIGNED DEFAULT NULL,
      `session_start` datetime NOT NULL,
      `session_stop` datetime NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     
    ALTER TABLE `map_sessions`
      ADD PRIMARY KEY (`id`),
      ADD UNIQUE KEY `unique_map_session` (`session_id`,`map_id`),
      ADD KEY `IDX_F892AB34613FECDF` (`session_id`),
      ADD KEY `IDX_F892AB3453C55F64` (`map_id`),
      ADD KEY `tm_start_idx` (`session_start`),
      ADD KEY `tm_stop_idx` (`session_stop`);
     
    ALTER TABLE `map_sessions`
      MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
     
    ALTER TABLE `map_sessions`
      ADD CONSTRAINT `FK_F892AB3453C55F64` FOREIGN KEY (`map_id`) REFERENCES `maps` (`id`) ON DELETE CASCADE,
      ADD CONSTRAINT `FK_F892AB34613FECDF` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`);
     
    CREATE TABLE `positions` (
      `id` int(10) UNSIGNED NOT NULL,
      `map_session_id` int(10) UNSIGNED DEFAULT NULL,
      `map_floor_id` int(10) UNSIGNED DEFAULT NULL,
      `map_tracked_item_id` int(10) UNSIGNED DEFAULT NULL,
      `timestamp` datetime NOT NULL,
      `lon` double NOT NULL,
      `lat` double NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     
    ALTER TABLE `positions`
      ADD PRIMARY KEY (`id`),
      ADD KEY `IDX_D69FE57C7440A9D1` (`map_session_id`),
      ADD KEY `IDX_D69FE57CE7B44FC5` (`map_floor_id`),
      ADD KEY `IDX_D69FE57CE108CB5E` (`map_tracked_item_id`),
      ADD KEY `tm_idx` (`timestamp`);
     
    ALTER TABLE `positions`
      MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
     
    ALTER TABLE `positions`
      ADD CONSTRAINT `FK_D69FE57C7440A9D1` FOREIGN KEY (`map_session_id`) REFERENCES `map_sessions` (`id`) ON DELETE CASCADE,
      ADD CONSTRAINT `FK_D69FE57CE108CB5E` FOREIGN KEY (`map_tracked_item_id`) REFERENCES `map_tracked_items` (`id`) ON DELETE CASCADE,
      ADD CONSTRAINT `FK_D69FE57CE7B44FC5` FOREIGN KEY (`map_floor_id`) REFERENCES `map_floors` (`id`) ON DELETE CASCADE;
    Хајде Јано коло да играмо

  4. #4
    Membre actif
    Profil pro
    Developpeur
    Inscrit en
    juillet 2004
    Messages
    299
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Developpeur

    Informations forums :
    Inscription : juillet 2004
    Messages : 299
    Points : 275
    Points
    275
    Par défaut
    Et hop, un explain de la première requête (via phpmyadmin) :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 	
    1 	SIMPLE 	ms 	ref 	PRIMARY,IDX_F892AB3453C55F64 	IDX_F892AB3453C55F64 	5 	const 	7471 	Using temporary; Using filesort
    1 	SIMPLE 	p 	ref 	IDX_D69FE57C7440A9D1,tm_idx 	IDX_D69FE57C7440A9D1 	5 	sdk_stat.ms.id 	53 	Using where
    Un profilage :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
     
    Copying To Tmp Table On Disk 	3,8 s 	56,69% 	1 	3,8 s
    Copying To Tmp Table 	2,5 s 	37,06% 	1 	2,5 s
    Sorting Result 	354,2 ms 	5,23% 	1 	354,2 ms
    Converting HEAP To Aria 	58,4 ms 	0,86% 	1 	58,4 ms
    Sending Data 	5,8 ms 	0,09% 	1 	5,8 ms
    Starting 	158 µs 	<0,01% 	1 	158 µs
    Statistics 	92 µs 	<0,01% 	1 	92 µs
    Creating Tmp Table 	46 µs 	<0,01% 	1 	46 µs
    End 	38 µs 	<0,01% 	1 	38 µs
    Updating Status 	37 µs 	<0,01% 	1 	37 µs
    Opening Tables 	29 µs 	<0,01% 	1 	29 µs
    Init 	27 µs 	<0,01% 	1 	27 µs
    Preparing 	25 µs 	<0,01% 	1 	25 µs
    Optimizing 	18 µs 	<0,01% 	1 	18 µs
    Freeing Items 	18 µs 	<0,01% 	1 	18 µs
    Unlocking Tables 	14 µs 	<0,01% 	1 	14 µs
    Checking Permissions 	12 µs 	<0,01% 	1 	12 µs
    Query End 	10 µs 	<0,01% 	1 	10 µs
    Cleaning Up 	9 µs 	<0,01% 	1 	9 µs
    Removing Tmp Table 	8 µs 	<0,01% 	1 	8 µs
    After Opening Tables 	5 µs 	<0,01% 	1 	5 µs
    System Lock 	4 µs 	<0,01% 	1 	4 µs
    Table Lock 	4 µs 	<0,01% 	1 	4 µs
    Closing Tables 	4 µs 	<0,01% 	1 	4 µs
    Executing 	3 µs 	<0,01% 	1 	3 µs
    6.8sec

    Et maintenant, pareil avec l'index proposé (je l'ai appelé 'test') :

    4.8729 ---> on a déjà gagné pas mal gratuitement ou presque !

    explain :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 	
    1 	SIMPLE 	ms 	ref 	PRIMARY,IDX_F892AB3453C55F64 	IDX_F892AB3453C55F64 	5 	const 	7471 	Using temporary; Using filesort
    1 	SIMPLE 	p 	ref 	IDX_D69FE57C7440A9D1,tm_idx,test 	test 	5 	sdk_stat.ms.id 	51 	Using where; Using index
    profilage :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
     
    ÉtatDocumentation
    	Temps total
    	% Temps
    	Appels
    	Temps moyen
    Copying To Tmp Table On Disk 	2,8 s 	59,19% 	1 	2,8 s
    Copying To Tmp Table 	1,6 s 	33,00% 	1 	1,6 s
    Sorting Result 	316,7 ms 	6,63% 	1 	316,7 ms
    Converting HEAP To Aria 	47,2 ms 	0,99% 	1 	47,2 ms
    Sending Data 	4,4 ms 	0,09% 	1 	4,4 ms
    Statistics 	95 µs 	<0,01% 	1 	95 µs
    Starting 	80 µs 	<0,01% 	1 	80 µs
    End 	71 µs 	<0,01% 	1 	71 µs
    Creating Tmp Table 	42 µs 	<0,01% 	1 	42 µs
    Updating Status 	33 µs 	<0,01% 	1 	33 µs
    Opening Tables 	26 µs 	<0,01% 	1 	26 µs
    Init 	22 µs 	<0,01% 	1 	22 µs
    Preparing 	21 µs 	<0,01% 	1 	21 µs
    Optimizing 	16 µs 	<0,01% 	1 	16 µs
    Unlocking Tables 	15 µs 	<0,01% 	1 	15 µs
    Freeing Items 	15 µs 	<0,01% 	1 	15 µs
    Removing Tmp Table 	13 µs 	<0,01% 	1 	13 µs
    Query End 	10 µs 	<0,01% 	1 	10 µs
    After Opening Tables 	5 µs 	<0,01% 	1 	5 µs
    Closing Tables 	5 µs 	<0,01% 	1 	5 µs
    Cleaning Up 	5 µs 	<0,01% 	1 	5 µs
    Checking Permissions 	4 µs 	<0,01% 	1 	4 µs
    System Lock 	4 µs 	<0,01% 	1 	4 µs
    Table Lock 	4 µs 	<0,01% 	1 	4 µs
    Executing 	3 µs 	<0,01% 	1 	3 µs
    Bien sur, j'ai utilisé la clause SQL_NO_CACHE dans ma requête...
    Хајде Јано коло да играмо

  5. #5
    Membre actif
    Profil pro
    Developpeur
    Inscrit en
    juillet 2004
    Messages
    299
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Developpeur

    Informations forums :
    Inscription : juillet 2004
    Messages : 299
    Points : 275
    Points
    275
    Par défaut
    Citation Envoyé par aieeeuuuuu Voir le message
    bonjour,

    quelles sont les structures des tables et indexes ?

    essayez en posant l'index suivant

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    CREATE INDEX IX_position_session_ts ON positions (map_session_id, timestamp, lon, lat)
    Juste une question, tu es certain que le lat et le lon servent dans l'index ?
    Car il n'y a pas de recherche dessus en direct, ça passe d'abord par une fonction.
    Du coup, je pense que le début de l'index est utilisé, mais pas le reste (lat lon).

    Je vais refaire quelques tests...
    Хајде Јано коло да играмо

  6. #6
    Modérateur

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 276
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 276
    Points : 11 991
    Points
    11 991
    Par défaut
    j'ai ajouté lat et lon dans dans l'index afin que celui-ci couvre la première requête, et éviter tout accès à la table positions

  7. #7
    Membre actif
    Profil pro
    Developpeur
    Inscrit en
    juillet 2004
    Messages
    299
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Developpeur

    Informations forums :
    Inscription : juillet 2004
    Messages : 299
    Points : 275
    Points
    275
    Par défaut
    Citation Envoyé par aieeeuuuuu Voir le message
    j'ai ajouté lat et lon dans dans l'index afin que celui-ci couvre la première requête, et éviter tout accès à la table positions
    Pareil, dans la première requête, je ne pense pas que le lat/lon soit utilisé dans l'index, car il y a une fonction à appliquer sur lat/lon, donc l'index ne doit pas être utile.
    Ca montre aussi ça dans mes tests.

    En fait, pour que ce soit utile pour le GROUP BY, il faudrait que la donnée ne soit pas retraitée par une fonction.
    Хајде Јано коло да играмо

  8. #8
    Modérateur

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 276
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 276
    Points : 11 991
    Points
    11 991
    Par défaut
    lat et lon ne sont pas utilisé lors de la recherche des lignes dans l'index, en effet.
    En revanche, une fois que ces lignes sont trouvées, le fait que ces colonnes fasse parti de la clef évite de devoir aller ensuite dans la table chercher les lignes qui correspondent pour trouver leur valeur : l'index suffit pour répondre à la requete (sans accés à la table). on parle alors d'index couvrant.

    Dans certains SGBDR comme SQL Server, il est possible d'ajouter des colonne en tant que colonne incluse (elle sont stockées dans les pages d'index, mais non ordonnées) justement pour cela.

    Car en dessous d'une certain seuil de sélectivité de l'index, il peut ne plus être utilisé, le cout des recherches dans la table devenant supérieur au gain apporté par l'utilisation de l'index...

  9. #9
    Membre actif
    Profil pro
    Developpeur
    Inscrit en
    juillet 2004
    Messages
    299
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Developpeur

    Informations forums :
    Inscription : juillet 2004
    Messages : 299
    Points : 275
    Points
    275
    Par défaut
    Citation Envoyé par aieeeuuuuu Voir le message
    lat et lon ne sont pas utilisé lors de la recherche des lignes dans l'index, en effet.
    En revanche, une fois que ces lignes sont trouvées, le fait que ces colonnes fasse parti de la clef évite de devoir aller ensuite dans la table chercher les lignes qui correspondent pour trouver leur valeur : l'index suffit pour répondre à la requete (sans accés à la table). on parle alors d'index couvrant.
    Claaaasse ! Je connaissais pas ce principe, merci!

    Malheureusement, je n'ai pas noté de réelles différences de perfs entre la version de l'index avec lat/lon et la version sans.


    Sinon, pour la suite, je pensais utiliser des tables de cache qui mettrait déjà les lat/lon sous forme d'entiers arrondis. Je n'ai pas besoin d'une précision supérieure au mètre... Et pareil pour le temps, je pensais mettre ça sous forme d'entier de type minutes depuis 1970.

    Ca me donnerait une table du type :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     
    CREATE TABLE `positions_cached` (
      `id` int(10) UNSIGNED NOT NULL,
      `map_session_id` int(10) UNSIGNED DEFAULT NULL,
      `map_floor_id` int(10) UNSIGNED DEFAULT NULL,
      `map_tracked_item_id` int(10) UNSIGNED DEFAULT NULL,
      `timestamp_in_minutes`  int(10) UNSIGNED DEFAULT NULL,
      `lon`  int(10) UNSIGNED DEFAULT NULL,
      `lat` int(10) UNSIGNED DEFAULT NULL,
      `nb` int(10) UNSIGNED DEFAULT NULL,
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    nb représente le nombre de positions concernées.

    Et après, je me demandais si je pouvais gagner en perf en partitionnant les tables...
    Car au bout d'un moment, je vais avoir pas mal de données...

    J'ai regardé aussi du coté du stockage de point (spartial) dans mysql, mais ça ne semble pas forcément utile. Ca semble être plutôt pour effectuer des opérations sur des points ou des polygones...
    Хајде Јано коло да играмо

  10. #10
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    YYYY
    Inscrit en
    mai 2002
    Messages
    19 135
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : YYYY
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 135
    Points : 45 186
    Points
    45 186
    Par défaut
    Citation Envoyé par SuperCed Voir le message
    Sinon, pour la suite, je pensais utiliser des tables de cache qui mettrait déjà les lat/lon sous forme d'entiers arrondis. Je n'ai pas besoin d'une précision supérieure au mètre... Et pareil pour le temps, je pensais mettre ça sous forme d'entier de type minutes depuis 1970.
    A moins que votre BD ne fasse quelques To dans quelques années, perdre de l'information et convertir des informations pour gratter quelques octets vous coutera très très cher à terme. Une date est une date. pas un entier ! Lorsque vous voudrez faire certains calcul temporels vous serez dans une merde noire avec des performances lamentable.
    Une précision qui va au millimètre mais qui a été relevée fausse sera encore plus fausse si vous arrondissez encore la valeur saisie….
    Tout ceci est donc hautement stupide.

    Ce qui serait intelligent c'est d'utiliser plutôt un type spatial pour stocker vos coordonnées. Cela vous permettrait de faire à terme des requêtes spatiale avec ces informations…
    C'est bien plus optimisé !
    Par exemple sous SQL Server :
    Nom : SQL spatial avec SQL Server et SSMS.jpg
Affichages : 30
Taille : 243,2 Ko

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  11. #11
    Membre actif
    Profil pro
    Developpeur
    Inscrit en
    juillet 2004
    Messages
    299
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Developpeur

    Informations forums :
    Inscription : juillet 2004
    Messages : 299
    Points : 275
    Points
    275
    Par défaut
    En fait, je ne pensais pas "perdre de l'information", mais seulement agréger les données dans une table de cache afin qu'il y ait moins de lignes, et donc moins de traitement au moment de la sélection des données.

    Pour le type spatial, ça m'intéresse! Aurais-tu un exemple d'utilisation dans mon cas dans lequel je pourrais faire un regroupement spatial optimisé ? J'ai vu un peu de doc, mais il me semble que ça fait à chaque fois appel à des fonctions, ce qui diminue grandement les perfs...
    Хајде Јано коло да играмо

  12. #12
    Membre actif
    Profil pro
    Administrateur
    Inscrit en
    mai 2008
    Messages
    138
    Détails du profil
    Informations personnelles :
    Localisation : Canada

    Informations professionnelles :
    Activité : Administrateur
    Secteur : Industrie

    Informations forums :
    Inscription : mai 2008
    Messages : 138
    Points : 241
    Points
    241
    Par défaut
    lat et lon peuvent être stockées dans une seule colonne de type point.
    Et par la suite, profiter des fonctions spatiales de MySQL pour calculer les distances, positions...

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
     
    CREATE TABLE positions (
      id int(10) AUTO_INCREMENT primary key,
      map_session_id int(10) UNSIGNED DEFAULT NULL,
      map_floor_id int(10) UNSIGNED DEFAULT NULL,
      map_tracked_item_id int(10) UNSIGNED DEFAULT NULL,
      timestamp datetime NOT NULL,
      localisation point NOT NULL,
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

  13. #13
    Modérateur

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 276
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 276
    Points : 11 991
    Points
    11 991
    Par défaut
    Citation Envoyé par SuperCed Voir le message
    Malheureusement, je n'ai pas noté de réelles différences de perfs entre la version de l'index avec lat/lon et la version sans.
    visiblement, le nombre de lignes concernées dans la table positions est très faible, ce qui explique que la différence ne se voit pas.
    Vous pouvez toutefois comparer les plans d’exécution, et devriez constater une opération supplémentaire (type key lookup) avec l'index sans les colonnes lon et lat...

  14. #14
    Membre actif
    Profil pro
    Developpeur
    Inscrit en
    juillet 2004
    Messages
    299
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Developpeur

    Informations forums :
    Inscription : juillet 2004
    Messages : 299
    Points : 275
    Points
    275
    Par défaut
    Citation Envoyé par aieeeuuuuu Voir le message
    visiblement, le nombre de lignes concernées dans la table positions est très faible, ce qui explique que la différence ne se voit pas.
    Vous pouvez toutefois comparer les plans d’exécution, et devriez constater une opération supplémentaire (type key lookup) avec l'index sans les colonnes lon et lat...
    Ok, je vais voir ça! Mais en effet, ça doit expliquer le peu de différence de perf.

    Merci!
    Хајде Јано коло да играмо

  15. #15
    Membre actif
    Profil pro
    Administrateur
    Inscrit en
    mai 2008
    Messages
    138
    Détails du profil
    Informations personnelles :
    Localisation : Canada

    Informations professionnelles :
    Activité : Administrateur
    Secteur : Industrie

    Informations forums :
    Inscription : mai 2008
    Messages : 138
    Points : 241
    Points
    241
    Par défaut colonnes calculées sinon vues matérialisées
    --
    Pourquoi nommer une colonnne 'timestamp' ou map_floor_id ( floor ?) ?
    Il est suffisant de faire un GROUP BY avec round(p.lat, 5) au lieu de ROUND(p.lat * 100000)

    S'il y a un choix à faire je n'aurai pas opter pour MySQl, connaissant les limites de MySQL en terme de fonctionnalités et de performances pour des projets sérieux. Il y a plus de 10 ans, MySQL était encore pire.

    Citation Envoyé par SuperCed Voir le message
    En fait, je ne pensais pas "perdre de l'information", mais seulement agréger les données dans une table de cache afin qu'il y ait moins de lignes, et donc moins de traitement au moment de la sélection des données..
    --

    L'approche aggrégée n'est pas mauvaise si vous avez des colonnes calculées qui consument les performances, surtout que vous faites des tris et des group by dessus.
    Avec des vues matérialisées, cela se fait bien PostGreSQL. Mais elles ne sont pas implémentées avec MySQL.

    Ajoutez alors des colonnes calculées (Generated Columns), pour contourner le problème et surement gagner en performance en y ajoutant des indexes.

    Comme ceci :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLE map_sessions (
      id int(10) AUTO_INCREMENT primary key,
      session_id int(10) UNSIGNED DEFAULT NULL,
      map_id int(10) UNSIGNED DEFAULT NULL,
      session_start datetime NOT NULL,
      session_stop datetime NOT NULL,
      diff integer as ( TIMESTAMPDIFF(MINUTE, ms.session_start, ms.session_stop) ) STORED
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    CREATE TABLE IF NOT EXISTS positions (
      id integer AUTO_INCREMENT primary key,
      map_session_id integer UNSIGNED DEFAULT NULL,
      map_floor_id integer UNSIGNED DEFAULT NULL,
      map_tracked_item_id integer UNSIGNED DEFAULT NULL,
      timestamp datetime NOT NULL default current_timestamp(),
      lon double NOT NULL,
      lat double NOT NULL,
      lon_int integer AS (ROUND(lon, 5)) STORED,
      lat_int integer AS (ROUND(lat, 5)) STORED,
      minutes integer as ( TIMESTAMPDIFF(MINUTE, '1970-01-01 00:00:00', timestamp)) STORED
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    CREATE INDEX positions_lon_int_idx ON positions (lon_int);
    CREATE INDEX positions_lat_int_idx ON positions (lat_int);
    CREATE INDEX positions_ts_minutes_idx ON positions (minutes);
    CREATE INDEX map_sessions_diff_idx ON map_sessions (diff);
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    SELECT p.minutes, SUM(ms.diff) AS diff, COUNT(DISTINCT ms.id) AS session_nb
    FROM map_sessions ms
    JOIN positions p ON ms.id = p.map_session_id
    WHERE ms.map_id IN (196) AND p.timestamp BETWEEN '2019-08-31 12:00:00' AND '2019-09-02 12:00:00'
    GROUP BY p.minutes
    ORDER BY p.minutes ASC;
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    SELECT COUNT(*) AS position_nb, p.minutes, p.lon_int, p.lat_int
    FROM map_sessions ms
    JOIN positions p ON ms.id = p.map_session_id
    WHERE ms.map_id IN (57) AND p.timestamp BETWEEN '2019-08-31 12:00:00' AND '2019-09-02 12:00:00'
    GROUP BY p.minutes, lon_int, lat_int
    ORDER BY p.minutes ASC;

  16. #16
    Membre actif
    Profil pro
    Developpeur
    Inscrit en
    juillet 2004
    Messages
    299
    Détails du profil
    Informations personnelles :
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Developpeur

    Informations forums :
    Inscription : juillet 2004
    Messages : 299
    Points : 275
    Points
    275
    Par défaut
    Citation Envoyé par aieeeuuuuu Voir le message
    lat et lon ne sont pas utilisé lors de la recherche des lignes dans l'index, en effet.
    En revanche, une fois que ces lignes sont trouvées, le fait que ces colonnes fasse parti de la clef évite de devoir aller ensuite dans la table chercher les lignes qui correspondent pour trouver leur valeur : l'index suffit pour répondre à la requete (sans accés à la table). on parle alors d'index couvrant.

    Dans certains SGBDR comme SQL Server, il est possible d'ajouter des colonne en tant que colonne incluse (elle sont stockées dans les pages d'index, mais non ordonnées) justement pour cela.

    Car en dessous d'une certain seuil de sélectivité de l'index, il peut ne plus être utilisé, le cout des recherches dans la table devenant supérieur au gain apporté par l'utilisation de l'index...
    Citation Envoyé par manzeki Voir le message
    --
    Pourquoi nommer une colonnne 'timestamp' ou map_floor_id ( floor ?) ?
    Il est suffisant de faire un GROUP BY avec round(p.lat, 5) au lieu de ROUND(p.lat * 100000)

    S'il y a un choix à faire je n'aurai pas opter pour MySQl, connaissant les limites de MySQL en terme de fonctionnalités et de performances pour des projets sérieux. Il y a plus de 10 ans, MySQL était encore pire.
    Pas mal en effet ! Je ne connaissais pas les colonnes "stored".

    Mais ça va être mieux avec une table de cache je pense car les données seront déjà agrégées. Plus besoind e group by sur la partie spatiale.
    Sur la temporelle, ça va pas marcher, car l'intervalle peut varier. Par contre, c'est toujours un multiple de 60. Donc, je peux limiter le nombre de lignes.
    Je vais virer le session_id, et comme ça, je pourrai faire un regroupement avec une colonne supplémentaire "number".

    En tous cas, ça avance, je commence à avoir différentes pistes.
    Je vais voir quelle serait la meilleure.
    Хајде Јано коло да играмо

Discussions similaires

  1. Combien de colonnes par table pour optimiser ma base ?
    Par Nicobp dans le forum Langage SQL
    Réponses: 5
    Dernier message: 24/01/2013, 18h51
  2. Optimiser les tables mysql, nécessaire ?
    Par Michaël dans le forum Requêtes
    Réponses: 5
    Dernier message: 15/07/2005, 18h11
  3. Optimisation des tables
    Par le-roy_a dans le forum Décisions SGBD
    Réponses: 5
    Dernier message: 24/01/2005, 10h04
  4. Optimiser les tables
    Par blizar dans le forum PostgreSQL
    Réponses: 3
    Dernier message: 04/06/2004, 08h34
  5. 3 tables, 1 attribut date par table > avoir la date MAX
    Par Amon dans le forum Langage SQL
    Réponses: 5
    Dernier message: 26/05/2004, 13h54

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo