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

**Hilal74** · 30/07/2017, 08h27

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!

**escartefigue** · 30/07/2017, 10h49

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

**Hilal74** · 30/07/2017, 11h05

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

**tbc92** · 30/07/2017, 11h15

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.

**Hilal74** · 30/07/2017, 11h46

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

**Artemus24** · 31/07/2017, 02h04

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 !

@+

**Hilal74** · 31/07/2017, 18h41

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

**Artemus24** · 31/07/2017, 19h25

Salut Hilal74.

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.

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.

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.

@+

**escartefigue** · 31/07/2017, 20h02

Envoyé par Artemus24

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

**Artemus24** · 01/08/2017, 02h45

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.

@+

**Hilal74** · 01/08/2017, 16h38

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

**Artemus24** · 01/08/2017, 17h26

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.

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 ?

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.

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.

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.

@+

**tbc92** · 01/08/2017, 18h54

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 ?

**Artemus24** · 01/08/2017, 20h16

Salut tbc92.

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.

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.

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 ?

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 !

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.

Envoyé par tbc92

Je me trompe, ou non ?

Non, je ne le pense pas car c'est un point critique de sa requête.

@+

**tbc92** · 01/08/2017, 20h35

Envoyé par Artemus24

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.

**Hilal74** · 03/08/2017, 06h22

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.

**tbc92** · 03/08/2017, 09h54

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' ...

**escartefigue** · 03/08/2017, 10h33

Envoyé par tbc92

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

**Hilal74** · 03/08/2017, 11h43

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

**tbc92** · 03/08/2017, 14h24

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.

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

MySQL

Discussions similaires

Partager

Partager