Discussion :

[audklie2]

Bonjour,

J'ai des problèmes de perfs
J'ai une base de données avec des tables partitionnées selon la date.
Je me demandais si rajouter un index sur cette date apporterait quelque chose, ou si cela revient au même, le partitionnement et l'indexation?

Merci d'avance de vos réponses.

[Waldar]

Le partitionnement va séparer physiquement votre table en plusieurs sous-table.

Vous avez tout à fait le droit d'indexer la colonne de partition, faites bien un index local.

Par exemple, si vous partitionnez par année et que vous faites une requête sur un jour précis, vous aurez un très bon gain de performance.

[audklie2]

Merci beaucoup!

[audklie2]

En fait j'ai encore une petite question à laquelle je ne trouve pas la réponse dans les tuto...

Quelle est la meilleure façon de créer un index? J'ai vu que certains créaient systématiquement un index sur la clef primaire. Est il plus intelligent de créer un index sur les colonnes les plus consultées lors des recherches (par exemple, j'ai 2000 registre par jour, pour 10 paramétrages différents (200 chaque) chacun avec un identifiant différent. Quel est l'index le plus pertinant, date + idf, date+idf+param, date+param? sachant que lors d'une requête qui me pose problème je le recherche par date, idf et param.

J'ai un peu du mal à cerner comment bien se servir des indexs. Si quelqu'un a une bonne page/ un bon tuto la dessus je suis prenneuse!

Merci d'avance

[Waldar]

J'ai vu que certains créaient systématiquement un index sur la clef primaire.

Vous n'avez pas le choix, la clef primaire se traduit toujours par un index unique combiné à une clef non nulle.

Est il plus intelligent de créer un index sur les colonnes les plus consultées lors des recherches

Oui, c'est l'idée. Plus un index est discriminant et plus votre requête sera rapide. Pour votre exemple, faites un index sur le triplet (date, idf, param).

[audklie2]

J'y vois plus clair, merci beaucoup!

[CinePhil]

Un peu de lecture chez SQLPro :
Quoi indexer ?
Maintenance des index

[audklie2]

Je vais regarder ça, merci!

ça me permettra peut être de passer de 1h30 de temps de traitement aux quelques minutes demandées par le client (je suis déjà passé d'une sortie en time out au bout de 10h à 1h30, je progresse )

[CinePhil]

ça me permettra peut être de passer de 1h30 de temps de traitement aux quelques minutes demandées par le client (je suis déjà passé d'une sortie en time out au bout de 10h à 1h30, je progresse )

2000 registre par jour, pour 10 paramétrages différents (200 chaque)

Quelques minutes me semblerait encore énorme ! A moins que je ne représente pas bien le volume total de données à examiner ?
Combien de lignes, de colonnes et de tables concernées par la requête ?

Un petit tour dans le forum Langage SQL ou celui consacré à votre SGBD pour y proposer la structure de vos tables et la requête effectuée serait peut-être utile !

[audklie2]

Oui je sais c'est énorme, mais je compte bien en venir à bout
J'appelle 2 tables une de 25000 registres ( et bien plus en production bien sur!) un de 50000, et quelques petites tables qui ne contiennent pas grand chose.
Vous croyez que je peux descendre à moins d'une minute en arrangeant mes requêtes et en ajoutant les index appropriés? Je ne visais pas aussi peu mais je suis novice en BD

[CinePhil]

Quand tu dis "25000 registres", tu veux dire 25 000 lignes dans la table ?

Ce n'est vraiment pas beaucoup pour un SGBD. J'ai travaillé avec des tables e plusieurs dizaines de millions de lignes.

Soit la structure de ta BDD est à revoir, soit tes tables sont très mal indexées, soit tes requêtes sont mal foutues !

On peut avoir le schéma de la BDD ?

[audklie2]

Pour le schéma, je pense que mon client ne serait pas d'accord
Mais globalement elle est mal indexée, et mal partitionnée, ce qui donne des temps de réponse énormissimes.
C'est pour ça que je suis en train de -tenter de - remettre tout ça sur pied.
(Je m'éclate, j'apprends plein de trucs!)
J'ai appris hier qu'on pouvait partitionner les index! Je suis en train d'en lire plus là dessus
Mais globalement les seuls indexs qu'il y avait avant que j'y touche étaient ceux sur les PK. Pas suffisant pour l'utilisation qu'on en fait.
Merci beaucoup Je pense que je reviendrai avec plus de questions!
Bonne journée

[mikedavem]

J'ai appris hier qu'on pouvait partitionner les index! Je suis en train d'en lire plus là dessus

Attention cette technique n'est réellement utile que lorsque le volume de données ramené ne peut être traité correctement en mémoire (ce qui est très peu probable en OLTP si l'index est bien posé et si la requête est bien construite avec les restrictions nécessaires) ou lorsque la maintenance des index devient très lourde.

++

[SQLpro]

Comme le dit Mikedavem, le partitionnement n'a de réel intérêt que lorsque plusieurs conditions sont réunies :
1) que le volume intrinsèque de la table dépasse
- a) la RAM pour sa partie active (fenêtre de données)
- b) la taille des disques physique pour sa totalité
2) que la gestion des espaces de stockage permettent de faire de la volumétrie prévisionnelle (tailler des espaces préalable de stockage)
3) que l'on puisse dissocier le partitionnement des données et des index

En sus, il faut considérer que les plans de requêtes seront plus complexes, donc cela nécessite plus de puissance de calcul (plus de CPU en particulier).

Enfin, ce n'est pas parce que vous avez partitionner votre table que toutes les requêtes y gagneront. En effet, pour que chacune des requêtes bénéficient du partitionnement il faut :
1) que le critère de partitionnement soit présent dans toutes les requêtes (INSERT, UPDATE, DELETE, SELECT) en tant que prédicat
2) que les prédicats de filtrage ou de jointure soient "sargeable"

Autrement dit, le partitionnement n'est pas du tout la panacée que l'on croit et en production, rares sont les cas ou il est réellement efficace.
En tout cas en dessous de 30 Go de volume de données de la table et avec un serveur doté de moins de 4 CPU physique il est strictement inutile d'utiliser une telle technique.
Il y a bien d'autres techniques d'optimisation, nettement plus efficaces. Pour un panagérique complet, lisez les articles que j'ai écrit à ce sujet : http://sqlpro.developpez.com/optimisation/
(même si c'est spécifique à SQL Server, les concepts d'optimisation des SGBDR sont identiques pour tous les SGBDR à l'exception de MySQL).

A +

[fsmrel]

@audklie2

Une bonne connaissance de l'instruction EXPLAIN PLAN (SHOW PLAN et compagnie) est indispensable pour maîtriser les problèmes de performance.

Quand vous saurez tout le Nested Loop, le Merge scan, le Table scan et autres techniques utilisées par les SGBD, vous verrez que, comme dit CinePhil, vous saurez régler vos requêtes pour qu'elles durent moins d'une seconde.

Bonne lecture.