Différence entre partitionnement et index

audklie2 · 31/03/2010, 10h03

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 · 31/03/2010, 10h33

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 · 31/03/2010, 10h35

Merci beaucoup!

audklie2 · 31/03/2010, 11h06

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 · 31/03/2010, 12h05

Citation:

Envoyé par audklie2

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.

Citation:

Envoyé par audklie2

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 · 31/03/2010, 12h32

J'y vois plus clair, merci beaucoup!

CinePhil · 31/03/2010, 15h31

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

audklie2 · 31/03/2010, 15h33

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 · 01/04/2010, 00h11

Citation:

Envoyé par audklie2

ç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

)

Citation:

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 · 05/04/2010, 10h18

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 · 05/04/2010, 22h02

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 · 06/04/2010, 09h16

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 · 03/05/2010, 19h13

Citation:

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 · 04/05/2010, 09h52

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 · 12/05/2010, 00h35

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

31/03/2010, 10h03	#1
audklie2 Invité régulier Inscription : janvier 2009 Messages : 36 Détails du profil Informations forums : Inscription : janvier 2009 Messages : 36 Points : 8 Points : 8	Différence entre partitionnement et index 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.
	00

31/03/2010, 10h33	#2
Waldar Modérateur Fabien Ingénieur d'études en décisionnel Inscription : septembre 2008 Messages : 6 278 Détails du profil Informations personnelles : Nom : Fabien Âge : 35 Localisation : France, Essonne (Île de France) Informations professionnelles : Activité : Ingénieur d'études en décisionnel Secteur : High Tech - Multimédia et Internet Informations forums : Inscription : septembre 2008 Messages : 6 278 Points : 13 549 Points : 13 549	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. __________________ Email : http://scr.im/waldar
	00

31/03/2010, 15h31	#7
CinePhil Modérateur Philippe Leménager Ingénieur d'études en informatique Inscription : août 2006 Messages : 13 659 Détails du profil Informations personnelles : Nom : Philippe Leménager Âge : 49 Localisation : France, Haute Garonne (Midi Pyrénées) Informations professionnelles : Activité : Ingénieur d'études en informatique Secteur : Enseignement Informations forums : Inscription : août 2006 Messages : 13 659 Points : 25 561 Points : 25 561	Un peu de lecture chez SQLPro : Quoi indexer ? Maintenance des index __________________ Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur. Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework... « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau) À la maison comme au bureau, j'utilise la suite Linux Mageïa !
	00

05/04/2010, 22h02	#11
CinePhil Modérateur Philippe Leménager Ingénieur d'études en informatique Inscription : août 2006 Messages : 13 659 Détails du profil Informations personnelles : Nom : Philippe Leménager Âge : 49 Localisation : France, Haute Garonne (Midi Pyrénées) Informations professionnelles : Activité : Ingénieur d'études en informatique Secteur : Enseignement Informations forums : Inscription : août 2006 Messages : 13 659 Points : 25 561 Points : 25 561	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 ? __________________ Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur. Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework... « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau) À la maison comme au bureau, j'utilise la suite Linux Mageïa !
	00

04/05/2010, 09h52	#14
SQLpro Rédacteur Frédéric BROUARD Expert SGBDR & SQL Inscription : mai 2002 Messages : 12 089 Détails du profil Informations personnelles : Nom : Frédéric BROUARD Localisation : France Informations professionnelles : Activité : Expert SGBDR & SQL Secteur : Conseil Informations forums : Inscription : mai 2002 Messages : 12 089 Points : 21 698 Points : 21 698	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 + __________________ Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL Site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/ *Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.* Blog SQL, SQL Server, modélisation données : http://blog.developpez.com/sqlpro http://www.sqlspot.com : modélisation, conseils, audit, optimisation, formation * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
	00

31/03/2010, 10h35	#3
audklie2 Invité régulier Inscription : janvier 2009 Messages : 36 Détails du profil Informations forums : Inscription : janvier 2009 Messages : 36 Points : 8 Points : 8	Merci beaucoup!
	00

31/03/2010, 11h06	#4
audklie2 Invité régulier Inscription : janvier 2009 Messages : 36 Détails du profil Informations forums : Inscription : janvier 2009 Messages : 36 Points : 8 Points : 8	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
	00

31/03/2010, 12h32	#6
audklie2 Invité régulier Inscription : janvier 2009 Messages : 36 Détails du profil Informations forums : Inscription : janvier 2009 Messages : 36 Points : 8 Points : 8	J'y vois plus clair, merci beaucoup!
	00

31/03/2010, 15h33	#8
audklie2 Invité régulier Inscription : janvier 2009 Messages : 36 Détails du profil Informations forums : Inscription : janvier 2009 Messages : 36 Points : 8 Points : 8	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 )
	00

05/04/2010, 10h18	#10
audklie2 Invité régulier Inscription : janvier 2009 Messages : 36 Détails du profil Informations forums : Inscription : janvier 2009 Messages : 36 Points : 8 Points : 8	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
	00

06/04/2010, 09h16	#12
audklie2 Invité régulier Inscription : janvier 2009 Messages : 36 Détails du profil Informations forums : Inscription : janvier 2009 Messages : 36 Points : 8 Points : 8	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
	00

12/05/2010, 00h35	#15
fsmrel Expert Confirmé Sénior François de Sainte Marie Spécialiste en bases de données Inscription : septembre 2006 Messages : 3 637 Détails du profil Informations personnelles : Nom : François de Sainte Marie Localisation : Autre Informations professionnelles : Activité : Spécialiste en bases de données Secteur : Conseil Informations forums : Inscription : septembre 2006 Messages : 3 637 Points : 9 170 Points : 9 170	@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. __________________ _ Faites simple, mais pas plus simple ! (A. Einstein) E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire ») => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale ») __________________ Bases de données relationnelles et normalisation : de la première à la sixième forme normale (Bonne lecture !)
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email