1. #1
    Membre à l'essai
    Inscrit en
    mai 2009
    Messages
    42
    Détails du profil
    Informations forums :
    Inscription : mai 2009
    Messages : 42
    Points : 21
    Points
    21

    Par défaut Optimisation d'une table mysql qui contient 120 000 000 de lignes

    Bonjour,

    J'ai besoin de vos conseils pour optimiser une table contenant 120 million de lignes afin d'analyser les donnees le plus rapidement possible:

    Pour analyser cette table je dois lancer des requetes "select" contenant des jointures sur:
    un champ temps "tp",
    une donnee "d1" ,
    une donnee "d2"
    et une clause where sur le champ temps "tp" et une donnee "d3".

    Les donnees de cette tables sont horodatees et on y trouve 2 ans de donnees comme illustre ci-dessous:


    unique_id;01/01/2016 12:00:20;typ1;typ2;.........
    ......................................
    unique_id;01/01/2017 12:30:03;typ1;typ2;.......
    ..............
    ...............
    unique_id;13/02/2017 04:00:16; ................
    ................
    unique_id27/07/2017 23:00:00;............

    Je sais que je vais devoir indexer les champs utilises dans ma jointure et dans la clause where mais je me demande aussi s'il ne serait pas judicieux de diviser cette table qui contient enormement de donnees en tables par mois pour
    faciliter l'indexation et optimiser la recherche car actuellement pour une recherche sur deux mois cela prends 20 heures ( avec une serveur contenant 80 G0 de memoire vive et processeur de 8 cores).

    Je me demande egalement si je dois placer ces champs (tp,d1,d2,d3..) dans un meme index ou pas?



    Merci pour vos conseils!

  2. #2
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 974
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 2 974
    Points : 6 533
    Points
    6 533
    Billets dans le blog
    1

    Par défaut

    L'une des pistes possibles est le partitionnement

    Avec une rétention maximale de deux ans, vous pouvez créer 24 partitions de un mois chacune

    Vous pouvez également positionner l'index cluster sur la colonne d'horodatage et faire périodiquement des réorg pour garantir le séquencement.
    Les requêtes sur critère date en seront grandement optimisées

  3. #3
    Membre à l'essai
    Inscrit en
    mai 2009
    Messages
    42
    Détails du profil
    Informations forums :
    Inscription : mai 2009
    Messages : 42
    Points : 21
    Points
    21

    Par défaut

    Bonjour,

    Merci pour ton conseil.

    Est ce que le fait de partinionner une table contenant 120 000 000 de lignes ne va pas prendre trop temps? Penses tu que si je lance le partionnement la nuit, le demain ce sera fait?

    C'est vrai que ton idee et genial car le fait de re-creer 24 tables avec des indexes et importer 6 000 000 de lignes par table risque de me prendre un temps fou.

    Je precise que je peux tout faire sur cette table car je l'ai importe dans un lab pour y analyser les donnees.


    Merci

  4. #4
    Rédacteur/Modérateur

    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    décembre 2013
    Messages
    1 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes
    Secteur : Conseil

    Informations forums :
    Inscription : décembre 2013
    Messages : 1 453
    Points : 3 039
    Points
    3 039

    Par défaut

    imaginons une requête du type :
    select sum(d1) , sum(d2) from ma_table where tp between tp1 and tp2 and d3 = 1


    Et imaginons que tu aies régulièrement des requêtes de ce type à exécuter. Avec des index, il faut aider le moteur à lire rapidement uniquement les données utiles. Donc des index sur d1 ou d2 ne sont pas utiles.

    Si tu crées un index sur tp, et un index sur d3, le moteur utilisera de toutes façons un seul des 2 index.
    Mais tu peux créer une clé composée, c'est à dire un index qui s'appuie sur 2 colonnes ou plus.
    Si tu crées un index sur tp+d3, ça ne donnera pas grand chose. Rien de mieux qu'un index sur tp seul.

    Mais un index sur d3+tp sera idéal. Pour une valeur de d3 donnée, et pour un intervalle de tp donné, le moteur sait accéder directement aux données utiles. Il ne lira que les données utiles.
    Aprs, pour des volumes comme ça, il faut effectivement partitionner la base.
    Partitionner la base sur le temps, c'est classique, ça facilite nettement la maintenance (suppression des anciennes données par exemple, ou import et indexation ciblée sur les nouvelles données...)
    Par contre, en cas de partition, je ne sais pas si le moteur sait combiner les bénéfices de la partition et d'un index composite.
    N'oubliez pas le bouton Résolu si vous avez obtenu une réponse à votre question.

  5. #5
    Membre à l'essai
    Inscrit en
    mai 2009
    Messages
    42
    Détails du profil
    Informations forums :
    Inscription : mai 2009
    Messages : 42
    Points : 21
    Points
    21

    Par défaut

    Merci beaucoup ces conseils et eclaircicements.

    Je vais donc partir sur un partitionnement de la table sur le temps et une indexation. Je vous tiens au courant.


    Merci

  6. #6
    Expert éminent Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    2 896
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 77
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 2 896
    Points : 8 557
    Points
    8 557

    Par défaut

    Salut Hilal74.

    Vous posez deux questions fort différentes.

    1) le choix de l'index dépend de votre requête et plus particulièrement de la clause where.
    Il faut savoir que MySql utilise un seul index pour chaque jointure.
    De ce fait, vous devez combiner plusieurs colonnes dans un index si votre clause "where" se fait sur plusieurs colonnes.
    Mais cela dépend de la cardinalité des valeurs pour chacune des colonnes.
    Une colonne ayant une cardinalité de 90% pour une valeur donnée, ne sert à rien dans un index.
    Cela est utilie quand cette cardinalité est une valeur très faible.

    1-a) je constate que vous utilisez les dates sous le format "01/01/2016".
    J'espère pour vous que vous n'utilisez pas un char ou encore un varchar sur ce type de colonne, car votre index ne sert à rien.
    Vous devez utiliser le type "date" et donc stocker la date sous le format "2016-01-01".
    La colonne ayant le type "date" contiendra une valeur numérique débutant le 1er janvier 1970.

    1-b) vous stockez les heures. Est-ce utilise ?
    Si oui, vous risquez d'avoir des doublons, c'est-à-dire ayant des dates et des heures identiques.
    Le mieux serait alors d'utiliser le type "timestamp".
    Cela comprend la date "YYYY-MM-DD", l'heure "HH:MM:SS" et le millionième de seconde sur six chiffres.
    Il faudrait mieux que chaque valeur de cette colonne "timestamp" soit unique.

    1-c) vous parlez d'une donnée "d3", mais vous n'indiquez rien sur sa nature.
    Si c'est une chaîne de caractères, je comprends pourquoi un simple select sur deux mois dure 20 heures.

    1-d) quel est le type de moteur que vous utilisez ? MyIsam ou InnoDB ?

    1-e) pour créer un index sur deux colonnes, vous devez déclarer :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    CREATE UNIQUE INDEX `idx` USING BTREE ON `test` (`col1`,`col2`);

    2) Escartefigue vous conseille de partitionner votre table en vous basant sur le mois.
    Pour ce faire, vous aurez besoin de deux colonnes calculées, l'année et le mois.
    Pour la gestion, vous devez faire chaque mois le ménage afin de détruire la partition la plus ancienne.

    2-a) comment créer votre table partitionnée ?
    Il existe plusieurs solutions pour produire des partitions basées sur le mois.
    Je vous propose celle avec le "range". Voici un exemple de ce que l'on peut faire :
    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
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    --------------
    SET AUTOCOMMIT = 0
    --------------
     
    --------------
    START TRANSACTION
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `test`
    --------------
     
    --------------
    CREATE TABLE `test`
    ( `id`         timestamp(6)      NOT NULL,
      `val`        tinyint unsigned  NOT NULL,
      `lib`        varchar(255)      NOT NULL,
      `year`       smallint unsigned GENERATED ALWAYS AS (year(id))  stored,
      `month`      tinyint  unsigned GENERATED ALWAYS AS (month(id)) stored,
      PRIMARY KEY (`id`,`year`,`month`),
      INDEX `IDX` (`year`,`month`,`val`)
    ) ENGINE=Innodb
      DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
     
      PARTITION BY RANGE COLUMNS (`year`,`month`)
      (
        PARTITION p2017s01 values less than (2017,02),
        PARTITION p2017s02 values less than (2017,03),
        PARTITION p2017s03 values less than (2017,04),
        PARTITION p2017s04 values less than (2017,05),
        PARTITION p2017s05 values less than (2017,06),
        PARTITION p2017s06 values less than (2017,07),
        PARTITION p2017s07 values less than (2017,08),
        PARTITION p2017s08 values less than (maxvalue,maxvalue)
      )
    --------------
     
    --------------
    commit
    --------------
     
    --------------
    insert into `test` (`id`,`val`,`lib`) values
      ('2017-01-01- 15:00:00.000001', 05,'un'),        ('2017-01-01- 15:00:00.000002',07,'deux'),       ('2017-01-01- 15:00:00.000003',06,'trois'),
      ('2017-02-01- 15:00:00.000001', 07,'quatre'),    ('2017-02-01- 15:00:00.000002',06,'cinq'),       ('2017-02-01- 15:00:00.000003',05,'six'),
      ('2017-03-01- 15:00:00.000001', 06,'sept'),      ('2017-03-01- 15:00:00.000002',05,'huit'),       ('2017-03-01- 15:00:00.000003',07,'neuf'),
      ('2017-04-01- 15:00:00.000001', 05,'dix'),       ('2017-04-01- 15:00:00.000002',07,'onze'),       ('2017-04-01- 15:00:00.000003',06,'douze'),
      ('2017-05-01- 15:00:00.000001', 07,'treize'),    ('2017-05-01- 15:00:00.000002',06,'quatorze'),   ('2017-05-01- 15:00:00.000003',05,'quinze'),
      ('2017-06-01- 15:00:00.000001', 06,'seize'),     ('2017-06-01- 15:00:00.000002',05,'dix-sept'),   ('2017-06-01- 15:00:00.000003',07,'dix-huit'),
      ('2017-07-01- 15:00:00.000001', 05,'dix_neuf'),  ('2017-07-01- 15:00:00.000002',07,'vingt'),      ('2017-07-01- 15:00:00.000003',06,'vingt-et-un'),
      ('2017-08-01- 15:00:00.000001', 07,'vingt-deux'),('2017-08-01- 15:00:00.000002',06,'vingt-trois'),('2017-08-01- 15:00:00.000003',05,'vingt-quatre')
    --------------
     
    --------------
    select * from `test`
    --------------
     
    +----------------------------+-----+--------------+------+-------+
    | id                         | val | lib          | year | month |
    +----------------------------+-----+--------------+------+-------+
    | 2017-01-01 15:00:00.000001 |   5 | un           | 2017 |     1 |
    | 2017-01-01 15:00:00.000002 |   7 | deux         | 2017 |     1 |
    | 2017-01-01 15:00:00.000003 |   6 | trois        | 2017 |     1 |
    | 2017-02-01 15:00:00.000001 |   7 | quatre       | 2017 |     2 |
    | 2017-02-01 15:00:00.000002 |   6 | cinq         | 2017 |     2 |
    | 2017-02-01 15:00:00.000003 |   5 | six          | 2017 |     2 |
    | 2017-03-01 15:00:00.000001 |   6 | sept         | 2017 |     3 |
    | 2017-03-01 15:00:00.000002 |   5 | huit         | 2017 |     3 |
    | 2017-03-01 15:00:00.000003 |   7 | neuf         | 2017 |     3 |
    | 2017-04-01 15:00:00.000001 |   5 | dix          | 2017 |     4 |
    | 2017-04-01 15:00:00.000002 |   7 | onze         | 2017 |     4 |
    | 2017-04-01 15:00:00.000003 |   6 | douze        | 2017 |     4 |
    | 2017-05-01 15:00:00.000001 |   7 | treize       | 2017 |     5 |
    | 2017-05-01 15:00:00.000002 |   6 | quatorze     | 2017 |     5 |
    | 2017-05-01 15:00:00.000003 |   5 | quinze       | 2017 |     5 |
    | 2017-06-01 15:00:00.000001 |   6 | seize        | 2017 |     6 |
    | 2017-06-01 15:00:00.000002 |   5 | dix-sept     | 2017 |     6 |
    | 2017-06-01 15:00:00.000003 |   7 | dix-huit     | 2017 |     6 |
    | 2017-07-01 15:00:00.000001 |   5 | dix_neuf     | 2017 |     7 |
    | 2017-07-01 15:00:00.000002 |   7 | vingt        | 2017 |     7 |
    | 2017-07-01 15:00:00.000003 |   6 | vingt-et-un  | 2017 |     7 |
    | 2017-08-01 15:00:00.000001 |   7 | vingt-deux   | 2017 |     8 |
    | 2017-08-01 15:00:00.000002 |   6 | vingt-trois  | 2017 |     8 |
    | 2017-08-01 15:00:00.000003 |   5 | vingt-quatre | 2017 |     8 |
    +----------------------------+-----+--------------+------+-------+
    --------------
    SELECT  PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
      FROM  INFORMATION_SCHEMA.PARTITIONS
      WHERE TABLE_NAME = 'test'
    --------------
     
    +----------------+-------------------+------------+
    | PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
    +----------------+-------------------+------------+
    | p2017s01       | NULL              |          3 |
    | p2017s02       | NULL              |          3 |
    | p2017s03       | NULL              |          3 |
    | p2017s04       | NULL              |          3 |
    | p2017s05       | NULL              |          3 |
    | p2017s06       | NULL              |          3 |
    | p2017s07       | NULL              |          3 |
    | p2017s08       | NULL              |          3 |
    +----------------+-------------------+------------+
    --------------
    explain select *
    from `test`
    where `year`  = 2017
      and `month` = 5
      and `val`   = 6
    --------------
     
    +----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
    |  1 | SIMPLE      | test  | p2017s05   | ref  | IDX           | IDX  | 4       | const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
    --------------
    select *
    from `test`
    where `year`  = 2017
      and `month` = 5
      and `val`   = 6
    --------------
     
    +----------------------------+-----+----------+------+-------+
    | id                         | val | lib      | year | month |
    +----------------------------+-----+----------+------+-------+
    | 2017-05-01 15:00:00.000002 |   6 | quatorze | 2017 |     5 |
    +----------------------------+-----+----------+------+-------+
    --------------
    COMMIT
    --------------
     
    --------------
    SET AUTOCOMMIT = 1
    --------------
     
    Appuyez sur une touche pour continuer...
    2-b) dans cet exemple, j'ai créé huit partitions, dont la dernière "p2017s08" correspond à tout ce qui est > 2017/07.

    2-c) j'ai fait un explain sur votre requête, afin de vous montrer l'usage de l'index dans la requête.

    P.S.: ce test a été fait avec mysql version 5.7.18 !

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  7. #7
    Membre à l'essai
    Inscrit en
    mai 2009
    Messages
    42
    Détails du profil
    Informations forums :
    Inscription : mai 2009
    Messages : 42
    Points : 21
    Points
    21

    Par défaut

    Salut Artemus24,

    D'abord un grand merci pour tes conseils.

    le champs temps est bien en Datetime, les heures sont utilises et en effet je peux avoir des doublons sur ce champs mais est ce que ca un impact sur l'indexation?

    Et le champs d3 est bien une chaine de caractere, pensez vous que je devrez utiliser un autre format pour ce champs? Car comme je l ai mentionnais c une base de donnees que je peux modifier afin de l'analyser dans des plus bref delai.

    Le storage engine est Innodb.

    Merci

  8. #8
    Expert éminent Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    2 896
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 77
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 2 896
    Points : 8 557
    Points
    8 557

    Par défaut

    Salut Hilal74.

    Citation Envoyé par Hilal74
    le champs temps est bien en Datetime, les heures sont utilises et en effet je peux avoir des doublons sur ce champs mais est ce que ca un impact sur l'indexation?
    Le fait que la colonne "temps" soit dans le type "datetime" n'est pas un problème en soit.
    Le problème, c'est l'usage que vous allez en faire. J'ai compris que cette colonne servait comme clef primaire.
    Et donc, vous ne pouvez pas avoir deux dates identiques car une clef primaire doit être non NULL et unique.

    Si vous recherchez une période allant disons du "2017-07-12 15:23:17" jusqu'au "2017-07-18 19:12:23", mettre votre colonne en "datetime" est une bonne idée.
    Pour la recherche de la période, il faudra utiliser :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    "where temps between "2017-07-12 15:23:17" and "2017-07-18 19:12:23"
    Cela n'a pas d'impact sur l'index car cela dépend de la déclaration de votre index.
    Si vous utilisez cette colonne en tant que clef primaire, cela ne peut pas convenir car les valeurs ne sont pas uniques.
    Il faudra, comme je vous l'ai dit, utiliser le type "timestamp" et utiliser les millionième de seconde pour distinguer les doublons.
    Ou bien créer une colonne supplémentaire numéroter de 1 à N juste pour distinguer les doublons.

    Sur un index unique, le problème est le même que la clef primaire.

    Ou bien faire un index non unique et dans ce cas là, le type "datetime" convient parfaitement, même avec des doublons.

    Et donc, qu'est-ce que vous allez utiliser comme clef primaire et pour faire quoi avec ?

    Est-ce que vous avez un ordre de rangement dans votre table ? Par exemple les lignes les plus récentes ont nécessairement la date la plus grande ?
    Mais vu que vous créez une requête pour extraire des lignes sur la date "temps", je vous conseille de créer votre clef primaire en fonction de cette date.

    Citation Envoyé par Hilal74
    Et le champs d3 est bien une chaine de caractere, pensez vous que je devrez utiliser un autre format pour ce champs?
    Une chaîne de caractère, c'est trop vague comme information.

    Est-ce un libellé ? Dans ce cas là, vous externalisez ce libellé dans une autre table et vous utilisez un identifiant pour l'associer en jointure à votre table.
    Dans ce cas là, vous utilisez l'identifiant dans la clause where (et aussi dans l'index) pour accéder à votre libellé.

    Est-ce une chaîne de caractères quelconque ?
    Que recherchez-vous dans cette chaîne ? Des mots ou de longues expressions ?
    Si ce sont des mots, dans ce cas là, vous devriez envisager d'utiliser le FTS (Full text serach).
    --> https://dev.mysql.com/doc/refman/5.7...xt-search.html

    Si ce sont des expressions, est-ce la chaîne de caractères en totalité, commençant dès le début de la chaîne ?
    Dans ce cas là, je comprends pourquoi votre recherche met 20 heures.
    Cette chaîne est votre noeud gordien pour obtenir une bonne pêrformance.

    Citation Envoyé par Hilal74
    Le storage engine est Innodb.
    D'accord et quel est la version de votre mysql ?

    Vous avez un exemple pour améliorer votre base de données et la performance de votre requête.
    Il est fort probable que votre modélisation ne soit pas conforme et il faudrait envisager de revoir cela de fond en comble.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  9. #9
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 974
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 2 974
    Points : 6 533
    Points
    6 533
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par Artemus24 Voir le message
    Cela n'a pas d'impact sur l'index car cela dépend de la déclaration de votre index.
    Si vous utilisez cette colonne en tant que clef primaire, cela ne peut pas convenir car les valeurs ne sont pas uniques.
    Il faudra, comme je vous l'ai dit, utiliser le type "timestamp" et utiliser les millionième de seconde pour distinguer les doublons.
    Ou bien créer une colonne supplémentaire numéroter de 1 à N juste pour distinguer les doublons.
    Attention : techniquement, une colonne timestamp n'est pas unique
    Certes le risque de collision est très faible mais il n'est pas nul, avec une CPU très performante et une sollicitation très faible, le doublon reste possible

  10. #10
    Expert éminent Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    2 896
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 77
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 2 896
    Points : 8 557
    Points
    8 557

    Par défaut

    Salut Escartefigue.

    Oui, je le sais.

    Mais rien n'empêche Hilal74 d'incrémenter le timestamp de +1 sur la millionième partie de la seconde afin de ne pas avoir de doublon.
    Ce qui l'intéresse, c'est la date et les heures uniquement.

    Sinon, il utilise le type datetime avec une colonne qui sera incrémenté de +1 en cas de doublon. Mais je trouve cela moins propre.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  11. #11
    Membre à l'essai
    Inscrit en
    mai 2009
    Messages
    42
    Détails du profil
    Informations forums :
    Inscription : mai 2009
    Messages : 42
    Points : 21
    Points
    21

    Par défaut

    Salut Artemus24,

    Oui j'ai bien un champs qui indique l'ordre d'insertion de mes tickets et ce champs contitue ma clef primaire (allant de 1 a 120 000 000). Je n'ai pas de clef primaire sur le champs temps. Mes lignes sont comme ci dessous:


    1;2016-01-17 12:00:00; d1;d2;d3...............

    Et ma requete pour analyser ces donnees aura une jointure, je pourrai y rechapper. Et une clause where

    Dans ma jointure j'ai un entier et le temps.
    Et dans ma clause where j'ai encore le temps et une chaine de caractere qui est bien un libele (que je pourrait externaliser si ca peut optimiser mes recherches).

    Donc si j'ai bien compris je doit tout mettre dans un seul index.

    ma version de mysql est 5.6.

    Merci

  12. #12
    Expert éminent Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    2 896
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 77
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 2 896
    Points : 8 557
    Points
    8 557

    Par défaut

    Salut Hilal74.

    Pour votre gouverne, quand on parle de SGBDR, on ne nomme pas cela de champs mais de colonnes.
    Colonne par opposition à ligne et non enregistrement.

    Citation Envoyé par Hilal74
    Oui j'ai bien un champs qui indique l'ordre d'insertion de mes tickets et ce champs constitue ma clef primaire (allant de 1 a 120 000 000).
    Et donc, vous avez une clef primaire qui est un auto incrément que vous n'utilisez pas en tant que tel.
    Alors à quoi cela peut vous servir cette colonne, sinon à rien ?

    Citation Envoyé par Hilal74
    Et ma requête pour analyser ces données aura une jointure,
    Une jointure pour quel usage ?
    D'après ce que j'ai pu comprendre votre clause where se consacre à votre colonne datetime et à un libellé.
    Et je suppose que ces deux colonnes sont bien présentes dans votre table.

    Citation Envoyé par Hilal74
    et une chaîne de caractère qui est bien un libellé (que je pourrais externaliser si ça peut optimiser mes recherches).
    Oui, cela va optimiser d'une par les temps d'accès mais aussi la volumétrie de votre table.
    Une colonne de type integer utilise moins de place qu'une chaîne de caractères.

    Citation Envoyé par Hilal74
    Donc si j'ai bien compris je doit tout mettre dans un seul index.
    C'est ce que je vous ai dit, et c'est ce que j'ai fait dans mon exemple.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  13. #13
    Rédacteur/Modérateur

    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    décembre 2013
    Messages
    1 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes
    Secteur : Conseil

    Informations forums :
    Inscription : décembre 2013
    Messages : 1 453
    Points : 3 039
    Points
    3 039

    Par défaut

    J'ai une remarque ... qui est à la fois une remarque et une question.

    Tu dis que tu veux filtrer les données qui vérifient D3=une certaine valeur, et tps dans un certain intervalle.

    Dans ta base de données, tu as en gros 100 Millions de lignes. Si tu sélectionnes un intervalle de temps, tu obtiens par exemple 10 Millions de lignes.
    Ensuite, quand tu filtres avec D3, ça filtre quoi ? Tu gardes la moitié des lignes, tu gardes 5% des lignes ... ou tu gardes 0,1% des lignes ?
    Je pense que c'est utile de savoir si le filtre sur D3 est très discriminant, ou s'il est peu discriminant... Selon cette information, tu vas certainement faire des choix différents.

    Je me trompe, ou non ?
    N'oubliez pas le bouton Résolu si vous avez obtenu une réponse à votre question.

  14. #14
    Expert éminent Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    2 896
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 77
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 2 896
    Points : 8 557
    Points
    8 557

    Par défaut

    Salut tbc92.

    Citation Envoyé par tbc92
    Dans ta base de données, tu as en gros 100 Millions de lignes.
    Il y a selon les dires de Hilal74, 120 millions de lignes.

    Citation Envoyé par tbc92
    Si tu sélectionnes un intervalle de temps, tu obtiens par exemple 10 Millions de lignes.
    Non, car sa sélection va se faire par mois. D'où le fait de créer des partitions par mois de sa table MySql.
    Comme il a stocké deux ans, un mois correspond donc à 120.000.000 / 24 = 5.000.000.

    Citation Envoyé par tbc92
    Ensuite, quand tu filtres avec D3, ça filtre quoi ?
    Les lignes de la table où il fait sa requête ??? Poyrquoi poser cette question ?

    Citation Envoyé par tbc92
    Tu gardes la moitié des lignes, tu gardes 5% des lignes ... ou tu gardes 0,1% des lignes ?
    J'ai posé la question mais il ne m'a pas répondu.
    Il est vrai que sur 5 millions de lignes pour un mois donné, il serait bien d'obtenir comme filtre quelques lignes.

    De plus, on ne sait pas pour quel usage fait-il sa requête ?
    Peut-être qu'il va ranger son résultat dans une table temporaire pour l'exploiter d'une manière plus fine.
    Peut-être une statistique !

    Citation Envoyé par tbc92
    Selon cette information, tu vas certainement faire des choix différents.
    Je ne le pense pas, mais cela aura un impact assez lourd sur la performance.

    Citation Envoyé par tbc92
    Je me trompe, ou non ?
    Non, je ne le pense pas car c'est un point critique de sa requête.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

  15. #15
    Rédacteur/Modérateur

    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    décembre 2013
    Messages
    1 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes
    Secteur : Conseil

    Informations forums :
    Inscription : décembre 2013
    Messages : 1 453
    Points : 3 039
    Points
    3 039

    Par défaut

    Citation Envoyé par Artemus24 Voir le message

    Ensuite, quand tu filtres avec D3, tu gardes la moitié des lignes, tu gardes 5% des lignes ... ou tu gardes 0,1% des lignes ?
    J'ai posé la question mais il ne m'a pas répondu.
    Il est vrai que sur 5 millions de lignes pour un mois donné, il serait bien d'obtenir comme filtre quelques lignes.
    On est bien d'accord, cette information est utile (voire essentielle) pour une réponse optimale.
    N'oubliez pas le bouton Résolu si vous avez obtenu une réponse à votre question.

  16. #16
    Membre à l'essai
    Inscrit en
    mai 2009
    Messages
    42
    Détails du profil
    Informations forums :
    Inscription : mai 2009
    Messages : 42
    Points : 21
    Points
    21

    Par défaut

    Salut Artemus24, tbc92,

    Je vous explique le contexte et je vais essaye de faire de précision car je comprends tt a fait que cette important pour l'optimisation.

    La base de données est issue d'une application "en temps réel", qui a chaque fois qu'il y a un événement il y a deux tickets insérés dans la base. (sa structure d'origine est celle du constructeur).
    Maintenant moi je dois analyser ces données rapidement, la base de données est importe dans un autre serveur (isole) et je peux changer sa structure comme je veux pour l'optimiser car elle contient 120 000 000 de lignes.

    un événement est toujours caractérisé par deux lignes TypEvent=A et TypEvent=B qui contiennent des informations complémentaires:

    1;Start_Event;Stop_Event;ChanalNumber;TypEvent;ModuleEvent....
    2;Start_Event;Stop_Event;ChanalNumber;TypEvent;ModuleEvent....
    Et le seul moyen de lier ces deux lignes malheureusement est la jointure sur le début et fin de l’événement et le Canal: on a fait

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    Select maTable  as a inner join maTable as b 
                                    on a.ChanalNumber=b.ChanalNumber
                                        and  a.TypEvent <>b.TypEvent                                       
                                   where 
                                            a.Start_Event < b.Stop_Event
                                     and  a.Stop_ Event > b.Start_Event
                                     and a.Start_Event between '2015-01-01 00:00:00' and '2015-02-28 00:00:00'            /*Periode d'analyse de donnees*/ 
                                     and a.Start_Event between '2015-01-01 00:00:00' and '2015-02-28 00:00:00' ;
    En fait, ces colonnes "temps" ( début et fin d’événement) sont importantes pour moi pour lier ces deux informations a un événement.

    Donc ce je comprends pour le moment, il faut que je crée un index sur les colonnes (ChanalNumber, TypEvent, Start_Event, Stop_Event).

    Mais avant de créer cette index je dois partitionner la table sur les colonnes Start_Event et Stop_event car apres ma table partionnee, l'indexation devrait etre rapide. Mais pour le partionnenemt j'ai vu dans un livre d'optimisation que l'on ne pouvait
    partitionner une table qui contient des clefs étrangères. Dois supprimer les clefs étrangères ?


    Merci pour votre soutient et n’hésitez a poser des questions si ce n est pas claire et a faire des remarques.

  17. #17
    Rédacteur/Modérateur

    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    décembre 2013
    Messages
    1 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes
    Secteur : Conseil

    Informations forums :
    Inscription : décembre 2013
    Messages : 1 453
    Points : 3 039
    Points
    3 039

    Par défaut

    Tu as une table que je vais appeler TABLE_ORIGINE.
    Cette table a comme structure :
    Start_Event;Stop_Event;ChanalNumber;TypEvent;ModuleEvent....
    Start_Event;Stop_Event;ChanalNumber;TypEvent;ModuleEvent....
    Par rapport à ton message, j'enlève le 1 et le 2 en première colonne, parce que j'imagine qu'il n'y a pas de colonne de ce type.

    Cette table, elle est alimentée par un système autre, et d'après ce que je comprends, tu ne peux pas la modifier (pas la partitionner, pas modifier les index ...) C'est ce que je comprends, mais j'ai quand même un gros doute.

    Tu veux extraire des lignes de cette table, et insérer cela dans une autre table (qu'on va appeler TABLE_ANALYSE) ; Dans cette nouvelle table, il y aura 2 fois plus de colonnes, et 2 fois moins de lignes. Et même , on ne va copier que les données correspondant à un mois, donc on aura beaucoup moins de lignes. Pour cette nouvelle table, tu peux faire ce que tu veux ( partition, index, clé étrangères...)

    Pour faire l'extraction de TABLE_ORIGINE vers TABLE_ANALYSE, tu envisages une requête comme ça :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    Select * from maTable  as a inner join maTable as b 
                                    on a.ChanalNumber=b.ChanalNumber
                                        and  a.TypEvent <>b.TypEvent                                       
                                   where 
                                            a.Start_Event < b.Stop_Event
                                     and  a.Stop_ Event > b.Start_Event
                                     and a.Start_Event between '2015-01-01 00:00:00' and '2015-02-28 00:00:00'            /*Periode d'analyse de donnees*/ 
                                     and b.Start_Event between '2015-01-01 00:00:00' and '2015-02-28 00:00:00' ;
    J'ai corrigé, j'ai mis b.start en dernière ligne. J'imagine que c'est un erreur de copier/coller.
    Atttention. Indépendamment des questions de performances, cette requête ne semble pas bonne. Il faut probablement remplacer a.TypEvent<>b.TypEvent par a.TypEvent<b.TypEvent.
    Et ta question, c'est : Comment faire en sorte que cette extraction (de TABLE_ORIGINE vers TABLE_ANALYSE) soit très rapide.

    Question 1 ; Est-ce que j'ai bien compris (je ne crois pas)
    Question 2 ; La colonne ChanalNumber, elle peut prendre combien de valeurs différentes environ.

    Suggestion : Quand tu filtres en disant : a.Start_Event between '2015-01-01 00:00:00' and '2015-02-28 00:00:00' , pour chaque ligne lue, le système doit convertir Start_Event de type timestamp vers un champ de type string, puis il compare.
    Si tu faisais : and a.Start_Event between to_date('2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2015-02-28 00:00:00','YYYY-MM-DD HH24:MI:SS') , je pense que tu gagnerais BEAUCOUP de temps.

    Enfin, le mis de février se finit à '2015-02-28 23:59:59' ...
    N'oubliez pas le bouton Résolu si vous avez obtenu une réponse à votre question.

  18. #18
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 974
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 2 974
    Points : 6 533
    Points
    6 533
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par tbc92 Voir le message
    Suggestion : Quand tu filtres en disant : a.Start_Event between '2015-01-01 00:00:00' and '2015-02-28 00:00:00' , pour chaque ligne lue, le système doit convertir Start_Event de type timestamp vers un champ de type string, puis il compare.
    Si tu faisais : and a.Start_Event between to_date('2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('2015-02-28 00:00:00','YYYY-MM-DD HH24:MI:SS') , je pense que tu gagnerais BEAUCOUP de temps.
    Si la colonne Start_event est de type timestamp, dans la mesure où le critère de filtrage est au format timestamp, la requête est déjà sargable sans rien modifier.
    Encore faut il qu'il y ait un index filtrant sur le critère bien sur

  19. #19
    Membre à l'essai
    Inscrit en
    mai 2009
    Messages
    42
    Détails du profil
    Informations forums :
    Inscription : mai 2009
    Messages : 42
    Points : 21
    Points
    21

    Par défaut

    Salut tbc92,

    Oui, en effet je comtpe passer d'une "tabe_origne " (car je ne peux toucher a la base de productuction) a une table_analyse (sur un serveur de Lab) pour pouvoir y apporter les modifications de performance car pour le moment la requete met 20 heures pour ressortir un resultat. Et j'ai deux analyser.
    La table_analyse va contenir les memes donnees sauf qu'elle contiendra des indexes supplementaires pour pouvoir optimiser ma requete. J'avais aussi proposer de divider cette table par mois mais on m a aussi proposer d'uliser les partitions qui aussi une idee judicieuse maintenant quelle la meilleur option diviser la table par mois ou faire la partition sur le temps (les clefs etrangeres ne sont pas supportees par la partition, je peux la supprimer).

    La colonne ChannelNumber peut prendre les valeurs de 1 a 2000. Un evenement aura deux tickets (deux lignes) dans la base avec le meme canal et un typEvent different, un ticket sera du type "A" et l'autre ticket du type "B" d'ou a.TypEvent <> b.TypEvent.

    Merci

  20. #20
    Rédacteur/Modérateur

    Homme Profil pro
    Ingénieur qualité méthodes
    Inscrit en
    décembre 2013
    Messages
    1 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Ingénieur qualité méthodes
    Secteur : Conseil

    Informations forums :
    Inscription : décembre 2013
    Messages : 1 453
    Points : 3 039
    Points
    3 039

    Par défaut

    Quand tu copies de Table_Origine vers table_analyse, tu as plusieurs options :
    - copier tous les jours ou toutes les semaines les données nouvelles. Et donc la table 'Table_Analyse' contient toutes les périodes de Table_Origine, mavec juste un petit temps de retard.
    - Pour une analyse donnée, vider la table_analyse, et copier uniquement les données correspondant aux périodes voulues.

    A priori, tu es dans l'option 1.
    Diviser la table par mois, ou créer plusieurs partitions ? J'ai envie de dire : Quand je ne connaissais pas cette notion de partition, ou quand j'utilisais des outils qui ne savaient pas gérer les partitions, je divisais mes données en plusieurs tables. Le concept de partition a été créé justement pour éviter de devoir créer plusieurs tables. Il faut utiliser les partitions. Ceci étant, je suis assez partisan de l'option 2 ci-dessus (créer une table avec uniquement les données utiles) ; le truc à ne pas faire, c'est créer N tables parce que tu as N périodes.

    Donc tu copies les données de table_origine vers table_analyse, en conservant strictement les mêmes colonnes, c'est bien ça ?
    Et c'est ensuite que tu veux lire table_analyse, et créer table_analyse_2, en associant les lignes 2 à 2 ?

    Quand tu associes les données 2 à 2, tu peux écrire : and a.TypEvent<b.TypEvent
    Mais d'après ce que tu dis, tu peux aussi écrire : and a.TypeEvent ='A' and b.type_event ='B'.
    Le résultat sera le même , parce que tu n'as que ces 2 valeurs possibles dans type_event. Mais en terme de performance, ça peut apporter beaucoup. Dans la version initiale, le moteur lit une ligne, c'est une ligne avec type='B' ; le moteur cherche s'il y a d'autres lignes avec les contraintes sur la date, et avec type>'B', et il n'en trouve pas. Mais il a passé du temps à chercher.
    Dans la version 2, le moteur lit la ligne, elle est de type B, et donc il ne s'intéresse pas à cette ligne.
    N'oubliez pas le bouton Résolu si vous avez obtenu une réponse à votre question.

+ Répondre à la discussion
Cette discussion est résolue.
Page 1 sur 2 12 DernièreDernière

Discussions similaires

  1. lancer un job uniquement si une table mysql contient des donnees
    Par jj4822 dans le forum Développement de jobs
    Réponses: 7
    Dernier message: 13/10/2014, 09h58
  2. Réponses: 5
    Dernier message: 20/10/2013, 03h09
  3. Fichier XML qui remplit une table MySQL + pb d'accent
    Par sheira dans le forum PHP & MySQL
    Réponses: 5
    Dernier message: 17/09/2010, 10h01
  4. interrogation d'une table MYSQL qui ne marche pas
    Par laurentSc dans le forum Débuter
    Réponses: 11
    Dernier message: 01/02/2010, 22h24
  5. Réponses: 23
    Dernier message: 22/10/2008, 17h31

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