Question optimsation (index)

Bonjour,

J'ai un site sur lequel il y a pas mal de traffic.
Sur chaque page, j'insère des données dans une table. Les infos que je stocke sont (entre autres): l'ip du visiteur, un numéro qui correspond à la page et un numéro qui correspond au referer (site qui a envoyé le visiteur).

Cela représente des données très volumineuses.
Afin d'établir des stats, je suis amené à faire de manière reguliere des select sur cette table.
Le select porte sur l'ip, mais aussi sur les différents numéros (N° de referer, N° de la page, etc.)

Si je lit mot pour mot la litterature, je vois qu'il faut faire un index multiple sur l'ensemble des champs qui interviennent dans le where.

Dans mon cas, cela conduit à générer un index assez lourd car il existe beaucoup de combinaisons d'ip/referer/pages.

Je remarque que pour une ip donnée, il y a finalement peu d'enregistrements.

Je serais tenté de placer un index juste sur l'ip du coup...

Est ce que quelqu'un peut me donner son avis sur la question ?

D'avance Merci

Bonsoir,

Un index sera d'autant plus performant qu'il sera sélectif.
Effectivement mettre un index sur l'ip peut être une bonne chose à voir.

Tout dépend ensuite comment vous filtrez dans la clause WHERE, il faudra sûrement ajouter certaines colonnes.

Si votre index est trop lourd, il vous reste la possibilité d'inclure certaines colonnes dans votre index... possible avec SQL Server 2005

++

Justement, c'est ma question ! Est ce qu'il faut que je fasse un index multiple !

Mon where est hyper simple:

Code:

---

select * from tableStats where adresseIp='x.x.x.x' and numeroReferer=5 and numeroPage=10

---

Je résume ma question:

Est ce que je fait un index multiple sur (adresseIp, numeroReferer, numeroPage) ?
Ou bien un index simple uniquement sur (adresseIp).

Sachant que : donnée essentielle : pour une IP donnée il n'y a pas beaucoup d'enregistrements (on va dire une cinquantaine en moyenne alors que la table en contient plusieurs dizaine de milliers...)

D'avance Merci !

Vous historisez donc vos logs.

Dites moi si je me trompe mais dans ce cas vous allez avoir beaucoup d'enregistrements pour une IP au bout d'un certain temps.. Vous allez également avoir un critère de date je suppose ... ou est ce que vous videz cette table ou bout d'un certain laps de temps ??

Pouvez vous nous en dire plus sur la structure de votre table tableStats ?

Quoi qu'il en soit, si dans votre SELECT * (A éviter), vous ne récupérez que les informations contenus dans la restricition de votre requête, il est intéressant de mettre dans votre index toutes les colonnes concernés par cette restriction. Dans le cas contraire, même si vous allez utiliser votre index, étant donné que celui-ci ne contiendra pas toutes les informations nécessaires il devrait faire une recherche par clés dans votre table.. donc mois performant ...

++

Avez vous une clef primaire dans cette table ?
Si oui, est-elle cluster ??
Si non, il serait intéressant de faire un index cluster sur la combinaison adresseIp, numeroReferer, numeroPage mais dans ce cas :
1) mettre un fill factor assez important (tout dépend du nombre d'insertion par jour par rapport au volume de la table)
2) ré indexer cette table une fois par jour aux heures creuses.

Donnez nous le script complet de votre table contraintes comprises, le volume de données actuel (sp_spaceused 'matable') et le nombre moyen de ligne inséré par jour.

A +

Cette table est en fait vidée a intervalle régulier (tout ce qui a plus de x heures) donc le nombre d'enreg par IP reste le même.

Il n'y a pas de clef primaire.

Je dit peut être une connerie, mais l'idée de mettre un index cluster sur tous les champs me fait peur: ça va créer un index immense et sql server va être hyper chargé pour construire ces index (qqsoit la valeur du fill factor).

D'ou l'idée de faire un index simple sur IP...

Un index cluster est en fait la table elle même triée. La taille de l'index est donc la taille des données de la table plus quelques pages techniques de navigation.

En théorie une relation (terme consacré pour la table dans la monde relationnel) sans clef n'existe pas. En pratique, une table sans clef est une aberration. En effet les bases de données relationnelles ont été créées pour gérer des relations (qui sont des objets mathématique porteur de données) devant avoir OBLIGATOIREMENT une clef, et non de vulgaires fichiers "à la Cobol". Ils sont spécialement conçus à cet effet.
Tous les SGBD relationnels, partent du principe que toute table possède un clef (dite primaire) afin d'en optimiser les accès. En l'absence de clef, la lecture de votre table sera toujours lente, car elle sera lue à la manière d'un fichier donc séquentiellement par balayage de toutes les lignes. L'ajout d'un index dans une table sans clef, améliore les choses, mais créée de la redondance, donc plus de volume.

Lorsque vous créez une clef primaire, SQL Server créé un index de type CLUSTER, c'est à dire qu'il organise les données de la table en triant les lignes dans le sens de la clef d'index (la clef de l'index étant la ou les colonnes qui participent à la définition de l'index).
Bien entendu il ne peut y avoir qu'un seul index CLUSTER puisque l'index cluster est la table elle même.

En créant une clef primaire sur l'ensemble des colonnes qui vous intéresse le plus, vous aurez donc tous les avantages et le moins d'inconvénients.
Cela dit, si l'insertion des lignes est aléatoire, alors la table cluster risque d'être fragmentée. C'est pourquoi il est intéressant de prévoir un FILL FACTOR. Le fait de vider votre table n'a pas réellement d'intérêt, sauf si vous êtes crucialement gêné par le volume de stockage. Mais une table de 100 millions de lignes correctement indexées, répond aussi vite qu'une petite table.

Lisez les articles que j'ai écrit à ce qujet : http://blog.developpez.com/sqlpro/p5...-ce-que-c-est/
http://sqlpro.developpez.com/cours/quoi-indexer/


A +

Merci pour vos réponses.

J'ai lu vos documents fort interessants.

Par contre, je n'ai pas compris à quoi correspond le "fill factor". C'est quoi exactement ? ça existe dans d'autres SGBDR comme mysql ?

Difficile de mettre une clef primaire dans cette table car on peut avoir des doublons. Du coup a part mettre un champ avec un entier qui s'incrémente automatiquement je vois pas.

On est obligé de vider la table. En fait on ne garde que quelques jours de données.

Citation:

---

Envoyé par boby62423

Par contre, je n'ai pas compris à quoi correspond le "fill factor". C'est quoi exactement ? ça existe dans d'autres SGBDR comme mysql ?

---

Oui, mais porte parfois un non différent comme chez Oracle qui l'a appelé PCTFREE. C'est le facteur de remplissage des pages d'index. A la création si les pages sont pleine, la moindre insertion provoque des splits de page. Si les pages ont un peu de réserve vide, alors il n'y a pas de split tant que la page n'est pas remplie.

Citation:

---

Envoyé par boby62423

Difficile de mettre une clef primaire dans cette table car on peut avoir des doublons. Du coup a part mettre un champ avec un entier qui s'incrémente automatiquement je vois pas.

---

Cela ne vous empêche pas de créer un index cluster qui ne sera pas unique. C'est toujours mieux que rien...

Citation:

---

Envoyé par boby62423

On est obligé de vider la table. En fait on ne garde que quelques jours de données.

---

Obligé est un grand mot... Irez vous en prison si vous ne le faites pas ???

A +

Le fill factor existe t il chez mysql ?

Pour l'index cluster sur plusieurs champs, je suis d'accord mais on ne peut pas appeler ça une clef primaire, on est bien d'accord ?

Vider la table est une obligation (de manière permanente en fait) car on a des requêtes qui vont faire des stats et ces requêtes ne filtrent pas sur la date (pour éviter une clause en plus dans le where).

D'ailleurs, un index sur un champ timestamp est il une bonne chose ?

Citation:

---

D'ailleurs, un index sur un champ timestamp est il une bonne chose ?

---

Question plus que triviale... A quoi sert un index ? Réponse : à accélérer les recherches !

A +

Pour le coup du timestamp, je me suis très mal exprimé, j'en suis désolé.

En fait, quand je vais aller filtrer sur le timestamp, ça va pas être pour choisir un timestamp mais plutot une plage (une grosse plage même qui peut comporter plus de la moitié des enregs de la table....)

Donc du coup, je repose ma question: Dans ces conditions, est ce que ça vaut quand même le coup ?

D'avance merci

Bonjour,

Créer un index sur une colonne de type TIMESTAMP ne vous coûtera physiquement pas plus cher que de l'avoir fait sur une colonne de type DATETIME.
Pour filtrer sur une plage, utilisez le prédicat BETWEEN.
Comme vous l'a conseillé SQLPro, vous devriez créer une colonne de type entier avec la propriété de compteur et poser un index cluster sur celle-ci.
Si vous n'en avez pas envie, et que vous êtes certains que vos dates sont uniques, créez donc l'index cluster sur les dates.

Un index cluster ne signifie pas nécessairement clé primaire, ce peut être un index de contrainte d'unicité aussi ;).

Il n'y a pas de mal à créer un index composite (cluster ou pas), ensuite c'est à vous de mesurer le gain que vous en retirez : exécutez votre requête sans index dans un premier temps, en la précédant de SET STATISTICS IO ON, et relevez le nombre de pages et d'analyses effectuées.
Ensuite posez votre index, et ré-exécutez votre requête, puis relevez le nombre de pages lues, il faut qu'il ait diminué substantiellement.

Lisez également le plan de requête (CTRL+L) et vérifiez qu'il n'y a pas de :

- recherche de clés
- bookmark lookup
- clustered index scan
- table scan
- hash match

@++ ;)